ORACLE存储过程--PROCEDURE

1、ORACLE 存储过程存储过程的基本信息 定义与作用： 存储过程（Stored Procedure），一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。基本ORACLE的过程定义 结构： DECLARE -声明变量等数据类型 BEGIN -主程序体，包含各种合法语句 EXCEPTION -异常处理 END；-主题程序结束分号必须例：打印1.100的数值 BEGIN FOR V_INDEX IN

2、1.100 LOOP DBMS_OUTPUT.PUT_LINE(V_INDEX); END LOOP; DBMS_OUTPUT.PUT_LINE(输出完毕!); END;错误号异常错误信息名称说明ORA-0001Dup_val_on_index违反了唯一性限制ORA-0051Timeout-on-resource在等待资源时发生超时ORA-0061Transaction-backed-out由于发生死锁事务被撤消ORA-1001Invalid-CURSOR试图使用一个无效的游标ORA-1012Not-logged-on没有连接到ORACLEORA-1017Login-denied无效的用户名/

3、口令ORA-1403No_data_foundSELECT INTO没有找到数据ORA-1422Too_many_rowsSELECT INTO 返回多行ORA-1476Zero-divide试图被零除ORA-1722Invalid-NUMBER转换一个数字失败ORA-6500Storage-error内存不够引发的内部错误ORA-6501Program-error内部错误ORA-6502Value-error转换或截断错误ORA-6504Rowtype-mismatch宿主游标变量与 PL/SQL变量有不兼容行类型ORA-6511CURSOR-already-OPEN试图打开一个已处于打开状

4、态的游标ORA-6530Access-INTO-null试图为null 对象的属性赋值ORA-6531Collection-is-null试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上ORA-6532Subscript-outside-limit对嵌套或varray索引得引用超出声明范围以外ORA-6533Subscript-beyond-count对嵌套或varray 索引得引用大于集合中元素的个数.ORACLE基本控制语句 判断结构：IF-THEN-END IF; /*判断if正确后执行THEN*/IF-THEN-ELSE

5、-END IF; /*判断if正确后执行THEN 否则执行ELSE*/IF-THEN-ELSIF -END IF; /*嵌套型判断*/例：IF-THEN-END IFDECLARE v1 NUMBER:=1; BEGIN IF v1=1 THEN DBMS_OUTPUT.PUT_LINE(V1的值为1); END IF; END;例：IF-THEN-ELSEEND IF DECLARE v1 NUMBER:=2; BEGIN IF v1=1 THEN DBMS_OUTPUT.PUT_LINE(V1的值为1); ELSE DBMS_OUTPUT.PUT_LINE(V1的值不为1); END IF

6、; END;例：IF-THEN-ELSIF-END IF DECLARE v1 NUMBER:=2;BEGIN IF v16 THEN DBMS_OUTPUT.PUT_LINE(V1的值为大于6); ELSIF v13 THEN DBMS_OUTPUT.PUT_LINE(V1的值大于3小于6); ELSE DBMS_OUTPUT.PUT_LINE(V1的值小于3); END IF;END;CASE CASE WHEN 情况1 THEN 程序执行块WHEN 情况2 THEN 程序执行块WHEN 情况3 THEN 程序执行块.ELSE 其他情况 END;例：CASE -查看员工表中的职员是CEO还

7、是职员 SELECT ENAME, (CASE WHEN JOB=PRESIDENT THEN CEO ELSE 职员 END) FROM EMP;循环结构LOOPLOOP -EXIT-END LOOP; /*用判断语句执行EXIT*/LOOP-EXIT WHEN-END LOOP; /* 当when为真是执行为假时跳出循环*/WHILE-LOOP-END LOOP; /*当while为真的时候执行为假的时候跳出*/FOR-IN -LOOP-END LOOP; /*用于已知循环次数*/例： LOOP -EXIT-END LOOP；DECLARE V_INDEX NUMBER:=1;BEGIN

8、LOOP IF V_INDEX10 THEN EXIT ; END IF; V_INDEX:=V_INDEX+1; DBMS_OUTPUT.PUT_LINE(V_INDEX); END LOOP; DBMS_OUTPUT.PUT_LINE(执行完毕！);END;例： LOOP-EXIT WHEN-END LOOP DECLARE V1_ NUMBER :=1; BEGIN LOOP EXIT WHEN V1_10; DBMS_OUTPUT.PUT_LINE(V1_); V1_:=V1_+1; END LOOP; END;例： WHILE-LOOP-END LOOP; DECLARE V1_ N

9、UMBER :=1; BEGIN WHILE V1_10 LOOP DBMS_OUTPUT.PUT_LINE(V1_); V1_:=V1_+1; END LOOP; END;例： FOR-IN -LOOP-END LOOP; BEGIN FOR INDEX_ IN 1.20 LOOP DBMS_OUTPUT.PUT_LINE(INDEX_); END LOOP; END;游标 概念： 游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。使用方法 1.声明游标：CUR

10、SOR IS SEELCT 2.打开游标： OPEN 3.提取游标中的值： FETCH INTO FETCHINTO PL/SQL 记录 4.关闭： CLOSE游标变量 基本属性：%ISOPEN：用于检查游标是否已经打开，已经打开返回true否则返回false%FOUND ：用查看游标是否查询到记录当查询到时返回true否则返回false% NOTFOUND：与%NOTFOUND相反%ROWCOUNT：返回游标中的记录数例：游标 DECLARE -声明与EMP记录相同的行变量 EMP_RECORD SCOTT.EMP%ROWTYPE; -声明游标并将emp中的记录读入到游标中 CURSOR E

11、MP_CURSOR IS SELECT * FROM EMP; BEGIN -打开游标 OPEN EMP_CURSOR; -将记录插入到emp_record中，只存一条 FETCH EMP_CURSOR INTO EMP_RECORD; -遍历游标 LOOP -游标为空时结束循环 EXIT WHEN EMP_CURSOR%NOTFOUND; -输出记录部分字段内容 DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME| |EMP_RECORD.JOB); -将游标下条记录读入到EMP_RECORD中 FETCH EMP_CURSOR INTO EMP_RECORD; EN

12、D LOOP; -关闭游标 CLOSE EMP_CURSOR; END;参数类型 DECLARE -定义记录类型 TYPE EMP_RECORD IS RECORD ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9) ); -定义游标引用 TYPE Pointer_CURSOR IS REF CURSOR RETURN EMP_RECORD; -定义引用类型游标 EMP_CURSOR Pointer_CURSOR; -定义记录类型变量 V_RECORD EMP_RECORD; BEGIN -打开游标并将查询数据加入游标 OPEN EMP

13、_CURSOR FOR SELECT EMPNO, ENAME, JOB FROM EMP; FETCH EMP_CURSOR INTO V_RECORD; -遍历游标内容 LOOP EXIT WHEN EMP_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(EMPNO |V_RECORD.EMPNO| ENAME |V_RECORD.ENAME| JOB |V_RECORD.JOB); FETCH EMP_CURSOR INTO V_RECORD; END LOOP; CLOSE EMP_CURSOR; END;其他遍历形式 DECLARE TYPE recode

14、 IS RECORD ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4) ); CURSOR EMPCUR IS SELECT EMPNO,ENAME,JOB,MGR FROM EMP; BEGIN FOR recode IN EMPCUR LOOP IF recode.JOB=CLERK THEN DBMS_OUTPUT.PUT_LINE(recode.JOB); ELSE DBMS_OUTPUT.PUT_LINE(ASDA); END IF; END LOOP; OPEN EMPCUR; END;使用游

15、标注意事项 1.使用前需用%is open 检查是否已经打开，只有返回值为true时才能使用，如果未打开则先打开（游标默认可多次打开当多次打开时系统会默认在下次打开前进行关闭操作） 2.在使用游标的过程中，每次都要用%found或%notfound 属性进行检查是否返回成功即是否还有要进行的操作。 3. 将游标中的值存到变量组中时，对应数据类型必须完全一致 4.游标使用完后必须关闭ORACLE存储过程 结构：CREATE OR REPLACE PROCEDURE 过程名(参数，方式 数据类型) IS/AS BEGIN 代码块；END 过程名；例：查询EMP表中工资大于输入数值的人数 CREAT

16、E OR REPLACE PROCEDURE COUNT_SAL (in_sal IN scott.emp.sal%type) AS out_count NUMBER; BEGIN SELECT COUNT(*) INTO out_count FROM EMP WHERE EMP.SALin_sal; DBMS_OUTPUT.PUT_LINE(工资比|in_sal|高的人数有|out_count|人); END COUNT_SAL;带返回参数的存储过程 CREATE OR REPLACE PROCEDURE COUNT_SAL( IN_SAL IN NUMBER, OUT_COUNT OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO OUT_COUNT FROM EMP WHERE SALIN_SAL; END COUNT_S