Oracle游标的使用及相关语法大全

来源：图艺博知识网

Oracle游标的使用及相关语法大全

当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取出数据,关闭。使用游标语法：

例：

SET SERVERIUTPUT ON

DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS select * from emp; BEGIN

OPEN c_emp; Loop

FETCH c_emp into r_emp;

EXIT WHEN C_EMP%NOTFOUND;

Dbms_output.put_line(\\’Salary of Employee\\’||r_emp.ename||\\’is’\\) End loop; Close c_emp; End;

%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标：

For record_name IN(corsor_name[(parameter[,parameter]…)] |(query_difinition) loop Statements End loop;

下面我们用for循环重写上面的例子：

在游标FOR循环中使用查询

在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。

游标中的子查询语法如下：

可以看出与SQL中的子查询有没什么区别。

游标中的更新和删除

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显示游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

语法：

在多表查询中，使用OF自居来锁定特定的表，如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下oracle将等待，直到数据行解锁。

在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：

WHERE {CURRENT OF cursor_name|search_condition} 实例:

DELCARE

CURSOR c1 IS

SELECT empno,salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER(10,2);

BEGIN

FOR r1 IN c1 LOOP IF r1.salary<500 THEN v_comm:=r1.salary*0.25; ELSEIF r1.salary<1000 THEN v_comm:=r1.salary*0.20; ELSEIF r1.salary<3000 THEN v_comm:=r1.salary*0.15;

ELSE v_comm:=r1.salary*0.12; END IF;

UPDATE emp

SET comm=v_comm WHERE CURRENT OF c1; END LOOP; END

=====================================================

oracle 隐式游标,显示游标,游标循环

动态SELECT语句和动态游标,异常处理,自定义异常

游标的概念:

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计

算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

隐式游标：

如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是： * 插入操作：INSERT。 * 更新操作：UPDATE。 * 删除操作：DELETE。

* 单行查询操作：SELECT ... INTO ...。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。 Sql代码：

隐式游标的属性返回值类型意义 1. 2. 3. 4.

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数

SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功 SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反 SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

【训练1】使用隐式游标的属性，判断对雇员工资的修改是否成功。步骤1：输入和运行以下程序： Sql代码：

1. 2. 3. 4. 5. 6. 7. 8.

SET SERVEROUTPUT ON BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！'); COMMIT; ELSE

DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');

9. END IF; 10. END;

运行结果为： Sql代码：

1. 修改雇员工资失败！ 2. PL/SQL 过程已成功完成。

步骤2：将雇员编号1234改为7788，重新执行以上程序：运行结果为： Sql代码：

1. 成功修改雇员工资！ 2. PL/SQL 过程已成功完成。

说明：本例中，通过SQL%FOUND属性判断修改是否成功，并给出相应信息。

显式游标：

游标的定义和操作

游标的使用分成以下4个步骤。 1．声明游标

在DECLEAR部分按以下格式声明游标：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句; 参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。 2．打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 3．提取数据在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...]; 或

FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

下面对这两种格式进行说明：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。定义记录变量的方法如下：变量名表名|游标名%ROWTYPE；

其中的表必须存在，游标名也必须先定义。 4．关闭游标 CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

以下是使用显式游标的一个简单练习。

【训练1】用游标提取emp表中7788雇员的名称和职务。 Sql代码：

1. SET SERVEROUTPUT ON 2. DECLARE

3. v_ename VARCHAR2(10); 4. v_job VARCHAR2(10); 5. CURSOR emp_cursor IS

6. SELECT ename,job FROM emp WHERE empno=7788; 7. BEGIN

8. OPEN emp_cursor;

9. FETCH emp_cursor INTO v_ename,v_job;

10. DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job); 11. CLOSE emp_cursor; 12. END;

执行结果为: Sql代码：

1. SCOTT,ANALYST

2. PL/SQL 过程已成功完成。

说明：该程序通过定义游标emp_cursor，提取并显示雇员7788的名称和职务。作为对以上例子的改进，在以下训练中采用了记录变量。

【训练2】用游标提取emp表中7788雇员的姓名、职务和工资。 Sql代码：

1. SET SERVEROUTPUT ON 2. DECLARE

3. CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno

=7788;

4. emp_record emp_cursor%ROWTYPE; 5. BEGIN

6. OPEN emp_cursor;

7. FETCH emp_cursor INTO emp_record;

8. DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||

','|| emp_record.sal); 9. CLOSE emp_cursor; 10. END;

执行结果为: Sql代码：

1. SCOTT,ANALYST,3000

2. PL/SQL 过程已成功完成。

说明：实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。

注意：可通过以下形式获得记录变量的内容：记录变量名.字段名。

【训练3】显示工资最高的前3名雇员的名称和工资。 Sql代码：

1. 2. 3. 4. 5.

SET SERVEROUTPUT ON DECLARE

V_ename VARCHAR2(10); V_sal NUMBER(5);

CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;

6. BEGIN

7. OPEN emp_cursor; 8. FOR I IN 1..3 LOOP

9. FETCH emp_cursor INTO v_ename,v_sal; 10. DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal); 11. END LOOP;

12. CLOSE emp_cursor; 13. END;

执行结果为: Sql代码：

1. 2. 3. 4. KING,5000

SCOTT,3000 FORD,3000

PL/SQL 过程已成功完成。

说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据。游标循环

【训练1】使用特殊的FOR循环形式显示全部雇员的编号和名称。 Sql代码

1. 2. 3. 4. 5. 6. 7. 8. 9.

SET SERVEROUTPUT ON DECLARE

CURSOR emp_cursor IS

SELECT empno, ename FROM emp; BEGIN

FOR Emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename); END LOOP; END;

执行结果为： Sql代码

1. 2. 3. 4. 5.

7369SMITH 7499ALLEN 7521WARD 7566JONES

PL/SQL 过程已成功完成。

说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。

【训练2】另一种形式的游标循环。 Sql代码

1. 2. 3. 4. 5. 6.

SET SERVEROUTPUT ON BEGIN

FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename) END LOOP; END;

执行结果为： Sql代码

1. 2. 3. 4.

SMITH ALLEN WARD JONES

说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。

显式游标属性

虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。 Sql代码：

游标的属性返回值类型意义 1. 2. 3. 4.

%ROWCOUNT 整型获得FETCH语句返回的数据行数

%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假 %NOTFOUND 布尔型与%FOUND属性返回值相反 %ISOPEN 布尔型游标已经打开时值为真，否则为假

可按照以下形式取得游标的属性：游标名%属性

要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。【训练1】使用游标的属性练习。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. V_ename VARCHAR2(10); 4. CURSOR emp_cursor IS 5. SELECT ename FROM emp; 6. BEGIN

7. OPEN emp_cursor;

8. IF emp_cursor%ISOPEN THEN 9. LOOP

10. FETCH emp_cursor INTO v_ename; 11. EXIT WHEN emp_cursor%NOTFOUND;

12. DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);

13. END LOOP; 14. ELSE

15. DBMS_OUTPUT.PUT_LINE('用户信息：游标没有打开！'); 16. END IF;

17. CLOSE emp_cursor; 18. END;

执行结果为： Sql代码

1. 2. 3. 4.

1-SMITH 2-ALLEN 3-WARD

PL/SQL 过程已成功完成。

说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH语句；使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行，当FETCH语句失败时说明数据已经取完，退出循环。

【练习1】去掉OPEN emp_cursor;语句，重新执行以上程序。

游标参数的传递

【训练1】带参数的游标。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. V_empno NUMBER(5); 4. V_ename VARCHAR2(10);

5. CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS 6. SELECT empno, ename FROM emp

7. WHERE deptno = p_deptno AND job = p_job; 8. BEGIN

9. OPEN emp_cursor(10, 'CLERK'); 10. LOOP

11. FETCH emp_cursor INTO v_empno,v_ename; 12. EXIT WHEN emp_cursor%NOTFOUND;

13. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); 14. END LOOP; 15. END;

执行结果为：

Sql代码

1. 7934,MILLER

2. PL/SQL 过程已成功完成。

说明：游标emp_cursor定义了两个参数：p_deptno代表部门编号，p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标，即部门为10、职务为CLERK，所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查询的内容。【练习1】修改Open语句的参数：部门号为20、职务为ANALYST，并重新执行。

也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值。对以上例子重新改动如下：

【训练2】通过变量传递参数给游标。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. v_empno NUMBER(5); 4. v_ename VARCHAR2(10); 5. v_deptno NUMBER(5); 6. v_job VARCHAR2(10);

7. CURSOR emp_cursor IS

8. SELECT empno, ename FROM emp

9. WHERE deptno = v_deptno AND job = v_job; 10. BEGIN

11. v_deptno:=10; 12. v_job:='CLERK'; 13. OPEN emp_cursor; 14. LOOP

15. FETCH emp_cursor INTO v_empno,v_ename; 16. EXIT WHEN emp_cursor%NOTFOUND; 17. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); 18. END LOOP; 19. END;

执行结果为： Sql代码：

1. 7934,MILLER

2. PL/SQL 过程已成功完成。

说明：该程序与前一程序实现相同的功能。

动态SELECT语句和动态游标的用法：

Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行，语法是：

execute immediate 查询语句字符串 into 变量1[,变量2...]; 以下是一个动态生成SELECT语句的例子。【训练1】动态SELECT查询。 Sql代码：

1. 2. 3. 4. 5. 6. 7. 8. 9.

SET SERVEROUTPUT ON DECLARE

str varchar2(100); v_ename varchar2(10); begin

str:='select ename from scott.emp where empno=7788'; execute immediate str into v_ename; dbms_output.put_line(v_ename); END;

执行结果为： Sql代码：

1. SCOTT

2. PL/SQL 过程已成功完成。

说明：SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。定义游标类型的语句如下： TYPE 游标类型名 REF CURSOR; 声明游标变量的语句如下：游标变量名游标类型名;

在可执行部分可以如下形式打开一个动态游标： OPEN 游标变量名 FOR 查询语句字符串;

【训练2】按名字中包含的字母顺序分组显示雇员信息。输入并运行以下程序： Sql代码：

1. declare

2. type cur_type is ref cursor; 3. cur cur_type;

4. 5. 6. 7. 8. 9.

rec scott.emp%rowtype; str varchar2(50); letter char:= 'A'; begin

loop

str:= 'select ename from emp where ename like ''%'||letter||'%''';

10. open cur for str;

11. dbms_output.put_line('包含字母'||letter||'的名字：'); 12. loop

13. fetch cur into rec.ename; 14. exit when cur%notfound;

15. dbms_output.put_line(rec.ename); 16. end loop;

17. exit when letter='Z';

18. letter:=chr(ascii(letter)+1); 19. end loop; 20. end;

运行结果为： Sql代码：

1. 包含字母A的名字： 2. ALLEN 3. WARD 4. MARTIN 5. BLAKE 6. CLARK 7. ADAMS 8. JAMES

9. 包含字母B的名字： 10. BLAKE

11. 包含字母C的名字： 12. CLARK 13. SCOTT

说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。

异常处理错误处理

错误处理部分位于程序的可执行部分之后，是由WHEN语句引导的多个分支构成的。错误处理的语法如下：

EXCEPTION

WHEN 错误1[OR 错误2] THEN 语句序列1；

WHEN 错误3[OR 错误4] THEN 语句序列2； WHEN OTHERS 语句序列n； END; 其中：

错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，参见下一节系统预定义的错误类型。语句序列就是不同分支的错误处理部分。

凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS部分进行统一处理，OTHENS必须是EXCEPTION部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类，可以通过使用预定义函数SQLCODE( )和SQLERRM( )来获得系统错误号和错误信息。

如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程序中。下面是由于查询编号错误而引起系统预定义异常的例子。【训练1】查询编号为1234的雇员名字。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. v_name VARCHAR2(10); 4. BEGIN

5. SELECT ename 6. INTO v_name 7. FROM emp

8. WHERE empno = 1234;

9. DBMS_OUTPUT.PUT_LINE('该雇员名字为：'|| v_name); 10. EXCEPTION

11. WHEN NO_DATA_FOUND THEN

12. DBMS_OUTPUT.PUT_LINE('编号错误，没有找到相应雇员！'); 13. WHEN OTHERS THEN

14. DBMS_OUTPUT.PUT_LINE('发生其他错误！'); 15. END;

执行结果为： Sql代码

1. 编号错误，没有找到相应雇员！ 2. PL/SQL 过程已成功完成。

说明：在以上查询中，因为编号为1234的雇员不存在，所以将发生类型为“NO_DATA_ FOUND”的异常。“NO_DATA_FOUND”是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，没有找到相应雇员!”。如果发生其他类型的错误，将执行OTHERS条件下的代码部分，显示“发生其他错误!”。

【训练2】由程序代码显示系统错误。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. v_temp NUMBER(5):=1; 4. BEGIN

5. v_temp:=v_temp/0; 6. EXCEPTION

7. WHEN OTHERS THEN

8. DBMS_OUTPUT.PUT_LINE('发生系统错误！');

9. DBMS_OUTPUT.PUT_LINE('错误代码：'|| SQLCODE( )); 10. DBMS_OUTPUT.PUT_LINE('错误信息：' ||SQLERRM( )); 11. END;

执行结果为： Sql代码

1. 2. 3. 4.

发生系统错误！

错误代码：?1476

错误信息：ORA-01476: 除数为 0 PL/SQL 过程已成功完成。

说明：程序运行中发生除零错误，由WHEN OTHERS捕捉到，执行用户自己的输出语句显示错误信息，然后正常结束。在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。预定义错误

Oracle的系统错误很多，但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION部分通过标准的错误名来进行判断，并进行异常处理。常见的系统预定义异常如下所示。 Sql代码

1. 2. 3. 4. 5.

错误名称错误代码错误含义

CURSOR_ALREADY_OPEN ORA_06511 试图打开已经打开的游标 INVALID_CURSOR ORA_01001 试图使用没有打开的游标

DUP_VAL_ON_INDEX ORA_00001 保存重复值到惟一索引约束的列中 ZERO_DIVIDE ORA_01476 发生除数为零的除法错误

6. INVALID_NUMBER ORA_01722 试图对无效字符进行数值转换 7. ROWTYPE_MISMATCH ORA_06504 主变量和游标的类型不兼容 8. VALUE_ERROR ORA_06502 转换、截断或算术运算发生错误

9. TOO_MANY_ROWS ORA_01422 SELECT„INTO„语句返回多于一行的数据 10. NO_DATA_FOUND ORA_01403 SELECT„INTO„语句没有数据返回 11. TIMEOUT_ON_RESOURCE ORA_00051 等待资源时发生超时错误 12. TRANSACTION_BACKED_OUT ORA_00060 由于死锁，提交失败 13. STORAGE_ERROR ORA_06500 发生内存错误

14. PROGRAM_ERROR ORA_06501 发生PL/SQL内部错误 15. NOT_LOGGED_ON ORA_01012 试图操作未连接的数据库

16. LOGIN_DENIED ORA_01017 在连接时提供了无效用户名或口令

比如，如果程序向表的主键列插入重复值，则将发生DUP_VAL_ON_INDEX错误。如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下：错误名 EXCEPTION;

定义后使用PRAGMA EXCEPTION_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联，就可以同系统预定义的错误一样使用了。语法如下： PRAGMA EXCEPTION_INIT(错误名，- 错误代码)；【训练1】定义新的系统错误类型。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. V_ENAME VARCHAR2(10);

4. NULL_INSERT_ERROR EXCEPTION;

5. PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400); 6. BEGIN

7. INSERT INTO EMP(EMPNO) VALUES(NULL); 8. EXCEPTION

9. WHEN NULL_INSERT_ERROR THEN

10. DBMS_OUTPUT.PUT_LINE('无法插入NULL值！'); 11. WHEN OTHERS THEN

12. DBMS_OUTPUT.PUT_LINE('发生其他系统错误！'); 13. END;

执行结果为： Sql代码

1. 无法插入NULL值！

2. PL/SQL 过程已成功完成。

说明：NULL_INSERT_ERROR是自定义异常，同系统错误1400相关联。

自定义异常

程序设计者可以利用引发异常的机制来进行程序设计，自己定义异常类型。可以在声明部分定义新的异常类型，定义的语法是：错误名 EXCEPTION;

用户定义的错误不能由系统来触发，必须由程序显式地触发，触发的语法是： RAISE 错误名；

RAISE也可以用来引发模拟系统错误，比如，RAISE ZERO_DIVIDE将引发模拟的除零错误。使用RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数，第一个是用户自定义的错误编号，第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在20 000和20 999之间选择。自定义异常处理错误的方式同前。

【训练1】插入新雇员，限定插入雇员的编号在7000～8000之间。 Java代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. new_no NUMBER(10); 4. new_excp1 EXCEPTION; 5. new_excp2 EXCEPTION; 6. BEGIN

7. new_no:=67;

8. INSERT INTO emp(empno,ename) 9. VALUES(new_no, '小郑'); 10. IF new_no<7000 THEN 11. RAISE new_excp1; 12. END IF;

13. IF new_no>8000 THEN 14. RAISE new_excp2; 15. END IF; 16. COMMIT; 17. EXCEPTION

18. WHEN new_excp1 THEN 19. ROLLBACK;

20. DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限！'); 21. WHEN new_excp2 THEN 22. ROLLBACK;

23. DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限！'); 24. END;

执行结果为：

雇员编号小于7000的下限！ PL/SQL 过程已成功完成。

说明：在此例中，自定义了两个异常：new_excp1和new_excp2，分别代表编号小于7000和编号大于8000的错误。在程序中通过判断编号大小，产生对应的异常，并在异常处理部

分回退插入操作，然后显示相应的错误信息。

【训练2】使用RAISE_APPLICATION_ERROR函数引发系统异常。 Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. New_no NUMBER(10); 4. BEGIN

5. New_no:=67;

6. INSERT INTO emp(empno,ename) 7. VALUES(new_no, 'JAMES'); 8. IF new_no<7000 THEN 9. ROLLBACK;

10. RAISE_APPLICATION_ERROR(-20001, '编号小于7000的下限！'); 11. END IF;

12. IF new_no>8000 THEN 13. ROLLBACK;

14. RAISE_APPLICATION_ERROR (-20002, '编号大于8000的下限！'); 15. END IF; 16. END;

执行结果为： Sql代码

1. 2. 3. 4. 5.

DECLARE *

ERROR 位于第 1 行:

ORA-20001: 编号小于7000的下限！ ORA-06512: 在line 9

说明：在本训练中，使用RAISE_APPLICATION_ERROR引发自定义异常，并以系统错误的方式进行显示。错误编号为20001和20002。

注意：同上一个训练比较，此种方法不需要事先定义异常，可直接引发。

可以参考下面的程序片断将出错信息记录到表中，其中，errors为记录错误信息的表，SQLCODE为发生异常的错误编号，SQLERRM为发生异常的错误信息。 DECLARE

v_error_code NUMBER;

v_error_message VARCHAR2(255); BEGIN ...

EXCEPTION ...

WHEN OTHERS THEN

v_error_code := SQLCODE ; v_error_message := SQLERRM ; INSERT INTO errors

VALUES(v_error_code, v_error_message); END;

【练习1】修改雇员的工资，通过引发异常控制修改范围在600～6000之间。阶段训练

【训练1】将雇员从一个表复制到另一个表。步骤1：创建一个结构同EMP表一样的新表EMP1：

CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2; 步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表： Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. v_empno NUMBER(5):=7788; 4. emp_rec emp%ROWTYPE; 5. BEGIN

6. SELECT * INTO emp_rec FROM emp WHERE empno=v_empno; 7. DELETE FROM emp WHERE empno=v_empno; 8. INSERT INTO emp1 VALUES emp_rec; 9. IF SQL%FOUND THEN 10. COMMIT;

11. DBMS_OUTPUT.PUT_LINE('雇员复制成功！'); 12. ELSE

13. ROLLBACK;

14. DBMS_OUTPUT.PUT_LINE('雇员复制失败！'); 15. END IF; 16. END;

执行结果为：雇员复制成功！

PL/SQL 过程已成功完成。步骤2：显示复制结果：

SELECT empno,ename,job FROM emp1; 执行结果为：

Sql代码

1. EMPNO ENAME JOB

2. ------------- -------------- ---------------- 3. 7788 SCOTT ANALYST

说明：emp_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员编号为7902，重新执行以上程序。【训练2】输出雇员工资，雇员工资用不同高度的*表示。输入并执行以下程序： Sql代码

1. 2. 3. 4.

SET SERVEROUTPUT ON BEGIN

FOR re IN (SELECT ename,sal FROM EMP) LOOP

DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));

5. END LOOP; 6. END;

输出结果为： Sql代码

1. SMITH ********

2. ALLEN **************** 3. WARD *************

4. JONES ****************************** 5. MARTIN *************

6. BLAKE ***************************** 7. CLARK ***************************** 8. SCOTT ******************************

9. KING ************************************************** 10. TURNER *************** 11. ADAMS *********** 12. JAMES **********

13. FORD ****************************** 14. MILLER ************* 15. 执行结果为：

16. PL/SQL 过程已成功完成。

说明：第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。

【训练3】编写程序，格式化输出部门信息。输入并执行如下程序： Sql代码：

1. SET SERVEROUTPUT ON

2. DECLARE

3. v_count number:=0;

4. CURSOR dept_cursor IS SELECT * FROM dept; 5. BEGIN

6. DBMS_OUTPUT.PUT_LINE('部门列表');

7. DBMS_OUTPUT.PUT_LINE('---------------------------------'); 8. FOR Dept_record IN dept_cursor LOOP

9. DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno); 10. DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname); 11. DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc); 12. DBMS_OUTPUT.PUT_LINE('---------------------------------'); 13. v_count:= v_count+1; 14. END LOOP;

15. DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！'); 16. END;

输出结果为： Sql代码

1. 部门列表

2. ------------------------------------ 3. 部门编号：10

4. 部门名称：ACCOUNTING 5. 所在城市：NEW YORK

6. ------------------------------------ 7. 部门编号：20

8. 部门名称：RESEARCH 9. 所在城市：DALLAS 10. ...

11. 共有4个部门！

12. PL/SQL 过程已成功完成。

说明：该程序中将字段内容垂直排列。V_count变量记录循环次数，即部门个数。【训练4】已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。

输入并执行如下程序： Sql代码

1. 2. 3. 4. 5.

SET SERVEROUTPUT ON DECLARE

v_deptno number(8); v_count number(3); v_sumsal number(6);

6. v_dname varchar2(15); 7. v_manager varchar2(15); 8. CURSOR list_cursor IS

9. SELECT deptno,count(*),sum(sal) FROM emp group by deptno; 10. BEGIN

11. OPEN list_cursor;

12. DBMS_OUTPUT.PUT_LINE('----------- 部门统计表 -----------'); 13. DBMS_OUTPUT.PUT_LINE('部门名称总人数总工资部门经理'); 14. FETCH list_cursor INTO v_deptno,v_count,v_sumsal; 15. WHILE list_cursor%found LOOP 16. SELECT dname INTO v_dname FROM dept 17. WHERE deptno=v_deptno;

18. SELECT ename INTO v_manager FROM emp 19. WHERE deptno=v_deptno and job='MANAGER';

20. DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8) 21. ||rpad(to_char(v_sumsal),9)||v_manager);

22. FETCH list_cursor INTO v_deptno,v_count,v_sumsal; 23. END LOOP;

24. DBMS_OUTPUT.PUT_LINE('--------------------------------------');

25. CLOSE list_cursor; 26. END;

输出结果为： Sql代码

1. 2. 3. 4. 5. 6. 7.

-------------------- 部门统计表 ----------------- 部门名称总人数总工资部门经理 ACCOUNTING 3 8750 CLARK RESEARCH 5 10875 JONES SALES 6 9400 BLAKE

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

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。

【训练5】为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。输入并调试以下程序： Sql代码

1. SET SERVEROUTPUT ON 2. DECLARE

3. V_NAME CHAR(10); 4. V_EMPNO NUMBER(5); 5. V_SAL NUMBER(8); 6. V_SAL1 NUMBER(8);

7. V_TOTAL NUMBER(8) := 800; --增加工资的总额 8. V_NUM NUMBER(5):=0; --增加工资的人数 9. CURSOR emp_cursor IS

10. SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC; 11. BEGIN

12. OPEN emp_cursor;

13. DBMS_OUTPUT.PUT_LINE('姓名原工资新工资'); 14. DBMS_OUTPUT.PUT_LINE('---------------------------'); 15. LOOP

16. FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL; 17. EXIT WHEN emp_cursor%NOTFOUND; 18. V_SAL1:= V_SAL*0.1;

19. IF V_TOTAL>V_SAL1 THEN

20. V_TOTAL := V_TOTAL - V_SAL1; 21. V_NUM:=V_NUM+1;

22. DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')|| 23. TO_CHAR(V_SAL+V_SAL1,'99999')); 24. UPDATE EMP SET SAL=SAL+V_SAL1 25. WHERE EMPNO=V_EMPNO; 26. ELSE

27. DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'9999

9'));

28. END IF; 29. END LOOP;

30. DBMS_OUTPUT.PUT_LINE('---------------------------'); 31. DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：

'||V_TOTAL);

32. CLOSE emp_cursor; 33. COMMIT; 34. END;

输出结果为： Sql代码

1. 2. 3. 4. 5. 6.

姓名原工资新工资

--------------------------------------------- SMITH 12 1418 JAMES 1531 1684 MARTIN 16 1830 MILLER 1730 1903

7. ALLEN 1760 1936 8. ADAMS 1771 1771 9. TURNER 1815 1815 10. WARD 1830 1830 11. BLAKE 2850 2850 12. CLARK 2850 2850 13. JONES 2975 2975 14. FORD 3000 3000 15. KING 5000 5000

16. ----------------------------------------------- 17. 增加工资人数：5 剩余工资：3 18. PL/SQL 过程已成功完成。

【练习1】按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。【练习2】为所有雇员增加工资，工资在1000以内的增加30%，工资在1000～2000之间的增加20%，2000以上的增加10%。

======================================================

oracle动态游标实例

1，带参数的游标

与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：

CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement; 定义参数的语法如下：

Parameter_name [IN] data_type[{:=|DEFAULT} value]

与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。

另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。在打开游标时给参数赋值，语法如下：

OPEN cursor_name[value[,value]....]; 参数值可以是文字或变量。例： DECALRE

CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp

WHERE deptno=p_dept ORDER BY ename r_dept DEPT%ROWTYPE; v_ename EMP.ENAME%TYPE; v_salary EMP.SALARY%TYPE; v_tot_salary EMP.SALARY%TYPE;

BEGIN

OPEN c_dept; LOOP

FETCH c_dept INTO r_dept; EXIT WHEN c_dept%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname); v_tot_salary:=0;

OPEN c_emp(r_dept.deptno); LOOP

FETCH c_emp INTO v_ename,v_salary; EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; CLOSE c_emp;

DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary); END LOOP; CLOSE c_dept; END;

2. 字符串变量

动态游标就是动态定义游标。 declare

type t_sor is ref cursor;

v_sor t_sor; --必需的，通过对象变量实现. ... begin .. end; /

给一个完整的例子:

create or replace procedure SP_CLEAR(V_TABLE IN STRING) IS

TYPE cur_type IS REF CURSOR; c_tab cur_type; v_str STRING(2000);

v_tab t_clear.wlbmc%TYPE; v_sql STRING(3000);

begin

--Get table from v_table --Reorder these tables v_str:=upper(v_table);

v_str:=REPLACE(v_str,',',''','''); v_str:=''''||v_str||'''';

v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC'; OPEN c_tab FOR v_sql; LOOP

FETCH c_tab INTO v_tab; EXIT WHEN c_tab%NOTFOUND;

v_sql:='delete from '||v_tab||''; EXECUTE IMMEDIATE v_sql; END LOOP; COMMIT;

--deal with exception EXCEPTION

WHEN OTHERS THEN ROLLBACK; RAISE; end SP_CLEAR; /

3.字符串变量例子2

给你一个完整的例子： DECLARE

TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec emp%ROWTYPE; sql_stmt VARCHAR2(200);

my_job VARCHAR2(15) := 'CLERK'; BEGIN

sql_stmt := 'SELECT * FROM emp WHERE job = :j'; OPEN emp_cv FOR sql_stmt USING my_job;

LOOP

FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- process record END LOOP; CLOSE emp_cv; END;

==========================================================================

oracle动态游标的简单实现方法

下面就是例子程序

--明细表打印予处理通用报表：

procedure mx_print_common(pd_id in mx_pd_syn.pd_id%type, p_pd_mxb_id IN mx_pd_mxb_syn.p_mxb_id%type, p_dept_no IN sc_mxk.dept_code%type, p1 sc_bz_syn.bz_code%type, p2 sc_cjjc_syn.cjjc_code%type, p3 sc_mxk.warehouse_num%type) is

sql2 varchar2(500); --存储查询语句 sql3 varchar2(500); --存储查询条件 str1 sc_print_syn.a%type; --存储车间进程

str2 sc_print_syn.b%type; --存储班组（工艺、工序）进程 s_ip sc_print_syn.ip%type; type cursor_type is ref cursor; c1 cursor_type;

type record_type is record( pbom_id sc_mxk.pbom_id%type );

r_c1 record_type; /*

注意上面红色的两行和蓝色的两行

红色的两行定义一个游标

蓝色的两行定义一个游标中将要返回的数据的数据结构 */

cursor c2(p_pbom_id sc_mxk.pbom_id%type) is

select a.dd_count,b.gx_name,c.bz_name,d.cjjc_name from sc_p_gx_syn a,sc_gx_syn b,sc_bz_syn c,sc_cjjc_syn d where pbom_id = p_pbom_id

and a.gx_code=b.gx_code(+) and b.dept_code=p_dept_no and a.bz_code=c.bz_code(+) and b.dept_code=p_dept_no and a.cjjc_code=d.cjjc_code(+) and b.dept_code=p_dept_no;

r_c2 c2%rowtype; BEGIN

s_ip :=sys_context('USERENV','IP_ADDRESS');

delete from sc_print_syn where ip=s_ip and p_id=pd_id; commit;

--下面开始构造查询语句

sql2:='select distinct a.pbom_id from sc_mxk a';

sql3:=' where a.p_id=' || pd_id || ' and a.dept_code= ''' || p_dept_no || '''';

if p_pd_mxb_id >0 then sql2:=sql3 || ',mxk c ';

sql3:=sql3 || ' and c.m_mxb_id= ' || p_pd_mxb_id || ' and a.mxb_id = c.mxb_id'; end if;

if p1 is not null then

sql2:=sql2 || ',sc_p_gx_syn b';

sql3:=sql3 || ' and a.pbom_id=b.pbom_id and b.bz_code = ''' || p1 || ''''; end if;

if p2 is not null then

sql2:=sql2 || ',sc_p_gx_syn b';

sql3:=sql3 || ' and a.pbom_id=b.pbom_id and b.cjjc_code = ''' || p2 || ''''; end if;

if p3 is not null then

sql3:=sql3 || ' and a.warehouse_num = ''' || p3 || ''''; end if;

sql2:=sql2 || sql3;

--打开动态游标，再往下就都一样了 open c1 for sql2; loop

fetch c1 into r_c1; exit when c1%notfound; str1:=''; str2:='';

--打开工序表进行处理 open c2(r_c1.pbom_id); loop fetch c2 into r_c2;

exit when c2%notfound; --没有记录退出 if r_c2.cjjc_name is not null then str1 :=str1 || to_char(r_c2.cjjc_name); end if;

if r_c2.bz_name is not null then

str2 := str2 || r_c2.bz_name || to_char(r_c2.dd_count); elsif r_c2.gx_name is not null then

str2 := str2 || to_char(r_c2.gx_name) || to_char(r_c2.dd_count); end if;

end loop; close c2;

insert into sc_print_syn(a,b,ip,p_id,r_id) values(str1,str2,s_ip,pd_id,r_c1.pbom_id); COMMIT; end loop; close c1; END mx_print_common;

当然，实现的方法一定很多，甚至可以用隐式游标。但是隐式游标中用动态查询语句也要费一些周折的。评论:

#re: oracle动态游标的简单实现方法 2008-05-27 09:18 | xzc

----定义

type cursor_type is ref cursor; c1 cursor_type; ----使用

--打开动态游标，再往下就都一样了 open c1 for sql2; loop

fetch c1 into r_c1;

exit when c1%notfound; 回复更多评论

#re: oracle动态游标的简单实现方法 [未登录] 2008-05-27 11:43 | xzc

TYPE cursor_type IS REF CURSOR; c1 cursor_type; --

OPEN c1 FOR lc_sql; LOOP FETCH c1

INTO lc_source_column_pk_value, lc_source_column_npk_value; EXIT WHEN c1%NOTFOUND; null; END LOOP; <>

CLOSE c1; 回复更多评论

#re: oracle动态游标的简单实现方法 [未登录] 2008-05-30 19:32 | xzc DECLARE

v_cursor NUMBER; v_stat NUMBER; v_row NUMBER;

v_id NUMBER; v_no VARCHAR(100); v_date DATE; v_sql VARCHAR(200); s_id NUMBER; s_date DATE; BEGIN s_id := 3000; s_date := SYSDATE;

v_sql := 'SELECT id,qan_no,sample_date FROM \"tblno\" WHERE id > :sid and sample_date < :sdate';

v_cursor := dbms_sql.open_cursor; --打开游标；

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句； dbms_sql.bind_variable(v_cursor, ':sid', s_id); --绑定输入参数； dbms_sql.bind_variable(v_cursor, ':sdate', s_date);

dbms_sql.define_column(v_cursor, 1, v_id); --定义列 dbms_sql.define_column(v_cursor, 2, v_no, 100); dbms_sql.define_column(v_cursor, 3, v_date);

v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。 LOOP

EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。

dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。

dbms_sql.column_value(v_cursor, 2, v_no); dbms_sql.column_value(v_cursor, 3, v_date);

dbms_output.put_line(v_id || ';' || v_no || ';' || v_date); END LOOP;

dbms_sql.close_cursor(v_cursor); --关闭游标。 END;

=============================================================================

oracle动态游标的简单实现方法

--明细表打印予处理通用报表：

procedure mx_print_common(pd_id in mx_pd_syn.pd_id%type,

p_pd_mxb_id IN mx_pd_mxb_syn.p_mxb_id%type, p_dept_no IN sc_mxk.dept_code%type, p1 sc_bz_syn.bz_code%type, p2 sc_cjjc_syn.cjjc_code%type, p3 sc_mxk.warehouse_num%type) is

sql2 varchar2(500); --存储查询语句 sql3 varchar2(500); --存储查询条件 str1 sc_print_syn.a%type; --存储车间进程

str2 sc_print_syn.b%type; --存储班组（工艺、工序）进程 s_ip sc_print_syn.ip%type; type cursor_type is ref cursor;

c1 cursor_type;type record_type is record( pbom_id sc_mxk.pbom_id%type );

r_c1 record_type;/*

注意上面红色的两行和蓝色的两行红色的两行定义一个游标

蓝色的两行定义一个游标中将要返回的数据的数据结构

cursor c2(p_pbom_id sc_mxk.pbom_id%type) is

select a.dd_count,b.gx_name,c.bz_name,d.cjjc_name

from sc_p_gx_syn a,sc_gx_syn b,sc_bz_syn c,sc_cjjc_syn d where pbom_id = p_pbom_id

and a.gx_code=b.gx_code(+) and b.dept_code=p_dept_no and a.bz_code=c.bz_code(+) and b.dept_code=p_dept_no

and a.cjjc_code=d.cjjc_code(+) and b.dept_code=p_dept_no;

r_c2 c2%rowtype; BEGIN

s_ip :=sys_context('USERENV','IP_ADDRESS');

delete from sc_print_syn where ip=s_ip and p_id=pd_id; commit;

--下面开始构造查询语句

sql2:='select distinct a.pbom_id from sc_mxk a';

sql3:=' where a.p_id=' || pd_id || ' and a.dept_code= ''' || p_dept_no || '''';

if p_pd_mxb_id >0 then sql2:=sql3 || ',mxk c ';

sql3:=sql3 || ' and c.m_mxb_id= ' || p_pd_mxb_id || ' and a.mxb_id = c.mxb_id'; end if;

if p1 is not null then

sql2:=sql2 || ',sc_p_gx_syn b';

sql3:=sql3 || ' and a.pbom_id=b.pbom_id and b.bz_code = ''' || p1 || ''''; end if;

if p2 is not null then

sql2:=sql2 || ',sc_p_gx_syn b';

sql3:=sql3 || ' and a.pbom_id=b.pbom_id and b.cjjc_code = ''' || p2 || ''''; end if;

if p3 is not null then

sql3:=sql3 || ' and a.warehouse_num = ''' || p3 || ''''; end if;

sql2:=sql2 || sql3;

--打开动态游标，再往下就都一样了 open c1 for sql2; loop

fetch c1 into r_c1;

exit when c1%notfound; str1:=''; str2:='';

--打开工序表进行处理

open c2(r_c1.pbom_id); loop fetch c2 into r_c2;

exit when c2%notfound; --没有记录退出 if r_c2.cjjc_name is not null then

str1 :=str1 || to_char(r_c2.cjjc_name); end if;

if r_c2.bz_name is not null then

str2 := str2 || r_c2.bz_name || to_char(r_c2.dd_count); elsif r_c2.gx_name is not null then

str2 := str2 || to_char(r_c2.gx_name) || to_char(r_c2.dd_count); end if;

end loop; close c2;

insert into sc_print_syn(a,b,ip,p_id,r_id)

values(str1,str2,s_ip,pd_id,r_c1.pbom_id); COMMIT; end loop; close c1;

END mx_print_common;

==================================================

oracle隐式游标和显式游标和REF游标

*隐式游标*

--//FOUND NOTFOUND ROWCOUNT ISOPEN

SET SERVEROUTPUT ON; BEGIN

UPDATE stuInfo SET stuName = 'AAA' WHERE stuID = '001'; IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('Update '||SQL%ROWCOUNT||' Lines'); END IF;

IF NOT SQL%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标关闭 '); END IF; END;

*显式游标*

--//声明打开提取关闭

--// only read

SET SERVEROUTPUT ON; DECLARE

stuName stuInfo.stuName%TYPE; stuAge stuInfo.stuAge%TYPE; stuRec stuInfo%ROWTYPE;

CURSOR stuRecCur IS SELECT * FROM stuInfo; BEGIN

OPEN stuRecCur; LOOP

FETCH stuRecCur INTO stuRec;--//先放到记录变量

EXIT WHEN stuRecCur%NOTFOUND; stuName := stuRec.stuName; stuAge := stuRec.stuAge;

DBMS_OUTPUT.PUT_LINE('学员 '||stuName||' , 年龄是'||stuAge); END LOOP;

CLOSE stuRecCur; COMMIT; END;

--//基于表连接查询的游标

SET SERVEROUTPUT ON; DECLARE

stuName stuInfo.stuName%TYPE; avgScore INTEGER; levelStr VARCHAR2(10);

CURSOR stuRecCur IS SELECT a.stuName,SUM(score)/4 avgScore FROM stuInfo a,stuScore b

WHERE a.stuID = b.stuID

GROUP BY a.stuID,a.stuName; BEGIN

OPEN stuRecCur; LOOP

FETCH stuRecCur INTO stuName,avgScore;--//直接按顺序提取 EXIT WHEN stuRecCur%NOTFOUND;

IF avgScore >= 80 THEN levelStr := '优秀'; ELSIF avgScore >= 70 THEN levelStr := '良好';

ELSIF avgScore >= 60 THEN levelStr := '及格'; ELSE

levelStr := '不及格';

END IF;

DBMS_OUTPUT.PUT_LINE('学员 '||stuName||' , 平均分数是'||avgScore||','||levelStr); END LOOP;

CLOSE stuRecCur; END;

--//可以更新数据的游标,游标循环

SET SERVEROUTPUT ON; DECLARE

CURSOR stuCur IS SELECT stuID,stuAge FROM stuInfo FOR UPDATE OF stuAge; stuAge stuInfo.stuAge%TYPE; stuCount INTEGER := 0; BEGIN

--游标FOR循环，简化游标操作.stuRec不需要声明

FOR stuRec IN stuCur LOOP

stuAge := stuRec.stuAge; IF stuAge < 18 THEN

UPDATE stuInfo SET stuAge = 18 WHERE CURRENT OF stuCur; stuCount := stuCount + 1; END IF; END LOOP; COMMIT;

DBMS_OUTPUT.PUT_LINE('更新了 '||stuCount||' 位学员信息'); EXCEPTION

WHEN others THEN ROLLBACK; END;

--//带参数的游标

SQL> SET SERVEROUTPUT ON; SQL>DECLARE

CURSOR stuCur(stuIdPara VARCHAR2) IS SELECT * FROM stuInfo WHERE stuName = stuIdPara;

stuRec stuInfo%ROWTYPE; stuName VARCHAR2(10); BEGIN

stuName := '&姓名';

OPEN stuCur(stuName); LOOP

FETCH stuCur INTO stuRec; IF stuCur%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('结束');

EXIT; ELSE

DBMS_OUTPUT.PUT_LINE('姓名: '||stuRec.stuName); DBMS_OUTPUT.PUT_LINE('年龄: '||stuRec.stuAge);

END IF; END LOOP; CLOSE stuCur;

EXCEPTION

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('错误'); END; 25 /

输入姓名的值: 'AAA'

原值 6: stuName := &姓名; 新值 6: stuName := 'AAA'; 姓名: AAA 年龄: 21 结束

PL/SQL 过程已成功完成。

SQL>

*REF游标*

SET SERVEROUTPUT ON;

VARIABLE msxAge NUMBER;--//全局变量 EXECUTE :msxAge := 20;

SET SERVEROUTPUT ON; DECLARE

sqlStr VARCHAR2(1000); stuName VARCHAR2(10);

TYPE stuCurType IS REF CURSOR; stuCur stuCurType;

selAct CHAR(1) := '&类型';

BEGIN

sqlStr := 'SELECT stuName FROM stuInfo WHERE 1=1 '; IF selAct = 'A' THEN

sqlStr := sqlStr || 'AND stuName=''AAA''' ; ELSE

sqlStr := sqlStr || 'AND stuName=''AAA'' AND stuAge = 30'; END IF;

OPEN stuCur FOR sqlStr;

FETCH stuCur INTO stuName;

DBMS_OUTPUT.PUT_LINE(stuName); CLOSE stuCur; END;

SQL> /

输入类型的值: A

原值 6: selAct CHAR(1) := &类型; 新值 6: selAct CHAR(1) := 'A'; AAA

PL/SQL 过程已成功完成。

PPT例子改进：

OPEN cur FOR 'select * from emp where sal>:1

order by sal desc' USING p_salary; =

OPEN cur FOR 'select * from emp where sal>'||p_salary ||'order by sal desc';

============================================

Oracle ref 游标

1.获得结果集 Sql代码

1. declare

2. type refcursor is ref cursor; --ref游标类型 3. infolist refcursor; --集合

4. customer bi_customer%rowtype; --行

5. customercode bi_customer.customercode%type;--字段 6. customername bi_customer.corporation%type; 7. begin

8. open infolist for

9. select bi.* from bi_customer cf; --全部 10. loop

11. fetch infolist

12. into customer;

13. exit when infolist%notfound; 14. dbms_output.put_line('客户编号为;：'||''||customer.customercode||', 地址为：'||customer.address ); 15. end loop;

16. close infolist; 17.end; 2 过程限定结果集,带返回记录数 Sql代码

1. create or replace package ord is--包 2.

3. -- Author : SIMON

4. -- Created : 2009-7-6 9:27:40 5. -- Purpose : 6.

7. type ref_type is ref cursor; 8.

9. procedure p_order(firstindex in number,--开始下标 10. lastindex in number,--结束下标 11. recordnum out number,--记录数 12. infolist out ref_type--结果集 13.);

14.end ord; 15.

16.create or replace package body ord is 17.

18. procedure p_order(firstindex in number, 19. lastindex in number, 20. recordnum out number, 21. infolist out ref_type) as 22. sql_tempstr varchar2(400); 23. sql_countstr varchar(500); 24. begin

25. sql_tempstr := 'select *

26. from (select rownum num, o.* from orders o) s 27. where 1>0';

28. if (firstindex is not null and lastindex is not null) then

29. sql_tempstr := sql_tempstr || 'and num between ' || firstindex ||

30. ' and

31. ' || lastindex; 32. end if;

33. sql_countstr := ' select count(*) from(' || (sql_tempstr) || ') ';

34. dbms_output.put_line(sql_tempstr); 35. dbms_output.put_line(sql_countstr); 36. open infolist for sql_tempstr; 37. Execute Immediate sql_countstr 38. into recordnum; 39. end p_order; 40.

41.end ord;

=====================================================================

参考游标(refcursor)的使用

我们知道在sql server中可以直接在存储过程中使用select * from 来产生记录集，但在oracle的存储过程里却不可以。怎么办呢？可以用参考游标来实现。 

参考游标（ref cursor）从Oracle 7.3开始引入，作用是允许在存储过程，函数，包中返回记录集。 

在Oracle 9i之前，参考游标以如下方式定义： 

首先定义一个参考游标： 

TYPE ref_type_name IS REF CURSOR  [RETURN {cursor_name%ROWTYPE  |ref_cursor_name%ROWTYPE  |record_name%TYPE  |record_type_name

 |table_name%ROWTYPE} ];  如： 

CREATE OR REPLACE PACKAGE Types AS  TYPE cursor_type IS REF CURSOR; END Types; / 

然后在存储过程中引用这个游标：



CREATE OR REPLACE

PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,  p_recordset OUT Types.cursor_type) AS BEGIN

 OPEN p_recordset FOR  SELECT ename,  empno,  deptno  FROM emp

 WHERE deptno = p_deptno  ORDER BY ename; END GetEmpRS; / 

Oracle 9i及以后版本，可省略第一步的定义，用sys_refcursor来代替： 

CREATE OR REPLACE

PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,  p_recordset OUT sys_refcursor) AS BEGIN

 OPEN p_recordset FOR  SELECT ename,  empno,  deptno  FROM emp

 WHERE deptno = p_deptno  ORDER BY ename; END GetEmpRS; / 

在程序中如何调用呢？ 

ADO中： 

Dim conn, cmd, rs

Set conn = Server.CreateObject(\"adodb.connection\") conn.Open \"DSN=TSH1;UID=scott;PWD=tiger\"

Set cmd = Server.CreateObject (\"ADODB.Command\") Set cmd.ActiveConnection = conn cmd.CommandText = \"GetEmpRS\"

cmd.CommandType = 4 'adCmdStoredProc Dim param1

Set param1 = cmd.CreateParameter (\"deptno\adInteger, adParamInput)

cmd.Parameters.Append param1 param1.Value = 30 Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF  -- Do something  rs.MoveNext Loop

rs.Close conn.Close

Set rs = nothing Set param1 = nothing Set cmd = nothing Set conn = nothing 

Java中： 

import java.sql.*; import oracle.jdbc.*;

public class TestResultSet {  public TestResultSet() {  try {

 DriverManager.registerDriver (new oracle.jdbc.OracleDriver());  Connection conn =

DriverManager.getConnection(\"jdbc:oracle:oci:@w2k1\\"scott\\"tiger\");  CallableStatement stmt = conn.prepareCall(\"BEGIN GetEmpRS(?, ?); END;\");

 stmt.setInt(1, 30); // DEPTNO

 stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR  stmt.execute();

 ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);  while (rs.next()) {

 System.out.println(rs.getString(\"ename\") + \":\" + rs.getString(\"empno\") + \":\" + rs.getString(\"deptno\"));  }

 rs.close();  rs = null;  stmt.close();  stmt = null;  conn.close();  conn = null;  }

 catch (SQLException e) {

 System.out.println(e.getLocalizedMessage());  }

 }

 public static void main (String[] args) {  new TestResultSet();  } }

=====================================================================

游标—Cursor

一：游标：

指向上下文区的句柄或指针，即为了处理SQL语句在内存中分配的一个区域。

二：显式游标：

是一种标准游标，用于处理返回多行数据的select语句。

处理步骤： 1：声明：

Cursor c_name is select_statement(不能包括into子句) 2：打开游标： Open c_name;

打开游标后将发生如下情况：

。检验邦定变量值，即使有新值，必须先关闭游标。。确定活动集。

。活动集指针指向第一行。

3：用游标检索值：fetch语句形式一：

Fetch c_name into list_of_variable(变量列表) 形式二：

Fetch c_name into pl/sql_record(记录) 4：关闭游标：

Close c_name;

三：游标属性：

被赋给游标名，并非返回一个类型，而是返回一个可使用的值。

%found %notfound %isopen %rowcound（数据行的个数） %bulk_rowcount(数组检索)

四：参数化游标：

绑定变量有时用参数来替代，即给游标定义参数，在open时传递参数。

五：隐式游标：—SQL游标

显式游标隐式游标

1．处理select语句 1. 被用于insert, update, delete,和select…into…等 2. 显式游标由程序打开 2. sql游标由pl/sql引擎打开或关闭，故没有相应的

或关闭 open, fetch…into…, close

3. 游标属性可以用在SQL游标上

六：游标检索循环：

1：简单循环：loop…end loop;

A：游标属性控制循环执行次数。

B：fetch 语句后立即是exit when 语句。 Open c_name; Loop

Fetch … into …;

Exit when …%...;（退出放在处理语句前或后是有区别） …(处理语句) End loop; 2：while循环： Open c_name; Fetch … into …;

While …%.... loop …..(语句块)； Fetch…into…; End loop;

说明：A：fetch出现二次，一次在循环前，一次在循环处理之后 B：也是显式游标

3：For循环：

For record_name in (select_statement) Loop

…statement; End loop

说明：A：record_name和游标都是隐式声明的 B：不出现游标名

C：record_name类似for语句中的计数器。 4：select for update游标：

修改由游标检索的数据行。此方法由二部分组成： A：游标声明中的for update子句

B：update或delete语句中的where current of 子句。

For update语法： Select … from …

For update [of column_reference][nowait | wait]

Where current of c_name(for update中声明的游标名)

说明：

1：update语句只更新在游标声明部分for update列出的列。

2：在没有声明for update的游标之上用where current是非法的。 3：for update 需要锁，commit释放锁。

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文