oracle数据库存储过程基础知识

工具pl/sql developer

1. 创建数据库存储过程(程序窗口 program window)

  1).无参数的存储过程

    create [or replace] procedure  存储过程名  is/as

      declare --声明变量的标识(可以写也可以不写)

     变量名   变量类型(值范围);

     变量名 变量类型(值范围)  :=值;

     变量名   contant  数据类型 :=初始值    (常量)

      begin

          pl/sql块

     exception 

         异常

      end 存储过程名;

 2).有参数的存储过程

     create [or replace] procedure 存储过程名(参数名 in/out 参数类型) is/as

     declare --声明变量的标识(可以写也可以不写)

       变量名  变量类型(值范围);

       变量名 变量类型(值范围) :=值;

     begin

       pl/sql块

     exception --异常处理标示

       异常

    end 存储过程名;

 注意:有参数的存储过程参数不用规定长度

IN表示输入参数，按值传递方式。

OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。

IN OUT 即可作输入参数，也可作输出参数。

 3)存储过程中游标的使用

 

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; 

2.删除存储过程 (命令窗口command window)

  drop  procedure  存储过程名

3.执行存储过程(命令窗口)

  exec/execute  存储过程名;

  或 call  存储过程名;

  执行存储过程 (sql 窗口SQL window)

    begin

      存储过程名(参数);

    end;

4.编辑已存在的存储过程(命令窗口 command window)

  edit   存储过程名

5.查看已经存在的存储过程(命令窗口 command window)

  view  存储过程名
 

6.测试存储过程(测试窗口 test window)

    在第7行输入存储过程名(); 如下图所示

 

 

 

7.查看正在运行的存储过程

select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

8.杀死正在执行的数据

elect   b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session   ' || '''' || b.sid || ',' ||b.SERIAL# ||  ''';' kill_command
               from   SYS.V_$ACCESS a, SYS.V_$session b
               where    a.type = 'PROCEDURE'
                  and   (a.OBJECT like upper('%存储过程名%') or
                            a.OBJECT like lower('%存储过程名%'))
                 and a.sid = b.sid

                 and b.status = 'ACTIVE';

alter system kill session 'sid,SERIAL#';

9.oracle中的死锁

查询数据库死锁

     select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.sql_text

      from v$locked_object t1,v$session t2,v$sqltext t3

      where t1.session_id=t2.sid 

     and t2.sql_address=t3.address

    order by t2.logon_time;

 

找到正在执行的包的 sid ,serial#

      select   b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session   ' || '''' || b.sid || ',' ||b.SERIAL# ||  ''';' kill_command
               from   SYS.V_$ACCESS a, SYS.V_$session b
               where    a.type = 'PACKAGE'
                 and a.sid = b.sid
                 and b.status = 'ACTIVE';

 

查询出来的结果就是有死锁的session了，
         下面就是杀掉
              拿到上面查询出来的SID和SERIAL#，填入到下面的语句中

alter system kill session 'sid,serial#';