ORACLEPLSQL编程详解全8篇.docx
- 文档编号:7497683
- 上传时间:2023-01-24
- 格式:DOCX
- 页数:138
- 大小:89.52KB
ORACLEPLSQL编程详解全8篇.docx
《ORACLEPLSQL编程详解全8篇.docx》由会员分享,可在线阅读,更多相关《ORACLEPLSQL编程详解全8篇.docx(138页珍藏版)》请在冰豆网上搜索。
ORACLEPLSQL编程详解全8篇
ORACLEPL/SQL编程详解
第一章:
PL/SQL 程序设计简介
SQL语言只是访问、操作数据库的语言,并不是一种具有流程控制的程序设计语言,而只有程序设计语言才能用于应用软件的开发。
PL/SQL是一种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE数据库进行访问。
由于该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理。
除此之外,可以在ORACLE数据库的某些客户端工具中,使用PL/SQL语言也是该语言的一个特点。
本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点,以及了解PL/SQL语言的重要性和数据库版本问题。
还要介绍一些贯穿全书的更详细的高级概念,并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
1.1SQL与PL/SQL
1.1.1什么是PL/SQL?
PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。
ORACLE的SQL是支持ANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)标准的产品。
PL/SQL是对SQL语言存储过程语言的扩展。
从ORACLE6以后,ORACLE的RDBMS附带了PL/SQL。
它现在已经成为一种过程处理语言,简称PL/SQL。
目前的PL/SQL包括两部分,一部分是数据库引擎部分;另一部分是可嵌入到许多产品(如C语言,JAVA语言等)工具中的独立引擎。
可以将这两部分称为:
数据库PL/SQL和工具PL/SQL。
两者的编程非常相似。
都具有编程结构、语法和逻辑机制。
工具PL/SQL另外还增加了用于支持工具(如ORACLEForms)的句法,如:
在窗体上设置按钮等。
本章主要介绍数据库PL/SQL内容。
1.2PL/SQL的优点或特征
1.2.1有利于客户/服务器环境应用的运行
对于客户/服务器环境来说,真正的瓶颈是网络上。
无论网络多快,只要客户端与服务器进行大量的数据交换。
应用运行的效率自然就回受到影响。
如果使用PL/SQL进行编程,将这种具有大量数据处理的应用放在服务器端来执行。
自然就省去了数据在网上的传输时间。
1.2.2适合于客户环境
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。
对于客户端来说,PL/SQL可以嵌套到相应的工具中,客户端程序可以执行本地包含PL/SQL部分,也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
1.2.3过程化
PL/SQL是Oracle在标准SQL上的过程性扩展,不仅允许在PL/SQL程序内嵌入SQL语句,而且允许使用各种类型的条件分支语句和循环语句,可以多个应用程序之间共享其解决方案。
1.2.4模块化
PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构,被分成单独的过程、函数、触发器,且可以把它们组合为程序包,提高程序的模块化能力。
1.2.5运行错误的可处理性
使用PL/SQL提供的异常处理(EXCEPTION),开发人员可集中处理各种ORACLE错误和PL/SQL错误,或处理系统错误与自定义错误,以增强应用程序的健壮性。
1.2.6提供大量内置程序包
ORACLE提供了大量的内置程序包。
通过这些程序包能够实现DBS的一些低层操作、高级功能,不论对DBA还是应用开发人员都具有重要作用。
当然还有其它的一些优点如:
更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。
1.3PL/SQL可用的SQL语句
PL/SQL是ORACLE系统的核心语言,现在ORACLE的许多部件都是由PL/SQL写成。
在PL/SQL中可以使用的SQL语句有:
INSERT,UPDATE,DELETE,SELECTINTO,COMMIT,ROLLBACK,SAVEPOINT。
提示:
在PL/SQL中只能用SQL语句中的DML部分,不能用DDL部分,如果要在PL/SQL中使用DDL(如CREATEtable等)的话,只能以动态的方式来使用。
ORACLE的PL/SQL组件在对PL/SQL程序进行解释时,同时对在其所使用的表名、列名及数据类型进行检查。
PL/SQL可以在SQL*PLUS中使用。
PL/SQL可以在高级语言中使用。
PL/SQL可以在ORACLE的开发工具中使用(如:
SQLDeveloper或ProcedureBuilder等)。
其它开发工具也可以调用PL/SQL编写的过程和函数,如PowerBuilder等都可以调用服务器端的PL/SQL过程。
1.4运行PL/SQL程序
PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。
这个引擎可能在ORACLE的服务器端,也可能在ORACLE应用开发的客户端。
引擎执行PL/SQL中的过程性语句,然后将SQL语句发送给数据库服务器来执行。
再将结果返回给执行端。
第二章:
PL/SQL块结构和组成元素
2.1 PL/SQL块
PL/SQL程序由三个块组成,即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下:
DECLARE
--声明部分:
在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数
BEGIN
-- 执行部分:
过程及SQL 语句 , 即程序的主要部分
EXCEPTION
-- 执行异常部分:
错误处理
END;
其中:
执行部分不能省略。
PL/SQL块可以分为三类:
1.无名块或匿名块(anonymous):
动态构造,只能执行一次,可调用其它程序,但不能被其它程序调用。
2.命名块(named):
是带有名称的匿名块,这个名称就是标签。
3.子程序(subprogram):
存储在数据库中的存储过程、函数等。
当在数据库上建立好后可以在其它程序中调用它们。
4. 触发器 (Trigger):
当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。
5.程序包(package):
存储在数据库中的一组子程序、变量定义。
在包中的子程序可以被其它程序包或子程序调用。
但如果声明的是局部子程序,则只能在定义该局部子程序的块中调用该局部子程序。
2.2 PL/SQL结构
PL/SQL块中可以包含子块;
子块可以位于 PL/SQL中的任何部分;
子块也即PL/SQL中的一条命令;
2.3 标识符
PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。
要求和限制有:
1.标识符名不能超过30字符;
2.第一个字符必须为字母;
3.不分大小写;
4.不能用’-‘(减号);
5.不能是SQL保留字。
提示:
一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
例如:
下面的例子将会删除所有的纪录,而不是’EricHu’的记录;
DECLARE
ename varchar2(20) :
='EricHu';
BEGIN
DELETE FROM scott.emp WHERE ename=ename;
END;
变量命名在PL/SQL中有特别的讲究,建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求,使得整个系统的文档在规范上达到要求。
下面是建议的命名方法:
标识符
命名规则
例子
程序变量
V_name
V_name
程序常量
C_Name
C_company_name
游标变量
Cursor_Name
Cursor_Emp
异常标识
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
2.4 PL/SQL 变量类型
在前面的介绍中,有系统的数据类型,也可以自定义数据类型。
下表给出ORACLE类型和PL/SQL中的变量类型的合法使用列表:
2.4.1 变量类型
在ORACLE8i中可以使用的变量类型有:
类型
子类
说 明
范 围
ORACLE限制
CHAR
Character
String
Rowid
Nchar
定长字符串
民族语言字符集
032767
可选,确省=1
2000
VARCHAR2
Varchar,String
NVARCHAR2
可变字符串
民族语言字符集
032767
4000
4000
BINARY_INTEGER
带符号整数,为整数计算优化性能
NUMBER(p,s)
Dec
Doubleprecision
Integer
Int
Numeric
Real
Smallint
小数,NUMBER 的子类型
高精度实数
整数,NUMBER 的子类型
整数,NUMBER 的子类型
与NUMBER等价
与NUMBER等价
整数, 比 integer 小
LONG
变长字符串
0->2147483647
32,767字节
DATE
日期型
公元前4712年1月1日至公元后4712年12月31日
BOOLEAN
布尔型
TRUE,FALSE,NULL
不使用
ROWID
存放数据库行号
UROWID
通用行标识符,字符类型
例1.插入一条记录并显示;
DECLARE
Row_id ROWID;
info VARCHAR2(40);
BEGIN
INSERT INTO scott.dept VALUES (90, '财务室', '海口')
RETURNING rowid, dname||':
'||to_char(deptno)||':
'||loc
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:
'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
其中:
RETURNING子句用于检索INSERT语句中所影响的数据行数,当INSERT语句使用VALUES子句插入数据时,RETURNING字句还可将列表达式、ROWID和REF值返回到输出变量中。
在使用RETURNING子句是应注意以下几点限制:
1.不能与DML语句和远程对象一起使用;
2.不能检索LONG类型信息;
3.当通过视图向基表中插入数据时,只能与单基表视图一起使用。
例2.修改一条记录并显示
DECLARE
Row_id ROWID;
info VARCHAR2(40);
BEGIN
UPDATE dept SET deptno=100 WHERE DNAME='财务室'
RETURNING rowid, dname||':
'||to_char(deptno)||':
'||loc
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:
'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
其中:
RETURNING子句用于检索被修改行的信息。
当UPDATE语句修改单行数据时,RETURNING 子句可以检索被修改行的ROWID和REF值,以及行中被修改列的列表达式,并可将他们存储到PL/SQL变量或复合变量中;当UPDATE语句修改多行数据时,RETURNING 子句可以将被修改行的ROWID和REF值,以及列表达式值返回到复合变量数组中。
在UPDATE中使用RETURNING 子句的限制与INSERT语句中对RETURNING子句的限制相同。
例3. 删除一条记录并显示
DECLARE
Row_id ROWID;
info VARCHAR2(40);
BEGIN
DELETE dept WHERE DNAME='办公室'
RETURNING rowid, dname||':
'||to_char(deptno)||':
'||loc
INTO row_id, info;
DBMS_OUTPUT.PUT_LINE('ROWID:
'||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
其中:
RETURNING子句用于检索被删除行的信息:
当DELETE语句删除单行数据时,RETURNING子句可以检索被删除行的ROWID和REF值,以及被删除列的列表达式,并可将他们存储到PL/SQL变量或复合变量中;当DELETE语句删除多行数据时,RETURNING子句可以将被删除行的ROWID和REF值,以及列表达式值返回到复合变量数组中。
在DELETE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。
2.4.2复合类型
ORACLE在PL/SQL中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.
2.4.2.1记录类型
记录类型类似于C语言中的结构数据类型,它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来,它必须包括至少一个标量型或RECORD数据类型的成员,称作PL/SQLRECORD的域(FIELD),其作用是存放互不相同但逻辑相关的信息。
在使用记录数据类型变量时,需要先在声明部分先定义记录的组成、记录的变量,然后在执行部分引用该记录变量本身或其中的成员。
定义记录类型语法如下:
TYPE record_name IS RECORD(
v1 data_type1 [NOT NULL] [:
= default_value ],
v2 data_type2 [NOT NULL] [:
= default_value ],
......
vn data_typen [NOT NULL] [:
= default_value ] );
例4:
DECLARE
TYPE test_rec IS RECORD(
Name VARCHAR2(30) NOT NULL :
= '胡勇',
Info VARCHAR2(100));
rec_book test_rec;
BEGIN
rec_book.Name :
='胡勇';
rec_book.Info :
='谈PL/SQL编程;';
DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info);
END;
可以用 SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。
例5:
DECLARE
--定义与hr.employees表中的这几个列相同的记录数据类型
TYPE RECORD_TYPE_EMPLOYEES IS RECORD(
f_name hr.employees.first_name%TYPE,
h_date hr.employees.hire_date%TYPE,
j_id hr.employees.job_id%TYPE);
--声明一个该记录数据类型的记录变量
v_emp_record RECORD_TYPE_EMPLOYEES;
BEGIN
SELECT first_name, hire_date, job_id INTO v_emp_record
FROM employees
WHERE employee_id = &emp_id;
DBMS_OUTPUT.PUT_LINE('雇员名称:
'||v_emp_record.f_name
||' 雇佣日期:
'||v_emp_record.h_date
||' 岗位:
'||v_emp_record.j_id);
END;
一个记录类型的变量只能保存从数据库中查询出的一行记录,若查询出了多行记录,就会出现错误。
2.4.2.2 数组类型
数据是具有相同数据类型的一组成员的集合。
每个成员都有一个唯一的下标,它取决于成员在数组中的位置。
在PL/SQL中,数组数据类型是VARRAY。
定义VARRY数据类型语法如下:
例6:
DECLARE
--定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型
TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);
--声明一个该VARRAY数据类型的变量
v_reg_varray REG_VARRAY_TYPE;
BEGIN
--用构造函数语法赋予初值
v_reg_varray :
= reg_varray_type
('中国', '美国', '英国', '日本', '法国');
DBMS_OUTPUT.PUT_LINE('地区名称:
'||v_reg_varray
(1)||'、'
||v_reg_varray
(2)||'、'
||v_reg_varray(3)||'、'
||v_reg_varray(4));
DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值:
'||v_reg_varray(5));
--用构造函数语法赋予初值后就可以这样对成员赋值
v_reg_varray(5) :
= '法国';
DBMS_OUTPUT.PUT_LINE('第5个成员的值:
'||v_reg_varray(5));
END;
2.4.2.3 使用%TYPE
定义一个变量,其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致,这时可以使用%TYPE。
使用%TYPE特性的优点在于:
所引用的数据库列的数据类型可以不必知道;
所引用的数据库列的数据类型可以实时改变,容易保持一致,也不用修改PL/SQL程序。
例7:
DECLARE
-- 用%TYPE 类型定义与表相配的字段
TYPE T_Record IS RECORD(
T_no emp.empno%TYPE,
T_name emp.ename%TYPE,
T_sal emp.sal%TYPE );
-- 声明接收数据的变量
v_emp T_Record;
BEGIN
SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788;
DBMS_OUTPUT.PUT_LINE
(TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||' ' || TO_CHAR(v_emp.t_sal));
END;
例8:
DECLARE
v_empno emp.empno%TYPE :
=&no;
Type t_record is record (
v_name emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_date emp.hiredate%TYPE);
Rec t_record;
BEGIN
SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);
END;
2.4.3 使用%ROWTYPE
PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于:
所引用的数据库中列的个数和数据类型可以不必知道;
所引用的数据库中列的个数和数据类型可以实时改变,容易保持一致,也不用修改PL/SQL程序。
例9:
DECLARE
v_empno emp.empno%TYPE :
=&no;
rec emp%ROWTYPE;
BEGIN
SELECT * INTO rec FROM emp WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('姓名:
'||rec.ename||'工资:
'||rec.sal||'工作时间:
'||rec.hiredate);
END;
2.4.4 LOB类型
ORACLE提供了LOB(LargeOBject)类型,用于存储大的数据对象的类型。
ORACLE目前主要支持BFILE,BLOB,CLOB 及 NCLOB 类型。
BFILE(Movie)
存放大的二进制数据对象,这些数据文件不放在数据库里,而是放在操作系统的某个目录里,数据库的表里只存放文件的目录。
BLOB(Photo)
存储大的二进制数据类型。
变量存储大的二进制对象的位置。
大二进制对象的大小<=4GB。
CLOB(Book)
存储大的字符数据类型。
每个变量存储大字符对象的位置,该位置指到大字符数据块。
大字符对象的大小<=4GB。
NCLOB
存储大的NCHAR字符数据类型。
每个变量存储大字符对象的位置,该位置指到大字符数据块。
大字符对象的大小<=4GB。
2.4.5 BIND 变量
绑定变量是在主机环境中定义的变量。
在PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。
为了在PL/SQL 环境中声明绑定变量,使用命令VARIABLE。
例如:
VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(20)
可以通过SQL*Plus命令中的PRINT 显示绑定变量的值。
例如:
PRINT return_code
PRINT return_msg
例10:
VARIABLE result NUMBER;
BEGIN
SELECT (sal*10)+nvl(comm, 0) INTO :
result FROM emp
WHERE empno=7844;
END;
--然后再执行
PRINT result
2.4.6 PL/SQL 表(TABLE)
定义记录表(或索引表)数据类型。
它与记录类型相似,但它是对记录类型的扩展。
它可以处理多行记录,类似于高级中的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLEPLSQL 编程 详解