Pl/sql学习笔记2
--
declare
type vsal_table is table of emp.sal%type;
a vsal_table;
begin
--必须得初始化 并且有数量上的区分 从一开的
a:=vsal_table(1000,2000,30000);
dbms_output.put_line(a(1));
end;
--使用嵌套表的案例
--定义一个类型
create type stu_name is table of varchar2(20);
create table stu (
sid number(9),age number(2) , sname stu_name
)nested table sname store as stu_name_table;
select *from stu;
--此时嵌套表就已经建立好了 需要插入语句
insert into stu values (1,20,stu_name('cheng','zhi'));
--查询sname 然后遍历出来
declare
sname stu_name;
begin
select sname into sname from stu where sid=1;
for i in sname.first..sname.last loop
dbms_output.put_line(sname(i));
end loop;
end;
--更新数据
declare
sname1 stu_name:=stu_name('zhang','san');
begin
update stu set sname=sname1 where sid=1;
end;
--变长数组 array 需要预先指定一种长度 并进行赋值
declare
type v_ename is array(3) of emp.ename%type;
v1 v_ename:=v_ename('aa','bb','cc');
begin
select ename into v1(2) from emp where empno=7369;
dbms_output.put_line(v1(2));
end;
--常用的函数
--collectionname.method_name(arg);
--exists是否存在什么样的元素 必须传入一个number类型的数据
--count 是取集合的长度 元素的个数
--limit 返回集合的最大个数
--prior 取出当前的上一个元素
--next 下一个的下标
declare
type v_ename is array(20) of emp.ename%type;
v1 v_ename:=v_ename('aa','bb','cc');
begin
if v1.exists(1) then
dbms_output.put_line(v1.count);
dbms_output.put_line(v1.limit);
dbms_output.put_line('hava');
dbms_output.put_line(v1(2));
dbms_output.put_line(v1(v1.prior(2)));
dbms_output.put_line(v1(v1.next(2)));
else
dbms_output.put_line('no');
end if;
end;
--delete函数
declare
type vsal_table is table of emp.sal%type;
a vsal_table;
begin
--必须得初始化 并且有数量上的区分 从一开的
a:=vsal_table(1000,2000,30000);
dbms_output.put_line(a(1));
dbms_output.put_line(a.count);
a.delete(3);
dbms_output.put_line(a.count);
end;
--删除的时候下标不会发生变化 位置不会变化
--oracle 里面的批量处理的问题
create table t1(tid number(9),tname varchar2(9));
select * from t1;
declare
type id_table is table of number(9) index by binary_integer;
type name_table is table of varchar2(9) index by binary_integer;
start_time number(10);
end_time number(10);
v_id_table id_table;
v_name_table name_table;
begin
for i in 1..10000 loop
v_id_table(i):=i;
v_name_table(i):=to_char(i);
end loop;
start_time:=dbms_utility.get_time;
for i in 1..v_id_table.count loop
insert into t1 values(v_id_table(i),v_name_table(i));
end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line(end_time-start_time);
end;
--瞬间插入10000条记录
create table t2(tid number(9),tname varchar2(9));
select count(*) from t2;
declare
type id_table is table of number(9) index by binary_integer;
type name_table is table of varchar2(9) index by binary_integer;
start_time number(10);
end_time number(10);
v_id_table id_table;
v_name_table name_table;
begin
for i in 1..10000 loop
v_id_table(i):=i;
v_name_table(i):=to_char(i);
end loop;
start_time:=dbms_utility.get_time;
forall i in 1..v_id_table.count
insert into t2 values(v_id_table(i),v_name_table(i));
end_time:=dbms_utility.get_time;
dbms_output.put_line(end_time-start_time);
end;
--批量处理=之更新
declare
type id_table is table of number(9) index by binary_integer;
type name_table is table of varchar2(9) index by binary_integer;
vid id_table;
vname name_table;
begin
for i in 1..100 loop
vid(i):=i;
vname(i):=to_char(i)||'name';
end loop;
forall i in 1..vid.count
update t2 set tname=vname(i) where tid=vid(i);
end;
select * from t2 where tname='1name';
--使用 bulk collect 处理批量任务
declare
type t2_table is table of t2%rowtype index by binary_integer;
vt t2_table;
begin
select * bulk collect into vt from t2;
end;
--游标案例
declare
v_emp emp%rowtype;
--声明游标
cursor cur_emp is
select * from emp;
begin
--open cursor
open cur_emp;
loop
--fetch
fetch cur_emp into v_emp;
exit when cur_emp%notfound;
dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
end loop;
--close cursor
close cur_emp;
end;
--灵活掌握
declare
type r1 is record (
ename emp.ename%type,
sal emp.sal%type
);
type a1 is table of r1 index by binary_integer;
a a1;
begin
select ename ,sal bulk collect into a from emp;
for i in a.first..a.last loop
dbms_output.put_line(a(i).ename||' '||a(i).sal);
end loop;
end;
