PL/SQL 注意03

1. CREATE PACKAGE ( PACKAGE 有问题, 没搞懂 )

  Packages bundle related PL/SQL types, items, and subprograms into one container.

  A package usually has a specification and a body, stored separately in the database.

有点类似高级语言中的类的定义，尤其象 objective -c 中的类的定义。

  语法 :

  CREATE [ OR REPLACE ] PACKAGE package_name

  IS | AS

    public type and item declarations       --> Declare variables, constants, cursors, exceptions, or types

    subprogram specifications                  --> Declare the PL/SQL subprograms

   END package_name ;

  例如 :

  CREATE OR REPLACE PACKAGE comm_package                   --> SPECIFICATION

  IS

  g_com  NUMBER := 0.1 ;

  PROCEDURE reset_comm( p_comm IN NUMBER ) ;

  END comm_package ;

 

  CREATE[ OR REPLACE ] PACKAGE BODY package_name

  IS | AS

       private type and item declarations             --> Declare private ( like objective-c )

       subprogram bodies

  END package_name;

 例如 :

  CREATE OR REPLACE PACKAGE BODY comm_package

  IS

  FUNCTION validate_comm ( p_comm IN NUMBER )

  RETURN BOOLEAN

  IS

      v_max_comm NUMBER ;

  BEGIN

      SELECT MAX( commission_pct)

      INTO v_max_comm

      FROM employees;

      IF p_comm > v_max_comm THEN RETURN( false ) ;

      ELSE RETURN( TRUE ):

      END IF;

  END validate_comm ;

END comm_package ; 

/

调用 PACKAGE :

  EXECUTE comm_package.validate_comm( 0.15 )

  EXECUTE scott.comm_package.validate_comm( 0.15 )

  EXECUTE comm_package.validate_comm@ny( 0.15 )                --> DB LINK

删除 PACKAGE :

DROP PACKAGE package_name ;  

DROP PACKAGE BODY package_name ;

包的作用:包可以将任何出现在块声明的语句(过程,函数,游标,游标,类型,变量)放于包中,相当于一个容器.将声明语句放入包中的好处是:用户可以从其他PL/SQL块中对其进行引用,因此包为PL/SQL提供了全程变量.

打包的PL/SQL程序和没有打包的有很大的差异，包数据在用户的整个会话期间都一直存在，当用户获得包的执行授权时，就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数，在包内可以用同一个名字声明多个程序，在运行时根据参数的数目和数据类型调用正确的程序

重载 PACKAGE 中的函数等等 可以重载 ( overload ) , 即 可以定义相同的名字,  但是参数不同 .  Only local or packaged subprograms can be overloaded. Stand-alone

subprograms cannot be overloaded .

CREATE OR REPLACE PACKAGE over_pack

 IS

   PROCEDURE add_dept

   ( p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'nuknow' , p_loc IN

      departments.location_id%TYPE DEFAULT 0 );        --> 3个参数, 每个参数独立一行, 好一点

   PROCEDURE add_dept

    ( p_name IN departments.department_name%TYPE, p_loc IN departments.location_id%TYPE )

END over_pack ;

EXECUTE over_pack.add_dept( 980, 'Education', 2500 )

EXECUTE over_pack.add_dept( 'Training', 2400 )

很多函数都是利用这种重载的功能 , 例如系统提供的函数 TO_CHAR

TO_CHAR( P1 DATE )

TO_CHAR(P2 NUMBER)

TO_CHAR(P1 DATE, P2 VARCHAR2)

TO_CHAR(P1 NUMBER, P2 VARCHAR2)

包中也有类似像C语言中的函数声明 , 以 ； 分号结束

One-Time-Only Procedure ( 有点类似 构造函数 )

A one-time-only procedure is executed only once , when the package is first invoked within the user session . ( 初始化使用 )

-----------------------------------------------------SG 13 没看懂 , PACKAGE有问题-------------------------------------------------------------

ORACLE SUPPORT PACKAGE

DBMS_SQL PACKAGE ( open_cursor, parse, bind_variable, execute, fetch_rows, close_cursor )

2. Large Object ( 再补充 )

  A LOB is a data type that is userd to store large , unstructured data such as text, graphic image and so on.

  There are four large object data types :

  ^ BLOB represents a binary large object, such as a video clip .

  ^ CLOB represents a character large object .

  ^ NCLOB represents a multibyte character large object .

  ^ BFILE represents a binary file stored in an operating system binary file outside the database. The BFILE column or attribute stores a file

     locator that points to the external file .

  ^ LOBs are characterized in two ways, according to their interpretation by the Oracle server

     Internal LOBs ( CLOB, NCLOB, BLOB ) are stored in the database.

     External files ( BFILE ) are stored outside the database.

  类型之间不能隐式转换 ( 除非 CLOB 与 VARCHAR2 ) , BFILES can be accessed only in read-only mode from an Oracle server .

  TO_LOB ( 用来将 LONG( 早期数据库使用类型, 用来存储大数据 ) 转换成 LOB )

  LOB locator ( pointer ), LOB value ( real object stored in the database )

  Regardless of where the value of the LOB is stored, a locator is stored in the row. ( 指针指向真正存储大数据的地方, 即便是在数据库中 )

  DIRECTORY  A DIRECTORY is a nonschema database object that provides for administration of access and usage of BFILEs .

  例如 : LOB_PATH = ' /oracle/lob/ '

  ------------------------------------------------------------- 需要再补充 ------------------------------------------------------

3. Creating Database Triggers  

 种类 :  application trigger : fires whenever an event occurs with a particular application 也是DML触发，只不过是在客户端的工具，例如 oracle Form

              database trigger : fires whenever a data event ( such as DML ) or system event ( such as logon or shutdown ) occus on a schema or database 重点

如果在 view 中最好使用 instead of 这个触发器。

database triggers can be system triggers on a database or a schema . with a database, triggers fire for each event for all users; with a schema, triggers fire for each event for that specific user.

A triggering statement contains :

  ^ Trigger timing

     - For table : BEFORE, AFTER

     - For view  : INSTEAD OF

  ^ Triggering event : INSERT, UPDATE, or DELETE

  ^ Table name : On table, view

  ^ Trigger type : Row or statement

  ^ WHEN clause : Restricting condition

  ^ Trigger body : PL/SQL block

  语句级别触发器，是将所有的DML作为一个整体，而 for each row 是独立的，例如 如果使用 after update 这样子，那么语句级触发器就是将所有的全部update之后再执行，而 for each row 则是每一行都执行。即，语句级只执行一次，而行级别则要每一行都执行一次。即 N 次。