Oralce 存储过程 快速入门

简单介绍Oracle存储过程里的变量声明、控制语句、循环语句、游标、存储过程、函数的基本语法及用法。让有SQL的人快速上手Oracle存储过程。

语句块定义


decalre
-- 变量声明
var1 number(2);                -- 仅声明
var2 char(2) := '11';          -- 在声明的同时初始化

begin
        -- 语句
end; -- 语句块结束

if 语句


if a = 1 or b = 2 then

elsif c = 3 then

else

end if;

case 语句

case语句如果作为分支控制语句,最后结束语句是end case,如果是作为select语句里的控制语句则只需要end


declare
num number(10) := 1;
begin
    case
        when num = 0 then dbms_output.put_line( 'zero');
        when num = 1 then dbms_output.put_line( 'one');
        else  dbms_output.put_line( 'default');
    end case;
   
    case num
        when 0 then dbms_output.put_line( 'zero');
        when 1 then dbms_output.put_line( 'one');
        else  dbms_output.put_line( 'default');
    end case;
end;

for 循环

  • 循环一个范围
    格式:for i in [start .. end] loop ... end loop;

  for i in 0..9 loop
      dbms_output.put_line('i:' || i);
  end loop;
  • 遍历隐式游标
    隐式游标的好处是不需要手动关闭,方便。

for currow in (
   select t.col1, t.col2
   from tableName t
   where ...
) loop
    if currow.col1 = 0 then
       return;    -- 中止sp,返回
   end if;
end loop;

while 循环


isok := 9;
while isok >= 0 loop
       isok := isok - 1;
    
       if isok = 8 then
          continue;                -- 与编程语言的 continue 语义一样,跳过当前循环的剩余语句,回到循环开始
       end if;
    
       if isok = 4 then
          exit;                    -- 与编程语言的 break 语义一样,跳出循环
       end if;

       dbms_output.put_line('isok:' || isok);
end loop;

dbms_output.put_line('outside while loop .');

cursor

隐式游标


for currow in (
   select t.col1, t.col2
   from tableName t
   where ...
) loop
    if currow.col1 = 0 then
       return;    -- 中止sp,返回
   end if;
end loop;

显式游标


declare
isok integer;
v_event_id number(10);
v_isagain number(2);
v_rate number(2);

v_sender char(11) := '13800138000';

cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- 声明游标


begin
    open cursorVar;    -- 打开游标
    loop
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值
         exit when cursorVar%notfound;                             --当没有记录时退出循环
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);
    end loop;
   
    close cursorVar;   -- 关闭游标
   
    --游标的属性有:%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT; 
    --%FOUND:已检索到记录时,返回true 
    --%NOTFOUNRD:检索不到记录时,返回true 
    --%ISOPEN:游标已打开时返回true 
    --%ROWCOUNT:代表检索的记录数,从1开始 
end;

带参数游标


declare
isok integer;
v_event_id number(10);
v_isagain number(2);
v_rate number(2);

v_sender char(11) := '13800138000';

cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 声明游标

begin
    open cursorVar(v_sender);    -- 打开游标,在括号里传参。
    loop
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值
         exit when cursorVar%notfound;                             --当没有记录时退出循环
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);
    end loop;
   
    close cursorVar;   -- 关闭游标
end;

存储过程


create or replace procedure sp_name (
        -- 入参、出参列表, 逗号分隔。
        uid in varchar2,                          -- 不能带长度信息
        startDate in date ,                        -- 第二个输入参数
        defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序
        isok out number ,                          -- 输出参数
        result out varchar2                       -- 第二个输出参数
)
as
-- 变量声明,每个声明用分号结束。可以在声明的同时初始化
var1 varchar2( 11);
var2 number( 2) := 123 ;

begin
        -- 字符串拼接用 ||
        dbms_output.put_line( 'isok:' || 'abc' );
       
        -- 调用其他存储过程
        sub_sp_name(param1, prarm2, outParam1, outParam2);

end;        -- 存储过程结束

函数


create or replace function func  (
        -- 入参、出参列表, 逗号分隔。
        uid in varchar2,                          -- 不能带长度信息
        startDate in date ,                        -- 第二个输入参数
        defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序
        isok out number ,                          -- 输出参数
        result out varchar2                       -- 第二个输出参数
)
return number       -- 定义返回类型
as
-- 变量声明,每个声明用分号结束。可以在声明的同时初始化
var1 varchar2( 11);
var2 number( 2) := 123 ;

begin
        -- 字符串拼接用 ||
        dbms_output.put_line( 'isok:' || 'abc' );
       

        return ret_val;
end;

存储过程与函数的异同

  1. 两者定义类似,都可以带输入输出参数。
  2. 函数有返回值,存储过程没有。
  3. 函数的调用要在select语句里;而存储过程不用,可以独立调用。

一些管理语句

查看最近执行的sql

select * from v$sql order by FIRST_LOAD_TIME desc

查看正在执行的sql

  SELECT B.SID,
       B.STATUS,
       B.OSUSER,
       B.TERMINAL,
       B.PROGRAM,
       B.MACHINE,
       B.SQL_EXEC_START AS "SQL STARTTIME",
       A.SQL_ID,
       A.SQLTYPE,
       A.SQL_TEXT,
       A.LAST_ACTIVE_TIME AS "SQL ENDSTIME"    -- 语句执行完成前,一直在变

  FROM V$SQL A, V$SESSION B
 WHERE A.SQL_ID = B.SQL_ID
   AND B.STATUS = 'ACTIVE'
   AND B.SID <> (SELECT SID FROM V$MYSTAT WHERE ROWNUM <= 1)
   AND B.SID > 1 ;

查看sql语句的执行进度:


  SELECT A.SID                          AS "进程SID",
       OPNAME                         AS "执行操作",
       TARGET                         AS "操作对象",
       START_TIME                     AS "开始时间",
      (SYSDATE - START_TIME) * 24 * 60 * 60 AS "已耗时(秒)",
       TIME_REMAINING                 AS "剩余时间",
       A.USERNAME                     AS "用户名称",
       MACHINE                        AS "主机",
       TERMINAL                       AS "终端",
       PROGRAM                        AS "进程",
       STATE                          AS "当前状态",
       ROUND( SOFAR/TOTALWORK*100,2 ) AS "完成百分比"
FROM GV$SESSION_LONGOPS A,
     GV$SESSION B
WHERE A.TIME_REMAINING>0 
AND   A.SID=B.SID
AND   B.SERIAL#=A.SERIAL# 
AND   A.INST_ID=B.INST_ID
AND TARGET NOT LIKE 'SYS%';

查看表空间的情况:


  SELECT TABLESPACE_NAME AS "表空间名称",
       BLOCK_SIZE AS "块大小(db_block_size)",
       STATUS AS "当前状态",
       DECODE(A.CONTENTS,
              'PERMANENT',
              '永久表空间',
              'TEMPORARY',
              '临时表空间',
              'UNDO',
              '回退表空间') AS "表空间类型",
       SEGMENT_SPACE_MANAGEMENT AS "空间管理方式",
       ROUND(DECODE(A.CONTENTS,
                    'PERMANENT',
                    (SELECT SUM(USER_BYTES) AS "SIZE_BYTE"
                       FROM DBA_DATA_FILES B
                      WHERE B.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'TEMPORARY',
                    (SELECT SUM(USER_BYTES)
                       FROM DBA_TEMP_FILES C
                      WHERE C.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'UNDO',
                    (SELECT SUM(USER_BYTES) AS "SIZE_BYTE"
                       FROM DBA_DATA_FILES D
                      WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME)) / 1024 / 1024 / 1024) AS "分配空间(GB)",
       ROUND(DECODE(A.CONTENTS,
                    'PERMANENT',
                    (SELECT SUM(BYTES)
                       FROM DBA_SEGMENTS H
                      WHERE H.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'TEMPORARY',
                    (SELECT ALLOCATED_SPACE
                       FROM DBA_TEMP_FREE_SPACE F
                      WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'UNDO',
                    (SELECT SUM(BYTES)
                       FROM DBA_UNDO_EXTENTS J
                      WHERE J.STATUS IN ('ACTIVE', 'UNEXPIRED')
                        AND J.TABLESPACE_NAME = A.TABLESPACE_NAME)) / 1024 / 1024 / 1024) AS "已用空间(GB)",
       ROUND(DECODE(A.CONTENTS,
                    'PERMANENT',
                    (SELECT SUM(BYTES)
                       FROM DBA_FREE_SPACE E
                      WHERE E.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'TEMPORARY',
                    (SELECT FREE_SPACE
                       FROM DBA_TEMP_FREE_SPACE F
                      WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME),
                    'UNDO',
                    (SELECT SUM(BYTES)
                       FROM DBA_FREE_SPACE G
                      WHERE G.TABLESPACE_NAME = A.TABLESPACE_NAME)) / 1024 / 1024 / 1024) AS "可用空间(GB)"
  FROM DBA_TABLESPACES A
 WHERE A.TABLESPACE_NAME LIKE 'RDC%'
    OR A.TABLESPACE_NAME LIKE 'UNDO%'
 ORDER BY CONTENTS, TABLESPACE_NAME;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据