Oracle数据库PL/SQL学习笔记三——复合数据的定义

--自定义记录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;