oracle PL/SQL

写pl-sql的一个很方便的工具是PLSQL Developer,但是也不能一丢了工具就写不出一个语法正确的函数或存储过程。现将函数、存储过程、触发器的框架总结如下。

1 函数

CREATE OR REPLACE FUNCTION 用户名或方案名.函数名    (
  入参列表)     
return  返回值类型    

as     

    /*变量声明*/      
  
begin     

       sql 语句     

exception     
     
   WHEN 异常名称  RETURN -1;     
     
end;

例如：

CREATE OR REPLACE FUNCTION CHLOE_DBA."AVGSELL"     (
  cur_barcode VARCHAR2     
)     
return FLOAT    
as     
    /*变量声明*/      
   v_selldaysref f_prod_info.selldaysref%TYPE;    /*根据表的列指定大小*/ 
   v_avgsell NUMBER(7,2);  /*直接指定大小*/      
begin     
   SELECT selldaysref into v_selldaysref from vprod_info WHERE prod_barcode=cur_barcode;   
   IF (v_selldaysref <=0) OR (v_selldaysref IS NULL) THEN     
        RETURN -1;     
   END IF;     
   
   SELECT SUM(sellnum)/v_selldaysref  INTO v_avgsell FROM prod_sell     
   WHERE barcode=cur_barcode AND selldate>=SYSDATE()-v_selldaysref  
   GROUP BY barcode;       
   IF (v_avgsell<=0) OR (v_avgsell IS NULL) THEN     
       RETURN -1;    
   ELSE     
       RETURN v_avgsell;    
   END IF;    
     
exception     
     
   WHEN NO_DATA_FOUND THEN  RETURN -1;     
     
end;

注意：入参列表和返回值不需要指定大小，只需要指定类型，而变量声明一定要指定大小。
通过OEM创建函数会隐藏一部分语法细节，如下图所示：

2 存储过程

create or replace procedure 用户名或方案名.过程名(入参列表) is
   /*参数列表*/
begin

  /*处理内容sql语句*/

exception

  WHEN 异常名称 THEN

end 过程名称;

最后的“end 过程名称;”也可以直接写成“end;” ，参数列表后面用“is”或“as”均可。例如

create or replace procedure chloe_dba.initiate(staffno   in VARCHAR2,
                                               errorcode out NUMBER,
                                               errormsg  out VARCHAR) as
begin
  errorcode := 0;
  errormsg  := 'no error';
  SAVEPOINT startpoint; /*========保存点，开启事务==========*/
  /*采购相关*/
  delete from purch_sugg_d;
  /*销售相关*/
  delete from orders_info;
    /*库存相关*/
  delete from warehouse_list_d;
  commit;

exception

  WHEN OTHERS THEN
    ROLLBACK TO SAVEPOINT startpoint;
    errorcode := -1; /*SQLCODE可以获得错误码*/
    errormsg  := SQLERRM;
    DBMS_OUTPUT.PUT_LINE('【异常】：' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('【信息】：很遗憾，初始化失败...操作已回滚.');
    RETURN;

end;

通过OEM创建存储过程与函数类似：

 

3. 触发器

触发器有多种类型：根据事件划分有insert，update，delete触发器，根据触发时刻划分有after和before触发器

CREATE OR REPLACE TRIGGER 方案名.触发器名 AFTER/BEFORE
UPDATE/DELETE/INSERT OF 列名 ON 表明 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
when 触发条件
BEGIN
   /*触发后操作内容*/ 
END;

例如：

CREATE OR REPLACE TRIGGER "CHLOE_DBA".AFTER_PASSTRADES AFTER
UPDATE OF "CHECKER_NO", "CHECK_TIME", "SELLER_MODIFIED", "SYS_STATUS" ON "CHLOE_DBA"."TRADES_INFO" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
when ((OLD.sys_status='imported') AND (NEW.sys_status='audited'))
BEGIN
     INSERT INTO modify_log
    VALUES
      (:NEW.checker_no,
       'TRADES_INFO',
       'SYS_STATUS',
       :OLD.tid,
       :NEW.seller_modified,
       'imported',
       'audited',
       '订单' || :OLD.tid || '审核通过');
  END;

 通过OEM创建触发器如下图所示：