Oracle 存储过程入门

一，基本入门介绍

看创建存储过程的基本定义。注意，带有[]的都是可选的，可有可无的。只是语法能通过，当然根据自己需要
处理。

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

create table students
( 
  ID int,
  userName varchar(100),
  userPass varchar(100),
  userAge  int
)

insert into students values(1,'jack','jjjaa',23);
insert into students values(2,'rose','jjjaa',21);
insert into students values(3,'lucy','jjjaa',22);
insert into students values(4,'Tony','jjjaa',24);
commit;

这里我们新建一个存储过程，对于某个用户添加年龄，哈哈，当然这个是没什么意义的，学习，从简单入手。
在实际开发中，语法，原理是一样的。

create or replace procedure  SP_Update_Age
(
 uName in varchar,--note,here don't have length ,sql have lenth ,not in oracle.
 Age in int
)
as
begin
    update students set UserAge = UserAge + Age where userName = uName;
    commit;
end SP_Update_Age; 

在执行存储过程之前，我们先查看原来的数据。

select * from students


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

ID    USERNAME    USERPASS    USERAGE

1    jack            jjjaa        23
2    rose            jjjaa        21
3    lucy            jjjaa        22
4    Tony            jjjaa        24

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

然后我们在SQL Editor中执行如下存储过程。注意看是怎么调用的：

exec SP_UPDATE_AGE('jack',1);

select * from students;

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

ID    USERNAME    USERPASS    USERAGE

1    jack    jjjaa    24  --noted,have changed 
2    rose    jjjaa    21
3    lucy    jjjaa    22
4    Tony    jjjaa    24

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

二，基本语法介绍

1，变量赋值
 变量名 := 值;

2，判断语句。
if
比较式
then
begin
end;
end
if

create or replace procedure Test(x in out number)
is
begin
     if x<0 then
         begin
             x:= 0 - x;
        end;
     elsif x > 0 then     --noted here elsif 
         begin
             x:= x ;
        end;
     else
        x:=    0;
     end if;
end Test;

set serveroutput on;  --没这句话，看不到dmbs_output信息。
declare
       num number;
begin
    num:= -1;
    test(num);
    dbms_output.put_line( 'num = ' || num );
end;
/******************************
num = 1
PL/SQL procedure successfully completed.
*******************************/

3，For循环，
For  in ..loop;

set serveroutput on;
DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP  --noted here 
      IF MOD(i,2) = 0 THEN     -- i is even
          dbms_output.put_line( 'i: '||i||' is even ' );
      ELSE
          dbms_output.put_line('i: '|| i||' is odd' );
      END IF;
      x := x + 100;
      dbms_output.put_line('x value: '|| x);
   END LOOP;
   COMMIT;
END;

/*************************
i: 1 is odd
x value: 200
i: 2 is even 
x value: 300
i: 3 is odd
x value: 400
i: 4 is even 
x value: 500
i: 5 is odd
x value: 600
i: 6 is even 
x value: 700
i: 7 is odd
x value: 800
i: 8 is even 
x value: 900
i: 9 is odd
x value: 1000
i: 10 is even 
x value: 1100
PL/SQL procedure successfully completed.


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

4，While 循环。

create or replace Procedure Test2(i in out number)
as
begin
     while i < 10 loop
            begin
                   i:= i+1;
           end;
          end loop;
end Test2;

set serveroutput on;
declare
       num number;
begin
    num:= 1;
    test2(num);
    dbms_output.put_line( 'num = ' || num );
end;


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

num = 10
PL/SQL procedure successfully completed.

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

Oracle存储过程

1、定义

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集
经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的
存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。

2、存储过程的创建

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

（1）无参存储过程语法
create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;

（2）带参存储过程实例
create or replace procedure queryempname(sfindno emp.empno%type)   
as  
   sName emp.ename%type;  
   sjob emp.job%type;  
begin  
       ....  
exception  
       ....  
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表示该参数可以向该过程中传递值，也可以将某个值传出去。

（4）存储过程中游标定义使用
as //定义(游标一个可以遍历的结果集)   
CURSOR cur_1 IS   
  SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,  
         SUM(usd_amt)/10000 usd_amt_sn   
  FROM BGD_AREA_CM_M_BASE_T   
  WHERE ym >= vs_ym_sn_beg   
       AND ym <= vs_ym_sn_end   
  GROUP BY area_code,CMCODE;   
      
begin //执行（常用For语句遍历游标）       
FOR rec IN cur_1 LOOP   
  UPDATE xxxxxxxxxxx_T   
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn   
   WHERE area_code = rec.area_code   
   AND CMCODE = rec.CMCODE   
   AND ym = is_ym;   
END LOOP; 

（5）游标的定义
--显示cursor的处理
declare  
---声明cursor,创建和命名一个sql工作区
cursor cursor_name is  
    select real_name from account_hcz;
    v_realname varchar2(20);
begin 
    open cursor_name;---打开cursor,执行sql语句产生的结果集
    fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
    dbms_output.put_line(v_realname);
    close cursor_name;--关闭cursor
end;

3、在Oracle中对存储过程的调用 
（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）过程调用方式二
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）过程调用方式三（SQL命令行方式下）
1、SQL>exec  proc_emp('参数1','参数2');//无返回值过程调用  
2、SQL>var vsal number  
     SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  
      或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用