DECLARE CURSOR <cursor_name> IS <select_statement>;
复制
在 PL/SQL 中,游标是指针,指向一段 DML 语句(INSERT/UPDATE/DELETE/MERGE)或者查询语句(SELECT)的上下文区域(context area)。
PL/SQL 中,游标分为显式游标(explicit cursor)和隐式游标(implicit cursor)。在声明游标时可以选择带有或不带有参数。
显式游标
在声明游标时可以选择带有参数或不带有参数。
游标的属性 | 返回值类型 | 意义 |
---|---|---|
%ROWCOUNT |
整型 |
获得 FETCH 语句返回的数据行数 |
%ROWTYPE |
记录类型 |
游标返回结果的记录类型 |
%FOUND |
布尔型 |
最近的 FETCH 语句返回一行数据则为 TRUE,否则为 FALSE |
%NOTFOUND |
布尔型 |
与 %FOUND 属性返回值相反 |
%ISOPEN |
布尔型 |
游标已经打开时值为 TRUE,否则为 FALSE |
使用步骤
显示游标可以在 SELECT 语句上创建,它使用的步骤为:
-
在声明部分声明游标:
-
<cursor_name>:定义游标 cursor 的名称
-
<select_statement>:游标的定义 select 语句。
-
-
在执行部分或异常处理部分打开游标:
OPEN <cursor_name>;
复制 -
取数据:
FETCH <cursor_name>;
复制 -
关闭游标:
CLOSE <cursor_name>;
复制
下面我们用一些例子来具体介绍显式游标的使用方法。
不带参数的显式游标
!set plsqlUseSlash true DECLARE -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。 transactions_type transactions%ROWTYPE -- 定义一个显式游标 cur,用来查询表 transactions 中的全部信息。 CURSOR cur IS SELECT * FROM transactions; BEGIN -- 打开游标 cur。 OPEN cur LOOP -- 把游标 cur 查询到的信息放进变量 transactions_type 中。 FETCH cur INTO transactions_type -- 如果游标 cur 没有查询到信息,就退出。 EXIT WHEN cur%NOTFOUND -- 输出变量 transactions_type 中的字段 price 的值。 DBMS_OUTPUT.PUT_LINE(transactions_type.price) END LOOP; CLOSE cur EXCEPTION -- 声明一个异常情况名 OTHERS。 WHEN OTHERS THEN -- 如果异常 OTHERS 发生,则返回的值。 DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!') CLOSE cur END; /
复制
输出结果为:
+---------+ | output | +---------+ | 10.31 | | 11.11 | | 7.02 | | 4.5 | | 10.03 | | 6.36 | | 7.52 | | 5.3 | | 12.21 | | 8.64 | | 18.38 | | 22.66 | | 6.12 | | 12.13 | | 5.25 | | 9.16 | | 9.81 | | 7.49 | | 68.43 | | 4.16 | +---------+
复制
!set plsqlUseSlash true DECLARE -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。 transactions_type transactions%ROWTYPE -- 定义一个显式游标 cur,用来查询表 transactions 中的全部信息。 CURSOR cur IS SELECT * FROM transactions; BEGIN -- for..loop 循环语句,对于每一个在游标里的变量值。 FOR transactions_type IN cur LOOP -- 输出变量 transactions_type 的字段名,账号,交易时间和价格的值。 DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' ||transactions_type.trans_time || ' ' ||transactions_type.price) END LOOP; END; /
复制
输出结果为:
+-------------------------------+ | output | +-------------------------------+ | 6670192 20140314145958 10.31 | | 3912384 20140205140521 11.11 | | 6513065 20140628133001 7.02 | | 3912384 20140430111523 4.5 | | 5224133 20140331115900 10.03 | | 5224133 20140801110003 6.36 | | 2755506 20140702113025 7.52 | | 0700735 20140611102830 5.3 | | 2394923 20141031135018 12.21 | | 6670192 20141130113905 8.64 | | 6513065 20140508094805 18.38 | | 0700735 20140315111111 22.66 | | 6513065 20140506133109 6.12 | | 6513065 20140105100520 12.13 | | 0700735 20140430143020 5.25 | | 6600641 20140228140005 9.16 | | 6513065 20140916105811 9.81 | | 6513065 20141225133500 7.49 | | 3912384 20140328102400 68.43 | | 3912384 20140214141519 4.16 | +-------------------------------+
复制
!set plsqlUseSlash true DECLARE -- 声明一个名为 transactions_cursor 的游标 CURSOR transactions_cursor IS SELECT * FROM transactions; -- 声明一个 transactions_cursor%rowtype 类型的记录变量 transactions_record transactions_cursor%ROWTYPE BEGIN OPEN transactions_cursor LOOP FETCH transactions_cursor INTO transactions_record EXIT WHEN transactions_cursor%NOTFOUND; dbms_output.put_line(transactions_record.acc_num || ' ' ||transactions_record.trans_time || ' ' ||transactions_record.price); END LOOP; CLOSE transactions_cursor; END; /
复制
查询结果如下,结果和上例一致:
+-------------------------------+ | output | +-------------------------------+ | 6670192 20140314145958 10.31 | | 3912384 20140205140521 11.11 | | 6513065 20140628133001 7.02 | | 3912384 20140430111523 4.5 | | 5224133 20140331115900 10.03 | | 5224133 20140801110003 6.36 | | 2755506 20140702113025 7.52 | | 0700735 20140611102830 5.3 | | 2394923 20141031135018 12.21 | | 6670192 20141130113905 8.64 | | 6513065 20140508094805 18.38 | | 0700735 20140315111111 22.66 | | 6513065 20140506133109 6.12 | | 6513065 20140105100520 12.13 | | 0700735 20140430143020 5.25 | | 6600641 20140228140005 9.16 | | 6513065 20140916105811 9.81 | | 6513065 20141225133500 7.49 | | 3912384 20140328102400 68.43 | | 3912384 20140214141519 4.16 | +-------------------------------+
复制
带参数的显式游标
!set plsqlUseSlash true DECLARE -- 定义一个带有参数的游标 cur 去查询表 transactions 中账号和参数的值相等的全部信息,其中参数名为 tacc_num,类型为 string。 CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE acc_num=tacc_num; -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。 transactions_type transactions%rowtype BEGIN -- 打开游标 cur,并赋参数值为 6513065。 OPEN cur('6513065') LOOP -- 把游标 cur 查询到的信息放进变量 transactions_type 中。 FETCH cur INTO transactions_type EXIT WHEN cur%NOTFOUND -- 输出变量 transactions_type 中的交易时间。 DBMS_OUTPUT.PUT_LINE(transactions_type.trans_time) END LOOP; CLOSE cur -- 如果有情况名为 OTHERS 的异常发生,则返回 Something unexpected happened!! 的信息。 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!') CLOSE cur END; /
复制
输出结果为:
+-----------------+ | output | +-----------------+ | 20140628133001 | | 20140508094805 | | 20140506133109 | | 20140105100520 | | 20140916105811 | | 20141225133500 | +-----------------+
复制
!set plsqlUseSlash true DECLARE -- 定义一个字段名和类型与表 transactions 均相同的记录变量 transactions_type。 transactions_type transactions%ROWTYPE -- 定义一个带有参数的游标 cur 去查询表 transactions 中账号和参数的值相等的全部信息,其中参数名为 tacc_num,类型为 string。 CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE acc_num=tacc_num; BEGIN -- for..loop 循环,对于每一个在游标 cur('6513065') 中的变量 transactions_type 的值。 FOR transactions_type IN cur('6513065') LOOP -- 输出表 transactions 中账号为 6513065 的账号,交易时间和价格。 DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' || ' ' || transactions_type.trans_time ||transactions_type.price) END LOOP; END; /
复制
返回结果如下:
+-------------------------------+ | output | +-------------------------------+ | 6513065 201406281330017.02 | | 6513065 2014050809480518.38 | | 6513065 201405061331096.12 | | 6513065 2014010510052012.13 | | 6513065 201409161058119.81 | | 6513065 201412251335007.49 | +-------------------------------+
复制
!set plsqlUseSlash true DECLARE -- 定义一个显式游标 cur,用来查询表 transactions 中的账号、交易时间以及价格信息。 CURSOR cur(tacc_num string) IS SELECT acc_num, trans_time, price FROM transactions WHERE acc_num = tacc_num; -- 定义一个字段名和类型与表 transactions.acc_num、transactions.trans_time、transactions.price 均相同的记录变量 transactions_type。 transactions_type cur%ROWTYPE; BEGIN -- Open Cursor,并传入参数值 6513065。 OPEN cur('6513065') LOOP -- 将 cur 的结果放入 transactions_type。 FETCH cur INTO transactions_type EXIT WHEN cur%NOTFOUND -- 输出变量 transactions_type 的字段名:账户、交易时间和价格。 DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' || transactions_type.trans_time || ' ' || transactions_type.price) END LOOP; CLOSE cur EXCEPTION WHEN OTHERS THEN -- 出现异常时的显示信息。 DBMS_OUTPUT.PUT_LINE('Something unexpected happened!!') CLOSE cur END; /
复制
输出结果上例一致:
+-------------------------------+ | output | +-------------------------------+ | 6513065 20140628133001 7.02 | | 6513065 20140508094805 18.38 | | 6513065 20140506133109 6.12 | | 6513065 20140105100520 12.13 | | 6513065 20140916105811 9.81 | | 6513065 20141225133500 7.49 | +-------------------------------+
复制
隐式游标
隐式游标是没有明确的声明语句的游标类型。所有的 DML 操作都被 ArgoDB 内部解析为一个游标名为 SQL 的隐式游标。
游标的属性 | 返回值类型 | 意义 |
---|---|---|
SQL%ROWCOUNT |
整型 |
代表 DML 语句成功执行的数据行数 |
SQL%FOUND |
布尔型 |
值为 TRUE 代表插入、删除、更新或单行查询操作成功 |
SQL%NOTFOUND |
布尔型 |
与 SQL%FOUND 属性返回值相反 |
SQL%ISOPEN |
布尔型 |
永远为 FALSE |
说明
-
对于 SELECT INTO 语句,如果执行成功,SQL%ROWCOUNT 的值为 1,如果没有成功,SQL%ROWCOUNT 的值为 0,同时产生一个异常 NO_DATA_FOUND。
-
在执行 DML 语句后,SQL%FOUND 的属性值将为 TRUE。
!set plsqlUseSlash true BEGIN UPDATE zara SET name='grace' WHERE age=20 IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('name is updated') ELSE DBMS_OUTPUT.PUT_LINE('name is not updated') END IF END; /
复制
输出结果为:
+----------------------+ | output | +----------------------+ | name is not updated | +----------------------+
复制
游标变量
-
游标变量指向指定查询结果集当前行,像游标一样。
-
但是相对游标,游标变量更加灵活因为其声明并不绑定指定查询。
-
要定义一个游标变量,您需要先声明一个游标类型 REF CURSOR,然后定义这个类型的游标变量。
游标变量可分为两类,强类型和弱类型的:
使用步骤
-
声明游标类型:
DECLARE TYPE <type_name> IS REF CURSOR;
复制-
<type_name>:游标类型。
-
-
声明该类型的游标变量:
cursor_name <type_name>;
复制 -
在执行部分或异常处理部分打开:
OPEN <cursor_name> FOR <sql_statement>;
复制-
<cursor_name>:游标名称。
-
<sql_statement>:定义游标的 select 语句。
-
-
取数据:
FETCH <cursor_name>;
复制 -
关闭游标:
CLOSE <cursor_name>;
复制
强类型游标变量
!set plsqlUseSlash true DECLARE -- 声明一个名为 cur_transaction 的游标类型,指定返回值的类型为 transactions%rowtype。 TYPE cur_transaction IS REF CURSOR RETURN transactions%ROWTYPE -- 定义一个名为 sqlcur 的游标变量。 sqlcur cur_transaction -- 依次定义两个类型为字符串的变量 v_trans_id,v_trans_time。 v_trans_id STRING v_trans_time STRING BEGIN -- 打开游标 sqlcur,去查询表 transactions中价格为 12.13 的交易号和交易时间。 OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13; LOOP -- 将游标查询的结果放进变量 v_trans_id,v_trans_time。 FETCH sqlcur INTO v_trans_id,v_trans_time EXIT WHEN sqlcur%NOTFOUND -- 输出变量 v_trans_id,v_trans_time 的值。 DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time) END LOOP; CLOSE sqlcur END; /
复制
输出结果为:
+---------------------------+ | output | +---------------------------+ | 943197522 20140105100520 | +---------------------------+
复制
弱类型游标变量
!set plsqlUseSlash true DECLARE -- 声明一个名为 cur_transaction 的游标类型,没有定义返回值的类型。 TYPE cur_transaction IS REF CURSOR -- 定义一个名为 sqlcur 的 cur_transaction 游标变量。 sqlcur cur_transaction -- 依次定义两个类型为字符串的变量 v_trans_id,v_trans_time。 v_trans_id STRING v_trans_time STRING BEGIN -- 打开游标 sqlcur,去查询表 transactions 中价格为 12.13 的交易号和交易时间。 OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13; LOOP -- 将游标查询的结果放进变量 v_trans_id,v_trans_time。 FETCH sqlcur INTO v_trans_id,v_trans_time EXIT WHEN sqlcur%NOTFOUND -- 输出变量 v_trans_id,v_trans_time 的值。 DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time) END LOOP; CLOSE sqlcur END; /
复制
输出结果为:
+---------------------------+ | output | +---------------------------+ | 943197522 20140105100520 | +---------------------------+
复制
游标变量作为参数传递
!set plsqlUseSlash true CREATE OR REPLACE PACKAGE pkg_a IS -- 在包 pkg_a 中定义一个游标类型 empcurtyp,和表 user_info 的行类型相同。 TYPE empcurtyp IS REF CURSOR RETURN user_info%ROWTYPE END pkg_a; / -- 定义一个过程 process_emp_cv,这个过程会一行行读取 user_info 中的记录的 name。 CREATE OR REPLACE PROCEDURE process_emp_cv(emp_cv in pkg_a.empcurtyp) IS user_p user_info%ROWTYPE BEGIN LOOP FETCH emp_cv INTO user_p EXIT WHEN emp_cv%NOTFOUND DBMS_OUTPUT.PUT_LINE('Name = ' || user_p.name) END LOOP; END; / DECLARE emp pkg_a.empcurtyp BEGIN -- 声明游标变量 emp,类型为在包 pkg_a 中定义的游标类型。 OPEN emp FOR SELECT * FROM user_info; -- 将游标变量 emp 座位参数传给过程 process_emp_cv。 process_emp_cv(emp) CLOSE emp END; /
复制
输出结果为:
+-------------+ | output | +-------------+ | Name = 魏** | | Name = 李** | | Name = 管** | | Name = 宁** | | Name = 华** | | Name = 李** | | Name = 祝** | | Name = 马** | | Name = 丘** | | Name = 潘** | +-------------+
复制