ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于存储过程的数据块

基于存储过程的数据块

原创 Linux操作系统 作者:Beyond518 时间:2009-04-27 14:46:42 0 删除 编辑

基于存储过程的数据块

先建一个包.

--包头

create or replace package emp_pkg is

  -- Public type declarations

  type emp_record_type is record(

                                  empno     emp.empno%type,

                                  ename     emp.ename%type,

                                  job       emp.job%type,

                                  mgr       emp.mgr%type,

                                  hiredate  emp.hiredate%type,

                                  sal       emp.sal%type,

                                  comm      emp.comm%type,

                                  deptno    emp.deptno%type

                                 );

                                

  type emp_ref_cursor is ref cursor return emp_record_type;

 

  type emp_table_type is table of emp_record_type index by binary_integer;

 

  -- Public function and procedure declarations

  procedure emp_query(dmlset in out emp_ref_cursor,p_ename in emp.ename%type) ;

  procedure emp_lock(dmlset in out emp_table_type);

  procedure emp_insert(dmlset in out emp_table_type);

  procedure emp_update(dmlset in out emp_table_type);

  procedure emp_delete(dmlset in out emp_table_type);

 

end emp_pkg;

 

--包体

create or replace package body emp_pkg is

 

-- Function and procedure implementations

-- 参数 p_ename 用于在表单查询时作为参数.否则表单在查询时输入ename值不起作用.

  procedure emp_query(dmlset in out emp_ref_cursor,p_ename in emp.ename%type) is

  begin

     open dmlset for

          select empno,ename,job,mgr,hiredate,sal,comm,deptno

          from emp

          where ename like p_ename || '%';

  end;

 

  procedure emp_lock(dmlset in out emp_table_type) is

     empno_holder number;

  begin

     FOR emp_ct IN 1..dmlset.count

     LOOP

       select empno into empno_holder

       from emp

       where empno = dmlset(emp_ct).empno for update;

     END LOOP;

  end;

 

  procedure emp_insert(dmlset in out emp_table_type) is

     v_message varchar(100);

  begin

     FOR emp_ct IN 1..dmlset.count

     LOOP

         v_message := 'Insert of EMP';

         insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

         values (dmlset(emp_ct).empno,dmlset(emp_ct).ename,dmlset(emp_ct).job,dmlset(emp_ct).mgr,dmlset(emp_ct).hiredate,dmlset(emp_ct).sal,dmlset(emp_ct).comm,dmlset(emp_ct).deptno);

     END LOOP;

     exception

          when others then

              raise_application_error(-20002,'Error: Insertion record' ||

                  'using EMP_PKG.EMP_INSET. Inform. Technical Support. ' ||

                  v_message);

         

  end;

 

  procedure emp_update(dmlset in out emp_table_type) is

     v_message varchar(100);

  begin

     FOR emp_ct IN 1..dmlset.count

     LOOP

         v_message := 'Update of EMP';

         update emp set

              ename = upper(dmlset(emp_ct).ename),

              job = upper(dmlset(emp_ct).job),

              mgr = dmlset(emp_ct).mgr,

              hiredate = dmlset(emp_ct).hiredate,

              sal =dmlset(emp_ct).sal,

              comm = dmlset(emp_ct).comm,

              deptno = dmlset(emp_ct).deptno

         where empno = dmlset(emp_ct).empno;

     END LOOP;

     exception

          when others then

              raise_application_error(-20002,'Error: Updating record' ||

                  'using EMP_PKG.EMP_UPDATE. Inform. Technical Support. ' ||

                  v_message);

  end;

 

  procedure emp_delete(dmlset in out emp_table_type) is

     v_message varchar(100);

  begin

     FOR emp_ct IN 1..dmlset.count

     LOOP

        v_message := 'Delete of EMP';

        delete from emp where empno = dmlset(emp_ct).empno;

     END LOOP;

     exception

          when others then

              raise_application_error(-20002,'Error: deleting record' ||

                  'using EMP_PKG.EMP_DELETE. Inform. Technical Support. ' ||

                  v_message);

  end;

 

end emp_pkg;

 

 

建立数据块

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.JPG

2.JPG

3.JPG

4.JPG

5.JPG

6.JPG

7.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/143412/viewspace-591930/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 你是不是没电了
下一篇: Oracle OPM 成本
请登录后发表评论 登录
全部评论

注册时间:2009-01-16

  • 博文量
    5
  • 访问量
    12306