关于存储过程

存储过程实际上是SQL+流程控制的结合体，相当于带了一丢丢编程的味道。

学会基础语法+流程控制语法，任何一个 Coder 都能够轻松地的写出存储过程了。

基础语法

无参语法

--视图（VIEW）中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。
--无参基础语法
CREATE OR REPLACE PROCEDURE p_Mebugs
AS
BEGIN    
    --删除180天前数据
    DELETE mebugs_log t WHERE (sysdate - to_date(t.starttime,'yyyyMMddhh24miss')) > 180; 
END p_Mebugs;

传参语法

--传参基础语法，默认为入参
--可以通过IN OUT指定如(time OUT NUMBER，输出time)
--IN OUT可以同时指定，相当于两个参数，可以有多个参数(,号隔开)
--入参只能指定数据类型，不能指定长度
CREATE OR REPLACE PROCEDURE p_Mebugs(time NUMBER)  
AS
--定义参数
countNo NUMBER; 
--定义参数(可以指定长度)
testMsg VARCHAR2(256);  
--参数赋值(这里是个示例，没有使用到，可以用入参进行赋值)
testObj := 123; 
BEGIN
     --INTO 赋值(可以多个赋值 COL1,COL2 INTO 参数1,参数2)
    SELECT count(*) INTO countNo  FROM mebugs_log WHERE starttime=time;
    DBMS_OUTPUT.PUT_LINE(time||'记录数'||countNo);
--异常场景
EXCEPTION
--记录不存在
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有找到记录');
END p_Mebugs;

调用存储过程

--调用存储过程
EXEC p_Mebugs(time);
--获取出存储过程，在程序块(BEGIN END块)中调用(存储过程就相当于JAVA中的一个方法)
DECLARE time NUMBER;
BEGIN
    p_Mebugs(time);
    DBMS_OUTPUT.PUT_LINE(pname);
END;

维护存储过程

--维护操作
--删除存储过程
DROP PROCEDURE p_Mebugs;
--查看过程状态
SELECT object_name,status FROM USER_OBJECTS WHERE object_type='PROCEDURE';
--重新编译过程
ALTER PROCEDURE p_Mebugs COMPILE;
--查看过程代码
SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';

流程控制

流程控制相关语法(在BEGIN...END程序块中运行)

IF

--判断条件示例：TEST=1
--IF判断
IF 判断条件 THEN
    BEGIN
    --DOING SQL
    END;
ELSIF 判断条件 THEN
    BEGIN
    --DOING SQL
    END;
ELSE
    BEGIN
    --DOING SQL
    END;
END IF;

WHILE

--WHILE循环
WHILE 判断条件 LOOP
BEGIN
    --DOING SQL
END;
END LOOP;

FOR IN

--FOR IN 循环
FOR 参数 IN 数组/游标 LOOP 
BEGIN
    --DOING SQL
END;
END LOOP;

关于数组/游标

数组

Oracle 中本是没有数组的概念的。

数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。

用户可以使用Oracle 已经定义好的数组类型，testPram OUT array。

游标

Oracle中Cursor是非常有用的。

游标用于遍历临时表中的查询结果(简单理解就是游标型变量用于存多个值，可以被遍历)。

其相关方法和属性也很多，此处常规介绍。

Cursor型游标

--Cursor型游标(不能用于参数传递) 
CREATE OR REPLACE PROCEDURE p_Mebugs
--回顾上文说过游标IS 视图AS
IS  
--游标1 直接赋值
cusorTest1 Cursor ID SELETE starttime FROM mebugs_log; 
cusorTest2 Cursor；
BEGIN    
    --游标2 代码块赋值
    SELETE starttime INTO cusorTest2 FROM mebugs_log; 
END p_Mebugs;

SYS_REFCURSOR型游标

--SYS_REFCURSOR型游标，Oracle预先定义的游标，可作出参数进行传递 
CREATE OR REPLACE PROCEDURE p_Mebugs(testCursor OUT SYS_REFCURSOR)
--回顾上文说过游标IS 视图AS
IS  
cursor SYS_REFCURSOR;
name varhcar(20);  
BEGIN    
    --SYS_REFCURSOR只能通过OPEN方法来打开
    OPEN cursor FOR SELETE starttime FROM mebugs_log;
    LOOP 
                --SYS_REFCURSOR 只能通过fetch into 来打开和遍历;
        FETCH cursor INTO name; 
                --SYS_REFCURSOR 中可使用三个状态属性：%NOTFOUND(未找到记录信息) %FOUND(找到记录信息) %ROWCOUNT( 当前游标所指向的行位置)        
        EXIT WHEN cursor%NOTFOUND; 
        DBMS_OUTPUT.PUTLINE(name); 
    END LOOP;
--赋值给出参    
testCursor := cursor; 
END p_Mebugs;

游标遍历

--游标遍历(FETCH打开)可以同时赋给多个变量
DECLARE   
    name VARCHAR2(10);  
    time VARCHAR2(10);  
    CURSOR emp_cursor IS SELECT name,time FROM mebugs_log;  
BEGIN  
    OPEN emp_cursor;  
    FETCH emp_cursor INTO v_ename,v_job; 
--略...