Oracle之PL/SQL的变量小结

PLSQL变量有四种类型，分别是：

－标量类型（Scalar）

－复合类型（Composite）

－参照类型（Reference）

－ LOB类型（Large Object）

1、标量类型：

－只能存放单个数值的变量

－定义时，必须要指定标量的数据类型

1.1常用标量类型

（1）VARCHAR2(n)

定义可变长度的字符串

n指定字符串最大长度

n最大值是32767字节

使用时必须指定长度

当在PLSQL块钟使用该数据类型操纵VARCHAR2表列时，起数值长度不应超过4000字节

（2）CHAR(n)

定义固定长度字符串

n指定字符串的最大长度

n最大值是32767自己

使用时指定长度，若没指定，则使用默认值1

当在PLSQL块钟使用该数据类型操纵CHAR2表列时，起数值长度不应超过2000字节

（3）NUMBER(p，s)

定义固定长度的整数和浮点数

p表示精度，用于指定数字的总位数

s表示标度，用于指定小数点后的数字位数

（4）DATE

定义日期和数据数据

（5）BOOLEAN

定义布尔变量

值为：TRUE、FALSE、NULL

为PLSQL独有数据类型，表列不能采用该数据类型

NULL表示missing、inapplicable或者unknown

（6）LONG和LONG ROW

long：数据类型用于定义变长字符串，类似于VARCHAR2数据类型，但其字符串的最大长度为32760字节

long row：数据类型用于定义变长的二进制数据，其数据最大长度为32760字节

（7）BINARY_INTEGER

定义整数

数值范围-2147483647和2174483647之间

（8）BINARY_FLOAT和BINARY_DOUBLE

BINARY_FLOAT定义单精度浮点数（oracle 10g新增加）

BINARY_DOUBLE定义双精度浮点数（oracle 10g新增加）

（9）TIMESTAMP

定义时间和日期数据（oracle 9i新增加）

对其赋值方法与对date变量赋值方法完全相同

当显示TIMESTAMP变量数据时，不仅会显示日期，而且还会显示时间和上下午标记

1.2、定义标量类型

语法：

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

identifier：指定变量或常量名称

CONSTANT：指定是常量

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

NOT NULL：非空，有它是必须初始化

:= ：赋值符合

DEFAULT：默认值

expr：初始值

1.3、标量定义规则

每行声明一个变量

变量的赋值使用：identifier := value;

变量名称最多30个字符

1.4使用%TYPE属性

安装数据库列或其他变量来确定新变量的类型和长度（具有动态性和绑定性）

语法：identifier Table.column_name%TYPE;

或 identifier other_variable_name%TYPE;

2、复合变量指用于存放多个值的变量

当定义复合变量时，必须要使用PL/SQL的复合数据类型

PL/SQL的复合数据类型包括：

PL/SQL记录

PL/SQL表（索引表）

PL/SQL嵌套表

VARRAY

关于这些复合数据类型，由相关章节给出

3、参照变量指用于存放数值指针的变量

可以通过参照变量来共享相同对象，从而降低占用空间

两种参照变量：

游标变量（REF CURSOR）

对象类型变量（REF obj_type）

关于这些参照变量，由相关章节给出

4、LOB变量用于存储大批量数据的变量

分为两种：

内部LOB：包括CLOB、BLOB、NCLOB，它们的数据被存储在数据库中，并且支持事务操作

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

CLOB：存储大批量字符数据

NCLOB：存储大批量字符数据，unicode编码

BLOB：存储大批量二进制数据

BFILE：存储指向OS文件的指针

5、非PL/SQL变量5.1、替换变量（仅用于SQL *Plus或者用于原理和SQL *Plus相同的开发工具）：

临时存储值

利用它可以达到创建通用脚本的目的

利用它可以达到和用户交互，故在SQL *Plus中又称交互式命令

替换变量的格式式在变量名称前加一个&，以便在运行SQL命令时提示用户输入替换数据，然后按输入数据运行SQL命令

语法：

（1）& ：“&变量名”eg：&name;

生命周期：单次引用中，不需要声明，如果替换字符或日期类型，最好用单引号扩起

使用范围：where、order by、列表达式、表名、整个SELECT 语句中

（2）&& ：“&&变量名”eg：&&name;

生命周期：整个会话（session连接）,不需要声明

（3）define ：“define 变量名=变量值”eg：DEFINE a = clark;

生命周期：整个会话，预先声明，使用时用&引用声明的变量

define variable=用户创建的CHAR类型的值：define 变量名=值；

define column_name(变量名)：查看变量命令。

undefine 变量名：清除变量

define：查看在当前会话中所有的替换变量和它们的值

举例：

DEFINE vMonth = 201001;

SELECT ‘&vMonth’ FROM dual;

UNDEFINE vMonth;

（4）accept

生命周期：整个会话

预先声明，可以客户化提示信息，使用时用&引用声明的变量。

定义：

accept 变量名name number/char/date prompt ‘提示信息内容’即：ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]

解释：

PROMPT命令：用于输出提示用户的信息，以便使用户了解脚本文件的功能和运行情况

PAUSE命令：用于暂停脚本文件的运行

HIDE选项：用于隐藏用户的输入，使别人不可见，安全

这条命令的意思是：当plsql程序段执行到变量name的时候，此时需要用户的交互才能继续执行下去，plsql程序段会显示“提示信息内容”让用户输入相关信息（如果指定hide选项，那么在接下去用户输入的东西将被用星号显示出来增加安全，有点像输入密码），用户输入的内容被接收到并且把它付给name，关于在“提示信息内容”下用户输入的内容的类型，plsql程序段开发人员来通过number/char/date指定，变量name得到正确的值以后，继续执行相关下面的程序！

例：accept a char prompt ‘请输入员工的雇佣时间(yyyy-mm-dd):’ hide

例：accept a char prompt ‘input a:’ hide

替换变量非常的依赖SQL *Plus的环境，当环境变量verify被关闭的时候它就不可用，只有通过打开它才能使用：

set verify(环境变量) off;关闭调试命令（关掉替换过程）

set verify(环境变量) on;打开调试命令(可以看到替换过程)

5.2、SQL *PLUS环境变量（仅用于SQL *Plus或者用于原理和SQL *Plus相同的开发工具）：

ECHO 显示回显

HEADING {OFF/ON}是否显示列标题

ARRAYSIZE{20/n}每一次从查询得到的返回量的大小

FEEDBACK{OFF/ON}回馈，反馈信息

LONG{80/n}on/text} LONG类型

LINESIZE 行的宽度

SET LINESIZE n(最好是在200之内)

PAGESIZE ：设置页的大小。SET PAGESIZE N

wrap{off/on} 折行

SET 修改

SHOW 显示

SQL *PLUS中格式化显示的命令：

COLUMN[column option]可以设置字段或字段别名的格式

COLUMN last_name HEADING employee|name ‘|’代表换行

col 字段名查看命令

CL[EAR]：清除列的格式

HEA[DING] TEXT：设置列标题

FOR[MAT] FORMAT：格式化显示列的值，对字符和数字有效，对日期无效，eg：column salary justify left format ＄999,999.00，其中justify left:左对齐；col manager_id format 999999999

限制字符串的长度有A+数字限制

限制数字的长度有9，有几为9就限制成几位。

NOPRINT/PRINT NOPRINT：把一个字段从输出上屏蔽掉（返回但不显示）。

col 字段名 noprint/print.

NULL如果有NULL值，显示什么。

col name null ‘on employee’

TTITLE[text/off/on]设置报表的表头

BTITLE[text/off/on]设置报表的表尾

做报表的时候要先想好PAGESIZE的大小。

BREAK ON [REPORT_ELEMENT]

压制重复值的显示，只能跟一个字段名才有效，eg：

select department_id,last_name

from employees

where rownum<30

order by 1,2;

break on department_id

5.3、做PL/SQL脚本文件的过程：变量定义accept

环境变量设置SET

格式控制命令

SPOOL

使用变量的SQL

SPOOL OFF

清除格式控制

重置环境变量

释放变量

5.4、引用非PL/SQL变量当要在PL/SQL块中引用非PL/SQL变量时，也就是要引用PL/SQL块所在地的host变量（或称“环境变量”）时，必须要在非PL/SQL变量前加冒号（“：”），eg：”:name”,name为非PL/SQL变量。

———————–

Oracle中PL/SQL语句的变量、常量声明和赋值

1.声明：

a.格式：Variable_name [constant] databyte [not null] [:=default expression]

b.变量与常量声明基本一致，使用constant声明的为常量，不使用为变量；

c.使用%TYPE和%ROWTYPE声明可以使变量的类型与表中字段类型或整个记录类型保持一致；

2.赋值：

a.使用“:=”赋值；

b.使用“select into” 或 “fetch into”赋值；

c.使用“&str”从键盘输入赋值；

实例：

declare

isal number(7,2);

iname constant varchar2(10) := ‘SCOTT’;

ino emp.empno%TYPE;

iemp emp%ROWTYPE;

begin

dbms_output.put_line(‘Your input string:’ || ‘&str’);

select sal into isal from emp where ename=iname;

dbms_output.put_line(‘SAL:’ || isal);

ino := ‘7499’;

select * into iemp from emp where empno=ino;

dbms_output.put_line(‘My name is ‘ || iemp.ename);

end;

——————–

在缓存区中显示

set serveroutput on size 10000 –改变数据量显示

declare

x varchar2(10);                                –定义变量

begin

x:=’This is..’;                                –为变量赋值

dbms_output.put_line(‘x的值为：’ || x);        –输出包       || 为字符串连接符

end;

/                                               –执行

————————————

SQL> var vMonth varchar2(10);

SQL> begin

2 :vMonth := ‘200901’;

3 end;

4 /

PL/SQL 过程已成功完成。

SQL> select :vMonth from dual;

———————————-

oracle 绑定变量（bind variable）

oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.

一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

含绑定变量的sql 语句：

SQL> SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

Sql*plus 中使用绑定变量：

sql> variable x number;

sql> exec 😡 := 123;

sql> SELECT fname, lname, pcode FROM cust WHERE id =:x;

pl/sql

pl/sql很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：

create or replace procedure dsal(p_empno in number)

as

begin

update emp

set sal=sal*2

where empno = p_empno;

commit;

end;

/

也许此时你会想要利用绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql中，引用变量即是引用绑定变量。

但是在pl/sql中动态sql并不是这样。

在vb，java以及其他应用程序中都得显式地利用绑定变量。

对于绑定变量的支持不仅仅限于oracle,其他RDBMS向SQLＳＥＲＶＥＲ也支持这一特性。

但是并不是任何情况下都需要使用绑定变量，　下面是两种例外情况：

１．对于隔相当一段时间才执行一次的ｓｑｌ语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消

２．数据仓库的情况下。

=======================================================================

oracle变量学习笔记

特点

PL/SQL语言是SQL语言的扩展，具有为程序开发而设计的特性，如数据封装、异常处理、面向对象等特性。PL/SQL是嵌入到Oracle服务器和开发工具中的，所以具有很高的执行效率和同Oracle数据库的完美结合。在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，这样就可以编写具有数据库事务处理功能的模块。

至于数据定义(DDL)和数据控制(DCL)命令的处理，需要通过Oracle提供的特殊的DMBS_SQL包来进行。PL/SQL还可以用来编写过程、函数、包及数据库触发器。过程和函数也称为子程序，在定义时要给出相应的过程名和函数名。它们可以存储在数据库中成为存储过程和存储函数，并可以由程序来调用，它们在结构上同程序模块类似。

PL/SQL过程化结构的特点是：可将逻辑上相关的语句组织在一个程序块内；通过嵌入或调用子块，构造功能强大的程序；可将一个复杂的问题分解成为一组便于管理、定义和实现的小块。

块结构和基本语法要求

PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。

块中各部分的作用解释如下：

(1) DECLARE：声明部分标志。

(2) BEGIN：可执行部分标志。

(3) EXCEPTION：异常处理部分标志。

(4) END；：程序结束标志。

在以下的训练中，将使用函数DBMS_OUTPUT.PUT_LINE显示输出结果。DBMS_OUTPUT是Oracle提供的包，该包有如下三个用于输出的函数，用于显示PL/SQL程序模块的输出信息。

第一种形式：

DBMS_OUTPUT.PUT(字符串表达式)；

用于输出字符串，但不换行，括号中的参数是要输出的字符串表达式。

第二种形式：

DBMS_OUTPUT.PUT_LINE(字符串表达式)；

用于输出一行字符串信息，并换行，括号中的参数是要输出的字符串表达式。

第三种形式：

DBMS_OUTPUT.NEW_LINE；

用来输出一个换行，没有参数。

调用函数时，在包名后面用一个点“.”和函数名分隔，表示隶属关系。

要使用该方法显示输出数据，在SQL*Plus环境下要先执行一次如下的环境设置命令：

SET SERVEROUTPUT ON [SIZE n]

用来打开DBMS_OUTPUT.PUT_LINE函数的屏幕输出功能，系统默认状态是OFF。其中，n表示输出缓冲区的大小。n的范围在2000～1 000 000之间，默认为2000。如果输出内容较多，需要使用SIZE n来设置较大的输出缓冲区。

在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，所以PL/SQL程序是同SQL语言紧密结合在一起的。在PL/SQL程序中，最常见的是使用SELECT语句从数据库中获取信息，同直接执行SELECT语句不同，在程序中的SELECT语句总是和INTO相配合，INTO后跟用于接收查询结果的变量，形式如下：

SELECT 列名1，列名2… INTO 变量1，变量2… FROM 表名 WHERE 条件；

注意：接收查询结果的变量类型、顺序和个数同SELECT语句的字段的类型、顺序和个数应该完全一致。并且SELECT语句返回的数据必须是一行，否则将引发系统错误。当程序要接收返回的多行结果时，可以采用后面介绍的游标的方法。

使用INSERT、DELETE和UPDATE的语法没有变化，但在程序中要注意判断语句执行的状态，并使用COMMIT或ROLLBACK进行事务处理。

以下训练包含了按照标准结构书写的一个包含SELECT语句的PL/SQL程序示例。

【训练1】查询雇员编号为7788的雇员姓名和工资。

步骤1：用SCOTT账户登录SQL*Plus。

步骤2：在输入区输入以下程序：

Sql代码

/*这是一个简单的示例程序*/

        SET SERVEROUTPUT ON

        DECLARE–定义部分标识

         v_name VARCHAR2(10); –定义字符串变量v_name

         v_sal   NUMBER(5); –定义数值变量v_sal

        BEGIN           –可执行部分标识

SELECT   ename,sal

          INTO v_name,v_sal

        FROM    emp

         WHERE empno=7788;

        –在程序中插入的SQL语句

        DBMS_OUTPUT.PUT_LINE(‘7788号雇员是：’||v_name||’，工资为：’||to_char(v_sal));

        –输出雇员名和工资

        END;

/*这是一个简单的示例程序*/

  SET SERVEROUTPUT ON

  DECLARE–定义部分标识

    v_name VARCHAR2(10); –定义字符串变量v_name

    v_sal   NUMBER(5); –定义数值变量v_sal

  BEGIN   –可执行部分标识

SELECT ename,sal

    INTO v_name,v_sal

  FROM emp

    WHERE empno=7788;

  –在程序中插入的SQL语句

  DBMS_OUTPUT.PUT_LINE(‘7788号雇员是：’||v_name||’，工资为：’||to_char(v_sal));

  –输出雇员名和工资

  END;   –结束标识

步骤3：按执行按钮或F5快捷键执行程序。

输出的结果是：

Sql代码

7788号雇员是：SCOTT，工资为：3000

        PL/SQL 过程已成功完成。

7788号雇员是：SCOTT，工资为：3000

  PL/SQL 过程已成功完成。

以上程序的作用是，查询雇员编号为7788的雇员姓名和工资，然后显示输出。这种方法同直接在SQL环境下执行SELECT语句显示雇员的姓名和工资比较，程序变得更复杂。那么两者究竟有什么区别呢？SQL查询的方法，只限于SQL环境，并且输出的格式基本上是固定的。而程序通过把数据取到变量中，可以进行复杂的处理，完成SQL语句不能实现的功能，并通过多种方式输出。

“–”是注释符号，后边是程序的注释部分。该部分不编译执行，所以在输入程序时可以省略。/*……*/中间也是注释部分，同“–”注释方法不同，它可以跨越多行进行注释。

PL/SQL程序的可执行语句、SQL语句和END结束标识都要以分号结束。

数据类型

变量的基本数据类型同SQL部分的字段数据类型相一致，但是也有不同，具体看附件里的图片。

NUMBER和VARCHAR2是最常用的数据类型。

VARCHAR2是可变长度的字符串，定义时指明最大长度，存储数据的长度是在最大长度的范围自动调节的，数据前后的空格，Oracle 9i会自动将其删去。

NUMBER型可以定义数值的总长度和小数位，NUMBER(10,3)表示定义一个宽度为10、小数位为3的数值。整个宽度减去小数部分的宽度为整数部分的宽度，所以整数部分的宽度为7。

CHAR数据类型为固定长度的字符串，定义时要指明宽度，如不指明，默认宽度为1。定长字符串在显示输出时，有对齐的效果。

   DATE类型用于存储日期数据，内部使用7个字节。其中包括年、月、日、小时、分钟和秒数。默认的格式为DD-MON-YY，如：07-8月-03表示2003年8月7日。

BOOLEAN为布尔型，用于存储逻辑值，可用于PL/SQL的控制结构。

LOB数据类型可以存储视频、音频或图片，支持随机访问，存储的数据可以位于数据库内或数据库外，具体有四种类型：BFILE、BLOB、CLOB、NCLOB。但是操纵大对象需要使用Oracle提供的DBMS_LOB包。

变量定义

1．变量定义

变量的作用是用来存储数据，可以在过程语句中使用。变量在声明部分可以进行初始化，即赋予初值。变量在定义的同时也可以将其说明成常量并赋予固定的值。变量的命名规则是：以字母开头，后跟其他的字符序列，字符序列中可以包含字母、数值、下划线等符号，最大长度为30个字符，不区分大小写。不能使用Oracle的保留字作为变量名。变量名不要和在程序中引用的字段名相重，如果相重，变量名会被当作列名来使用。

变量的作用范围是在定义此变量的程序范围内，如果程序中包含子块，则变量在子块中也有效。但在子块中定义的变量，仅在定义变量的子块中有效，在主程序中无效。

变量定义的方法是：

变量名 [CONSTANT] 类型标识符 [NOT NULL][:=值|DEFAULT 值];

关键字CONSTANT用来说明定义的变量是常量，如果是常量，必须有赋值部分进行赋值。

关键值NOT NULL用来说明变量不能为空。

∶=或DEFAULT用来为变量赋初值。

变量可以在程序中使用赋值语句重新赋值。通过输出语句可以查看变量的值。

在程序中为变量赋值的方法是：

变量名:=值或 PL/SQL 表达式;

以下是有关变量定义和赋值的练习。

【训练1】变量的定义和初始化。

输入和运行以下程序：

Sql代码

SET SERVEROUTPUT ON

        DECLARE     –声明部分标识

        v_job       VARCHAR2(9);

        v_count BINARY_INTEGER DEFAULT 0;

        v_total_sal NUMBER(9,2) := 0;

        v_date      DATE := SYSDATE + 7;

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

        v_valid     BOOLEAN NOT NULL := TRUE;

        BEGIN

        v_job:=’MANAGER’;

        –在程序中赋值

         DBMS_OUTPUT.PUT_LINE(v_job);

        –输出变量v_job的值

         DBMS_OUTPUT.PUT_LINE(v_count);

        –输出变量v_count的值

        DBMS_OUTPUT.PUT_LINE(v_date);

        –输出变量v_date的值

        DBMS_OUTPUT.PUT_LINE(c_tax_rate);

        –输出变量c_tax_rate的值

        END;

SET SERVEROUTPUT ON

  DECLARE  –声明部分标识

  v_job  VARCHAR2(9);

  v_count BINARY_INTEGER DEFAULT 0;

  v_total_sal NUMBER(9,2) := 0;

  v_date  DATE := SYSDATE + 7;

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

  v_valid  BOOLEAN NOT NULL := TRUE;

  BEGIN

  v_job:=’MANAGER’;

  –在程序中赋值

    DBMS_OUTPUT.PUT_LINE(v_job);

  –输出变量v_job的值

    DBMS_OUTPUT.PUT_LINE(v_count);

  –输出变量v_count的值

   DBMS_OUTPUT.PUT_LINE(v_date);

  –输出变量v_date的值

   DBMS_OUTPUT.PUT_LINE(c_tax_rate);

  –输出变量c_tax_rate的值

  END;

执行结果：

Sql代码

MANAGER

        0

        18-4月 -03

        8.25

        PL/SQL 过程已成功完成。

MANAGER

  0

  18-4月 -03

  8.25

  PL/SQL 过程已成功完成。

说明：本训练共定义了6个变量，分别用“:=”赋值运算符或DEFAULT 关键字对变量进行了初始化或赋值。其中：c_tax_rate为常量，在数据类型前加了 “CONSTANT” 关键字；v_valid变量在赋值运算符前面加了关键字“NOT NULL”，强制不能为空。如果变量是布尔型，它的值只能是“TRUE”、“FALSE”或“NULL”。本练习中的变量v_valid布尔变量的值只能取“TRUE”或“FALSE”。

2．根据表的字段定义变量

变量的声明还可以根据数据库表的字段进行定义或根据已经定义的变量进行定义。方法是在表的字段名或已经定义的变量名后加 %TYPE，将其当作数据类型。定义字段变量的方法如下：

变量名表名.字段名%TYPE;

【训练2】根据表的字段定义变量。

输入并执行以下程序：

Sql代码

SET SERVEROUTPUT ON

        DECLARE

        v_ename emp.ename%TYPE;–根据字段定义变量

        BEGIN

        SELECT ename

         INTO       v_ename

         FROM       emp

         WHERE empno = 7788;

        DBMS_OUTPUT.PUT_LINE(v_ename);

        –输出变量的值

        END;

SET SERVEROUTPUT ON

  DECLARE

     v_ename emp.ename%TYPE;–根据字段定义变量

  BEGIN

     SELECT ename

   INTO  v_ename

   FROM  emp

   WHERE empno = 7788;

  DBMS_OUTPUT.PUT_LINE(v_ename);

  –输出变量的值

  END;

执行结果：

Sql代码

SCOTT

        PL/SQL 过程已成功完成。

SCOTT

  PL/SQL 过程已成功完成。

说明：变量v_ename是根据表emp的ename字段定义的，两者的数据类型总是一致的。

如果我们根据数据库的字段定义了某一变量，后来数据库的字段数据类型又进行了修改，那么程序中的该变量的定义也自动使用新的数据类型。使用该种变量定义方法，变量的数据类型和大小是在编译执行时决定的，这为书写和维护程序提供了很大的便利。

3．结合变量的定义和使用

我们还可以定义SQL*Plus环境下使用的变量，称为结合变量。结合变量也可以在程序中使用，该变量是在整个SQL*Plus环境下有效的变量，在退出SQL*Plus之前始终有效，所以可以使用该变量在不同的程序之间传递信息。结合变量不是由程序定义的，而是使用系统命令VARIABLE定义的。在SQL*Plus环境下显示该变量要用系统的PRINT命令。

在SQL*Plus环境下定义结合变量的方法如下：

VARIABLE 变量名数据类型

【训练3】定义并使用结合变量。

步骤1：输入和执行下列命令，定义结合变量g_ename：

Sql代码

VARIABLE g_ename VARCHAR2(100)

VARIABLE g_ename VARCHAR2(100)

步骤2：删掉刚才输入的语句,输入和执行下列程序：

Sql代码

SET SERVEROUTPUT ON

        BEGIN

         :g_ename:=:g_ename|| ‘Hello~ ‘;

        –在程序中使用结合变量

         DBMS_OUTPUT.PUT_LINE(:g_ename);

        –输出结合变量的值

        END;

SET SERVEROUTPUT ON

  BEGIN

   :g_ename:=:g_ename|| ‘Hello~ ‘;

  –在程序中使用结合变量

   DBMS_OUTPUT.PUT_LINE(:g_ename);

  –输出结合变量的值

  END;

输出结果：

Sql代码

Hello~

        PL/SQL 过程已成功完成。

Hello~

  PL/SQL 过程已成功完成。

步骤3：重新执行程序。

输出结果：

Sql代码

Hello~ Hello~

        PL/SQL 过程已成功完成。

Hello~ Hello~

  PL/SQL 过程已成功完成。

步骤4：程序结束后用命令显示结合变量的内容：

Sql代码

PRINT g_ename

PRINT g_ename

输出结果：

Sql代码

G_ENAME

        ———————————————–

        Hello~ Hello~

G_ENAME

  ———————————————–

  Hello~ Hello~

说明：g_ename为结合变量，可以在程序中引用或赋值，引用时在结合变量前面要加上“∶”。在程序结束后该变量的值仍然存在，其他程序可以继续引用。

4．记录变量的定义

还可以根据表或视图的一个记录中的所有字段定义变量，称为记录变量。记录变量包含若干个字段，在结构上同表的一个记录相同，定义方法是在表名后跟%ROWTYPE。记录变量的字段名就是表的字段名，数据类型也一致。

记录变量的定义方法是：

记录变量名表名%ROWTYPE；

获得记录变量的字段的方法是：记录变量名.字段名，如emp_record.ename。

如下练习中定义并使用了记录变量。

【训练4】根据表定义记录变量。

输入并执行如下程序：

Sql代码

SET SERVEROUTPUT ON

        DECLARE

        emp_record      emp%ROWTYPE;–定义记录变量

        BEGIN

         SELECT * INTO emp_record

         FROM           emp

         WHERE      mpno = 7788;–取出一条记录

        DBMS_OUTPUT.PUT_LINE(emp_record.ename); –输出记录变量的某个字段

        END;

SET SERVEROUTPUT ON

  DECLARE

  emp_record  emp%ROWTYPE;–定义记录变量

  BEGIN

    SELECT * INTO emp_record

    FROM   emp

    WHERE  mpno = 7788;–取出一条记录

  DBMS_OUTPUT.PUT_LINE(emp_record.ename); –输出记录变量的某个字段

  END;

执行结果为：

Sql代码

SCOTT

        PL/SQL 过程已成功完成。

SCOTT

  PL/SQL 过程已成功完成。

说明：在以上的练习中定义了记录变量emp_record，它是根据表emp的全部字段定义的。SELECT语句将编号为7788的雇员的全部字段对应地存入该记录变量，最后输出记录变量的雇员名称字段emp_record.ename的内容。如果要获得其他字段的内容，比如要获得编号为7788的雇员的工资，可以通过变量emp_record.sal获得，依此类推。

5．TABLE类型变量

在PL/SQL中可以定义TABLE类型的变量。 TABLE数据类型用来存储可变长度的一维数组数据，即数组中的数据动态地增长。要定义TABLE变量，需要先定义TABLE数据类型。通过使用下标来引用TABLE变量的元素。

TABLE数据类型的定义形式如下：

TYPE 类型名 IS TABLE OF 数据类型[NOT NULL] INDEX BY BINARY_INTEGER;

此数据类型自动带有BINARY_INTEGER型的索引。

【训练5】定义和使用TABLE变量：

Sql代码

SET SERVEROUTPUT ON

        DECLARE

        TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; –类型说明

         v_t      type_table;     –定义TABLE变量

        BEGIN

         v_t(1):=’MONDAY’;

         v_t(2):=’TUESDAY’;

        v_t(3):=’WEDNESDAY’;

         v_t(4):=’THURSDAY’;

        v_t(5):=’FRIDAY’;

DBMS_OUTPUT.PUT_LINE(v_t(3)); –输出变量的内容

        END;

SET SERVEROUTPUT ON

  DECLARE

   TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; –类型说明

   v_t   type_table;     –定义TABLE变量

  BEGIN                v_t.extend;

   v_t(1):=’MONDAY’;                 vt.extend;

   v_t(2):=’TUESDAY’;

   DBMS_OUTPUT.PUT_LINE(v_t(2)); –输出变量的内容

  END;

执行结果为：

Sql代码

WEDNESDAY

        PL/SQL 过程已成功完成。

WEDNESDAY

  PL/SQL 过程已成功完成。同样，除了TABLEl数组外，还有VARRAY数组类型，定义方式一致。例子：declare type varray_table is varray(3) of varchar2(10);     ta_va varray_table;[:=varray_table(3)]    begin      [ta_va.extend;]      ta_va(1):=’we’;      [ta_va.extend;]       ta_va(2):=’we8′;    end;和在TABLE中一样，必须在声明数组名的时候初始化数组大小，如果不在声明的地方初始化数组大小，则在begin后面，且在每条数组处理代码前面添加数组名.extend;

说明：本例定义了长度为10的字符型TABLE变量，通过赋值语句为前五个元素赋值，最后输出第三个元素。

循环和判断

判断语句为IF THEN END IF;

如：begin

if &no>8

then raise_application_error(-2001,’not!’);

end if;

end;

循环语句为FOR i IN 0..100 LOOP开始，以END LOOP;结束。这里i 是指循环变量，0..100指循环变量的循环值。

如：begin

for i in 0..10 loop

dbms_output.put_line(i);

end loop;

end;

运算符和函数

PL/SQL常见的运算符和函数包括以下方面(这里只做简单的总结，可参见SQL部分的例子)：

* 算术运算：加(+)、减(?)、乘(*)、除(/)、指数(**)。

* 关系运算：小于()、大于等于(>=)、等于(=)、不等于(!=或<>)。

* 字符运算：连接(||)。

* 逻辑运算：与(AND)、或(OR)、非(NOT)。

还有如下所示的特殊运算。

IS NULL:用来判断运算对象是否为空，为空则返回TRUE

LIKE:用来判断字符串是否与模式匹配

BETWEEN…AND…:判断值是否位于一个区间

IN(…):测试运算对象是否在一组值的列表中

IS NULL或IS NOT NULL用来判断运算对象的值是否为空，不能用“=”去判断。另外，对空值的运算也必须注意，对空值的算术和比较运算的结果都是空，但对空值可以进行连接运算，结果是另外一部分的字符串。例如：

NULL+5的结果为NULL。

NULL>5的结果为NULL。

NULL|| ‘ABC’ 的结果为’ABC’。

在PL/SQL中可以使用绝大部分Oracle函数，但是组函数(如AVG( )、MIN( )、MAX( )等)只能出现在SQL语句中，不能在其他语句中使用。还有GREATEST( )、LEAST( )也不能使用。类型转换在很多情况下是自动的，在不能进行自动类型转换的场合需要使用转换函数。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10527166/viewspace-683268/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10527166/viewspace-683268/