基于oracle数据库PLSQL存储过程的创建及调用

1. PLSQL编程

1.1 概念和目的

1. PL/SQL（Procedure Language/SQL）

2. PLSQL是Oracle对sql语言的过程化扩展  (类似于Basic)

3. 指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

1.2 程序结构

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

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

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

-- Created on 2018/3/21 by ADMINISTRATOR 
DECLARE
  -- 声明变量、游标。
  I INTEGER;
BEGIN
  -- 执行语句
  --[异常处理]
END;

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

1.3 Hello World

BEGIN
  --打印hello world
  DBMS_OUTPUT.PUT_LINE('hello world');
END;

其中DBMS_OUTPUT 为oracle内置程序包,而PUT_LINE()是调用的方法

在sqlplus中也可以编写运行PLSQL程序：

SQL> BEGIN
  2
  3    --打印hello world
  4
  5    DBMS_OUTPUT.PUT_LINE('hello world');
  6
  7  END;
  8  /

PL/SQL 过程已成功完成。

1、执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的 我们需要开启一下 set serveroutput on ；

2、SQLPLUS中执行PLSQL程序 需要在程序最后添加一个  /  标识程序的结束 ；

1.4 变量

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

1、普通数据类型（char,varchar2, date, number, boolean, long）

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

声明变量的方式为：

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

1.4.1 普通变量

变量赋值的方式有两种：

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

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

DECLARE
  V_NAME VARCHAR2(20) := '张三'; -- 声明变量直接赋值
  V_SAL NUMBER;
  V_ADDR VARCHAR2(200);
BEGIN
  --在程序中直接赋值
  V_SAL := 1580;
  --语句赋值
  SELECT '上海市传智播客' INTO V_ADDR FROM DUAL;
  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' ||V_ADDR);
END;

1.4.2.   引用型变量

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

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

v_name  emp.ename%TYPE;

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

1.4.3.  记录型变量

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

语法： 

变量名称   表名%ROWTYPE

例如： 

**v_emp emp%rowtype;

示例代码：

DECLARE
  -- 记录型变量接受一行
  V_EMP EMP%ROWTYPE;
BEGIN
  --记录型变量默认接受表中的一行数据，不能指定字段。
  SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839;
  --打印变量，通过变量名.属性的方式获取变量中的值
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.ENAME || ',薪水：' || V_EMP.SAL);
END;

1、记录型变量可以方便的解决多字段问题，避免声明过多的变量；

2、该方法不能指定字段类型搜索，必须完整的行，以下是不正确的 SELECT ename,sal INTO V_EMP FROM EMP WHERE EMPNO = 7839;

3、只能获取一行数据，select语句查询结果只能一行；

1.5.  流程控制

1.5.1 条件分支

语法：

BEGIN
  IF 条件1 THEN 执行1   
   ELSIF 条件2 THEN 执行 2 
   ELSE 执行3   
  END IF;  
END;

注意关键字：ELSIF并不是ELSEIF

1.5.2 循环

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

语法：

BEGIN
  LOOP
  	EXIT WHEN 退出循环条件  
  END LOOP;
END;

示例代码：

DECLARE
  --声明循环变量并赋初值
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN V_NUM > 10;
    DBMS_OUTPUT.PUT_LINE(V_NUM);
    --循环变量自增
    V_NUM := V_NUM + 1;
  END LOOP;
END;

2. 游标

2.1 什么是游标

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

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

2.2 语法

游标声明：

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

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

2.3 游标的属性

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

不带参数示例代码：

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明游标
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP;
  
  --遍历游标中的值
  LOOP
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;
    --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  END LOOP;

  --关闭游标
  CLOSE C_EMP;
END;

带参数示例代码：

DECLARE
  --声明游标传递参数
  CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS
    SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO;
  
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标并传递参数
  OPEN C_EMP(10);
  
  --遍历游标中的值
  LOOP
    --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
    EXIT WHEN C_EMP%NOTFOUND;
       
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
           INTO V_ENAME, V_SAL;

    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  END LOOP;

  --关闭游标
  CLOSE C_EMP;
END;

注意:%NOTFOUND属性默认值为FLASE,所以在循环中要注意判断条件的位置.如果先判断在FETCH会导致最后一条记录的值被打印两次(多循环一次默认);

3.   存储过程

3.1.  概念作用

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

存储过程作用：

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

2，  ORACLE官方给的建议：能够让数据库操作的不要放在程序中。

3.2 语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END [过程名称];

根据参数的类型，我们将其分为3类讲解：1、不带参数的；2、带输入参数的；3、带输入输出参数(返回值)的。

3.3 无参存储

3.3.1 创建存储

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

创建存储过程:

--通过调用存储过程打印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('hello world');
END P_HELLO;

3.3.2.  调用存储过程

通过PLSQL程序调用：

BEGIN
 --直接输入调用存储过程的名称
  P_HELLO;

END P_HELLO;

在SQLPLUS中通过EXEC命令调用:

exec p_hello;

提示:SQLPLUS中显示结果的前提是需要 set serveroutput on;

is和as是可以互用的，用哪个都没关系的 过程中没有declare关键字，declare用在语句块中

3.4 带输入参数的存储过程

示例代码：查询某位员工的姓名和薪水，要求，调用存储过程时传员工编号，控制台输出。

CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
  --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
BEGIN

  --根据用户传递的员工号查询姓名和薪水
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;

  --输出结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);

END P_QUERYNAMEANDSAL;

命令调用：

exec p_querynameandsal(7839);

PLSQL程序调用：

BEGIN
  P_QUERYNAMEANDSAL(7839);
END;

3.5.  带输出参数的存储过程

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

CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(I_EMPNO IN EMP.EMPNO%TYPE,O_SAL OUT EMP.SAL%TYPE) IS
BEGIN
  SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;
END P_QUERYSAL_OUT;

PLSQL程序调用：

DECLARE
  --声明一个变量接受存储过程的输出参数
  V_SAL EMP.SAL%TYPE;

BEGIN

  P_QUERYSAL_OUT(7839, V_SAL); --注意参数的顺序
  DBMS_OUTPUT.PUT_LINE(V_SAL);

END;

注意:调用的时候,参数要与定义的参数的顺序和类型一致。

 您阅读本篇文章共花了：