PL/SQL编程（高级特性）

下文为plsql编程中高级特性的应用实例。以供大家在编程时参考。

sql 代码

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

   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;

分享到：

SQL PLUS命令的使用大全 | web开发中如何调试ajax（图解全过程）

• 2007-09-01 08:59
• 浏览 1700
• 评论(0)
• 论坛回复 / 浏览 (0 / 7205)
• 分类:数据库
• 查看更多