易强xi

Oracle学习笔记之存储过程

                                                                                                                                             存储过程

在Oracle中,可以在数据库中定义子程序,这种程序块称为存储过程(procedure)。它存放在数据字典中,可以在不同用户和应用程序之间共享,并可实现程序的优化和重用。使用存储过程的优点是:

(1)      过程在服务器端运行,执行速度快。

(2)      过程执行一次后代码就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译代码执行,提高了系统性能。

(3)      确保数据库的安全。可以不授权用户直接访问应用程序中的一些表,而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程,否则就不能访问这些表。

(4)       自动完成需要预先执行的任务。过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务。

注意:在用户的定义中不能使用下列对象创建语句:

CREATE  VIEW、CREATE  DEFAULT 、CREATE  RULE   、CREATE  PROCEDURE 、CREATE  TRIGGER

SQL命令创建存储过程:

语法格式:

         CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name  如下通过实例描述存储过程结构和写法:

例如:[A1] 

写一个存储过程:

例如:该存储过程根据人以部门编号,查询该部门中员工工资以及员工编号,如果如果员工工资低于该部门平均工资,此时将该员工工资涨薪为平均工资;

例如:上面题加一个条件,张薪资以后,将涨薪资的人员信息以游标形式返回;

 

例如:写一个存储过程将emp中empno和ename查询结果以out类型游标返回;

包中声明游标:

createorreplacepackage t is

   type record_type isrecord(empno number,ename varchar2(20));

   type cursor_type isrefcursorreturn record_type; 

end t;

存储过程代码:

createorreplaceprocedure myProcedure(cur_out out t.cursor_type)is

begin

    open cur_out forselect empno,ename from emp;

end myProcedure;

测试代码:

-- Created on 2017/10/19 by ADMINISTRATOR

declare

    type cur_type isrefcursor;-- 弱类型游标

     cur   cur_type;

     type record_type isrecord(empno number,ename varchar2(20));

     employee record_type;

begin

   myprocedure(cur);

   loop

      fetch cur into employee;

      dbms_output.put_line(employee.ename||':'||employee.empno);

      exitwhen cur%notfound;

   endloop; 

end;

实例2:

方法:

create or replace function login(username in varchar2,pwd in varchar2) return number is

   res number(5);

   flag number(1):=0;

begin

    select count(*) into res from emp where ename=username and empno=pwd;

    if res>0 then

        flag:=1;

    end if;

  return flag;

end login;

存储过程:

create or replace procedure EmpProcedure(username in varchar2,pwd in varchar2,controlFlag in varchar2,

cur_emp out pac.cur_type1,cur_dpet out pac.cur_type2

) is

  flag number(1);

 

 

begin

     flag:=login(username,pwd);--验证用户是否登陆成功

     if flag=1 --登陆成功

       then

           --判断controlFlag   1 查看该员工信息  2 查看emp表数据  3 查看该员工部门信息

            case controlFlag

              when 1 then  --查看该员工信息

                   open cur_emp for

                  select * from emp where empno=pwd;

                 

              when 2 then

                   open cur_emp for

                   select * from emp;

                    

              when 3 then

                  open cur_dpet for

                  select * from dept where deptno=(select deptno from emp where emp.empno=pwd);

                  

             end case;

             

      

       else--登陆失败

       dbms_output.put_line('登陆失败');

          

      

      end if;

      

 

end EmpProcedure;

包:

 type r is record(empno number(5),ename varchar2(20),job varchar2(20),mgr number(5),hiredate date,sal number(15,2),comm number(15,2),deptno number(5));

  type cur_type1 is ref cursor return r;

 

  type p is record(deptno number(5),dname varchar2(20),loc varchar2(20));

  type cur_type2 is ref cursor return p;

测试:

-- Created on 2017/10/21 by ADMINISTRATOR

declare

   type r is record(empno number(5),ename varchar2(20),job varchar2(20),mgr number(5),hiredate date,sal number(15,2),comm number(15,2),deptno number(5));

  type cur_type1 is ref cursor ;--注意不能声明为return 强制类型

 

  type p is record(deptno number(5),dname varchar2(20),loc varchar2(20));

  type cur_type2 is ref cursor;

  cur1 cur_type1;

  cur2 cur_type2;

 

  res_1 r;

  res_2 p;

  flag number(1):=2;

 

begin

    empprocedure('SMITH','123',flag,cur1,cur2);

         if flag=1 or flag=2 then

                loop

                  fetch cur1 into res_1;

                  exit when cur1%notfound;

                  dbms_output.put_line(res_1.empno||res_1.ename||res_1.hiredate);

             

                end loop;

         elsif flag=3 then

                loop

                    fetch cur2 into res_2;

                    exit when cur2%notfound;

                    dbms_output.put_line(res_2.deptno||res_2.dname||res_2.loc);

               

                end loop;

        end if;

       Exception  when others then dbms_output.put_line('系统异常');

    

end;

 

305上课实例:

包:

create or replace package empdeptPac is

 

  type cur_empType is ref cursor return emp%rowtype;

  type cur_deptType is ref cursor return dept%rowtype;

 

 

end empdeptPac;

函数:

create or replace function emplogin(username in varchar2,pwd in varchar2) return boolean is

   total number(10);

   flag boolean :=false;

begin

  select count(*) into total from emp where ename =username and empno=pwd;

  if total>0 then

    flag:=true;

   else flag:=false;

   end if;

  return flag;

end emplogin;

存储过程:

create or replace procedure empdeptpro(username in varchar2,pwd in varchar2,flag in varchar2,

empitems out empdeptpac.cur_empType, deptitems out empdeptpac.cur_deptType, empres out emp%rowtype

) is

f boolean ;

begin

      f:=emplogin(username,pwd);

      if f then

           case flag

             when 'A' then--查询所有员工信息

               open  empitems for select * from emp;

            

             when 'B' then--查询所在部门信息

                open   deptitems for select dept.* from emp,dept where dept.deptno=emp.deptno and emp.empno=pwd;

            

             when 'C' then--查询个人信息

                select emp.* into empres from emp where emp.empno=pwd;

             end case;

     

     

      end if;

 

end empdeptpro;

测试:

-- Created on 2017/11/4 by ADMINISTRATOR

declare

   type curtype_dept is ref cursor;

   type curtype_emp is ref cursor ;

   cur_dept curtype_dept;

   cur_emp curtype_emp;

   empRow emp%rowtype;

begin

   empdeptpro('SMITH',7369,'A',cur_emp,cur_dept,empRow);

   loop

     fetch cur_emp into empRow;

     dbms_output.put_line(empRow.empno);

     exit when cur_emp%notfound;

   end loop;

  

 

end;

 

                               


 

①    IN:表示参数是输入给过程的;

②    OUT:表示参数在过程中将被赋值,可以传给过程体的外部;

③    IN OUT:表示该类型的参数既可以向过程体传值,也可以在过程体中赋值。

 

posted on 2017-10-23 16:46  易强xi  阅读(312)  评论(0编辑  收藏  举报

导航