oracle-PL/SQL-基础学习

什么是 PL/SQL

PL/SQL 是 Procedure Language & Structured Query Language 的缩写
PL/SQL是对 SQL 语言存储过程语言的扩展
是一种过程处理语言，简称 PL/SQL

PL/SQL的好处

有利于客户/服务器环境应用的运行

对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数
据交换。应用运行的效率自然就回受到影响。如果使用 PL/SQL 进行编程，将这种具有大量数据处理的应用
放在服务器端来执行。自然就省去了数据在网上的传输时间。

适合于客户环境

PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。对于客户端来说，PL/SQL 可以嵌套到相应的工具中，

客户端程序可以执行本地包含 PL/SQL 部分，也可以向服务发 SQL 命令或激活服务器端的 PL/SQL 程序运行

PL/SQL可用的SQL语句

PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。
在 PL/SQL 中可以使用的 SQL 语句有：
NSERT，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 的 开发工具中使用
• 其它开发工具也可以调用 PL/SQL 编写的过程和函数，如 Power Builder 等都可以调用服务器端的PL/SQL 过程

运行 PL/SQL 程序

PL/SQL 程序的运行是通过 ORACLE 中的一个引擎来进行的。这个引擎可能在 ORACLE 的服务器端，也可
能在 ORACLE 应用开发的客户端。引擎执行 PL/SQL 中的过程性语句，然后将 SQL 语句发送给数据库服务器
来执行。再将结果返回给执行端

PL/SQL 块结构和组成元素

PL/SQL块

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

PL/SQL 块的结构如下：

DECLARE 
/* 声明部分: 在此声明 PL/SQL 用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
 /* 执行部分: 过程及 SQL 语句 , 即程序的主要部分 */
EXCEPTION
 /* 执行异常部分: 错误处理 */
END;

其中执行部分是必须的。

PL/SQL 块可以分为三类：

1. 无名块：动态构造，只能执行一次。
2. 子程序:存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
3. 触发器:当数据库发生操作时，会触发一些事件，从而自动执行相应的程序

PL/SQL 结构

• PL/SQL 块中可以包含子块；
• 子块可以位于 PL/SQL 中的任何部分；
• 子块也即 PL/SQL 中的一条命令；

标识符

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

• 标识符名不能超过30字符
• 第一个字符必须为字母
• 不分大小写
• 不能使用减号(-)
• 不能是SQL保留字
  提示：一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果

例如：下面的例子将会删除所有的纪录，而不是 KING 的记录；

declare 
ename varchar2(20) :='KING'l
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 中可以使用的变量类型有

复合类型

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

记录类型

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

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

使用%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE
使用%TYPE 特性的优点在于：

• 所引用的数据库列的数据类型可以不必知道；
• 所引用的数据库列的数据类型可以实时改变。

declare 
	type test_rec is record(
		l_name employees.last_name%type,
		d_id employees.department_id%type
	);
begin
	select last_name,
	department_id into v_emp
	from employees where employee_id=200;
	dbms_output.put_line(v_emp.l_name|| ',' || v_emp.d_id);
end;		

使用%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。使用%ROWTYPE的优点在于:

• 所引用的数据库中列的个数和数据类型可以不必知道
• 所引用的数据库中列的个数和数据类型可以实时改变

declare 
	v_emp employees%rowtype;
	begin
	select * into v_emp from employees where employee_id=200;
	dbms_output.put_line(v_emp.last_name ||',' || v_emp.department_id|| ',' || v_emp.hire_date);
end;
/	

sql语句添加/,表示立即执行

PL/SQL表(嵌套表)

PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表. 声明嵌套表类型的一般语法如下:

TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};

declare 
type dep_table_type is table of departments%rowtype;
my_dep_table dep_table_type :=dep_tabl_type();
begin
	my_dep_table.extend(5);
for i in 1 .. 5 loop
select * into my_dep_table(i) from departments
where department_id=200+10*i;
end loop;
dbms_output.put_line(my_dep_table.count());
dbms_output.put_lie(my_dep_table(1).department_id);
end;

说明: 1) 在使用嵌套表之前必须先使用该集合的构造器初始化它. PL/SQL 自动提供一个带有相同名字的构造器
作为集合类型.
2) 嵌套表可以有任意数量的行. 表的大小在必要时可动态地增加或减少: extend(x) 方法添加 x 个空元
素到集合末尾; trim(x) 方法为去掉集合末尾的 x 个元素

运算符和表达式

关系运算符

一般运算符

逻辑运算符

变量赋值

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

字符及数字运算特点

空值加数字仍是空值：NULL + < 数字> = NULL
空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>

BOOLEAN 赋值

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

数据库赋值

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

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 语句中的列赋值给布尔变量

可转换的类型赋值

• char 转换为number
  使用 TO_NUMBER 函数来完成字符到数字的转换，如

v_total := TO_NUMBER('100.')+sal;

• number转换为char
  使用 TO_CHAR 函数可以实现数字到字符的转换，如：

v_comm :=TO_CHAR('123.45') || '元'

• 字符转换为日期
  使用 TO_DATE 函数可以实现 字符到日期的转换，如：

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

• 日期转换为字符
  使用 TO_CHAR 函数可以实现日期到字符的转换，如：

v_to_day := to_char(sysdate,'yyyy.mm.dd hh24.mi:ss');

变量作用范围可见性

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

• 变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束
• 一个变量（标识）只能在你所引用的块内是可见的
• 当一个变量超出了作用范围，PL/SQL 引擎就释放用来存放该变量的空间（因为它可能不用了）
• 在子块中重新定义该变量后，它的作用仅在该块内

注释

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

• 使用双-减号来加注释
  PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：

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

• 使用/* */ 来加一行或多行注释
  被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。

简单例子

/*本例子仅是一个简单的插入，不是实际应用*/
declare
	v_ename varchar2(20) :='Bill';
	v_sal    number(7,2) :=123.456;
	v_deptno number(2)   :=10;
	v_empmo  number(4)    :=8888;
begin
	insert into emp(empno,ename,job,sal,deptno,hiiredate)
	values (v_empno,v_ename,'Manage',v_sal,v_deptno,
	to_date('1954.06.09','yyyy.mm.dd'));
end;		

/*本例子仅是一个简单的删除，不是实际应用*/
declare 
	v_empno  number(4) :=8888;
begin
	delete from emp where empno=v_empno;
	commit;
end;