触发器与存储过程

Oracle触发器与存储过程[转]触发器是特定事件出现的时候，自动执行的代码块。

类似于存储过程，但是用户不能直接调用他们。

功能：1、允许/限制对表的修改2、自动生成派生列，比如自增字段3、强制数据一致性4、提供审计和日志记录5、防止无效的事务处理6、启用复杂的业务逻辑开始create trigger biufer_employees_department_idbefore insert or updateof department_idon employeesreferencing old as old_valuenew as new_valuefor each rowwhen (new_value.department_id<>80 )begin:new_mission_pct :=0;end;/触发器的组成部分：1、触发器名称2、触发语句3、触发器限制4、触发操作1、触发器名称create trigger biufer_employees_department_id命名习惯：biufer（before insert update for each row）employees 表名department_id 列名2、触发语句比如：表或视图上的DML语句DDL语句数据库关闭或启动,startup shutdown 等等before insert or updateof department_idon employeesreferencing old as old_valuenew as new_valuefor each row说明：1、无论是否规定了department_id ，对employees表进行insert的时候2、对employees表的department_id列进行update的时候3、触发器限制when (new_value.department_id<>80 )限制不是必须的。

此例表示如果列department_id不等于80的时候，触发器就会执行。

其中的new_value是代表跟新之后的值。

4、触发操作是触发器的主体begin:new_mission_pct :=0;end;主体很简单，就是将更新后的commission_pct列置为0触发：insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commissio n_pct )values( 12345,’Chen’,’Donny’, sysdate, 12,‘donny@’,60,10000,.25);select commission_pct from employees where employee_id=12345;触发器不会通知用户，便改变了用户的输入值。

触发器类型：1、语句触发器2、行触发器3、 INSTEAD OF 触发器4、系统条件触发器5、用户事件触发器1、语句触发器是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。

能够与INSERT、UPDATE、DELETE或者组合上进行关联。

但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。

比如，无论update多少行，也只会调用一次update语句触发器。

例子：需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。

Create table foo(a number);Create trigger biud_fooBefore insert or update or deleteOn fooBeginIf user not in (‘DONNY’) thenRaise_application_error(-20001, ‘You don’t have access to modify this table.’);End if;End;/即使SYS，SYSTEM用户也不能修改foo表[试验]对修改表的时间、人物进行日志记录。

1、建立试验表create table employees_copy as select *from hr.employees2、建立日志表create table employees_log(who varchar2(30),when date);3、在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。

Create or replace trigger biud_employee_copyBefore insert or update or deleteOn employees_copyBeginInsert into employees_log(Who,when)Values( user, sysdate);End;/4、测试update employees_copy set salary= salary*1.1;select *from employess_log;5、确定是哪个语句起作用？即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断：beginif inserting then-----elsif updating then-----elsif deleting then------end if;end;if updating(‘COL1’) or updating(‘COL2’) then------end if;[试验]1、修改日志表alter table employees_logadd (action varchar2(20));2、修改触发器，以便记录语句类型。

Create or replace trigger biud_employee_copyBefore insert or update or deleteOn employees_copyDeclareL_action employees_log.action%type;Beginif inserting thenl_action:=’Insert’;elsif updating thenl_action:=’Update’;elsif deleting thenl_action:=’Delete’;elseraise_application_error(-20001,’You should never ever get this error.’);Insert into employees_log(Who,action,when)Values( user, l_action,sysdate);End;/3、测试insert into employees_copy( employee_id, last_name, email, hire_date, job_id) values(12345,’Chen’,’Donny@hotmail’,sysdate,12);select *from employees_logupdate employees_copy set salary=50000 where employee_id = 12345;2、行触发器是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：1、定义语句中包含FOR EACH ROW子句2、在BEFORE……FOR EACH ROW触发器中，用户可以引用受到影响的行值。

比如：定义：create trigger biufer_employees_department_idbefore insert or updateof department_idon employees_copyreferencing old as old_valuenew as new_valuefor each rowwhen (new_value.department_id<>80 )begin:new_mission_pct :=0;end;/Referencing 子句：执行DML语句之前的值的默认名称是:old ,之后的值是:newinsert 操作只有:newdelete 操作只有:oldupdate 操作两者都有referencing子句只是将new 和old重命名为new_value和old_value，目的是避免混淆。

比如操作一个名为new的表时。

作用不很大。

[试验]：为主健生成自增序列号drop table foo;create table foo(id number, data varchar2(20));create sequence foo_seq;create or replace trigger bifer_foo_id_pkbefore insert on foofor each rowbeginselect foo_seq.nextval into :new.id from dual;end;/insert into foo(data) values(‘donny’);insert into foo values(5,’Chen’);select * from foo;3、 INSTEAD OF 触发器更新视图Create or replace view company_phone_book asSelect first_name||’, ’||last_n ame name, email, phone_number,employee_id emp_idFrom hr.employees;尝试更新email和nameupdate pany_phone_bookset name=’Chen1, Donny1’where emp_id=100create or replace trigger update_name_company_phone_bookINSTEAD OFUpdate on pany_phone_bookBeginUpdate hr.employeesSet employee_id=:new.emp_id,First_name=substr(:, instr(:,’,’)+2),last_name= substr(:,1,instr(:,’,’)-1),phone_number=:new.phone_number,email=:new.emailwhere employee_id=:old.emp_id;end;4、系统事件触发器系统事件：数据库启动、关闭，服务器错误create trigger ad_startupafter startupon databasebegin-- do some stuffend;/5、用户事件触发器用户事件：用户登陆、注销，CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE / RENAME / TRUNCATE / LOGOFF例子：记录删除对象1. 日志表create table droped_objects(object_name varchar2(30),object_type varchar2(30),dropped_on date);2．触发器create or replace trigger log_drop_triggerbefore drop on donny.schemabegininsert into droped_objects values(ora_dict_obj_name, -- 与触发器相关的函数ora_dict_obj_type,sysdate);end;/3. 测试create table drop_me(a number);create view drop_me_view as select *from drop_me; drop view drop_me_view;drop table drop_me;select *from droped_objects禁用和启用触发器alter trigger <trigger_name> disable;alter trigger <trigger_name> enable;事务处理：在触发器中，不能使用commit / rollback因为ddl语句具有隐式的commit，所以也不允许使用oracle 存储过程的基本语法 2007-4-19 动态网站制作指南(1834)1.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(参数1 IN NUMBER,参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)例子：BEGINSELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTIONWHEN NO_DATA_FOUND THENxxxx;END;...3.IF 判断IF V_TEST=1 THENBEGINdo somethingEND;END IF;4.while 循环WHILE V_TEST=1 LOOPBEGINXXXXEND;END LOOP;5.变量赋值V_TEST := 123;6.用for in 使用cursor...ISCURSOR cur IS SELECT * FROM xxx;BEGINFOR cur_result in cur LOOPBEGINV_SUM :=cur_result.列名1+cur_result.列名2END;END LOOP;END;7.带参数的cursorCURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;OPEN C_USER(变量值);LOOPFETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;do somethingEND LOOP;CLOSE C_USER;8.用pl/sql developer debug连接数据库后建立一个Test WINDOW在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试存储过程基本的语法都已经讲过了，是不是很想试试手啊，不急，我们来看看下面的：【转自】/AXzhz/archive/2006/09/20/510007.html①为什么要使用存储过程?因为它比SQL语句执行快.②存储过程是什么?把一堆SQL语句罗在一起,还可以根据条件执行不通SQL语句.(AX写作本文时观点)③来一个最简单的存储过程CREATE PROCEDURE dbo.testProcedure_AXASselect userID from USERS order by userid desc注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了.AS下面就是一条SQL语句,不会写SQL语句的请回避.④我怎么在中调用这个存储过程?下面黄底的这两行就够使了.public static string GetCustomerCName(ref ArrayList arrayCName,ref ArrayList arrayID){SqlConnection con=ADConnection.createConnection();SqlCommand cmd=new SqlCommand("testProcedure_AX",con);mandType=CommandType.StoredProcedure;con.Open();try{SqlDataReader dr=cmd.ExecuteReader();while(dr.Read()){if(dr[0].ToString()==""){arrayCName.Add(dr[1].ToString());}}con.Close();return "OK!";}catch(Exception ex){con.Close();return ex.ToString();}}注:其实就是把以前SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con);中的SQL语句替换为存储过程名,再把cmd的类型标注为CommandType.StoredProcedure(存储过程)⑤写个带参数的存储过程吧,上面这个简单得有点惨不忍睹,不过还是蛮实用的. 参数带就带两,一个的没面子,太小家子气了.CREATE PROCEDURE dbo.AXzhz/*这里写注释*/@startDate varchar(16),@endDate varchar(16)ASselect id from table_AX where commentDateTime>@startDate and commentDateTime<@endDate order by contentownerid DESC注:@startDate varchar(16)是声明@startDate 这个变量,多个变量名间用【,】隔开.后面的SQL就可以使用这个变量了.⑥我怎么在中调用这个带参数的存储过程?public static string GetCustomerCNameCount(string startDate,string endDate,ref DataSet ds){SqlConnection con=ADConnection.createConnection();//-----------------------注意这一段-------------------------------------------------------------- SqlDataAdapter da=new SqlDataAdapter("AXzhz",con);para0=new SqlParameter("@startDate",startDate);para1=new SqlParameter("@endDate",endDate);da.SelectCommand.Parameters.Add(para0);da.SelectCommand.Parameters.Add(para1);mandType=CommandType.StoredProcedur e;//----------------------------------------------------------------------------------------------- try{con.Open();da.Fill(ds);con.Close();return "OK";}catch(Exception ex){return ex.ToString();}}注:把命令的参数添加进去,就OK了鸟的,改字体颜色的东西太垃圾了,改不好,大家凑活着看.⑦我还想看看SQL命令执行成功了没有.注意看下面三行红色的语句CREATE PROCEDURE dbo.AXzhz/*@parameter1 用户名@parameter2 新密码*/@password nvarchar(20),@userName nvarchar(20)ASdeclare @err0 intupdate WL_user set password=@password where UserName=@userName set @err0=@@errorselect @err0 as err0注:先声明一个整型变量@err0,再给其赋值为@@error(这个是系统自动给出的语句是否执行成功,0为成功,其它为失败),最后通过select把它选择出来,某位高人说可以通过Return返回,超出本人的认知范围,俺暂时不会,以后再补充吧⑧那怎么从后台获得这个执行成功与否的值呢?下面这段代码可以告诉你答案:public static string GetCustomerCName(){SqlConnection con=ADConnection.createConnection();SqlCommand cmd=new SqlCommand("AXzhz",con);mandType=CommandType.StoredProcedure;para0=new SqlParameter("@startDate","2006-9-10");para1=new SqlParameter("@endDate","2006-9-20");da.SelectCommand.Parameters.Add(para0);da.SelectCommand.Parameters.Add(para1);con.Open();try{Int32 re=(int32)cmd.ExecuteScalar();con.Close();if (re==0)return "OK!";elsereturn "false";}catch(Exception ex){con.Close();return ex.ToString();}}注:就是通过SqlCommand的ExecuteScalar()方法取回这个值,这句话是从MSDN上找的,俺认为改成:int re=(int)cmd.ExecuteScalar(); 99%正确,现在没时间验证,期待您的测试!!!⑨我要根据传入的参数判断执行哪条SQL语句!!~下面这个存储过程可以满足我们的要求,竟然是Pascal/VB的写法,Begin----End ,不是{},,,对使用C#的我来说,这个语法有点恶心.........ALTER PROCEDURE dbo.selectCustomerCNameCount@customerID intASif @customerID=-1beginselect contentownerid ,userCName,count(*) as countAll fromview_usercomment group by contentownerid,userCName order by contentownerid DESCendelsebeginselect contentownerid ,userCName,count(*) as countAll fromview_usercomment where contentownerid=@customerID group by contentownerid,userCName order by contentownerid DESCend好了,俺的水平只止于此,也够菜鸟们喝一壶的了,还有更多东西等着我们去发现,无尽的征途!!!!!!!!!!!下面是存储过程的调用方式：存储过程包含三部分：声明，执行部分，异常。