SELECT-INTO
用于从SELECT语句中，获取单行数据的最快最简单的方法。
语法：
SELECT select_list INTO variable_list FROM remainder_of_query;
- remainder_of_query中，包括了查询需要的table或view，where语句以及其他。
- variable_list的数量和类型要和select_list匹配。
- 如果查询出了多行数据，会抛出
TOO_MANY_ROWS异常。 - 如果没有查询结果，会抛出
NO_DATA_FOUND异常。
关于SELECT-INTO的一些异常：
| 异常代码 | 异常原因 |
|---|---|
| ORA-00947: not enough values | INTO变量列表少于SELECT变量列表 |
| ORA-00913: too many values | INTO变量列表多于SELECT变量列表 |
| ORA-06502: PL/SQL: numeric or value error | 变量数量匹配，但类型不匹配，且ORACLE无法隐式转换类型。 |
从显示游标中获取
SELECT-INTO也称为隐式查询，因为ORACLE数据库隐式地为查询语句打开游标，获取行，再关闭游标（或抛出异常）。也可以选择显示地声明游标，亲自执行打开、FETCH、关闭操作。
1 DECLARE
2 l_total INTEGER := 10000;
3 CURSOR employee_id_cur
4 IS
5 SELECT employee_id FROM plch_employees ORDER BY salary ASC;
6 l_employee_id employee_id_cur%ROWTYPE;
7 BEGIN
8 OPEN employee_id_cur;
9 LOOP
10 FETCH employee_id_cur INTO l_employee_id;
11 EXIT
12 WHEN employee_id_cur%NOTFOUND;
13 END LOOP;
14 CLOSE employee_id_cur;
15 END;
| 行 | 描述 |
|---|---|
| 3-5 | 显示游标声明。从可执行块（写SELECT-INTO语句的地方）移动查询语句，使用CURSOR关键字声明这个查询。 |
| 6 | 根据查询返回的数据行的类型声明记录。最好使用%ROWTYPE声明一个记录。即使游标的SELECT列表发生变化，变量也会随之改变。 |
| 8 | 打开游标，就可以从查询中获取数据行。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。 |
| 9 | 开始循环获取数据行。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。 |
| 10 | 获取游标的下一行，把这行的信息存入INTO子句中指定的记录中 |
| 11、12 | 如果不能获取到数据行，结束LOOP |
| 14 | 关闭游标。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。 |
注意：
- 如果查询结果为空，不会抛出NO_DATA_FOUND，要判断是否有数据，使用cursor_name%NOTFOUND属性，如果会空会返回TRUE。
- 查询可以返回多行数据，且不会抛出TOO_MANY_ROWS异常。
- 如果在包里声明了游标，并且游标是打开的，那么直到显式关闭游标或会话中止，游标都会保持打开状态。
- 游标在声明部分（不是包中）声明时，ORACLE数据库会在它所在的块终止时自动关闭游标。但最好能显式关闭游标。如果游标在包中，就必须CLOSE。And if it is local, then including a CLOSE statement will also show other developers and your manager that you are paying attention.（没看懂）
使用游标循环
游标循环：
BEGIN
FOR employee_rec IN (
SELECT *
FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;
显式声明的游标循环：
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;
使用EXECUTE IMMEDIATE进行动态查询
使用动态查询，就不用硬编码SQL，而是在运行时再完成SQL语句，然后解析、执行它。
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;
如示例代码，本来应该使用SELECT-INTO语句，这里使用的是EXECUTE IMMEDIATE-INTO。并且SELECT语句的查询参数、表名、WHERE子句都是从参数中获取的。
EXCUTE IMMEDIATE-INTO也会在查询结果为空时抛出NO_DATA_FOUND，在返回多行数据时抛出TOO_MANY_ROWS。
也可以使用EXECUTE IMMEDIATE来获取多行数据，这就需要用集合来存储，所以要用到BULK COLLECT。
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;
游标变量
游标变量：指向游标或结果集的变量，可以作为参数传递给存储过程或方法。
适用于：
- 将游标变量传递给调用这个程序单元的主机环境。结果集可以用来显示或用作其他处理。
- 在函数中构造一个结果集，并将游标变量返回给它。当你需要使用PL/SQL和SQL来构建结果集的时，尤其有用。
- 将游标变量传递给pipelined table function，一种功能强大且技术先进的技术。
游标变量可以与硬编码（静态）SQL和动态SQL一起使用。下面的代码中的names_for函数，它根据传递给函数的参数返回一个获取员工或部门名称的游标变量。
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 | 声明函数要返回的游标变量 |
| 7 | 使用CASE语句，根据name_type_in的值决定打开哪个查询 |
| 10-13 | 打开查询员工表的游标变量 |
| 16-19 | 打开查询部门表的游标变量 |
选择正确的查询方式
- 查询单行数据，使用SELECT-INTO或者EXECUTE IMMEDIATE-INTO。别用显示游标或游标FOR循环。
- 查询所有数据，使用游标FOR循环。如果在循环体中要执行一个或多个DML语句(INSERT/UPDATE/DELETE/MERGE)，那就用BULK COLLECT和FORALL。
- 如果需要用到BULK COLLECT的话，就使用显式游标，但是要限制每次获取返回的行数。
- 需要获取多行数据，但有可能会中途有条件地退出的话，就用显式游标。
- 如果你要获取的查询在运行时是变化的（不一定是动态的），尤其是你想要将结果传递给非PL/SQL主机环境的话，请使用游标变量。
- 如果你在写代码时无法完全确定SELECT语句的话，请使用EXECUTE IMMEDIATE来查询数据。
网友评论