oracle plsql例子,oracle plsql 存储过程常用例子

Author:qinyangzhao

describe:PL/SQL记录记录 (type is record)

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

set serveroutput on--打开显示模式

declare

type cust_record_type is record(--定义记录类型

name customer.name%type,--声明标量变量

total ord.total%type--声明记录变量

);

cust_record cust_record_type;

begin

select a.name ,b.total ,into cust_record

from customer a ,ord b

where a.customer_id=b.customer_id and b.ord_id=&id;

dbms_output.put_line('客户名'||cust_record.name);

dbms_output.put_line('订单总额:'||cust_record.total);

end;

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

Author:qinyangzhao

describe:%rowtype属性(rowtype)

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

delcare

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;