在数据库中,游标是一个十分重要的概念,它提供了一种对从表中检索出的数据进行遍历的灵活手段,让我们可以对查询结果逐行处理,让我们可以对查询的结果集执行复杂的逻辑, 极大地提高了SQL的表达能力,使得在数据库中构建复杂应用更加轻松。Transwarp Inceptor是目前Hadoop平台上主流SQL引擎中唯一支持原生游标语义的产品,使得用户可以在分布式系统上无缝的使用游标来遍历大数据中的数据集。既具备分布式系统的强大处理能力,又具备游标的灵活用法。Transwarp Inceptor同时支持Oracle PL/SQL和DB2 SQL PL两大主流语法标准,其中包括了对游标的完整支持。今天,我们介绍Transwarp Inceptor PL/SQL中游标的使用。
1.1. 显式游标
显示游标可以在SELECT语句上创建,它使用的步骤为:
1. 在声明部分声明游标:
DECLARE CURSOR cursor_name IS select_statement;
2. 在执行部分或异常处理部分打开游标:
OPEN cursor_name;
3. 取数据:
FETCH cursor_name;
4. 关闭游标:
CLOSE cursor_name;
表 1. 显式游标的属性
游标的属性 | 返回值类型 | 意义 |
%ROWCOUNT | 整型 | 获得FETCH语句返回的数据行数 |
%FOUND | 布尔型 | 最近的FETCH语句返回一行数据则为TRUE,否则为FALSE |
%NOTFOUND | 布尔型 | 与%FOUND属性返回值相反 |
%ISOPEN | 布尔型 | 游标已经打开时值为TRUE,否则为FALSE |
下面我们用一些例子来具体介绍显式游标的使用方法。
不带参数的显式游标使用示例
例1:在LOOP中使用
DECLARE
transactions_type transactions%ROWTYPE;
CURSOR cur IS SELECT * FROM transactions;
BEGIN
OPEN cur; LOOP
FETCH cur INTO transactions_type; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(transactions_type.price);
END LOOP; CLOSE cur; EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Something unexpected happened.');
CLOSE cur;
END;
例2:在FOR LOOP中使用
DECLARE
transactions_type transactions%ROWTYPE;
CURSOR cur IS SELECT * FROM transactions;
BEGIN
FOR transactions_type IN cur
LOOP
DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' ||
transactions_type.trans_time || ' ' ||transactions_type.price);
END LOOP;
END;
带参数的显式游标使用示例
例1:在LOOP中使用
DECLARE
CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE
acc_num=tacc_num;
transactions_type transactions%rowtype;
BEGIN
OPEN cur('6513065'); LOOP
FETCH cur INTO transactions_type; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(transactions_type.trans_time);
END LOOP; CLOSE cur; EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Something unexpected happened.');
CLOSE cur;
END;
例2:在FOR LOOP中使用
DECLARE
transactions_type transactions%ROWTYPE;
CURSOR cur(tacc_num string) IS SELECT * FROM transactions WHERE
acc_num=tacc_num;
BEGIN
FOR transactions_type IN cur('6513065') LOOP
DBMS_OUTPUT.PUT_LINE( transactions_type.acc_num || ' ' ||
transactions_type.trans_time || ' ' ||transactions_type.price);
END LOOP;
END;
1.2. 隐式游标
隐式游标是没有明确的声明语句的游标类型。所有的DML操作都被Inceptor内部解析为一个游标名为SQL的隐式游标。
表 2. 隐式游标的属性
游标的属性 | 返回值类型 | 意义 |
SQL%ROWCOUNT | 整型 | 代表DML语句成功执行的数据行数 |
SQL%FOUND | 布尔型 | 值为TRUE代表插入、删除、更新或单行查询操作成功 |
SQL%NOTFOUND | 布尔型 | 与SQL%FOUND属性返回值相反 |
SQL%ISOPEN | 布尔型 | 永远为FALSE |
隐式游标使用示例
例:指向DML语句的隐式游标
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;
1.3. 游标变量
像游标一样,游标变量指向指定查询结果集当前行,但是相对游标,游标变量更加灵活因为其声明并不绑定指定查询。要定义一个游标变量,您需要先声明一个游标类型 REF CURSOR。 然后定义这个类型的游标变量。
游标变量的使用步骤如下:
1. 声明游标类型:
DECLARE TYPE type_name IS REF CURSOR;
2. 声明该类型的游标变量:
cursor_name type_name;
3. 在执行部分或异常处理部分打开:
OPEN cursor_name FOR sql_statement;
4. 取数据:
FETCH cursor_name;
5. 关闭游标:
CLOSE cursor_name;
游标变量可分为两类,强类型和弱类型的:
• 如果在声明游标类型REF CURSOR的时候指定了返回类型,那么REF CURSOR及其类型的游标 变量被称为 强类型。
• 如果在声明游标类型REF CURSOR的时候不指定返回类型,那么REF CURSOR及其类型的游标 变量被称为 弱类型。
游标变量使用示例
例1:强类型游标变量
DECLARE
TYPE cur_transaction IS REF CURSOR RETURN transactions%ROWTYPE;
sqlcur cur_transaction; v_trans_id STRING; v_trans_time STRING;
BEGIN
OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13; LOOP
FETCH sqlcur INTO v_trans_id,v_trans_time; EXIT WHEN sqlcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time);
END LOOP;
CLOSE sqlcur;
END;
例2:弱类型游标变量
DECLARE
TYPE cur_transaction IS REF CURSOR;
sqlcur cur_transaction; v_trans_id STRING; v_trans_time STRING;
BEGIN
OPEN sqlcur FOR SELECT trans_id,trans_time FROM transactions WHERE price=12.13; LOOP
FETCH sqlcur INTO v_trans_id,v_trans_time; EXIT WHEN sqlcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_trans_id||' ' ||v_trans_time);
END LOOP;
CLOSE sqlcur;
END;
例3:游标变量作为参数传递
CREATE OR REPLACE PACKAGE pkg_a IS
TYPE empcurtyp IS REF CURSOR RETURN user_info%ROWTYPE;
END pkg_a;
CREATE OR REPLACE PROCEDURE process_emp_cv(emp_cv in pkg_a.empcurtyp)
IS
user user_info%ROWTYPE;
BEGIN
LOOP
FETCH emp_cv INTO user; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Name = ' || user.name);
END LOOP;
END;
DECLARE
emp pkg_a.empcurtyp;
BEGIN
OPEN emp FOR SELECT * FROM user_info;
process_emp_cv(emp);
CLOSE emp;
END;