////////PL/SQL编程////////////////////////
1、pl/sql是什么？
pl/sql是oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得他的功能变得更加强大。
例如在数据库中，存储过程，函数，触发器是由pl/sql编写的，并放在oracle中，是一个非常强大的数据库过程语言，pl/sql编写的存储过程，函数可以在java中调用。
pl/sql与传统的编程主要是减少了传统sql语句查询时需要编译的过程，从而提高了性能。
//创建简单的存储过程：
create procedure 存储过程名 is
begin
insert into mytest vaues('shunping','m123');
end;
/ --->表示让存储过程编译
create or replace procedure 存储过程名 is //replace表示如果存储过程名存在，则替换当前的
begin
insert into mytest('shunping','m123');
end;
/
如何查看存储过程错误信息:show error;
如何调用存储过程（两种方法）：方法（1）exec 过程名（参数1，参数2...） 方法（2）call 过程名（参数1，参数2...） //oracle11g中不能用
2、pl/sql究竟可以做什么
模块编程----->存储过程编程，函数，触发器，包
3、编写规范
1）注释
单行注释：--
多行注释：
/* ...*/来划分
2）标识符号的命名规范
当定义变量时，建议用v_作为前缀v_sal
当定义常量时，建议用c_作为前缀c_rate
当定义游标时，建议用_cursor作为后缀emp_cursor
当定义例外时，建议用e_作为前缀e_error
4、块
块是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能要在一个pl/sql块中嵌套其他的sql块
块的结构：
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。如下所示：
1)declear/*定义部分---定义常量，变量，游标，例外，复杂数据类型*/
2)begin/*执行部分---要执行的pl/sql语句和sql语句*/
3)exception /*处理运行的各种错误*/
4)end;
例1如：
set serveroutput on --打开输出选项 ，若关闭set serveroutput off
begin
dbms_output.put_line('hello');
end;
/
相关说明：dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。
例2如：
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no; --into v_name表示查出来的ename的值放入到v_name中。
dbms_output.put_line('ename='||v_ename); --||表示连接符
end;
/
相关说明：&表示要接受从控制台输入变量
若增加需要显示雇员的薪水？
declare
v_ename varchar2(5);--定义字符串变量
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no; --ename,sal与v_ename,v_sal顺序必须一致
dbms_output.put_line('ename='||v_ename ||' sal='||v_sal); --||表示连接符
end;
/
注意：这里以上查询的结果只有一行数据，若查询的结果是多行数据，则需要游标来控制。
例3如：带有异常处理的块
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要：比如例2中输入不存在的编号，如何处理？有时候出现异常，希望用户用另外的逻辑进行处理。
其中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; --ename,sal与v_ename,v_sal顺序必须一致
dbms_output.put_line('ename='||v_ename ||' sal='||v_sal); --||表示连接符
--异常处理
exception
when no_data_found then
dbms_output.put_line('the empno is error!');
end;
/
5、存储过程
存储过程用于执行特定的操作，当建立存储过程时，既可以指定输入参数，也可以指定输出参数，通过在存储过程中使用输入参数，可以将数据传递到执行部分，通过使用输出参数，可以将执行部分的数据传递到应用环境（如java程序中）。在sql/plus中可以使用create procedure命令来建立过程。
例如：
1）请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资
create procedure procedure_name(new_name varchar2,new_sal number) is --带有输入参数的存储过程
begin
--执行部分,根据用户名修改工资
update emp set sal=new_sal where ename=new_name;
end;
/
2）如何调用过程有两种方法：exec 存储过程名
3）如何在java程序中调用一个存储过程。（重要）
如何使用存储过程返回值？
6、函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例：
create function annual_income(name varchar2)
return number is --number指定返回的类型，可以是其他类型
annual_salazy number(7,2);--声明返回变量
begin
select sal*12+nvl(comm,0) into annual_salazy from emp where ename=name; --nvl函数是防止comm为空
return annual_salazy ;
end;
/
调用函数
var income number; --decare声明一个变量来接受返回的参数
call annual_income('SCOTT') into:income;
print income
同样可以通过在java中调用函数
select annual_income('SCOTT') from dual;
可以通过rs.getInt()得到返回值。
7、包
包用于在逻辑上组合存储过程和函数，它由包规范和包体两部分组成。
（1）我们可以使用create package命令来创建包：
create package packeage_name is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了存储过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现包规范中的过程和函数，
（2）建立包体可以使用create package body 命令
create or replace package body packeage_name is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
//调用包的存储过程和函数
当调用包的存储过程和函数时，在存储过程和函数前面要带有包名，如果要访问其他方案的包，还需要在包名前面加方案名。
如：call packeage_name.update_sal('SCOTT',1500);或者exec packeage_name.update_sal('SCOTT',1500);
特别说明：包是pl/sql中非常重要的部分。
8、触发器
触发器是指隐含的执行的存储过程，当定义触发器时，必须要指定出发的事件和触发的操作，常用的触发事件包括insert，update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器
特别说明：触发器可以维护数据库的安全和一致性。一般是当用户修改一个表的时候，自动触发修改该表所关联的其他表的数据，从而达到数据的一致性。
9、在编写pl/sql程序时，可以定义变量和常量：
（1）标量类型（scalar）也可以理解成变量。
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null][:=|default expr]
identifier：名称
constant：指定常量，需要指定它的初始值，且其值是不能改变的。
datatype：数据类型
not null：指定变量值不能为null
：= 给变量或是常量指定初始值
default ：用于指定初始值
expr：指定初始值的pl/sql表达式，可以是文本值，其他变量、函数等
例如：1）标量定义的案例
定义一个变长字符串：v_name varchar2(10);
定义一个小数 范围：-9999.99-9999.99： v_sal number(6,2);
定义一个小数并给定一个初始值为5.4：v_sal number(6,2):=5.4;
定义一个日期类型的数据：v_diredate date;
定义一个布尔变量，不能为空哦，初始值为false：v_valid boolean not null default false;
2）使用标量
在定义好变量后，就可以使用这些变量。这里需要说明的是：pl/sql块为变量赋值不同于其他编程语言，需要在等号前面加冒号（：=）。
例如：输入员工工号，如何显示雇员姓名，工资，个人所得税（0.03）？
declare
c_tax_rate number(3,2):=0.03;--税率
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename ,sal into v_ename, v_sal from emp where empno=&no; --ename ,sal 必须与后面的v_ename, v_sal对应，意思就是查出来的ename，sal放入到v_ename, v_sal中
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('name='||v_ename||' salary='||v_sal||' tax='||v_tax_sal);
end;
注意：这里存在一个问题，当定义的变量的长度与查询出来的结果的长度不匹配，则会报错。如：定义的v_ename的长度是5，若查出来的ename的长度超过了5就报错。解决办法就是采用%type
使用方法：就是在声明的时候使用%type。即：
v_ename emp.ename%type;--即采用emp表的类型及长度都一致
如：
declare
c_tax_rate emp.sal%type:=0.03;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('name='||v_ename||' salary='||v_sal||' tax='||v_tax_sal);
end;
（2）复合类型（composite）
用于存放多个值的变量，主要包括以下几种：
1）pl/sql记录
类似与高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀（记录变量，记录成员）如下：
declare
type emp_record_type is record( name emp.ename%type,salary emp.sal%type,title emp.job%type);--定义一个pl/sql记录类型，名称为emp_record_type ，包含name，salary，title三个数据
sp_record emp_record_type;--定义了一个变量sp_record ，该变量的类型是上面的pl/sql记录类型，相当于一个变量可以存放三个数据。
begin
select ename,sal,job into sp_record from emp where empno=&no;--语句，依次查询三个数据，并存放到对应的pl/sql记录的对象sp_record当中
dbms_output.put_line('employee='||sp_record.name);--输出pl/sql记录对象的成员
end;
/--表示执行。
2）pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中，数组的小标不能为负数，而pl/sql是可以为负数的，并且表元素的小标没有限制。示例如下：
declare
type sp_table_type is table of emp.ename%type --定义pl/sql表类型：sp_table_type emp.ename%type指明了表元素的类型和长度
index by binary_integer;--表示pl/sql表类型是按整数来排序的
sp_table sp_table_type;--定义了pl/sql表类型变量sp_table
begin
select ename into sp_table(0) from emp where empno=&no;//将查询的结果放到pl/sql表类型变量sp_table下标为0的位置上。
dbms_output.put_line('ename='||sp_table(0));
end;
//上面如果把where条件语句去掉会怎么样呢？则需要用到参照变量
3）嵌套表
4）varray
（3）参照类型（reference） //当查询的结果返回的是多行数据，则需要用到参照变量
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以是的应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型
1）游标变量（ref cursor）
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样子一个游标就与一个select语句结合了。示例如下：
11）请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和工作
declare
--定义变量
type sp_emp_cursor is ref cursor;--定义一个游标类型sp_emp_cursor
test_cursor sp_emp_cursor;--定义游标变量
--定义变量作为中间值，方便最后循环取出查询的结果
v_name emp.ename%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_name,v_sal; --fetch就是取出每一行数据，并给两个中间变量
--判断游标test_cursor是否为空
exit when test_cursor%notfound;--循环退出的条件
dbms_output.put_line('name='||v_name||' sal='||v_sal);
end loop;
close test_cursor;--关闭游标
end;
22）在11）的基础上，如果某个员工的工资低于200元，就增加100元。
2）对象类型变量（ref obj_type）
（4）lob（large object）
10、控制结构
（1）条件分支语句
pl/sql中提供了三种条件分支语句if--then，if---then--else，if--then--elseif---else
（2）简单的条件判断 if--then
例如：如何编写一个存储过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%？
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spName;
--判断
if v_sal<2000 then --if条件判断语句的用法
update emp set sal=sal+sal*10% where ename=spName;
end if;
end;
（3）二重条件分支 if--then--else
例如：如何编写一个存储过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100，如果补助为0就把补助设为200.
create or replace procedure sp_pro7(spName varchar2) is
--定义
v_comm emp.comm%type;
begin
--执行
select comm into v_comm from emp where ename=spName;
--判断
if v_comm<>0 then --if v_comm不等于0
update emp set comm =comm +100 where ename=spName;
else
update emp set comm =200 where ename=spName;
end if;
end;
（4）多重条件分支
例如：如何编写一个存储过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其他的职位的雇员工资增加200。
create or replace procedure sp_pro8(spNum number) is
--定义
v_job emp.job%type;
begin
--执行
select job into v_job from emp where empno=spNum;
--判断
if v_job='PRESIDENT' then
update emp set sal =sal +1000 where empno=spNum; --由于判断语句需要用到职位，所以上面选择语句查询的是职位job
elsif v_job='MANAGER' then
update emp set sal =sal+500 where empno=spNum;
else
update emp set sal =sal+200 where empno=spNum;
end if;
end;
（5）循环语句---loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次
案例：现有一张表users，表的结构如下
用户id 用户名
请编写一个存储过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。
create or replace procedure sp_pro9(spName varchar2) is
--定义 :=表示赋值
v_num number:=1;
begin
loop
insert into users values(v_num,spName);--往users表中插入数据，注意这个表应该实现存在
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
（6）循环语句---while
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while ..loop开始，以end loop结束
请编写一个存储过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始。
create or replace procedure sp_pro10(spName varchar2) is
--定义 :=表示赋值
v_num number:=1;
begin
while v_num<=20 loop --while循环是先判断，在执行循环体
insert into users values(v_num,spName);--往users表中插入数据，注意这个表应该实现存在
--自增
v_num:=v_num+1;
end loop;
end;
（7）循环语句--for循环
基本for循环的基本结构如下
begin
for i in reverse 1..10 loop
insert into users values(i,'sp');
end loop;
end;
(8)顺序控制语句
1）goto语句
2）nul语句
null语句不会执行任何操作，并且会直接将控制传递到下一条语句，使用null语句的主要好处是可以提高pl/sql的可读性
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<3000 then
update emp set comm =sal*0.1 where ename=v_ename;
else
null;
end if;
end;
（9）无返回值的存储过程
案例：现有一张表book，表的结构如下： 书号 书名 出版社
请编写一个存储过程，可以向book表添加书，要求通过java程序调用该过程
create procedure book(bookid in number,bookname in varchar2,publishhost in varchar2) is ---过程名中的in代表输入参数，后面还有一个out表示输出参数
begin
insert into addbook values(bookid,bookname,publishhost);
end;
--java中调用它
（10）有返回值的存储过程（返回值是非列表类型）
案例：如何编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名？
create or replace procedure pro11(spNo in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spNo;
end;
--然后再java中调用该存储过程，得到返回值。
//调用存储过程
CallableStatement cs=ct.prepareCall("{call sp_pro11(?,?)}");
//给存储过程第一个输入参数赋值
cs.setInt(1,7788);
//给存储过程第二个输出参数的类型进行关联
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//获取返回的参数值,取返回值要注意问好的顺序
String name=cs.getString(2);
System.out.println("7788的名字是："+name);
案例扩展(返回多个值)：编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名、工资、和岗位？
create or replace procedure sp_pro11(spno in number,spName out varchar2,spval out number,spjob out varchar2) is
begin
select ename,sal,job into spName,spval,spjob from emp where empno=spno;
end;
--然后再java中调用该存储过程，得到返回值。
//调用存储过程
CallableStatement cs=ct.prepareCall("{call sp_pro11(?,?,?,?)}");
//给存储过程第一个输入参数赋值
cs.setInt(1,7788);
//给存储过程第二、三、四个输出参数的类型进行关联
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//获取返回的参数值,取返回值要注意问好的顺序
String name=cs.getString(2);
String job=cs.getString(4);//获得工作岗位
System.out.println("7788的名字是："+name + "工作:"+job);
（11）有返回值得存储过程（返回值是列表【结果集】）
案例：编写一个存储过程，输入部门编号，返回该部门所有雇员的信息。对该题分析如下：
由于oracle存储过程没有返回值，他的所有返回值都是通过out参数来代替的，列表同样也不例外，但是由于是集合，所以不能用一般的参数，必须要用package了。所以要分两部分：
1）建一个包。如下：
create or replace package testpackage as
type test_curor is ref cursor;--在该包中定义了一个类型ref cursor（游标）类型的对象test_curor
end testpackage;
2）建立存储过程。如下：
create or replace procedure sp_pro12(sp_no in number,p_cursor out testpackage.test_curor) is
begin
open p_cursor for select * from emp where deptno=sp_no;
end;
3）如何咋java中调用以上的过程
//创建CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro12(?,?)}");
//给问号赋值
cs.setInt(1,10);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
（12）编写分页存储过程
案例：编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数和返回的结果集。
1）建一个包。如下：
create or replace package testfenye as
type test_curor is ref cursor;--在该包中定义了一个类型ref cursor（游标）类型的对象test_curor
end testfenye ;
2）建立存储过程。如下：
create or replace procedure fenye
(table_name in varchar2,--输入表名
pagesize in number,--每页的显示的总记录
pagenow in number,--显示的当前页
total_recoldnum out number,--总记录
total_pagenum out number,--总页数
result_cursor out testfenye.test_curor --结果集
) is
--定义部分
v_sql varchar2(1000);--定义一个sql语句
--定义两个整数
v_begin number:=(pagenow-1)*pagesize+1 ; --当前页的第一个记录
v_end number:=pagenow*pagesize; --当前页的最后一个记录
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from ( select * from '|| table_name ||') t1 where rownum<='||v_end||') where rn>'||v_begin;
--把游标和sql关联
open result_cursor for v_sql;
--计算total_recoldnum 和total_pagenum 。
--组织一个sql语句来查询总记录
v_sql:='select count(*) from '|| table_name;
--执行上面组织的sql语句，并把返回的值，赋给total_recoldnum；
execute immediate v_sql into total_recoldnum;
--计算total_pagenum总页数
if mod(total_recoldnum,pagesize)=0 then
total_pagenum:=total_recoldnum/pagesize;
else
total_pagenum:=total_recoldnum/pagesize+1;
endif;
--关闭游标
close result_cursor;
end;
---使用java来测试
//创建CallableStatement
CallableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?)}");
//给问号赋值
cs.setString(1,"emp");--表名
cs.setInt(2,5);--每页显示多少条记录
cs.setInt(3,1);--设置显示当前第几页，这里可以设置一个变量来根据用户选择来显示想要的页
//注册总记录数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注册总页数
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注册返回的结果集
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
//取得总记录数，要注意的是getInt中的参数是有参数的位置决定的
int rownum=cs.getInt(4);
//取得总页数
int pagenum=cs.getInt(5);
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(6);--这里需要注意强制转换
//显示
System.out.println("总记录rownum="+rownum);
System.out.println("总页数pagenum="+pagenum);
while(rs.next()){
System.out.println("编号："+rs.getInt(1)+" 名字："+rs.getString(2));
}
（13）新的需求，要求按照某个字段从低到高排序，然后在分页显示的话，则只需要在分页sql语句最内层的语句中添加order by [字段]
也就是上面的存储过程中的v_sql修改一下
v_sql:='select * from (select t1.*,rownum rn from ( select * from '|| table_name ||'order by sal) t1 where rownum<='||v_end||') where rn>'||v_begin;
浙公网安备 33010602011771号