pl/sql的介绍
为什么需要pl/sql编程？
因为使用纯的sql语句来操作数据库，有先天性的技术缺陷：
1、不能模块编程；
2、执行速度慢；
3、安全性有问题；
4、浪费带宽。
pl/sql是什么？
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
学习必要性
1、提高应用程序的运行性能；
2、模块化的设计思想[分页的过程，订单的过程，转账的过程...]；
3、减少网络传输量；
4、提高安全性。
缺点:移植性不好。
sqlplus开发工具
sqlplus是oracle公司提供的一个工具。
举一个简单案例：
编写一个存储过程，该过程可以向某表中添加记录。
创建add_emp存储过程：
create procedure add_emp is
begin
insert into emp (empno,ename) values(4444,'4444');
end;
/
执行：
exec add_emp;
pl/sql developer开发工具
pl/sql developer是用于开发pl/sql块的集成开发环境(IDE)，他是一个独立的产品，而不是oracle的一个附带品。
举一个简单案例：
编写一个存储过程，该过程可以删除某表记录。
创建删除del_emp过程
create procedure del_emp(in_empno number) is
begin
delete from emp where empno=in_empno;
end;
/
执行过程：
exec del_emp(4444);
创建过程基本语法：
create procedure 过程名(参数1,...)
is
begin
执行语句;
end;
/
执行过程语法:
exec 过程名(传入参数,...)
pl/sql基础知识--介绍
开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。
比如：分页存储过程模块、订单管理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询要求。
pl/sql简单分类
块(编程)包含：
过程(存储过程)、函数、触发器、包
pl/sql基础知识--编写规范
1、注释
单行注释：--
多行注释：/*...*/来划分
2、标识符号的命名规范
1)当定义变量时，建议用v_作为前缀；如：v_sal
2)当定义常量时，建议用c_作为前缀；如：c_rate
3)当定义游标时，建议用_cursor作为后缀；如：emp_cursor
4)当定义例外时，建议用e_作为前缀；如：e_error
pl/sql块介绍
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可以只需要编写一个pl/sql块；但是如果要想实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。
块结构示意图：
pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。
如下所示：
declare
/*定义部分----定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分----要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分----处理运行的各种错误*/
end;
重要说明：
1、定义部分是从declare开始的，该部分是可选的；
2、执行部分是从begin开始的，该部分是必需的；
3、例外处理部分是从exception开始的，该部分是可选的。
pl/sql实例：只包括执行部分的pl/sql块
案例：输出hello,world
相关说明：dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output的一个过程。
--开发一个只包括执行部分的block块
set serveroutput on;
begin
dbms_output.put_line('hello,world');
end;
/
特别说明：在默认情况下，dbms_output.put_line是不输出内容的，需要set serveroutput on;才可输出。
实例2：包含定义部分和执行部分的pl/sql块
案例：根据用户输入的雇员编号，显示该雇员的名字
相关说明：
&表示要接收从控制台输入的变量
||表示把两个串拼接起来
declare
--定义变量的格式：变量名称 变量类型
v_ename varchar2(64);
begin
select ename into v_ename from emp where empno=&empno;--把查询到的ename放到v_ename变量中
--输出v_ename
dbms_output.put_line('雇员名字：'||v_ename);
end;
/
将上面的块改为过程
create procedure pro3(in_empno number) is
v_ename varchar2(64);
begin
select ename into v_ename from emp where empno=in_empno;
dbms_output.put_line('雇员名字：'||v_ename);
end;
/
实例3--包含定义部分、执行部分和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要：
1、比如在实例2中，如果输入了不存在的雇员号，应当做例外处理；
2、有时出现异常，希望用另外的逻辑处理。比如，如果不存在就加入编号为1，名字为“马大哈”这么一个人。
我们看看如何完成1的要求：
相关说明：oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。
可以查看pl/sql官方文档看看oracle提供了哪些例外。
declare
v_ename varchar2(64);
begin
select ename into v_ename from emp where empno=&empno;
dbms_output.put_line('雇员名字：'||v_ename);
exception
when no_data_found then
dbms_output.put_line('你查询的雇员信息不存在!');
end;
/
对该案例的细节说明：
这里我们涉及到异常处理，
异常的基本语法：
exception
when 异常的名称1 then
//对异常处理的代码
when 异常的名称2 then
//对异常处理的代码
end;
异常处理的作用：
1、可以捕获异常，可以给出明确提示；
2、有时可以利用异常来进行业务处理。
declare
v_ename varchar2(64);
begin
select ename into v_ename from emp where empno=&empno;
dbms_output.put_line('雇员名字：'||v_ename);
exception
when no_data_found then
dbms_output.put_line('你查询的雇员信息不存在!加入一条信息');
isnert into emp (empno,ename) values(1,'马大哈');
end;
/
java捕获异常
try{
//如果用户输入字串
int abc=Integer.parse(str);
}catch(Exception e){
//对不起你输入的字串不是一个数
}
pl/sql基础知识--过程快速入门
过程
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下：
1、请考虑编写一个过程，可以输入雇员名，新工资可修改雇员的工资
2、如何调用过程有两种方法：
exec 过程名(参数值,..)
call 过程名(参数值,..)
创建存储过程基本语法：
create or replace procedure 过程名(变量 in 变量类型,..,变量 out 变量类型) is
//定义变量
begin
//执行语句;
end;
/
特别说明：or replace在创建存储过程中可带也可不带。带or replace是指在存储过程名字相同时将其覆盖。不带则无法覆盖。在使用or replace时要小心，建议不使用or replace对原存储过程进行覆盖。
举例：请考虑编写一个过程，可以输入雇员名，新工资可修改雇员的工资
create procedure update_sal(in_name in varchar2,in_new_sal in number) is
begin
update emp set sal=in_new_sal where ename=in_name;
dbms_output.put_line('更新成功!');
end;
/
特别说明：当编写过程出现错误时，查看具体错误信息。输入show error;
java中调用过程
3、如何在java程序中调用一个存储过程？
动手体验：我们写一个java程序来调用前面的存储过程。
课堂小练习：编写一个过程，可以接受id和薪水，更新薪水，如果id不存在，需要在exception中捕获，并给出提示!
如何使用过程返回值？
特别说明：对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。
Java代码:
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestProcedure {
//调用oracle中update_sal存储过程
public static void main(String[] args) {
Connection ct=null;
CallableStatement cs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott", "tiger");
//创建CallableStatement接口引用对象
cs=ct.prepareCall("{call update_sal(?,?)}");
//给？赋值
cs.setString(1, "BOSS");
cs.setFloat(2, 8888f);
//执行我们的语句
cs.execute();
//提交
ct.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(cs!=null){
cs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
cs=null;
ct=null;
}
}
}
对前面的java程序的SQLHelper类进行升级，添加一个可以调用存储过程的方法
代码如下：
private static CallableStatement cs=null;
//调用存储过程的方法
public static void executeProcedure(String sql,String [] parameters){
try {
ct=DriverManager.getConnection(url,username,password);
cs=ct.prepareCall(sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++){
cs.setString(i+1, parameters[i]);
System.out.println(parameters[i]);
}
}
//执行
cs.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}finally{
close(rs, cs, ct);
}
}
调用方法：
//当我们需要去调用过程的时候传SQL语句
String sql="{call update_sal(?,?)}";
String paras[]={"BOSS","1520"};
SQLHelper.executeProcedure(sql, paras);
课堂小练习：编写一个过程，可以接受id和薪水，更新薪水，如果id不存在，需要在exception中捕获，并给出提示!需要在控制台和java程序中都调用。
oracle控制台
create procedure update_sal2(in_empno in number,in_new_sal in number) is
v_ename varchar2(32);
begin
select ename into v_ename from emp where empno=in_empno;
update emp set sal=in_new_sal where empno=in_empno;
dbms_output.put_line('更新成功!');
exception
when no_data_found then
dbms_output.put_line('输入的ID不存在!');
end;
/
pl/sql基本知识--函数快速入门
oracle函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例：
建立函数的基本语法：
create function 函数名(参数1,...)
return 数据类型 is
定义变量;
begin
执行语句;
end;
/
函数调用的基本语法：
var 变量名 变量类型
call 函数名(参数值,...) into :变量名;
print 变量名
select 函数名(参数,...) from dual;
案例：请编写一个函数，可以接收用户名并返回该用户的年薪。
create function inName_outSal(v_in_name varchar2)
return number is
v_annual_sal number;
begin
select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_name;
return v_annual_sal;
end;
/
函数和过程的区别：
1、函数必须有返回值，而过程可以没有；
2、函数和过程在java中调用的方式不一样；
java中调用oracle函数可以在select语句中直接调用，如：select 自定义的函数名(参数) from 表;
过程则是使用CallableStatement完成调用。
Java调用函数方式
package com.test;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestFunction {
//如何在java中调用自己编写的函数
public static void main(String[] args) {
String sql="select inName_outSal('KING') annual from dual";
ResultSet rs=SQLHelper.executeQuery(sql, null);
try {
if(rs.next()){
System.out.println(rs.getDouble("annual"));//此处可以用数字或别名接收返回值
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
rs=null;
}
}
}
pl/sql基本知识--包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1、我们可以使用create package命令来创建包：
建包基本语法：
create [or replace] package 包名 is
procedure 过程名(变量名 变量类型,...);
function 函数名(变量名 变量类型,...) return 数据类型;
end;
/
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范的过程和函数。
请编写一个包，该包有一个过程，该过程可以接收用户名和新的薪水。(将来用于通过用户去更新薪水)还有一个函数，该函数可以接收一个用户名(将来要实现得到该用户的年薪是多少)
create package emp_package is
procedure update_sal(v_in_ename varchar2,v_in_newsal number);
function inName_outSal(v_in_name varchar2) return number;
end;
2、建立包体可以使用create package body 命令
建立包体基本语法：
create or replace package body 包名 is
procedure 过程名(变量名 变量类型,...) is
--声明变量;
begin
--执行语句;
exception
when 异常名 then
--异常处理;
end;
function 函数名(变量名 变量类型,...)
return 数据类型 is
--声明变量;
begin
--执行语句;
end;
end;
/
案例：请实现前面定义的包中的过程和函数。
create or replace package body emp_package is
procedure update_sal(v_in_ename varchar2,v_in_newsal number) is
v_empno number;
begin
select empno into v_empno from emp where ename=v_in_ename;
update emp set sal=v_in_newsal where ename=v_in_ename;
dbms_output.put_line('员工号为：'||v_empno||'的薪水更新成功');
exception
when no_data_found then
dbms_output.put_line('您输入的人员信息不存在!');
end;
function inName_outSal(v_in_name varchar2)
return number is
v_annual_sal number;
begin
select (sal+nvl(comm,0))*13 into v_annual_sal from emp where
ename=v_in_name;
return v_annual_sal;
end;
end;
/
细节说明：
1、包体中要现实的函数或过程，应当在包规范中声明；
2、在调用包中的某个函数或过程的时候，需要使用对应的方法才可以调用。
3、如何调用包的过程或函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。
调用基本方法：
exec 方案名.包名.过程名(参数,...);
call 方案名.包名.函数名(参数,...);
也可以直接用select 方案名.包名.函数名(参数,...) from dual;
在java中调用oracle包下的过程和函数与之前java调用的过程和函数是一致的，只是尽可能的将方案名.包名加上。
String sql="{call scott.emp_package.update_sal(?,?)}";//过程
String sql="select scott.emp_package.inName_outSal('KING') annual from dual";//函数
特别说明：包是pl/sql中非常重要的部分，在使用过程分页时，将会再次体验它的威力。
pl/sql基础知识--触发器
触发器简单介绍
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert/update/delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger 来建立触发器。
特别说明：
我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的案例和一致性。
pl/sql基础知识--定义并使用变量
介绍
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1、标量类型(scalar)
2、复合类型(composite)
3、参照类型(reference)
4、lob(large object)
标量(scalar)--常用类型
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=|default expr]
名称 [指定常量] 数据类型 [不为null] [:=(赋初值) | default(默认值) expr(指定初始值)]
说明：
identifier:名称
constant:指定常量。需要指定它的初始值，且其值是不能改变的。
datatype:数据类型
not null:指定变量值不能为null
:=给变量或是常量指定初始值
default:用于指定初始值
expr:指定初始值的pl/sql表达式，可是文本值、其它变量、函数等。
标量定义的案例：
1、定义一个变长字符串
v_ename varchar2(10);
2、定义一个小数范围-9999.99～9999.99
v_sal number(6,2);
3、定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号
v_sal2 number(6,2):=5.4
4、定义一个日期类型的数据
v_hiredate date;
5、定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
特别说明：pl/sql在定义一个变量的时候，如果要赋初值，则需要使用:=，如果只是=则是用于判断两个值是否相等。
标量(scalar)使用标量
在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号(:=)
案例：以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写？
create or replace procedure emp_info(in_empno number) is
v_ename varchar2(32);
v_sal number;
v_tax number;
c_tax_rate number(3,2):=0.03;
begin
select ename,sal,sal*c_tax_rate into v_ename,v_sal,v_tax from emp where empno=in_empno;
dbms_output.put_line('姓名：'||v_ename||' 工资：'||v_sal||' 个人所得税：'||v_tax);
exception
when no_data_found then
dbms_output.put_line('你的输入有误!');
end;
/
标量(scalar)--使用%type类型
对于上面的pl/sql块有一个问题：
就是如果员工的姓名超过了5字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：
%type类型使用的基本语法：
标识符名 表名.列名%type;
create or replace procedure emp_info(in_empno number) is
v_ename emp.ename%type;--为了让v_ename的类型更加灵活，我们使用%type，这样就会自适应
v_sal emp.sal%type;
v_tax number;
c_tax_rate number(3,2):=0.03;
begin
select ename,sal,sal*c_tax_rate into v_ename,v_sal,v_tax from emp where empno=in_empno;
dbms_output.put_line('姓名：'||v_ename||' 工资：'||v_sal||' 个人所得税：'||v_tax);
exception
when no_data_found then
dbms_output.put_line('你的输入有误!');
end;
/
复合变量(composite)--介绍
用于存放多个值的变量。常用的包括：1、pl/sql记录；2、pl/sql表
复合类型--pl/sql记录
类似与高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
复合变量定义基本语法：
type 自定义的pl/sql记录名 is record(
变量名 变量类型,
变量名 变量类型
);
//使用自定义的pl/sql记录
复合变量基本使用语法：
变量名 自定义的pl/sql记录名;
请编写一个过程，该过程可以接收一个用户编号，并显示该用户的名字，薪水，工作岗位(注意：要求用pl/sql记录实现)
create or replace procedure inEmpno(in_empno number) is
--定义一个记录数据类型
type my_emp_record is record(
v_ename emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type
);
--定义一个变量，该变量的类型是my_emp_record
v_emp_record my_emp_record;
begin
select ename,sal,job into v_emp_record from emp where empno=in_empno;
dbms_output.put_line('名字：'||v_emp_record.v_ename||' 工资：'||v_emp_record.v_sal||' 职位：'||v_emp_record.v_job);
exception
when no_data_found then
dbms_output.put_line('你的输入有误!');
end;
/
复合类型--pl/sql表(了解即可)
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。(可以理解为是oracle下的数组)实例如下:
复合类型pl/sql表的基本语法：
type 自定义的pl/sql表名 is table of 对应表.列名%type
index by binary_integer;
//使用自定义的pl/sql表
变量名 自定义的pl/sql表名;
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;--定义一个变量：sp_table类型
begin
select ename into sp_table(-1) from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_table(-1));
end;
说明：
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
参照变量--介绍(重点，必须掌握)
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
游标变量
通过游标可以取得返回结果集(这个结果集，往往是select语句的结果)的任何一行数据，从而提供共享的效率。
参照变量--游标(ref cursor)使用
定义游标基本语法：
type 自定义游标名 is ref cursor;
变量名 自定义游标名;
打开游标基本语法：
open 游标变量 for select 语句;
取出当前游标指向的行基本语法：
fetch 游标变量 into 其它变量;
判断游标是否指向记录最后基本语法：
游标变量%notfound
参照变量--游标(ref cursor)变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时，(open时)需要指定select语句，这样一个游标就写一个select语句结合了。
实例如下：
1、请使用pl/sql编写一个过程，可以输入部门号，并显示该部门所有员工姓名和他的工资。
create or replace procedure test_cursor(in_deptno in number) is
--定义一个记录数据类型
type my_emp_record is record(v_ename emp.ename%type,v_sal emp.sal%type);
--定义一个变量，该变量的类型是my_emp_record
v_emp_record my_emp_record;
--先定义一个游标变量类型
type my_emp_cursor is ref cursor;
--定义一个游标变量
v_emp_cursor my_emp_cursor;
begin
--打开游标，执行语句
open v_emp_cursor for select ename,sal from emp where deptno=in_deptno;
--取出游标指向的每行数据，使用循环语句取出
loop
fetch v_emp_cursor into v_emp_record;--会引起游标向下走
--判断当前游标是否到达最后
exit when v_emp_cursor%notfound;--判断游标是否为空，为空时退出循环
dbms_output.put_line('姓名：'||v_emp_record.v_ename||' 工资：'||v_emp_record.v_sal);
end loop;
--关闭游标[游标使用完后，一定要关闭，避免资源浪费]
close v_emp_cursor;
end;
/
2、在1基础上，如果某个员工的工资低于200元，就增加100元。
create or replace procedure test_cursor(in_deptno in number) is
--创建一个记录数据类型
type my_emp_record is record(v_ename emp.ename%type,
v_sal emp.sal%type,
v_empno emp.empno%type
);
v_emp_record my_emp_record;
--创建游标
type my_emp_cursor is ref cursor;
v_emp_cursor my_emp_cursor;
begin
open v_emp_cursor for select ename,sal,empno from emp where deptno=in_deptno;
loop
fetch v_emp_cursor into v_emp_record;
exit when v_emp_cursor%notfound;
--判断工资低于200的人，加100块
if v_emp_record.v_sal<200 then
v_emp_record.v_sal:=v_emp_record.v_sal+100;
update emp set sal=v_emp_record.v_sal where empno=v_emp_record.v_empno;
end if;
dbms_output.put_line('姓名：'||v_emp_record.v_ename||' 工资：'||v_emp_record.v_sal);
end loop;
close v_emp_cursor;
end;
/
pl/sql练习题：
使用pl/sql块编程实现，注意必需的异常处理。
1、输入一个员工号，输出该员工的姓名、薪金和大概的服务年限(按年月日显示)
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_year varchar2(20);
begin
select ename,sal,to_char(to_date('00010101','yyyymmdd')+(sysdate-hiredate)-366-31,'yy"年"mm"个月"dd"天"') into v_ename,v_sal,v_year from emp where empno=&empno;
dbms_output.put_line('姓名：'||v_ename||' 薪金：'||v_sal||' 服务年限：'||v_year);
exception
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在!');
end;
/
2、接收一个员工号，输出该员工所在部门的名称
declare
v_ename emp.ename%type;
v_dname dept.dname%type;
begin
select e.ename,d.dname into v_ename,v_dname from emp e,dept d where e.deptno=d.deptno and empno=&empno;
dbms_output.put_line('姓名：'||v_ename||' 所在部门：'||v_dname);
exception
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在!');
end;
/
3、接收一个部门号，如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%；如果该员工职位是CLERK,并且在NEW YORK工作，那么就给他薪金扣除5%,其他情况不作处理。
declare
v_deptno number;
v_deptno1 dept.deptno%type;
v_deptno2 dept.deptno%type;
begin
v_deptno:=&deptno;
select deptno into v_deptno1 from dept where loc='DALLAS';
select deptno into v_deptno2 from dept where loc='NEW YORK';
if v_deptno=v_deptno1 then
update emp set sal=sal*1.15 where job='MANAGER' and deptno=v_deptno1;
dbms_output.put_line('已将job为MANAGER且在DALLAS工作的员工薪资提高了15%');
elsif v_deptno=v_deptno2 then
update emp set sal=sal*0.95 where job='CLERK' and deptno=v_deptno2;
dbms_output.put_line('已将job为CLERK且在NEW YORK工作的员工薪资降低了5%');
else
dbms_output.put_line('数据未做修改或输入的部门号不存在!');
end if;
end;
/
4、接收一个员工号，输出这个员工所在部门的平均工资
declare
v_deptno emp.deptno%type;
v_avgsal number(7,2);
begin
select deptno into v_deptno from emp where empno=&empno;
select avg(sal+nvl(comm,0)) into v_avgsal from emp where deptno=v_deptno;
dbms_output.put_line(v_deptno||'部门的平均工资为：'||v_avgsal);
exception
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在!');
end;
/
5、以交互的方式给部门插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样。
declare
v_deptno dept.deptno%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
v_deptno:=&deptno;
v_dname:=&dname;
v_loc:=&loc;
insert into dept (deptno,dname,loc) values(v_deptno,v_dname,v_loc);
dbms_output.put_line('部门编号：'||v_deptno||'部门名称：'||v_dname||'所在地：'||v_loc||'已成功添加!');
exception
when dup_val_on_index then
dbms_output.put_line('部门号已被占用!');
end;
/
过程函数练习题：
1、建立一个存储过程用来接收一个员工号，返回他的工资和他所在的部门的平均工资并作为传出参数传出。
create or replace procedure inempno_outsaldname(in_empno in number,out_sal out number,out_avgsal out number) is
v_deptno emp.deptno%type;
begin
select sal,deptno into out_sal,v_deptno from emp where empno=in_empno;
dbms_output.put_line('部门号：'||v_deptno);
select avg(sal) into out_avgsal from emp where deptno=v_deptno;
dbms_output.put_line('平均工资：'||out_avgsal);
end;
/
create or replace procedure in_out_emp(in_empno number) is
v_sal number;
v_avgsal number(7,2);
begin
inempno_outsaldname(in_empno,v_sal,v_avgsal);
dbms_output.put_line('员工号：'||in_empno||'工资为：'||v_sal||'平均工资为：'||v_avgsal);
exception
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在!');
end;
/
2、建立一个存储过程用来接收一个部门号，找出其中的两位最老的员工的员工号，并打印。
create or replace procedure indeptno_outhiredate(in_deptno number) is
type my_emp_cursor is ref cursor;
v_emp_cursor my_emp_cursor;
v_i number;
type my_emp_record is record(v_empno emp.empno%type,v_ename emp.ename%type,v_hiredate emp.hiredate%type);
v_emp_record my_emp_record;
begin
v_i:=0;
open v_emp_cursor for select empno,ename,hiredate from emp where deptno=in_deptno order by hiredate;
loop
fetch v_emp_cursor into v_emp_record;
v_i:=v_i+1;
if(v_i=3)then
exit;
end if;
dbms_output.put_line('员工编号：'||v_emp_record.v_empno||'姓名：'||v_emp_record.v_ename||'入职日期：'||to_char(v_emp_record.v_hiredate,'yyyy-mm-dd'));
end loop;
close v_emp_cursor;
exception
when no_data_found then
dbms_output.put_line('您输入的部门编号不存在!');
end;
/
3、编写一个过程用来传入一个员工号，在emp表中删除一个员工，当该员工是该部门的最后一个员工时就在dept表中删除该员工所在的部门。
create or replace procedure inempno_deldept(in_empno number) is
v_count number;
v_empno emp.empno%type;
v_deptno emp.deptno%type;
begin
v_empno:=in_empno;
select deptno into v_deptno from emp where empno=v_empno;
delete from emp where empno=v_empno;
dbms_output.put_line('工号：'||v_empno||'成功删除!');
select count(*) into v_count from emp where deptno=v_deptno;
dbms_output.put_line(v_deptno||'部门还有'||v_count||'雇员!');
if v_count=0 then
delete from dept where deptno=v_deptno;
dbms_output.put_line(v_deptno||'删除部门成功!');
end if;
exception
when no_data_found then
dbms_output.put_line('您输入的员工编号不存在!');
end;
/
pl/sql的进阶
Procedural Language/SQL叫做过程化SQL编程语言，是oracle对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以pl/sql就是把数据操作和查询语言组织在pl/sql代码的过程性单元中，通过逻辑判断，循环等操作实现复杂的功能或者计算的程序语言。
pl/sql进阶--控制结构
在任何计算机语言(c,java,c#,c++)都有各种控制语句(条件语句，循环语句，顺序控制结构..)在pl/sql中也存在这样的控制结构。
条件分支语句
pl/sql中提供了三种条件分支语句if--then,if--then--else,if--then--elsif--elsif--else这里我们可以和java语句进行一个比较。
简单的条件判断if--then
基本语法：
if 条件表达式 then
执行语句...;
end if;
编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%。
create or replace procedure inempno_upsal(in_ename varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=in_ename;
if v_sal<2000 then
update emp set sal=sal*1.1 where ename=in_ename;
end if;
exception
when no_data_found then
dbms_output.put_line('您输入的姓名不存在!');
end;
/
二重条件分支if--then--else
基本语法：
if 条件表达式 then
执行语句;
else
执行语句;
end if;
编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
create or replace procedure inname_upcomm(in_ename varchar2) is
v_comm emp.comm%type;
begin
select nvl(comm,0) into v_comm from emp where ename=in_ename;
if v_comm<>0 then
update emp set comm=comm+100 where ename=in_ename;
else
update emp set comm=comm+200 where ename=in_ename;
end if;
exception
when no_data_found then
dbms_output.put_line('您输入的姓名不存在!');
end;
/
多重条件分支if--then--elsif--else
基本语法：
if 条件表达式 then
执行语句;
elsif 条件表达式 then
执行语句;
else
执行语句;
end if;
编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200。
create or replace procedure inempno_upsal(in_empno number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=in_empno;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=in_empno;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=in_empno;
else
update emp set sal=sal+200 where empno=in_empno;
end if;
exception
when no_data_found then
dbms_output.put_line('您输入的编号有误!');
end;
/
循环结构--loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
基本语法：
loop
执行语句;
exit when 条件表达式;
end loop;
案例：现在有一张表users，表结构如下：
|
用户ID |
用户名 |
|
|
|
请编写一个过程，可以输入用户名和添加用户的个数n;循环添加n个用户到users表中，用户编号从1开始增加，直到n
create table users(userId number primary key,userName varchar2(32));
create or replace procedure inname_adduser(in_username varchar2,in_n number) is
v_i number:=0;
begin
loop
exit when in_n<=0;
v_i:=v_i+1;
insert into users values(v_i,in_username);
exit when v_i=in_n;
end loop;
end;
/
循环语句--while循环
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while..loop开始，以end loop结束。
基本语法：
while 条件表达式 loop
执行语句;
end loop;
案例：现在有一张表users，表结构如下：
|
用户ID |
用户名 |
|
|
|
请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。
create table users(userId number primary key,userName varchar2(32));
create or replace procedure inname_addusers(in_name varchar2) is
v_i number:=0;
v_j number:=10;
begin
while v_i<10 loop
v_j=v_j+1;
insert into users values(v_j,in_name);
end loop;
end;
/
看下面题判断是否正确
下面的过程是否正确，如果不正确，应该怎么改？
create or replace procedure sp_pro6(spName varchar2) is
v_test varchar2(40);
v_test:='aaa';--赋初值需在定义时就直接赋值，或在定义之后在begin中进行赋值。
begin
dbms_output.put_line(v_test);
end;
create or replace procedure sp_pro6(spName varchar2) is
v_test varchar2(40):='aaa';
begin
spName:='你好';--spName为传入变量，不能重复赋值。
dbms_output.put_line(v_test||spName);
end;
说明：
1、在is--begin之间只能定义变量类型同时初始化赋值，或定义变量类型后在begin内进行赋值，不能在定义变量类型之后再对变量赋值。
2、传入的参数变量不能在存储过程中再次赋值。
循环语句--for循环
基本for循环的基本结构如下：
begin
for i in reverse 1..10 loop
insert into users values(i,'顺平');
end loop;
end;
基本语法：
for 变量 in reverse 开始值..结束值 loop
执行语句;
end loop;
我们可以看到控制变量i,在隐含中就在不停的增加
注意：推荐使用loop循环结构，不推荐使用for循环。
顺序控制语句--goto,null
1、goto语句
goto语句用于跳转到特定标号去执行语句，注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。基本语法如下：goto lable，其中lable是已定义好的标号名。
基本语法：goto 标号;
标号定义：<<标号>>
例：
declare
i number:=1;
begin
<<start_loop>>
loop
dbms_output.put_line('输出i='||i);
if i=12 then
goto end_loop;
end if;
i:=i+1;
if i=10 then
goto start_loop;
end if;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
--输出1至12 循环结束。
2、null
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;
pl/sql进阶--编写分页过程
介绍
分页是任何一个网站(bbs、网上商城、blog)都会使用到的技术，因此学习pl/sql编程开发一定要掌握该技术。
无返回值的存储过程
古人云：欲速则不达，为了让大家比较容易接受分页过程编写，还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现在有一张表book,表结构如下：
字段名 字段类型
id number(5)
name varchar2(100)
pubHouse varchar2(100)
请编写一个过程，可以向book表添加书，要求通过java程序调用该过程。
提示查看jdk,看看CallableStatement是怎么调存储过程的!
建book表
create table book(id number(5) primary key,name varchar2(100) not null,pubHouse varchar2(100));
create or replace procedure inBook(in_id number,in_name varchar2,in_pubHouse varchar2)
begin
insert into book values(in_id,in_name,in_pubHouse);
exception
when dup_val_on_index then
dbms_output.put_line('错误：序号不能为空或不能重复!');
end;
java调用无返回值的存储过程代码:
//当我们需要去调用过程的时候传SQL语句
String sql="{call 存储过程名称(?,?)}";//调用格式{call 用户区.包名.过程名(参数?)}
String paras[]={"过程中的参数1","过程中的参数2"};
SQLHelper.executeProcedure(sql, paras);
public class SQLHelper {
//定义三个变量
private static Connection ct=null;
private static PreparedStatement ps=null;
private static ResultSet rs=null;
private static CallableStatement cs=null;
//连接数据库的用户名，密码，url，驱动
//说明：在实际开发中，我们往往把这些变量写到一个外部文件中
//当程序启动时，我们读入这些配置信息。java.util.Properites
private static String username;
private static String password;
private static String driver;
private static String url;
//使用静态块加载驱动(驱动只需要加载一次)
static{
//使用Properties类，来读取配置文件
Properties pp=new Properties();
FileInputStream fis=null;
try {
fis=new FileInputStream("dbinfo.properties");
//让pp与dbinfo.properties文件关联起来
pp.load(fis);
//获取dbinfo.properties文件内信息
username=pp.getProperty("username");
password=pp.getProperty("password");
driver=pp.getProperty("driver");
url=pp.getProperty("url");
//获得驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(fis!=null){
fis.close();
}
} catch (Exception e) {
e.printStackTrace();
}
fis=null;
}
}
//调用存储过程的方法
public static void executeProcedure(String sql,String [] parameters){
try {
ct=DriverManager.getConnection(url,username,password);
cs=ct.prepareCall(sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++){
cs.setString(i+1, parameters[i]);
System.out.println(parameters[i]);
}
}
//执行
cs.execute();
ct.commit();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}finally{
close(rs, cs, ct);
}
}
//把关闭资源写成函数
public static void close(ResultSet rs,Statement ps,Connection ct){
//关闭资源
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs=null;
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
ps=null;
}
if(ct!=null){
try {
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
ct=null;
}
}
}
dbinfo.properties 连接Oracle数据库配置文件
username=scott
password=tiger
driver=oracle.jdbc.driver.OracleDriver
url=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:orcl
有返回值的存储过程(非列表，只有一个返回值)
再看如何处理有返回值的存储过程：
建立有返回值的存储过程基本语法：
create or replace procedure 过程名(参数名 in 类型,..,参数名 out 类型,..) is
定义变量..;
begin
执行语句..;
exception
when 错误提示 then
处理或提示语句;
end;
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
create or replace procedure inEmpno_outEname(in_v_empno in number,out_v_ename out varchar2) is
begin
select ename into out_v_ename from emp where empno=in_v_empno;
exception
when no_data_found then
dbms_output.put_line('您输入的雇员编号不存在!');
end;
在java中去调用该过程，并接受返回的用户名。
[TestProcedureOutValues.java]源码示例：
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProcedureOutValues {
//调用oracle存储过程并获得存储过程的返回值。
public static void main(String[] args) {
//定义需要的变量
Connection ct=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//创建CallableStatement接口
cs=ct.prepareCall("{call inEmpno_outEname(?,?)}");//第一个?输入值，第二个?是获得值。
//给第一个?赋值
cs.setString(1, "7839");
//给第二个?注册(因为它是输出值)
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出输出的值
String ename=cs.getString(2);
System.out.println("用户的名字是："+ename);
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
if(cs!=null){
cs.close();
}
if(rs!=null){
rs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs=null;
rs=null;
ct=null;
}
}
}
说明：
1、对于过程的输入值，使用set方法，对于输出值使用registerOutParameter来注册接收返回值。问号的顺序要对应，同时考虑类型。
2、取出过程返回值的方法是CallableStatement提供的get方法(输出参数的位置)；同时要考虑输出的参数类型。
java调用关键代码：
CallableStatement cs=null;
cs=ct.prepareCall("{call 过程名(?,?)}");
cs.registerOutParameter(输出参数的在第几个问号,oracle.jdbc.OracleTypes.类型);//Types.类型是输出参数的类型。
cs.get类型(输出参数在问号的位置);//不用的类型要用不同的get方法接收。
案例扩展：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
create or replace procedure inEmpno_outAllinfo(in_v_empno in number,out_v_ename out varchar2,out_v_sal out number,out_v_job out varchar2) is
begin
select ename,sal,job into out_v_ename,out_v_sal,out_v_job from emp where empno=in_v_empno;
exception
when no_data_found then
dbms_output.put_line('您输入的雇员编号不存在!');
end;
在java中去调用该过程，并接受返回的用户名、工资、职位。
[TestProcedureOutValues.java]返回多个值的调用存储过程源代码。
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProcedureOutValues {
//调用oracle存储过程并获得存储过程的返回值。
public static void main(String[] args) {
//定义需要的变量
Connection ct=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//创建CallableStatement接口
cs=ct.prepareCall("{call inEmpno_outAllinfo(?,?,?,?)}");//第一个?输入值，第二个?是获得值。
//给第一个?赋值
cs.setString(1, "7839");
//给第二-四个?注册(因为它是输出值)
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出输出的值
String ename=cs.getString(2);
Float sal=cs.getFloat(3);
String job=cs.getString(4);
System.out.println("姓名："+ename+" 工资："+sal+" 职位："+job);
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
if(cs!=null){
cs.close();
}
if(rs!=null){
rs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs=null;
rs=null;
ct=null;
}
}
}
有返回值的存储过程(列表[结果集])
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
对该题分析如下：
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package了，步骤如下：
1、建一个包。
2、建立存储过程。
3、下面如何在java程序中调用。
创建包同时定义一个游标类型
create or replace package empPackage is
--定义一个游标数据类型
type my_emp_cursor is ref cursor;
end;
创建存储过程
create or replace procedure indeptno_outAllInfo(v_in_deptno in number,v_out_result out empPackage.my_emp_cursor) is
begin
open v_out_result for select * from emp where deptno=v_in_deptno;
--close v_out_result;--此处不能关闭游标，需要在程序中关闭游标。
end;
编写java程序获得存储过程返回的结果集。
[TestProcedureOutAllValues.java]源代码
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProcedureOutAllValues {
//调用oracle存储过程并获得存储过程的返回结果集。
public static void main(String[] args) {
//定义需要的变量
Connection ct=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//创建CallableStatement接口
cs=ct.prepareCall("{call indeptno_outAllInfo(?,?)}");
//给?赋值
cs.setInt(1, 20);
//给第二个?注册
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
/*这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以。*/
rs=(ResultSet)cs.getObject(2);
//循环取出
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
if(cs!=null){
cs.close();
}
if(rs!=null){
rs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs=null;
rs=null;
ct=null;
}
}
}
编写分页过程
有了上面的基础，相信大家可以完成分页存储过程了。
要求：请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页，排序字段(deptno降序)。返回总记录数，总页数和返回的结果集。
把一个字符串，当做sql语句执行，并把查询得到的结果赋给某个变量，语法如下：
execute immediate v_sql into myrows;
基本语法：
execute immediate 变量(sql拼接语句) into 输出变量名;
温馨提示：
如果大家忘了oracle中如何分页，请参考第三天的内容
提示：为了讲的清楚明白，这里使用循序渐进的方法，逐步增加传入的参数来讲解。
先简化再复杂
通过输入表名、每页显示记录数、当前页，返回结果集。
1、创建包同时创建游标
create or replace package pagingPackage is
type paging_cursor is ref cursor;
end;
2、创建分页存储过程
create or replace procedure paging_cursor(v_in_table in varchar2,v_in_pagesize in number,v_in_pagenow in number,v_out_result out pagingPackage.paging_cursor) is
--定义需要的变量
v_sql varchar2(4000);
v_start number;
v_end number;
begin
--执行代码
--计算v_start和v_end是多少
v_start:=v_in_pagesize*(v_in_pagenow-1)+1;
v_end:=v_in_pagesize*v_in_pagenow;
v_sql:='select t2.* from (select t1.*,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标，让游标指向结果集
open v_out_result for v_sql;
end;
java调用分页存储过程
[TestProcedurePaging.java]源代码
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProcedurePaging {
//调用oracle分页存储过程并获得存储过程的返回结果集。
public static void main(String[] args) {
//定义需要的变量
Connection ct=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//创建CallableStatement接口
cs=ct.prepareCall("{call paging_cursor(?,?,?,?)}");
//给in?赋值
cs.setString(1,"emp");//传表名
cs.setInt(2, 6);//传入pagesize，每页显示多少条记录
cs.setInt(3, 1);//传入pagenow，显示第几页。
//给out?注册
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
/*这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以。*/
rs=(ResultSet)cs.getObject(4);
//循环取出
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
if(cs!=null){
cs.close();
}
if(rs!=null){
rs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs=null;
rs=null;
ct=null;
}
}
}
要求：请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页，排序字段(deptno降序)。返回总记录数，总页数和返回的结果集。
1、创建包同时创建游标
create or replace package pagingPackage is
type paging_cursor is ref cursor;
end;
2、创建分页存储过程
create or replace procedure paging_cursor(v_in_table in varchar2,v_in_pagesize in number,v_in_pagenow in number,v_out_result out pagingPackage.paging_cursor,v_out_rows out number,v_out_pagecount out number) is
--定义需要的变量
v_sql varchar2(4000);
v_sql_select varchar2(4000);
v_start number;
v_end number;
begin
--执行代码
--计算v_start和v_end是多少
v_start:=v_in_pagesize*(v_in_pagenow-1)+1;
v_end:=v_in_pagesize*v_in_pagenow;
v_sql:='select t2.* from (select t1.*,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标，让游标指向结果集
open v_out_result for v_sql;
--查询共有多少条记录
v_sql_select:='select count(*) from '||v_in_table;
execute immediate v_sql_select into v_out_rows;
--统计多少页记录
if mod(v_out_rows,v_in_pagesize)=0 then
v_out_pagecount:=v_out_rows/v_in_pagesize;
else
v_out_pagecount:=v_out_rows/v_in_pagesize+1;
end if;
end;
java调用分页存储过程(完整)源代码
[TestProcedurePaging.java]源代码
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestProcedurePaging {
//调用oracle分页存储过程并获得存储过程的返回结果集。
public static void main(String[] args) {
//定义需要的变量
Connection ct=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//创建CallableStatement接口
cs=ct.prepareCall("{call paging_cursor(?,?,?,?,?,?)}");
//给in?赋值
cs.setString(1,"emp");//传表名
cs.setInt(2, 6);//传入pagesize，每页显示多少条记录
cs.setInt(3, 1);//传入pagenow，显示第几页。
//给out?注册
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
//执行
cs.execute();
/*这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以。*/
rs=(ResultSet)cs.getObject(4);
//循环取出
while(rs.next()){
System.out.println(rs.getString("ename")+" "+rs.getString("sal"));
}
//取出总记录数
int rowCount=cs.getInt(5);
//取出总页数
int pageCount=cs.getInt(6);
System.out.println("共有记录："+rowCount+"条! "+"共有记录："+pageCount+"页!");
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
if(cs!=null){
cs.close();
}
if(rs!=null){
rs.close();
}
if(ct!=null){
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs=null;
rs=null;
ct=null;
}
}
}
