目录
一、集合
collection
相同类型元素的组合
使用唯一的下标来标识其中的每个元素
分类
集合数据类型分类:
1.索引表(index by tables)==>关联数组(associative arrays)
2.嵌套表(nested table)
3.可变长度数组(varray:variable-size arrays) ==> 变长数组
属性和方法
集合名字.属性名
集合名字.方法名
1.first 取集合的第一元素的下标
2.last 最后一个
3.count 总长度
4.limit 最大值
索引表和嵌套表是不限个数的，所以返回null，
变长数组返回定义时的最大索引
5.delete([n]) 删除集合中的元素
加n表示下标，删除对应下标的值
不加删除所有
6.extend(n[,ind]):扩展集合元素
！！！无限大的集合不能扩展！！！
n表示扩展的元素个数，
ind是集合中的一个元素的下标，加上它表示扩展集合时，给扩展的元素加上值，值是ind这个下标对应的元素
7.next(下标) 取当前元素下一个元素的下标
8.prior(下标) 上一个索引表
使用整数，可以为负
（pls_integer,binary_integer）
字符串来作为下标
下标可以不连续
元素个数无限制
只能在PLSQL中使用,不可以存储在数据库中.
定义索引表类型
type 类型名称 is table of 数据类型(是集合中值的数据类型)--单个元素的长度
index by 下标的数据类型;
(varchar2,pls_integer,binary_integer)
--type mytype is table of varchar2(200) index by pls_integer;
声名一个集合变量
变量名 类型名
--v mytype;
集合中数据的存取：
集合变量(下标)
--v(下标);
--v(下标):=值;
---------------------------------
declare
--声名一个索引表类型
type mytype is table of varchar2(20) index by binary_integer;
--声名变量 上面那个类型
a mytype;
--声名一个变量保存索引表的下标
b pls_integer;
begin
a(1):='张三';
a(-2):='李四';
a(0):='王五';
a(6):='老六';
--a.delete(); --全删
--a.delete(6); --删老六
--a.extend(100); --无限大集合不能扩展
--dbms_output.put_line(a(6));
dbms_output.put_line(a(1)); --张三
dbms_output.put_line(a.next(1)); --老六 没有就空
dbms_output.put_line(a.prior(1)); --老六 没有就空
dbms_output.put_line(a(a.first)); --李四
dbms_output.put_line(a(a.last)); --老六
dbms_output.put_line(a.count); --4
dbms_output.put_line(a.limit); --啥也没有
----------遍历集合------------------------
------loop遍历----------
--初始条件
b:=a.first; -- -2
loop
--打印集合元素
dbms_output.put_line(a(b));
--退出条件
exit when b:=a.last; --到最后一个就退出循环
b:=a.next(b); --迭代
end loop;
------while遍历----------
b:=a.first;
while b != a.last loop
dbms_output.put_line(a(b));
b:=a.next(b);
end loop;
------for遍历----------
必须是连续下标才可以
for循环自动迭代+1
for v in a.first..a.last loop;
dbms_output.put_line(a(v));
end loop
end; declare
--声名记录类型
type recType is record(
name varchar2(30),
sex varchar2(3)
);
--声名一个索引表类型
type indType is table of recType index by pls_integer;
--声名一个变量
ind indType;
--声名一个记录类型变量
rec recType;
--声名一个记录类型变量
rec2 recType;
begin
--给记录类型赋值
rec.name:='张三';
rec.sex:='男';
--给集合中添加一个元素
ind(1):=rec;
--取集合中的值
rec2:=ind(1);---取出来的是一个记录类型数据
dbms_output.put_line(rec2.name||','||rec2.sex);
end;嵌套表
使用整数（只能为正）作为下标
下标是连续的
元素个数无限制的
可以用在PLSQL中,也可以存储在数据库中
需要初始化
数据的存取和索引表相同
定义嵌套表类型
type 类型名称 is table of 数据类型（存储的数据的数据类型）;
变量声名：
变量名 类型名称
！！！嵌套表在赋值之前必须初始化！！！
变量名:=集合类型名(值...);
declare
--声名一个嵌套表类型，存放字符串数据
type a is table of varchar2(20); --单个元素长度
--声名变量
b a;
c a;
--声名一个变量保存嵌套表的下标
ind pls_integer;
begin
--初始化嵌套表b
b:=a(); --空，自己扩展
dbms_output.put_line('b的长度'||tab1.count);
--初始化嵌套表tab2
c:=a('a','b','c','d');
c(1):='aa' --替换
--本来是a 现在被替换成aa
dbms_output.put_line('tab2的长度'||tab2.count);--4
--初始化只给了四个位置 需要扩展
c.extend(10); --扩展10个
--c.extend(10，1);
--扩展10个，里面的值为下标1的值
c(5):= 'e';
dbms_output.put_line(c(5)); --没扩展就报错
------遍历集合--------------------
------loop----------
i := 1;
loop
--打印集合元素
dbms_output.put_line(c(i));
--退出条件
exit when i=c.last;
i:=c.next(i);
end loop;
------while----------
while i <= c.last loop
dbms_output.put_line(c(i));
i:=c.next(i);
end loop;
------for----------
for i in c.first..c.last loop
dbms_output.put_line(c(i));
end loop;
end;可以永久保存到数据库 ==> Types
嵌套表在数据库中的使用：
create type 类型名称 is table of 数据类型;
create type myt is table of varchar2(20);
declare
--声名一个tabType类型的嵌套表变量
a myt;
begin
--初始化
a := myt('1','2','3');
--遍历
for i in a.first..a.last loop
dbms_output.put_line(a(i));
end loop;
end;
-------------------------------------------------
建表时使用嵌套表类型 --先创建嵌套表类型再建表
create table 表名(
列名 数据类型,
...
列名 嵌套表类型
...
)nested table 嵌套表类型列名 store as 表名（是数据库中没有的表）;
-------------------------
create table sb(
id number(10),
name myt
)nested table name store as aaaaaa;
insert into sb values(1,myt('张三'，'李四','王五')); --嵌套到表
insert into sb values(2,myt('老六'，'老八'));
select * from aaaaaa;--插入一个集合，小三角点开看到刚刚插入的名字
select * from table(select name from aaaaaa);
--id2的数据
select * from table(select name from aaaaaa where id =2);
删除
drop type myt;
类型在使用的时候，必须先删除表
drop table sb;
drop type myt;变长数组
使用正整数作为下标
下标是连续的
元素个数有限制的
可以用在PLSQL中,也可以存储在数据库中
初始化
type 类型名称 is varray(长度)|varying (长度)
of 元素数据类型（保存的数据的数据类型）;
变量名 类型名;
使用和嵌套表一样，使用前需要初始化
变量：=类型();
变量：=类型(值,值,值,值,值);
declare
--声名一个变长数组类型
type a is varray(666) of varchar2(20);
--声名一个数组变量
arr a;
begin
--初始化
arr:=arrType('a','b','c');
dbms_output.put_line('数组的长度'||arr.count); --3
arr.extend(7); --扩展
--不能扩展超过声明（varray(666)）的长度
dbms_output.put_line('数组的长度'||arr.count); --10
end;变长数组在数据库中的使用;
create type 类型名称a is varray(长度)|varying (长度) of 数据类型;
create table 表名c(
列名 数据类型,
数组列b 数组类型a
);
insert into 表名c(列名,数组列名b) values(值,数组类型a(值,值,值));
select * from table(select 数组列b from 表名c where 条件);bulk collect
可以把一组数据取出来存入一个集合类型变量中
select … into 变量
只能查出一条数据保存到变量中
++++++++++++++++++++++++++++++++
select … bulk collect into
可以查出多条数据存入一个变量中
(集合类型变量)
查询所有的名字
declare
--声名一个集合类型
type t is table of a.name%type;
--声名一个嵌套表变量
aname t;
begin
--注意：这里是不需要初始化集合变量 namelist:=tabType();
select name bulk collect into aname from a;
for i in aname.first..aname.last loop
dbms_output.put_line(aname(i));
end loop;
end;
fetch 游标 bulk collect into 集合类型变量
查询a表的信息
declare
--声名一个游标变量
cursor cur is select * from a;
--声名一个集合类型
type ty is table of a%rowtype;
--声名一个变量
aaa ty;
begin
open cur; --打开游标
fetch cur bulk collect into aaa;
close cur; --关闭游标
for i in aaa.first..aaa.last loop
dbms_output.put_line(aaa(i).name||aaa(i).aga);
end loop;
end;批量绑定
简约版for循环
forall 变量 in 集合
sql语句; --增、删、改
-- insert update delete
根据id删除name
declare
--声名一个集合类型
type ty is table of a.id%type;
--声名一个变量
typ ty;
begin
select id bulk collect into typ from a;
forall i in typ.first..typ.last
delete from b where id=id(i);
end;二、动态 SQL
在PL/SQL程序执行时生成的 SQL 语句
编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行
DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行
执行动态 SQL 的语法：
execute immediate
字符串类型的命令
字符串类型的sql语句，或字符串类型的plsql代码
--'select * from a'
execute immediate 字符串参数 [into] 变量 using 参数
using 参数：传给sql语句的参数
===================================================
按照id查询信息
declare
v_a a%rowtype;
v_sql varchar2(250); --声名一个字符串类型变量
b varchar(100);
begin
execute immediate 'select * from a where id=6' into v_a;
dbms_output.put_line(v_a.name||','||v_a.job||v_a.age);
execute immediate 'select * from a where id := a' into v_a usind 6;
--a占位符 不可以特殊符号
dbms_output.put_line(v_a.name||','||v_a.job||v_a.age);--按照顺序往里传值
----------------------------------------------------------
v_sql:='select * from a where id=6';
execute immediate v_sql into v_a;
dbms_output.put_line(v_emp.ename||','||v_emp.job);
v_sql:='select * from a where id=:a' ;
b:=' and age=:b'; --注意空格
execute immediate v_sql || b into v_a using 6,18;
dbms_output.put_line(v_a.name||','||v_.job||v_a.age);
end;
-----DDL----------------------------------------
declare
begin
execute immediate 'insert into a values (:1) using 799';
execute immediate 'update a set sal=:1 where id=:2' using n,799 ;
execute immediate 'delete from a where id=:1' using 99;
execute immediate 'create table t(id number(11),name varchar2(20))';
for v in 1..10 loop
execute immediate 'insert into a values (:1) using v';
end loop;
end;三、异常
在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
系统异常
预定义异常和非预定义异常。
自定义异常
declare
声明部分;
begin
plsql代码块;
exception
异常处理部分;
end;预定义异常
Oracle定义了它们的错误编号和异常名字，
常见的预定义异常处理如下:
ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常
CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发
COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合
CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发
INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发
INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发
LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正解的用户名和口令时会触发
NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包
ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发
SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发
STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发
SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发
SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发
SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发
TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发
TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发
VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发
ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发begin
exception
when 异常名称 then
异常处理代码;
...
when others then
异常处理代码;
end;
exception:出现在end前面
others其他所有的异常
------------------------------
declare
v a.name%type;
begin
select name into v from a where 1=0;
exception
when no_data_found then --NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
dbms_output.put_line('nono');
when others then
dbms_output.put_line('就是错了'||sqlcode); --就是错了-Oracle错误编码
dbms_output.put_line('就是错了'||sqlerrm); --就是错了-Oracle错误信息
end;
SQLCODE与SQLERRM为异常处理函数。
函数SQLCODE用于取得Oracle错误号，
函数SQLERRM用于取得与错误号对应的相关错误消息非预定义异常
ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。
pragma exception_init(自定义的异常,编号);
declare
myexc exception; --声名异常变量
pragma exception_init(myexc,-00001); --绑定异常
begin
--delete a where id =10;
--exeception
--when myexc then
--dbms_output.put_line('nonono');
begin
insert into a(id,age) values(799,20);
--commit;
exception
when myexc then
--rollback;
dbms_output.put_line('违反唯一约束');
end;
dbms_output.put_line('-----');
end;自定义异常
程序员从业务角度出发，制定的一些规则和限制。
异常名称 exception;
抛出异常：
raise 异常名称;
exception 捕获异常 处理异常
declare
myexcp exception; --定义一个自己的异常
m number(10):=&m;
n number(10):=&n;
--声名两个变量接收两个数
begin
if n=0 then
dbms_output.put_line('除数不能为零');
raise myexcp;--抛出异常 需要处理异常
end if;
dbms_output.put_line(m/n);
end;引发应用程序错误
RAISE_APPLICATION_ERROR 过程
用于创建用户定义的错误信息
可以在可执行部分和异常处理部分使用
错误编号必须介于 –20000 和 –20999 之间
RAISE_APPLICATION_ERROR(error_number, error_message);
declare
myexc exception;
pragma exception_init(myexc,-20001);
begin
dbms_standard.raise_application_error(-20001,'我的异常');
exception
when myexc then
dbms_output.put_line('1111111111');
end;
1429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
