Set of pl/sql -2

bulk collect

Make up for select…into、cursor Only one row of data can be received at a time

select…into… And bulk collect

One 、 The key part is grammar

select  Name  bulk collect into  Collection variable name  from  Table name ;

Two 、 A simple example

-- Output emp Names of all persons in the table 
declare
	-- Declare nested table types a
	type a is table of emp.ename%type;
	-- Declare nested table types a Bearing variable of b
	b a;
begin
	-- take emp All in the table ename Assign a value to b Nested table variables 
	select ename bulk collect into b from emp;
	-- Loop out nested tables b Chinese content 
	for i in b.first..b.last loop
		dbms_output.put_line(b(i));
	end loop;
end;
--2
declare
	-- Declare nested table types a
	type a is table of emp%rowtype;
	-- Declare nested table types a Bearing variable of b
	b a;
begin
	-- take emp All in the table ename Assign a value to b Nested table variables 
	select * bulk collect into b from emp;
	-- Loop out nested tables b Chinese content 
	for i in b.first..b.last loop
		dbms_output.put_line(b(i).ENAME);
	end loop;
end;

cursor And bulk collect

One 、 The key part is grammar

fetch  You name  bulk collect into  Collection variable name ;

Two 、 A simple example

declare
	-- Declare a a The cursor of 
	cursor a is select ename from emp;
	-- Declare a nested table type 
	type b is table of emp.ename%type;
	-- Declare a set variable that hosts nested tables 
	c b;
begin
	-- Open cursor 
	open a;
	-- Matching data 
	fetch a BULK collect into c;
	-- Close cursor 
	close a;
	-- Cyclic output c Medium element 
	for i in c.first..c.last loop
		dbms_output.put_line(c(i));
	end loop;
end;
--2
declare
	-- Declare a a The cursor of 
	cursor a is select * from emp;
	-- Declare a nested table type 
	type b is table of emp%rowtype;
	-- Declare a set variable that hosts nested tables 
	c b;
begin
	-- Open cursor 
	open a;
	-- Matching data 
	fetch a BULK collect into c;
	-- Close cursor 
	close a;
	-- Cyclic output c Medium element 
	for i in c.first..c.last loop
		dbms_output.put_line(c(i).ename);
	end loop;
end;

Batch binding forall

One 、 The key part is grammar

forall  Variable  in  Collection variable name ;

Two 、 A simple example

-- according to emp Delete the department number in the table dept In the information 
-- Implicit cursors 
begin
delete from dept where deptno in(select distinct deptno from emp);
end;
-- Batch binding 
declare
	-- Declare nested table types 
	type a is table of dept.deptno%type;
	-- Declare variables that host nested table types 
	b a;
	-- Declare variables 
	c dept.deptno%type;
begin
	-- assignment 
	SELECT DISTINCT DEPTNO BULK collect INTO b FROM EMP;
	-- Batch binding 
	forall i  in b.first..b.last
		delete from DEPT where deptno=b(i);
end;