PL/SQL学习笔记—-存储过程(Oracle)

存储过程指的是在大型数据库系统中专门定义的一组SQL语句集，它可以定义用户操作参数，并且存在于数据库中，当使用时直接调用即可

存储过程=过程的声明+PL/SQL块

定义过程语法：

CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称[参数模式] NOCOPY 数据类型 [参数名称 [参数模式] NOCOPY 数据类型,...]])
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
声明部分;
BEGIN
程序部分;
EXCEPTION
导常处理;
END;

• 参数中定义参数模式表示过程的数据的接收操作，一般分为IN,OUT,IN OUT 3类
• CREATE [OR REPLACE]：表示创建或者替换过程，如果过程存在则替换，如果不存在就创建一个新的
• AUTHID子句定义了一个过程的所有者权限，DEFINER（默认）表示定义者权限执行，或者用CURRENT_USER覆盖程序的默认行为，变为使用者权限
• PRAGMA AUTONOMOUS_TRANSACTION:表示过程启动一个自治事务，自治事务可以让主事挂起，在过程中执行完SQL后，由用户处理提交或者回滚自治事务， 然后恢复主事务
• EXECUTE 过程名 来调用过程 或者EXEC 过程名

在sqlplus中设置过程显示

SET serveroutput ON

示例一、定义一个简单的过程

CREATE OR REPLACE PROCEDURE bdqn_proc
AS
BEGIN
  dbms_output.put_line('学习使用存储过程!');
END;
执行
EXEC bdqn_proc;

示例二、定义一个简单的过程

CREATE OR REPLACE PROCEDURE FIND_EMP(PNO EMP.EMPNO%TYPE) AS
  V_ENAME EMP.ENAME%TYPE;
  V_JOB   EMP.JOB%TYPE;
  V_COUNT NUMBER;
BEGIN
  SELECT COUNT(EMPNO) INTO V_COUNT FROM EMP; --查询表中的记录总数
  IF V_COUNT = 0 THEN
    --判断是否有记录0表示没有
    RETURN; --结束
  END IF;
  SELECT ENAME, JOB INTO V_ENAME, V_JOB FROM EMP WHERE EMPNO = PNO; --查寻姓名和职位，并将值传给变量
  DBMS_OUTPUT.PUT_LINE('员工编号: ' || PNO || ' 员工姓名: ' || V_ENAME ||
                       ' 员工职位： ' || V_JOB);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('没有找到数据');
END FIND_EMP;
执行
EXECUTE FIND_EMP(&pnd)

示例三、使用过程增加部门

CREATE OR REPLACE PROCEDURE dpetadd_proc(

v_deptno               dept.deptno%TYPE,
v_dname                dept.dname%TYPE,
v_loc                  dept.loc%TYPE)

AS
 v_count                NUMBER;
BEGIN
  SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno;          --统计
  IF v_count>0 THEN
    raise_application_error(-20888,'增加失败,这个部门已经存在');
    ELSE
      INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
      dbms_output.put_line('新部门增加成功');
      COMMIT;
  END IF;
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM='||SQLERRM);
    ROLLBACK;
END;
执行
EXEC dpetadd_proc(10,'北大青鸟','北京')
EXEC dpetadd_proc(90,'北大青鸟','北京')
SELECT * FROM dept;

示例四、使用过程增加部门

CREATE OR REPLACE PROCEDURE dpetadd2_proc(

v_deptno               dept.deptno%TYPE:=&deptno,
v_dname                dept.dname%TYPE:='&dname',
v_loc                  dept.loc%TYPE:='&loc')

AS
 v_count                NUMBER;
BEGIN
  SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno;          --统计
  IF v_count>0 THEN
    raise_application_error(-20888,'增加失败,这个部门已经存在');
    ELSE
      INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
      dbms_output.put_line('新部门增加成功');
      COMMIT;
  END IF;
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM='||SQLERRM);
    ROLLBACK;
END;
执行
EXEC dpetadd2_proc(10,'北大青鸟','北京')
EXEC dpetadd2_proc(16,'北大青鸟','北京')

示例五、使用过程查询1981年入职的员工的工资和公司平均工资比较小于输出低工资，等于工资还行，高于输出高工资

CREATE OR REPLACE PROCEDURE SEARCH_PRO AS
  V_EMPNO    EMP.EMPNO%TYPE;
  V_NAME     EMP.ENAME%TYPE;
  V_JOB      EMP.JOB%TYPE;
  V_HIREDATE EMP.HIREDATE%TYPE;
  V_GRADE    SALGRADE.GRADE%TYPE;
  V_AVG      NUMBER;
  V_DNAME    DEPT.DNAME%TYPE;
  V_SAL      EMP.SAL%TYPE;
  CUR_S      SYS_REFCURSOR;
BEGIN
  SELECT AVG(SAL) INTO V_AVG FROM EMP;
  OPEN CUR_S FOR
    SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE, E.SAL, S.GRADE, D.DNAME
      FROM EMP E, SALGRADE S, DEPT D
     WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
       AND E.DEPTNO = D.DEPTNO(+);
  LOOP
    FETCH CUR_S
      INTO V_EMPNO, V_NAME, V_JOB, V_HIREDATE, V_SAL, V_GRADE, V_DNAME;
    EXIT WHEN CUR_S%NOTFOUND;
    IF V_SAL < V_AVG THEN
      DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' || V_EMPNO || ' 姓名:' ||
                           V_NAME || ' 职位:' || V_JOB || ' 入职日期:' ||
                           V_HIREDATE || ' 工资:||v_sal' || ' 工资等级' ||
                           V_GRADE || ' 部门名称:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工资太低了');
    ELSIF V_SAL = V_AVG THEN
      DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' || V_EMPNO || ' 姓名:' ||
                           V_NAME || ' 职位:' || V_JOB || ' 入职日期:' ||
                           V_HIREDATE || ' 工资:||v_sal' || ' 工资等级' ||
                           V_GRADE || ' 部门名称:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工资还行');
    ELSE
       DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '员工编号:' ||
                                              V_EMPNO || ' 姓名:' || V_NAME ||
                                              ' 职位:' || V_JOB || ' 入职日期:' ||
                                              V_HIREDATE || ' 工资:||v_sal' ||
                                              ' 工资等级' || V_GRADE ||
                                              ' 部门名称:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工资高了');
    END IF;
  END LOOP;
  CLOSE CUR_S;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
执行
EXEC SEARCH_PRO

参数模式

IN模式

示例一、定义过程使用IN默认可以不写

CREATE OR REPLACE PROCEDURE in_proc(
     p_a IN VARCHAR2,                     --明确定义IN参数模式
     p_b IN VARCHAR2                      --默认的参数模式为in
) 
AS
BEGIN
  dbms_output.put_line('执行in_proc()过程: p_a='||p_a);
  dbms_output.put_line('执行in_proc()过程: p_b='||p_b);
END;

执行
DECLARE
    v_a VARCHAR2(50):='Java开发实战经典';
    v_b VARCHAR2(50):='Oracle开发实战经典';
BEGIN
    in_proc(v_a,v_b);
END;
结果:
执行in_proc()过程: p_a=Java开发实战经典
执行in_proc()过程: p_b=Oracle开发实战经典

示例二、定义过程使用default定义参数默认值

CREATE OR REPLACE PROCEDURE in_proc(
     p_a IN VARCHAR2 DEFAULT '好好学习JAVA',                   --明确定义IN参数模式
     p_b IN VARCHAR2 DEFAULT '努力看Oracle'                   --默认的参数模式为in
) 
AS
BEGIN
  dbms_output.put_line('执行in_proc()过程: p_a='||p_a);
  dbms_output.put_line('执行in_proc()过程: p_b='||p_b);
END;

DECLARE
    v_a VARCHAR2(50):='Java开发实战经典';

BEGIN
    in_proc(v_a);
END;
结果:
执行in_proc()过程: p_a=Java开发实战经典
执行in_proc()过程: p_b=努力看Oracle
使用了第二个参数没有写，使用了默认值，如果有传递参数则使用传递的参数

OUT模式

示例一、定义过程使用OUT

CREATE OR REPLACE PROCEDURE out_proc(
     p_a OUT VARCHAR2,                     --明确定义out参数模式
     p_b OUT VARCHAR2)                       --明确定义out参数模式

AS
BEGIN
  dbms_output.put_line('执行out_proc()过程: p_a='||p_a);
  dbms_output.put_line('执行out_proc()过程: p_b='||p_b);
   p_a :='Java开发实战经典';               --将此值返回给实参
   p_b :='Oracle开发实战经典';
END;

执行
DECLARE
    v_a VARCHAR2(50):='好好学习';
    v_b VARCHAR2(50):='天天向上';
BEGIN
    out_proc(v_a,v_b);
    dbms_output.put_line('调用out_proc()过程: v_a='||v_a);
  dbms_output.put_line('调用out_proc()过程: v_b='||v_b);

END;

结果:
执行out_proc()过程: p_a=
执行out_proc()过程: p_b=
调用out_proc()过程: v_a=Java开发实战经典
调用out_proc()过程: v_b=Oracle开发实战经典

OUT模式时，传入的参数数是无用的，传入的内容不会传递 到过程中去

inout模式

示例一、定义过程使用INOUT

CREATE OR REPLACE PROCEDURE inout_proc(
     p_a IN OUT VARCHAR2,                     --明确定义out参数模式
     p_b IN OUT VARCHAR2)                       --明确定义out参数模式

AS
BEGIN
  dbms_output.put_line('执行inout_proc()过程: p_a='||p_a);
  dbms_output.put_line('执行inout_proc()过程: p_b='||p_b);
   p_a :='Java开发实战经典';               --将此值返回给实参
   p_b :='Oracle开发实战经典';
END;

执行
DECLARE
    v_a VARCHAR2(50):='好好学习';
    v_b VARCHAR2(50):='天天向上';
BEGIN
    inout_proc(v_a,v_b);
    dbms_output.put_line('调用inout_proc()过程: v_a='||v_a);
  dbms_output.put_line('调用inout_proc()过程: v_b='||v_b);

END;
结果:
执行inout_proc()过程: p_a=好好学习
执行inout_proc()过程: p_b=天天向上
调用inout_proc()过程: v_a=Java开发实战经典
调用inout_proc()过程: v_b=Oracle开发实战经典

调用inout_proc过程时，将2个变量v_a,v_b传入到了过程中，由于是INOUT模式，所过程可以接收到传递的变量内容，同时过程对变量做了修改也可以运回给实参

示例二、利用过程增加部门

CREATE OR REPLACE PROCEDURE DEPTINSER_PROC(
p_DNO    DEPT.DEPTNO%TYPE,
                                           p_DNAME  DEPT.DNAME%TYPE,
                                           p_LOC    DEPT.LOC%TYPE,
                                           P_RESULT OUT NUMBER --此为标记变量
                                           ) AS
  V_COUNT NUMBER; --保存count函数的结果
BEGIN
  SELECT COUNT(DEPTNO) INTO V_COUNT FROM DEPT WHERE DEPTNO = p_DNO;
  IF V_COUNT > 0 THEN
    P_RESULT := -1;
  ELSE
    INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (p_DNO, p_DNAME, p_LOC);
    P_RESULT := 0;
    COMMIT;
  END IF;
END;
调用
DECLARE
  V_RESULT NUMBER; --定义变量接收结果
BEGIN
  DEPTINSER_PROC(66, 'test', 'China', V_RESULT); --调用过程
  IF V_RESULT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('新部门增加成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('新部门增加失败');
  END IF;

END;

示例三、利用OUT传递游标使用过程是查询员工ID，姓名，职位，工资

CREATE OR REPLACE PROCEDURE search_pro(
       p_emp OUT SYS_REFCURSOR
)
AS
BEGIN
     OPEN p_emp FOR SELECT e.empno,e.ename,e.job,e.sal
                     FROM emp e;
END;

调用
DECLARE
  V_ID    EMP.EMPNO%TYPE;
  V_NAME  EMP.ENAME%TYPE;
  V_JOB   EMP.JOB%TYPE;
  V_SAL   EMP.SAL%TYPE;
  CUR_EMP SYS_REFCURSOR; --定义弱类型游标
BEGIN
  SEARCH_PRO(CUR_EMP);
  LOOP
    FETCH CUR_EMP
      INTO V_ID, V_NAME, V_JOB, V_SAL;
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || ' 员工编号:' || V_ID || ' 姓名:' ||
                         V_NAME || ' 职位:' || V_JOB || ' 工资:' || V_SAL);
  END LOOP;
  CLOSE CUR_EMP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

示例四 、定义过程，根据员工编号，查询员工姓名和工资，并输出参数输出

CREATE OR REPLACE PROCEDURE pro_emp_query(
                  v_eno     IN      emp.empno%TYPE,
                  v_name    OUT     emp.ename%TYPE,
                  v_sal     OUT     emp.sal%TYPE
)AS
BEGIN
  SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_eno;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('没有这个员工!');
END;
DECLARE
    v_eno       emp.empno%TYPE:=&empno;
    v_ename     emp.ename%TYPE;
    v_sal       emp.sal%TYPE;
BEGIN
  pro_emp_query(v_eno,v_ename,v_sal);
  dbms_output.put_line('编号:'||v_eno||' 姓名:'||v_ename||' 工资:'||v_sal);

END;

示例五 、交换值

CREATE OR REPLACE PROCEDURE swap(
       p1   IN OUT NUMBER,
       p2   IN OUT NUMBER
)AS
v_temp NUMBER;
BEGIN
      v_temp:=p1;
      p1:=p2;
      p2:=v_temp;
END;
--调用
DECLARE
    num1   NUMBER:=10;
    num2   NUMBER:=20;
BEGIN
   dbms_output.put_line(num1||'    '||num2);
    swap(num1,num2);
    dbms_output.put_line(num1||'    '||num2);
END;

自治事务

使用下列语句声明

PRAGMA AUTONOMOUS_TRANSACTION;

示例一、

CREATE OR REPLACE PROCEDURE dept_insert_proc
AS
       PRAGMA AUTONOMOUS_TRANSACTION;            --自治事务
BEGIN
      INSERT INTO dept(deptno,dname,loc)VALUES(80,'JAVA','北京');
      COMMIT;                                           --提交自治事务
END;
调用
DECLARE
BEGIN
   INSERT INTO dept(deptno,dname,loc)VALUES(60,'Oracl','深圳');
   dept_insert_proc();
   ROLLBACK;                                          --主事务回滚
END;

SELECT * FROM dept;

首先会向部门表中添加一条60部门的信息，此时调用过程，主程序会被挂起，到过程执行完

结果看出80部门已经添加成功，并没有受到ROLLBACK的影响

NOCOPY选项

PL/SQL中对于IN模式的传递的参数采用孝是引用的传递方式，所以其性能较高。

而对于OUT或者IN OUT模式传递参数采用的是数值传递，在传递时需要将实参数据复制一份给形参，但当传递的数据较大时（如集合，记录），那么这一复制过程就会变长

也会消耗大量的内在空间，

在定义参数时可以使用NOCOPY选项，将OUT或者IN OUT的值传递变为引用传递

语法：

参数名称 [参数模式out | in out] NOCOPY数据类型

--定义过程
create or replace procedure change_proc(
p_a in out number,p_nocopy in out nocopy NUMBER
)
 AS
begin
  p_a:=100;
  p_nocopy:=100;
  raise_application_error(-20008,'测试NOCOPY');
end change_proc;

调 用

--调用过程
DECLARE
 v_a NUMBER:=10;
 v_b NUMBER:=20;
BEGIN
  dbms_output.put_line('调用过程之前 v_a='||v_a||' v_b='||v_b);
  change_proc(v_a,v_b);

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line('调用过程之后v_a='||v_a||' v_b='||v_b);
END;

结果：

调用过程之前 v_a=10 v_b=20
ORA-20008: 测试NOCOPY
调用过程之后v_a=10 v_b=100

因为过程抛出了导常所以使用NOCOPy定义的参数正确将结果返回了，而没有使用NOCOPY定义的参数不能正常返回，

子程序权限:

过程授权
GRANT EXECUTE ON 过程名 TO 用户名 --将执行权授给用户，但用户不能再授权给其它用户
GRANT EXECUTE ON 过程名 TO 用户名 WITH GRANT OPTION; --将执行权授给用户，但用户可以再授权给其它用户