PL/SQL基础

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Kalen_Chen/article/details/1663143

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;             /*块结束标记*/

注意,DECLAREBEGINEXCEPTION后面没有分号(;),而END后则必须要带分号(;)

 

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

set serveroutput on

BEGIN

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

END;

 

输出结果:

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

 

其中dbms_outputOracle所提供的系统包,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
过程已成功完成。

 

其中&noSQL*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         触发器:是指隐含执行的存储过程。当定义触发器时,必须要指定触发事件以及触发操作,常用的触发事件包括INSERTUPDATEDELETE语句,而触发操作实际是一个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)LONGLONGRAW

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

(7)BOOLEAN

该数据类型用于定义布尔变量,其变量的值为TRUEFALSENULL

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

(8)BINARY_INTEGER

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

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

(9)BINARY_FLOATBINARY_DOUBLE

BINARY_FLOATBINARY_DOUBLEOracle 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_enamev_salEMP表的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_typePL/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_typePL/SQL表类型;emp.ename%TYPE指定了PL/SQL表元素的数据类型和长度;ename_tablePL/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
过程已成功完成。

 

C1REF 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变量是指用于存储大批量数据的变量。OracleLOB分为两种:

l         内部LOB:包括CLOBBLOBNCLOB三种类型,它们的数据被存储在数据库中,并支持事务操作(提交、回退、保存点)

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

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

 

2.5PL/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~Za~z)开始,并且最大长度为30个字符。如果以其他字符开始,那么必须要使用双引号引住。

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

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

 

3.       文本

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

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

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

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

l         布尔文本:是指BOOLEAN值,它主要用在条件表达式中,布尔文本有三种值:TRUEFALSENULL

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_salv_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关键字采用大写格式,例如SELECTUPDATESETWHERE

l     PL/SQL关键字采用大写格式,例如DECLAREBEGINEND

l     数据类型采用大写格式,例如INTVARCHAR2DATE

l     标识符和参数采用小写格式,例如v_salc_rate

l     数据库对象和列采用小写格式,例如empsalename

 

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函数包括GREATESTLEASTDECODE以及所有分组函数(例如SUM)

 
展开阅读全文

没有更多推荐了,返回首页