PL/SQL编程（高级特性）

PL/SQL编程（高级特性）2009年04月09日 星期四 11:20--set serveroutput On--打开显示模式
declare
type cust_record_type is record(--定义记录类型
customer_name customer.customer_name%type,--声明标量变量
customer_status customer.customer_status%type--声明记录变量
);
cust_record cust_record_type;
begin
select a.customer_name ,a.customer_status into cust_record from customer a Where Rownum=1 ;
--where a.customer_id=b.customer_id and b.ord_id=&id;
dbms_output.put_line('客户名:'||cust_record.customer_name);
dbms_output.put_line('状态:'||cust_record.customer_status);
end;


/*********************************************************

Author:qinyangzhao
describe:%rowtype属性(rowtype)

*********************************************************/
declare
product_record product%rowtype;
begin
product_record.product_id:=&id;
product_record.description:='&description';
insert into product values product_record;
end;
/*********************************************************

Author:qinyangzhao
describe:索引表(table)

*********************************************************/
declare
type item_table_type is table of item.ename%type
index by pls_integer;
item_table item_table_type;
begin
select * bulk collect into item_table(-1)
from item where ord_id=&id;
dbms_output.put_line('条款编号:'||item_table(-1));

end;

/*********************************************************

Author:qinyangzhao
describe:嵌套表(table)

*********************************************************/
declare
type item_table_type is table of item.ename%type;
item_table item_table_type;
begin
item_table:=item_table_type('mary','mary','mary');
select ename into item_table(2) from item
where empno=&no;
dbms_output.put_line('雇员名:'||item_table(2));
end;
/*********************************************************

Author:qinyangzhao
describe:变长数组(array)

*********************************************************/
declare

type name_array_type is varray(20) of varchar2(30);
type city_array_type is varray(20) of varchar2(30);
name_array name_array_type;
city_array city_array_type;
begin
select name ,city bulk collect
into name_array,city_array from customer;
for i in 1..name_array.count loop
dbms_output.put_line('客户名:'||name_array(i)||',所在城市:'||city_array(i));
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:记录表(table)

*********************************************************/
declare
type item_table_type is table of item%rowtype
index by pls_integer;
item_table item_table_type;
begin
select * bulk collect into item_table
from item where ord_id=&id;
for i in 1..item_table.count loop
dbms_output.put_line('条款编号:'||item_table(i).item_id||',总价:'||
item_table(i).total);
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:多级(varray)

*********************************************************/
declare
type al_varray_type is varray(10) of int;--定义一维varray
type nal_varray_type is varray(10) of al_varray_type;--定义二维varrary集合
--初始化二维集合变量
nvl nal_varrary_type:=nal_varrary_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_arrary_type(2,4));
begin
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nvl.count loop
for j in 1..nvl(i).count loop
dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));
end loop;
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:多级(嵌套)

*********************************************************/
declare
type al_table_type is table of int;--定义一维嵌套表
type nal_table_type is table of al_table_type;--定义二维嵌套表集合
--初始化二维集合变量
nvl nal_varrary_type:=nal_varrary_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_arrary_type(2,4));
begin
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nvl.count loop
for j in 1..nvl(i).count loop
dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));
end loop;
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:多级(索引表)

*********************************************************/
declare
type al_table_type is table of int
index by binary_integer;--定义一维table
type nal_table_type is table of al_table_type
index by binary_integer;--定义二维table集合
nvl nal_varrary_type;
begin
--初始化二维集合变量
nvl(1)(1):=10;
nvl(1)(2):=5;
nvl(2)(1):=32;
nvl(2)(2):=88;
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nvl.count loop
for j in 1..nvl(i).count loop
dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));
end loop;
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:处理多行查询语句

*********************************************************/
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
emp_record emp%rowtype;
sql_stat varchar2(100);
begin
sql_stat:='select * from emp where deptno:=dno';
open emp_cv for sql_stat using &dno;
loop
fetch emp_cv into emp_record;
exit when emp_cv%notfound ;
dbms_output.put_line('雇员名:'||emp_record.ename||',工资:'||emp_record.sal);
end loop;
close emp_cv;
end;
/*********************************************************

Author:qinyangzhao
describe:使用bulk子句处理dml语句返回子句

*********************************************************/
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
type sal_table_type is table of emp.sal%type
index by binary_integer;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
begin
sql_stat:='update emp set sal=sal*(1+:percent/100)'
||'where deptno=:dno'
||'returning ename,sal into :name,:salary';
execute immediate sql_stat using &percen ,&dno returning bulk collect into ename_table,sal_table;
for i in 1..ename_table.count loop
dbms_output.put_line('雇员:'||ename_table(i)
||',的新工资为'|| sal_table(i));
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:使用bulk子句处理多行查询

*********************************************************/
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(100);
begin
sql_stat:='select ename from emp where deptno+:dno';
execute immediate sql_stat bulk collect into ename_table using &dno;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
end;
/*********************************************************

Author:qinyangzhao
describe:在fetch语句中使用bulk子句

*********************************************************/
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp ;
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(100);
begin
sql_stat:='select ename from emp where job:title';
open emp_cv for sql_stat using '&job';
fetch emp_cv bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cv;
end;
/*********************************************************

Author:qinyangzhao
describe:在forall语句中使用bulk子句

*********************************************************/
declare
type ename_table_type is table of emp.ename%type;
type sal_table_type is table of emp.sal%type;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
begin
ename_table:=name_table_type('scott','smith','clark');
sql_stat:='update emp set sal=sal*1.1 where ename=:1'
||'returning sal into :2';
forall i in 1..ename_talbe.count
execute immediate sql_stat using ename_table(i)
returing bulk collect into sal_table ;
for j in 1..ename_table.count loop
dbms_output.put_line('雇员'||ename_table(j)
||'的新工资为'||sal_table(j));
end loop;
end;