碰到的问题,有一个员工表,存储着员工的各种信息。
我要写一个过程,传进去员工的名字,打印该员工的信息。
create or replace procedure getInfo(name emp.ename%type) as
-- ei 相当于empInfomation
ei emp%rowtype;
begin
select * into ei from emp where emp.ename=name;
dbms_output.put_line(ei.empno||','||ei.ename||','||ei.job||','||ei.mgr||','||ei.hiredate||','||ei.sal||','||nvl(ei.comm,0)||','||ei.deptno);
exception
when too_many_rows then
dbms_output.put_line('多行数据');
when no_data_found then
dbms_output.put_line('无该名字');
end;
但这样有个问题,无论传入的员工的名字是多少,都会输出–多行数据。
原因是select * into ei
是一条一条赋值的,自然不是一行。
如果能将一条员工信息存在一个变量里面就好了。
需要用到bulk collect
create procedure getInfo(ename emp.ename%type) as
type arrtype is table of emp%rowtype index by binary_integer;
fault exception;
arry arrtype;
begin
select * bulk collect into arry from emp where getInfo.ename=emp.ename;
if arry.count=0 then raise fault;
end if;
for i in 1..arry.count loop
dbms_output.put_line(arry(i).empno||','||arry(i).ename||','||arry(i).job||','||arry(i).mgr||','||arry(i).hiredate||','||arry(i).sal||','||nvl(arry(i).comm,0)||','||arry(i).deptno);
end loop;
exception
when fault then
dbms_output.put_line('没找到');
end;
bulk collect介绍
BULK COLLECT子句是批量SQL的一项功能,从SQL向PL/SQL返回的是一簇数据,而不是一次一条。这样效率更高。(而forall 是sends DML statements from PL/SQL to SQL in batches rather than one at a time.)
可以出现在
- select into statement
- fetch statement
- returning into clause of
1. delete statement
2. insert statement
3. update statement
4. execute immediate statement
需要注意的是,当bulk collect 语句没有行数返回时,PL/SQL不会引发异常,所以看你的需要来定义异常。(PL/SQL does not raise an exception when a statement with a BULK COLLECT clause returns no rows)
用在select into 里面
The SELECT INTO statement with the BULK COLLECT clause (also called
the SELECT BULK COLLECT INTO statement) selects an entire result set
into one or more collection variables.
select bulk collect into语句选择一整个结果集到一个或者更多的集合变量中。
这个collection variables可以使用数组。
DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;
names NameTab;
PROCEDURE print_first_n (n POSITIVE) IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
ELSE
DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE (
' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;
print_first_n(3);
print_first_n(6);
END;
/
输出
First 3 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
First 6 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
Employee #103: Hunold
Employee #104: Ernst
Employee #105: Austin
DECLARE
CURSOR c1 IS
SELECT first_name, last_name, hire_date
FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
stock_managers NameSet; -- nested table of records
BEGIN
-- Assign values to nested table of records:
SELECT first_name, last_name, hire_date
BULK COLLECT INTO stock_managers
FROM employees
WHERE job_id = 'ST_MAN'
ORDER BY hire_date;
-- Print nested table of records:
FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
stock_managers(i).hire_date || ' ' ||
stock_managers(i).last_name || ', ' ||
stock_managers(i).first_name
);
END LOOP;END;
/
结果
01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin
下面这段代码是,输出数组中大于输入值i的数。
CREATE OR REPLACE TYPE numbers_type IS
TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
numbers1 numbers_type := numbers_type(1,2,3,4,5);
BEGIN
DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
--Self-selecting BULK COLLECT INTO clause:
SELECT a.COLUMN_VALUE
BULK COLLECT INTO numbers1
FROM TABLE(numbers1) a
WHERE a.COLUMN_VALUE > p.i
ORDER BY a.COLUMN_VALUE;
DBMS_OUTPUT.PUT_LINE('After SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
END p;
/
上面这段代码是错误的,因为你从数组numbers1里面选,又插入到里面,结果就是里面什么也没有。
有两种解决办法,一是用游标存储,而是用新数组存储。第二种方法要比第一种快。
1.
CREATE OR REPLACE TYPE numbers_type IS
TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
numbers1 numbers_type := numbers_type(1,2,3,4,5);
CURSOR c IS
SELECT a.COLUMN_VALUE
FROM TABLE(numbers1) a
WHERE a.COLUMN_VALUE > p.i
ORDER BY a.COLUMN_VALUE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before FETCH statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
OPEN c;
FETCH c BULK COLLECT INTO numbers1;
CLOSE c;
DBMS_OUTPUT.PUT_LINE('After FETCH statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
IF numbers1.COUNT() > 0 THEN
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
END IF;
END p;
/
CREATE OR REPLACE TYPE numbers_type IS
TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
numbers1 numbers_type := numbers_type(1,2,3,4,5);
numbers2 numbers_type := numbers_type(0,0,0,0,0);
BEGIN
DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
FOR j IN 1..numbers2.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
END LOOP;
SELECT a.COLUMN_VALUE
BULK COLLECT INTO numbers2 -- numbers2 appears here
FROM TABLE(numbers1) a -- numbers1 appears here
WHERE a.COLUMN_VALUE > p.i
ORDER BY a.COLUMN_VALUE;
DBMS_OUTPUT.PUT_LINE('After SELECT statement');
DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
IF numbers1.COUNT() > 0 THEN
FOR j IN 1..numbers1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
IF numbers2.COUNT() > 0 THEN
FOR j IN 1..numbers2.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
END LOOP;
END IF;
END p;
/
用在fetch 里面
用在 returning into clause里
参考自:https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-19F50644-C88E-49AF-B31C-3EE4B4432714