Oracle 匿名块和命名块存储过程函数包触发器

青旅半醒 2022-04-11 04:30 1184阅读 0赞

# PL/SQL块分类（匿名块、命名块、子程序、触发器） #

2016年07月28日 11:34:15 [王涛WantTao][WantTao] 阅读数：2512更多

个人分类： [ORACLE | PL/SQL][ORACLE _ PL_SQL]

1.  `-匿名块`
2.  `DECLARE`
3.  `V_NAME VARCHAR2(5);`
4.  `BEGIN`
5.  `SELECT NAME INTO V_NAME WHERE AGE=100;`
6.  `DBMS_OUTPUT.PUT_LINE(V_NAME);`
7.  `EXCEPTION`
8.  `WHER NO_DATA_FOUND THEN`
9.  `DBMS_OUTPUT.PUT_LINE('未查到到任何数据');`
10. `END;`

1. `-命名块`
2. `<<OUTER>>`
3. `DECLARE V_NAME VARCHAR2(5);`
4. `BEGIN`
5. `SELECT NAME INTO V_NAME WHERE AGE=100;`
6. `DBMS_OUTPUT.PUT_LINE(V_NAME);`
7. `EXCEPTION`
8. `WHER NO_DATA_FOUND THEN`
9. `DBMS_OUTPUT.PUT_LINE('未查到到任何数据');`
10. `END;--<<OUTER>>`

1.  `-子程序之过程`
2.  `CREATE OR REPLACE PROCEDURE UPDATE_SAL(NEWNAME VARCHAR2，_ID NUMBER)`
3.  `IS`
4.  `ERROR_EXP EXCEPTION;`
5.  `BEGIN`
6.  `UPDATE TABLENAME SET NAME=NEWNAME WHERE ID=_ID;`
7.  `IF SQL%NOTFOUND THEN`
8.  `RAISE ERROR_EXP;`
9.  `END IF;`
10. `EXCEPTION`
11. `WHEN ERROR_EXP`
12. `RAISE_APPLICATION_ERROR('-20004','error');`
13. `END;-`
14. `-执行过程`
15. `exec UPDATE_SAL('WT',100);`

1.  `--子程序之函数`
2.  `CREATE FUNCTION ANNUAL_INCOME(NAME VARCHAR2)`
3.  `RETURN NUMBER IS`
4.  `ANNUAL_SALARY NUMBER(7，2);`
5.  `BEGIN`
6.  `SELECT SAL*12+NVL(COMM,0) INTO ANNUAL_SALARY`
7.  `FROM TABLENAME WHERE LOWER(ENUMA)=LOWER(NAME);`
8.  `END;`
9.  `--执行函数`
10. `SQL> VAR INCOME NUMBER`
11. `SQL> CALL ANNUAL_INCOME('NAME') INTO:INCOME`
12. `SQL> PRINT INCOME`

1.  `--触发器 指隐含执行的存储过程`
2.  `CREATE TRIGGER UPDATE_CASCADE`
3.  `AFTER UODATE OF deptno ON dept FOR EACH ROW`
4.  `BEGIN`
5.  `UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;`
6.  `END;`
7.  `如上例所示，触发器UPDATE_CASCADE用于实现联级更新，当更新 deptno列时，emp的deptno列也会相应更新`

[https://blog.csdn.net/wangtao510/article/details/52053599][https_blog.csdn.net_wangtao510_article_details_52053599]

# Oracle中命名块之存储过程的简单使用 #

2017年04月05日 10:29:32 [菜鸟的奋斗之路][Link 1] 阅读数：1585

一、匿名块和命名块  
◆PL/SQL块分为良好总：命名块和匿名块。  
  
◆匿名块：以declare或begin开始，每次执行匿名块都要通过客户端工具将其发送给Oracle，经过语法分析、编译然后执行。  
  
◆命名块：具有名称的PL/SQL块，这些命名块被存储在Oracle中，编译一次，以后只可调用就可多次执行。如：存储过程、存储函数、包、触发器等、  
  存储过程：无返回值;  
  存储函数：有返回值;  
  包：可容纳多个过程或函数的一个容器(较好管理这些过程和函数，类似于java中的包);  
  触发器：在合适的实际被自动执行。(无需调用，在满足要求的情况下，自动执行；触发器也被称为隐式执行的命名块！)  
  
  
◆匿名块和命名块的区别：  
  匿名块：能够动态地创建和执行过程代码的PL/SQL结构，每次执行匿名块时就会自动的将该块数据通过文本的形式发送给Oracle，然后执行，反馈给用户，每次执行都需发送和编译！  
  命名块：持久化的方式将代码作为数据库属性储存在系统目录中，在Oracle中编译一次，下次若使用可直接调用执行，无需再次编译，非常高效。  
  
  
二、存储过程：  
存储过程是具有名称、完整特定功能和无返回值的PL/SQL块，只需发送调用命令即可执行该过程，能够实现代码的重用，不能在SQL语句当中调用！  
  
  
◆创建存储过程：  
  格式：create\[or replace\] procedure proc\_name  
          \[(para1\[int|out|in out\]type,...)\]  
          \{is|AS\}  
          prdc\_body  
    
  proc\_name表示存储过程名;  
  para1表示参数名;type是参数para1的数据类型;  
  proc\_body表示过程体.过程体是遵循PL/SQL块的结构，但不能使用declare关键字，end后面可带有过程名。  
    
注意：参数列表是可选的  
  
  
◆调用存储过程：(存储过程创建的时候会自动的保存在Oracle中，在执行储存过程的时候，PL/SQL块只需调用即可！)  
  (1)如果在PL/SQL块中调用，直接使用proc\_name(...);  
  (2)如果使用sql plus环境中，需要使用 EXEC proc\_name(...)的形式;  
  (3)如果存储过程无参数，这调用时可带有圆括号也可不带！  
    
【示例】创建一个无参数的存储过程(显示所有员工的信息)  
create or replace procedure pr\_show\_employee   
is  
\--进入块的声明部分(无需编写declare关键字)  
     --定义游标  
     cursor v\_emp\_cursor is select \* from employees;  
     --定义游标类型的记录型行变量  
     v\_emp\_record v\_emp\_cursor%rowtype;  
begin  
     for v\_emp\_record in v\_emp\_cursor loop  
         dbms\_output.put\_line(v\_emp\_record.employee\_id||','||v\_emp\_record.first\_name||','||  
                              v\_emp\_record.last\_name||','||v\_emp\_record.salary);  
     end loop;  
end pr\_show\_employee ;  
  
  
\--调用存储过程(在一个匿名块中执行)  
begin  
     pr\_show\_employee();  
end;   
  
  
【示例】存储过程的参数接受产品类别编号，输出该类所有产品的信息  
create or replace procedure pr\_show\_product(  
product\_type products.product\_type\_id%type)  
as  
  --定义游标  
  cursor v\_prd\_cursor is select \* from products p where product\_type = p.product\_type\_id;  
  --定义游标的行类型变量  
  v\_prd\_record v\_prd\_cursor%rowtype;  
begin  
     for v\_prd\_record in v\_prd\_cursor loop    dbms\_output.put\_line(v\_prd\_record.product\_id||','||v\_prd\_record||','||product\_type\_id||','||v\_prd\_record.description||','  
||v\_prd\_record. name||','||v\_prd\_record.price);  
     end loop;  
end pr\_show\_product;  
  
  
\--调用存储过程(执行)  
declare  
       --定义编号变量的类型  
       v\_prd\_type\_id products.product\_type\_id%type := 1;  
begin  
     pr\_show\_product(v\_prd\_type\_id);  
end;   
  
  
◆参数的传递模式：  
  参数传递有三种模式：in、out、in out  
  in：过程调用时，实参的值会传递给形参。形参被看作是常量，在过程内部不能被修改。  
  out：过程调用时，实参的值被忽略，形参为null值，在过程内部形参变量可以读写。过程结束时，形参的值赋值给实参！  
  in out：过程调用时，实参的值传递给形参，在过程内部形参变量可读可写，过程结束时，形参的值赋值给实参。  
    
注意：如果在定义存储过程时，没有指明参数传递模式，则默认为 in 模式  
  
  
【示例】创建一个有参数传递默认的存储过程  
create or replace procedure pr\_test(p\_1 in out varchar2)  
is   
  
  
begin  
     dbms\_output.put\_line('p\_1='||p\_1);  
     p\_1 := 'abc';   
     dbms\_output.put\_line('p\_1='||p\_1);  
end pr\_test;  
  
  
\--调用存储过程  
declare  
    v\_1 varchar2(5) := 'wwww';  
begin  
    pr\_test(v\_1);   
    dbms\_output.put\_line('v\_1='||v\_1);  
end;  
注意:(1)在创建存储过程时，形参类型不能指定具体的长度,字符类型就为 varchar2,数字类型就是number；默认和实参类型的长度等同  
     (2)使用 in 模式，形参接受实参的值，默认形参为常量，在存储过程当中不能被修改，否则出错！  
     (3)使用 out 模式，实参的值被忽略，但必须是变量，用来接收存储过程执行完形参的值；而形参是可读可写的。  
     (4)使用 in out 模式，实参的值传递给形参，也必须是变量，形参可读可写，最后执行完存储过程形参的值传递给实参！  
     (5)参数传递，实参的值和长度或精度约束会传递给形参，因此，形参不能有长度或精度的约束。在过程中给形参赋值时，要注意它的长度或精度的限制！

[https://blog.csdn.net/super\_yc/article/details/69226920][https_blog.csdn.net_super_yc_article_details_69226920]

## [【PL/SQL】匿名块、存储过程、函数、触发器][PL_SQL] ##

名词解释

子程序：PL/SQL的过程和函数统称为子程序。

匿名块：以DECLARE或BEGIN开始，每次提交都被编译。匿名块因为没有名称，所以不能在数据库中存储并且不能直接从其他PL/SQL块中调用。

命名块：除匿名块之外的其他块。包括过程、函数、包和触发器。可以在数据库中存储并在适当的使用运行。

子程序的优点：

1）具有扩展性

可以自定义PL/SQL语言以满足实际应用

2）高可用和可维护性

子程序的调用不受调用者数目的影响，只要有效就可被调用。

如果定义被更改以后，只有子程序受到影响，简化了维护、优化过程。

匿名块、过程、函数的执行过程基本相似，不同之处在于：匿名块在DECLARE后面声明变量，而过程和函数在IS后面声明。

函数也可以称为过程，特殊之处为：函数总是有一个返回值（RETURN）。

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 匿名块 &nbsp; [DECLARE] &nbsp; &nbsp; BEGIN &nbsp;&nbsp;--statements &nbsp; [EXCEPTION] &nbsp; END; </td> 
 <td style="vertical-align:top;"> 过程 &nbsp; PROCEDURE name IS &nbsp; BEGIN &nbsp;&nbsp;--statements &nbsp; [EXCEPTION] &nbsp; END; </td> 
 <td style="vertical-align:top;"> 函数 &nbsp; FUNCTION name RETURN datatype IS BEGIN &nbsp;&nbsp;--statements &nbsp;&nbsp;RETURN value; [EXCEPTION] &nbsp; END; </td> 
 </tr> 
 </tbody> 
</table>

1、 创建存储过程

1）语法

CREATE \[OR REPLACE\] PROCEDURE procedure\_name

\[argument\[\{IN|OUT|IN OUT\}\] TYPE\]

\{IS|AS\}

<类型、变量的说明>

BEGIN

<执行部分>

EXCEPTION

<可选的异常错误处理程序>

END;

2）示例

①要求：记录登录数据的userid和logdate。

\#\#创建表，用来存储userid和logdate

SQL> create table logtable (userid varchar2(10),logdate date);

Table created.

\#\#创建存储过程

SQL> create or replace procedure logexecution is

2  begin

3    insert into logtable (userid, logdate) values (user, sysdate);

4  end;

5  /

Procedure created.

\#\#执行存储过程

SQL>  exec logexecution;

PL/SQL procedure successfully completed.

\#\#验证执行结果

SQL> select \* from logtable;

USERID     LOGDATE

\---------- -------------------

SYS        2017-03-12 01:18:28

② 要求：删除表中记录

\#\#创建过程

SQL> create or replace procedure delemp(v\_empno in emp.empno%type) is

2    no\_result exception;

3  begin

4    delete from emp where empno = v\_empno;

5    if sql%notfound then

6      raise no\_result;

7    end if;

8    dbms\_output.put\_line('employee number' || v\_empno || 'was deleted!');

9  exception

10    when no\_result then

11      dbms\_output.put\_line('no row found!');

12    when others then

13      dbms\_output.put\_line('other error!');

14  end delemp;

15  /

Procedure created.

\#\#执行过程

SQL> exec delemp(999);

no row found!

PL/SQL procedure successfully completed.

SQL> exec delemp(7499);

employee number7499was deleted!

PL/SQL procedure successfully completed.

2、 创建函数

1）语法

CREATE \[OR REPLACE\] FUNCTION function\_name

\[(argument\[\{IN|OUT|IN OUT\}\] TYPE,

...

argument\[\{IN|OUT|IN OUT\}\] TYPE)\]

RETURN return\_type \{IS|AS\}

function\_body

2）示例

\#\#创建函数

SQL> create or replace function get\_salary(dept\_no number, v\_num out number)

2    return number is

3    v\_sum number;

4  begin

5    select sum(sal), count(\*)

6      into v\_sum, v\_num

7      from emp

8     where deptno = dept\_no;

9    return v\_sum;

10  exception

11    when no\_data\_found then

12      dbms\_output.put\_line('你需要的数据不存在！');

13    when too\_many\_rows then

14      dbms\_output.put\_line('程序运行错误！请使用游标');

15    when others then

16      dbms\_output.put\_line('发生其他错误！');

17  end get\_salary;

18  /

Function created.

\#\#调用函数（注：函数不能单独调用，只能在语句中调用）

SQL> declare

2    v\_num number;

3    v\_sum number;

4  begin

5    v\_sum := get\_salary(30,v\_num);

6    dbms\_output.put\_line('30 department sum salary is '||v\_sum||' employee: '||v\_num);

7  end;

8  /

30 department sum salary is 9400 employee: 6

PL/SQL procedure successfully completed.

3、 触发器

触发器：在数据库中以独立的对象存储，它与存储过程不同的是，存储过程通过其他程序来启动运行或者直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且触发器不能接收参数，不能commit。

**触发器类型：**

DML触发器：Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

替代触发器：由于在Oracle里，不能对复杂视图进行操作。所以给出了替代触发器。它就是Oracle专门为进行视图操作的一种处理方法。

系统触发器：它可以在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。

**触发器组成：**

触发事件：即在何种情况下触发TRIGGER。

触发事件：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后（AFTER）触发。

触发器本身：即该TRIGGER被触发之后的目的和意图。

触发类型：说明触发器内定义的动作被执行的次数。即语句级（STATEMENT）触发器和行级（ROW）触发器。

语句级（STATEMENT）触发器：指当某触发事件发生时，该触发器只执行一次。

行级（ROW）触发器：指当某触发器事件发生时，对收到该操作影响的每一行数据，触发器都单独执行一次。

1）示例

①创建DML触发器

要求：当职工表emp被删除一条记录时，把被删除记录写到职工表删除日志表中。

\#\#建立删除日志表

SQL> create table emp\_his as select \* from emp where 1=2;

Table created.

\#\#创建触发器

SQL> create or replace trigger del\_emp

2    before update or delete on scott.emp

3    for each row

4  begin

5    insert into emp\_his

6      (deptno, empno, ename, job, mgr, sal, comm, hiredate)

7    values

8      (:old.deptno,

9       :old.empno,

10       :old.ename,

11       :old.job,

12       :old.mgr,

13       :old.sal,

14       :old.comm,

15       :old.hiredate);

16  end del\_emp;

17  /

Trigger created.

\#\#验证触发器

SQL> delete from emp where empno=7844;

1 row deleted.

SQL> select count(\*) from emp\_his;

COUNT(\*)

\----------

②创建替代触发器

\#\#创建复杂视图

SQL> create or replace view emp\_view as

2  select deptno,count(\*) total\_employeer,sum(sal) total\_salary

3  from emp group by deptno;

View created.

\#\#对视图进行DML会报错，因为复杂视图不能直接进行DML操作

SQL> delete from emp\_view where deptno=10;

delete from emp\_view where deptno=10

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

\#\#创建触发器

SQL> create or replace trigger emp\_view\_delete

2    instead of delete on emp\_view

3    for each row

4  begin

5    delete from emp where deptno = :old.deptno;

6  end emp\_view\_delete;

7  /

Trigger created.

\#\#再次删除，发现执行成功

SQL> delete from emp\_view where deptno=10;

1 row deleted.

③ 创建系统事件触发器

注意：系统时间触发器要以sys用户运行

\#\#登录sys用户

SQL> conn / as sysdba

Connected.

\#\#创建表

SQL> create table logtable (name varchar2(20),log\_time date);

Table created.

\#\#创建触发器

SQL> create or replace trigger login\_his

2    after logon on database

3  begin

4    insert into logtable values (user, sysdate);

5  end;

6  /

Trigger created.

\#\#验证触发器

SQL> select \* from logtable;

NAME                 LOG\_TIME

\-------------------- -------------------

SYS                  2017-03-12 00:15:29

分类: [Oracle][]

[https://www.cnblogs.com/NextAction/p/7366636.html][PL_SQL]

原创  
oracle存储过程、匿名块、函数、包  
梦Scarlett0人评论1121人阅读2017-11-29 11:41:29  
使用过程与函数的原则：

1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。

2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

一、存储过程

1、存储过程初步

\--存储过程:实现搬历史表 
create or replace procedure movetohistory\_1 ( 
                                           
                                           o\_count out number , 
                                           error out VARCHAR2) IS 
V\_COU-NT number; 
V\_SUBSCRIBE\_ID TI\_C\_IOM\_BUSIFORM.SUBSCRIBE\_ID% TYPE; 
v\_time date  := to\_date( '2015/3/20 16:24:23','yyyy-mm-dd hh24:mi:ss' ); 
CURSOR C\_MOVE2HIS IS 
 SELECT SUBSCRIBE\_ID FROM TI\_C\_IOM\_BUSIFORM where accept\_date <= v\_time; 
  
BEGIN 
  o\_count :=0; 
  OPEN C\_MOVE2HIS; 
  LOOP 
    FETCH C\_MOVE2HIS INTO V\_SUBSCRIBE\_ID; 
    EXIT WHEN C\_MOVE2HIS%NOTFOUND; 
    BEGIN 
       SELECT COUNT (\*) INTO V\_COUNT from  TI\_C\_IOM\_BUSIFORM where accept\_date <= v\_time; 
       IF(V\_COUNT > 0 ) THEN 
            INSERT INTO TI\_CH\_IOM\_BUSIFORM 
            (BUSIFORM\_ID,DAY,TRADE\_ID,TRADEGROUP\_ID,TRADEGROUP\_NUM,TRADEGROUP\_INDEX,BUSIFORM\_TYPE,SUBSCRIBE\_ID,BUSIFORM\_OPER\_TYPE,BUSINESS\_TYPE,STATUS,NET\_TYPE\_CODE,ADDRESS\_CODE\_A,ADDRESS\_CODE\_Z,FLOW\_TEMPLET\_ID,IN\_MODE\_CODE,DBSRC,BPM\_MAIN\_ID,ORDER\_ID,PRIORITY,SUGGEST\_TIME,CONFIRM\_RES\_NO,PROD\_SPEC\_ID,RES\_TEMPLET\_ID,ACCEPT\_DATE,EXEC\_TIME,EXEC\_MONTH,LATEST\_FINISH\_TIME,PLAN\_FINISH\_TIME,FINISH\_TIME,SRC\_TYPE,BUSIFORM\_EXTEND,USER\_ID,TRADE\_STAFF\_ID,TRADE\_EPARCHY\_CODE,TRADE\_CITY\_CODE,TRADE\_DEPART\_ID,AREA\_CODE,SERIAL\_NUMBER,BUSIFORM\_CUST\_TYPE,TERM\_IP,CUST\_CLASS\_ID,CUST\_ID,CUST\_NAME,CUST\_LINK\_NAME,CUST\_LINK\_PHONE,PRODUCT\_ID,PRODUCT\_NAME,ERROR\_TYPE, ERROR\_CODE,ERROR\_DESC,CLOSE\_SATISFY\_DEGREE,CLOSE\_DESC,RSRV\_STR1,RSRV\_STR2,RSRV\_STR3,RSRV\_DATE1,RSRV\_DATE2, REMARK) 
            ( select BUSIFORM\_ID,DAY,TRADE\_ID,TRADEGROUP\_ID,TRADEGROUP\_NUM,TRADEGROUP\_INDEX,BUSIFORM\_TYPE,SUBSCRIBE\_ID,BUSIFORM\_OPER\_TYPE,BUSINESS\_TYPE,STATUS,NET\_TYPE\_CODE,ADDRESS\_CODE\_A,ADDRESS\_CODE\_Z,FLOW\_TEMPLET\_ID,IN\_MODE\_CODE,DBSRC,BPM\_MAIN\_ID,ORDER\_ID,PRIORITY,SUGGEST\_TIME,CONFIRM\_RES\_NO,PROD\_SPEC\_ID,RES\_TEMPLET\_ID,ACCEPT\_DATE,EXEC\_TIME,EXEC\_MONTH,LATEST\_FINISH\_TIME,PLAN\_FINISH\_TIME,FINISH\_TIME,SRC\_TYPE,BUSIFORM\_EXTEND,USER\_ID,TRADE\_STAFF\_ID,TRADE\_EPARCHY\_CODE,TRADE\_CITY\_CODE,TRADE\_DEPART\_ID,AREA\_CODE,SERIAL\_NUMBER,BUSIFORM\_CUST\_TYPE,TERM\_IP,CUST\_CLASS\_ID,CUST\_ID,CUST\_NAME,CUST\_LINK\_NAME,CUST\_LINK\_PHONE,PRODUCT\_ID,PRODUCT\_NAME,ERROR\_TYPE, ERROR\_CODE,ERROR\_DESC,CLOSE\_SATISFY\_DEGREE,CLOSE\_DESC,RSRV\_STR1,RSRV\_STR2,RSRV\_STR3,RSRV\_DATE1,RSRV\_DATE2, REMARK 
             from  TI\_C\_IOM\_BUSIFORM 
             where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID 
               and accept\_date <= v\_time); 
             delete FROM TI\_C\_IOM\_BUSIFORM 
             where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID 
             and accept\_date <= v\_time; 
            o\_count := o\_count + 1; 
        END IF ; 
      commit;   
    EXCEPTION 
        WHEN OTHERS THEN 
           rollback; 
           delete from TI\_CH\_IOM\_BUSIFORM 
            where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID; 
            commit; 
    END; 
  END LOOP; 
  close C\_MOVE2HIS; 
end movetohistory\_1;

2、存储过程，加自定义exception，并改进，由外部传参数

\--存储过程 
create or replace 
procedure movetohistory ( o\_time in date, 
                          o\_count out number ) IS 
V\_COUNT number; 
V\_SUBSCRIBE\_ID TI\_C\_IOM\_BUSIFORM.SUBSCRIBE\_ID% TYPE; 
v\_time date := o\_time; 
v\_error exception; --自定义异常 
CURSOR C\_MOVE2HIS IS 
 SELECT SUBSCRIBE\_ID FROM TI\_C\_IOM\_BUSIFORM where accept\_date <= v\_time; 
BEGIN 
  o\_count :=0; 
  SELECT COUNT (\*) INTO V\_COUNT from  TI\_C\_IOM\_BUSIFORM where accept\_date <= v\_time; 
  if(V\_COUNT <= 0 ) then 
   raise v\_error; 
  end if; 
  OPEN C\_MOVE2HIS; 
  LOOP 
    FETCH C\_MOVE2HIS INTO V\_SUBSCRIBE\_ID; 
    EXIT WHEN C\_MOVE2HIS%NOTFOUND; 
    BEGIN 
    SELECT COUNT (\*) INTO V\_COUNT from  TI\_C\_IOM\_BUSIFORM where accept\_date <= v\_time; 
    IF(V\_COUNT > 0 ) THEN 
         INSERT INTO TI\_CH\_IOM\_BUSIFORM 
        (BUSIFORM\_ID,DAY,TRADE\_ID,TRADEGROUP\_ID,TRADEGROUP\_NUM,TRADEGROUP\_INDEX,BUSIFORM\_TYPE,SUBSCRIBE\_ID,BUSIFORM\_OPER\_TYPE,BUSINESS\_TYPE,STATUS,NET\_TYPE\_CODE,ADDRESS\_CODE\_A,ADDRESS\_CODE\_Z,FLOW\_TEMPLET\_ID,IN\_MODE\_CODE,DBSRC,BPM\_MAIN\_ID,ORDER\_ID,PRIORITY,SUGGEST\_TIME,CONFIRM\_RES\_NO,PROD\_SPEC\_ID,RES\_TEMPLET\_ID,ACCEPT\_DATE,EXEC\_TIME,EXEC\_MONTH,LATEST\_FINISH\_TIME,PLAN\_FINISH\_TIME,FINISH\_TIME,SRC\_TYPE,BUSIFORM\_EXTEND,USER\_ID,TRADE\_STAFF\_ID,TRADE\_EPARCHY\_CODE,TRADE\_CITY\_CODE,TRADE\_DEPART\_ID,AREA\_CODE,SERIAL\_NUMBER,BUSIFORM\_CUST\_TYPE,TERM\_IP,CUST\_CLASS\_ID,CUST\_ID,CUST\_NAME,CUST\_LINK\_NAME,CUST\_LINK\_PHONE,PRODUCT\_ID,PRODUCT\_NAME,ERROR\_TYPE, ERROR\_CODE,ERROR\_DESC,CLOSE\_SATISFY\_DEGREE,CLOSE\_DESC,RSRV\_STR1,RSRV\_STR2,RSRV\_STR3,RSRV\_DATE1,RSRV\_DATE2, REMARK) 
        ( select BUSIFORM\_ID,DAY,TRADE\_ID,TRADEGROUP\_ID,TRADEGROUP\_NUM,TRADEGROUP\_INDEX,BUSIFORM\_TYPE,SUBSCRIBE\_ID,BUSIFORM\_OPER\_TYPE,BUSINESS\_TYPE,STATUS,NET\_TYPE\_CODE,ADDRESS\_CODE\_A,ADDRESS\_CODE\_Z,FLOW\_TEMPLET\_ID,IN\_MODE\_CODE,DBSRC,BPM\_MAIN\_ID,ORDER\_ID,PRIORITY,SUGGEST\_TIME,CONFIRM\_RES\_NO,PROD\_SPEC\_ID,RES\_TEMPLET\_ID,ACCEPT\_DATE,EXEC\_TIME,EXEC\_MONTH,LATEST\_FINISH\_TIME,PLAN\_FINISH\_TIME,FINISH\_TIME,SRC\_TYPE,BUSIFORM\_EXTEND,USER\_ID,TRADE\_STAFF\_ID,TRADE\_EPARCHY\_CODE,TRADE\_CITY\_CODE,TRADE\_DEPART\_ID,AREA\_CODE,SERIAL\_NUMBER,BUSIFORM\_CUST\_TYPE,TERM\_IP,CUST\_CLASS\_ID,CUST\_ID,CUST\_NAME,CUST\_LINK\_NAME,CUST\_LINK\_PHONE,PRODUCT\_ID,PRODUCT\_NAME,ERROR\_TYPE, ERROR\_CODE,ERROR\_DESC,CLOSE\_SATISFY\_DEGREE,CLOSE\_DESC,RSRV\_STR1,RSRV\_STR2,RSRV\_STR3,RSRV\_DATE1,RSRV\_DATE2, REMARK 
         from  TI\_C\_IOM\_BUSIFORM 
         where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID 
         and accept\_date <= v\_time); 
         delete FROM TI\_C\_IOM\_BUSIFORM 
         where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID 
         and accept\_date <= v\_time; 
         o\_count := o\_count + 1; 
    end if ; 
    commit;  
    EXCEPTION 
        WHEN OTHERS THEN 
           rollback; 
           delete from TI\_CH\_IOM\_BUSIFORM 
            where SUBSCRIBE\_ID =  V\_SUBSCRIBE\_ID; 
            commit; 
    END; 
  END LOOP; 
  close C\_MOVE2HIS; 
  exception 
   when v\_error then 
          RAISE\_APPLICATION\_ERROR(- 20010, 'data is not exists!' ); 
end movetohistory;

\--存储过程调用  
set serveroutput on;  
declare  
v\_date date  := to\_date( '2015/3/24 19:19:21','yyyy-mm-dd hh24:mi:ss' );  
o\_count number;  
begin  
o\_count := 0;  
movetohistory(v\_date,o\_count);  
dbms\_output.put\_line( 'o\_count:'||o\_count);  
end;

exec 存储过程名;

\--存储过程赋权限  
grant create any table to username;  
grant create any procedure to username;  
grant execute any procedure to username;

二、匿名块

\--匿名块：在控制台实现简单输出（输入暂时没实现）  
SET SERVEROUTPUT ON;  
declare  
v\_flow\_templet\_id TD\_M\_NODE\_TEMPLET.flow\_templet\_id% type;  
v\_node\_templet\_id TD\_M\_NODE\_TEMPLET.node\_templet\_id% type;  
begin  
\--v\_node\_templet\_id := &请输入节点名;   -- 这块还没有实现，总是报没有声明的错  
v\_node\_templet\_id := 'BIZOPPORDER';  
SELECT flow\_templet\_id into v\_flow\_templet\_id FROM TD\_M\_NODE\_TEMPLET a WHERE node\_templet\_id = v\_node\_templet\_id;  
dbms\_output.put\_line(v\_flow\_templet\_id);  
EXCEPTION  
\--WHEN NO\_DATA\_FOUND THEN  
\--dbms\_output.put\_line('未找到数据');  
WHEN OTHERS THEN  
dbms\_output.put\_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);  
end;

三、函数

1、函数简单示例

create or replace function tomorrow  
return date --必须有返回  
is  
  today date; --返回值在声明部分  
  nextdate date;  
begin  
  today := sysdate;  
  nextdate := today + 1;  
return nextdate; --return  
exception  
when others then  
  return '-1';  --异常部分有return  
end;

2、通过给函数传参数调用函数

\--函数：有入参  
create or replace function find\_flow\_name(node\_temid in varchar2)  
return VARCHAR2  
is  
v\_flow\_templet\_id TD\_M\_NODE\_TEMPLET.flow\_templet\_id% type;  
begin  
SELECT flow\_templet\_id into v\_flow\_templet\_id FROM TD\_M\_NODE\_TEMPLET a WHERE node\_templet\_id = node\_temid;  
dbms\_output.put\_line(v\_flow\_templet\_id);  
return v\_flow\_templet\_id;  
EXCEPTION  
\--WHEN NO\_DATA\_FOUND THEN  
\--dbms\_output.put\_line('未找到数据');  
WHEN OTHERS THEN  
dbms\_output.put\_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);  
return '-1' ;  
end find\_flow\_name;

\--调用函数  
set serveroutput on;  
declare  
v\_node\_name varchar2( 20):= 'ToOrder\_PreOrderFZX1' ;  
v\_flow\_name VARCHAR2( 20);  
begin  
\--v\_node\_name := 'ToOrder\_PreOrderFZX1';  
v\_flow\_name := find\_flow\_name(v\_node\_name);  
dbms\_output.put\_line( '流程名：'|| v\_flow\_name);  
exception  
when others THEN  
dbms\_output.put\_line( SQLCODE||' AND ' ||SQLERRM);  
end;

四、SQLCODE和SQLERRM使用

set SERVEROUTPUT on;  
DECLARE  
v\_error VARCHAR2( 500);  
BEGIN  
v\_error:=SQLERRM;  
dbms\_output.put\_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);  
END;

收藏  
上一篇：我的友情链接 下一篇：jdbc实现事物管理并搬表

[http://blog.51cto.com/1385903/2045575][http_blog.51cto.com_1385903_2045575]

[WantTao]: https://me.csdn.net/wangtao510
[ORACLE _ PL_SQL]: https://blog.csdn.net/wangtao510/article/category/6291650
[https_blog.csdn.net_wangtao510_article_details_52053599]: https://blog.csdn.net/wangtao510/article/details/52053599
[Link 1]: https://me.csdn.net/super_YC
[https_blog.csdn.net_super_yc_article_details_69226920]: https://blog.csdn.net/super_yc/article/details/69226920
[PL_SQL]: https://www.cnblogs.com/NextAction/p/7366636.html
[Oracle]: https://www.cnblogs.com/NextAction/category/1059045.html
[http_blog.51cto.com_1385903_2045575]: http://blog.51cto.com/1385903/2045575