Oracle PL/SQL入门

概述

       一、PL/SQL出现的目的

结构化查询语言(Structured Query Language，简称SQL)是用来访问关系型数据库一种通用语言，它属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单的调用相应语句来直接取得结果即可。显然，这种不关注任何实现细节的语言对于开发者来说有着极大的便利。然而，对于有些复杂的业务流程又要求相应的程序来描述，那么4GL就有些无能为力了。PL/SQL的出现正是为了解决这一问题，PL/SQL是一种过程化语言，属于第三代语言，它与C,C++,Java等语言一样关注于处理细节，因此可以用来实现比较复杂的业务逻辑。

本教程分两部分，第一部分主要对PL/SQL的编程基础进行讨论，第二部分结合一个案例来讲解PL/SQL编程。希望读者阅读本文后能够对PL/SQL编程有一个总体上的认识，为今后深入PL/SQL编程打下一个基础。

二、PL/SQL编程基础

掌握一门编程语言首要是要了解其基本的语法结构，即程序结构、数据类型、控制结构以及相应的内嵌函数（或编程接口）。

1、PL/SQL程序结构

PL/SQL程序都是以块（block）为基本单位。如下所示为一段完整的PL/SQL块：

/*声明部分，以declare开头*/
declare
v_id integer;
v_name varchar(20);
cursor c_emp is select * from employee where emp_id=3;
/*执行部分，以begin开头*/
begin
open c_emp;　　　　　　　　　　　　　--打开游标
loop
fetch c_emp into v_id,v_name;　　--从游标取数据
exit when c_emp%notfound ;
end loop ;
close c_emp;　　　　　　　　　　 --关闭游标
dbms_output.PUT_LINE(v_name);
/*异常处理部分，以exception开始*/
exception
when no_data_found then
dbms_output.PUT_LINE('没有数据');
end ;

从上面的PL/SQL程序段看出，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以begin开头）和异常处理部分（以 exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。

2、变量声明与赋值

PL/SQL主要用于数据库编程，所以其所有数据类型跟Oracle数据库里的字段类型是一一对应的，大体分为数字型、布尔型、字符型和日期型。为方便理解后面的例程，这里简单介绍两种常用数据类型：number、varchar2。

number

用来存储整数和浮点数。范围为１E-130 ～10E125，其使用语法为：

    number［（precision, scale）］

其中（precision, scale）是可选的，precision表示所有数字的个数，scale表示小数点右边数字的个数。

varchar2

用来存储变长的字符串，其使用语法为：

    varchar2［（size）］

其中size为可选，表示该字符串所能存储的最大长度。

在PL/SQL中声明变量与其他语言不太一样，它采用从右往左的方式声明，比如声明一个number类型的变量v_id，那其形式应为：

    v_id number;

如果给上面的v_id变量赋值，不能用”=”应该用”:=”,即形式为：

    v_id :=5;

3、控制结构

PL/SQL程序段中有三种程序结构：条件结构、循环结构和顺序结构。

条件结构

与其它语言完全类似，语法结构如下：

if condition then
statement1
else
statement2
end if ;

循环结构

这一结构与其他语言不太一样，在PL/SQL程序中有三种循环结构：

a. loop … end loop;
b. while condition loop … end loop;
c. for variable in low_bound . . upper_bound loop … end loop;

其中的“…”代表循环体。

顺序结构

实际就是goto的运用，不过从程序控制的角度来看，尽量少用goto可以使得程序结构更加的清晰。

4、SQL基本命令

PL/SQL使用的数据库操作语言还是基于SQL的，所以熟悉SQL是进行PL/SQL编程的基础。表１-1为SQL语言的分类。

表1-1　SQL语言分类

类别     SQL语句
数据定义语言(DDL)     Create ,Drop,Grant,Revoke, …
数据操纵语言(DML)     Update,Insert,Delete, …
数据控制语言(DCL)     Commit,Rollback,Savapoint, …
其他     Alter System,Connect,Allocate, …

可以参阅其他关于SQL语言的资料来了解具体的语法结构，这里就不多赘述了。

三、过程与函数

PL/SQL中的过程和函数与其他语言的过程和函数的概念一样，都是为了执行一定的任务而组合在一起的语句。过程无返回值，函数有返回值。其语法结构为：
过程：Create or replace procedure procname(参数列表) as PL/SQL语句块

函数：Create or replace function funcname(参数列表) return 返回值 as PL/SQL语句块

这里为了更为方面的说明过程的运用，下面给出一个示例：

问题：假设有一张表t1，有f1和f2两个字段，f1为number类型，f２为varchar2类型，然后往t１里写两条记录，内容自定。

Create or replace procedure test_procedure as
V_f11 number :=1; /*声明变量并赋初值*/
V_f12 number :=2;
V_f21 varchar2(20) :=’first’;
V_f22 varchar2(20) :=’second’;
Begin
Insert into t1 values (V_f11, V_f21);
Insert into t1 values (V_f12, V_f22);
End test_procedure; /*test_procedure可以省略*/

至此，test_procedure存储过程已经完成，然后经过编译后就可以在其他PL/SQL块或者过程中调用了。由于函数与过程具有很大的相似性，所以这里就不再重复了。

四、游标

这里特别提出游标的概念，是因为它在PL/SQL的编程中非常的重要。其定义为：用游标来指代一个DML　SQL操作返回的结果集。即当一个对数据库的查询操作返回一组结果集时，用游标来标注这组结果集，以后通过对游标的操作来获取结果集中的数据信息。定义游标的语法结构如下：

cursor cursor_name is SQL语句;

在本文第一段代码中有一句话如下：

cursor c_emp is select * from employee where emp_id=3;

其含义是定义一个游标c_emp，其代表着employee表中所有emp_id字段为３的结果集。当需要操作该结果集时，必须完成三步：打开游标、使用fetch语句将游标里的数据取出、关闭游标。请参照本文第一段代码的注释理解游标操作的三步骤。

五、其他概念

PL/SQL中包的概念很重要，主要是对一组功能相近的过程和函数进行封装，类似于面向对象中的名字空间的概念。

触发器是一种特殊的存储过程，其调用者比较特殊，是当发生特定的事件才被调用，主要用于多表之间的消息通知。

六、调试环境

PL/SQL的调试环境目前比较多，除了Oracle自带有调试环境Sql*plus以外，本人推荐TOAD这个工具，该工具用户界面友好，可以提高程序的编制效率。

本文主要讲解PL/SQL的基础部分，熟悉这部分内容后可以进行存储过程的编写和应用，对于提高数据库服务器端的执行效率很有帮助。


案例实践

一． 案例介绍

某数据库有两张表，是关于某公司员工资料、薪水和部门信息的，它们分别是emp表和dept表，两张表的结构如下：
  

要求如下：

1、按照上表结构建立相应的表，并每张表写入5组合法数据。

2、操纵相关表，使得“技术部”的员工的薪水上涨20%。

3、建立日志，追踪薪水变动情况。

4、建立测试包。

二． 案例的分析与实现

从前面案例的介绍不难看出，要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察触发器的应用；要求4的考察面相对多一些，不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法。了解了这些考察的知识点，就可以一一去解决。

要求1：

首先根据前面表的结构可以创建两张表：

——创建员工表

create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));

——部门表

create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。

/*给emp表添加记录的存储过程*/
create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary number) as
v_emp_id number:=p_emp_id;
v_emp_name varchar2(20):=p_emp_name;
v_emp_salary number:=p_emp_salary;
begin
insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
end ins_table_emp;

/*给dept表添加记录的存储过程*/
create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) as
v_dept_id number:=p_dept_id;
v_dept_name varchar2(20):=p_dept_name;
v_emp_id number:=p_emp_id;
begin
insert into dept values (v_dept_id,v_dept_name,v_emp_id);
end ins_table_emp;

/*调用相应的存储过程实现记录添加*/
begin
ins_table_emp(10000,'',4000);
ins_table_emp(10001,'??èy',2300);
ins_table_emp(10002,'3?t',3500);
ins_table_emp(10003,'à???',3500);
ins_table_emp(10004,'á?ò?',3500);

ins_table_dept(111,'DD?t2?',10000);
ins_table_dept(111,'DD?t2?',10001);
ins_table_dept(111,'DD?t2?',10002);
ins_table_dept(112,'??ê?2?',10003);
ins_table_dept(113,'êD3?2?',10004);
end;

要求2：

给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工塞选出来，然后对这些员工的薪水进行相应的改动。依照这一思路，代码如下：

（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）

create or replace procedure add_salary(p_dept_name varchar2) as
v_dept_name varchar2(20):=p_dept_name;
begin
update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='??ê?2?');
end add_salary;

要求3：

建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。

create or replace trigger print_salary_change 　
before delete or insert or update on emp　　--触发事件
for each row　　　　　　　　　　　　　　　　　　-- 每修改一行都需要调用此过程

declare 　　　　　　　　　　--只有触发器的声明需要declare，过程和函数都不需要
salary_balance number;
begin
--:new 与:old分别代表该行在修改前和修改后的记录
salary_balance=:new.salary=:old.salary;
dbms_output.PUT_LINE('old salary is: '|| :old.salary);
dbms_output.PUT_LINE('old salary is: '|| :new.salary);
dbms_output.PUT_LINE('old salary is: '|| to_char(salary_balance));
end print_salary_change;

要求4：

与其他语言（c/C++等）相比，PL/SQL的测试有其不同之处，归纳下来有三种方法：

1、使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量，以此来观察程序是否存在逻辑错误。

2、插入测试表的方法。即创建一个临时的中间表，然后把所有涉及到的中间变量的结果都作为记录插入到中间表中，这样可以查询表中的结果来观察程序的执行情况。

3、使用异常处理手段，对可疑的程序段使用begin … end ，然后可以在exception里进行异常捕获处理。

这里准备使用第二种方法来建立一个测试包，PL/SQL里包的概念类似于面向对象里的类的概念，包将一组操作和属性封装在一起，不仅增强了程序的模块化，而且由于封装了更多的操作和属性而提高了执行效能。建立一个PL/SQL需要两个步骤：首先要建立包头，类似于建立一个类的头文件，里面主要对包中的过程，函数和变量的声明；第二部分主要是包体部分，实现前面声明的过程和函数，另外还需要对包进行初始化等工作。

根据这一思路，建立测试包如下：

/*包头部分*/
create or replace package debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2)
procedure reset;
v_numberOfLine number;
end debug;
/*包体部分*/
create or replace package body debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2) is
begin
insert into debugtable
values(v_numberOfLine,v_description,v_valueOfvariable);
v_numberOfLine:=v_numberOfLine+1;
end debug;
procedure reset is
begin
v_numberOfLine:=1;
delete from debugtable;
end reset;
/*初始化部分*/
begin
reset;
end debug;

三．小结

综合前面对4个问题的解答，基本把PL/SQL的主要部分融会进来了，虽然很多地方只是涉及到比较粗浅的层次，但是有了这一基础，深入下去也是不难的。

总之，PL/SQL编程与其他语言编程有一定的区别，读者只有把握好其特点才能更好的掌握数据库开发的方面知识。