--案例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;
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'));
