第10章 PL-SQL编程语言1

福建工程学院第10章PL/SQL编程基础1本章要点编写PL/SQL匿名块声明PL/SQL变量、常量、数据类型介绍赋值语句和算术运算符理解%TYPE属性掌握各种变量的范围和使用如何在PL/SQL块中编写可执行的代码掌握DBMS_OUTPUT包与输入输出嵌套块应遵循的规则准备编写简单的PL/SQL块什么是PL/SQL

PL/SQL(Procedurallanguage/SQL)是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言。

PL/SQL是对SQL的扩展PL/SQL支持现代软件工程的很多重要特征，

如:数据封装、面向对象编程（OOP)、错误（例外）处理和信息隐藏等。PL/SQL是一种“后台技术”，它的输入输出是面向数据库的，主要用于编写服务器端的程序。什么是PL/SQL

支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，给SQL语句的执行添加程序逻辑。PL/SQL是Oracle数据库的核心编程语言，用于编写存储过程、函数、触发器、包，面向对象数据库的类的定义等等。与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性。PL/SQL简介支持SQL，在PL/SQL中可以使用：数据操纵命令事务控制命令游标控制SQL函数和SQL运算符可移植性，可运行在任何操作系统和平台上的Oralce数据库更佳的性能，PL/SQL经过编译执行安全性，可以通过存储过程限制用户对数据的访问用户将整个语句块发送给OracleOracleProcedureBeginProcedureCallSQLCommand…EndPL/SQL简介PL/SQL引擎驻留在Oracle服务器中该引擎接受PL/SQL块并对其进行编译执行将PL/SQL块发送给Oracle服务器用户执行过程语句引擎将SQL语句发送给SQL语句执行器Oracle服务器PL/SQL引擎SQL

语句

执行器过程语句

执行器执行SQL语句将结果发送给用户PL/SQL基本原理PL/SQL程序中可以使用大写或小写字母一个完整的语句可以占若干行，但关键字不能被分开写在两行。PL/SQL语句包括保留字，标识符，定界符，字面值和注释保留字（关键字）：专门用途的单词用户定义标识符：用来命名变量、常量、过程、函数、游标、表、记录和异常。字面值：数字类型，字符类型和布尔类型注释：对每个代码段的目的和用途的描述PL/SQL块的语法和原则标识符至多有30个字符允许使用字母,数字,$,#,_不能是保留字，但若保留字用双引号标明，即可作为标识符（例如，"SELECT")。必须以字母开头不允许与数据库中表的列名相同保留字应为大写形式，以提高可读性。PL/SQL块的语法和原则字面值字面值是精确的数值、字符、串或布尔值。字符或日期型文字必须用单引号标识。数字可以是简单的值(如,–32.5)，也可是用科学计数法表示的数值(如,2E5,意义2*105=200000)。v_ename:='Henderson';注释在单行注释前加前缀双短线(--)。多行注释放在/*和*/之间。举例：用月工资计算年工资.SETSERVEROUTPUTONDECLAREv_salNUMBER(9,2);BEGIN/*Computetheannualsalarybasedonthemonthlysalaryinputfromtheuser*/v_sal:=&monthly_sal*12;DBMS_OUTPUT.PUT_LINE('Theannualsalaryis'||TO_CHAR(v_sal));END;--Thisistheendoftheblock/

在PL/SQL中显示输出结果DBMS_OUTPUT是Oracle提供的包DBMS_OUTPUT包为PL/SQL程序提供了对一个内部缓冲区的输入输出功能DBMS_OUTPUT包中的过程实现了两个基本操作：GET和PUTPUT操作是将过程参数中的数据放入到一个内部缓冲区中，而GET操作则是从这个缓冲区中读出数据，并将其内容作为参数值返回DBMS_OUTPUT包与输入输出在PL/SQL中显示输出结果要使用该方法显示输出数据，必须将环境变量作如下的设置：

SETSERVEROUTPUTON

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

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

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

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

DBMS_OUTPUT包与输入输出注释、替换变量与

DBMS_OUTPUT包使用实例SETSERVEROUTPUTONDECLAREv_salNUMBER(9,2);BEGIN/*SQL中的替换变量在PL/SQL中仍然有效*/v_sal:=&monthly_sal*12;--不需要在程序块中声明替换变量DBMS_OUTPUT.PUT_LINE('Theannualsalaryis'

||TO_CHAR(v_sal));END;/以下程序只能在sql-plus环境中运行PL/SQL中的SQL函数过程语句中的有效函数:单行数字单行字符数据类型转换日期过程语句中的无效函数:DECODE分组函数。分组函数仅在PL/SQL块中的SQL语句中适用。与SQL中同PL/SQL函数PL/SQL提供了大量的有效函数用来处理数据，这些函数有以下几类：错误报告函数（Errorreporting）数字函数（Number）字符函数（Character）转换函数（Conversion）日期函数（Date）杂项函数（Miscellaneous）PL/SQL函数例：给一个公司创建一个通信地址.把员工名转换成小写形式.v_mailing_address:=v_name||CHR(10)|| v_address||CHR(10)||v_state|| CHR(10)||v_zip;v_ename :=LOWER(v_ename);注解：CHR是一个SQL函数，把ASCII码转换成相应的字符‘-’。数据类型转换函数 把数据转换成可比较的数据类型。混合的数据类型可能会出现错误，影响性能转换函数:TO_CHARTO_DATETO_NUMBERDECLAREv_dateVARCHAR2(15);BEGINSELECTTO_CHAR(hiredate, 'MON.DD,YYYY')INTOv_dateFROMempWHEREempno=7839;END;数据类型转换函数如果variablev_date被声明为DATE型，该语句就会产生一个编译错误。v_date:='January13,1998';v_date:=TO_DATE('January13,1998','MonthDD,YYYY');使用TO_DATE转换函数可改正错误。PL/SQL块结构DECLARE–

可选变量、游标、例外声明用户自定义的特殊类型BEGIN–

必须SQL语句PL/SQL语句EXCEPTION–

可选错误发生时的处理动作END;–

必须DECLAREBEGINEXCEPTIONEND;PL/SQL是块结构化语言,即程序能被分成若干个逻辑块。愉中对象有自己的作用域。一个PL/SQL块有三个部分构成：声明（可选）,执行(必须)和异常处理(可选)。可选的该部分给出当在执行部分发生错误或出现异常情况时的具体处理动作。可嵌套块。异常处理部分必须的该部分包括SQL语句（用于处理数据库中的数据）及PL/SQL语句（用于处理块中的数据）。可嵌套块。执行部分可选的该部分包括所有在声明、执行部分所用到的变量、常量、游标以及用户自定义的特殊类型。

声明部分备注描述块结构PL/SQL块结构PL/SQL块结构DECLAREv_variableVARCHAR2(5);BEGINSELECT column_nameINTO v_variableFROM table_name;EXCEPTIONWHENexception_nameTHEN...END;DECLAREBEGINEXCEPTIONEND;PL/SQL块结构PL/SQL块是构成PL/SQL程序的基本单元将逻辑上相关的声明和语句组合在一起

写一个简单的PL/SQL过程,输出HelloWorld!!!setserveroutputonbegindbms_output.put_line('HelloWorld!!!');end;/当该块成功执行，没有任何未经处理的错误和编译错误时，将会显示如下语句：PL/SQL过程已成功完成。PL/SQLproceduresuccessfullycompletedPL/SQL块的执行过程与运行环境

PL/SQL块的编辑环境有如下几种：（1）Oracle工具。Oracle9i

中的SQL*plusWorksheet，Oracle10g中的iSQL*Plus。（2）嵌入在应用程序中。（3）使用记事本写脚本，以SQL为文件后缀名进行保存。输入如下的程序：DECLAREv_comm_percentCONSTANTnumber:=10;BEGINupdateEMPSETcomm=sal*v_comm_percentWHEREdeptno=10;END;执行PL/SQL块。PL/SQL块的执行方法有两种：（1）SQL命令行。SQL*Plus中匿名的PL/SQL块的执行是在PL/SQL块后输入符号“/”来执行的。PL/SQL块如下：执行方法：SQL>/PL/SQLproceduresuccessfullycompleted.或者SQL>RUN如果是脚本可以：SQL>@脚本文件名（2）在工具SQLPlusWorksheet（Oracle9i）中直接执行，或在工具iSQL*Plus（Oracle10g）中执行。块包括两种类型：匿名块和命名块匿名块匿名块是没有被命名的块。他们是在应用程序即将执行时被声明，在运行期间被传递给PL/SQL引擎执行。命名块子程序是被命名的PL/SQL块，能接收参数，能被调用。子程序存储在服务器或应用程序层中。它可以声明为过程(procedure)也可以为函数(function)，通常仅执行一动作时定义为过程，计算值时定义为函数。包(package)是由一组过程和函数组成。触发器(trigger)是由DML语句以隐含方式调用的一个块。附注：函数类似于过程，只是函数必须返回一个值。块的类型块的类型匿名块 过程函数[DECLARE]BEGIN--statements[EXCEPTION]END;PROCEDUREnameISBEGIN--statements[EXCEPTION]END;FUNCTIONnameRETURNdatatypeISBEGIN--statementsRETURNvalue;[EXCEPTION]END;程序结构匿名块应用程序触发器已存储的过程/函数数据库触发器应用程序中的过程/函数打包的过程/函数DECLAREBEGINEXCEPTIONEND;Developer/2000组件--例如，表单PL/SQL块，与应用程序有关；能自动执行。应用程序触发器Oracle服务器PL/SQL块，与数据库中的表有关；由DML语句触发，自动执行。数据库触发器Oracle服务器和Developer/2000组件--例如，表单有命名的PL/SQL模块，是一组相关的过程、函数和标识符。包Developer/2000组件--例如，表单有命名的PL/SQL块，存储在Developer/2000应用程序中或被库共享；可通过名字被重复调用。应用程序中的过程/函数Oracle服务器有命名的PL/SQL块；存储在Oracle服务器中；能接受参数；可通过名字被重复调用。已存储的过程/函数所有PL/SQL环境未被命名的PL/SQL块；可嵌入到应用程序中亦可交互使用。匿名块适用性描述程序结构TRUE变量类型

P20025-OCT-99Atlanta“Fourscoreandsevenyearsagoourfathersbroughtforthuponthiscontinent,anewnation,conceivedinLIBERTY,anddedicatedtothepropositionthatallmenarecreatedequal.”256120.08PL/SQl数据类型和操作符PL/SQL支持的内置数据类型数据类型引用类型%ROWTYPE标量类型数字字符布尔型日期时间LOB类型BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型复合类型PL/SQl数据类型和操作符指定数值的存储格式BINARY_INTEGER(-2^31=1~2^31-1)INTEGERINTSMALLINTPOSTIVE0~2^31不能为空NATURAL1~2^31不能为空PLS_INTEGERNUMBERDEC(定点数)DECIMAL(定点数)NUMERIC(定点数)FLOAT(浮点数)REAL(浮点数)DOUBLEPRECISION(浮点数)PL/SQl数据类型和操作符字符数据类型包括：CHARVARCHAR2LONGRAWLONGRAWPL/SQL的数据类型与SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONGRAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767PL/SQl数据类型和操作符用于引用数据库列的数据类型，以及表示表中一行的记录类型属性类型有两种：%TYPE-引用变量和数据库列的数据类型%ROWTYPE-提供表示表中一行的记录类型使用属性类型的优点：不需要知道被引用的表列的具体类型如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变bhemp.empno%TYPE;emp_recscott.emp%ROWTYPE;变量的使用

PL/SQL中可以声明变量，继而在SQL中使用它们，也可在任一过程语句的表达式中使用。数据的临时存储在数据流过程执行之前需输入合法的数据，这些数据将被暂时存放在一个或多个变量中。对其值的操作无须访问数据库，通过变量就可进行计算和对其他数据操作。重复使用变量一经声明，就可在应用程序中重复使用。简单地通过引用，其他语句（包括其他的声明语句）即可重复使用这些变量。易于维护PL/SQL变量定义PL/SQL块中可以使用变量和常量在声明部分声明，使用前必须先声明声明时必须指定数据类型，每行声明一个标识符在可执行部分的SQL语句和过程语句中使用声明变量和常量的语法：identifier[CONSTANT]datatype[NOTNULL] [:=|DEFAULTexpr];给变量赋值有两种方法：使用赋值语句:=使用SELECTINTO语句PL/SQL中的变量的操作在声明部分声明并初始化变量。在执行部分为变量指派新值。通过参数把值传递给PL/SQL块。通过输出变量显示结果。作用：变量主要用于程序与数据库之间的数据交换以及保存程序的中间结果。变量的类型PL/SQL变量标量型复合型引用型LOB型(大型的对象)外部变量绑定变量和主变量主要的标量数据类型：VARCHAR2(最大长度)NUMBER[(精度,刻度)]DATECHAR[(最大长度)]LONGLONGRAWBOOLEANBINARY_INTEGERPLS_INTEGER声明PL/SQL变量语法举例identifier[CONSTANT]datatype[NOTNULL] [:=|DEFAULTexpr];Declare v_hiredate DATE; v_deptno NUMBER(2)NOTNULL:=10; v_location VARCHAR2(13):='Atlanta';c_ comm CONSTANTNUMBER:=1400;声明PL/SQL变量规则遵循命名的惯例。可以按习惯命名—例如v_name

代表一个变量和c_name代表一个常变量,g_代表

global变量。初始化NOTNULL型变量。用赋值操作符(:=)或用DEFAULT关键字给变量赋初始值。每行至多声明一个标识符。声明常量时，必须在类型说明符之前标明关键字CONSTANT，并给该常量赋初值。命名规则不同块中的两个变量可以同名。变量的名字（标识符）不能与块中表的列同名。DECLAREempno NUMBER(4);BEGINSELECT empno INTO empno FROM empWHEREename='SMITH';END;变量的赋值v_ename:='Maduro';v_hiredate:='31-DEC-98';语法举例为新员工设定雇佣的日期员工的名字设定为“Maduro”identifier:=expr;变量的初始化和关键字使用:=（赋值操作符）DEFAULT（缺省）NOTNULL（非空）标量类型仅有一个值内部没有分量25-OCT-99Atlanta“Fourscoreandsevenyearsagoourfathersbroughtforthuponthiscontinent,anewnation,conceivedinLIBERTY,anddedicatedtothepropositionthatallmenarecreatedequal.”TRUE256120.08基本标量类型VARCHAR2(最大长度)NUMBER[(精度,刻度)]DATECHAR[(最大长度)]LONGLONGRAWBOOLEANBINARY_INTEGERPLS_INTEGER数据类型描述VARCHAR2(最大长度）变长字符的基本类型，最大的存储数据是32767字节。VARCHAR2型的变量和常量没有默认的长度。NUMBER[(精度,刻度)]整数和浮点数的基本类型。DATE日期和时间的基本类型。DATE类型来存储包含从一天的午夜开始的定长的时间。有效的时间范围是从公元前4712年1月1号开始到公元9999年12月31号。CHAR[(最大长度)]固定长度字符数据的基本类型，最大有32767字节。如果没有指定的最大值，缺省值为1。LONG变长字符串的基本类型，最大长度是32760字节。LONG型的数据库列的最大长度是2147483647字节。LONGRAW二进制数据和字节字符串的基本类型，最大字节为32760。PL/SQL不能解释LONGRAW型的数据。BOOLEAN存储逻辑值——TRUE、FALSE和NULL的基本类型。BINARY_INTEGER介于-2147483647和2147483647之间有符号整数的基本类型。与NUMBER相比，BINARY_INTEGE占有较少的存储空间PLS_INTEGER介于-2147483647和2147483647之间有符号整数的基本类型。与NUMBER和BINARY_INTEGER相比，PLS_INTEGER占有较少的存储空间且运行效率高。标量型变量的声明v_job VARCHAR2(9);v_count BINARY_INTEGER:=0;v_total_sal NUMBER(9,2):=0;v_orderdate DATE:=SYSDATE+7;c_tax_rate CONSTANTNUMBER(3,2):=8.25;v_valid BOOLEANNOTNULL:=TRUE;举例变量常量声明、初始化和赋值DECLAREV_A1NUMBER;V_A2NUMBERNOTNULL:=2000;V_A3CONSTANTNUMBER:=3000;V_A4CONSTANTNUMBERDEFAULT4000;V_A5NUMBER;

BEGINV_A1:=1000;SELECTSALINTOV_A5FROMEMPWHEREEMPNO=7876;INSERTINTOTVALUES(V_A1);INSERTINTOTVALUES(V_A2);INSERTINTOTVALUES(V_A3);INSERTINTOTVALUES(V_A4);INSERTINTOTVALUES(V_A5);END;/变量不可以与块中涉及的列同名变量声明在DECLARE之后;变量声明时如果不赋值，其值为NULL;变量声明时如果设置为NOTNULL，则一定要在声明时使用“:=”或者“DEFAULT”赋初值。变量在程序体中可以用“:=”或者“SELECT…INTO…”赋值常量声明时一定要赋值，在程序中只能引用，不能修改2．使用%type定义变量%type

类型定义的变量是“不知道”的类型变量。这个变量的类型与表中字段的类型是一致。为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle提供了%type定义方法。语法格式如下：<变量名称><变量名|字段名>%TYPE;其中，如果是如下形式：<变量名称><变量名>%TYPE;表明<变量名称>和<变量名>的类型是相同的。如果是如下形式：<变量名称><字段名>%TYPE;表明<变量名称>和<字段名>的类型是相同的。例如：V_NAMEEMP.ENAME%TYPE;但是当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。使用%rowtype定义变量使用%type可以使变量获得字段的数据类型，但是使用%rowtype可以使变量获得整个记录的数据类型。二者在定义上也不同，%rowtype的语法格式是：<变量名称><数据表的名称>%rowtype;%rowtype的属性如下：（1）与%TYPE作用类似，用于定义不确定的类型。（2）变量类型将定义为由数据库的表的字段集合构成的RECORD类型。（3）%rowtype的前缀是数据库的表名，或者另一个已经定义好的RECORD变量。（4）RECORD中的域，与表的字段的名称和数据类型完全相同。%TYPE的属性声明的变量对应于:数据库的列其它先前声明过的变量在%TYPE前缀上:数据库中表和列名先前声明过的变量名优点：利于书写和维护代码,因为不必要因数据库中列的修改而去修改PL/SQL块。能和表中列的数据类型保持一致可以与先前声明过的变量保持一致用%TYPE声明变量举例...v_ename emp.ename%TYPE;v_balance NUMBER(7,2); v_min_balance v_balance%TYPE:=10;... 声明布尔型变量布尔型变量仅有三个值—TRUE、FALSE和NULL.布尔型变量由逻辑运算符—AND、OR和NOT连接.布尔型变量输出三个值—TRUE、FALSE或NULL.算术、字符和日期表达式可以返回一个布尔值。复合类型类型PL/SQL表(tables)PL/SQL记录(records)PL/SQL可变数组(varrays)LOB型变量食谱(CLOB)照片(BLOB)电影(BFILE)NCLOB键盘输入(替换变量)键盘输入是通过在变量名前面加一个“&”符号实现的，如：&V_INPUT。如果此变量是数值型（如：number，integer等），在提示输入时直接输入数据；如果此变量是字符型（如：varchar2()等），有两个办法输入：如果书写时是：&V_INPUT，那么输入时要加单引号“'”把字符串引起来；如果书写时是：'&V_INPUT'，那么输入时不要加单引号，直接输入字符串。外部变量(绑定变量)Server外部变量(BindVariable)外部变量是在某一程序的主环境下声明的一种变量，用来把运行时的值（可以是数字、字符）传入或传出一个或更多PL/SQL程序，在这个程序中可以像其他变量一样使用外部变量。在主环境或一个调用环境（可以是由过程、函数或包的PL/SQL语句调用的）下，声明外部变量，之后就可引用这些外部变量了。外部变量(绑定变量)外部变量是在程序运行的主环境中定义的变量在PL/SQL中引用外部变量需要在外部变量前加”:”在PL/SQL中可以象使用自己的变量一样使用外部变量。外部变量使得PL/SQL可以与主机环境之间传递数据应尽量减少使用外部变量，因为他会影响性能。为了尽量减少对主机的呼叫，可以将变量的值赋给局部变量。引用外部变量SQL>VARIABLEemp_numNUMBER--定义SQL*PLUS中的变量SQL>BEGIN2SELECTCOUNT(*)INTO:emp_num3FROMemp;4END;5/PL/SQL过程已成功完成。SQL>PRINTemp_num--在SQL*PLUS中显示变量EMP_NUM----------13在SQL*PLUS环境中：嵌套块和变量的作用域只要可执行的语句能运行，其所在的块就能被嵌套。一个嵌套块可看作是一个语句。异常处理部分可以包含若干嵌套块。一个对象的作用域是指所涉及到该对象的程序的区域。嵌套块和变量的作用域一个标识符只有在它的作用域中用一个不限定的名字来引用时才是可见的:块可以由内向外查找标识符。(如在嵌套块的父块中的声明部分进行查找)块不可以由外向内查找标识符。(如不会向内在其子块或其兄弟块中查找）附注:可以使用块标签作为前缀限定一个标识符。

嵌套块和变量的作用域...xBINARY_INTEGER;BEGIN...DECLAREyNUMBER;BEGIN...END;...END;

x的范围Y的范围举例决定变量的作用域本课习题...DECLAREV_SAL NUMBER(7,2):=60000;V_COMM NUMBER(7,2):=V_SAL*.20;V_MESSAGE VARCHAR2(255):='eligibleforcommission';BEGIN...DECLAREV_SAL NUMBER(7,2):=50000;V_COMM NUMBER(7,2):=0;V_TOTAL_COMP NUMBER(7,2):=V_SAL+V_COMM;BEGIN...V_MESSAGE:='CLERKnot'||V_MESSAGE;END;V_MESSAGE:='SALESMAN'||V_MESSAGE;END;习题1. 子块中的V_MESSAGE.2. 主块中的V_TOTAL_COMP.3. 子块中的V_COMM.4. 主块中的V_COMM.5. 主块中的V_MESSAGE.PL/SQL中的运算符逻辑运算符算术运算符连接运算符控制运算符次序的括号求幂运算符(**)与SQL中同}PL/SQL中的特殊运算符运