Oracle存储过程总结

1、存储过程：

　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

2、创建一个存储过程简要过程：

　　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体的结束。

3、存储过程创建语法：

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(‘打印信息’);
    Else if (判断条件) 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+异常名.

 4、oracle存储过程语法：

　　1.判断语句：if 比较式 then begin end; end if;

　　　　例：

create or replace procedure test1(x in number) as
  y integer(10, 4);
begin
  if x > 0 then
    y := 1;
  end if;
  if x < 0 then
    y := -1;
  end if;
  if x = 0 then
    y := 0;
  end if;
  DBMS_OUTPUT.put_line('y=' || y);
end test1;

 

plsql 中执行

declare param1 integer(10, 2);
begin
param1 := -2; test1(param1);
end;

执行结果：y=-1

　　2.for循环

　　For ... in ... LOOP 

　　-- 执行语句 

　　end LOOP; 

例：

create or replace procedure test2() as
  cursor testcursor is select uname from test_pro_users;
  name varchar(20);
begin
 for name in testcursor loop
   -- begin
      DBMS_OUTPUT.put_line('aa');
   -- end;
  end loop;
  commit;
end test2;

plsql中执行

begin
  test2();
end;
--有错误，待修改

 

 

例子

  --创建一个存储过程，名：test_pro
  create or replace procedure test_pro(uname   in varchar,
                                       itemnum out number) as
  --定义变量名
  itemaccount   number;
  likeuname     varchar(20);
  user_borndate date;
  vs_msg        varchar(1000);
  i            number;
  --定义游标
  cursor items is
    select * from test_pro_users where uname like '张小小%' order by uname;
begin
  --《测试1》
  --变量赋值
  --选择表中的uname和'张小小%'匹配的个数，保存到itemaccount
  select count(*)
    into itemaccount
    from test_pro_users
   where uname like '张小小%';
  --把itemaccount的值赋给itemnum
  itemnum := itemaccount;
  --输出
  DBMS_OUTPUT.put_line('uname:' || uname); --参数
  DBMS_OUTPUT.put_line('itemaccount:' || itemaccount); --变量
  --《测试2》
  --组合表插入到另一个表，这里组合test_pro_users中的uname,address和TEST_PRO_RELATIONSHIP中的father,mother，插入到TEST_PRO_USERINFO
  INSERT INTO TEST_PRO_USERINFO
    (UNAME, BORNDATE, FATHER, MOTHER, ADDRESS)
    SELECT T1.UNAME, T1.BORNDATE, T2.FATHER, T2.MOTHER, T1.ADDRESS
      FROM TEST_PRO_USERS T1, TEST_PRO_RELATIONSHIP T2
     WHERE T1.UNAME = T2.UNAME;
  --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount 
  DBMS_OUTPUT.put_line('插入记录数=' || SQL%rowcount || '条');

  --《测试3》使用游标
  --items即为游标，在定义变量的时候就已经’初始化？‘，不知道算不算初始化。
  --遍历items中的每一条记录
  for item in items loop
    --取test_pro_users中对应记录的borndate，并且将值传给已定义的变量user_borndate
    select borndate
      into user_borndate
      from test_pro_users
     where uname = item.uname;
    --输出user_borndate的值
    DBMS_OUTPUT.put_line('user_borndate=' || user_borndate);
    --每个人的出生日期推迟12个月
    update test_pro_users
       set borndate = ADD_MONTHS(user_borndate, 12)
     where uname = item.uname;
  end loop;

  --while循环
  i:=0;
  while i < 10 LOOP
    begin
      i := i + 1;
      DBMS_OUTPUT.put_line('i=' || i);
    end;
  end LOOP;

  COMMIT;
end test_pro;

 

plsql中执行

  declare param1 varchar(20); param2 integer(10, 4);
begin
  param1 := '张小小'; test_pro(param1, param2);
--DBMS_OUTPUT.put_line('param1:' || param1 || ' ' || 'param2:' || param2);
end;

结果：

uname:张小小
itemaccount:4
插入记录数=4条
user_borndate=01-9月 -96
user_borndate=01-9月 -97
user_borndate=01-9月 -98
user_borndate=01-9月 -99
i=1
i=2
i=3
i=4
i=5
i=6
i=7
i=8
i=9
i=10