PL/SQL游标
来源:互联网 发布:c语言不等于0怎么表示 编辑:程序博客网 时间:2024/05/23 02:03
mark一个oracle学习网站 作者:Steven Feuerstein 在O’Reilly Media出版过10本Oracle PL/SQL方面的书,是一个oracleOracle ACE Director
这个是他的个人网站 里面有一个专栏是 learning PL/SQL
他在oracle magazine上发表的文章汇总地址
参考资料:
1:WIKI
2:游标讲解的很详细
3:介绍oracle游标
4:教程--cursor
5:如上教程游标
ORACLE PL/SQL语言是为了尽可能容易和有效的查询和改变数据库中表的内容而存在的。当然在这个过程中必须使用SQL语言,去访问表。
几乎每一次你都这样做,你常常使用游标去做这项工作。
1:游标的定义
游标是一个指针 指向oracle的私有的SQL区域,这个私有的SQL区域存储着查询(select)或者数据操纵语言(DML)语句像(INSERT
,UPDATE,DELETE,or MERGE)。游标可以管理DML语句的处理在数据库中,但是PL/SQL提供若干种方式定义和操纵游标去执行SELECT语句,这篇博文主要
是为了说明PL/SQL程序员在PL/SQL中如何执行SELECT语句。
通常使用以下方式:
a):使用SELECT - INTO 语句
b):Fetch 从一个显式游标中
c):使用cursor 中 FOR LOOP
d):使用 EXECUTE IMMEDIATE INTO 为动态查询
e):使用游标变量
a:SELECT-INTO
SELECT -INTO 提供了一个最快和最简单的方式去fetch一个单行记录从一个SELECT语句中,语法格式如下:
SELECT select_list INTO variable_list FROM remainder_of_query where ..;
remainder_of_query包含了表或者视图等其他的 where语句是查询的条件,在variable_list中元素的个数和类型必须和select_list中的相匹配。
如果这个SELECT 语句定义了操作一行的记录去fetch,对于以上ORACLE将出现TOO_MANY_ROWS异常,如果这个SELECT语句没有查找到数据,ORACLE数据库将出现
NO_DATA_FOUND异常。
以下是使用SELECT-INTO的一些例子:
例子1:Get the last name for a specific employee ID (the primary key in the employees table):
DECLARE l_last_name employees.last_name%TYPE;BEGIN SELECT last_name INTO l_last_name FROM employees WHERE employee_id = 138; DBMS_OUTPUT.put_line ( l_last_name);END;
以下是解释: 太简单就简单解释以下吧!
如果employments中employee_id只有一个为138的这个语句块能够正常的执行,最后打印出last_name的结果,
如果没有employee_id为138的 以上语句块将出现一个异常 NO_DATA_FOUND,如果这一列中有unique索引,以上语句块将永远不会
出现TOO_MANY_ROWS异常。
PS: not null + unique = primary key 就是相当于primary key的功能。
例子2: FETCH 一整行 从 employees表中:
DECLARE l_employee employees%ROWTYPE;BEGIN SELECT * INTO l_employee FROM employees WHERE employee_id = 138; DBMS_OUTPUT.put_line ( l_employee.last_name);END;如上所示: 如果 employee存在id为138的行, 这个last_name将显示,以上例子中我声明了一个记录 基于 employees表,然后fetch
所有的列进入这个record 。
例子3:从不同的表中Fetch列。
DECLARE l_last_name employees.last_name%TYPE; l_department_name departments.department_name%TYPE;BEGIN SELECT last_name, department_name INTO l_last_name, l_department_name FROM employees e, departments d WHERE e.department_id=d.department_id AND e.employee_id=138; DBMS_OUTPUT.put_line ( l_last_name || ' in ' || l_department_name);END;
在这个例子中,我需要超过一列值但是又不是所有列的值在两个表中的,因此,我声明了两个变量然后fetch这两列值到这些变量中。
如果这些查询出的列的值和INTO到的变量的类型值不匹配会如何呢?下面是一个错误的列表:
ORA-00947: not enough valuesThe INTO list contains fewer variables than the SELECT list.ORA-00913: too many valuesThe INTO list contains more variables than the SELECT list.ORA-06502: PL/SQL: numeric or value errorThe number of variables in the INTO and SELECT lists matches, but the datatypes do not match and Oracle Database was unable to convert implicitly from one type to the other.
b:从显式游标中Fetch 值(Fetching from Explicit Cursors)
SELECT INTO也是叫做隐式的查询,因为Oracle Database为SELECT语句隐式的打开了一个游标,fetch 一行,然后关闭游标,当完场以上
操作的时候,或者当一个异常出现的时候。
相对的,你能够通过显式的声明一个游标,执行open,fetch,和关闭操作手工的。
假设我需要写一个块fetchemployees以一个工资的升序,给它们一些奖金从总基金中通过调用 assign_bonus存储过程来完成,存储国臣管道头如下:
PROCEDURE assign_bonus ( employee_id_in IN employees.employee_id%TYPE, bonus_pool_io IN OUT INTEGER)
每次assign_bonus存储过程都会被调用,存储过程执行从total中减去奖金,然后返回减少的total,当奖金pool是耗尽的时候,停止fetching 然后
提交所有的changes
如下过程通过一个显式游标实现这个过程逻辑。
例子如下:
1 DECLARE 2 l_total INTEGER := 10000; 3 4 CURSOR employee_id_cur 5 IS 6 SELECT employee_id 7 FROM plch_employees 8 ORDER BY salary ASC; 910 l_employee_id employee_id_cur%ROWTYPE;11 BEGIN12 OPEN employee_id_cur;1314 LOOP15 FETCH employee_id_cur INTO l_employee_id;16 EXIT WHEN employee_id_cur%NOTFOUND;1718 assign_bonus (l_employee_id, l_total);19 EXIT WHEN l_total <= 0;20 END LOOP;2122 CLOSE employees_cur;23 END;
下面详细分析以上语句块:
4-8 显式游标的声明,从执行区域移动 查询的结果,使用游标keywords声明查询结果
10基于查询返回的数据行声明一个record,在这个例子中,有仅仅一个单独的列值,因此你能够容易的声明
l_employee_id 作为employees.employee_id%TYPE类型,但是如果你使用显式cursor,最好声明一个
record 使用 %ROWTYPE,因为如果SELECT 列出的游标有可能在以后改变,这个变量也能跟着改变12打开cursor,因为rows能够fetched 从查询中,注意 这一步在oracle database执行 SELECT-INTO语句中也执行14 开始一个loop循环到fetch 行 15fetch 游标的下一行,存放行信息到这个record中 ,注意 这一步oracle database执行 SELECT-INTO语句中也执行16如果FETCH 不能够找到一行,退出循环18调用 assign_bonus存储过程19如果所有的bonus(奖金)是用完了,退出循环22关闭游标, 注意 这一步Oralce数据库在SELECT-INTO中也执行下面是一些使用显式游标的注意事项:
1:如果查询不能够得到一些行,ORACLE数据库将不出现 NO_DATA_FOUND,作为代替,这个cursor_name%NOTFOUND属性将返回TRUE
2:查询超过一行,ORACLE DATABASE也不将出现TOO_MANY-ROWS
3:当你在包中声明一个游标(注意不是在包的子程序中)这个游标会打开,它将一直保持打开状态,直到显式的关闭或者session终止。
4:当cursor是声明在声明区(注意不是在一个包中),oracle database 将自动关闭它,当这个声明块结束的时候,但是即使这样,一个良好的编程习惯,
还是建议显式的关闭游标,如果游标是移动到包内,你将有必关闭游标手动的,如果是在局部,关闭游标也能让别的开发者能够很好的读懂你的代码。
c:使用游标循环
BEGIN FOR employee_rec IN ( SELECT * FROM employees WHERE department_id = 10) LOOP DBMS_OUTPUT.put_line ( employee_rec.last_name); END LOOP;END;
也可以使用显式的游标 FOR循环 如下:
DECLARE CURSOR employees_in_10_cur IS SELECT * FROM employees WHERE department_id = 10;BEGIN FOR employee_rec IN employees_in_10_cur LOOP DBMS_OUTPUT.put_line ( employee_rec.last_name); END LOOP;END;
关于游标for循环的使用:首先数据库打开游标,使用 %ROWTYPE 在游标中,fetch每一行进入record,然后当所有的行被fetched或者
d :Dynamic Queries with EXECUTE IMMEDIATE
CREATE OR REPLACE FUNCTION single_number_value ( table_in IN VARCHAR2, column_in IN VARCHAR2, where_in IN VARCHAR2) RETURN NUMBERIS l_return NUMBER;BEGIN EXECUTE IMMEDIATE 'SELECT ' || column_in || ' FROM ' || table_in || ' WHERE ' || where_in INTO l_return; RETURN l_return;END;
如上例子所示:使用了EXECUTE IMMEDIATE-INTO 代替 SELECT -INTO 语句,通过函数的传入的参数构建SELECT 语句,下面是调用函数的一个例子;
BEGIN DBMS_OUTPUT.put_line ( single_number_value ( 'employees', 'salary', 'employee_id=138'));END;
使用 SELECT-INTO,EXECUTE IMMEDIATE-INTO如果没有查询出数据将出现NO_DATA_FOUND,如果查询出多条数据(>1)将出现
CREATE OR REPLACE PROCEDURE show_number_values ( table_in IN VARCHAR2, column_in IN VARCHAR2, where_in IN VARCHAR2)IS TYPE values_t IS TABLE OF NUMBER; l_values values_t;BEGIN EXECUTE IMMEDIATE 'SELECT ' || column_in || ' FROM ' || table_in || ' WHERE ' || where_in BULK COLLECT INTO l_values; FOR indx IN 1 .. l_values.COUNT LOOP DBMS_OUTPUT.put_line (l_values (indx)); END LOOP;END;
BEGIN show_number_values ( 'employees', 'salary', 'department_id = 10 order by salary desc');END;
将会看到如下输出:
动态SQL的使用中需要注意SQL注入的问题,关于如何避免SQL注入,写更加安全的SQL,可以参见这篇文章44003200
1 CREATE OR REPLACE FUNCTION names_for ( 2 name_type_in IN VARCHAR2) 3 RETURN SYS_REFCURSOR 4 IS 5 l_return SYS_REFCURSOR; 6 BEGIN 7 CASE name_type_in 8 WHEN 'EMP' 9 THEN10 OPEN l_return FOR11 SELECT last_name12 FROM employees13 ORDER BY employee_id;14 WHEN 'DEPT'15 THEN16 OPEN l_return FOR17 SELECT department_name18 FROM departments19 ORDER BY department_id;20 END CASE;2122 RETURN l_return;23 END names_for;
描述如下 3定义返回的数据类型为SYS_REFCURSOR类型5声明一个游标变量(cursor variable)被用于函数的返回值7使用CASE语句根据name_type_in的值定义查询的内容10-13打开一个游标变量指向 employees表的查询结果 16-19打开一个游标变量指向departments表的查询结果
DECLARE l_names SYS_REFCURSOR; l_name VARCHAR2 (32767);BEGIN l_names := names_for ('DEPT'); LOOP FETCH l_names INTO l_name; EXIT WHEN l_names%NOTFOUND; DBMS_OUTPUT.put_line (l_name); END LOOP; CLOSE l_names;END;
如上例所示:关于查询的信息“隐藏”在函数的背后,仅仅通过函数头来调用即可,函数只需要调用合适的SELECT语句,打开游标变量
CREATE OR REPLACE FUNCTION numbers_from ( query_in IN VARCHAR2) RETURN SYS_REFCURSORIS l_return SYS_REFCURSOR;BEGIN OPEN l_return FOR query_in; RETURN l_return;END numbers_from;下面是一个调用以上函数的语句块如下:
DECLARE l_salaries SYS_REFCURSOR; l_salary NUMBER;BEGIN l_salaries := numbers_from ( 'select salary from employees where department_id = 10'); LOOP FETCH l_salaries INTO l_salary; EXIT WHEN l_salaries%NOTFOUND; DBMS_OUTPUT.put_line (l_salary); END LOOP; CLOSE l_salaries;END;
查询的时候要选择正确的方式:
- PL/SQL游标
- PL/SQL中的游标
- PL/SQL游标
- Oracle PL/SQL游标
- PL/SQL:游标
- PL/SQL游标总结
- 初学PL/SQL 游标
- PL/SQL游标描述
- PL/SQL --> 游标
- PL/SQL游标使用
- PL/SQL 游标变量
- pl/sql游标
- PL/SQL 游标变量
- PL/SQL游标
- PL/SQL游标使用
- PL/SQL游标使用
- oracle pl sql 游标
- PL/SQL游标
- 广度优先搜索
- 黑马程序员_java 其他对象
- ORACLE跨库查询
- spring流入静态bean spring在普通类中注入bean实例
- ORACLE 中判断是否为数字
- PL/SQL游标
- 黑马程序员_java 多线程(1)
- android 放烟花效果
- 黑马程序员_java 多线程(2)
- 黑马程序员_java异常体系
- 解决超链接添加 target="_blank"之后在WebView中不能打开
- 对==与equals的比较的理解
- 黑马程序员_java集合框架——List
- 嵌入式