1 存储过程 create or replace procedure <> [(in|out|in out)] is |as begin end [procedurename]; 存储过程是用
1 存储过程
create or replace procedure <> [(in|out|in out)] is |as
begin
end [procedurename];
存储过程是用来完成用户的某种操作，
存储过程的调用 exec procedure;
默认值用(v_emp int number default 7788)
1 create or replace procedure show_sal(v_empno in number) as 2 v_sal number; 3 begin 4 select sal into v_sal from emp where empno = v_empno; 5 dbms_output.put_line(v_sal); 6* end show_sal;# 存储过程已经创建完成，下面需要调用SQL> exec show_sal(7839);9500PL/SQL procedure successfully completed存储过程返回值
1 declare 2 v_emp number :=7788; 3 v_sal number; 4 begin 5 show_sal(v_emp,v_sal); 6 dbms_output.put_line(v_emp||' '||v_sal); 7* end;SQL> /7788 5000PL/SQL procedure successfully completed.# 返回一个值注意：存储过程调用过程中如果只单独调用存储过程 exec procudername . 如果在另一个程序中调用，则直接用procudername（）；
create or replace procedure show_in_out(v_phone in out varchar2) asv_title varchar2(10);v_last varchar2(50);v_show varchar2(50);beginv_title :=substr(v_phone,1,3);v_last :=substr(v_phone,4,length(v_phone)-3);v_show :='['||v_title||']'||'-'||v_last;v_phone :=v_show;end;/# 统一个参数既做输入又做输出，一个能修改电话号码格式的存储过程# 下面掉用： 1 declare 2 vphone varchar2(20) :=☎ 3 begin 4 show_in_out(vphone); 5 dbms_output.put_line(vphone); 6* end;SQL> /Enter value for phone: '02867676768'old 2: vphone varchar2(20) :=☎new 2: vphone varchar2(20) :='02867676768';[028]-67676768PL/SQL procedure successfully completed.# 电话号码区号已经被修改为固定的格式注意：传入参数不可以直接被用来传值，但是统一个参数既可以做输入，又可以做输出，这个变量是可以被赋值的。
查看存储过程的在表里面的记录：
select object_name,procedure_name from user_procedures;select name,text from user_source；
show parameter;# 里面会有下面这个参数，设置列告警日志存放的位置background_dump_dest string /u01/product/diag/rdbms/orcl/o rcl/traceSQL> select name,value from v$parameter where name = 'background_dump_dest';NAME--------------------------------------------------------------------------------VALUE--------------------------------------------------------------------------------background_dump_dest/u01/product/diag/rdbms/orcl/orcl/trace# 在上面显示的路径后面跟上 实例名称.log结尾的日志，比如：# -rw-r----- 1 oracle dba 91750 Dec 1 09:14 alert_orcl.log# 当前实例查看SQL> select name from v$database;NAME---------ORCLalter procedure show_sal compile 重新编译存储过程。
/u01/product/11g/rdbms/admin/utlrp.sql 执行这个文件就会批量编译
执行文件用 @ /filename
2 函数
create or replace function functionname [in|out|in out]
return <datatype>
is|as
begin
exception
end functionname;
1 create or replace function fun_1(v_empno in number) return varchar2 is 2 v_ename varchar2(20); 3 begin 4 select ename into v_ename from emp where empno = v_empno; 5 return v_ename; 6* end;SQL> /Function created.# 函数创建编译成功SQL> select fun_1(7788) from dual;FUN_1(7788)--------------------------------------------------------------------------------SCOTT# 调用成功函数同样可以像存储过程那样用out 返回参数
1 create or replace function fun_2(in_empno in number,out_job out varchar2) 2 return varchar2 is 3 v_re_name varchar2(20); 4 begin 5 select ename,job into v_re_name,out_job from emp where empno = in_empno; 6 return v_re_name; 7* end;# 下面调用 1 declare 2 v_job varchar2(20); 3 v_name varchar2(20); 4 begin 5 v_name :=fun_2(7788,v_job); 6 dbms_output.put_line(v_name||' '||v_job); 7* end;SQL> /SCOTT ANALYST在数据库中查看之前函数的逻辑
SQL> select text from user_source where name = 'FUN_1';TEXT--------------------------------------------------------------------------------function fun_1(v_empno in number) return varchar2 isv_ename varchar2(20);beginselect ename into v_ename from emp where empno = v_empno;return v_ename;end;6 rows selected.
3 工作下常用的包
dbms_rowid 回复数据用的包
dbms_metadata：查看数据库中对象的创建逻辑源代码
dbms_monitor :对数据库监控用到的
dbms_output:输出
dbms_redefinition:普通表转换成分区表
utl_file :将数据库中表输出，以文件的方式来显示。相当于输出到文本。
utl_mail :对数据库监控的过程中可以将信息通过邮件发送。
dbms_addm:对数据库监控统计信息的收集
dbms_advisor:提供数据库的优化提供建议
raise_application_error:抛出异常。
dbms_auto_task_admin:自动任务管理
4 触发器
当用户执行操作时，触发器用来完成某个特定的动作
分类：
4.1 DML 触发
当用户执行DML 语句的时候触发器完成某个动作。
insert,update,delete 触发
4.2 替代触发
通常状态下使用到视图中，当用户对视图进行操作的时候遇到视图有限制，而不可能完成，这个时候触发触发器，替代视图完成某种动作。比如：复杂的视图是不可以插入数据的，这个时候如果我们一定要在视图中插入数据就用触发器替代我们取插入数据。
4.3 事件触发
database| schema
用户在关闭数据库或者打开数据库引发的时间，登录退出数据库的时候引发的事件。
4.4 触发时机
after|before
动作之前或者之后触发。对于DML 之前和之后是没有区别的
对于事件触发之前和之后有区别
4.5 触发动作
是DML，DDL，还是事件触发
4.6 触发顺序
语句级触发：满足条件的行只触发一次
行级触发：for each row，满足条件每一行都触发一次
4.7 触发器关键谓词
inserting 表示插入动作
updating 更新动作
deleting 删除动作
：new
比如、：new.sal
：old
比如，：old.sal
4.8 触发器中执行的动作
DML 或者DQL ，不能执行DDL 操作，也不能执行TCL （事物操作，回退，提交）
4.9 触发器语法：
DML 触发
create or replace trigger triggername
before|after insert|or update|or delete of <column>
on tablename for each row
begin
触发器要执行的操作（操作内容不能超过32K）
end；
不跟for each row 默认就是语句级触发
同一个对象下涉及到的触发器不能超过12个
同一个对象中不能存在相同功能的触发器
DML 触发：
1 create or replace trigger emp_sal_tirgger 2 before update of sal on emp for each row 3 begin 4 insert into tmp_emp (empno,sal) values(:old.empno,:old.sal); 5 insert into tmp_emp (empno,sal) values(:old.empno,:new.sal); 6* end;SQL> /SQL> update emp a set a.sal=5500 where a.empno = 7788;1 row updated.SQL> select * from tmp_emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO---------- 7788 5000 7788 5500#上面触发器已经工作了，记录下列之前和之后的数据SQL> rollback;Rollback complete.SQL> select * from emp where empno = 7788; EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO---------- 7788 SCOTT ANALYST 7566 19-APR-87 5000 20# 更新表后回滚，不更新数据，SQL> select * from tmp_emp;no rows selected# 触发器插入的数据也回滚列，触发器的回滚提交都是和被触发对象一起的。SQL> create or replace trigger emp_trigger 2 before insert or update or delete on emp for each row 3 begin 4 if inserting then 5 dbms_output.put_line('we are insert data'); 6 end if; 7 if updating then 8 dbms_output.put_line('we are update date'); 9 end if; 10 if deleting then 11 dbms_output.put_line('we are delete data'); 12 end if; 13 end emp_trigger; 14 /# 使用触发器谓词SQL> update emp a set a.sal = 4000 where a.empno = 7788;we are update date1 row updated. # 谓词生效替代触发：
# 替代触发# 先建一个视图SQL> create or replace view tmp_view as 2 select a.deptno,avg(a.sal) avg_sal from emp a group by a.deptno;#查看视图SQL> select * from tmp_view; DEPTNO AVG_SAL---------- ---------- 30 1566.66667 20 2218.75 10 5708.33333#对视图进行插入数据，会报错SQL> insert into tmp_view select 80,5000 from dual;insert into tmp_view select 80,5000 from dual*ERROR at line 1:ORA-01733: virtual column not allowed here# 下面我们新建一个替代触发器SQL> create or replace trigger emp_instead_triger 2 instead of insert or update on tmp_view 3 begin 4 insert into emp (empno,sal,ename) select max(empno)+1,max(sal)+1000,'WZL' from emp; 5 end emp_instead_triger; 6 /Trigger created.SQL> insert into tmp_view select 80,5000 from dual;we are insert data1 row created.SQL> commit;Commit complete.# 现在对视图插入数据的时候触发了替代触发器，在emp 里面插入了数据，同时在插入数据的时候又触发了之前emp上的触发器，打印列语句we are insert data，我们查看emp 表里面的数据是不是插入了select * from emp； 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1950 10 7935 WZL 10500事件触发：
datebase 级别事件，针对整个数据库
schema级别事件，只正对当前创建触发器的一个用户
事件谓词：
logon，在登录之后触发，after
logoff，在退出之前触发，before
shutdown，在之前触发，before
startup，在中
5 DDL 触发器
1 create or replace trigger ddl_tigger 2 before drop or alter on scott.schema 3 begin 4 raise_application_error(-20001,'you are not allowed drop or alter table'); 5* end;SQL> /Trigger created.SQL> drop table EMP3;drop table EMP3*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-20001: object is not dropORA-06512: at line 2# 不能删除表6 触发器禁用
alter trigger trigger_name
SQL> select trigger_name,status from user_triggers;TRIGGER_NAME STATUS------------------------------ --------EMP_SAL_TIRGGER ENABLEDEMP_TRIGGER ENABLEDEMP_INSTEAD_TRIGER ENABLEDSQL> alter trigger EMP_SAL_TIRGGER disable;Trigger altered.# 触发器已经禁用
