Oracle数据库笔记（基于oracle数据库的PLSQL编程以及存储过程的创建和使用）

Oracle数据库笔记（基于oracle数据库的PLSQL编程以及存储过程的创建和使用）

1. PLSQL编程
   1. 概念和目的
   2. 程序结构
   3. Hello World
   4. 变量普通变量
      1. 引用型变量
      2. 记录型变量
   5. 流程控制
      1. 条件分支
      2. 循环
2. 游标
   1. 什么是游标
   2. 语法
   3. 游标的属性
   4. 创建和使用
   5. 带参数的游标
3. 存储过程
   1. 概念作用
   2. 语法
   3. 无参存储
      1. 创建存储
      2. 调用存储过程
   4. 带输入参数的存储过程
   5. 带输出参数的存储过程、
   6. JAVA程序调用存储过程
      1. 分析jdk API
      2. 实现代码

PLSQL编程 1.概念和目的

    什么是PL/SQL?

1. PL/SQL(Procedure Language/SQL)
2. PLSQL是Oracle对sql语言的过程化扩展（类似于Basic）
3. 指在SQL命令语言中增加了过程处理语句（如分支，循坏等），使用SQL语言具有过程处理能力

2.程序结构

通过Plsql Developer工具的Test Window创建程序模板或者通过语句SQL Window编写

提示：PLSQL语言大小写是不区分的

PL/SQL可以分为三个部分：声明部分，可执行部分，异常处理部分

其中DECLARE部分用来声明变量或者游标（结构集类型变量），如果程序中无3变量声明可以省略掉

3.Hello World

第一个hello world程序

其中DBMS_OUTPUT为oracle内置程序包，相当于Java中的system.out而PUT_LINE()是调用的方法，相当于println（）方法；

在sqlplus（window命令窗口）也可以编写运行PLSQL程序

执行结束后未显示输出结果，默认情况下，输出选项是关闭状态，我们需要输入命令 set serveroutput on开启。

SQLPLUS中执行PLSQL程序需要在程序最后添加“/”标识程序的结束

4.变量

  PLSQL编程中常见的变量分两大类：

1. 普通数据类型（char，varchar2，date，number，Boolean，long）
2. 特殊变量类型（引用型变量，记录型变量）

声明变量的方式为：

普通变量

变量的赋值方式有两种：

 1.直接赋值语句   变量名 := 值 ；（v_name 数据类型 ：= ‘zhangshan’；）

 2.语句赋值，使用select……into……赋值：（语法select 值 into 变量名；）

示例：打印人员信息：姓名，薪水，地址

引用型变量

变量的类型和长度取决于表中的字段类型和长度

通过表名.列名%TYPE指定变量的类型和长度（表中是什么样子，取出来也是什么样子，不需要去考虑长度类型是否一致），例如：

  V_name emp.ename%TYPE；

示例：

引用变量的好处：使用普通变量定义方式，需要知道表中列的类型，使用引用类型，不需要考虑列的类型，使用%type是非常好的编程风格。因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

记录型变量

接受表中的一整行记录，相当于java中的对象

语法：变量名称 表名.ROWTYPE, 例如： v_emp emp%rowtype；

示例：查询并打印7839员工的姓名和薪水

如果一个表有一百个字段，那么程序如果要使用这100个字段，如果使用引用类型一个个声明，会特别麻烦，记录变量可以方便解决这个问题，如果你要使用一个字段，而使用记录变量查询全表会增加数据库的负担。

错误的使用：

1. 记录型变量只能存储一个完整的行数据

此处指定了ename，sal字段

返回的行太多，纪律变量也接收不了

                    此处返回表中所有的行，而只定义一个字段接收。

5.流程控制

条件分支

语法：

注意关键字：ELSIF

[示例] 判断emp表中记录是否超过20条，10-20之间，或者10条以下

​​​​​​​​​​​​​​

循环

在oracle中有三种循坏，在此介绍loop循环

示例：

游标​​​​​​​ 1.什么是游标

用于临时存储一个查询返回的多行数据（结果集，类似于java的jdbc链接返回的ResultSet集合），通过遍历游标，可以逐步进行访问处理结果集的数据。

游标的使用方式：声明 à 打开 à 读取 à 关闭

​​​​​​​2.语法

游标声明：

 CURSOR 游标名（参数列表） IS 查询语句；

游标的打开：

 OPEN 游标名：

游标的取值：

 FETCH 游标名 INTO 变量列表；

游标的关闭：

 CLOSE 游标名；

​​​​​​​3.游标的属性

​​​​​​​ 4.创建和使用

示例：使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来

5.带参数的游标

示例：使用游标查询并打印某部门的员工姓名和薪资，部门编号为运行时手动输入。

注意：%NOTFOUND属性默认值为flase，所以在循坏中要注意判断体哦条件的位置，如果先判断在FETCH会导致最后一条记录的值被打印两次（多循坏一次默认）

存储过程 1.概念作用

      之前我们编写的PLSQL程序可以进行表的 *** 作，判断，循坏逻辑的处理工作，但无法重复调用

可以理解之前的代码全部编写在一个main方法中，是匿名程序，java可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序被称之为存储过程。

  存储过程的作用：

           1.在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接和关闭是很耗费资源的），需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到  PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次 数据库就可以实现我们的业务，可以大大提高效率。

          2.ORACLE官方给的建议是：能够让数据库 *** 作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中 *** 作可能会存在错误。（如果在数据库中 *** 作数据，可以有一定的日志恢复功能）

​​​​​​​2.语法

根据参数的类型，我们将其分为3类讲解：

|不带参数的

|带输入参数的

|带输入输出参数（返回值）的

​​​​​​​3.无参存储

创建存储

通过Plsql Developer或者语句创建存储过程；

示例：通过调用存储过程打印hello world

创建存储过程：

通过工具查看创建好的存储过程：

​​​​​​​调用存储过程

1. 通过PLSQL程序调用：

  

 2.在SQLPLUS中通过EXEC命令调用：

提示：SQLPLUS中显示结果前提是需要开启 set serveroutput on；调用大小写没有关系。

注意：

第一个问题：is和as是可以互用的，用那个都没有关系

第二个问题：过程中没有declare关键字，declare用在语句块中

​​​​​​​4.带输入参数的存储过程

示例：查询并打印某个员工（如7839）的姓名和薪水 存储过程：要求，调用的时候传入员工编号，自动控制台打印

命令调用：

Plsql程序调用：

​​​​​​​5.带输出参数的存储过程

示例：输入员工号查询某个员工（如7839号）信息，要求，将薪水作为返回值输出，给调用的程序使用。

PLSQL程序调用：

注意：调用的时候，参数要与定义的参数顺序和类型一样。

​​​​​​​6.JAVA程序调用存储过程

需求：如果一条语句无法实现结果集，比如需要多表查询，或者需要复杂的逻辑查询，我们可以选择调用存储查询出你的结果。

​​​​​​​分析jdk API

通过Connection对象的prepareCall方法可以调用存储过程

得出结论：通过Connection对象调用prepareCall方法传递一个转义sql语句调用存储过程，输入参数直接调用set方法传递，输出参数需要注册后，执行存储过程，通过get方法获取，参数列表的下标是从1开始的。

​​​​​​​实现代码

 

总结：这是我自学的Oracle数据库笔记，看的是网上的黑马的教材，有需要的可以找一下这个视频教材。