转自：http://blog.sina.com.cn/s/blog_4c302f060101i4o1.html
一 PL/SQL的介绍
1 PL/SQL是什么？
PL/SQL(procedural language/SQL)是Oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变的更加强大。
（1）过程，函数，触发器是PL/SQL编写的
（2）过程，函数，触发器是在Oracle中的
（3）PL/SQL是非常强大的数据库过程化语言
（4）过程，函数可以在java程序中调用
2 学习的必要性
（1）提供应用程序的运行性能
传统的操作数据库的方法是：sql语句写到java程序中，java连接Oracle，传递给数据库，Oracle翻译SQL语句，编译成自己能识别的代码，然后才执行。
PL/SQL:直接在Oracle中写过程，由java调用过程，而此过程已经编译成可执行代码。这样节省了时间。
（2）模块化的设计思想[分页的过程，订单的过程，转账的过程。。。]
（3）减少网络传输量
传统的方法：数据库和java中的SQL语句不在同一机器上，要经过网络传输
过程：直接调用
（4）提高安全性
过程中封装了用户名、密码、表名、字段名等信息。
3 pl/sql的缺点
移植性不好 换数据库了就不能用了
4 pl/sql开发工具
（1）sqlplus开发工具 开始——运行——sqlplusw——用户名、口令
（2）pl/sql developer 开发工具 文件——新建命令窗口
5 例子：
eg1：编写一个存储过程，该过程可以向某表中添加记录——用sqlplus开发工具
（1）创建一张简单表 ：create table mytest(name varchar2(30),passwd varchar2(30));
（2）创建过程：
create or replace procedure sp_pro1 is --replace：代表如果原来有sp_pro1将被替换
begin--执行部分
insert into mytest values('李叶','m123');
end;
回车 / 回车
（3）如何查看错误信息
如果编译有错，则可以用show error显示错误。如果没错，则提示过程已创建。
（4）如何调用该过程
①第一种方式：exec 过程名（参数值1，参数值2。。。）;
②第二种方式：call 过程名（参数值1，参数值2。。。）;
eg2:编写一个存储过程，该过程可以删除某表记录——用pl/sql developer开发工具
create or replace procedure sp_pro2 is
begin
delete from mytest where name='韩顺平';
end;
/
exec sp_pro2;
二 PL/SQL的基础
开发人员使用pl/sql编写应用程序模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。
1 pl/sql可以做什么？
（1）简单分类
块（编程的基础单元） 过程（存储过程）、函数、触发器和包
（2）编写规范
①注释
单行注释 --
多行注释
②表示符号的命名规范
1）当定义变量时，建议用v_作为前缀v_sal
2）当定义常量时，建议用c_作为前缀c_rate
3）当定义游标时，建议用_cursor作为后缀emp_cursor
4）当定义例外时，建议用e_作为前缀e_error
2 pl/sql块介绍
（1）介绍
块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果要想完成复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。
（2）块结构示意图
pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。
declare
begin
exception
end;
块结构和java程序的比较
java程序结构：
public static void main(String[] args)
{
int a=1;//定义部分
try{
a++;//执行部分
}
catch(Exception e){
//捕获异常
}
}
3 例子
eg1 只包括执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
end;
相关说明：dbms_output是Oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。
eg2 包括定义部分和执行部分
declare
v_ename varchar2(5); --定义字符串变量
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal);
end;
相关说明：(1) select ename into v_ename from... 把查出的ename赋值给v_ename
(2)& 表示要接收从控制台输入的变量
(3)|| 字符串连接符
eg3 包含定义部分、执行部分和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要：
① 比如在eg2中，如果输入了不存在的雇员号，应当做例外处理
②有时出现异常，希望用另外的逻辑处理
相关说明：Oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。
declare
v_ename varchar2(5);--定义字符串变量
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename||' 工资:'||v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('朋友你的编号输入有误！');
end;
4 过程
过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out），通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
5 实例
①请考虑编写一个过程，可以输入雇员名，新工资 可修改雇员的工资
create procedure sp_prop3(sp_Name varhcar2,newSal number) is
begin
--执行部分 根据用户名修改工资
update emp set sal=newSal where ename=sp_Name;
end;
② 如何调用过程有两种方法
exec sp_pro3('SCOTT',4768);
或者call。。。
③ 如何在java中调用一个存储过程，C C++程序都可以调用
启动eclipse——file new java project——project命名——new package 命名 com.sp——new class 命名 TestOraclePro
package com.sp;
import java.sql.*;
public class TestOraclePro{
public static void main(String[] args)
{
try{
//1.加载驱动
Class.forName("Oracle.jdbc.driver.OracleDriver");
//2. 得到连接
Connection ct=DriverManager.getConnection("jdbc:Oracle:thin:@127.0.0.1","SCOTT","m123");
//3 创建CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro3(?,?)}");
//4 给？赋值
cs.setString(1,"SMITH");
cs.setInt(2,10);
//5 执行
cs.execute();
//关闭
cs.close();
ct.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}
加入驱动 引入一个jar包 property——jave build path——libraries——add external jars
问题：如何使用过程返回值？？
6 函数
函数用于返回特定的数据，当建立函数时，在函数的头部必须包含return子句，而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，
实例：
--输入雇员的姓名，返回该雇员的年薪
create function sp_fun2(spName varchar2) return number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
调用
var abc number;
call sp_fun2('SCOTT') into:abc;
7 包
包用于逻辑上组合过程和函数，它是由包规范和包体组成。
① 我们可以使用create package命令来创建包：
实例
--创建一个包sp_package
--声明该包有一个过程
--声明该包有一个函数
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
/ 执行 --包被创建
包的规范只包含了过程和函数的声明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
② 建立包体可以使用create package body命令
--给包 sp_package 实现包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
undate emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return number is annual_sal number;
begin
select sal*12+nvl(comm,0)*12 into annual_sal from emp where ename=name;
return annual_sal;
end;
end;
/ 包体被创建
③ 如何调用包的过程或函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名
如：call sp_package.update_sal('SCOTT',1500);
8 触发器
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
9 定义并使用变量
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括：
①标量类型（scalar）
②复合类型（composite）
③参照类型（reference）
④lob（large Object）
（1）标量（scalar）——常用类型
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=|default expr]
identifier：名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default： 用于指定初始值
expr：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等
例子：
①定义一个变长字符串
v_ename varchar2(10);
②定义一个小数 范围-9999.99~9999.99
v_sal number(6,2);
③定义一个小数并给一个初始值5.4
v_sal2 number(6,2):=5.4
④定义一个日期类型的数据
v_hiredate date;
⑤定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
使用标量
在定义好标量之后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号（:=）
例子
下面以输入员工号，显示员工姓名、工资、个人所得税（税率为0.03）为例，说明变量的使用，看看如何编写。
declare
c_taxrate constant number(3,2):=0.03;
v_name varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2)
begin
select ename,sal into v_name,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=sal*c_taxrate;
--输出
dbms_optput.put_line('姓名是:'||v_name||'工资：'||v_sal||'交税:'||v_tax_sal);
（2）标量（scalar）——使用%type类型
对于上面的pl/sql块有一个问题：
就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它就会按照数据库列来确定你定义的变量的类型和长度。
标识符名 表名.列名%type; v_name emp.ename%type; v_name的类型和emp.ename的类型长度一致
（3）复合变量（composite）——介绍
①pl/sql记录
类似于高级语言中的结构体，需要注意的是当引用pl/sql记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）如下：
declare
--定义一个pl/sql记录类型，名字emp_record_type,类型包含3个数据分别是name,salary,title。
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--定义了一个变量，该变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name||'工资是:'||sp_record.salary);
②pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下表不能为负数，而pl/sql是可以为负数的，并且表元素的下表没有限制。实例如下
declare
--定义了一个pl/sql表类型，sp_table_type,该类型用于存放emp.ename%type
type sp_table_type is table of emp.ename%type
index by binary_integer;--下表为整数，可以为负
sp_table sp_table_type;--定义了一个sp_table_type类型的变量sp_table
begin
select ename into sp_table(0) from emp where empno=7788;--如果把where去掉，则应该使用参照变量
dbms_output.put_line('员工名：'||sp_table(0));
end;
③嵌套表
④varray
（4）参照变量
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型。
参照变量——ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就和一个select语句结合了。
实例：
①请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资
declare
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义两个变量
v_ename emp.name%type;
v_sal emp.sal%type;
--执行
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no ;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor not found;
dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
end loop;
end;
②在①的基础上，如果某个员工的工资低于200元，就增加100元
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义两个变量
v_ename emp.name%type;
v_sal emp.sal%type;
--执行
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no ;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断工资是否小于200 决定是否更新
if v_sal<200 then
update emp set sal=sal+100 ;
--判断是否test_cursor为空
exit when test_cursor not found;
dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
end loop;
end;