第一章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是Procedure Language & Structured Query Language 的缩写。ORACLE 的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库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，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。提示：在PL/SQL中只能用SQL语句中的DML 部分，不能用DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。
● ORACLE 的PL/SQL 组件在对
PL/SQL 程序进行解释时，同时
对在其所使用的表名、列名及数
据类型进行检查。
● PL/SQL 可以在SQL*PLUS 中
使用。
● PL/SQL 可以在高级语言中使
用。
● PL/SQL可以在ORACLE的开发
工具中使用(如：SQL Developer
或Procedure Builder等)。
● 其它开发工具也可以调用
PL/SQL编写的过程和函数，如
Power Builder 等都可以调用服
务器端的PL/SQL过程。
1.4运行PL/SQL程序
PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将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 的标识符定义的要求相同。要求和限制有：
●标识符名不能超过30字符；
●第一个字符必须为字母；
●不分大小写；
●不能用?-…(减号);
●不能是SQL保留字。
提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
例如：下面的例子将会删除所有的纪录，而不是?EricHu?的记录；
DECLARE
ename varchar2(20) :='EricHu';
BEGIN
DELETE FROM scott.emp WHERE ename=ename;
END;
复制代码
变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：
2.4 PL/SQL 变量类型
在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表给出ORACLE类型和PL/SQL中的变量类型的合法使用列表：
2.4.1 变量类型
在ORACLE8i中可以使用的变量类型有：
例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=100WHERE 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/SQL RECORD 的域(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_https://www.doczj.com/doc/7817924943.html, :='胡勇';
rec_https://www.doczj.com/doc/7817924943.html, :='谈PL/SQL编程;';
DBMS_OUTPUT.PUT_LINE(rec_https://www.doczj.com/doc/7817924943.html,||' '||rec_https://www.doczj.com/doc/7817924943.html,);
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数据类型语法如下：
TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];
varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。
例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 (Large OBject)类型，用于存储大的数据对象的类型。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)
定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。
定义记录表类型的语法如下：
TYPE table_name IS TABLE OF element_type [NOT NULL]
INDEX BY[BINARY_INTEGER | PLS_INTEGER | VARRAY2];
复制代码
关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。
例11：
DECLARE
TYPE dept_table_type IS TABLE OF
dept%ROWTYPE INDEX BY BINARY_INTEGER;
my_dname_table dept_table_type;
v_count number(2) :=4;
BEGIN
FOR int IN1 .. v_count LOOP
SELECT*INTO my_dname_table(int) FROM dept WHERE deptno=int*10;
END LOOP;
FOR int IN my_dname_table.FIRST .. my_dname_https://www.doczj.com/doc/7817924943.html,ST LOOP
DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno); DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);
END LOOP;
END;
复制代码
例12：按一维数组使用记录表
DECLARE
--定义记录表数据类型
TYPE reg_table_type IS TABLE OF varchar2(25)
INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
v_reg_table REG_TABLE_TYPE;
BEGIN
v_reg_table(1) :='Europe';
v_reg_table(2) :='Americas';
v_reg_table(3) :='Asia';
v_reg_table(4) :='Middle East and Africa';
v_reg_table(5) :='NULL';
DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、'
||v_reg_table (2)||'、'
||v_reg_table (3)||'、'
||v_reg_table (4));
DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5));
END;
复制代码
例13：按二维数组使用记录表
DECLARE
--定义记录表数据类型
TYPE emp_table_type IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
v_emp_table EMP_TABLE_TYPE;
BEGIN
SELECT first_name, hire_date, job_id INTO
v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id FROM employees WHERE employee_id =177;
SELECT first_name, hire_date, job_id INTO
v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id FROM employees WHERE employee_id =178;
DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name
||' 雇佣日期：'||v_emp_table(1).hire_date
||' 岗位：'||v_emp_table(1).job_id);
DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name
||' 雇佣日期：'||v_emp_table(2).hire_date
||' 岗位：'||v_emp_table(2).job_id);
END;
复制代码
2.5 运算符和表达式(数据定义)
2.5.1 关系运算符
2.5.2 一般运算符
2.5.3 逻辑运算符
2.6 变量赋值
在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：variable := expression ;
variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.
2.6.1 字符及数字运算特点
空值加数字仍是空值：NULL + < 数字> = NULL
空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串> 2.6.2 BOOLEAN 赋值
布尔值只有TRUE, FALSE及NULL 三个值。如：
DECLARE
bDone BOOLEAN;
BEGIN
bDone := FALSE;
WHILE NOT bDone LOOP
Null;
END LOOP;
END;
复制代码
2.6.3 数据库赋值
数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：
例14：
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语句中的列赋值给布尔变量。
2.6.4 可转换的类型赋值
CHAR 转换为NUMBER：
使用TO_NUMBER 函数来完成字符
到数字的转换，如：
PL/SQL:是过程化/sql的缩写，是Oracle在sql的基础之上扩展来的一种数据库编程语言，在兼容sql的基础之上，扩充了许多新功能，是面向过程化语言和sql的结合。 PL/SQL特点： ·除了基本的sql之外，包含了控制结构和异常处理，从而具有了sql语句的简洁性和过程性。 ·每个sql语句的请求处理，都会引起一次网络的传输，用户量一旦大量增加，可能会导致网络堵塞，而PLSQL是将整个语句块发送给数据库服务器，减少了网络通信的数量，提高了应用程序的运行效率。 ·PLSQL语句是存储在Oracle服务器上，可以被其他的PLSQL程序或者sql调用。具有很高的重用性。 ·PLSQL程序语言是一种块结构语言，PLSQL的基本代为是块。 PLSQL程序块有两种： ·匿名块：没有名称，只用一次，不能存储在数据库中 ·命名块：指的是保存在数据库中的PLSQL程序块，比如我们的存储过程、函数和触发器 块的基本结构： ·匿名块的基本结果
declare：声明部分，用来声明程序中的变量、类型、游标等等。 begin：是PLSQL程序的主体部分，一般使用sql语句或者过程化语言来处理特定的操作。 exception：异常的处理部分，用来检查和处理异常或者错误。 块中的每一条语句都需要以分号结束，一条sql可以分成多行来写，但是最终只需要一个分号。 注释：在PLSQL中，注释分为两种，一种单行注释“--”，两外就是多行注释”/* 需要注释的内容*/” 范例：向表中插入一笔数据并且提交。
声明常量和变量 ·声明常量：常量名constant 类型(长度) := 值 constant：表示的就是固定不变的，就是我们说的常量。声明之后不允许改变。 ·声明变量：变量名类型(长度) （not null）（default | := ） 声明变量和常量中一些规则： ·一行只能声明一个变量或者常量 ·变量时存储值的内存区域，用来处理程序中的值，变量名称不能重复，也不能使用系统关键字。 ·变量名称以字母开头（不区分大小写）,可以由字母，数字，下划线组成。 ·变量名称的长度不能超过30个字符
1.PL/SQL综述 本章学习目标，了解如下内容： PL/SQL的功能和作用 PL/SQL 的优点和特征； Oracle 10g、Oracle9i 的PL/SQL新特征 1.1.SQL简介 1.1.1.SQL语言特点 SQL语言采用集合操作方式 1.1. 2.SQL语言分类 ●数据查询语言（SELECT语句）：检索数据库数据。 ●数据操纵语言（DML）：用于改变数据库数据。包括insert，update和delete三条语句。 ●事务控制语言（TCL）：用于维护数据库的一致性，包括commit，rollback和savepoint 三 条语句 ●数据定义语言（DDL）：用户建立、修改和删除数据库对象。 ●数据控制语言（DDL）：用于执行权限授予和收回操作。包括grant 和revoke两条命令。 1.1.3.SQL 语句编写规则 ●SQL关键字不区分大小写 ●对象名和列名不区分大小写 ●字符值和日期值区分大小写 ●书写格式随意 1.2.PL/SQL简介 1.3.Oracle 10G PL/SQL 新特征 2.PL/SQL开发工具 本章学习目标： 学会使用SQL*PLUS 学会使用PL/SQL developer； 学会使用Procedure Builder。 2.1.SQL*PLUS 在命令行运行SQL*Plus
Sqlplus [username]/[password] [@server] 3.PL/SQL 基础 学习目标： ●了解PL/SQL块的基本结构以及PL/SQL块的分类； ●学会在PL/SQL块中定义和使用变量 ●学会在PL/SQL块中编写可执行语句； ●了解编写PL/SQL代码的指导方针； ●了解Oracle 10g的新特征——新数据类型BINARY_FLOAT 和 BINARY_DOUBLE，以及指定字符串文本的新方法。 3.1.PL/SQL 块简介 3.1.1.PL/SQL块结构 3.1.2.PL/SQL 块分类 匿名块 命名块 子程序 触发器 3.2. 定义并使用变量 3.2.1.标量变量 3.2.2.复合变量 3.2.3.参照变量 3.2. 4.LOB 变量 3.2.5.非PL/SQL 变量
Chap1 DML语句是select 、insert、update、delete和merge DDL语句是create、alter、drop、rename、truncate、comment DCL语句是grant、revoke TCL语句是commit、rollback和savepoint sql16个基本命令——参考书《OCA认证考试指南（IZ0-051）》清华大学出版社 《oracle database sql language reference 11g》有非遵循格式字符串依赖于格式掩码 chap2 2.1.3 关于语句中有多个单引号时处理： 1、 select'It''s a bird,no plan can''t be 'as pharse from dual; 此处两个单引号即为一个单引号 2、只能用q 再加’(语句)’ select q'(It's a bird,no plan can't be)'as pharse from dual; 均输出 PHARSE ---------------------------- It's a bird,no plan can't be 2.1.4 定义变量与申明变量的区别： 定义变量即为变量分配名称并指定数据类型；申明变量首先需要定义变量，然后为其赋值。(赋值也称为初始化) 替代变量前面要加&前缀且若替代变量为字符型时要加两个单引号如’&a’ declare lv_whom varchar2(20);/*lv-whom为申明变量，a为替代变量，a没有变量类型*/ begin lv_whom := '&a'; end; 或者 declare lv_whom varchar2(20); begin lv_whom := &a; end;但是要在输入框中字符加两个单引号 替代变量用define申明，且定义时不可以指定类型，默认为char型 ①Define x=emp; Select * from &x; /*调用要用&，此处不加单引号，解析后即为emp表*/
实验6 PL/SQL程序设计 1 实验目的 (1)掌握PL/SQL程序开发方法。 (2)掌握存储过程、函数、触发器、包的创建于调用。 2 实验要求 (1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。 (2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。 3 实验步骤 以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。 (1)创建一个存储过程，输出不同类型图书的数量、平均价格。
SQL> create or replace procedure proc_category_static 2 as 3 --定义游标，获取当前有哪些图书种类 4 cursor c_all_category is select distinct category from books; 5 --图书的平均价格 6 v_avg_cost number; 7 begin 8 --保存图书种类 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where category= group by category; 11 ('种类为：'||||',平均价格为：'|| v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 /
(2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。create or replace procedure proc_get_orderinfo( 2 p_customer_id %type) 3 as 4 --声明游标存储客户的订单号 5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id; 6 v_orderid %type; 7 --声明游标存储订单信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;
淮海工学院计算机工程学院实验报告书 课程名：《大型数据库概论》 题目：PL/SQL编程基础 班级：********* 学号：*********** 姓名：**********
实验2 PL/SQL编程基础 实验目的 1.掌握PL/SQL基础，包括PL/SQL语句块、变量、常用数据类型、运算符和表达式。 2. 掌握PL/SQL控制结构，包括顺序、选择、NULL和循环四种结构及GOTO语句。 3. 掌握PL/SQL中游标的使用方法。 4. 掌握SQL *Plus和Worksheet开发环境的使用方法。 实验环境 局域网，windows 2003 实验学时 2学时，必做实验。 实验内容 1. 编写一个PL/SQL程序块，计算100以内的奇数和。 2. 编写PL/SQL块计算并输出S=1!+2!+…+10!。 3．编写一个PL/SQL块用于计算两个正整数的最大公约数。 4．编写一个PL/SQL程序块，使用游标对一个数据库表中的数据进行查询。 实验步骤 1、选择“开始”→“所有程序”→Oracle-OraHome92→Application Development→SQL Plus 2、系统出现“登录”对话框。输入用户名为“scott”、口令为“tiger”、主机字符串 为“school”。 3、单击“确定”按钮，出现“SQL *Plus的工作窗口”。 4、编写PL/SQL程序。 4.1、编写一个PL/SQL程序块，计算100以内的奇数和。 set serveroutput on; declare p integer; m integer; n integer; begin m:=2; n:=0; for p in 1..100 loop if p mod m<>0 then n:=n+p; end if; end loop;
课程：Oracle 同组者：实验日期：2017.5.16 实验6 PL/SQL程序设计 1 实验目的 (1)掌握PL/SQL程序开发方法。 (2)掌握存储过程、函数、触发器、包的创建于调用。 2 实验要求 (1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。 (2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。 3 实验步骤 以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。 (1)创建一个存储过程，输出不同类型图书的数量、平均价格。 SQL> create or replace procedure proc_category_static 2 as 3 --定义游标，获取当前有哪些图书种类 4 cursor c_all_category is select distinct category from books; 5 --图书的平均价格 6 v_avg_cost number; 7 begin 8 --保存图书种类 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where
课程：Oracle 同组者：实验日期：2017.5.16 category=v_each_category.category group by category; 11 dbms_output.put_line('种类为：'||v_each_category.category||',平均价格为：'|| v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 / (2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。create or replace procedure proc_get_orderinfo( 2 p_customer_id customers.customer_id%type) 3 as 4 --声明游标存储客户的订单号 5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id; 6 v_orderid orders.order_id%type; 7 --声明游标存储订单信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; 9 --保存图书的书名 10 v_title books.title%type; 11 12 begin 13 open c_orderid; 14 LOOP 15 fetch c_orderid into v_orderid;
实验三PL/SQL编程 一、实验目的及要求 (1) 掌握PL/SQL块结构、PL/SQL的基本语法、PL/SQL的控制结构。 (2) 掌握PL/SQL块中使用复合数据类型和游标的方法。 (3) 掌握PL/SQL异常处理技术。 二、实验主要内容 (1) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。 (2) 记录利用SQL*Plus或iSQL*Plus编写、执行PL/SQL程序的命令。 (3) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。 三、实验仪器设备 在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。 四、实验步骤 1．在SQL*Plus中使用PL/SQL块处理EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000： 2．declare 3．num_sal emp.sal%type; 4．begin 5．select sal into num_sal from emp where empno=7788; 查询EMP表中职工号7788的工资 6．if(num_sal<3000) then 判断工资是否小于3000 7．update emp set sal=3000 where empno=7788; 把小于3000的工资改为3000 8．end if; 9．dbms_output.put_line('更新'||sql%rowcount||'条记录'); 10．dbms_output.put_line('工资是:'||num_sal); 11．end; 12． 2．编写一个程序块，将emp表中前5人的名字显示出来。 declare 创建一个记录类型v_record，类型包含name，salary，job，deptno分量 type v_record is record( name emp.ename%type, salary emp.sal%type, job emp.job%type, deptno emp.deptno%type 记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现） ); t_record v_record;
PL/SQL程序设计
目录 第一章PL/SQL 程序设计简介 (4) §1.2SQL与PL/SQL (4) §1.2.1 什么是PL/SQL? (4) §1.2.1 PL/SQL的好处 (4) §1.2.2 PL/SQL 可用的SQL语句 (5) §1.3运行PL/SQL程序 (5) 第二章PL/SQL块结构和组成元素 (6) §2.1PL/SQL块 (6) §2.2PL/SQL结构 (6) §2.3标识符 (6) §2.4PL/SQL变量类型 (7) §2.4.1 变量类型 (7) §2.4.2 复合类型 (9) §2.4.3 使用%ROWTYPE (11) §2.4.4 LOB类型* (11) §2.4.5 BIND 变量 (12) §2.4.6 INDEX BY TABLES (12) §2.4.7 数据类型的转换* (13) §2.5运算符和表达式(数据定义) (14) §2.5.1 关系运算符 (14) §2.5.2 一般运算符 (14) §2.5.3 逻辑运算符 (14) §2.6变量赋值 (14) §2.6.1 字符及数字运算特点 (15) §2.6.2 BOOLEAN 赋值 (15) §2.6.3 数据库赋值 (15) §2.6.4 可转换的类型赋值 (15) §2.7变量作用范围及可见性 (16) §2.8注释 (17) §2.9简单例子 (17) §2.9.1 简单数据插入例子 (17) §2.9.2 简单数据删除例子 (18) 第三章PL/SQL流程控制语句 (19) §3.1条件语句 (19) §3.2CASE表达式 (20) §3.3循环 (20) §3.3标号和GOTO (22) §3.4NULL语句 (22) 第四章游标的使用 (24)
实验三PL/SQL编程 1．PL/SQL块处理 定义一个包含声明、执行和异常处理的块 查询EMP表中职工号7788的工资，输出工资的值并且如果工资小于3000那么把工资更改为3000，异常部分对NO_DATA_FOUND异常进行处理，输出没有该员工。 如果想运行缓冲区的内容，那么可以用RUN命令或者/命令；serveroutput需要设置为on 2．记录类型的使用 创建一个记录类型v_record，类型包含name，salary，job，deptno等分量，要求记录类型的分量的数据类型和emp表中列的数据类型一致（%type实现）。创建一个变量，变量类型为v_ record，读取EMP表中职工号为7788的ename，sal，job，deptno为该变量赋值，输出变量的分量。 3．条件语句的使用 分别用IF语句和CASE语句实现以下要求：输入一个员工号，修改该员工的工资，如果该员工职位是CLERK，工资增加100；若为SALESMAN，工资增加160；若为ANALYST，工资增加200；否则增加300。 4. 循环和显示游标的使用 分别用简单循环、WHILE循环、FOR循环以及显示游标统计并输出各个部门的人数以及平均工资 5．用隐式游标实现以下要求：修改部门号为50的部门地址为‘BEIJING’。如果该部门不存在，则向dept表中插入一个部门号为50，地址为‘BEIJING’的记录。 6．创建一个显示雇员总人数的存储过程emp_count,并执行该存储过程 7. 编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程 8. 创建函数，实现功能为：在scott.emp表和scott.dept表中查询出任意给定职工号的职工姓名及职工所在部门的名称。 9. 创建触发器，实现更新dept表中的deptno值，级联更新emp表中相应值。 10. 对存储过程、函数及触发器实现查看、修改、删除等基本操作。 主要算法和程序清单： 1. DECLARE v_empno emp.empno%TYPE:=7788; v_sal emp.sal%TYPE; v_add emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=v_empno; IF v_sal<3000 THEN v_add:=3000;
ORACLE PL/SQL编程详解 第一章：PL/SQL 程序设计简介 SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL 语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。 1.1 SQL与PL/SQL 1.1.1 什么是PL/SQL? PL/SQL是Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS 附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。 1.2 PL/SQL的优点或特征 1.2.1 有利于客户/服务器环境应用的运行 对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。 1.2.2 适合于客户环境 PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相
实验6P L/S Q L程序设计1 实验目的 (1)掌握PL/SQL程序开发方法。 (2)掌握存储过程、函数、触发器、包的创建于调用。 2 实验要求 (1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。 (2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。 3 实验步骤 以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。 (1)创建一个存储过程，输出不同类型图书的数量、平均价格。 SQL> create or replace procedure proc_category_static 2 as 3 --定义游标，获取当前有哪些图书种类 4 cursor c_all_category is select distinct category from books; 5 --图书的平均价格 6 v_avg_cost number; 7 begin 8 --保存图书种类 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where category= group by category; 11 ('种类为：'||||',平均价格为：'|| v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 / (2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。 create or replace procedure proc_get_orderinfo( 2 p_customer_id %type) 3 as 4 --声明游标存储客户的订单号 5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id; 6 v_orderid %type; 7 --声明游标存储订单信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; 9 --保存图书的书名 10 v_title %type; 11 12 begin 13 open c_orderid; 14 LOOP 15 fetch c_orderid into v_orderid; 16 exit when c_orderid%NOTFOUND; 17 for v_orderitem in c_orderitem LOOP
-------------------------PL/SQL-------------------------------------------------CREATE PROCEDURE------------------------CREATE OR REPLACE PROCEDURE PRO_003(PARAMS) IS --内部使用变量或自定义错误异常 EXCEPTION EXP_NAME; BEGIN EXCEPTION --出现异常会跳到此处 END PRO_003; --------------------CREATE FUNCTION-------------------------CREATE OR REPLACE FUNCTION FUN_003(PARAMS) RETURN NUMBER IS --内部使用变量 BEGIN RETUNE EXCEPTION --出现异常会跳到此处 RETUNE END PRO_003; SET SERVEROUTPUT ON; --开启DBMS_OUTPUT --------------------调用示例--------------------------------DECLARE R NUMBER(1); BEGIN R := USER_ADD('baskball') ; DBMS_OUTPUT.PUT_LINE(R); END; --------------------CREATE SEQUENCE-------------------------CREATE SEQUENCE DATA_SEQ_003 START WITH0 INCREMENT BY1 MAXVALUE99 MINVALUE0 NOCACHE NOCYCLE; --------------------CREATE FUNCTION-------------------------
Oracle PL/SQL Cheatsheet Symbols ; Semicolon.Statement terminator % Percent sign Attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE). Also used as multibyte wildcard symbol, as in SQL. _ Single underscore Single-byte wildcard symbol, as in SQL : Colon Host variable indicator, such as :block.item in Oracle Forms ** Double asterisk Exponentiation operator < > and !=Not equals" || Double vertical bar Concatenation operator << and >>Label delimiters :=Assignment operator =>Association operator for positional notation --Double dash: single-line comment indicator /* and */Beginning and ending multiline comment block delimiters
Data Types. Database types NUMBER CHAR(N), VARCHAR2(N) DATE LONG LONG RAW ROWID MLSLABEL Definition Used to store any number Used for storing text Oracle system date Stores large blocks of text Stores large blocks of binary data Smaller binary data store Uesd for row identifier Security label Non database types.DEC, DECIMAL, REAL, DOUBLE-PRECISION, INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC, BINARY-INTEGER, CHARACTER, VARCHAR, BOOLEAN, TABLE, RECORD
实验五PL/SQL编程语言的使用与程序设计 【开发语言及实现平台或实验环境】 Oracle10g 【实验目的】 （1）了解PL/SQL在Oracle中的基本概念； （2）掌握PL/SQL的各组成部分； （3）PL/SQL的运用。 【实验原理】 1．PL/SQL字符集 和所有其他程序设计语言一样，PL/SQL也有一字符集。读者能从键盘上输入的字符集是PL/SQL的字符。此外，在某些场合，还有使用某些字符的规定。我们将要详细介绍： ●用PL/SQL编程时可能使用的字符 ●算术运算符 ●关系运算符 ●杂符号 1）合法字符 用PL/SQL程序时，允许使用下列字符： ●所有大、小写字母 ●数字0到9 ●符号：（）+-*/〈〉=！~；：.‘@ %，“# $ ^ & _ | { } ？[ ] 2）算术运算符 下面列出了PL/SQL中常用的算术运算符。如果读者使用过其他高级程序设计语言，想必不会陌生： 表6 算术运算符 运算符意义 + 加法 - 减法 * 乘法 / 除法 ** 幂 关系运算符 下面列出了PL/SQL中常用的关系运算符。如果读者有使用其他程序设计语言的经验，一定见过这些符号： 表7 关系运算符 运算符意义 <> 不等于 != 不等于 ^= 不等于 < 小于 > 大于 = 等于
3）杂符号 PL/SQL为支持编程，还使用下述符号。下面列出了部分符号，它们是最常用的，也是使用PL/SQL的所有读者都必须了解的。 表8 杂符号 4）变量 变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称： ●变量必须以字母（A~Z）开头。 ●其后跟可选的一个或多个字母，数字（0~9）或特殊字符$、# 或_。 ●变量长度不超过30个字符。 ●变量名中不能有空格。 5）保留字 保留字可视为PL/SQL版权所有的字符串。在定义变量名时，读者不能使用这些保留字。例如，词“loop”在PL/SQL中有特殊含义，因此下列代码是非法的： declare employee varchar2(30); loop number; 保留字不能用作变量名。尽管我们不推荐，但如果读者坚持，也可以连接两个保留字形成变量名（如loop_varchar2）。完整的PL/SQL保留字表可以在Oracle8文档中找到。 2、常用数据类型 到目前为止，我们讨论了在PL/SQL中编程时允许使用的字符，变量的命名和保留字。下面着重讨论数据类型。PL/SQL程序用于处理和显示多种类型的数据。和所有计算机软件一样，Oracle也将数据类型分成大量的子类。例如：数可分为整型（不允许有小数）和小数（有一位小数或多位小数）类。PL/SQL支持多种数据类型，此处讨论代码中最常用并且最实用的数据类型。 ●Varchar2 ●Number ●Date ●Boolean 3．PL/SQL的组件 1）块结构(Block structure) PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。PL/SQL 可以作为一个命名的子程序存放在数据库中，或直接在SQL*Plus窗口中作为一个匿名的块
《数据库开发技术》课程之实践三 PL/SQL程序设计 一、实践目的 1．掌握PL/SQL程序设计的基本知识。 2．掌握PL/SQL中控制结构的使用。具体包括： 选择结构（IF语句和CASE语句）； 循环结构（三种循环语句）： 3．掌握PL/SQL中SELECT语句和DML语句的正确使用方法。 二、实践内容和要求 1.编写匿名块，查询指定条件(如员工编号、姓名、部门、工作、工资、工资等级等)的记录并输出 declare e_name varchar(20); begin select ename into e_name from emp where empno='7902'; dbms_output.put_line(e_name); exception when no_data_found then dbms_output.put_line('ni shi sb'); end; / 2.修改1，增加异常处理 3.编写匿名块，向表中插入一条记录，数据由键盘输入 declare d_no dept.deptno%type; d_name dept.dname%type; d_loc dept.loc%type; begin insert into dept values(&d_no,&d_name,&d_loc); dbms_output.put_line('插入成功'); end; / 4.修改3，数据由键盘输入，加入异常处理 5.编写匿名块，修改表中的某些数据 declare d_no dept.deptno%type; d_name dept.dname%type; d_loc dept.loc%type;