oracle存储过程基础语句

可以用oracle已经存在的账号scott密码triger登陆进去用里面已存在的表来做试验。

create or replace procedure lpmtest2
as 
  para1 varchar2(10);
  cursor  youbiao is  select ename from test where sal>1300;
begin
  open youbiao;
  loop
    fetch youbiao into para1;
    exit when youbiao%notfound;
    dbms_output.put_line('++:'||para1);
  end loop;
  close youbiao;
end;--最后分号记得写，否则会报错。

 ======================================================================

create or replace procedure lpmtest2
as 
  cursor  youbiao is  select ename,sal,job from test where sal>1300;
  c_row youbiao%rowtype; --定义一个游标变量c_row ,该类型为游标youbiao中的一行数据类型
begin
  open youbiao;
  loop
    fetch youbiao into c_row;
    exit when youbiao%notfound;
    dbms_output.put_line('++:'||c_row.ename||':'||c_row.sal||':'||c_row.job);
  end loop;
  close youbiao;
end;

 =========================================================================

create or replace procedure lpmtest3
as
cursor c_dept is select * from dept order by deptno;
cursor c_emp(p_dept varchar2) is select ename,sal from emp where deptno=p_dept order by ename;
r_dept c_dept%rowtype;
v_ename emp.ename%type;
v_sal   emp.sal%type;
v_totalsal emp.sal%type; --用来存每个部门所有员工的总工资
begin
  open c_dept;
  loop
    fetch c_dept into r_dept;
    exit when c_dept%notfound;
    dbms_output.put_line(r_dept.deptno||':'||r_dept.dname||'+++++++++++');
    v_totalsal:=0;
    open c_emp(r_dept.deptno);
    loop
      fetch c_emp into v_ename,v_sal;
      exit when c_emp%notfound;
      dbms_output.put_line('v_ename:'||v_ename||';'||'v_sal:'||v_sal);
      v_totalsal:=v_totalsal+v_sal;
     end loop;
     close c_emp;
     dbms_output.put_line('deptsaltotal:'||v_totalsal);
   end loop;
   close c_dept;
end;

 打印出来效果：

10:ACCOUNTING+++++++++++
v_ename:CLARK;v_sal:2450
v_ename:KING;v_sal:5000
v_ename:MILLER;v_sal:1300
deptsaltotal:8750
20:RESEARCH+++++++++++
v_ename:ADAMS;v_sal:1100
v_ename:FORD;v_sal:3000
v_ename:JONES;v_sal:2975
v_ename:SCOTT;v_sal:3000
v_ename:SMITH;v_sal:800
deptsaltotal:10875
30:SALES+++++++++++
v_ename:ALLEN;v_sal:1600
v_ename:BLAKE;v_sal:2850
v_ename:JAMES;v_sal:950
v_ename:MARTIN;v_sal:1250
v_ename:TURNER;v_sal:1500
v_ename:WARD;v_sal:1250
deptsaltotal:9400
40:OPERATIONS+++++++++++
deptsaltotal:0

 ======================================================================

create or replace procedure lpmtest9(v_name varchar2)
is
v_num number:=1;
begin
  loop
     insert into lpm_user values(v_name,v_num);
     commit;
     exit when v_num=5;
     v_num:=v_num+1;
  end loop;
end;

 

=======================================================================

create or replace procedure lpmtest8(lpm_empno number)
is
lpm_job emp.job%type;
begin
  select job into lpm_job from emp where empno=lpm_empno; 
  if lpm_job='PRESIDENT' then
    begin
    update emp set sal=sal+500 where empno=lpm_empno;
    commit;
    end;
  elsif lpm_job='MANAGER' then  --是elsif不是elseif!要少个e
    begin
      update emp set sal=sal+300 where empno=lpm_empno;
      commit;
    end;
  else
    begin
    update emp set sal=sal+100 where empno=lpm_empno;
    commit;
    end;
  end if;
end;

 ========================================================

create or replace procedure lpmtest15(lpmEmpId emp.empno%type)
is 
addincrement emp.sal%type;
tempsal emp.sal%type;
begin
  select sal into tempsal from emp where empno=lpmEmpId;
  if tempsal>2000 then addincrement:=50;
  elsif tempsal>1500 then addincrement:=100;
  else addincrement:=150;
  end if;
  update emp set sal=sal+addincrement where empno=lpmEmpId;
  commit;
end;

 =================================================================

例：建立一个存储过程，将STUDENTS表中按学号指定的学生记录移至HISTORY表中。并在history表中增加移入日期 (删除STUDENTS表中的记录，同时录入到HISTORY表中)。
Create or replace procedure move( stuno varchar) is
Begin
/*将students中学号为stuno的记录插入到history表中*/
Insert into history(stu_id,name,sex,ldate) 
select stu_id,name,sex ,sysdate from students
where stu_id=stuno;
/*将students中学号为stuno的记录删除*/
delete from students
where stu_id=stuno;
end; 

 ========================================================================

现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment
通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。

create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begi
i := 1;
get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP 
fetch rsCursor into stdId,math,article,language,music,sport; 
exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;

for i in 1..commentArray.count   LOOP 
 record := commentArray(i);    
if stdId = record.stdId then  
 begin     
 if record.comment = 'A' then     
  begin         
 total := total + 20;   
    go to next; --使用go to跳出for循环       
  end;     
end if;  
end; 
end if; 
end LOOP;

average := total / 5;
update student t set t.total=total and t.average = average where t.stdId = stdId; 
end LOOP;

end;
end autocomputer;

--取得学生评论信息的存储过程
create or replace procedure get_comment(recommentArray out myPackage.myArray) is
rs         SYS_REFCURSOR； 
record    myPackage.stdInfo; 
stdId     varchar(30); 
comment  varchar(1); 
i          number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
 fetch rs into stdId,comment;
 exit when rs%NOTFOUND; 
record.stdId := stdId;
 record.comment := comment; 
recommentArray(i) := record; 
i:=i + 1; 
end LOOP;
end get_comment;

--定义数组类型myArray 
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;

 ======================================================================

1. 存储过程格式

/* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */

CREATE OR REPLACE procedure proc_trade(

  v_tradeid in number,                        --交易id

  v_third_ip in varchar2,                     --第三方ip

  v_third_time in date ,                      --第三方完成时间

  v_thire_state in number ,                   --第三方状态

  o_result out number,                       --返回值

  o_detail out varchar2                     --详细描述

)

as

-- 定义变量

  v_error varchar2(500);

begin

    --对变量赋值

     o_result:=0;

     o_detail:='验证失败';

 

    --业务逻辑处理

    if v_tradeid >100 then

        insert into table_name(...) values(...);

        commit;

    elsif v_tradeid < 100 and v_tradeid>50 then

        insert into table_name(...) values(...);

        commit;

    else

            goto log;

    end if;

--跳转标志符，名称自己指定

<<log>>

        o_result:=1;

--捕获异常

exception

   when no_data_found

   then

      result := 2;

   when dup_val_on_index

   then

      result := 3;

   when others

   then

      result := -1;

end proc_trade;

 

            在上面这个存储过程中使用了输入参数，并返回输出参数，这里的参数类型是我们自己指定的。 这种写法可行，但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。 这样就不会出现参数类型的错误。

 

如：

CREATE OR REPLACE PROCEDURE spdispsms (

   aempid      IN       otherinfo.empid%TYPE,

   amsg        IN       otherinfo.msg%TYPE,

   abillno     IN       otherinfo.billno%TYPE,

   ainfotype   IN       otherinfo.infotype%TYPE,

   aopid       IN       otherinfo.OPERATOR%TYPE,

   ainfoid     OUT      otherinfo.infoid%TYPE,

   RESULT      OUT      INTEGER

)

 

2. 存储过程中的循环

            存储过程写的是业务逻辑，循环是常用的处理方法之一。 

 

2.1  for ... in ... loop 循环

2.1.1：循环遍历游标

示例1：

CREATE OR REPLACE PROCEDURE proc_test

AS

   CURSOR c1

   IS

      SELECT   * FROM dat_trade;

BEGIN

   FOR x IN c1

   LOOP

      DBMS_OUTPUT.put_line (x.id);

   END LOOP;

END proc_test;

 

示例2：

CREATE OR REPLACE PROCEDURE proc_test

AS

BEGIN

   FOR x IN (SELECT   power_id FROM sys_power)

   LOOP

      DBMS_OUTPUT.put_line (x.power_id);

   END LOOP;

END proc_test;

 

2.1. 2：根据数值进行循环

示例1：

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

     for x in 1..100 loop

         dbms_output.put_line(x);

     end loop;

END proc_test;        

 

示例2：在过程里指定输入参数v_num. 在调用过程时指定循环次数。

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

   FOR x IN 1 .. v_num

   LOOP

      DBMS_OUTPUT.put_line (x);

   END LOOP;

END proc_test;        

 

 

2.2  loop 循环

   LOOP

      DELETE FROM orders

            WHERE senddate < TO_CHAR (ADD_MONTHS (SYSDATE, -3),

                                      'yyyy-mm-dd')

              AND ROWNUM < 1000;

 

      EXIT WHEN SQL%ROWCOUNT < 1;

      COMMIT;

   END LOOP;

 

这里的SQL%ROWCOUNT 是隐士游标。 除了这个，还有其他几个：%found，%notfound， %isopen。 

 

2.3  while 循环

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

   i   NUMBER := 1;

BEGIN

   WHILE i < v_num

   LOOP

      BEGIN

         i := i + 1;

         DBMS_OUTPUT.put_line (i);

      END;

   END LOOP;

END proc_test;

 

3. 存储过程中的判断

            判断也是存储过程中最常用的方法之一。

 

3.1  if ... elsif ... else ... 判断

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

   IF v_num < 10

   THEN

      DBMS_OUTPUT.put_line (v_num);

   ELSIF v_num > 10 AND v_num < 50

   THEN

      DBMS_OUTPUT.put_line (v_num - 10);

   ELSE

      DBMS_OUTPUT.put_line (v_num - 50);

   END IF;

END proc_test;

 

3.2  case ... when ... end case 判断

CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)

AS

BEGIN

    case v_num

        when 1 then

             DBMS_OUTPUT.put_line (v_num);

        when 2 then

            DBMS_OUTPUT.put_line (v_num);

        when 3 then

            DBMS_OUTPUT.put_line (v_num);

        else null;

    end case;   

END proc_test;

 

4. 游标

            存储过程中使用游标也是很常见的。 这里的游标分两种：

 

4.1  Cursor型游标(不能用于参数传递)

            这种方法具体参考 2.1.1：循环遍历游标 中的示例。 

 

4.2  SYS_REFCURSOR型游标

            该游标是Oracle以预先定义的游标，可作出参数进行传递。 

            注意一点：SYS_REFCURSOR只能通过OPEN方法来打开和赋值

 

4.2.1  我们可以使用这种类似的游标来返回一个结果集：

 

CREATE OR REPLACE procedure  proc_test(

checknum in number,  --每次返回的数据量

ref_cursor out sys_refcursor  --返回的结果集，游标

)

as 

begin

    open ref_cursor for select * from (select * from dat_trade where state=41 order by id) where rownum<checknum;

end proc_test;

/

 

SYS_REFCURSOR中可使用三个状态属性：

（1）.            %NOTFOUND(未找到记录信息) 

（2）.            %FOUND(找到记录信息) 

（3）.            %ROWCOUNT(然后当前游标所指向的行位置)

 


CREATE OR REPLACE PROCEDURE proc_test (

checknum     IN     NUMBER, --每次返回的数据量
ref_cursor      OUT sys_refcursor --返回的结果集，游标
)
AS
t_tmp   table_name%ROWTYPE;
BEGIN
   OPEN ref_cursor FOR
      SELECT   *

        FROM   (  SELECT   *

                    FROM   table_name

                   WHERE   state = 41
                ORDER BY   id)
       WHERE   ROWNUM < checknum;
--循环游标
   LOOP
      FETCH ref_cursor INTO   t_tmp;
      EXIT WHEN ref_cursor%NOTFOUND;
--    DBMS_OUTPUT.put_line (t_tmp.id);
      UPDATE   table_name

         SET   state = 53
       WHERE   id = t_tmp.id;
      COMMIT;
   END LOOP;

 

   CLOSE ref_cursor;
END proc_test;