plsql包、触发器和大对象操作

[size=medium]
703

----------------------------------包package-------------------------------------
--Package概念：按照业务逻辑、把相关的Func , Procedure 组织到一起，形成一个函数或者过程集合
--package组成：包说明（package specification）、包体（package body）
--package好处：
1、模块化：一般把有相关性的函数和过程放到一个Package中；
2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这 有利于分工合作；
3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的 公有函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说 明部分出现的函数和过程相当于私有的。
4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该
Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。这 个特性也提醒我们不要去搞巨无霸的Package, 把你用到的任何函数都写到一个Package中 这会导致严重的内存浪费。
5、重载：一个package 中可以定义同名、不同参数的函数或过程。
--package向前声明特性
在Package body中，一个函数中调用另一个函数（也在该Package中），则另一个函数必须在前面先定义； 如果你非要调用在程序代码中后定义的函数，可把这个函数设置成公有函数，在包说明部分说明；
--初始化过程代码
session加载时被执行一次
一般用于一些复杂变量的初始化（比如某个公有变量的初始化值是需要通过一段负责的SQL来获取的）
不需要则ＮＵＬＬ
--package中的变量的持久化状态
package中的各个变量，不同的session不会相互影响。
---------------------------------------------------------------------------------

------------------------------------内置ＳＱＬ工具包-------------------------------

---DBMS_SQL 动态ＳＱＬ
1、DBMS_SQL
CREATE OR REPLACE PROCEDURE delete_all_rows(p_tab_name IN VARCHAR2,
p_rows_del OUT NUMBER) IS
cursor_name INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name,
'DELETE FROM ' || p_tab_name,
DBMS_SQL.NATIVE);
p_rows_del := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
2、EXECUTE IMMEDIATE
CREATE PROCEDURE del_rows(p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER) IS
BEGIN
EXECUTE IMMEDIATE 'delete from ' || p_table_name;
p_rows_deld := SQL%ROWCOUNT;
END;


--DBMS_DDL 程序中执行DDL

1、在程序中执行编译命令
DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')
2、在程序中执行数据收集命令
DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')


--DBMS_JOB 定义job定期执行某个程序

job的定义、提交、更改、停止、移除

DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job => jobno,
what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);',
next_date => TRUNC(SYSDATE + 1),
interval => 'TRUNC(SYSDATE + 1)');
dbms_output.put_line(‘job_no = ‘ || jobno) COMMIT;
END;

查找自己提交的job号

SELECT job, log_user, next_date, next_sec, broken, what FROM DBA_JOBS;

--UTL_FILE 读写外部文件

。。。。。

-----------------------------------------------------------------------------------


--------------------------------------plsql中大对象的操作---------------------------

--oracle数据库中的lob类型：

1、CLOB ：字符大对象，存储在数据库内部；
2、NCLOB：多字节字符大对象，存储在数据库内部；
3、BLOB：二进制大对象，存储在数据库内部；
4、BFILE：二进制文件，存储在数据库外部；

--内部LOB的一般操作步骤

1、在表中添加LOB类型的列
2、在程序中声明和初始化LOB的Locator
3、使用SELECT FOR UPDATE 锁定目标行，准备更新行上的LOB列（LOB的Locator）
4、生成LOB对象，可使用DBMS_LOB 这样的PLSQL包，也可以使用OCI,JDBC等；
5、Commit 提交更改；

--外部LOB BFILE的操作

Bfile是数据库外部文件，在数据库表上这种类型的字段实际只是存储一个Locator

Bfile的使用限制：Bfile是数据库外部文件，是只读的，所以不参与事务操作；用户必须先创建文件并放到
特定的目录下，给予Oracle进程以目录和文件的读取权限；

--oracle directory
控制Bfile存储的安全性
使用Bfile的一般步骤：
1、在操作系统上创建目录，并给Oracle数据库进程赋予阅读权限，把外部文件放入这个目录
2、在Oracle数据库中表添加Bfile类型字段
3、在Oracle 数据库中创建Directory 对象
CREATE DIRECTORY dir_name AS os_path;
4、授权读权限给特定的数据库用户
GRANT READ ON DIRECTORY dir_name TO user|role|PUBLIC;
5、往表中插入数据时使用BFILENAME 函数，它可以关联外部文件和表上的Bfile列
6、在程序中声明和初始化LOB的Locator
7、Select 指定行上Bfile 列到Locator
8、使用DBMS_LOB 或者通过OCI 读取Bfile (使用Locator作为文件的一个引用）

--DBMS_LOB
1、更改LOB的值： APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE
2、读取、检查LOB的值： GETLENGTH, INSTR, READ, SUBSTR
3、Bfile专用：FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN, FILEOPEN
---------------------------------------------------------------------------------------

-------------------------------------oracle触发器trigger-------------------------------
--关键因素
时机：Before 或者After 或Instead of
事件：Insert 或Update 或Delete
对象：表名（或视图名）
类型：Row 或者Statement级；
条件：满足特定Where条件才执行；
内容：通常是一段PLSQL块代码；

-Instead of : 用Trigger的内容替换事件本身的动作
-Row级：SQL语句影响到的每一行都会引发Trigger
-Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）


--创建statement级别Trigger语法
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
trigger_body

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR(-20502,
'You may delete from EMPLOYEES
table only during business hours.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR(-20500,
'You may insert into
EMPLOYEES table only during business hours.');
ELSIF UPDATING('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503,
'You may update
SALARY only during business hours.');
ELSE
RAISE_APPLICATION_ERROR(-20504,
'You may update
EMPLOYEES table only during normal hours.');
END IF;
END IF;
END;

--创建row级别trigger

CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]
trigger_body

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table
(user_name,
timestamp,
id,
old_last_name,
new_last_name,
old_title,
new_title,
old_salary,
new_salary)
VALUES
(USER,
SYSDATE,
:OLD.employee_id,
:OLD.last_name,
:NEW.last_name,
:OLD.job_id,
:NEW.job_id,
:OLD.salary,
:NEW.salary);
END;

--INSTEAD OF Trigger


[/size]