Oracle存储

一、存储过程定义

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

二、存储过程的创建

1)无参存储过程语法:

create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;

2)带参存储过程实例
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

SQL_statements

END 存储过程名字

3）带参数存储过程含赋值方式

create or replace procedure runbyparmeters    

    (isal in emp.sal%type,   

     sname out varchar,  

     sjob in out varchar)  

 as   

    icount number;  

 begin  

      select count(*) into icount from emp where sal>isal and job=sjob;  

      if icount=1 then  

        ....  

      else  

       ....  

     end if;  

exception  

     when too_many_rows then  

     DBMS_OUTPUT.PUT_LINE('返回值多于1行');  

     when others then  

     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  

end;

详解:

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

三、存储组成语法

1.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

2.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;

3.while 循环和for循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

4.变量赋值
  V_TEST := 123;

5.Merge into

6.高级查询  row_number() over (partition by x order by y)

 

四.在Oracle中对存储过程的调用使用

1）调用 方式1

declare  

      realsal emp.sal%type;  

      realname varchar(40);  

      realjob varchar(40);  

begin   //过程调用开始  

      realsal:=1100;  

      realname:='';  

      realjob:='CLERK';  

      runbyparmeters(realsal,realname,realjob);－－必须按顺序  

      DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  

END;  //过程调用结束

 

2） 调用方式2

declare  

     realsal emp.sal%type;  

     realname varchar(40);  

     realjob varchar(40);  

begin    //过程调用开始  

     realsal:=1100;  

     realname:='';  

     realjob:='CLERK';  

     －－指定值对应变量顺序可变  

     runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);           

    DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  

END;  //过程调用结束

 

3）调用方式3(SQL命令行下)

 

1、SQL>exec  proc_emp('参数1','参数2');//无返回值过程调用  

2、SQL>var vsal number  

     SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  

      或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用