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

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