mark一个oracle学习网站 作者:Steven Feuerstein 在O’Reilly Media出版过10本Oracle PL/SQL方面的书,是一个oracleOracle ACE Director
这个是他的个人网站 里面有一个专栏是 learning PL/SQL
他在oracle magazine上发表的文章汇总地址
参考资料:
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 values | The INTO list contains fewer variables than the SELECT list. |
ORA-00913: too many values | The INTO list contains more variables than the SELECT list. |
ORA-06502: PL/SQL: numeric or value error | The 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;
9
10 l_employee_id employee_id_cur%ROWTYPE;
11 BEGIN
12 OPEN employee_id_cur;
13
14 LOOP
15 FETCH employee_id_cur INTO l_employee_id;
16 EXIT WHEN employee_id_cur%NOTFOUND;
17
18 assign_bonus (l_employee_id, l_total);
19 EXIT WHEN l_total <= 0;
20 END LOOP;
21
22 CLOSE employees_cur;
23 END;
下面详细分析以上语句块:
4-8 |
| |
10 | 基于查询返回的数据行声明一个record,在这个例子中,有仅仅一个单独的列值,因此你能够容易的声明 l_employee_id 作为employees.employee_id%TYPE类型,但是如果你使用显式cursor,最好声明一个 record 使用 %ROWTYPE,因为如果SELECT 列出的游标有可能在以后改变,这个变量也能跟着改变 | |
12 | 打开cursor,因为rows能够fetched 从查询中,注意 这一步在oracle database执行 SELECT-INTO语句中也执行 | |
14 | 开始一个loop循环到fetch 行 | |
15 | fetch 游标的下一行,存放行信息到这个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 NUMBER
IS
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,可以参见 这篇文章4400 3200
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 THEN
10 OPEN l_return FOR
11 SELECT last_name
12 FROM employees
13 ORDER BY employee_id;
14 WHEN 'DEPT'
15 THEN
16 OPEN l_return FOR
17 SELECT department_name
18 FROM departments
19 ORDER BY department_id;
20 END CASE;
21
22 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_REFCURSOR
IS
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;