Oracle存储过程与触发器
存储过程
oracle的存储过程返回结果集是很困难的
参考
https://www.cnblogs.com/linjiqin/category/349944.html oracle存储过程(一):简单入门 - i孤独行者 - 博客园 (cnblogs.com)oracle存储过程(二)：两种循环+基本增删改查操作 - i孤独行者 - 博客园 (cnblogs.com)https://blog.csdn.net/qq_34745941/article/details/109487071 https://www.656463.com/wenda/oracleccgcrhfhjlj_71
语法
<procedure_name>是PROCEDURE的名称; <parameter_name>是要传递的参数的名称IN，OUT或IN，OUT <parameter_data_type>是相应参数的PL / SQL数据类型。
CREATE [OR REPLACE] PROCEDURE <procedure_name> [(
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )] IS|AS
--the declaration section
BEGIN
-- the executable section
EXCEPTION
-- the exception-handling section
END;
调用方式
DECLARE --传入参数使用
BEGIN
myproc;--myproc();
END;
BEGIN
myproc;--myproc();
END;
CALL myproc(); --无参数调用简单些，不能省略()
例子
无参数
CREATE OR REPLACE PROCEDURE myproc
AS
BEGIN
DBMS_OUTPUT.put_line('myproc');
END;
------------------------------------------------例子二
create or replace procedure myDemo02
as
name varchar(10);--声明变量，注意varchar需要指定长度
age int;
begin
name:='xiaoming';--变量赋值
age:=18;
dbms_output.put_line('name='||name||', age='||age);--通过||符号达到连接字符串的功能
end;
CALL mydemo02();
有参数
create or replace procedure myDemo03(name in varchar,age in int)
as
begin
dbms_output.put_line('name='||name||', age='||age);
end;
--调用
begin
myDemo03('xiaoming',18);
end;
--或者
call myDemo03('xiaoming',18);
--一定注意声明变量用;间隔而不是,
DECLARE ne VARCHAR2(10);ae INT;
BEGIN
ne := '清';
ae := 22;
myDemo03(ne,ae);
end;
in out参数在调用存储过程时，=>前面的变量为存储过程的形参且必须于存储过程中定义的一致，而=>后的参数为实际参数。当然也不可以不定义变量保存实参
create or replace procedure myDemo05(name out varchar,age in int)
as
begin
dbms_output.put_line('age='||age);
select 'xiaoming' into name from dual;
end;
----调用方式一
declare
name varchar(10);
age int;
begin
myDemo05(name=>name,age=>10);
dbms_output.put_line('name='||name);
end;
--age=10
--name=xiaoming
----调用方式二
declare
name varchar(10);
age INT :=10; --值默认10
begin
myDemo05(age=>age,name=>name); --参数不一定按顺序
dbms_output.put_line('name='||name);
end;
异常处理
create or replace procedure mydemo0006
as
age int;
begin
age:=10/0;
dbms_output.put_line(age);
exception when others then
dbms_output.put_line('error');
end;
begin
mydemo0006();
end;
存储过程中使用游标
CREATE OR REPLACE PROCEDURE pro_attend_card(VIC_CORPNO VARCHAR2,VIC_YM VARCHAR)
AS
--声明游标
CURSOR C0 IS
SELECT distinct dept_no,branch_no FROM ATTEND_CARD
WHERE CORP_NO = VIC_CORPNO
AND card_ym = VIC_YM;
--声明变量
VC_RULNO BRANCH.RUL_NO%TYPE;
VC_PROJNO BRANCH.PROJ_NO%TYPE;
BEGIN
--循环游标
FOR X IN C0 LOOP
SELECT rul_no,proj_no into vc_rulno,vc_projno FROM BRANCH
WHERE CORP_NO = VIC_CORPNO
AND DEPT_NO = X.DEPT_NO
AND BRANCH_NO = X.BRANCH_NO;
--报错
IF vc_rulno IS NULL THEN
RAISE_APPLICATION_ERROR(-20002,'部门:'|| X.DEPT_NO||'组别:'||X.BRANCH_NO||'没设考勤方案,请找信息中心处理后重算!');
END IF;
IF vc_projno IS NULL THEN
RAISE_APPLICATION_ERROR(-20002,'部门:'||X.DEPT_No||'组别:'||X.BRANCH_NO||'没设薪资方案,请设置后重算!');
END IF;
END LOOP;
END pro_attend_card;
返回结果集
https://www.cnblogs.com/attraction/archive/2004/06/04/13489.html
触发器
行级的触发器里使用:new或:old来访问变更前和变更后的数据。如果是增加新记录操作，则只有:new可以访问；如果是修改操作，则:new和:old都可以访问，:new表示修改后的记录，:old表示修改前的记录；删除则只有:old可以访问，因为该操作是删除已有的记录。
创建触发器:
CREATE [OR REPLACE] TRIGGER <触发器名>
BEFORE|AFTER
INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
[FOR EACH ROW]
WHEN (<条件>)
<pl/sql块>
关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;
关键字"FOR EACH ROW"指定触发器每行触发一次.
关键字"OF <列名>" 不写表示对整个表的所有列.
WHEN (<条件>)表达式的值必须为"TRUE".
特殊变量:
:new --为一个引用最新的列值;
:old --为一个引用以前的列值;
这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;
禁用和启用
禁用某个触发器
ALTER TRIGGER <触发器名> DISABLE
重新启用触发器
ALTER TRIGGER <触发器名> ENABLE
禁用所有触发器
ALTER TRIGGER <触发器名> DISABLE ALL TRIGGERS
启用所有触发器
ALTER TRIGGER <触发器名> ENABLE ALL TRIGGERS
删除触发器
DROP TRIGGER <触发器名>
参考
Oracle触发器用法实例详解 - Sharpest - 博客园 (cnblogs.com)触发器模版
CREATE OR REPLACE TRIGGER 触发器名称
BEFORE|AFTER INSERT OR UPDATE OR DELETE
ON 表名 FOR EACH ROW
DECLARE 变量声明;
BEGIN
IF INSERTING THEN
--插入数据
ELSIF UPDATING THEN
--更新数据
ELSIF DELETING THEN
--删除数据
END IF;
END;
测试
例子一：插入前检查数据
CREATE OR REPLACE TRIGGER BIU_ABSENT_CARDD
BEFORE INSERT OR UPDATE OR DELETE
ON ABSENT_CARDD FOR EACH ROW
DECLARE
BEGIN
--插入和更新前操作
IF inserting OR updating THEN
IF SUBSTR(:NEW.S_YMD,1,6) <> SUBSTR(:NEW.E_YMD,1,6) THEN
RAISE_APPLICATION_ERROR(-20002,'不能跨月,如果需跨月,请分月新增一笔记录!');
END IF;
IF :NEW.CANCEL_DATE > :NEW.E_YMD OR :NEW.CANCEL_DATE < :NEW.S_YMD THEN
RAISE_APPLICATION_ERROR(-20002,'销假日期输入错误!必须在请假有效日期区间内销假!');
END IF;
ELSIF DELETING THEN
DELETE from curvoum_nosign where cur_no='40' AND CORP_NO=:OLD.CORP_NO AND NOTE_NO = :OLD.ABSENT_NO;
END IF;
END BIU_ABSENT_CARDD;
例子二：表序号自增
create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);
--序列
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
--创建触发器插入新id
CREATE OR REPLACE TRIGGER MY_TGR
BEFORE INSERT ON TAB_USER
FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
:NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;
--插入数据
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;
SELECT * FROM tab_user;
测试三：检查当前的操作
CREATE OR REPLACE TRIGGER trisale
BEFORE INSERT OR UPDATE OR DELETE
ON SALE FOR EACH ROW
DECLARE V_TYPE VARCHAR2(10);
BEGIN
IF INSERTING THEN
V_TYPE := 'INSERT';
DBMS_OUTPUT.PUT_LINE(V_TYPE);
ELSIF UPDATING THEN
V_TYPE := 'UPDATE';
DBMS_OUTPUT.PUT_LINE(V_TYPE);
ELSIF DELETING THEN
V_TYPE := 'DELETE';
DBMS_OUTPUT.PUT_LINE(V_TYPE);
END IF;
END;
C#中捕获触发器的错误
触发器中抛出自定义异常，c#的catch中exception的InnerException中有此错误信息
IF(:NEW.S_YMD<>:NEW.E_YMD) THEN
RAISE_APPLICATION_ERROR(-20002,'加班起止日期需一致,请重新选择加班日期!');
END IF;
webapi的异常过滤器
public class WebApiException : ExceptionFilterAttribute
{
/// <summary>
/// 处理异常
/// </summary>
/// <param name="actionContext"></param>
public override void OnException(System.Web.Http.Filters.HttpActionExecutedContext actionContext)
{
if (actionContext.Exception.InnerException != null)
{
var msg = actionContext.Exception.InnerException.InnerException.Message.Split('\n').FirstOrDefault();
}
}
}
浙公网安备 33010602011771号