set serveroutput on
--单行单列,可以用标量变量
declare
v_job emp.job%type;
begin
select emp.job into v_job from emp where emp.ename=&name;
dbms_output.put_line(' 职业:'||v_job);
end;
/
--单行多列,可用记录record
declare
type emp_record_type is record( v_ename emp.ename%type, v_job emp.job%type, v_sal emp.sal%type);
emp_record emp_record_type;
begin
select emp.ename,emp.job,emp.sal into emp_record from emp where emp.empno=&no;
dbms_output.put_line('名字:'||emp_record.v_ename||' 职业:'||emp_record.v_job||'工资:'||emp_record.v_sal);
end;
/
--多行单列,可以用集合
declare
type name_table_type is table of varchar2(10) index by varchar2(10);
name_table name_table_type;
begin
name_table(1):='张三';
name_table(2):='李四';
name_table(3):='王五';
for i in 1..3 loop
dbms_output.put_line(name_table(i));
end loop;
end;
/
--多行多列,可以用游标
declare
cursor v_cursor is select emp.ename,emp.job,emp.sal from emp;
v_ename emp.ename%type;
v_job emp.job%type;
v_sal emp.sal%type;
begin
open v_cursor;
loop fetch v_cursor into v_ename,v_job,v_sal;
exit when v_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||' 职业:'||v_job||'工资:'||v_sal);
end loop;
close v_cursor;
end;
/
set serveroutput on
--使用记录表提取多行多列数据-emp%rowtype--批量绑定,使用bulk collect批量提取数据
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp;
for i in 1..emp_table.count loop
dbms_output.put_line('姓名:'||emp_table(i).ename);
end loop;
end;
/
--单行多列,使用索引表--批量绑定,使用forall批量插入数据
declare
type ename_table_type is table of VARCHAR2(10) index by binary_integer;
type job_table_type is table of VARCHAR2(9) index by binary_integer;
type sal_table_type is table of NUMBER index by binary_integer;
type comm_table_type is table of NUMBER index by binary_integer;
ename_table ename_table_type; job_table job_table_type;
sal_table sal_table_type;
comm_table comm_table_type;
begin
for i in 1..100 loop
ename_table(i):='name'||to_char(i);
job_table(i):='job'||to_char(i);
sal_table(i):=1000*i;
comm_table(i):=100*i;
end loop;
forall i in 1..ename_table.count insert into bonus values(ename_table(i),job_table(i),sal_table(i),comm_table(i));
commit;
end;
/
PL/SQL学习笔记五之复合数据类型/批量绑定!!!
最新推荐文章于 2024-01-24 13:47:50 发布