当前位置:   article > 正文

Oracle (PLSQL) [集合,动态SQL]_plsql 动态sql

plsql 动态sql

目录

一、集合

分类

属性和方法

索引表

嵌套表

bulk collect

二、动态 SQL

三、异常

预定义异常

非预定义异常

自定义异常


一、集合

collection
相同类型元素的组合
使用唯一的下标来标识其中的每个元素

分类

集合数据类型分类:
1.索引表(index by tables)==>关联数组(associative arrays)
2.嵌套表(nested table)
3.可变长度数组(varray:variable-size arrays) ==> 变长数组

属性和方法

集合名字.属性名
集合名字.方法名

  1. 1.first  取集合的第一元素的下标                     
  2. 2.last   最后一个
  3. 3.count  总长度 
  4. 4.limit  最大值  
  5. 索引表和嵌套表是不限个数的,所以返回null
  6. 变长数组返回定义时的最大索引
  7. 5.delete([n])  删除集合中的元素
  8. 加n表示下标,删除对应下标的值
  9. 不加删除所有
  10. 6.extend(n[,ind]):扩展集合元素 
  11. !!!无限大的集合不能扩展!!!
  12. n表示扩展的元素个数,
  13. ind是集合中的一个元素的下标,加上它表示扩展集合时,给扩展的元素加上值,值是ind这个下标对应的元素
  14. 7.next(下标)  取当前元素下一个元素的下标
  15. 8.prior(下标) 上一个

索引表

使用整数,可以为负
(pls_integer,binary_integer)
字符串来作为下标

下标可以不连续
元素个数无限制
只能在PLSQL中使用,不可以存储在数据库中.

  1. 定义索引表类型
  2.     type 类型名称 is table of 数据类型(是集合中值的数据类型)--单个元素的长度
  3.     index by 下标的数据类型;
  4. (varchar2,pls_integer,binary_integer)
  5. --type mytype is table of varchar2(200) index by pls_integer;
  6. 声名一个集合变量
  7.     变量名 类型名
  8.   --v  mytype;
  9. 集合中数据的存取:
  10.     集合变量(下标)  
  11. --v(下标); 
  12. --v(下标):=值;
  13. ---------------------------------
  14. declare
  15.    --声名一个索引表类型
  16.    type mytype is table of varchar2(20) index by binary_integer;
  17.    --声名变量 上面那个类型
  18.    a mytype;
  19.    --声名一个变量保存索引表的下标
  20.    b pls_integer;
  21. begin
  22.    a(1):='张三';
  23.    a(-2):='李四';
  24.    a(0):='王五';
  25.    a(6):='老六';
  26.    --a.delete(); --全删   
  27.    --a.delete(6); --删老六
  28.    --a.extend(100); --无限大集合不能扩展
  29.    --dbms_output.put_line(a(6));
  30.    dbms_output.put_line(a(1)); --张三
  31.    dbms_output.put_line(a.next(1)); --老六 没有就空
  32.    dbms_output.put_line(a.prior(1)); --老六 没有就空
  33.    dbms_output.put_line(a(a.first)); --李四
  34.    dbms_output.put_line(a(a.last)); --老六
  35.    dbms_output.put_line(a.count); --4
  36.    dbms_output.put_line(a.limit); --啥也没有
  37. ----------遍历集合------------------------
  38. ------loop遍历----------
  39.    --初始条件
  40.    b:=a.first; -- -2
  41.    loop
  42.       --打印集合元素
  43.       dbms_output.put_line(a(b));
  44.       --退出条件
  45.       exit when b:=a.last; --到最后一个就退出循环
  46.       b:=a.next(b); --迭代
  47.    end loop;
  48. ------while遍历----------
  49.     b:=a.first;
  50.     while b != a.last loop
  51.     dbms_output.put_line(a(b));
  52.     b:=a.next(b);
  53.     end loop;
  54. ------for遍历----------
  55. 必须是连续下标才可以
  56. for循环自动迭代+1
  57.     for v in a.first..a.last loop;
  58.     dbms_output.put_line(a(v));
  59.     end loop
  60. end;  
  1. declare
  2.    --声名记录类型
  3.    type recType is record(
  4.        name varchar2(30),
  5.        sex varchar2(3)
  6.    );
  7.    --声名一个索引表类型
  8.    type indType is table of recType index by pls_integer;
  9.    --声名一个变量
  10.    ind  indType;
  11.    --声名一个记录类型变量
  12.    rec recType;
  13.    --声名一个记录类型变量
  14.    rec2 recType;
  15. begin
  16.    --给记录类型赋值
  17.    rec.name:='张三';
  18.    rec.sex:='男';
  19.    --给集合中添加一个元素
  20.    ind(1):=rec;
  21.    --取集合中的值 
  22.    rec2:=ind(1);---取出来的是一个记录类型数据
  23.    dbms_output.put_line(rec2.name||','||rec2.sex);
  24. end;

嵌套表

使用整数(只能为正)作为下标

下标是连续的
元素个数无限制
可以用在PLSQL中,也可以存储在数据库中
需要初始化

  1. 数据的存取和索引表相同 
  2. 定义嵌套表类型
  3.     type 类型名称 is table of 数据类型(存储的数据的数据类型);
  4. 变量声名:
  5.      变量名 类型名称
  6. !!!嵌套表在赋值之前必须初始化!!!
  7.     变量名:=集合类型名(值...);
  8.     
  9. declare
  10.   --声名一个嵌套表类型,存放字符串数据
  11.   type a is table of varchar2(20); --单个元素长度
  12.   --声名变量
  13.   b a;
  14.   c a;
  15.   --声名一个变量保存嵌套表的下标
  16.   ind pls_integer;
  17. begin
  18.   --初始化嵌套表b
  19.     b:=a(); --空,自己扩展
  20.     dbms_output.put_line('b的长度'||tab1.count);
  21.   --初始化嵌套表tab2
  22.     c:=a('a','b','c','d');
  23.     c(1):='aa' --替换
  24.   --本来是a 现在被替换成aa
  25.     dbms_output.put_line('tab2的长度'||tab2.count);--4
  26.     --初始化只给了四个位置 需要扩展
  27.     c.extend(10); --扩展10个
  28.     --c.extend(10,1); 
  29.     --扩展10个,里面的值为下标1的值
  30.     c(5):= 'e'
  31.     dbms_output.put_line(c(5)); --没扩展就报错
  32. ------遍历集合--------------------
  33. ------loop----------
  34.    i := 1;
  35.    loop
  36.       --打印集合元素
  37.       dbms_output.put_line(c(i));
  38.       --退出条件
  39.       exit when i=c.last;
  40.       i:=c.next(i);
  41.    end loop;
  42. ------while----------
  43.     while i <= c.last loop
  44.         dbms_output.put_line(c(i));
  45.         i:=c.next(i);
  46.     end loop;
  47. ------for----------
  48.   for i in c.first..c.last loop
  49.     dbms_output.put_line(c(i));
  50.   end loop;
  51. end;
  1. 可以永久保存到数据库 ==> Types
  2. 嵌套表在数据库中的使用:
  3. create type 类型名称 is table of 数据类型;
  4. create type myt is table of varchar2(20);
  5. declare
  6.    --声名一个tabType类型的嵌套表变量
  7.    a myt;
  8. begin
  9.    --初始化
  10.    a := myt('1','2','3');
  11.    --遍历
  12.    for i in a.first..a.last loop
  13.      dbms_output.put_line(a(i));
  14.    end loop;
  15. end;
  16. -------------------------------------------------
  17. 建表时使用嵌套表类型 --先创建嵌套表类型再建表
  18. create table 表名(
  19.    列名 数据类型,
  20.    ...
  21.    列名 嵌套表类型
  22.    ...
  23. )nested table 嵌套表类型列名 store as 表名(是数据库中没有的表);
  24. -------------------------
  25. create table sb(
  26.     id number(10),
  27.     name myt
  28. )nested table name store as aaaaaa;
  29. insert into sb values(1,myt('张三''李四','王五')); --嵌套到表
  30. insert into sb values(2,myt('老六''老八'));
  31. select * from aaaaaa;--插入一个集合,小三角点开看到刚刚插入的名字
  32. select * from table(select name from aaaaaa);
  33. --id2的数据
  34. select * from table(select name from aaaaaa where id =2);
  35. 删除 
  36. drop type myt;
  37. 类型在使用的时候,必须先删除表
  38. drop table sb;
  39. drop type myt;
  1. 变长数组
  2. 使用正整数作为下标
  3. 下标是连续的
  4. 元素个数有限制的
  5. 可以用在PLSQL中,也可以存储在数据库中
  6. 初始化
  7. type 类型名称 is varray(长度)|varying (长度) 
  8. of 元素数据类型(保存的数据的数据类型);
  9. 变量名 类型名;
  10. 使用和嵌套表一样,使用前需要初始化
  11. 变量:=类型();  
  12. 变量:=类型(值,值,值,值,值);
  13. declare
  14.    --声名一个变长数组类型
  15.    type a is varray(666) of varchar2(20);
  16.    --声名一个数组变量
  17.    arr a;
  18. begin
  19.    --初始化
  20.    arr:=arrType('a','b','c');
  21.    dbms_output.put_line('数组的长度'||arr.count);  --3
  22.    arr.extend(7); --扩展
  23.    --不能扩展超过声明(varray(666))的长度   
  24.    dbms_output.put_line('数组的长度'||arr.count); --10
  25. end;
  1. 变长数组在数据库中的使用;
  2. create type 类型名称a is varray(长度)|varying (长度) of 数据类型;
  3. create table 表名c(
  4.    列名 数据类型,
  5.    数组列b 数组类型a
  6. );
  7. insert into 表名c(列名,数组列名b) values(值,数组类型a(值,值,值));
  8. select * from table(select 数组列b from 表名c where 条件);

bulk collect

可以把一组数据取出来存入一个集合类型变量中

select … into 变量
只能查出一条数据保存到变量中
++++++++++++++++++++++++++++++++
select … bulk collect into
可以查出多条数据存入一个变量中
(集合类型变量)

  1. 查询所有的名字
  2. declare
  3.    --声名一个集合类型
  4.    type t is table of a.name%type;
  5.    --声名一个嵌套表变量
  6.    aname t;
  7. begin
  8.    --注意:这里是不需要初始化集合变量 namelist:=tabType();
  9.    select name bulk collect into aname from a;
  10.    for i in aname.first..aname.last loop
  11.        dbms_output.put_line(aname(i));
  12.    end loop; 
  13. end
  14.  
  15. fetch 游标  bulk collect into 集合类型变量
  16. 查询a表的信息
  17. declare
  18.    --声名一个游标变量
  19.    cursor cur is select * from a;
  20.    --声名一个集合类型
  21.    type ty is table of a%rowtype;
  22.    --声名一个变量
  23.    aaa ty;
  24. begin
  25.    open cur;   --打开游标
  26.    fetch cur bulk collect into aaa;
  27.    close cur;   --关闭游标
  28.    for i in aaa.first..aaa.last loop
  29.      dbms_output.put_line(aaa(i).name||aaa(i).aga);
  30.    end loop;
  31. end;

批量绑定

  1. 简约版for循环
  2. forall 变量 in 集合
  3.    sql语句; --增、删、改
  4.    -- insert update delete
  5. 根据id删除name
  6. declare
  7.    --声名一个集合类型
  8.    type ty is table of a.id%type;
  9.    --声名一个变量
  10.    typ ty;
  11. begin
  12.   select id bulk collect into typ from a;
  13.   forall i in typ.first..typ.last
  14.      delete from b where id=id(i);
  15. end;

二、动态 SQL

在PL/SQL程序执行时生成的 SQL 语句
编译程序对动态 SQL 不做处理,而是在程序运行时动态构造语句、对语句进行语法分析并执行
DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用,但是可以通过动态 SQL 来执行

  1. 执行动态 SQL 的语法:
  2.    execute immediate 
  3.    字符串类型的命令
  4.    字符串类型的sql语句,或字符串类型的plsql代码
  5.    --'select * from a'
  6. execute immediate 字符串参数 [into] 变量 using 参数
  7. using 参数:传给sql语句的参数
  8. ===================================================
  9. 按照id查询信息
  10. declare
  11.   v_a a%rowtype;
  12.   v_sql varchar2(250);  --声名一个字符串类型变量
  13.   b varchar(100);
  14. begin
  15.   execute immediate 'select * from a where id=6' into v_a;
  16.   dbms_output.put_line(v_a.name||','||v_a.job||v_a.age);
  17.   execute immediate 'select * from a where id := a' into v_a usind 6
  18.   --a占位符 不可以特殊符号 
  19.   dbms_output.put_line(v_a.name||','||v_a.job||v_a.age);--按照顺序往里传值
  20.  ----------------------------------------------------------
  21.   v_sql:='select * from a where id=6';
  22.   execute immediate v_sql into v_a;
  23.   dbms_output.put_line(v_emp.ename||','||v_emp.job); 
  24.   v_sql:='select * from a where id=:a' ;
  25.   b:=' and age=:b'; --注意空格
  26.   execute immediate v_sql || b into v_a using 6,18;
  27.   dbms_output.put_line(v_a.name||','||v_.job||v_a.age);
  28.   
  29. end;
  30. -----DDL----------------------------------------
  31. declare
  32.  
  33. begin
  34.   execute immediate 'insert into a values (:1) using 799';
  35.   execute immediate 'update a set sal=:1 where id=:2' using n,799 ;
  36.   execute immediate 'delete from a where id=:1' using 99;
  37.   execute immediate 'create table t(id number(11),name varchar2(20))';
  38.   
  39.   for v in 1..10 loop
  40.         execute immediate 'insert into a values (:1) using v';
  41.   end loop;
  42. end;

三、异常

在运行程序时出现的错误叫做异常
发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分

系统异常
预定义异常和非预定义异常。
自定义异常

  1. declare
  2.    声明部分;
  3. begin
  4.    plsql代码块;
  5.    exception
  6.       异常处理部分;
  7. end;

预定义异常

Oracle定义了它们的错误编号和异常名字,
常见的预定义异常处理如下:

  1. ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性,必须首先初始化对象。直接引用未初始化的对象属性时,会发生异常
  2. CASE_NOT_FOUND:对应ORA-06592,当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时,会触发
  3. COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前,必须首先初始化集合
  4. CURSOR_ALREADY_OPEN:ORA-06511,当已打开游标上执行OPEN操作时会触发
  5. INVALID_CURSOR:ORA-01001,当视图从未打开游标,提取数据,或者关闭未打开游标时会触发
  6. INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发
  7. LOGIN_DENIED:ORA-01017,连接Oracle数据库时,如果提供了不正解的用户名和口令时会触发
  8. NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
  9. NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
  10. PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题,在这种情况下需要重新安装数据字典视图和PL/SQL
  11. ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时,如果宿主变量和游标变量不兼容的返回类型时,会触发
  12. SELF_IS_NULL: ORA-30625,当使用对象类型时,如果在null实例上调用成员方法时,会触发
  13. STORAGE_ERROR:ORA-06500当执行PL/SQL块时,如果超出内存空间或者内存被破坏,会触发
  14. SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发
  15. SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532,使用嵌套表或者varray元素时,如果元素下标为负值时,会触发
  16. SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串,会触发
  17. TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发
  18. TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时,如果返回超过一行、会触发
  19. VALUE_ERROR:ORA-06502,执行赋值时,如果变量长度不足,会触发
  20. ZERO_DIVIDE:ORA-01476如果用数字值除0,会触发
  1. begin
  2.   exception
  3.     when 异常名称 then
  4.        异常处理代码;
  5.           ...
  6.     when others then
  7.        异常处理代码;  
  8. end;
  9. exception:出现在end前面
  10. others其他所有的异常
  11. ------------------------------
  12. declare
  13.    v a.name%type;
  14. begin
  15.    select name into v from a where 1=0;
  16.    exception 
  17.     when no_data_found then --NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
  18.         dbms_output.put_line('nono'); 
  19.     when others then
  20.         dbms_output.put_line('就是错了'||sqlcode); --就是错了-Oracle错误编码
  21.         dbms_output.put_line('就是错了'||sqlerrm); --就是错了-Oracle错误信息
  22. end;
  23. SQLCODE与SQLERRM为异常处理函数。
  24. 函数SQLCODE用于取得Oracle错误号,
  25. 函数SQLERRM用于取得与错误号对应的相关错误消息

非预定义异常

  1. ORACLE为它定义了错误编号,但没有定义异常名字。我们使用的时候,先声名一个异常名,通过伪过程PRAGMA EXCEPTION_INIT,将异常名与错误号关联起来。
  2. pragma exception_init(自定义的异常,编号);
  3. declare
  4.    myexc exception;   --声名异常变量
  5.    pragma exception_init(myexc,-00001);   --绑定异常
  6. begin
  7.     --delete a where id =10;
  8. --exeception
  9.     --when myexc then
  10.        --dbms_output.put_line('nonono');
  11.    begin
  12.       insert into a(id,age) values(799,20);
  13.       --commit;
  14.       exception
  15.           when myexc then
  16.              --rollback;
  17.             dbms_output.put_line('违反唯一约束');
  18.    end;
  19.    dbms_output.put_line('-----');  
  20. end;

自定义异常

程序员从业务角度出发,制定的一些规则和限制。

  1. 异常名称 exception;
  2. 抛出异常:
  3. raise 异常名称;
  4. exception 捕获异常 处理异常
  5. declare
  6.    myexcp exception;   --定义一个自己的异常
  7.    m number(10):=&m;
  8.    n number(10):=&n;
  9.       --声名两个变量接收两个数
  10. begin
  11.    if n=0 then
  12.       dbms_output.put_line('除数不能为零');
  13.       raise myexcp;--抛出异常 需要处理异常
  14.    end if;
  15.    dbms_output.put_line(m/n);
  16. end;

引发应用程序错误
RAISE_APPLICATION_ERROR 过程
用于创建用户定义的错误信息
可以在可执行部分和异常处理部分使用
错误编号必须介于 –20000 和 –20999 之间

  1. RAISE_APPLICATION_ERROR(error_number, error_message);
  2. declare
  3.    myexc exception;
  4.    pragma exception_init(myexc,-20001);
  5. begin
  6.   dbms_standard.raise_application_error(-20001,'我的异常');
  7.   exception
  8.     when myexc then
  9.       dbms_output.put_line('1111111111');
  10. end;
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/寸_铁/article/detail/793033
推荐阅读
相关标签
  

闽ICP备14008679号