oracle入门12

存储过程

   存储过程是一种命名pl/sql程序块,它可以被赋予参数,存储在数据库中,可以被用户调用.由于存储过程是已编译好的代码,所以在调用的时候不必再次编译代码，从而提高程序的运行效率。另外存储过程可以实现程序的模块化设计.

 

1、  创建存储过程

语法：

  Create [or replace]procedure procedure_name

 [ (parameter[{in|inout}]) data_type，

  (parameter[{in|inout}]) data_type，

  ……

 ]

{ is|as}

 Decoration section

Begin

   Executablesection;

Exception

   Exceptionhandlers;

End;

Procedure_name存储过程的名称

Parameter 参数

In 向存储过程传递参数

Out：从存储过程返回参数

In out:传递和返回参数

Data_type:参数的类型  不能够指明长度

As|is后声明的变量主要过程体内,且不能加declare语句。

 

//创建一个插入emp中记录的存储过程

SQL> create procedureinsert_emp as

  2 begin

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

  4   values('7777','wangshuo','teacher','7369',9000,1000,20);

  5   commit;

  6  endinsert_emp;

  7  /

 

 

 

2、  调用存储过程

 

SQL> set serveroutput on;

SQL> begin

  2    insert_emp;

  3  end;

  4  /

 

3、  修改存储过程

 

SQL> create or replaceprocedure insert_emp as  //修改时只需加 or replace就可以了 里边的存储过程就可以修改了

  2 begin

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

  4   values('7777','wangshuo','teacher','7369',9000,1000,20);

  5   commit;

  6  endinsert_emp;

  7 

  8  /

4、  参数

Oracle中有三种参数模型 in out 和in out

1、  in参数

该类型的参数值有调用者传入,并且只能被存储过程读取,也是默认格式.

案例：

SQL> create or replaceprocedure insert_emp(

  2   cempno in number,

  3   cename in varchar2,

  4   cjob in varchar2,

  5   cmgr in number,

  6   chiredate in date,

  7   csal in number,

  8   ccomm in number,

  9   cdeptno in number

 10  ) as

 11 begin

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

 13   values(cempno,cename,cjob,cmgr,chiredate,csal,ccomm,cdeptno);

 14  endinsert_emp;

 15  /

 Procedure created

 

上面创建的存储过程需要出入参数,在oralce有如下三种方式传入参数

1、1    名称表示法

语法如下：

  参数名称=>参数值;多个之间用逗号隔开

SQL> set serveroutput on;

SQL> begin

  2    insert_emp(cempno=>7377,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);

  3  end;

  4  /

 

1、2    位置表示法

当参数比较多时,名称表示法可能会比较长,为克服名称表示法的弊端,可以采用位置表示法,注意参数一定要对应。

SQL> set serveroutput on;

SQL> begin

  2    insert_emp(3333,'mjjj','stu',7777,to_date('2011-01-01','YYYY-MM-dd'),5000,1000,20);

  3  end;

  4  /

 

 

1、3    混合表示法

 

SQL> set serveroutput on;

SQL> begin

  2    insert_emp(9999,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);

  3  end;

  4  /

注意：当用户使用的混合表示法时,分界线之前必须一致,分界线之后必须一致,并且不能穿插。

 

2、  out参数

    该类型的参数值是有存储过程写入.out类型的参数适用于存储过程向调用者返回多条信息的情况。

//创建一个根据员工编号查询员工名称及薪资存储过程

SQL> create or replaceprocedure emp_select(cempno in number,

  2     cename out emp.ename%type,

  3     csal  out emp.sal%type

  4  ) is

  5 begin

  6     select ename,sal into cename,csal from emp where empno=cempno;

  7 exception

  8    when NO_DATA_FOUND then

  9    cename:='NULL';

 10    csal:=0;

 11  endemp_select;

 12  /

 Procedure created

  调用存储过程：

  out输出的参数是返回值,也就说在调用存储过程的时候必须有提供能够接受返回值的变量。

在这里我们需要使用variable命令绑定参数

SQL> variable ename varchar2(20);//绑定参数的声明

SQL> variable sal number;

SQL> begin

  2    emp_select('7777',:ename,:sal); //执行存储过程

  3  end;

  4  /

 

PL/SQL procedure successfullycompleted

ename

---------

wangshuo

sal

---------

9000

 

SQL> print ename;  //打印相应的参数

ename

---------

wangshuo

 

SQL>print sal;  //打印相应的参数

sal

---------

9000

 

3、  in out参数

in参数可以接收一个值,但是不能在存储过程中修改这个值,而对于out参数,它在调用过程时为空,在过程执行中将为为这个参数指定一个值，并在执行后返回.

而in out参数同时具有了in参数和out参数的特性,在过程中可以读取和写入该类型的参数。

 

//作业实现 案例交换两个数

 

 

 

   

//在存储过程|局部变量 和子过程

//函数