PL/SQL基础

PL/SQL基础

 

一．PL/SQL块简介

块(Block)是PL/SQL的基本程序单元，编写PL/SQL程序实际就是编写PL/SQL块。要完成相对简单的应用功能，可能只需要编写一个PL/SQL块；而如果要实现复杂的应用功能，那么可能需要在一个PL/SQL块中嵌套其他PL/SQL块。编写PL/SQL应用模块，块的嵌套层次没有限制。

 

1.1   PL/SQL块结构

PL/SQL块由三个部分组成：

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

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

l         例外处理：用于处理执行部分可能出现的运行错误；

基本结构如下：

DECLARE

/*

* 定义部分——定义常量、变量、复杂数据类型、游标、例解(可选)

*/

BEGIN

/*

* 执行部分——PL/SQL语句和SQL语句(必选)

*/

EXCEPTION

/*

* 例外处理部分——处理运行错误(可选)

*/

END;             /*块结束标记*/

注意，DECLARE，BEGIN，EXCEPTION后面没有分号(;)，而END后则必须要带分号(;)。

 

示例一：只包含执行部分的PL/SQL块

set serveroutput on

BEGIN

  dbms_output.put_line('Hello,everyone!');

END;

 

输出结果：

Hello,everyone!
PL/SQL 过程已成功完成

 

其中dbms_output是Oracle所提供的系统包，put_line是该包所包含的过程，用于输出字符串信息。

注意：当使用dbms_output包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput设置为on。

 

示例二：包含定义部分和执行部分的PL/SQL块

DECLARE

  v_ename VARCHAR2(5);

BEGIN

  SELECT ename INTO v_ename FROM emp

  WHERE empno = &no;

  dbms_output.put_line('雇员名：'||v_ename);

END;

 

输出结果：

原值 5: WHERE empno = &no;
新值 5: WHERE empno = 7788;
雇员名：SCOTT
PL/SQL 过程已成功完成。

 

其中&no为SQL*Plus的替代变量。

 

示例三：包含定义部分、执行部分和例外处理部分的PL/SQL块

DECLARE

  v_ename VARCHAR2(5);

BEGIN

  SELECT ename INTO v_ename FROM emp

  WHERE empno = &no;

  dbms_output.put_line('雇员名：'||v_ename);

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    dbms_output.put_line('请输入正确的雇员名!');

END;

 

输出结果：

原值 5: WHERE empno = &no;
新值 5: WHERE empno = 12;
请输入正确的雇员名!
PL/SQL 过程已成功完成。

 

如输入了不正确的雇员名，会提示你输入正确的雇员编号，从而避免了程序运行错误。

 

1.2   PL/SQL块分类

根据需要实现的应用模块功能，可以将PL/SQL块划分为以下四种类型：

l         匿名块：是指没有名称的PL/SQL块，匿名块既可以内嵌到应用程序(例如Pro*C/C++)中，也可以在交互式环境(例如SQL*Plus)中直接使用。

l         命名块：是指在PL/SQL块前使用<<>>加以标记的特定名称标识的PL/SQL块。当使用嵌套块时，为了区分多级嵌套层次关系，可以使用命名块加以区分。

l         子程序：开发PL/SQL子程序时，即可以开发客户端的子程序，也可以开发服务器端的子程序。客户端子程序主要用在Developer中，而服务器端子程序可以用在任何应用程序中。通过将商业逻辑和企业规则集成到PL/SQL子程序中，可以简化客户端程序的开发和维护，并且提高应用程序的性能。

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

函数：用于返回特定数据。当建立函数时，在函数头部必须包含RETURN字句，而在函数体内必须要包含RETURN语句返回数据。在SQL*Plus中可以使用CREATE FUNCTION命令建立函数。

包：用于逻辑组合相关的过程和函数，它由包规则和包体两部分组成。包规则用于定义公用的常量、变量、过程和函数，在SQL*Plus中建立包规范可以使用CREATE PACKAGE命令。包规范只包含了过程和函数的说明，而没有过程和函数的实现代码。包体用于实现包规范中的过程和函数，在SQL*Plus中建立包体可以使用CREATE PACKAGE BODY命令。

l         触发器：是指隐含执行的存储过程。当定义触发器时，必须要指定触发事件以及触发操作，常用的触发事件包括INSERT，UPDATE和DELETE语句，而触发操作实际是一个PL/SQL块。在SQL*Plus中建立触发器是使用CREATE TRIGGER命令来完成的。

匿名块示例：

DECLARE

  v_avgsal NUMBER(6,2);

BEGIN

  SELECT avg(sal) INTO v_avgsal FROM emp

  WHERE deptno = &no;

  dbms_output.put_line('平均工资：'||v_avgsal);

END;

 

输出结果：

原值 5: WHERE deptno = &no;
新值 5: WHERE deptno = 10;
平均工资：2916.67
PL/SQL 过程已成功完成。

 

该PL/SQL块直接以DECLARE开始，没有给出任何名称，所以该PL/SQL块属于匿名块。

 

命名块示例：

<<outer>>

DECLARE

  v_deptno NUMBER(2);

  v_dname VARCHAR2(10);

BEGIN

  <<inner>>

  BEGIN

    SELECT deptno INTO v_deptno FROM emp

    WHERE lower(ename) = lower('&name');

  END;--<<inner>>

  SELECT dname INTO v_dname FROM dept

  WHERE deptno = v_deptno;

  dbms_output.put_line('部门名：'||v_dname);

END;--<<outer>>

 

输出结果：

原值 9: WHERE lower(ename) = lower('&name');
新值 9: WHERE lower(ename) = lower('scott');
部门名：RESEARCH
PL/SQL 过程已成功完成。

 

<<outer>>和<<inner>>分别是主块(外层块)和子块(内层块)的标记，这种PL/SQL块被称为命名块。

 

过程示例：

CREATE PROCEDURE update_sal(name VARCHAR2,newsal NUMBER)

IS

BEGIN

  UPDATE emp SET sal = newsal

  WHERE lower(ename) = lower(name);

END;

 

输出结果：

过程已创建。

 

调用过程可以使用execute命令或call命令，例如：

exec update_sal('scott',2000);

call update_sal('scott',3000);

 

输出结果：

调用完成。

 

函数示例：

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;

 

输出结果：

函数已创建。

 

调用过程可以使用call命令，例如：

VAR income NUMBER

CALL annual_income('scott') INTO:income;

PRINT income;

 

输出：

调用完成。

INCOME

36000

 

包示例：

建立包规范

CREATE PACKAGE emp_pkg IS

  PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);

  FUNCTION annual_income(name VARCHAR2) RETURN NUMBER;

END;

 

输出结果：

程序包已创建。

 

调用：

CALL emp_pkg.update_sal('scott',1500);

 

输出结果：

CALL emp_pkg.update_sal1('scott',1500)

     *

第 1 行出现错误:

ORA-04067: 未执行, package body "SCOTT.EMP_PKG" 不存在

ORA-06508: PL/SQL: 无法在调用之前找到程序单元 ORA-06512: 在 line 1

 

建立包体：

CREATE PACKAGE BODY emp_pkg IS

  PROCEDURE update_sal1(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

    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;

END;

 

输出：

程序包体已创建。

 

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

CALL emp_pkg.update_sal('scott',1500);

 

输出：

调用完成。

 

VAR income NUMBER

CALL emp_pkg.annual_income('soctt') INTO:income;

PRINT income;

 

输出：

调用完成。

INCOME

18000

 

触发器示例：

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列的相关数据。

 

二．定义并使用变量

编写PL/SQL程序时，若临时存储数值，必须要定义变量和常量；若在应用环境和子程序之间传递数据，必须要为子程序指定参数。而在PL/SQL程序中定义变量、常量和参数时，则必须要为它们指定PL/SQL数据类型。在编写PL/SQL程序时，可以使用以下四种变量类型：

l         标量(Scalar)类型：标量变量是指只能存放单个数值的变量。当定义标量变量时，必须要指定标量数据类型。标量数据类型包括数字类型、字符类型、日期类型和布尔类型，每种类型又包含有相应的子类型。

l         复合(Composite)类型

l         参照(Reference)类型

l         LOB(Large Object)类型

 

2.1标量变量

1.常用标量类型

(1)VARCHAR2(n)

该数据类型用于定义可变长度的字符串，其中n用于指定字符串的最大长度，其最大值为32767字节。当使用该数据类型定义变量时，必须要指定长度。

注意：在PL/SQL块中使用该数据类型操纵VARCHAR2表列时，其数值的长度不应超过4000字节。

(2)CHAR(n)

该数据类型用于定义固定长度的字符串，其中n用于指定字符串的最大长度，其最大值为32767字节。当使用该数据类型定义变量时，如果没有指定n，则其默认值为1。

注意：在PL/SQL块中使用数据类型操纵CHAR表列时，其数值的长度不应该超过2000字节。

(3)NUMBER(p,s)

该数据类型用于定义固定长度的整数和浮点数，其中p表示精度，用于指定数字的总位数；s表示标度，用于指定小数点后的数字位数。

(4)DATE

该数据类型用于定义日期和事件数据，其数据长度为固定长度(7字节)。

注意：当给DATE变量赋值时，数据必须要与日期格式和日期语言匹配。

(5)TIMESTAMP

该数据类型是Oracle9i新增加的数据类型，它也用于定义日期和事件数据。用法和DATA变量一样，但当显示TIMESTAMP变量数据时，不仅会显示日期，而且还会显示时间和上下午标记。

(6)LONG和LONGRAW

LONG数据类型用于定义变长字符串，类似于VARCHAR2数据类型，但其字符串的最大长度为32760字节；LONGRAW数据类型用于定义变长的二进制数据，其数据最大长度为32760字节。

(7)BOOLEAN

该数据类型用于定义布尔变量，其变量的值为TRUE、FALSE或NULL。

注意：该数据类型是PL/SQL数据类型，表列不能采用该数据类型。

(8)BINARY_INTEGER

该数据类型用于定义整数，其数值范围在-2147483647和2147483647之间。在Oracle9i之前，当在PL/SQL块中定义PL/SQL表时，必须使用该数据类型作为下标的数据类型。

注意：该数据类型是PL/SQL数据类型，表列不能采用该数据类型。

(9)BINARY_FLOAT和BINARY_DOUBLE

BINARY_FLOAT和BINARY_DOUBLE是Oracle 10g 新增加的数据类型，分别用于定义单精度的浮点数和双精度的浮点数。这两种数据类型主要用于高速的科学计算，当为BINARY_FLOAT类型的变量赋值时，应该带有后缀f(例如 1.5f )；当为BINARY_DOUBLE类型的变量赋值时，应该带有后缀d(例如3.00095d)。

 

2.定义标量变量

(1)语法

identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]

l         identifier：用于指定变量或常量的名称

l         CONSTANT：用于指定常量。当定义常量时，必须指定它的初始值，并且其数值不能改变

l         datatype：用于指定变量或常量的数据类型

l         NOT NULL：用于强制初始化变量(不能为NULL)。当指定NOT NULL选项时，必须要为变量提供数值

l         :=：用于为变量和常量指定初始值

l         DEFAULT：用于为变量和常量指定初始值

l         expr：用于指定初始值的PL/SQL表达式，可以是文本值、其他变量、函数等

 

(2)使用标量变量示例：

DECLARE

  v_ename VARCHAR2(5);

  v_sal NUMBER(6,2);

  c_tax_rate CONSTANT NUMBER(3,2) := 0.03;

  v_tax_sal NUMBER(6,2);

BEGIN

  SELECT ename,sal INTO v_ename,v_sal

  FROM emp WHERE empno = &eno;

  v_tax_sal := v_sal*c_tax_rate;

  dbms_output.put_line('雇员名：'||v_ename);

  dbms_output.put_line('雇员工资：'||v_sal);

  dbms_output.put_line('所得税：'||v_tax_sal);

END;

 

输出：

原值 8: FROM emp WHERE empno = &eno;
新值 8: FROM emp WHERE empno = 7788;
雇员名：SCOTT
雇员工资：1500
所得税：45
PL/SQL 过程已成功完成。

 

当在定义部分定义了标量变量之后，在执行部分和例外处理部分可以引用这些标量变量。

注意：在PL/SQL块中为变量赋值不同于其他编程语言，必须要在等号前加冒号(:=)。

 

在执行该PL/SQL块时，如果雇员名长度超过5字节，则会显示错误信息“ORA-06502:PL/SQL:数字或值错误：字符串缓冲区太小”，原因是v_ename变量的最大长度为5字节。因为ename列的最大长度为10字节，所以只需要将v_ename变量的最大长度设置为10字节就可以了。在应用模块设计完成之后，假定将来需要输入名称超过10字节的雇员，那么就需要修改EMP表的ENAME列数据长度，很显然还需要修改应用模块。为了提高程序的可用性，降低PL/SQL程序的维护工作量，可以使用%TYPE属性定义变量。

 

(3)使用%TYPE属性

使用%TYPE属性定义变量时，它会按照数据库列或其他变量来确定新变量的类型和长度。

示例：

DECLARE

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

  c_tax_rate CONSTANT NUMBER(3,2) := 0.03;

  v_tax_sal v_sal%TYPE;

BEGIN

  SELECT ename,sal INTO v_ename,v_sal

  FROM emp WHERE empno = &eno;

  v_tax_sal := v_sal*c_tax_rate;

  dbms_output.put_line('雇员名：'||v_ename);

  dbms_output.put_line('雇员工资：'||v_sal);

  dbms_output.put_line('所得税：'||v_tax_sal);

END;

 

输出：

原值 8: FROM emp WHERE empno = &eno;
新值 8: FROM emp WHERE empno = 7788;
雇员名：SCOTT
雇员工资：1500
所得税：45
PL/SQL 过程已成功完成。

 

变量v_ename，v_sal与EMP表的ename列、sal列的数据类型和长度完全一致，而变量v_tax_sal与变量v_sal的数据类型和长度完全一致。这样，当ename列和sal列的类型和长度发生改变时，该PL/SQL块将不需要进行任何修改。

 

2.2复合变量

复合变量是指用于存放多个值的变量。当定义复合变量时，必须要使用PL/SQL的复合数据类型。PL/SQL包括以下四种复合数据类型：

l         PL/SQL记录：类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。引用记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)。

l         PL/SQL表：类似于高级语言中的数组。需要注意，PL/SQL表与高级语言的数组有所区别，高级语言数组的下标不能为负，但PL/SQL表的下标可以为负值；高级语言数组的元素个数有限制，而PL/SQL表的元素个数没有限制，并且其下标没有上下限。当使用PL/SQL表时，必须首先在定义部分定义PL/SQL表类型和PL/SQL表变量，然后在执行部分中引用该PL/SQL表变量。

l         嵌套表：类似于高级语言中的数组。需要注意，高级语言数组和嵌套表的下标都不能为负值；高级语言的元素个数有限制，而嵌套表的元素个数是没有限制的。嵌套表和PL/SQL表非常类似，但嵌套表可以作为表列的数据类型，而PL/SQL表不能作为表列的数据类型。当在表列中使用嵌套表时，必须首先使用CREATE TYPE语句建立嵌套表类型。

l         VARRAY：（变长数组）类似于嵌套表，它可以作为表列和对象类型属性的数据类型。但需要注意，嵌套表的元素个数没有限制，而VARRAY的元素个数是由限制的。当使用VARRAY时，必须首先建立VARRAY类型。

 

PL/SQL记录示例：

DECLARE

  TYPE emp_record_type IS RECORD(

    name emp.ename%TYPE,

    salary emp.sal%TYPE,

    title emp.job%TYPE);

  emp_record emp_record_type;

BEGIN

  SELECT ename,sal,job INTO emp_record

  FROM emp WHERE empno = 7788;

  dbms_output.put_line('雇员名：'||emp_record.name);

  dbms_output.put_line('雇员工资：'||emp_record.salary);

END;

 

输出：

雇员名：SCOTT
雇员工资：1500
PL/SQL 过程已成功完成。

 

emp_record_type是PL/SQL记录类型，并且该PL/SQL记录类型包含了三个成员(name,salary,title)；emp_record是记录变量；emp_record.name则表示引用记录变量emp_record的成员name。

 

PL/SQL表示例：

DECLARE

  TYPE ename_talbe_type IS TABLE OF emp.ename%TYPE

    INDEX BY BINARY_INTEGER;

  ename_table ename_talbe_type;

BEGIN

  SELECT ename INTO ename_table(-1) FROM emp

  WHERE empno = 7788;

  dbms_output.put_line('雇员名：'||ename_table(-1));

END;

 

输出：

雇员名：SCOTT
PL/SQL 过程已成功完成。

 

ename_table_type为PL/SQL表类型；emp.ename%TYPE指定了PL/SQL表元素的数据类型和长度；ename_table为PL/SQL表变量，ename_table(-1)则表示下标为-1的元素。

 

嵌套表示例：

CREATE OR REPLACE TYPE emp_type AS OBJECT(

  name VARCHAR2(10),

  salary NUMBER(6,2),

  hiredate DATE);

 

输出：

类型已创建。

 

CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;

输出：

类型已创建。

 

CREATE TABLE department(

  deptno NUMBER(2),

  dname VARCHAR2(10),

  employee emp_array

)NESTED TABLE employee STORE AS employee;

 

输出：

表已创建。

 

对象类型emp_type用于存储雇员信息，而emp_array是基于emp_type的嵌套表类型，它可以用于存储多个雇员的信息。当建立了嵌套表类型之后，就可以在表列或对象属性中将其作为用户自定义数据类型来引用。但需要注意，当使用嵌套表类型作为表列时，必须要为其指定专门的存储表。

 

VARRAY示例：

CREATE TYPE arricle_type AS OBJECT(

  title VARCHAR2(30),

  PUBDATE DATE);

 

输出：

类型已创建。

 

CREATE TYPE article_array IS VARRAY(20) OF article_type;

 

输出：

类型已创建。

 

CREATE TABLE author(

  id NUMBER(6),

  name VARCHAR2(10),

  article article_array);

 

输出：

表已创建。

 

对象类型article_type用于存储文章信息，而article_array则用于存储多篇文章的信息，并且最多可以存储20篇文章。当建立了VARRAY类型之后，可以在表列或对象属性中将其作为用户自定义数据类型来引用。

注意，嵌套表列数据需要存储在专门的存储表中，而VARRAY数据则与其他列数据一起存放在表段中。

 

2.3参照变量

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用空间。在编写PL/SQl程序时，可以使用以下两种参照变量类型：

l         游标变量(REF CURSOR)：当使用显式游标时，需要在定义显式游标时指定相应的SELECT 语句，这种显式游标称为静态游标。当使用游标变量时，在定义游标变量时不需要指定SELECT语句，而是在打开游标时指定SELECT语句，从而实现动态的游标操作。

l         对象类型变量(REF obj_type)：当编写对象类型应用时，为了共享相同对象，可以使用REF引用对象类型，REF实际是指向对象实例的指针。

 

游标变量(REF CURSOR)示例：

DECLARE

  TYPE c1 IS REF CURSOR;

  emp_cursor c1;

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

BEGIN

  OPEN emp_cursor FOR

    SELECT ename,sal FROM emp WHERE deptno = 10;

  LOOP

    FETCH emp_cursor INTO v_ename,v_sal;

    EXIT WHEN emp_cursor%NOTFOUND;

    dbms_output.put_line(v_ename);

  END LOOP ;

  CLOSE emp_cursor;

END;

 

输出：

CLARK
KING
MILLER
PL/SQL 过程已成功完成。

 

C1为REF CURSOR类型，而emp_cursor为游标变量，并且在打开游标变量时指定了其所对应的SELECT语句。

 

对象类型变量(REF obj_type)示例：

CREATE OR REPLACE TYPE home_type AS OBJECT(

street VARCHAR2(50),

city VARCHAR2(20),

state VARCHAR2(20),

zipcode VARCHAR2(6),

owner VARCHAR2(10));

 

输出：

类型已创建。

 

CREATE TABLE homes OF home_type;

 

输出：

表已创建。

 

INSERT INTO homes VALUES('呼和浩特12号','呼和浩特','内蒙','010010','马鸣');

INSERT INTO homes VALUES('呼和浩特13号','呼和浩特','内蒙','010010','秦斌');

COMMIT;

 

输出：

已创建 1 行。

已创建 1 行。

提交完成。

 

对象表homes存放家庭所在地以及户主姓名。假定每个家庭的四口人，当进行人口统计时，为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。

 

CREATE TABLE person(

id NUMBER(6) PRIMARY KEY,

name VARCHAR2(10),

addr REF home_type);

 

输出：

表已创建。

 

INSERT INTO person SELECT 1,'马鸣',ref(p)

FROM homes p WHERE p.owner='马鸣';

INSERT INTO person SELECT 2,'马武',ref(p)

FROM homes p WHERE p.owner='马鸣';

INSERT INTO person SELECT 3,'王敏',ref(p)

FROM homes p WHERE p.owner='马鸣';

COMMIT;

 

输出：

已创建 1 行。

已创建 1 行。

已创建 1 行。

提交完成。

 

当为person表插入数据时，addr列将会存放指向homes表相应数据的地址指针。

 

2.4LOB变量

LOB变量是指用于存储大批量数据的变量。Oracle将LOB分为两种：

l         内部LOB：包括CLOB、BLOB和NCLOB三种类型，它们的数据被存储在数据库中，并支持事务操作(提交、回退、保存点)。

l         外部LOB：只有BFILE一种类型，该类型的数据被存储在OS文件中，并且不支持事务操作。

其中，CLOB/NCLOB用于存储大批量字符数据，BLOB用于存储大批量二进制数据，而BFILE则存储指向OS文件的指针。

 

2.5非PL/SQL变量

在SQL*Plus或应用程序(例如Pro*C/C++)中与PL/SQL块之间进行数据交互时，需要使用SQL*Plus变量或应用程序变量来完成。当在PL/SQL块中引用非PL/SQL变量时，必须要在非PL/SQL变量前加冒号(“:”)。

1.       使用SQL*Plus变量

VAR name VARCHAR2(10)

BEGIN

  SELECT ename INTO :name FROM emp

  WHERE empno = 7788;

END;

/

PRINT name;

 

输出：

PL/SQL 过程已成功完成。

NAME

SCOTT

 

在PL/SQL块中引用SQL*Plus变量时，必须首先使用VARIABLE命令定义变量：而如果要在SQL*Plus中输出变量内容，则需要使用PRINT命令。

 

2.       使用Procedure Builder变量

CREATE CHAR name LENGTH 10

BEGIN

  SELECT ename INTO : name FROM emp

  WHERE empno = 7788;

END;

TEXT_IO.PUT_LINE(:name);

 

当在PL/SQL块中引用Procedure Builder变量时，必须首先使用.CREATE命令定义变量，而如果在Procedure Builder中输出变量内容，则可以使用包TEXT_IO。

 

3.       使用Pro*C/C++变量

CHAR name[10];

EXEC SQL EXECUTE

  BEGIN

    SELECT ename INTO :name FROM emp

    WHERE empno = 7788;

  END;

END-EXEC;

printf("雇员名：%s/n",name);

 

当在PL/SQL块中引用Pro*C/C++程序的宿主变量时，必须首先定义宿主变量，而如果要输出变量内容，则可以使用printf()语句。

 

三．编写PL/SQL代码

 

3.1PL/SQL词汇单元

编写PL/SQL块时，每个PL/SQL块都包含多行代码，而每行代码又是由多个合法单元组成的，这些合法单元被称为词汇。PL/SQL有四种词汇单元：

l         分隔符(Delimiter)：指具有特定含义的单个符号或组合符号。

l         标识符(Identifier)

l         文字串(Literal)

l         注释(Comment)

 

1.       分隔符

(1)    单符号分隔符

单符号分隔符是指只包含单个符号的PL/SQL分隔符。

符号	含义
+	加法操作符
%	属性提示符
‘	字符串分隔符
.	组件分隔符
/	除法分隔符
(	表达式或列表分隔符
)	表达式或列表分隔符
:	非PL/SQL变量提示符
,	项分隔符(表名、列名等分隔符)
*	乘法操作符
“	双引号变量分隔符
=	相等操作符
<	小于操作符
>	大于操作符
@	远程数据库访问操作符
;	语句终止符
-	减法操作符或负数操作符

 

(2)    组合分隔符

组合分隔符是指由多个符号组成的PL/SQL分隔符。

符号	含义
:=	赋值操作符
->	关联操作符
||	连接操作符
**	幂操作符
<<	标号开始分隔符
>>	标号结束分隔符
/*	多行注释开始分隔符
*/	多行注释结束分隔符
..	范围操作符
<>	不等操作符
!=	不等操作符
^=	不等操作符
<=	小于等于操作符
>=	大于等于操作符
--	单行注释提示符

 

2.       标识符

标识符用于指定PL/SQL程序单元和程序项的名称。通过使用标识符，可以定义常量、变量、例外、显式游标、游标变量、参数、子程序以及包的名称。当使用标识符定义PL/SQL程序项或程序单元时，必须要满足以下规则：

l         当使用标识符定义变量、常量时，每行只能定义一个标识符

l         当使用标识符定义变量、常量时，标识符名称必须要以阿拉伯字符(A~Z，a~z)开始，并且最大长度为30个字符。如果以其他字符开始，那么必须要使用双引号引住。

l         当使用标识符定义变量、常量时，标识符名称只能使用符号A~Z，a~z，0~9，_，$和#。如果要使用其他字符，那么必须要使用双引号引住。

l         当使用标识符定义变量、常量时，标识符名称不能使用Oracle的关键字。如果要使用Oracle关键字定义变量、常量，那么必须要使用双引号引住。

 

3.       文本

文本是指数字、字符、字符串、日期值或布尔值，而不是标识符。文本包括：

l         数字文本：是指整数或浮点数，它们可以直接在算术表达式中引用。当编写PL/SQL代码时，用户可以使用科学计数法和幂操作符(**)。注意，科学计数法和幂操作符只适用于PL/SQL语句，而不适用于SQL语句。

l         字符文本：是指用单引号引住的单个字符，这些字符可以是PL/SQL支持的所有可打印字符，包括阿拉伯字符(A~Z，a~z)、数字字符(0~9)以及其他符号(<，>等)。

l         字符串文本：是指由两个或两个以上字符组成的字符值。当指定字符串文本时，必须要用单引号将字符串文本引住。

l         布尔文本：是指BOOLEAN值，它主要用在条件表达式中，布尔文本有三种值：TRUE、FALSE和NULL。

l         日期时间文本：是指日期事件值，与字符串类似，日期文本也必须要用单引号引住，并且日期值必须要与日期格式和日期语言匹配。

 

4.       注释

注释用于解释单行代码或多行代码的作用，从而提高了PL/SQL程序的可读性。当编译并执行PL/SQL代码时，PL/SQL编译器会忽略注释。注释包括：

l         单行注释：是指放置在一行上的注释文本，并且单行注释主要用于说明单行代码的作用。在PL/SQL中使用—符号编写单行注释。

l         多行注释：是指分布到多行上的注释文本，并且其主要的作用是说明一段代码的作用。在PL/SQL中使用/*…*/来编写多行注释。

 

3.2PL/SQL代码编写规则

 

1.       标识符名迷宫规则

当在PL/SQL中使用标识符定义变量、常量时，标识符名称必须以字符开始。并且长度不能超过30个字符。另外，为了提高程序的可读性，Oracle建议用户按照以下规则定义各种标识符：

l         当定义变量时，建议使用v_作为前缀，例如v_sal，v_job等

l         当定义常量时，建议使用c_作为前缀，例如c_rate

l         当定义游标时，建议使用_cursor作为后缀，例如emp_cursor

l         当定义例外时，建议使用e_作为前缀，例如e_integrity_error

l         当定义PL/SQL表类型时，建议使用_table_type作为后缀，例如al_table_type

l         当定义PL/SQL表变量时，建议使用_table作为后缀，例如sal_table

l         当定义PL/SQL记录类型时，建议使用_record_type作为后缀，例如emp_record_type

l         当定义PL/SQL记录变量时，建议使用_record作为后缀，例如emp_record

 

2.       大小写规则

当在PL/SQl块中编写SQL语句和PL/SQL语句时，语句既可以使用大写格式，也可以使用小写格式，但是，为了提高程序的可读性和性能，Oralce建议用户按照以下大小写规则编写代码：

l     SQL关键字采用大写格式，例如SELECT，UPDATE，SET，WHERE等

l     PL/SQL关键字采用大写格式，例如DECLARE，BEGIN，END等

l     数据类型采用大写格式，例如INT，VARCHAR2，DATE等

l     标识符和参数采用小写格式，例如v_sal，c_rate等

l     数据库对象和列采用小写格式，例如emp，sal，ename等

 

3.       代码缩进

类似于其他编程语言，当编写PL/SQL块时，为了提高程序可读性，同级代码应该对齐，而下一级代码则应该缩进。

 

4.       嵌套块和变量范围

嵌套块是指嵌入在一个PL/SQL块中的另一个PL/SQL块，其中被嵌入的块被称为子块，而包含子块的PL/SQL块则被称为主块。当使用嵌套块时，注意，子块可以引用主块所定义的任何标识符，但主块却不能引用子块的任何标识符。也就是说，子块定义的标识符是局部标识符(局部变量)，而主块定义的标识符是全局标识符(全局变量)。

 

5.       在PL/SQL块中可以使用的SQL函数

在编写PL/SQL代码时，可以直接引用大多数的单行SQL函数。这些SQL函数包括单行数字函数(例如ROUND)、单行字符函数(例如UPPER)、转换函数(例如TO_CHAR)以及各种日期函数(例如MONTHS_BETWEEN)。但是，用户需要注意，某些SQL函数只能在SQL语句中引用，而不能直接在PL/SQL语句中引用，这些SQL函数包括GREATEST，LEAST，DECODE以及所有分组函数(例如SUM)。