原文:http://blog.csdn.net/caoshichaocaoshichao/article/details/5349576
- 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;