PL/SQL存储过程备忘

 

嗯，好久不写存储过程了，最近有一个业务用数据库的存储过程来实现比较妥当，于是再次接触了一下，下面是一些记录，以便以后翻查；

1、如何定义一个存储过程：

下面是一个简单的存储过程定义，实现了将“hello”打印出来

CREATE OR REPLACE PROCEDURE P_TEST(P_START_TIME TEST.CREATE_TIME%TYPE, P_END_TIME TEST.CREATE_TIME%TYPE) IS

//这里定义所有在正文中会使用到的局部变量

BEGIN

//这里是存储过程正文

    DBMS_OUTPUT.PUT_LINE('HELLO');

 

END P_TEST;

 

说明：P_START_TIME TEST.CREATE_TIME%TYPE 代表该形参类型与TEST表的CREATE_TIME字段一致；

 

2、如何在sql窗口调用一个名为P_TEST的存储过程？

格式如下：

DECLARE

BEGIN

P_TEST(SYSDATE,SYSDATE);

END;

 

3、如何使用循环？

 

LOOP

//这里是循环中的内容

END LOOP;

 

4、如何在循环中continue?

 

LOOP

if 条件满足 then

goto main_loop;

end if;

 

<<main_loop>>

 null;

END LOOP;

 

 

5、如何使用游标？

一般使用游标分这样几个步骤：定义游标、打开游标、获取当前游标数据并让游标指向下一条数据、关闭游标；

定义游标示例：

CURSOR myCur is

        select a, b, c

          from tbl_test;

 

打开游标示例：

    if myCur%isopen = false then

        open myCur;

    end if;

 

获取当前数据并让游标指向下一跳数据：

    loop

        fetch myCur into tmp_a,tmp_b,tmp_c;

        exit when myCur%notfound;

        dbms_output.put_line(tmp_a || ',' || tmp_b || ',' ||

                             tmp_c);

    end loop;

 

关闭游标示例：

close myCur;

 

一个完整的例子：

CREATE OR REPLACE PROCEDURE P_TEST IS

    CURSOR myCur is

        select a, b, c from tbl_test;

 

    tmp_a varchar(32);

    tmp_b varchar(32);

    tmp_c varchar(32);

BEGIN

    if myCur%isopen = false then

        open myCur;

    end if;

 

    loop

        fetch myCur

            into tmp_a, tmp_b, tmp_c;

        exit when myCur%notfound;

        dbms_output.put_line(tmp_a || ',' || tmp_b || ',' || tmp_c);

    end loop;

 

    close myCur;

 

END P_TEST;

 

 

6、使用结构体与游标结合进行使用

在上面的应用中，我们可以把a,b,c三个值放到一个结构体中，以方便使用；

结构体定义示例：

    type SourceData is record(

        a tbl_test.a%type,

        b tbl_test.b%type,

        c tbl_test.c%type);

 

对循环的使用，我们也可以通过使用for-in语句，下面的完整示例：

CREATE OR REPLACE PROCEDURE P_TEST IS

    CURSOR myCur is

        select a, b, c from tbl_test;

 

    type SourceData is record(

        a tbl_test.a%type,

        b tbl_test.b%type,

        c tbl_test.c%type);

 

    srcData SourceData;

BEGIN

    for srcData in myCur loop

        dbms_output.put_line(srcData.a || ',' || srcData.b || ',' ||

                             srcData.c);

    end loop;

 

END P_TEST;

 

7、如何获取当前插入数据的id？

insert into tbl_test(id,name) values(seq_test.nextval,'myName');

select seq_test.currval into tmpId from dual;

 

8、在存储过程处理过程中，遇到异常一般如何处理？

下面是一个存储过程正文中的常用格式：

CREATE OR REPLACE PROCEDURE P_TEST(P_START_TIME TEST.CREATE_TIME%TYPE, P_END_TIME TEST.CREATE_TIME%TYPE) IS

BEGIN

//这里做一些数据库操作

      COMMIT;

EXCEPTION

//遇到异常，则回滚

    WHEN OTHERS THEN

   

        ROLLBACK;

        RAISE;

 

END P_TEST;

 

 

9、常用函数

打印：

DBMS_OUTPUT.PUT_LINE('hello' || ' ' || 'world');

字符串转日期：

to_date('2011-01-10 12:13:14','YYYY-MM-DD HH24:MI:SS')

日期转字符串：

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')

去掉前后空格：

trim(' str ');