Oracle PL/SQL 学习笔记（三）

 

3.PL/SQL基础

3.1PL/SQL块简介

块(BLOCK)是PL/SQL的基本程序单元，编写PL/SQL程序就是编写PL/SQL块。

3.1.1PL/SQL块结构

PL/SQL由3个部分组成：定义部分、执行部分、例外处理部分。

定义部分：定义常量、变量、游标、例外、复杂数据类型等。

执行部分：实现应用模块功能，该部分包含要执行的PL/SQL语句和SQL语句。

例外处理部分：处理执行部分可能出现的运行错误。

基本结构：

DECLARE

/* 定义部分（可选） */

BEGIN

/* 执行部分（必须） */

EXCEPTION

/* 例外处理部分（可选） */

END;         /*块结束标记*/

注意：DECLARE,BEGIN,EXCEPTION后面没有分号，END后必须有分号。

 

示例：

DECLARE

  name VARCHAR2(100);

BEGIN

  SELECT offer_name INTO name FROM bbnn

  WHERE offer_id = &id;

    dbms_output.put_line('套餐名：'||name);

EXCEPTION

  WHEN NO_DATE_FOUND THEN

  dbms_output.put_line('请输入正确的套餐号！');

END;

输入不正确套餐号，会提示输入正确套餐号。其中NO_DATE_FOUND是PL/SQL的预定义例外。

 

3.1.2 PL/SQL块分类

(1)匿名块：没有名称的PL/SQL块，既可以内嵌到应用程序中，也可以在交互式环境中使用。

(2)命名块：具有特定标识的PL/SQL块，使用<<>>标记。

<<outer>>

DECLARE

         v_deptno NUMBER(2);

         v_dname VARCHAR2(10);

BEGIN

         <<inter>>

         BEGIN

         SELECT deptno INTO  v_deptno FROM emp

         Where lower(ename)=lover(‘&name’);

END;--- <<inner>>

         SELECT dname INTO  v_dname FROM dept

         WHERE deptno = v_deptno;

         Dbms_output.put_line(‘部门名：’||v_dname);

END;--- <<outer>>

(3)子程序：包括过程、函数和包。

过程

用于执行特定操作。当建立过程时，既可以指定输入参数(IN)，也可以指定输出参数(OUT)。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。

CREATE  PROCEDURE  update_sal(name VARCHAR2, newsal  NUMBER)

IS

BEGIN

         UPDATE  emp  SET  sal = newsal

         WHERE  lower(ename) = lower(name);

END;

过程update_sal用于更新雇员工资。

示例一：exec update_sal(‘scott’,2000)

示例二：call update_sal(‘scott’,2000)

 

函数

         用于返回特定数据。当建立函数时，在函数头必须包含RETURN子句，而在函数体内必须要包含RETURN语句返回数据。

CREATE  FUNCTION  annual_income(name VARCHAR2)

RETURN  NUMBER  IS

         Annual_salary  NUMBER(7,2);

BEGIN

         SELECT sal*12+nvl(comm,0) INTO annual_salary

         FROM  emp  WHERE  lower(ename)=lower(name);

         RETURN  annual_salary;

END;

函数annual_income返回雇员的全年收入（包括工资和奖金）。

SQL>VAR income NUMBER

SQL>CALL annual_income(‘scott’) INTO : income

SQL>PRINT income

 

包

         包用于逻辑组合相关的过程和函数，由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。

CREATE  PACKAGE  emp_pkg  IS

         PROCEDURE  update_sal(name  VARCHAR2,  newsal  NUMBER);

         FUNCTION  annual_income(name  VARCHAR2)  RETURN  NUMBER;

END;

包规范只包含了过程和函数的说明，没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。建立包体使用CREATE  PACKAGE  BODY命令。

CREATE  PACKAGE  BODY  emp_pkg  IS

         PROCEDURE  update_sal(name  VARCHAR2, newsal  NUMBER)

         IS

         BEGIN

                   UPDATE  emp  SET  sal=newsal

                   WHERE  lower(ename)=lower(name);

         END;

         FUNCTION  annual_income(name VARCHAR2)  RETURN  NUMBER

         IS

                   Aunnual_salary  NUMBER(7,2);

         BEGIN

                   SELECT  sal*12+nvl(comm,0)  INTO  annual_salary

                   FORM  emp  WHERE  lower(ename)=lower(name);

                   RETURN  annual_salary;

         END;

END;

 

当调用包的过程和函数时，在过程和函数名之前必须要带有包名作为前缀（包名.子程序名），如果访问其他方案的包，还必须加方案名作为前缀（方案名.包名.子程序名）

示例一：SQL> call  emp_pkg.update_sal(‘scott’,1500);

示例二：SQL> VAR  income  NUMBER

                   SQL> CALL  emp_pkg.annual_income(‘scott’) INFO:income;

                   SQL> PRINT  income

 

 

(4)触发器：

         触发器是指隐含执行的存储过程。当定义触发器时，必须要制定出发事件以及处罚操作，常用的触发事件包括INSERT,UPDATE和DELETE语句。触发操作实际是一个PL/SQL块。

CREATE  TRIGGER  update_cascade

         AFTER  UPDATE  OF  deptno  ON dept

         FOR  EACH  ROW

BEGIN

         UPDATE  emp  SET  deptno=:new.deptno

         WHERE  deptno=:old.deptno;

END;

触发器update_cascade用于实现级联更新，如果不建立该触发器，那么当更新dept表的deptno列数据时就会出错，”ORA-02292：违反完整约束条件(SCOTT.FK_DEPTNO)”。而建立触发器后，当更新deptno列时，就会级联更新emp表的deptno列的相关数据。