基于Oracle数据库存储过程及调用

1.4.变量

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

普通数据类型（char,varchar2,date,number,boolean,long)

特殊变量类型（引用型变量、记录型变量）

声明能量的方式为

1变量名变量类型（变量长度）例如：v_namevarchar2(20);

1.4.1.普通变量

变量赋值的方式有两种：

直接赋值语句 := 比如：v_name := 'zhangsan'

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

【示例】打印人员个人信息，包括姓名、薪水、地址

SQL>-- 打印人员个人信息，包括：姓名、薪水、地址

SQL>DECLARE

2-- 姓名

3v_namevarchar2(20) :='张三';

4-- 薪水

5v_salNUMBER;

6-- 地址

7v_addrVARCHAR(200);

8BEGIN

9-- 直接赋值

10v_sal :=580;

11-- 语句赋值

12SELECT'上海'intov_addrfromdual;

13Dbms_output.put_line('姓名：'||v_name||',薪水:'||v_sal||',地址:'||v_addr);

14end;

15/

姓名：张三,薪水:580,地址:上海

PL/SQL 过程已成功完成。

1.4..2.引用型变量

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

通过表名.列名%TYPE指定变量的类型和长度，例如：v_name emp.ename%TYPE;

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

1 DECLARE--查询emp表中comm=1400.00的员工的个人信息，打印姓名和薪水

2--姓名

3V_NAME emp.ename%TYPE:='张三';--声明变量直接赋值

4V_SAL emp.sal%TYPE;-- 薪水

5BEGIN

6selectename,salintov_name,v_salfromempwherecomm=1400.00;--查询表中的姓名和薪水并赋值给变量

7Dbms_Output.put_line('姓名'||v_name||'薪水'||v_sal);-- 注意查询的字段和赋值的变量的顺序、个数、类型要一致 -- 打印变量

8end;

引用型变量的好处：

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

1.4.3.记录型变量

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

语法：变量名称表名%ROWTYPE，例如：v_emp emp%ROWTYPE;

【示例】

查询并打印comm是1400.00的员工的姓名和薪水

1-- 查询emp表中comm是1400.00的员工的个人信息，打印姓名和薪水

2declare

3-- 记录型变量接受一行

4V_EMP emp%ROWTYPE;

5begin

6-- 记录变量默认接受表中的一行数据，不能指定字段。

7select*intov_empfromempwherecomm=1400.00;

8-- 打印变量

9Dbms_Output.put_line('姓名：'||v_emp.ename||'薪水：'||v_emp.sal);

11end;

如果有一个表，有100个字段，那么你程序如果要使用这100个字段话，如果你使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题。

错误的使用：

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

1.5.流程控制

1.5.1.条件分支

语法：

1 begin

4IF条件1THEN执行1

6ELSIF条件2THEN执行2

8ELSE执行3

10ENDIF；

12end;

注意关键字：ELSIF

【示例】判断emp表中记录是否超过20条，10~20之间，或者10条以下

1DECLARE

2-- 声明变量接受emp表中的记录数

3V_COUNTNUMBER;

4begin

6-- 查询emp表中的记录数赋值给变量

8SELECT COUNT(1) INTO V_COUNT FROM EMP;

10-- 判断打印

12IFV_COUNT >20THEN

13dbms_output.put_line('EMP表中的记录数超过了20条为：'||V_COUNT||'条。');

15ELSIFV_COUNT >=10THEN

16dbms_output.put_line('EMP表中的记录数在10~20条之间为：'||V_COUNT||'条。');

18ELSE

19dbms_output.put_line('EMP表中的记录数10条以下为：'||V_COUNT||'条。');

20ENDIF;

21end;

1.5.2.循环

在ORACLE中有三种循环方式，这里我们不再展开，只介绍其中一种：loop循环

语法：

BEGIN

LOOP

EXIT WHEN 退出循环条件

END LOOP;

END;

【示例】打印数字1-10

1declare

2-- 声明循环变量

3v_numNUMBER:=1;

4begin

5LOOP

6exitwhenv_num >10;

7dbms_output.put_line(v_num);

8-- 循环变量的自增

9v_num := v_num +1;

10ENDLOOP;

11end;

2.游标

2.1.什么是游标

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

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

2.2.语法

游标声明：

CURSOR 游标名[(参数列表)] IS 查询语句;

游标的打开：

OPEN 游标名；

游标的取值；

FETCH 游标名 INTO 变量列表；

游标的关闭；

CLOSE 游标名；

2.3.游标的属性

游标的属性返回值类型说明

%ROWCOUNT-----------整型----------获得FETCH语句返回的数据整行

%FOUND------------布尔型---------------最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND--------------布尔型-----------与%FOUND属性返回值相反

%ISOPEN----------------布尔值-------------游标已经打开时值为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环。

2.4.创建和使用

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

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

DECLARE

-- 声明游标

CURSORC_EMPISSELECTENAME,SALFROMEMP;

-- 声明变量接受游标中的数据

V_ENAME EMP.ENAME%TYPE;

V_SAL EMP.SAL%TYPE;

BEGIN

-- 打开游标

OPENC_EMP;

-- 遍历游标

LOOP

-- 获取游标中的数据

FETCHC_EMPINTOV_ENAME,V_SAL;

-- 退出循环条件

EXITWHENC_EMP%NOTFOUND;

Dbms_Output.put_line('姓名：'||V_ENAME||'薪资：'||V_SAL);

ENDLOOP;

-- 关闭游标

CLOSEC_EMP;

END;

执行结果：

2.5.带参数的游标

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

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

2-- 声明游标传递参数

3CURSORC_EMP(V_EMPNO EMP.EMPNO%TYPE)IS

4SELECTENAME, SALFROMEMPWHEREEMPNO = V_EMPNO;

6-- 声明变量用来接受游标中的元素

7V_ENAME EMP.ENAME%TYPE;

10V_SAL EMP.SAL%TYPE;

13begin

16-- 打开游标并传递参数

17OPENC_EMP(7839);

19-- 遍历游标中的值

20LOOP

22-- 通过FETCH语句获取游标中的值并赋值给变量

23FETCHC_EMPINTOV_ENAME, V_SAL;

25EXITWHENC_EMP%NOTFOUND;

26DBMS_OUTPUT.PUT_LINE('姓名：'||V_ENAME||'薪水'||V_SAL);

28ENDLOOP;

30end;

执行结果：

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

3.存储过程

3.1.概念作用

之前我们编写的PLSQL程序可以进行表的操作、判断、循环逻辑处理的工作，但无法重复调用。可以理解之前的代码全部编写在了main方法中，是匿名程序，JAVA可以通过封装对象和方法来解决复用问题。PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程

存储过程作用：

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

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

3.2.语法

-- 这部分的as可以换成is，效果一样

create procedure 过程名 as

-- 没有使用declare声明变量，但是可以在begin上边直接声明变量

begin

-- 执行部分

end[过程名];

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

|不带参数

|带输入参数的

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

3.3.无参函数

3.3.1.创建存储

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

3.3.2调用存储过程

通过PLSQL程序调用（在新建文件夹处，重新打开一个test window）：

1begin

2-- 直接输入调用存储过程的名称

3p_hello;

5end;

提示：SQLPLUS中显示结果的前提是需要set serveroutpput on

注意：

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

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

3.4.带输入参数的存储过程

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

1createorreplaceprocedurep_querynameandsal(I_EMPNOINEMP.EMPNO%TYPE)is-- 查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印。

2-- 声明变量

3v_ename emp.ename%TYPE;

4v_sal emp.sal%TYPE;

5begin

7-- 查询emp表中某个员工的姓名和薪水并复制给变量

8-- 根据用户传递的员工号查询姓名和薪水

9selectename, salintov_ename, v_salfromempwhereempno=I_EMPNO;

10-- 打印结果

11dbms_output.put_line('姓名：'||v_ename||'，薪水：'||v_sal);

14endp_querynameandsal;

命令调用：

1SQL>execp_querynameandsal(7654)

2姓名：MARTIN，薪水：1250

4PL/SQL 过程已成功完成。

PLSQL程序调用：

1 begin

4 p_querynameandsal(7654);

7 end;

执行结果：