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