Oracle数据库 PL/SQ入门

转载：http://www.cnblogs.com/reonlyrun/archive/2007/03/09/669164.html

ORACLE PL/SQ入门

最近在学习PL/SQL中的存储过程，看了不少资料。总结在此，基本可以做为新手的入门资料吧。

一、块
    1.块结构
        1)块的三个部分
        2)块语法
    2.块的命名和匿名
    3.块的执行
二、变量、常量与字符集
    1.变量
        1)声明变量
        2)给变量赋值
    2.常量
    3.有效字符集
三、分支语语句
    1.条件
        1)IF条件判断逻辑结构
        2)CASE表达式
    2.循环
        1)LOOP…EXIT…END循环控制语句
        2)WHILE…LOOP循环控制语句
        3)FOR…LOOP循环控制语句
    3.跳转
    4.嵌套
四、异常
    1.简介
    2.预定义异常
    3.自定义异常
五、游标
    1.声明游标
    2.打开游标
    3.从游标中取数据
    4.关闭游标
    5.隐式游标
    6.实例
    7.游标的属性
        1)%ISOPEN属性
        2)%FOUND属性
        3)%NOTFOUND属性
        4)%ROWCOUNT属性
六、存储过程
    1.命令格式
    2.调用
    3.释放
    4.实例：
七、函数
    1.命令格式
    2.调用
    3.释放
    4.实例
八、触发器
    1.触发器的创建规则：
    2.可以创建被如下语句所触发的触发器：
    3.注意事项
    4.删除触发器的语句格式为：
    5.实例
九、包
    1.包头
    2.包体
    3.实例

ORACLE PL/SQ入门

一、块

1.块结构
  PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分。

  1)块的三个部分
  ①声明部分（Declaration section）
    声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分。
  ②执行部分（Executable section）
    执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。
  ③异常处理部分（Exception section）
    这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论在后面进行。

  2)块语法
  PL/SQL块语法结构如下：

[ DECLARE ]
  Declaration Statements
BEGIN
  Executable Statements
   [ EXCEPTION Exception Handlers ]
END

  PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以多行，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。注释由–标示。

2.块的命名和匿名
  PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块，匿名程序块可以用在服务器端也可以用在客户端。
  执行部分包含了所有的语句和表达式，执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字 END结束。分号分隔每一条语句，使用赋值操作符:=或SELECT INTO或FETCH INTO给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块。
  所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能在屏幕上显示SELECT语句的输出。SELECT语句必须包括一个INTO子串或者是 游标的一部分，执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句 COMMIT和ROLLBACK可以在执行部分使用，数据定义语言(Data Definition language)不能在执行部分中使用，DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。

3.块的执行
  SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行。
  命名的程序与匿名程序的执行不同，执行命名的程序块必须使用EXECUTE关键字。
  如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要EXECUTE关键字。
  注意：如果在PL/SQL Developer中执行需在EXECUTE前后加上BEGIN和END关键字。

二、变量、常量与字符集

1.变量

  1)声明变量
  声明变量的语句格式如下：
    Variable_Name [CONSTANT] databyte [NOT NULL] [:=DEFAULT EXPRESSION]
  注意：可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。

  2)给变量赋值
  给变量赋值有两种方式：
  ①直接给变量赋值

eno : =   7369 ;
myname : =   ’ SCOTT ’ ;

  ②用户交互赋值

eno : =   & empno;

    运行时系统会提示用户输入empno，用户输入的值将存入eno变量。
  ③通过SQL SELECT INTO 或FETCH INTO给变量赋值

SELECT  EMP_NAME  INTO  MyName  FROM  EMPLOYEES  WHERE  EMPID  =  eno;

  注意：只有在该查询返回一行的时候该语句才可以成功否则就会抛出异常。

2.常量
  常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。
  为了减少这部分程序的修改，编程时使用%TYPE、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同步，随表的变化而变化，这样的程序在一定程度上具有更强的通用性。

3.有效字符集
① 所有的大写和小写英文字母；
② 数字0-9；
③ 符号：0＋一*／＜＞＝！一；:.‘＠％，“‘＃“＆＿｝｛｝？[]；
PL/SQL标识符的最大长度是30个字符，并且不区分字母的大小写。但是适当地使用大小写，可以提高程序的可读性。

例如：定义如下若干类型变量，常量。

DECLARE
  ORDER_NO     NUMBER ( 3 );
  CUST_NAME    VARCHAR2 ( 20 );
  ORDER_DATE  DATE;
  EMP_NO       INTEGER  : =   25 ;
   PI  CONSTANT  NUMBER  : =   3.1416 ;
BEGIN
   NULL ;
END ;

① 算术操作符

+	加
-	减
*	乘
/	除
**	乘方

② 关系操作符

<	小于
<=	小于等于
>	大于
>=	大于等于
=	等于
!=	不等于
<>	不等于
:=	赋值

③ 比较操作符

IS NULL	如果操作数为NULL返回TRUE
LIKE	比较字符串值
BETWEEN	验证值是否在范围之内
IN	验证操作数在设定的一系列值中

④ 逻辑操作符

AND	两个条件都必须满足
OR	只要满足两个条件中的一个
NOT	取反

三、分支语语句

1.条件

  1)IF条件判断逻辑结构
  If条件判断逻辑结构有三种表达方式。
  ①表达式一：

IF  Condition  THEN
  Statement;
END   IF ;

  该表达式的功能为：若条件为真，执行then后的语句；否则，跳出条件语句执行end if后的语句。
  ②表达式二：

IF  Condition  THEN
  Statements_1;
ELSE
  Statements_2;
END   IF ;

  该表达式的功能为：如果条件为真执行then后的语句，否则执行else后的语句。
  ③表达式三：

IF  Condition1  THEN
  Statements_1;
ELSEIF Condition2  THEN
  Statements_2;
ELSE
  Statements_3;
END   IF ;

  该表达式的功能为：如果if后的条件成立，执行then后面的语句，否则判断elseif后面的条件，条件成立执行第二个then后面的语句，否则执行 else后的语句。这是条件语句嵌套。IF 可以嵌套，可以在IF 或IF ..ELSE语句中使用IF或IF…ELSE语句。

  2)CASE表达式
  CASE语句的基本格式如下：

CASE  Grade
   WHEN   ’ A ’   THEN  DBMS_OUTPUT.PUT_LINE ( ’ Excellent ’ );
   WHEN   ’ B ’   THEN  DBMS_OUTPUT.PUT_LINE ( ’ Very Good ’ );
   WHEN   ’ C ’   THEN  DBMS_OUTPUT.PUT_LINE ( ’ Good ’ );
   WHEN   ’ D ’   THEN  DBMS_OUTPUT.PUT_LINE ( ’ Fair ’ );
   WHEN   ’ F ’   THEN  DBMS_OUTPUT.PUT_LINE ( ’ Poor ’ );
   ELSE  DBMS_OUTPUT.PUT_LINE ( ’ No such grade ’ );
END   CASE ;

  CASE语句的功能：首先设定变量的值作为条件，然后顺序检查表达式，一旦从中找到与条件匹配的表达式值，就停止CASE语句的处理。

2.循环

  1)LOOP…EXIT…END循环控制语句
  LOOP循环语句是其中最基本的一种，格式如下：

LOOP
  Statements;
END  LOOP;

  这种循环语句是没有终止的，如果不人为控制的话，其中的Statements将会无限地执行。一般可以通过加入EXIT语句来终结该循环。

  2)WHILE…LOOP循环控制语句
  WHILE…LOOP循环控制语句的格式如下：

WHILE  Condition
LOOP
  Statements;
END  LOOP;

  WHILE…LOOP有一个条件与循环相联系，如果条件为TRUE，则执行循环体内的语句，如果结果为FALSE，则结束循环。

  3)FOR…LOOP循环控制语句
  FOR…LOOP循环控制语句的格式如下：

FOR  Counter  IN   [ REVERSE ]  Start_Range…End_Range
LOOP
  Statements;
END  LOOP;

  LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，Counter是一个隐式声明的变量，初始值是 Start_Range，第二个值是Start_Range + 1，直到End_Range,如果Start_Range等于End _Range，那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。

3.跳转
  GOTO语句的格式如下：
    GOTO LABEL;
  执行GOTO语句时，控制会立即转到由标签标记的语句（使用<<>>声明）。PL/SQL中对GOTO语句有一些限制，对于块、循环、IF语句而言，从外层跳转到内层是非法的。

DECLARE
  X          NUMBER ( 3 );
  Y          NUMBER ( 3 );
  V_COUNTER  NUMBER ( 2 );
BEGIN
  X : =   100 ;
   FOR  V_COUNTER  IN   1  ..  10  LOOP
     IF  V_COUNTER  =   4   THEN
       GOTO  end_of_loop;
     END   IF ;
    X : =  X  +   10 ;
   END  LOOP;
   << end_of_loop >>
  Y : =  X;
  dbms_output.put_line( ’ Y: ’ || Y);
END ;

  输出结果为“Y：130”。

4.嵌套
  程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序 块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转道子块中，反之则是合法的。

四、异常

1.简介
  异常处理块中包含了与异常相关的错误发生以及当错误发生时要进行执行和处理的代码。异常部分的语法一般如下：

BEGIN
  EXCEPTION
   WHEN  Excep_Name1  THEN
    Statements1;
   WHEN  Excep_Name2  THEN
    Statements2;
   WHEN  OTHERS  THEN
    Statements3;
END ;

2.预定义异常
简单列一下常用的吧：

异常名	异常标题	异常号
ACCESS_INTO_NULL	ORA-06530	-6530
CASE_NOT_FOUND	ORA-06592	-6592
COLLECTION_IS_NULL	ORA-06531	-6531
CURSOR_ALREADY_OPEN	ORA-06511	-6511
DUP_VAL_ON_INDEX	ORA-00001	-1
INVALID_CURSOR	ORA-01001	-1001
INVALID_NUMBER	ORA-01722	-1722
LOGIN_DENIED	ORA-01017	-1017
NO_DATA_FOUND	ORA-01403	-1403
NOT_LOGGED_ON	ORA-01012	-1012
PROGRAM_ERROR	ORA-06501	-6501
ROWTYPE_MISMATCH	ORA-06504	-6504
SELF_IS_NULL	ORA-30625	-30625
STORAGE_ERROR	ORA-06500	-6500
SUBSCRIPT_BEYOND_COUNT	ORA-06533	-6533
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	-6532
SYS_INVALID_ROWID	ORA-01410	-1410
TIMEOUT_ON_RESOURCE	ORA-00051	-51
TOO_MANY_ROWS	ORA-01422	-1422
VALUE_ERROR	ORA-06502	-6502
ZERO_DIVIDE	ORA-01476	-1476

以上异常说明：

异常名	说明
ACCESS_INTO_NULL	Your program attempts to assign values to   the attributes of an  uninitialized (atomically null) object.
CASE_NOT_FOUND	one of the choices in the WHEN clauses of a  ASE  tatement is selected, and there is no  ELSE clause.
COLLECTION_IS_NULL	Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	Your program attempts to open an alrea*** open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	Your program attempts to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR	Your program attempts an illegal cursor operation such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character st***  into a number fails because the st***  does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED	Your program attempts to log on to Oracle with an invalid username and/or password.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
NOT_LOGGED_ON	Your program issues a database call without being connected to Oracle.
PROGRAM_ERROR	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL	Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null.
STORAGE_ERROR	PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT	Your program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID	The conversion of a character st***  into a universal rowid fails because the character st***  does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	A time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character st***  into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE	Your program attempts to divide a number by zero.

3.自定义异常
  异常不一定必须是Oracle返回的系统错误，用户可以在自己的应用程序中创建可触发及可处理的自定义异常，调用异常处理需要使用RAISE语句。
  异常情态的传播指的是当在程序块的声明、执行、异常部分分别出现异常情态时，或在本块中没有相应的异常处理器时会将这个异常情态传播到哪里，会去激发那个 块中的处理器。传播规则是这样的：当一个异常情态是在块的执行部分引发的(最常见的)，PL/SQL使用下面的规则确定激活哪个异常处理器。
  ① 若当前块对该异常情态设置了处理器，则执行它并成功完成该块的执行，然后控制转给包含块。
  ② 若当前块没有该处理器，则通过在包含块中引发它来传播异常情态。然后对包含块执行PL/SQL的异常操作。另外，无论是在声明部分引发了一个异常情态，还 是在异常处理部分引发，则该异常情态将立即传播给包含块。在包含块引用上述规则进行异常情态的处理，即使在当前块设置了OTHERS处理器也不会被执行。
五、游标
  Oracle游标是一种用于轻松的处理多行数据的机制。如果没有游标，Oracle开发人员必须单独地、显式地取回并管理游标查询选择的每一条记录。游标的另一项功能是，它包含一个跟踪当前访问的记录的指针，这使程序能够一次处理多条记录。

1.声明游标
  声明游标的语句格式如下：

DECLARE  Cursor_Name  IS   SELECT  Statement

 
  声明游标完成了下面两个目的：
    ① 给游标命名.
    ② 将一个查询与游标关联起来。

2.打开游标
  打开游标的语句格式如下：

OPEN  Cursor_Name;

  打开游标将激活查询并识别活动集，可是在执行游标取回命令之前，并没有真正取回记录。OPEN命令还初始化了游标指针，使其指向活动集的第一条记录。游标 被打开后，直到关闭之前，取回到活动集的所有数据都是静态的。换句话说，游标忽略所有在游标打开之后，对数据执行的SQL DML命令(INSERT、UPDATE、DELETE和SELECT)，因此只有在需要时才打开它，要刷新活动集，只需关闭并重新打开游标即可。

3.从游标中取数据
  FETCH命令以每次一条记录的方式取回活动集中的记录。通常将FETCH命令和某种迭代处理结合起来使用，在迭代处理中，FETCH命令每执行一次，游标前进到活动集的下一条记录。
  FETCH命令的语句格式如下：

FETCH  Cursor_Name  INTO  Record_List;

  执行FETCH命令后，活动集中的结果被取回到PL/SQL变量中，以便在PL/SQL块中使用。每取回一条记录，游标的指针就移向活动集的下一条记录。

4.关闭游标
  CLOSE语句关闭以前打开的游标。
  CLOSE语句的格式：

CLOSE  Cursor_Name;

5.隐式游标
  隐式游标也可以叫做SQL游标。和显式的游标不同，不能对一个SQL游标显式的执行OPEN、CLOSE和FETCH语句。Oracle隐式的打开SQL 游标、处理SQL游标、然后再关闭该游标。Oracle提供隐式游标的主要目的就是利用这些游标的属性来确定SQL语句运行的情况。

6.实例
  一个游标应用的完整程序代码：

DECLARE
   CURSOR  C1  IS
     SELECT  VIEW_NAME  FROM  ALL_VIEWS  WHERE  ROWNUM  <=   10   ORDER   BY  VIEW_NAME;
  VNAME  VARCHAR2 ( 40 );
BEGIN
   OPEN  C1;
   FETCH  C1  INTO  VNAME;
   WHILE  C1 % FOUND LOOP
     FETCH  C1  INTO  VNAME;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1 % ROWCOUNT )  ||   ”   ||  VNAME);
   END  LOOP;
   CLOSE  C1;
END ;

7.游标的属性

  1)%ISOPEN属性
  该属性功能是测试游标是否打开，如果没有打开游标就使用fetch语句将提示错误。

DECLARE
  TempSal EMPLOYEES.SAL % TYPE;
   CURSOR  MyCursor  IS
     SELECT   *   FROM  EMPLOYEES  WHERE  SAL  >  TempSal;
  CursorRecord MyCursor % ROWTYPE;
BEGIN
  TempSal : =   800 ;
   IF  MyCursor % ISOPEN  THEN
     FETCH  MyCursor  INTO  CursorRecord;
      dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
   ELSE
    dbms_output.put_line( ’ 游标未打开! ’ );
   END   IF ;
END ;

  输出结果为“游标未打开!”。

  2)%FOUND属性
  该属性功能是测试前一个fetch语句是否有值，有值将返回true，否则为false。

DECLARE
  TempSal EMPLOYEES.SAL % TYPE;
   CURSOR  MyCursor  IS
     SELECT   *   FROM  EMPLOYEES  WHERE  SAL  >  TempSal;
  CursorRecord MyCursor % ROWTYPE;
BEGIN
  TempSal : =   800 ;
   OPEN  MyCursor;
   FETCH  MyCursor  INTO  CursorRecord;
   IF  MyCursor % FOUND  THEN
    dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
   ELSE
    dbms_output.put_line( ’ 未发现数据! ’ );
   END   IF ;
END ;

  输出结果为“tom”。

  3)%NOTFOUND属性
  该属性是%found属性的反逻辑，常被用于退出循环。

DECLARE
  TempSal EMPLOYEES.SAL % TYPE;
   CURSOR  MyCursor  IS
     SELECT   *   FROM  EMPLOYEES  WHERE  SAL  >  TempSal;
  CursorRecord MyCursor % ROWTYPE;
BEGIN
  TempSal : =   800 ;
   OPEN  MyCursor;
   FETCH  MyCursor  INTO  CursorRecord;
   IF  MyCursor % NOTFOUND  THEN
    dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
   ELSE
    dbms_output.put_line( ’ 发现数据！ ’ );
   END   IF ;
END ;

  输出结果为“发现数据！”

  4)%ROWCOUNT属性
  该属性用于返回游标的数据行数。

DECLARE
  TempSal EMPLOYEES.SAL % TYPE;
   CURSOR  MyCursor  IS
     SELECT   *   FROM  EMPLOYEES  WHERE  SAL  >  TempSal;
  CursorRecord MyCursor % ROWTYPE;
BEGIN
  TempSal : =   800 ;
   OPEN  MyCursor;
   FETCH  MyCursor  INTO  CursorRecord;
    dbms_output.put_line(to_char(MyCursor % ROWCOUNT ));
END ;

输出结果为“1” 。

六、存储过程

1.命令格式
  存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同， 存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，定义存储过程的语法如下：

PROCEDURE  Name  [ (Parameter[,Parameter, ] )]
IS | AS
   [ Local Declarations ]
BEGIN
   Execute  statements;
   [ EXCEPTION Exception Handlers ]
END   [ Name ] ;

2.调用
  存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。用EXECUT命令调用存储过程的格式如下：

SQL > EXCUTE  Proc_Name(par1, par2…);

  存储过程也可以被另外的PL/SQL块调用，调用的语句是：

DECLARE  par1, par2;
BEGIN
  Proc_Name(par1, par2…);
END ;

3.释放
  当某个存储过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。释放过程的语句格式如下：

SQL > DROP   PROCEDURE  Proc_Name;

4.实例：
  编写存储过程，显示所指定雇员名所在的部门名和位置。

CREATE   OR   REPLACE   PROCEDURE  DeptMesg(pename emp.ename % TYPE,
                                     pdname OUT dept.dname % TYPE,
                                     ploc   OUT dept.loc % TYPE)  AS
BEGIN
   SELECT  dname, loc
     INTO  pdname, ploc
     FROM  emp, dept
    WHERE  emp.deptno  =  dept.deptno
      AND  emp.ename  =  pename;
END ;

  调用：

VARIABLE vdname  VARCHAR2 ( 14 );
VARIABLE vloc  VARCHAR2 ( 13 );
EXECUTE  DeptMesg( ’ SMITH ’ , :vdname£¬ :vloc);
PRINT  vdname vloc; 

七、函数

1.命令格式
  函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数，有一个返回值，返回值的数据类型在创建函数时定义。定义函数的语法如下：

FUNCTION  Name  [ {Parameter[,Parameter, ] )]
RETURN  DataTypes
IS
[ Local Declarations ]
BEGIN
   Execute  Statements;
   [ EXCEPTION Exception Handlers ]
END   [ Name ] ;

2.调用
  无论在命令行还是在程序语句中，函数都可以通过函数名称直接在表达式中调用。例如：将函数Count_Num(‘女’)的返回值赋予变量Man_Num。

SQL > EXECUTE  Man_Num : =  Count_Num( ’ 女 ’ );

3.释放
  当函数不再使用时，要用DROP命令将其从内存中删除，例如：

SQL > DROP   FUNCTION  Count_Num;

4.实例
  编写一个函数以显示该雇员在此组织中的工作天数。

CREATE   OR   REPLACE   FUNCTION  Hire_Day(no emp.empno % TYPE)  RETURN   NUMBER   AS
  vhiredate emp.hiredate % TYPE;
  vday       NUMBER ;
BEGIN
   SELECT  hiredate  INTO  vhiredate  FROM  emp  WHERE  empno  =  no;
  vday : =  CEIL(SYSDATE  -  vhiredate);
   RETURN  vday;
END ;

八、触发器

1.触发器的创建规则：
  ①作用范围清晰；
  ②不要让触发器去完成Oracle后台已经能够完成的功能；
  ③限制触发器代码的行数；
  ④不要创建递归的触发器；
  ⑤触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。

2.可以创建被如下语句所触发的触发器：
  ①DML语句（DELETE，INSERT，UPDATE）；
  ②DDL语句（CREATE，ALTER， DROP）；
  ③数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。

3.注意事项
  ①触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发；
  ②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次；
  ③SELECT 并不更改任何行，因此不能创建 SELECT 触发器.这种场合下规则和视图更适合；
  ④触发器和某一指定的表格有关，当该表格备删除时，任何与该表有关的触发器同样会被删除；
  ⑤在一个表上的每一个动作只能有一个触发器与之关联；
  ⑥在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器；

4.删除触发器的语句格式为：

DROP   TRIGGER  name  ON   table ;

  一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句，可为对一指定表的INSERT、 UNPDATE或DELETE语句。触发限制是指定一个布尔表达式，当触发器激发时该布尔表达式是必须为真。触发器作为过程，是PL/SQL块，当触发语 句发出、触发限制计算为真时该过程被执行。

5.实例
  编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。

CREATE   OR   REPLACE   TRIGGER  del_emp_deptno
  BEFORE  DELETE   ON  dept
   FOR  EACH ROW
BEGIN
   DELETE   FROM  emp  WHERE  deptno  =  :OLD.deptno;
END ;

九、包

1.包头
  创建包头的语句格式如下：
  CREATE PACKAGE＜包名> IS
    变量、常量及数据类型定义；
    游标定义；
    函数、过程定义和参数列表及返回类型；
  END＜包名＞；

2.包体
  创建包主体部分的语句格式如下：
  CREATE PACKAGE BODY＜包名＞
  AS
    游标、函数、过程的具体定义；
  END＜包名＞；

3.实例

  包头代码：

– 创建包头
CREATE  PACKAGE test_package  IS
   – 定义变量
  man_num    NUMBER ;
  woman_num  NUMBER ;
   – 定义游标
  CURSOR学生;

   – 定义函数
   CREATE   FUNCTION  f_count( in  sex  IN  学生.sex % TYPE)
   – 定义返回值类型
   RETURN   NUMBER ;

   – 定义过程
   CREATE   PROCEDURE  p_count(in_sex  IN  学生.sex % TYPE, out_num OUT  NUMBER );

– 包头结束
END  test_package;

  包体代码：

– 创建包体
CREATE  PACKAGE BODY test_package  AS
   – 游标具体定义
   CURSOR  学生IS
     SELECT  学号，姓名  FROM  学生  WHERE  学号  <   50 ;

   – 函数具体定义
   FUNCTION  f_count(in_sex IN学生.sex % TYPE)
   – 定义返回值类型
    RETURN   NUMBER   IS
    out_num  NUMBER ;
     – 函数体
   BEGIN
     IF  in_sex  =   ’ 男 ’   THEN
       SELECT   count (sex)  INTO  out_num  FROM  学生 WHERE性别＝ ’ 男 ’ ;
     ELSE
       SELECT   count (sex)  INTO  out_num  FROM  学生  WHERE  性别＝ ’ 女 ’ ;
     END   IF ;
     – 返回函数值
     RETURN (out_num);
     – 函数定义结束
   END  f_count;

   – 过程具体定义
   PROCEDURE  p_count(in_sex IN学生.sex % TYPE， out_num OUT  NUMBER )  AS
     – 过程体
   BEGIN
     IF  in_sex  =   ’ 男 ’   THEN
       SELECT   count (sex)  INTO  out_num  FROM  学生 WHERE性别  =   ’ 男 ’ ;
     ELSE
       SELECT   count (sex)  INTO  out_num  FROM  学生  WHERE  性别＝  ’ 女 ’ ;
     END   IF ;
     – 过程定义结束
   END  P_count;

– 包体定义结束
END  test_package;