Oracle存储过程

Oracle存储过程语法

阅读目录

 

• 创建基本的存储过程
• 创建带参数存储过程
• 创建带事务的存储过程
• 使用游标
• 其他使用技巧
  • 分支条件判断
  • 动态sql
  • 给变量赋值
  • while 循环
  • 判断是否存在

 

 

 

回到顶部

 

创建基本的存储过程

 

1 CREATE OR REPLACE PROCEDURE MyProName IS
2 BEGIN
3   NULL;
4 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体的结束

 

回到顶部

 

创建带参数存储过程

 

现在想给存储过程加上参数，定义变量怎么处理呢？如下

 

1 CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2,param2 out varchar2) as
2   v_cnt      number; --定义变量1
3   tablename1 varchar2(80); --定义变量2
4   sqlTxt     varchar2(2000); --定义变量2
5 BEGIN
6   --处理逻辑
7   NULL;
8 END;

 

 (1)存储过程参数不带取值范围，in表示传入，out表示输出；类型可以使用任意Oracle中的合法类型。

 

 (2) 变量带取值范围，后面接分号

 

回到顶部

 

创建带事务的存储过程

 

CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2,param2 out varchar2) as
  v_cnt      number; --定义变量1
  tablename1 varchar2(80); --定义变量2
  sqlTxt     varchar2(2000); --定义变量2
BEGIN
  --处理逻辑
  NULL;
   commit;--提交事务
Exception
  When others then
   Dbms_output.Put_line(sqlerrm);--打印输出错误
   Rollback;--回滚事务
END;

 

 提交事务，存在异常则回滚事务；

 

回到顶部

 

使用游标

 

游标遍历

 

CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2, param2 out varchar2) as

  v_cnt      number; --定义变量1
  tablename1 varchar2(80); --定义变量2
  sqlTxt     varchar2(2000); --定义变量2
BEGIN
  --定义游标
  cursor c_tab_temp1 is
    select t.modelnumber, t.tablename from d_modelmap t;
  c_bom_row1 c_tab_temp1%rowtype;
  --处理逻辑
  --循环游标
  for c_bom_row1 in c_tab_temp1 loop
    tablename1 := c_bom_row1.tablename;  --取游标中的值
    
   
  END LOOP;
  commit; --提交事务
Exception
  When others then
    Dbms_output.Put_line(sqlerrm); --打印输出错误
    Rollback; --回滚事务
END;

 

返回游标

 

create or replace procedure test(
res out varchar2,
p_cur out sys_refcursor)   as
Begin
open p_cur for
     select * from tableName
res:='';
end;

 

回到顶部

 

其他使用技巧

分支条件判断

 

--分支判断
    if tablename1 <> '' then
      --处理逻辑
      null;
    else
      --处理逻辑
      null;
    end if;

动态sql

 

 --拼接动态sql
    sqltxt := 'update ' || tablename1 || '  t
             set t.partid=(select t3.f_id from i_partlist t3 where t3.part_no=t.part_no
             and nvl(t3.part_techstate,'' '')=nvl(t.part_techstate,'' ''))
             where not  exists(select t2.f_id from i_Partlist t2 where t2.f_id=t.partid)';
    --执行动态sql
    execute immediate sqlTxt;

给变量赋值

 

(1)用select XX into xx给变量赋值

 

 select count(1)  into v_count  from A t where t.A='aaa';

 

(2)直接赋值

 

V_TEST := 123;

while 循环

 

  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

判断是否存在

 

在判断语句前最好先用count（*）函数判断是否存在该条操作记录

 

  --判断是否存在，v_count是定义的数值变量
    select count(1)
      into v_count
      from A t
     where t.A='aaa';
    if v_count = 0 then
      
    else
    
    end if;