plsql 命名块
1、存储过程
（1）存储过程和函数的区别是什么？
存储过程和函数都是存储在数据库中的程序，可由用户直接或间接调用，它们都可以有输出参数，都是由一 系列的 SQL 语句组成。 具体而言，存储过程和函数的不同点如下所示： （1）标识符不同。函数的标识符为 FUNCTION，存储过程为 PROCEDURE。 （2）函数必须有返回值，且只能返回一个值，而存储过程可以有多个返回值。 （3）存储过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，在调用函数时，除了用在SELECT 语句中，在其它情况下必须将函数的返回值赋给一个变量。 （4）函数可以在 SELECT 语句中直接使用，而存储过程不能，例如：假设已有函数 FUN_GETAVG()返回 NUMBER 类型绝对值。那么，SQL 语句“SELECT FUN_GETAVG(COL_A) FROM TABLE”是合法的。
创建存储过程
reate or replace procedure runbyparmeters (isal in emp.sal%type, ---in参数
sname out varchar,sjob in out varchar) ---out、in out
is icount number;
begin
select count(*) into icount from emp where sal>isal and job=sjob;
if icount=1 then
....
else
....
end if;
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
when others then
DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end;
IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去
-- 查看 select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual; ---存储过程定义
select object_name from user_procedures; desc del_emp; -- 可以查看过程的参数个数、名字、类型
show error ---调用错误信息
-- 调用一：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //存储过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
runbyparmeters(realsal,realname,realjob); －－必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
---方法二：（在命令提示符下面测试） var v_dept number; -- 定义一个变量 exec del_emp(20,:v_dept); -- 执行过程 -- 如果要看输出内容，需要 set serveroutput on
备注：只有in参数，调用时无需指定参数：exec prohistorydata(startTime='2010-01-01',endTime='2010-12-30');
-- 删除过程 drop procedure del_emp;
---获取当前正在执行的存储过程的信息 在调用存储过程的时候，发生异常，经常无法判断是那个存储过程发生了错误，而导致问题不好排查，Oracle提供了一个在运行过程中获取存储过程名字的过程：OWA_UTIL.WHO_CALLED_ME。 --1、创建存储过程 create or replace procedure child_proc(id number) as owner_name VARCHAR2(100); caller_name VARCHAR2(100); line_number NUMBER; caller_type VARCHAR2(100); begin OWA_UTIL.WHO_CALLED_ME(owner_name, caller_name, line_number, caller_type); DBMS_OUTPUT.put_line('【id:】 ' || id || ' 【caller_type:】 ' || caller_type || ' 【owner_name:】 ' || owner_name || ' 【caller_name:】 ' || caller_name || ' 【line_number:】 ' || line_number); end; / create or replace procedure parent_proc as v_child_proc VARCHAR2(100) := 'begin child_proc (1); end;'; begin execute immediate v_child_proc; child_proc(2); end; / --2、测试 set serveroutput on; exec parent_proc;
游标参数
调用
2、函数
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual; ---获取函数定义
3、包（package）
是一组相关过程、函数、变量、常量#SinaEditor_Temp_FontName、类型和游标等PL/SQL程序设计元素的组合
--1、定义包规范
create or replace package p_stu
as
--定义结构体
type re_stu is record(
rname student.name%type,
rage student.age%type
);
--定义游标
type c_stu is ref cursor;
--定义函数
function numAdd(num1 number,num2 number)return number;
--定义过程
procedure GetStuList(cid in varchar2,c_st out c_stu);
end;
--2、实现包体，名称一致。
create or replace package body p_stu
as
--游标和结构体，包规范中已声明，包体中不用再声明，直接使用。
--实现方法
function numAdd(num1 number,num2
number)return number
as
num number;
begin
num:=num1+num2;
return num;
end;
--实现过程
procedure GetStuList(cid
varchar2,c_st out c_stu)
as
r_stu re_stu; --直接使用包规范中的结构
begin
open c_st for select name,age
from student where classid=cid;
-- 如果已经在过程中遍历了游标，在使用这个过程的块中，将没有值。
-- loop
--
fetch c_st into r_stu;
-- exit when c_st%notfound;
-- dbms_output.put_line('姓名='||r_stu.rname);
-- end loop;
end;
end;
---调用：
declare
c_stu p_stu.c_stu; --定义包中游标变量
r_stu p_stu.re_stu; --定义包中结构体变量
num number;
begin
--使用及遍历包中过程返回的结果集
p_stu.GetStuList('C001',c_stu);
loop
fetch c_stu into r_stu;
exit when c_stu%notfound;
dbms_output.put_line('姓名='||r_stu.rname);
end loop;
--使用包中函数的方法
select p_stu.numAdd(5,6) into num from dual;
dbms_output.put_line('Num='||num);
end;
4、触发器
是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。
功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
触发器（TRIGGER）的组成主要有以下几部分：
1 触发事件：即在何种情况下触发 TRIGGER。例如：DML 语句（INSERT、UPDATE 和 DELETE 语句对表或视图执行数据处理操作)、DDL 语句（如 CREATE、ALTER、DROP 语句等在数据库中创建、修改、删除模式
对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用 OR 逻辑组合，不能使用 AND 逻辑组合）。
2 触发时间（触发时机）：即该 TRIGGER 是在触发事件发生之前（BEFORE）还是之后（AFTER）触发，也就是触发事件和该 TRIGGER 的操作顺序。如果指定为 BEFORE，那么表示在执行 DML 操作之前触发，以便防
止某些错误操作发生或实现某些业务规则；如果指定为 AFTER，那么表示在执行 DML 操作之后触发，以便记录该操作或做某些事后处理。
3 触发器本身：即该 TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。
4 触发频率：说明触发器内定义的动作被执行的次数，分为语句级（STATEMENT）触发器和行级（ROW）触发器。
a. 语句级（STATEMENT）触发器：是指当某触发事件发生时，该触发器只执行一次；语句级触发器不允许和 WHEN 子句一起使用。
b. 行级（ROW）触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
c. 当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。换句话说，语句级触发器针对某一条语句只触发一次，而行级触发器则针对语句所影响的每一行都触发一次。
例如：某条 UPDATE 语句修改了表中的 100 行数据，那么针对该 UPDATE 事件的语句级触发器将被触发一次，而行级触发器将被触发 100 次。
5 触发对象：包括表、视图、用户、数据库。只有在这些对象上发生了符合触发条件的触发事件时，才会执行触发操作。
6 触发条件：由 WHEN 子句指定一个逻辑表达式。只有当该表达式的值为 TRUE 时，遇到触发事件才会自动执行触发器，使其执行触发操作。
4.1、DML 触发器
创建 DML 触发器的一般语法如下所示：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] table_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;
其中：
BEFORE 和 AFTER 指出触发器的触发时机为前触发还是后触发，前触发是在执行触发事件之前触发，后触发是在执行触发事件之后触发当前所创建的触发器。
FOR EACH ROW 选项说明触发器为行级触发器。当省略 FOR EACH ROW 选项时，BEFORE 和 AFTER触发器为语句级触发器，而 INSTEAD OF 触发器则为行级触发器。
REFERENCING 子句说明相关名称，在行级触发器的 PL/SQL 块和 WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为 OLD 和 NEW。在触发器的 PL/SQL 块中应用相关名称时，必须在它
们之前加冒号，但在 WHEN 子句中则不能加冒号。
WHEN 子句说明触发约束条件。当 Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用 PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在 INSTEAD OF 行触发器和其它类型的触发器中。
DML 触发器有如下几种事件：
1、 INSERTING：当触发事件是 INSERT 时，取值为 TRUE，否则为 FALSE。
2、 UPDATING[（column_1,column_2,…,column_x）]：当触发事件是 UPDATING 时，若修改了
column_x 列，则取值为 TRUE，否则为 FALSE。其中，column_x 是可选的。
3、 DELETING：当触发事件是 DELETE 时，则取值为 TRUE，否则取值为 FALSE。
DML 触发器有如下的限制条件：
CREATE TRIGGER 语句文本的字符长度不能超过 32KB
触发器体内的 SELECT 语句只能为 SELECT … INTO … 结构，或者为定义游标所使用的 SELECT 语句
触发器中不能使用事务控制语句 COMMIT、ROLLBACK 和 SAVEPOINT
由触发器所调用的存储过程或函数也不能使用数据库事务控制语句
触发器中不能使用 LONG、LONG RAW 类型
触发器内不能通过:NEW 修改 LOB 列中的数据
触发器最多可以嵌套 32 层
--建立日志表
CREATE TABLE SCOTT.MYLOG_LHR
(CUR_USER VARCHAR2(30), CUR_DATE DATE, ACTION VARCHAR2(30));
--建立触发器
CREATE OR REPLACE TRIGGER SCOTT.TRI_DML_EMP_LHR
AFTER INSERT OR DELETE OR UPDATE ON SCOTT.EMP
BEGIN
IF INSERTING THEN
INSERT INTO SCOTT.MYLOG_LHR
(CUR_USER, CUR_DATE, ACTION)
VALUES
(USER, SYSDATE, 'I');
ELSIF DELETING THEN
INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'D');
ELSE
INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'U');
END IF;
END;
/
测试语句级触发器：
SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;
no rows selected
SYS@lhrdb> DELETE SCOTT.EMP WHERE ROWNUM<=1;
1 row deleted.
SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;
CUR_USER CUR_DATE ACTION
------------------------------ ------------------- ------------------------------
SYS 2016-10-26 14:14:37 D
4.2、替代触发器（INSTEAD OF 触发器）
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF {INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON [schema.] view_name --只能定义在视图上 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] --因为 INSTEAD OF 触发器只能在行级上触发,所以没有必要指定 [WHEN condition] PL/SQL_block | CALL procedure_name;
其中，INSTEAD OF 选项使 Oracle 激活触发器，而不执行触发事件。只能对视图和对象视图建立 INSTEADOF 触发器，而不能对表、模式和数据库建立 INSTEAD OF 触发器。其它选项和 DML 触发器的语法相同。
创建替代触发器需要注意以下几点内容： 1 只能创建在视图上，并且该视图没有指定 WITH CHECK OPTION 选项。 2 不能指定 BEFORE 或 AFTER 选项。 3 FOR EACH ROW 是可选的，即替代触发器只能是行级触发器，所以，没有必要指定。 4 没有必要在针对一个表的视图上创建替代触发器，只要创建 DML 触发器就可以了。 5 每一个表和视图只能有一个替代触发器。 6 替代触发器被用于更新那些没有办法通过正常方式更新的视图。 7 替代触发器的主要优点就是可以使不能更新的视图支持更新。它支持多个表中数据的插入、更新和删除操作。
CREATE OR REPLACE TRIGGER SCOTT.EMP_VIEW_DELETE
INSTEAD OF DELETE ON SCOTT.VW_EMP_LHR
FOR EACH ROW
BEGIN
DELETE FROM SCOTT.EMP WHERE DEPTNO = :OLD.DEPTNO;
END;
测试替代触发器，如下所示：
SYS@lhrdb> DELETE FROM SCOTT.VW_EMP_LHR WHERE DEPTNO=10;
1 row deleted.
SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE DEPTNO=10;
no rows selected
4.3、DDL 触发器和系统触发器
由于 DDL 触发器和系统触发器的创建语法很相似，所以，在此一并讲解。其实，该部分内容涉及到面试中的一个问题，那就是，“如何监控会话的登录登出情况？”，答案就是使用审计或系统触发器来实现。下面将详 细讲解该部分的内容。系统触发器可以在 DDL 或数据库系统上被触发。DDL 指的是数据定义语言，如 CREATE、ALTER 及 DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与登出、数据库服务错误等。 值得注意的一点是，隐含参数“_SYSTEM_TRIG_ENABLED”的默认值是 TRUE，即允许 DDL 和系统触发器。当设置隐含参数“_SYSTEM_TRIG_ENABLED”为 FALSE 的时候，将禁用 DDL 和系统触发器。
---创建系统触发器的一般语法如下所示： CREATE OR REPLACE TRIGGER [sachema.]trigger_name {BEFORE|AFTER} {DDL_EVENT_LIST | DATABASE_EVENT_LIST} ON { DATABASE | [schema.]SCHEMA } [WHEN condition] PL/SQL_block | CALL procedure_name;
其中：
DDL_EVENT_LIST：一个或多个 DDL 事件，事件间用 OR 分开，可以对所有 DDL 语句监控（直接写 DDL），也可以对个别 DDL 语句监控。
DATABASE_EVENT_LIST：一个或多个数据库事件，事件之间用 OR 分开。
ON：系统触发器按照作用范围，分为 SCHEMA 触发器和 DATABASE 触发器。SCHEMA 触发器作用在单个用户上，而 DATABASE 触发器作用在整个数据库所有用户上。
对于 WHEN 子句后边的内容需要由小括号括起来。
需要注意的是，系统触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式（SCHEMA）之上时，只有模式所指定用户的 DDL 操作和该用户操作所导致的错误才能激活触发器，默认时为当前用户模式。
当建立在数据库（DATABASE）之上时，该数据库所有用户的 DDL 操作和所有用户操作所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器，要求用户具有 ADMINISTER DATABASE
TRIGGER 权限或具有 DBA 角色。一般应该赋予的权限包括 ADMINISTER DATABASE TRIGGER、ALTER ANY TRIGGER 和 CREATE ANY TRIGGER。
就像 DML 触发器一样，系统触发器可以使用 WHEN 子句来指定触发器激活条件。关于系统触发器需要了解以下几点：
STARTUP 和 SHUTDOWN 触发器不能带有任何条件。
SERVERERROR 触发器可以使用 ERRNO 测试来检查特定的错误。
LOGON 和 LOGOFF 触发器可以使用 USERID 或 USERNAME 测试来检查用户标识或用户名。
DDL 触发器可以检查正在修改对象的名称、类型和操作类别。
DDL 触发器有很多实际用途，如下所示：
1 建表的同时建立公共同义词
2 阻止非授权用户的 TRUNCATE 操作
3 记录所有的 DDL 语句（包括 SERVERERROR、GRANT、SHUTDDOWN、ALTER、REVOKE、DROP、TRUNCATE、
COMMENT、STARTUP、AUDIT、CREATE、ANALYZE），以便查找责任人
4 阻止 DDL 操作
5 记录服务器错误
6 填充 V$SESSION 的 CLIENT_INFO 和 CLIENT_IDENTIFIER 列
7 记录用户登录数据库失败的详细信息
8 监控会话的登录登出情况
select DBMS_METADATA.GET_DDL('TRIGGER','TRIGGERNAME','USERNAME) FROM DUAL; ---获取trigger ddl定义
4.4 禁用启用触发器
要禁用或启用表的所有触发器，可以使用 ALTER TABLE 语句，如下所示： ALTER TABLE T_20161026_LHR DISABLE ALL TRIGGERS; --禁用触发器 ALTER TABLE T_20161026_LHR ENABLE ALL TRIGGERS; --启用触发器 将触发器设置为禁用或启用使用 ALTER TRIGGER 语句，如下所示： ALTER TRIGGER TRIGGER_NAME ENABLE; ALTER TRIGGER TRIGGER_NAME DISABLE;
重新编译触发器的语句为：
ALTER TRIGGER [SCHEMA.] TRIGGER_NAME COMPILE;
4.5注意事项
最后，介绍一下编写触发器的一些注意事项： 触发器不接受参数。 一个表上最多可以有 12 个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。 在一个表上的触发器越多，对在该表上的 DML 操作的性能影响就越大。 触发器最大为 32KB。若确实需要，则可以先建立存储过程，然后在触发器中调用存储过程。 在触发器的执行部分只能使用 DML 语句（例如 SELECT、INSERT、UPDATE、DELETE 等），不能使 用 DDL 语句（例如 CREATE、ALTER、DROP 等）。 触发器中不能包含事务控制语句（例如 COMMIT、ROLLBACK、SAVEPOINT 等）。因为触发器是触发 语句的一部分，当触发语句被提交、回退时，触发器也被提交、回退了。 在触发器主体中调用的任何存储过程、函数，都不能使用事务控制语句。 在触发器主体中不能声明任何 LONG 或 BLOB 变量。
5、记录客户端连接ip信息（其他常用触发器参考实战脚本3触发器）
--1、利用 DBMS_SESSION 过程包. BEGIN DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS')); END; --2、通过触发器 create or replace trigger on_logon_trigger after logon on database begin dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) ); end; --3、在v$session的client_info列会记录其相应的IP信息 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc;
6、游标
---查看系统最大游标数
show parameter open_cursors; select NAME,VALUE,DESCRIPTION from v$parameter where name = 'open_cursors';
---查看打开的游标数
select count(*) from v$open_cursor;
---查询游标使用情况以及游标最大数
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME = 'opened cursors current' AND P.NAME = 'open_cursors' GROUP BY P.VALUE;
---通过sid查询具体sql
select o.sid, q.sql_text from v$open_cursor o, v$sql q where q.hash_value = o.hash_value and o.sid = 214;
---查询某个用户的游标使用情况
select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs
from v$open_cursor o, v$session s
where user_name = 'GLOGOWNER'
and o.sid = s.sid
group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type
order by num_curs desc;
---各用户打开的游标总数
SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;
---查找数据库各用户各个终端的缓存游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE)
FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'session cursor cache count') AA
GROUP BY AA.USERNAME, AA.MACHINE
ORDER BY AA.USERNAME, AA.MACHINE;
---查找数据库各用户各个终端的打开游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE)
FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'opened cursors current') AA
GROUP BY AA.USERNAME, AA.MACHINE
ORDER BY AA.USERNAME, AA.MACHINE;
浙公网安备 33010602011771号