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

简介: 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;
目录
相关文章
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
2013 152
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
1403 156
|
SQL 人工智能 Linux
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
1004 5
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
|
SQL 存储 监控
SQL日志优化策略:提升数据库日志记录效率
通过以上方法结合起来运行调整方案, 可以显著地提升SQL环境下面向各种搜索引擎服务平台所需要满足标准条件下之数据库登记作业流程综合表现; 同时还能确保系统稳健运行并满越用户体验预期目标.
595 6
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
Oracle 关系型数据库 Linux
【赵渝强老师】Oracle数据库配置助手:DBCA
Oracle数据库配置助手(DBCA)是用于创建和配置Oracle数据库的工具,支持图形界面和静默执行模式。本文介绍了使用DBCA在Linux环境下创建数据库的完整步骤,包括选择数据库操作类型、配置存储与网络选项、设置管理密码等,并提供了界面截图与视频讲解,帮助用户快速掌握数据库创建流程。
1110 93
|
Oracle 关系型数据库 Linux
【赵渝强老师】使用NetManager创建Oracle数据库的监听器
Oracle NetManager是数据库网络配置工具,用于创建监听器、配置服务命名与网络连接,支持多数据库共享监听,确保客户端与服务器通信顺畅。
632 0
|
存储 Oracle 关系型数据库
服务器数据恢复—光纤存储上oracle数据库数据恢复案例
一台光纤服务器存储上有16块FC硬盘,上层部署了Oracle数据库。服务器存储前面板2个硬盘指示灯显示异常,存储映射到linux操作系统上的卷挂载不上,业务中断。 通过storage manager查看存储状态,发现逻辑卷状态失败。再查看物理磁盘状态,发现其中一块盘报告“警告”,硬盘指示灯显示异常的2块盘报告“失败”。 将当前存储的完整日志状态备份下来,解析备份出来的存储日志并获得了关于逻辑卷结构的部分信息。
|
Oracle 安全 关系型数据库
【Oracle】使用Navicat Premium连接Oracle数据库两种方法
以上就是两种使用Navicat Premium连接Oracle数据库的方法介绍,希望对你有所帮助!
2999 28
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle RMAN的目录数据库
Oracle RMAN默认将备份元信息存储在控制文件中,但控制文件损坏或丢失会导致恢复失败,且备份增多会使控制文件无限增长。为解决这些问题,Oracle引入了RMAN目录数据库(Catalog Database),专门用于存储RMAN备份的元信息。使用目录数据库可提升备份管理效率,支持多数据库共享、长期备份历史记录存储,并可保存RMAN脚本。本文详细介绍了如何创建目录数据库、注册目标数据库及其操作步骤。
505 0

热门文章

最新文章

推荐镜像

更多