笔记081121 游标(cursor)和存储过程(procedure)

80 阅读 0 评论 53 点赞

我是靠谱客的博主无辜白猫，最近开发中收集的这篇文章主要介绍笔记081121 游标(cursor)和存储过程(procedure)，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
创建一个游标

SQL> edit;
已写入 file afiedt.buf

1 DECLARE
2 CURSOR mycur IS
3 SELECT * FROM STUDENT;
4 myrecode student%ROWTYPE;
5 BEGIN
6 OPEN mycur;
7 FETCH mycur INTO myrecode;
8 WHILE mycur%FOUND LOOP
9 DBMS_OUTPUT.PUT_LINE('the data is : '||myrecode.sno||myrecode.sname);
10 FETCH mycur INTO myrecode;
11 END LOOP;
12 CLOSE mycur;
13* END;
SQL> /

PL/SQL 过程已成功完成。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SET SERVEROUTPUT ON SIZE 10000

SQL> SET SERVEROUTPUT ON SIZE 10000
SQL> /
the data is : 01062401揍晔
the data is : 01062402黄费用
the data is : 01062403卡烂
the data is : 01062404住没
the data is : 01062405没猪
the data is : 01062406张故乡
the data is : 01062407刘晔
the data is : 01062408张晔
the data is : 01062101揍晔
the data is : 01062102黄费用
the data is : 01062103卡烂
the data is : 01062104住没
the data is : 01062105没猪
the data is : 01062106张故乡
the data is : 01062107刘晔
the data is : 01062108张晔

PL/SQL 过程已成功完成。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```
一个含有参数的游标

SQL> EDIT
已写入 file afiedt.buf

1 DECLARE
2 CURSOR mycur(ID varchar2) IS
3 SELECT SNAME FROM STUDENT WHERE SNO = ID;
4 myrecode STUDENT.SNAME%TYPE;
5 BEGIN
6 OPEN mycur('01062104');
7 LOOP
8 FETCH mycur INTO myrecode;
9 EXIT WHEN mycur%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE('SNAME IS '||myrecode);
11 END LOOP;
12 CLOSE mycur;
13* END;
SQL> /
SNAME IS 住没

PL/SQL 过程已成功完成。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

SQL> DECLARE
2 CURSOR CUR_PARA(NAME VARCHAR2) IS
3 SELECT SNO FROM STUDENT WHERE SNAME = NAME ;
4 BEGIN
5 FOR CUR IN CUR_PARA('张故乡') LOOP
6 DBMS_OUTPUT.PUT_LINE(CUR.SNO);
7 END LOOP;
8 END;
9 /
01062406
01062106

PL/SQL 过程已成功完成。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SQL> DECLARE
2 SNAME STUDENT.SNAME%TYPE;
3 CURSOR CUR_PARA(ID CHAR) IS
4 SELECT SNAME FROM STUDENT WHERE SNO = ID;
5 BEGIN
6 IF CUR_PARA%ISOPEN THEN
7 DBMS_OUTPUT.PUT_LINE('*******************CURSOR IS OPEN *************');
8 ELSE
9 OPEN CUR_PARA('01062105');
10 END IF;
11 FETCH CUR_PARA INTO SNAME ;
12 CLOSE CUR_PARA;
13 DBMS_OUTPUT.PUT_LINE(SNAME);
14 END ;
15 /
没猪

PL/SQL 过程已成功完成。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
ROWCOUNT

SQL> DECLARE
2 SNAME STUDENT.SNAME%TYPE;
3 CURSOR MYCUR IS
4 SELECT SNAME FROM STUDENT;
5 BEGIN
6 OPEN MYCUR;
7 LOOP
8 FETCH MYCUR INTO SNAME;
9 EXIT WHEN MYCUR%NOTFOUND OR MYCUR%NOTFOUND IS NULL;
10 DBMS_OUTPUT.PUT_LINE('ROWCOUNT: '||MYCUR%ROWCOUNT) ;
11 DBMS_OUTPUT.PUT_LINE(SNAME);
12 END LOOP;
13 CLOSE MYCUR;
14 END ;
15 /
ROWCOUNT: 1
揍晔
ROWCOUNT: 2
黄费用
ROWCOUNT: 3
卡烂
ROWCOUNT: 4
住没
ROWCOUNT: 5
没猪
ROWCOUNT: 6
张故乡
ROWCOUNT: 7
刘晔
ROWCOUNT: 8
张晔
ROWCOUNT: 9
揍晔
ROWCOUNT: 10
黄费用
ROWCOUNT: 11
卡烂
ROWCOUNT: 12
住没
ROWCOUNT: 13
没猪
ROWCOUNT: 14
张故乡
ROWCOUNT: 15
刘晔
ROWCOUNT: 16
张晔

PL/SQL 过程已成功完成。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```````
游标中使用UPDATE
SQL> EDIT
已写入 file afiedt.buf

1 DECLARE
2 CURSOR CUR IS
3 SELECT SNAME FROM STUDENT FOR UPDATE;
4 TEXT STUDENT.SNAME%TYPE;
5 BEGIN
6 OPEN CUR;
7 FETCH CUR INTO TEXT;
8 WHILE CUR%FOUND LOOP
9 UPDATE STUDENT SET SNAME = SNAME ||'T' WHERE CURRENT OF CUR;
10 FETCH CUR INTO TEXT;
11 END LOOP;
12 CLOSE CUR;
13* END;
SQL> /

PL/SQL 过程已成功完成。

SQL> SELECT * FROM STUDENT;

SNO              SNAME                SSEX     BIRTH               SDEPT
---------------- -------------------- -------- -------------- ----------
01062401         揍晔T                男       02-3月 -88              2
01062402         黄费用T              女       05-3月 -89              2
01062403         卡烂T                男       02-6月 -88              2
01062404         住没T                女       04-3月 -87              2
01062405         没猪T                男       02-7月 -86
01062406         张故乡T              女       03-3月 -84
01062407         刘晔T                男       02-8月 -84
01062408         张晔T                男       02-9月 -85
01062101         揍晔T                男       02-3月 -88              3
01062102         黄费用T              女       05-3月 -89              3
01062103         卡烂T                男       02-6月 -88              3

SNO              SNAME                SSEX     BIRTH               SDEPT
---------------- -------------------- -------- -------------- ----------
01062104         住没T                女       04-3月 -87              3
01062105         没猪T                男       02-7月 -86              3
01062106         张故乡T              女       03-3月 -84              3
01062107         刘晔T                男       02-8月 -84              3
01062108         张晔T                男       02-9月 -85

已选择16行。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~``
隐式游标

SQL> DECLARE
2 BEGIN
3 FOR CUR IN( SELECT SNAME FROM STUDENT) LOOP
4 DBMS_OUTPUT.PUT_LINE(CUR.SNAME);
5 END LOOP
6 ;
7 END ;
8 /
揍晔T
黄费用T
卡烂T
住没T
没猪T
张故乡T
刘晔T
张晔T
揍晔T
黄费用T
卡烂T
住没T
没猪T
张故乡T
刘晔T
张晔T

PL/SQL 过程已成功完成。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
创建一个存储过程

SQL> EDIT
已写入 file afiedt.buf

1 CREATE OR REPLACE PROCEDURE MYPROC(ID IN CHAR)
2 IS
3 NAME VARCHAR2(10);
4 BEGIN
5 SELECT SNAME INTO NAME FROM STUDENT WHERE SNO = ID;
6 DBMS_OUTPUT.PUT_LINE(NAME);
7* END ;
SQL> /

过程已创建。

SQL> EXECUTE MYPROC('01062108');
张晔T

PL/SQL 过程已成功完成。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SHOW ERRORS

SQL> edit
已写入 file afiedt.buf

1 CREATE OR REPLACE PROCEDURE MYPROC(ID IN CHAR(8))
2 IS
3 NAME VARCHAR2(10);
4 BEGIN
5 SELECT SNAME INTO NAME FROM STUDENT WHERE SNO = ID;
6 DBMS_OUTPUT.PUT_LINE(NAME);
7* END ;
SQL> /

警告: 创建的过程带有编译错误。

SQL> show errors;
PROCEDURE MYPROC 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/29     PLS-00103: 出现符号 "("在需要下列之一时：
         := ) , default
         varying character large
         符号 ":=" 被替换为 "(" 后继续。

SQL> show errors procedure myproc;
PROCEDURE MYPROC 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/29     PLS-00103: 出现符号 "("在需要下列之一时：
         := ) , default
         varying character large
         符号 ":=" 被替换为 "(" 后继续。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```
执行存储过程

SQL> EDIT
已写入 file afiedt.buf

1 DECLARE
2 TID CHAR(8);
3 BEGIN
4 TID:= '01062108';
5 MYPROC(TID);
6* END;
SQL> /
张晔T

PL/SQL 过程已成功完成。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```
有输入有输出的存储过程

SQL> CREATE OR REPLACE PROCEDURE MYPROC2(ID CHAR , NAME OUT VARCHAR2)
2 IS
3 BEGIN
4 SELECT SNAME INTO NAME FROM STUDENT WHERE SNO = ID;
5 END;
6 /

过程已创建。

SQL> SAVE E:/oracle/myproce2.sql
已创建 file E:/oracle/myproce2.sql
SQL> DECLARE
2 TNAME VARCHAR2(10);
3 BEGIN
4 MYPROC2('01062108',TNAME);
5 DBMS_OUTPUT.PUT_LINE(TNAME);
6 END;
7 /
张晔T

PL/SQL 过程已成功完成。