Oracle存储过程语法

Oracle存储过程语法


Oracle存储过程基本语法 存储过程  　　1 CREATE OR REPLACE PROCEDURE 存储过程名  　　2 IS  　　3 BEGIN  　　4 NULL;  　　5 END; 
行1:  　　CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;  行2:  　　IS关键词表明后面将跟随一个PL/SQL体。  行3:  　　BEGIN关键词表明PL/SQL体的开始。  行4:  　　NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;  行5:  　　END关键词表明PL/SQL体的结束  存储过程创建语法：  create or replace procedure 存储过程名（param1 in type，param2 out type）  as  变量1 类型（值范围）; --vs_msg VARCHAR2(4000);  变量2 类型（值范围）; 


复制代码代码如下:
Begin  Select count(*) into 变量1 from 表A where列名=param1； 
If (判断条件) then  Select 列名 into 变量2 from 表A where列名=param1；  Dbms_output。Put_line(‘打印信息');  Elsif (判断条件) then  Dbms_output。Put_line(‘打印信息');  Else  Raise 异常名（NO_DATA_FOUND）;  End if;  Exception  When others then  Rollback;  End; 


注意事项： 
1， 存储过程参数不带取值范围，in表示传入，out表示输出 
类型可以使用任意Oracle中的合法类型。 
2， 变量带取值范围，后面接分号 
3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录 
4， 用select 。。。into。。。给变量赋值 
5， 在代码中抛异常用 raise+异常名 


一，基本入门介绍


公司系统需要用到oracle,可是还没在项目用过oracle,好吧，从基本学起。有问题的地方，欢迎指导啊。


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


还是从简单例子开始学习，




复制代码
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]


IS
    [declaration_section]


BEGIN
    executable_section


[EXCEPTION
    exception_section]


END [procedure_name];
复制代码
 


先初始化数据。我用的是Toad工具，下面的代码是在SQL Editor 中执行的。




复制代码
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;
复制代码
 


当然，新建存储过程是需要在Procedure Editor中编写，但是执行存储过程又需要在SQL Editor中去执行，Procedure中是不可以执行(like exec的语句)的。


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




复制代码
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;
复制代码
 


 


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.


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