1、名词释义
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，是由流程控制和SQL语句书写的命名语句块。
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
2、基本语法
1 --创建语法 2 CREATE [OR REPLACE] PROCEDURE 存储过程名（PARAM1 IN TYPE，PARAM2 OUT TYPE） 3 AS --as和is任选一个，在这没有区别 4 变量1 类型（值范围）; 5 变量2 类型（值范围）; 6 BEGIN 7 SELECT COUNT(*) INTO 变量1 FROM 表A WHERE列名=PARAM1； 8 9 IF (判断条件) THEN 10 SELECT 列名 INTO 变量2 FROM 表A WHERE列名=PARAM1； 11 DBMS_OUTPUT.PUT_LINE(‘打印信息’); 12 ELSIF (判断条件) THEN 13 DBMS_OUTPUT.PUT_LINE(‘打印信息’); 14 ELSE 15 RAISE 异常名（NO_DATA_FOUND）; 16 END IF; 17 EXCEPTION 18 WHEN OTHERS THEN 19 ROLLBACK; 20 END; 21 22 --调用语法一 23 BEGIN 24 存储过程名(); 25 END; 26 27 --调用语法二 28 CALL 存储过程名(); 29 30 --删除语法 31 DROP PROCEDURE 存储过程名;
3、异常释义
ACCESS_INTO_NULL 未定义对象
CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置ELSE 时
COLLECTION_IS_NULL 集合元素未初始化
CURSER_ALREADY_OPEN 游标已经打开
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值
INVALID_CURSOR 在不合法的游标上进行操作
INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的
TOO_MANY_ROWS 执行 select into 时，结果集超过一行
ZERO_DIVIDE 除数为 0
SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL系统包
ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时
4、存储过程
4.1、无参存储过程
1 --1)无参存储过程语法 2 3 CREATE OR REPLACE PROCEDURE PRO_1_NOPAR 4 AS --声明 5 6 BEGIN --执行 7 --SELECT * FROM D_DEPT D; 8 DBMS_OUTPUT.PUT_LINE(‘无参存储过程‘); 9 EXCEPTION--异常 10 WHEN OTHERS THEN 11 ROLLBACK; 12 END; 13 14 --调用 15 BEGIN 16 PRO_1_NOPAR; 17 END;
4.2、带参数存储过程
IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调。
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。
1 --2）带参数存储过程含赋值方式 2 CREATE OR REPLACE PROCEDURE PRO_2_PAR 3 (VAL IN NUMBER, 4 SNAME OUT VARCHAR, 5 DT_CODE IN OUT VARCHAR) 6 AS 7 ICOUNT NUMBER; 8 BEGIN 9 SELECT COUNT(*) INTO ICOUNT FROM D_DEPT WHERE DT_ID>VAL; 10 IF ICOUNT=1 THEN 11 SNAME:= ‘MLB事业部1‘ ||VAL; 12 DT_CODE:= ‘MLB1‘||DT_CODE; 13 ELSE 14 SNAME:= ‘MLB事业部2‘||VAL; 15 DT_CODE:= ‘MLB2‘||DT_CODE; 16 END IF; 17 EXCEPTION 18 WHEN TOO_MANY_ROWS THEN 19 DBMS_OUTPUT.PUT_LINE(‘返回值多于1行‘); 20 WHEN OTHERS THEN 21 DBMS_OUTPUT.PUT_LINE(‘在PRO_1_PAR过程中出错！‘); 22 END; 23 24 --调用 25 declare 26 REVAL NUMBER; 27 RENAME varchar(40); 28 RECODE varchar(40); 29 begin --过程调用开始 30 REVAL:=20210421; 31 RENAME:=‘‘; 32 RECODE:=‘JUNIOR‘; 33 --指定值对应变量顺序可变 34 PRO_2_PAR(SNAME=>RENAME,VAL=>REVAL,DT_CODE=>RECODE); 35 DBMS_OUTPUT.PUT_LINE(RENAME||‘ ‘||RECODE); 36 END; --过程调用结束
4.3、带if的存储过程
1 --3)带if的存储过程 2 CREATE OR REPLACE PROCEDURE PRO_3_IF(A IN NUMBER, B IN NUMBER,RS OUT NUMBER) 3 AS 4 TEMP NUMBER; 5 BEGIN 6 TEMP:=A; 7 IF A < B THEN 8 TEMP := B; 9 END IF; 10 RS:=TEMP; 11 END; 12 13 --调用 14 DECLARE 15 RS NUMBER; 16 BEGIN 17 PRO_3_IF(10,20,RS); 18 DBMS_OUTPUT.PUT_LINE(‘RS=‘||RS); 19 END;
4.4、带if else的存储过程
1 CREATE OR REPLACE PROCEDURE PRO_4_IFELSE(A IN NUMBER, B IN NUMBER,RS OUT NUMBER) 2 AS 3 BEGIN 4 IF A > B THEN 5 RS := A; 6 ELSE 7 RS := B; 8 END IF; 9 END; 10 11 --调用 12 DECLARE 13 RS NUMBER; 14 BEGIN 15 PRO_4_IFELSE(30,20,RS); 16 DBMS_OUTPUT.PUT_LINE(‘RS=‘||RS); 17 END;
4.5、带elsif的存储过程
1 CREATE OR REPLACE PROCEDURE PRO_5_ELSEIF(Y IN NUMBER) 2 AS 3 BEGIN 4 IF Y=2020 THEN 5 DBMS_OUTPUT.PUT_LINE(‘2020年‘); 6 ELSIF Y =2021 THEN 7 DBMS_OUTPUT.PUT_LINE(‘2021年‘); 8 ELSE 9 DBMS_OUTPUT.PUT_LINE(‘未知年份‘); 10 END IF; 11 END; 12 13 --存储过程调用 14 BEGIN 15 PRO_5_ELSEIF(Y => 2021); 16 END;
4.6、带while循环的存储过程
1 --6)带while循环的存储过程 2 CREATE OR REPLACE PROCEDURE PRO_6_WHILE(I IN NUMBER) 3 AS 4 J NUMBER; 5 BEGIN 6 J := 1; 7 WHILE J <= I LOOP 8 DBMS_OUTPUT.PUT_LINE(‘J=‘||J); 9 J := J + 1; 10 END LOOP; 11 END; 12 13 --存储过程调用 14 BEGIN 15 PRO_6_WHILE(I=> 100); 16 END;
4.7、带select into的存储过程
在利用SELECT…INTO…语法时，必须先确保数据库中有该条记录，否则会报出"NO_DATA_FOUND"异常。
可先利用SELECT COUNT(*) FROM 查看数据库中是否存在该记录，存在则使用SELECT…INTO。
在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错。
1 --7)带SELECT INTO的存储过程 2 CREATE OR REPLACE PROCEDURE PRO_7_SELINTO(ID IN NUMBER) 3 AS 4 M VARCHAR(50); 5 G VARCHAR(50); 6 BEGIN 7 SELECT MONTH,ORG_OID INTO M,G FROM D_DEPT where DT_ID=ID; 8 DBMS_OUTPUT.PUT_LINE(‘M‘||M||‘G‘||G); 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 DBMS_OUTPUT.PUT_LINE(‘NO_DATA_FOUND异常‘); 12 END; 13 14 15 --存储过程调用 16 BEGIN 17 PRO_7_SELINTO(ID=> 21); 18 END;
4.8、带for的存储过程
1 --8）带for循环的存储过程 2 3 CREATE OR REPLACE PROCEDURE PRO_8_FOR 4 AS 5 BEGIN 6 FOR D IN (SELECT * FROM D_LESSON) LOOP 7 IF (D.LN_ID>10) THEN 8 DBMS_OUTPUT.PUT_LINE(D.LN_ID); 9 END IF; 10 END LOOP; 11 COMMIT; 12 END; 13 14 --调用方式一 15 BEGIN 16 PRO_8_FOR(); 17 END; 18 19 --调用方式二 20 CALL PRO_8_FOR(); 21 22 --删除储存过程 23 DROP PROCEDURE PRO_8_FOR;
4.9、带immediate的存储过程
1 --1、给动态SQL传值(USING 子句) 2 3 CREATE OR REPLACE PROCEDURE PRO_LOOP_BYDATE(V_STARTDATE IN DATE, 4 V_ENDDATE IN DATE) IS 5 V_DATE DATE; 6 V_ERR_MSG VARCHAR2(2000) := ‘-1‘; 7 8 BEGIN 9 V_DATE := V_STARTDATE; 10 WHILE V_DATE < V_ENDDATE LOOP 11 EXECUTE IMMEDIATE ‘BEGIN PRO_DW_PRO_D_LIST_V(:V_DATE,:V_ERR_MSG); END;‘ 12 --传入开始日期 返回错误信息 13 USING IN V_DATE, OUT V_ERR_MSG; --黓认为IN类型,其它类型必须显式指定 14 V_DATE := V_DATE + 1; 15 END LOOP; 16 17 END PRO_LOOP_BYDATE; 18 19 --调用 20 DECLARE 21 STARTDATE DATE; 22 ENDDATE DATE; 23 BEGIN 24 STARTDATE:=TO_DATE(20210423,‘YYYYMMDD‘); 25 ENDDATE:=TO_DATE(20210423,‘YYYYMMDD‘); 26 PRO_LOOP_BYDATE(V_STARTDATE => STARTDATE, 27 V_ENDDATE => ENDDATE); 28 END; 29 30 31 --2、传递并检索值.INTO子句用在USING子句前 32 33 CREATE OR REPLACE PROCEDURE PRO_INTO_USING 34 IS 35 LN_ID PLS_INTEGER := 41; 36 LN_NAME VARCHAR2(256); 37 LN_DESC VARCHAR2(256); 38 BEGIN 39 EXECUTE IMMEDIATE ‘SELECT LN_NAME, LN_DESC FROM D_LESSON WHERE LN_ID = :1‘ 40 INTO LN_NAME, LN_DESC --返回动态SQL中的LN_NAME, LN_DESC值 41 USING LN_ID ; -- 把参数LN_ID 传入到动态SQL 42 43 DBMS_OUTPUT.PUT_LINE(‘ID:‘||LN_ID||‘LN_NAME:‘||LN_NAME||‘LN_DESC‘||LN_DESC); 44 END; 45 46 --调用 47 CALL PRO_INTO_USING();
4.10 带游标的存储过程
4.10.1 游标语法与属性
1 --游标创建语法 2 DECLARE 3 ---声明CURSOR,创建和命名一个SQL工作区 4 CURSOR CURSOR_NAME IS 5 SELECT ENAME FROM EMP; 6 V_REALNAME VARCHAR2(20); 7 BEGIN 8 OPEN CURSOR_NAME;---打开CURSOR,执行SQL语句产生的结果集 9 FETCH CURSOR_NAME INTO V_REALNAME;--提取CURSOR,提取结果集中的记录 10 DBMS_OUTPUT.PUT_LINE(V_REALNAME); 11 CLOSE CURSOR_NAME;--关闭CURSOR 12 END; 13 14 --游标的属性： 15 %ISOPEN 是否打开 BOOLEAN类型 16 %ROWCOUNT 影响的行数 不是总行数，例如总数100，已经取了10条，那么这个数为10 17 %FOUND 是否找到 BOOLEAN类型 18 %NOTFOUND 是否没找到 BOOLEAN类型
4.10.2 无参游标存储过程
1 --使用无参CURSOR，查询所有员工的姓名和工资 2 CREATE OR REPLACE PROCEDURE PROC_10_CURSOR_NOPAR 3 AS 4 BEGIN 5 DECLARE 6 --定义游标 7 CURSOR CEMP IS SELECT ENAME,SAL FROM EMP; 8 --定义变量 9 VENAME EMP.ENAME%TYPE; 10 VSAL EMP.SAL%TYPE; 11 BEGIN 12 --打开游标，这时游标位于第一条记录之前 13 OPEN CEMP; 14 --循环 15 LOOP 16 --向下移动游标一次 17 FETCH CEMP INTO VENAME,VSAL; 18 --退出循环,当游标下移一次后，找不到记录时，则退出循环 19 EXIT WHEN CEMP%NOTFOUND; 20 --输出结果 21 DBMS_OUTPUT.PUT_LINE(VENAME||‘：‘||VSAL); 22 END LOOP; 23 --关闭游标 24 CLOSE CEMP; 25 END; 26 END; 27 28 --调用 29 BEGIN 30 PROC_10_CURSOR_NOPAR; 31 END;
4.10.3 带参游标存储过程
1 --使用带参CURSOR，查询10号部门的员工姓名和工资 2 CREATE OR REPLACE PROCEDURE PROC_10_CURSOR_PAR(DEPTNO NUMBER) 3 AS 4 BEGIN 5 DECLARE 6 CURSOR CEMP(PDEPTNO EMP.DEPTNO%TYPE) IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO=PDEPTNO; 7 PENAME EMP.ENAME%TYPE; 8 PSAL EMP.SAL%TYPE; 9 BEGIN 10 OPEN CEMP(DEPTNO); 11 LOOP 12 FETCH CEMP INTO PENAME,PSAL; 13 EXIT WHEN CEMP%NOTFOUND; 14 DBMS_OUTPUT.PUT_LINE(PENAME||‘的工资是‘||PSAL); 15 END LOOP; 16 CLOSE CEMP; 17 END; 18 END; 19 20 --调用 21 22 DECLARE 23 DEPTNO NUMBER(10):=&EMPNO; 24 BEGIN 25 PROC_10_CURSOR_PAR(DEPTNO); 26 END;