首页 / ORACLE / Oracle进阶（一）存储过程

Oracle进阶（一）存储过程

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle进阶（一）存储过程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7375字，纯文字阅读大概需要11分钟。

内容图文

1、名词释义

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，是由流程控制和SQL语句书写的命名语句块。
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

2、基本语法

             1
            --
            创建语法
             2
            CREATE
            [
            OR REPLACE
            ]
            PROCEDURE 存储过程名（PARAM1 IN TYPE，PARAM2 OUT TYPE）
 3AS--as和is任选一个，在这没有区别 4变量1 类型（值范围）;
 5变量2 类型（值范围）;
 6BEGIN 7SELECTCOUNT(*) INTO 变量1 FROM 表A WHERE列名=PARAM1；
 8 9IF (判断条件) THEN10SELECT 列名 INTO 变量2 FROM 表A WHERE列名=PARAM1；
11       DBMS_OUTPUT.PUT_LINE(‘打印信息’);
12     ELSIF (判断条件) THEN13       DBMS_OUTPUT.PUT_LINE(‘打印信息’);
14ELSE15       RAISE 异常名（NO_DATA_FOUND）;
16ENDIF;
17EXCEPTION
18WHEN OTHERS THEN19ROLLBACK;
20END;
2122--调用语法一23BEGIN24  存储过程名();
25END;
2627--调用语法二28CALL 存储过程名();
2930--删除语法31DROPPROCEDURE 存储过程名;

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--异常10WHEN OTHERS THEN11ROLLBACK;
12END;
1314--调用15BEGIN16  PRO_1_NOPAR;
17END;

4.2、带参数存储过程

IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调。
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

             1
            --
            2）带参数存储过程含赋值方式
             2
            CREATE
            OR
            REPLACE
            PROCEDURE
             PRO_2_PAR   

             3     (VAL INNUMBER,   
 4      SNAME OUT VARCHAR,  
 5      DT_CODE IN OUT VARCHAR)  
 6AS 7     ICOUNT NUMBER;  
 8BEGIN 9SELECTCOUNT(*) INTO ICOUNT FROM D_DEPT WHERE DT_ID>VAL;  
10IF ICOUNT=1THEN11         SNAME:=‘MLB事业部1‘||VAL;
12                 DT_CODE:=‘MLB1‘||DT_CODE;
13ELSE14       SNAME:=‘MLB事业部2‘||VAL;
15                 DT_CODE:=‘MLB2‘||DT_CODE;
16ENDIF;  
17EXCEPTION  
18WHEN TOO_MANY_ROWS THEN19      DBMS_OUTPUT.PUT_LINE(‘返回值多于1行‘);  
20WHEN OTHERS THEN21      DBMS_OUTPUT.PUT_LINE(‘在PRO_1_PAR过程中出错！‘);  
22END;  
2324--调用25declare26      REVAL NUMBER;  
27      RENAME varchar(40);  
28      RECODE varchar(40);  
29begin--过程调用开始  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);  
36END;  --过程调用结束

4.3、带if的存储过程

             1
            --
            3)带if的存储过程
             2
            CREATE
            OR
            REPLACE
            PROCEDURE PRO_3_IF(A INNUMBER, B INNUMBER,RS OUT NUMBER)
 3AS 4TEMPNUMBER;
 5BEGIN 6TEMP:=A;
 7IF A < B THEN 8TEMP := B;
 9ENDIF;
10     RS:=TEMP;
11END;
1213--调用14DECLARE15     RS NUMBER;
16BEGIN17     PRO_3_IF(10,20,RS);
18     DBMS_OUTPUT.PUT_LINE(‘RS=‘||RS);
19END;

4.4、带if else的存储过程

             1
            CREATE
            OR
            REPLACE
            PROCEDURE PRO_4_IFELSE(A INNUMBER, B INNUMBER,RS OUT NUMBER)
 2AS 3BEGIN 4IF A > B THEN 5         RS := A;
 6ELSE 7         RS := B;
 8ENDIF;
 9END;
1011--调用12DECLARE13     RS NUMBER;
14BEGIN15     PRO_4_IFELSE(30,20,RS);
16     DBMS_OUTPUT.PUT_LINE(‘RS=‘||RS);
17END;

4.5、带elsif的存储过程

             1
            CREATE
            OR
            REPLACE
            PROCEDURE PRO_5_ELSEIF(Y INNUMBER)
 2AS 3BEGIN 4IF Y=2020THEN 5         DBMS_OUTPUT.PUT_LINE(‘2020年‘);
 6     ELSIF Y =2021THEN 7         DBMS_OUTPUT.PUT_LINE(‘2021年‘);
 8ELSE 9         DBMS_OUTPUT.PUT_LINE(‘未知年份‘);
10ENDIF;
11END;
1213--存储过程调用14BEGIN15   PRO_5_ELSEIF(Y =>2021);
16END;

 

4.6、带while循环的存储过程

             1
            --
            6)带while循环的存储过程
             2
            CREATE
            OR
            REPLACE
            PROCEDURE PRO_6_WHILE(I INNUMBER)
 3AS 4     J NUMBER;
 5BEGIN 6     J :=1;
 7WHILE J <= I LOOP
 8         DBMS_OUTPUT.PUT_LINE(‘J=‘||J);
 9         J := J +1;
10END LOOP;
11END;
1213--存储过程调用14BEGIN15   PRO_6_WHILE(I=>100);
16END;

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 INNUMBER)
 3AS 4 M VARCHAR(50);
 5 G VARCHAR(50);
 6BEGIN 7SELECTMONTH,ORG_OID INTO M,G FROM D_DEPT where DT_ID=ID;
 8    DBMS_OUTPUT.PUT_LINE(‘M‘||M||‘G‘||G);
 9  EXCEPTION
10WHEN NO_DATA_FOUND THEN11       DBMS_OUTPUT.PUT_LINE(‘NO_DATA_FOUND异常‘);
12END;
131415--存储过程调用16BEGIN17   PRO_7_SELINTO(ID=>21);
18END;

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
 7IF (D.LN_ID>10) THEN 8        DBMS_OUTPUT.PUT_LINE(D.LN_ID);
 9ENDIF;
10END LOOP;
11COMMIT;
12END;
1314--调用方式一15BEGIN16  PRO_8_FOR();
17END;
1819--调用方式二20CALL PRO_8_FOR();
2122--删除储存过程23DROPPROCEDURE 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 8BEGIN 9   V_DATE := V_STARTDATE;
10WHILE V_DATE < V_ENDDATE LOOP
11EXECUTE 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;
15END LOOP;
1617END PRO_LOOP_BYDATE;
1819--调用20DECLARE21STARTDATE DATE;
22ENDDATE DATE; 
23BEGIN24    STARTDATE:=TO_DATE(20210423,‘YYYYMMDD‘);  
25    ENDDATE:=TO_DATE(20210423,‘YYYYMMDD‘);  
26   PRO_LOOP_BYDATE(V_STARTDATE => STARTDATE,
27                       V_ENDDATE => ENDDATE);
28END;
293031--2、传递并检索值.INTO子句用在USING子句前3233CREATEORREPLACEPROCEDURE PRO_INTO_USING
34IS35    LN_ID    PLS_INTEGER :=41;   
36    LN_NAME      VARCHAR2(256);   
37    LN_DESC      VARCHAR2(256);   
38BEGIN39EXECUTE IMMEDIATE ‘SELECT LN_NAME, LN_DESC FROM D_LESSON WHERE LN_ID = :1‘40INTO LN_NAME, LN_DESC  --返回动态SQL中的LN_NAME, LN_DESC值41      USING LN_ID ;  -- 把参数LN_ID 传入到动态SQL4243           DBMS_OUTPUT.PUT_LINE(‘ID:‘||LN_ID||‘LN_NAME:‘||LN_NAME||‘LN_DESC‘||LN_DESC);
44END; 
4546--调用47 CALL PRO_INTO_USING();

4.10 带游标的存储过程

4.10.1 游标语法与属性

             1
            --
            游标创建语法
             2
            DECLARE
             3
            --
            -声明CURSOR,创建和命名一个SQL工作区
             4
            CURSOR CURSOR_NAME IS 5SELECT ENAME FROM EMP;
 6     V_REALNAME VARCHAR2(20);
 7BEGIN 8OPEN CURSOR_NAME;---打开CURSOR,执行SQL语句产生的结果集 9FETCH CURSOR_NAME INTO V_REALNAME;--提取CURSOR,提取结果集中的记录10    DBMS_OUTPUT.PUT_LINE(V_REALNAME);
11CLOSE CURSOR_NAME;--关闭CURSOR12END;
1314--游标的属性：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 ISSELECT ENAME,SAL FROM EMP;
 8--定义变量 9     VENAME EMP.ENAME%TYPE;
10     VSAL   EMP.SAL%TYPE;
11BEGIN12--打开游标，这时游标位于第一条记录之前13OPEN CEMP;
14--循环15    LOOP
16--向下移动游标一次17FETCH CEMP INTO VENAME,VSAL;
18--退出循环,当游标下移一次后，找不到记录时，则退出循环19EXITWHEN CEMP%NOTFOUND;
20--输出结果21        DBMS_OUTPUT.PUT_LINE(VENAME||‘：‘||VSAL);
22END LOOP;
23--关闭游标24CLOSE CEMP;
25END;
26END;
2728--调用29BEGIN30  PROC_10_CURSOR_NOPAR;
31END;

4.10.3 带参游标存储过程

             1
            --
            使用带参CURSOR，查询10号部门的员工姓名和工资
             2
            CREATE
            OR
            REPLACE
            PROCEDURE PROC_10_CURSOR_PAR(DEPTNO NUMBER)
 3AS 4BEGIN 5DECLARE 6CURSOR CEMP(PDEPTNO EMP.DEPTNO%TYPE) ISSELECT ENAME,SAL FROM EMP WHERE DEPTNO=PDEPTNO;
 7     PENAME EMP.ENAME%TYPE;
 8     PSAL EMP.SAL%TYPE;
 9BEGIN10OPEN CEMP(DEPTNO);
11    LOOP
12FETCH CEMP INTO PENAME,PSAL;    
13EXITWHEN CEMP%NOTFOUND;
14         DBMS_OUTPUT.PUT_LINE(PENAME||‘的工资是‘||PSAL);
15END LOOP;
16CLOSE CEMP;
17END;
18END;
1920--调用2122DECLARE23 DEPTNO NUMBER(10):=&EMPNO;   
24BEGIN25PROC_10_CURSOR_PAR(DEPTNO);   
26END;

 

原文：https://www.cnblogs.com/yiershanren/p/oracle_advanced.html

内容总结

以上是互联网集市为您收集整理的Oracle进阶（一）存储过程全部内容，希望文章能够帮你解决Oracle进阶（一）存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1197464.html

来源：【匿名】