--自定义记录record
declare type demo_record_type is record( id number default 1, value varchar2(10) := 'one' ); demo demo_record_type; begin dbms_output.put_line('['||demo.id||']['||demo.value||']'); end;
--自定义数组（varray）
declare type number_varray is varray(10) of number; --类型number_varray是包含10个数字的number类型的varray数组 list number_varray := number_varray(1,2,3,4,5,6,7,8,null,null); --变量list的类型是number_varray，并初始化 begin for i in 1..list.limit loop --limit返回最大尺寸 dbms_output.put('['||list(i)||']'); end loop; dbms_output.new_line; end;
--嵌套表数据类型（索引列表或java类），list集合， 特点：有序，密集填充 ，大小不固定
declare type number_table is table of number; --类型是table，包含的是number，不用声明长度 list number_table := number_table(1,90,3,4,5,6,7,8); begin list.delete(2); --删除第二个元素，但不删除已分配的空间 dbms_output.put_line(list.count); dbms_output.put_line(list.limit); --table没有limit属性，只有varray数组才有limit属性 for i in 1..list.count loop if list.exists(i) then dbms_output.put('['||list(i)||']'); end if; end loop; dbms_output.new_line; end;
--联合数组数据类型 （类似set集合）
特点：无序 稀疏，大小不固定
--和嵌套表定义非常相似，主要不同点：指定了如何进行索引 index by Pls_Integer 或者 index by varchar2(10)
declare type number_table is table of number index by Pls_Integer; list number_table; begin for i in 1..8 Loop dbms_output.put_line('The index value is ['||i||']'); list(i):= i; end loop; dbms_output.put_line(list.count); list.delete(2); --和varray和嵌套表中不同，删除元素时也会删除已分配的空间 dbms_output.put_line(list.count); for i in 1..list.count loop --由于count变小，所有最后一个值8查不到 if list.exists(i) then dbms_output.put('['||list(i)||']'); end if; end loop; dbms_output.new_line; end;
--引用游标
--弱类型的引用游标没有返回类型，强类型的引用游标有返回类型
--主要作用： 实现在程序间传递结果集的功能，在同一过程中使用引用游标没有意义。
declare type weakly_typed is ref cursor; quick weakly_typed; v_a1 varchar2(30); v_b1 number(10); begin open quick for select t.name,t.age from tb_user t; /* loop fetch quick into v_a1,v_b1; dbms_output.put_line(v_a1||'今年'||v_b1||'岁'); exit when quick%notfound; end loop;*/ end;
--动态显示游标（sql语句中带有局部变量）
--局部变量的名称必须与列名不同,变量在打开游标的时候才被替换
--显示游标的典型用法***********
declare p_id number :=34; type item_record is record ( id number, name varchar2(30) ); item item_record; cursor c is select t.userid,t.name from tb_user t where t.userid = p_id; begin p_id:=45; --在游标打开之前，仍可改变形参 open c; --打开游标 loop --开始循环 fetch c into item; --从游标中抓取值 exit when c%notfound; dbms_output.put_line('Name is ['||item.name||']'); end loop; --结束循环 close c; --关闭游标 End;
--通过游标索引指派记录结构
declare type item_record is record( id number, name varchar2(30) ); ex_item item_record; cursor c is select t.userid,t.name from tb_user t; begin for i in c loop ex_item := i; --给record赋值 dbms_output.put_line('name['||ex_item.name||']'); end loop; end;
--单行隐式游标(使用record接受查询值只能接受单行记录，多了就报错)
declare type item_record is record ( id tb_user.userid%type, name tb_user.name%type, age tb_user.age%type ); dataset item_record; begin select t.userid,t.name,t.age into dataset from tb_user t where rownum<2; dbms_output.put_line(dataset.name); end;
--多行隐式游标（在pl/sql块中编写DML语句或者直接在游标for循环中编写嵌入式而不是在声明块中定义）
--方式一：sql嵌入式for循环 在这种隐式游标中SQL%ROWCOUNT特性返回null
begin for i in (select * from tb_user t) loop if i.name = 'JONES' then exit; end if; dbms_output.put_line('The name is ['||i.name||']'); dbms_output.put_line('update1['||SQL%rowcount||']'); end loop; dbms_output.put_line('update2['||SQL%rowcount||']'); dbms_output.put_line('循环结束'); end;
--方式二：pl/sql块中编写dml语句
begin update tb_user t set t.name ='xiaolei' where t.userid=90; if SQL%found then --隐式游标的%found游标特性只有在行被更新是返回true dbms_output.put_line('update['||SQL%rowcount||']'); --%rowcount表示影响的行数 else dbms_output.put_line('nothing updated!'); end if; end;
--带参数的游标使用
declare v_id number; type item_record is record ( id number, name varchar2(30) ); item item_record; cursor c(p_id number) is select t.userid,t.name from tb_user t where t.userid = p_id; begin v_id:=45; open c(v_id); --在打开游标的时候传入游标的参数 loop fetch c into item; --从游标中抓取值 exit when c%notfound; dbms_output.put_line('Name is ['||item.name||']'); end loop; --结束循环 close c; --关闭游标 End;
--批游标 （bulk collect）
相当于java中的集合colletion
--主要是针对table类型
--好处：比标准游标更快，用于从表或视图中选择，插入，更新或删除大数据集
-- 收集： select 字段 collect bulk into 批游标
--fetch抓取 ： fetch 字段 bulk collect into 批游标
--从list中获取第i个值 list(i) 注意：这里用的是小括号
--并行集合目标
declare type name_collection is table of varchar2(30); type age_collection is table of number; v_name_list name_collection; v_age_list age_collection; begin select t.name,t.age bulk collect into v_name_list,v_age_list from tb_user t ; for i in 1..v_name_list.count loop dbms_output.put_line('name is ['||v_name_list(i)||']'); end loop; end; --记录集合目标 declare type user_record is record( --定义record name varchar2(30), age number ); type collection is table of user_record; --定义集合 full_name collection; begin select name,age bulk collect into full_name from tb_user; for i in 1..full_name.count loop dbms_output.put_line('name is ['||full_name(i).name||']'); end loop; end;
--limit约束的集合目标
--用于设置批收集返回的最大行数
--例：如何每次10行管理批集合
declare type name_collection is table of varchar2(30); type age_collecction is table of number; namelist name_collection; agelist age_collecction; cursor c is select name,age from tb_user; begin open c; fetch c bulk collect into namelist,agelist limit 10; --limit 10限制一次只从游标中取10个值 for i in 1..namelist.count loop exit when namelist.count = 0; dbms_output.put_line('name is ['||namelist(i)||']'); end loop; close c; end;
declare type name_record is record( name varchar2(30), age number ); type collection is table of name_record; full_name collection; cursor c is select name,age from tb_user; begin open c; loop fetch c bulk collect into full_name limit 10; --每次循环从游标中抓取10条记录 exit when full_name.count = 0; for i in 1..full_name.count loop dbms_output.put_line('name is ['||full_name(i).name||']'); end loop; end loop; end;
--forall语句
--在进行批量insert，update，delete时，forall的效率高于for loop的效率
declare type id_collection is table of number; type name_collection is table of varchar2(30); idlist id_collection; namelist name_collection; cursor c is select userid,name from tb_user; begin open c; loop fetch c bulk collect into idlist,namelist limit 10; exit when namelist.count =0; forall i in idlist.first..idlist.last update tb_user set name = Substrb(namelist(i), 0, Length(namelist(i))-1) where userid = idlist(i); end loop; end;
--错误管理（编译错误和运行时错误）
when 错误类型 then
异常处理;
return | exit；
--使用when others then可以捕获任何异常
--内置函数： SQLCODE 返回Oracle预定义的异常的负数
-- SQLERRM 返回引发异常的错误代码和消息
declare a int:=0; ex_1 exception; --定义异常 begin if a=0 then raise ex_1; --抛出异常 end if; exception when others then --捕获所有异常 dbms_output.put_line('exception is' + SQLERRM); end;
