当前位置:Gxlcms > 数据库问题 > oracle数据库之PL/SQL 块结构和组成元素

oracle数据库之PL/SQL 块结构和组成元素

时间:2021-07-01 10:21:17 帮助过:9人阅读

(一)PL/SQL 程序由三个块组成,即声明部分、执行部分、异常处理部分

PL/SQL 块的结构如下:

1、DECLARE

/* 声明部分: 在此声明 PL/SQL 用到的变量,类型及游标,以及局部的存储过程和函数 */

2、BEGIN

/* 执行部分:过程及 SQL 语句 , 即程序的主要部分*

3、EXCEPTION

/* 执行异常部分: 错误处理 */

4、END;

其中 执行部分是必须的。

(二)PL/SQL 块可以分为三类:

1、无名块:动态构造,只能执行一次。

2、子程序:存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。

3、触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。

二 、PL/SQL 结构

1、PL/SQL 块中可以包含子块;

2、子块可以位于 PL/SQL 中的任何部分;

3、 子块也即 PL/SQL 中的一条命令;

三  标识符

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有:

1、标识符名不能超过 30 字符;

2、第一个字符必须为字母;

3、不分大小写;

4、不能用’-‘(减号);

5、不能是 SQL 保留字。

提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.

例如:下面的例子将会删除所有的纪录,而不是 KING 的记录;

DECLARE
Ename varchar2(20) := ’KING’;
BEGIN
DELETE FROM emp WHERE ename=ename;
END;

变量命名在 PL/SQL 中有特别的讲究,建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求,使得整个系统的文档在规范上达到要求。下面是建议的命名方法:

标识符

命名规则

例子

 

 

 

程序变量

V_name

V_name

 

 

 

程序常量

C_Name

C_company_name

 

 

 

游标变量

Name_cursor

Emp_cursor

 

 

 

异常标识

E_name

E_too_many

 

 

 

表类型

Name_table_type

Emp_record_type

 

 

 

Name_table

Emp

 

 

 

记录类型

Name_record

Emp_record

 

 

 

SQL*Plus 替代变量

P_name

P_sal

 

 

 

绑定变量

G_name

G_year_sal

 

 

 

四 PL/SQL 变量类型

在前面的介绍中,有系统的数据类型,也可以自定义数据类型。下表是 ORACLE 类型和 PL/SQL 中的变量类型的合法使用列表:

(一)变量类型

在 ORACLE8i 中可以使用的变量类型有:

1.     字符类型

数据类型

长度

说明

CHAR(n BYTE/CHAR)

默认1字节,n值最大为2000

末尾填充空格以达到指定长度,超过最大长度报错。默认指定长度为字节数,字符长度可以从1字节到四字节。

NCHAR(n)

默认1字符,最大存储内容2000字节

末尾填充空格以达到指定长度,n为Unicode字符数。默认为1字节。

NVARCHAR2(n)

最大长度必须指定,最大存储内容4000字节

变长类型。n为Unicode字符数

VARCHAR2(n BYTE/CHAR)

最大长度必须指定,至少为1字节或者1字符,n值最大为4000

变长类型。超过最大长度报错。默认存储的是长度为0的字符串。

VARCHAR

同VARCHAR2

不建议使用

2.     数字类型

数据类型

长度

说明

NUMBER(p[,s])

1-22字节。

P取值范围1到38

S取值范围-84到127

存储定点数,值的绝对值范围为1.0 x 10 -130至1.0 x 10 126。值大于等于1.0 x 10 126时报错。p为有意义的10进制位数,正值s为小数位数,负值s表示四舍五入到小数点左部多少位。

BINARY_FLOAT

5字节,其中有一长度字节。

32位单精度浮点数类型。

符号位1位,指数位8位,尾数位23位。

BINARY_DOUBLE

9字节,其中有一长度字节。

64位双精度浮点数类型。

 3.  时间、时间间隔类型

时间字段

时间类型有效值

时间间隔类型有效值

YEAR

-4712至9999,包括0

任何整数

MONTH

01至12

0至11

DAY

01至31

任何整数

HOUR

00 至 23

0 至 23

MINUTE

00 至 59

0至 59

SECOND

00 to 59.9(n),9(n)不适用与DATE类型

0 to 59.9(n)

TIMEZONE_HOUR

-1至14,不适用与DATE和TIMESTAMP类型

不可用

TIMEZONE_MINUTE

00至59,不适用与DATE和TIMESTAMP类型

不可用

TIMEZONE_REGION

 

不可用

TIMEZONE_ABBR

 

不可用

 

数据类型

长度

说明

DATE

7字节

默认值为SYSDATE的年、月,日为01。包含一个时间字段,若插入值没有时间字段,则默认值为:00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。没有分秒和时间区。

TIMESTAMP [(fractional_seconds_precision)]

7至11字节

fractional_seconds_precision为Oracle存储秒值小数部分位数,默认为6,可选值为0到9。没有时间区。

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

13字节

使用UTC,包含字段YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_

HOUR, TIMEZONE_MINUTE

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

7至11字节

存时使用数据库时区,取时使用回话的时区。

INTERVAL YEAR [(year_precision)] TO MONTH

5字节

包含年、月的时间间隔类型。year_precision是年字段的数字位数,默认为2,可取0至9。

INTERVAL DAY [(day_precision)]

TO SECOND [(fractional_seconds_precision)]

11字节

day_precision是月份字段的数字位数,默认为2,可取0至9。

1、TO_DATE()、DATE使用的时间字段值都是午夜值。或者使用TRUNC()函数进行过滤,确保时间字段为午夜值。

2、时间和时间间隔类型操作规则:

在DATE和TIMESTAMP(会被转化为DATE类型值)类型上加、减NUMBER类型常量,该常量单位为天数。

所有TIMESTAMP类型运算都以UTC时间为准。即对于TIMESTAMP WITH LOCAL TIME ZONE来说,先转化为UTC时间,计算完成后再转化回来。

3、INTERVAL YEAR TO MONTH常量:

INTERVAL‘year-month’YEAR/MONTH(precision) TO MONTH

year位数超过precision时,返回一个错误。

其中precision为最大的位数,默认为2,可取0到9。

例子:INTERVAL ‘123-2‘ YEAR(3) TO MONTH 、

INTERVAL ‘123‘ YEAR(3) 、

INTERVAL ‘300‘ MONTH(3)。

4、INTERVAL DAY TO SECOND常量:

INTERVAL ‘n/time_expr/n time_expr’ DAY/HOUR/MINUTE(leading_precision)  TO  HOUR/MINUTE/SECOND(fractional_second_precision)

INTERVAL ‘n/time_expr’ SECOND(leading_precision, fractional_second_precision)

time_expr格式:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n] 若n大于分秒精度,则四舍五入n。

只有当第一个字段是DAY时,才可以使用n time_expr。

leading_precision默认为2,可取0至9。

4.     大对象类型

数据类型

长度

说明

BLOB

最大为(4GB-1)*数据库块大小

存储非结构化二进制文件。支持事务处理。

CLOB

最大为(4GB-1)*数据库块大小

存储单字节或者多字节字符数据。支持事务处理。

NCLOB

最大为(4GB-1)*数据库块大小

存储Unicode数据。支持事务处理。

BFILE

最大为2 32-1字节

LOB地址指向文件系统上的一个二进制文件,维护目录和文件名。不参与事务处理。只支持只读操作。

5.     其他类型

数据类型

长度

说明

LONG

最大为2GB

变长类型,存储字符串。创建表时不要使用该类型。

RAW(n)

最大2000字节,n为字节数,必须指定n

变长类型,字符集发生变化时不会改变值。

LONG RAW

最大为2GB

变长类型,不建议使用,建议转化为BLOB类型,字符集发生变化时不会改变值。

ROWID

10字节

代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即被确定且唯一。

UROWID(n)

 

 

(二)复合类型

ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.

1、 记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来,称作 PL/SQL RECORD 的域(FIELD),其作用是存放互不相同但逻辑相关的信息。

定义记录类型语法如下:

TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;

例 1 :

技术分享

提示:

1) DBMS_OUTPUT.PUT_LINE 过程的功能类似于 Java 中的 System.out.println() 直接将输出结果送到标准输出中.

2) 在使用上述过程之前必须将 SQL * PLUS 的环境参数 SERVEROUTPUT 设置为 ON, 否则将看不到输出结果: set serveroutput on

可以用 SELECT 语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。

2、使用%TYPE

定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,这时可以使用%TYPE。

使用%TYPE 特性的优点在于:

1、 所引用的数据库列的数据类型可以不必知道;

2、 所引用的数据库列的数据类型可以实时改变。

例 2:

技术分享

3、使用%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致

使用%ROWTYPE 特性的优点在于:

1、 所引用的数据库中列的个数和数据类型可以不必知道

2、 所引用的数据库中列的个数和数据类型可以实时改变

 例 3:

技术分享

四、 运算符和表达式(数据定义)
(一)关系运算符 技术分享

(二)一般运算符
技术分享

技术分享

(三)逻辑运算符
技术分享

五 变量赋值

在 PL/SQL 编程中,变量赋值是一个值得注意的地方,它的语法如下: variable := expression ;

variable 是一个 PL/SQL 变量, expression 是一个 PL/SQL 表达式.

(一)字符及数字运算特点

空值加数字仍是空值:NULL + < 数字> = NULL

空值加(连接)字符,结果为字符:NULL || <字符串> = < 字符串>

(二)2BOOLEAN 赋值

布尔值只有 TRUE, FALSE 及 NULL 三个值。

(三)数据库赋值

数据库赋值是通过 SELECT语句来完成的,每次执行 SELECT语句就赋值一次,一般要求被赋值的变量与

SELECT中的列名要一一对应。如:

例 9:

DECLARE
emp_id emp.empno%TYPE :=7788;
emp_name emp.ename%TYPE;
wages emp.sal%TYPE;
BEGIN
SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages
FROM emp WHERE empno = emp_id;
DBMS_OUTPUT.PUT_LINE(emp_name||----‘||to_char(wages));
END;

提示:不能将SELECT语句中的列赋值给布尔变量

(四) 可转换的类型赋值

1、 CHAR 转换为 NUMBER:

使用 TO_NUMBER 函数来完成字符到数字的转换,如:

v_total := TO_NUMBER(‘100.0’) + sal;

2、NUMBER 转换为 CHAR:

使用 TO_CHAR 函数可以实现数字到字符的转换,如:

v_comm := TO_CHAR(‘123.45’) || ’元’ ;

3、字符转换为日期:

使用 TO_DATE 函数可以实现 字符到日期的转换,如:

 v_date := TO_DATE(‘2001.07.03‘,‘yyyy.mm.dd‘);

 4、日期转换为字符

使用 TO_CHAR 函数可以实现日期到字符的转换,如:

v_to_day := TO_CHAR(SYSDATE, ‘yyyy.mm.dd hh24:mi:ss‘) ;

 六、 变量作用范围及可见性

在 PL/SQL 编程中,如果在变量的定义上没有做到统一的话,可能会隐藏一些危险的错误,这样的原因主要是变量的作用范围所致。与其它高级语言类似,PL/SQL 的变量作用范围特点是:

1、 变量的作用范围是在你所引用的程序单元(块、子程序、包)内。即从声明变量开始到该块的结束。

2、一个变量(标识)只能在你所引用的块内是可见的。

3、当一个变量超出了作用范围,PL/SQL 引擎就释放用来存放该变量的空间(因为它可能不用了)。

4、在子块中重新定义该变量后,它的作用仅在该块内。

七、 注释

在PL/SQL里,可以使用两种符号来写注释,即:

1、使用双 ‘-‘ ( 减号) 加注释

PL/SQL允许用 – 来写注释,它的作用范围是只能在一行有效。如:

V_Sal NUMBER(12,2); -- 工资变量。

2、使用 /*   */  来加一行或多行注释,如:

/***********************************************/

/* 文件名: department_salary.sql */ /***********************************************/

提示:被解释存放在数据库中的 PL/SQL 程序,一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留;另外程序中的空行也自动被去掉。

八、 简单例子

(一) 简单数据插入例子

例 11:

/* 本例子仅是一个简单的插入,不是实际应用。 */

DECLARE
v_ename VARCHAR2(20) := ‘Bill’;
v_sal NUMBER(7,2) :=1234.56;
v_deptno NUMBER(2) := 10;
v_empno NUMBER(4) := 8888;
BEGIN
INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )
VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,
TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );
COMMIT;
END;

(二) 简单数据删除例子

例 12:

/* 本例子仅是一个简单的删除例子,不是实际应用。 */

DECLARE
v_empno number(4) := 8888;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
END;

oracle数据库之PL/SQL 块结构和组成元素

标签:cell   大于等于   als   record   字段   manager   clock   one   oracle数据库   

人气教程排行