写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创建触发器如下图所示: