数据库之存储过程
1.存储过程语法
Oracle存储过程基本语法存储过程
1 CREATE OR REPLACE PROCEDURE 存储过程名
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
行2:
IS关键词表明后面将跟随一个PL/SQL体。
行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;
行5:
END关键词表明PL/SQL体的结束
存储过程创建语法：
create orreplace procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;--vs_msg VARCHAR2(4000);
变量2 类型（值范围）;
Begin
Select count(*)into 变量1 from 表A where列名=param1；
If (判断条件) then
Select 列名 into 变量2 from 表A where列名=param1；
Dbms_output。Put_line(‘打印信息');
Elsif (判断条件) then
Dbms_output。Put_line(‘打印信息');
Else
Raise 异常名（NO_DATA_FOUND）;
End if;
Exception
When others then
Rollback;
End;
例子2
create orreplace procedure EMP_PROCEDURE2(
eno number, --输入参数，
name varchar2,
)
IS
emp_null_error EXCEPTION; --声明异常变量
PRAGMA EXCEPTION_INIT(emp_null_error,-1400);--非预定义异常，前提：deptno列非空。插入空值会报错
emp_no_deptno EXCEPTION; --声明异常变量
PRAGMA EXCEPTION_INIT(emp_no_deptno, -2291);--非预定义异常，
begin
insert into emp(empno,ename,sal,deptno)values (eno,name,sal,dno);
exception
when DUP_VAL_oN_INDEX then
RAISE_APPLICATION_ERROR(-20000,'该雇员已存在');
when emp_null_error then
RAISE_APPLICATION_ERROR(-20001,'部门编号不能为空');
when emp_no_deptno then
RAISE_APPLICATION_ERROR(-20002,'不存在该部门编号');
end;
EXECUTEEMP_PROCEDURE2(1001,'荣世林',2000,10);
注意事项：
1， 存储过程参数不带取值范围，in表示传入，out表示输出
类型可以使用任意Oracle中的合法类型。
2， 变量带取值范围，后面接分号
3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4， 用select 。。。into。。。给变量赋值
5， 在代码中抛异常用 raise+异常名
2.CallableStatement 执行存储过程
概述
如果想要执行存储过程，我们应该使用 CallableStatement 接口。
CallableStatement 接口继承自 PreparedStatement 接口。所以 CallableStatement 接口
包含有 Statement 接口和 PreparedStatement 接口定义的全部方法，但是并不是所有的方法我
们都要使用，主要使用的方法有这样几个：
2.1常用方法
|
返回类型 |
方法签名 |
说明 |
|
boolean |
execute() |
执行 SQL 语句，如果第一个结果是 ResultSet 对 象，则返回 true；如果第一个结果是更新计数或者没 有结果，则返回 false |
|
void |
registerOutParameter (int parameterIndex, int sqlType) |
按顺序位置 parameterIndex 将 OUT 参数注册为 JDBC 类型 sqlType，sqlType 为 Types 类中的常量 |
|
Type |
getType(int parameterIndex) |
根据参数的序号获取指定的 JDBC 参数的值。第一 个参数是 1，第二个参数是 2，依此类推 |
我们可以使用 execute()方法来执行存储过程。CallableStatement 为所有的数据库提供了
一种统一的标准形式调用存储过程。所以，你将会看到我们使用 execute()调用存储过程的语
法与在 Oracle 中会所有不同。
为了获得存储过程或函数的返回值，我们需要使用registerOutParameter()方法将返回的
参数注册为 JDBC 的类型。 registerOutParameter()方法的第一个参数是参数的序号，第一个
为 1，第二个为 2，以此类推。第二个参数需要一个 int 值，用来标记 JDBC 的类型，我们可以
使用 java.sql.Types 类中的常量来设置这个参数。比如 VARCHAR、DOUBLE 等类型。如果类型不
够用，也可以从具体数据库的驱动中寻找合适的类型常量。如果存储过程或函数有返回值，这
个方法是必须要调用的，否则无法得到返回值，甚至会发生异常。
CallableStatement 接口中定义了很多 get 方法，用于获取存储过程返回的值，根据值的
类型不同，你可以使用不同 get 方法，比如 getInt()、getString()、getDouble()等等。
我们看一下使用 CallableStatement 接口执行存储过程和函数的语法格式。
存储过程：{call<procedure-name>[(<arg1>,<arg2>, ...)]}
函数：{?= call<procedure-name>[(<arg1>,<arg2>, ...)]}
如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call 后面是过程名，
如果没有参数，可以省略小括号。
如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际上这个问号就是一
个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同。
这样说起来可能比较抽象，我们通过代码来学习一下。
2.2执行存储过程
（1） 执行不带参但是有返回值的存储过程
存储过程代码如下：
create or replaceprocedure getNewsCount(v_totalCount out number) as
begin
select count(*) intov_totalCount from news_detail;
end;
该 存 储 过 程 为 查 询 新 闻 明 细 表 (news_detail) 中新 闻 的 总 记 录 数 ， 并 将 结 果 存 储 在
v_totalCount 中返回。执行该存储过程的代码如下：
//获取新闻总数量（执行存储过程）
public intgetTotalCountProc(){
int totalCount=0;
CallableStatementproc=null;
Stringsql="{call getNewsCount(?)}";
getConnection();
try {
proc=conn.prepareCall(sql);
proc.registerOutParameter(1,Types.INTEGER);
proc.execute();
totalCount=proc.getInt(1);
} catch (SQLExceptione) {
e.printStackTrace();
}
return totalCount;
}
（2） 执行带参带返回值的存储过程
存储过程代码如下：
create or replaceprocedure getNewsCount(v_categoryId in number,v_title in varchar2,v_totalCount out number) as
begin
if (v_categoryId = 0)then
select count(*) intov_totalCount from news_detail
where title like'%'||v_title||'%';
else
select count(*) intov_totalCount from news_detail
where categoryId =v_categoryId
and title like'%'||v_title||'%';
end if;
end;
该存储过程为根据新闻类别 ID（categoryId）以及新闻标题(title)查询新闻明细表
(news_detail)中新闻的总记录数，并将结果存储在 v_totalCount 中返回。其中，新闻类别以
及新闻标题分别以输入参数的形式传入存储过程。执行该存储过程的代码如下：
//根据新闻类别 ID 以及新闻标题获取新闻总记录数
public intgetTotalCount(int categoryId, String title) {
int iCount = 0;
CallableStatementproc = null;
try {
String sql ="{call getNewsCount(?,?,?)}";
getConnection();
proc =conn.prepareCall(sql);
proc.setInt(1,categoryId);
proc.setString(2,title);
proc.registerOutParameter(3,Types.INTEGER);
proc.execute();
iCount =proc.getInt(3);
} catch (Exception e){
e.printStackTrace();
}finally{
if (proc != null){
try {
proc.close();
} catch (SQLExceptione) {
e.printStackTrace();
}
}
closeResource();
}
return iCount;
}
（3） 执行返回值为游标的存储过程
Employees 雇员表（name 雇员姓名;gender 雇员性别;borndate 雇员生日）
存储过程代码如下：
create or replaceprocedure find_emp3(emp_cursor out sys_refcursor) is
begin
open emp_cursor forselect name,gender,borndate from employees;
end find_emp3;
该存储过程为查询所有雇员姓名、性别、生日的存储过程，结果以游标的形式返回。执行
该存储过程的代码如下：
String sql ="{call find_emp3(?)}";
Connection con =null;
CallableStatementcstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager 个.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl","pbdevj","pwd1234");
cstmt =con.prepareCall(sql);
cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs =(ResultSet)cstmt.getObject(1);
while (rs.next()) {
//省略
}
} catch { //省略 } finally { //省略 }
（4） 执行函数
函数代码如下：
create or replacefunction find_emp2(emp_no number) return varchar2 is
empname varchar2(20);
|
begin
select name intoempname from employees where id=emp_no;
return empname;
exception
when no_data_foundthen
return '雇员编号未找到';
end find_emp2;
该函数功能为根据雇员 id 返回姓名。执行该函数的代码如下：
String sql ="{?=call find_emp2(?)}";
Connection con =null;
CallableStatementcstmt = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
con =DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl","pbdevj","pwd1234");
cstmt =con.prepareCall(sql);
cstmt.setInt(2, 1);
cstmt.registerOutParameter(1,Types.VARCHAR);
cstmt.execute();
System.out.println(cstmt.getString(1));
} catch { //省略 } finally { //省略 }
