--案例01：简单测试
declare
?? type studenttabletype is table of varchar(10) index by binary_integer;
?? stu1? studenttabletype ;
begin
?? stu1(1):='tom';
?? stu1(2):='wind';
?? dbms_output.put_line('第一个元素是：' ||stu1(1));
?? dbms_output.put_line('第二个元素是： '||stu1(2));
end;
--案例02：通过循环测试PL/SQL表
declare
?? type studenttabletype is table of varchar(10) index by binary_integer;
?? stu1? studenttabletype ;
begin
?? stu1(1):='tom';
?? stu1(2):='wind';
?? for i in 1..10 loop
????? stu1(i):='number'||i;
??? end loop;
for i in 1..10 loop
??? dbms_output.put_line('第'||i||'元素是'||stu1(i));
??? end loop;
end;
?
--案例03：测试PL/SQL表的删除行01
declare
? type stunametabletype is table of varchar(10) index by binary_integer;
? stunametable stunametabletype;
begin
?? stunametable(2):='10';
?? dbms_output.put_line('stunametable' ||stunametable(2));
?? stunametable(6):='10';
?? dbms_output.put_line('stunametable' ||stunametable(6));
?? stunametable.delete(6);
?? dbms_output.put_line('stunametable' ||stunametable(6));
end;
?
--案例04：测试PL/SQL表的插入单行01
?
/*第一步：新建表*/
create table pl001
(
sna? varchar2(10)
);
?
/*第二步：使用PL/SQL表*/
declare
? type stunametabletype is table of varchar2(10) index by binary_integer;
? stunametable stunametabletype;
begin
? stunametable(1):='&wn';
? insert into pl001(sna) values (stunametable(1));
? dbms_output.put_line('插入的值是：'||stunametable(1));
end;
/*
该案例可以测试PL/SQL表插入行但是效率不高，原因是如果pl01表中有多行呢？
则需要下面的案例
*/
?
--案例04：测试PL/SQL表的插入多行02
/*第一步：新建表*/
create table pl02
(
sid int,
sno int,
sname? varchar2(10)
);
/*第二步：使用PL/SQL表*/
declare
? type stunametabletype is table of pl02.sname%type index by binary_integer;
/*这里不使用标量类型使用的是标量变量数据类型*/
? stunametable stunametabletype;
begin
? stunametable(1):='&wn';
? insert into pl02(sname) values (stunametable(1));
? dbms_output.put_line('插入的值是：'||stunametable(1));
end;
--案例05：测试PL/SQL表的插入多行03
/*第一步：新建表*/
create table pl03
(
sid int,
sname? varchar2(10)
);
/*第二步：使用PL/SQL表*/
declare
? type stunametabletype is table of pl03.sname%type index by binary_integer;
/*这里不使用标量类型使用的是标量变量数据类型*/
? stunametable stunametabletype;
begin
? stunametable(1):='&w01n';
? stunametable(2):='&w02n';
? insert into pl03(sname) values (stunametable(1));
? insert into pl03(sname) values (stunametable(2));
? dbms_output.put_line('插入的值是：'||stunametable(1));
? dbms_output.put_line('插入的值是：'||stunametable(2));
end;
/*
如果在使用过程中，元对应元素没有赋值则会报对应的错误：
declare
? type stunametabletype is table of pl03.sname%type index by binary_integer;
/*这里不使用标量类型使用的是标量变量数据类型*/
? stunametable stunametabletype;
begin
? stunametable(1):='&w01n';
? insert into pl03(sname) values (stunametable(1));
? stunametable(2):='&w02n';
? insert into pl03(sname) values (stunametable(2));
? dbms_output.put_line('插入的值是：'||stunametable(2));
? dbms_output.put_line('插入的值是：'||stunametable(3));
end;
*/
--案例06：PL/SQL记录01
declare
? type sturecordtype is record
?(
id student.stuid%type,
name? student.stuname%type,
sex? student.sex%type
);
sturec sturecordtype;
begin
select stuid,stuname,sex into sturec from student
where stuid='1001';
dbms_output.put_line(sturec.id || '? ' ||sturec.name ||'' ||sturec.sex);
exception
? when no_data_found then
dbms_output.put_line('对不起，没有对应的数据！');
end;
/*
字段在记录变量定义的顺序必须和赋值的顺序一致否则会报错。
*/
--也可以如下赋值
?
--案例07：PL/SQL记录变量嵌套
declare
? type sturecordtype is record
?(
id student.stuid%type,
sex? student.sex%type,
pon? emp%rowtype?? --嵌套字段类型
);
sturec sturecordtype;
begin
sturec.id:='1001';
sturec.name:='wind';
sturec.sex:='男';
sturec.pon.empno:=9000? ;? --嵌套字段类型
sturec.pon.ename:='kite';
dbms_output.put_line(sturec.id || '? ' ||sturec.name ||'' ||sturec.sex);
dbms_output.put_line(sturec.pon.empno ||sturec.pon.ename);
exception
? when no_data_found then
dbms_output.put_line('对不起，没有对应的数据！');
end;
?
--案例08：批量绑定(oracle第一版不支持)
declare
? type studenttabletype is table of student%rowtype index by binary_integer;
? st1 studenttabletype;
begin
? select stuid,sex bulk collect into st1 from student;
? dbms_output.put_line('student:? '? || st1(1).stuid);
? dbms_output.put_line('student:? '? || st1(2).stuid);
? dbms_output.put_line('student:? '? || st1(3).stuid);
? dbms_output.put_line('student:? '? || st1(1).stuname);
? dbms_output.put_line('student:?? ' || st1(2).stuname);
? dbms_output.put_line('student:?? ' || st1(3).stuname);
? dbms_output.put_line('student:?? ' || st1(1).sex);
? dbms_output.put_line('student:?? ' || st1(2).sex);
? dbms_output.put_line('student:?? ' || st1(3).sex);
end;
--案例09：批量绑定:查看oracle变量中全部行数
declare
? type studenttabletype is table of student%rowtype index by binary_integer;
? st1 studenttabletype;
begin
? select stuid,sex bulk collect into st1 from student;
?? dbms_output.put_line('变量元素总数为：' || st1.count);
? dbms_output.put_line('student:? '? || st1(1).stuid);
? dbms_output.put_line('student:? '? || st1(2).stuid);
? dbms_output.put_line('student:? '? || st1(3).stuid);
? dbms_output.put_line('student:? '? || st1(1).stuname);
? dbms_output.put_line('student:?? ' || st1(2).stuname);
? dbms_output.put_line('student:?? ' || st1(3).stuname);
? dbms_output.put_line('student:?? ' || st1(1).sex);
? dbms_output.put_line('student:?? ' || st1(2).sex);
? dbms_output.put_line('student:?? ' || st1(3).sex);
end;
?
--案例10：成员函数和过程
--第一步：新建类型
create or replace type persontype as object
(
id int,
name? varchar2(20),
member function getid return int,
member procedure setid(pid int),
member function getname return varchar,
member procedure setname(pname varchar)
) not final;
--新建包体
create or replace type body persontype as
?member function getid return int
is
begin
?return id;
end getid;
member procedure setid(pid int) as --is or as
begin
?? id:=pid;
end setid;
member function getname return varchar is? --is or as
begin
? return name;
end getname;
member procedure setname(pname varchar) is? --is or as
begin
? name:=pname;
end setname;
end;
--新建测试01
declare
? p persontype;
begin
? p:=persontype(1001,'tom');
? dbms_output.put_line(p.id);
? dbms_output.put_line(p.getid);
? dbms_output.put_line(p.getname);
end;
?
--新建测试02
declare
? p persontype;
begin
? p:=persontype(1001,'tom');
? dbms_output.put_line(p.id);
? dbms_output.put_line(p.getid);
? dbms_output.put_line(p.getname);
? p.setid(2001);
? p.setname('kill');
?? dbms_output.put_line(p.getid);
? dbms_output.put_line(p.getname);
end;
?
--新建测试03
create table t10 of persontype;
insert into t10 values (2008,'snow');
insert into t10 values (persontype(2002,'peter'));
