简单介绍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;
存储过程与函数的异同
- 两者定义类似，都可以带输入输出参数。
- 函数有返回值，存储过程没有。
- 函数的调用要在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;
欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。
