数据库之存储过程
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关键词
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
明后面将跟随一个PL/SQL体。 行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少
需要有一句;
行5:
END关键词表明PL/SQL体的结束
存储过程创建语法:
create or replace 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 or replace 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;
EXECUTE EMP_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常用方法
返回类型 方法签名 说明
执行 SQL 语句，如果第一个结果是 ResultSet 对 boolean execute()
象，则返回 true;如果第一个结果是更新计数或者没
有结果，则返回 false
按顺序位置 parameterIndex 将 OUT 参数注册为 void registerOutParameter
JDBC 类型 sqlType，sqlType 为 Types 类中的常量 (int parameterIndex,
int sqlType)
根据参数的序号获取指定的 JDBC 参数的值。第一 Type getType(int
个参数是 1，第二个参数是 2，依此类推 parameterIndex)
我们可以使用 execute()方法来执行存储过程。CallableStatement 为所有
的数据库提供了
一种统一的标准形式调用存储过程。所以，你将会看到我们使用 execute()调
用存储过程的语
法与在 Oracle 中会所有不同。
为了获得存储过程或函数的返回值，我们需要使用 registerOutParameter()
方法将返回的
参数注册为 JDBC 的类型。 registerOutParameter()方法的第一个参数是参数
的序号，第一个
为 1，第二个为 2，以此类推。第二个参数需要一个 int 值，用来标记 JDBC
的类型，我们可以
使用 java.sql.Types 类中的常量来设置这个参数。比如 VARCHAR、DOUBLE
等类型。如果类型不
够用，也可以从具体数据库的驱动中寻找合适的类型常量。如果存储过程或函
数有返回值，这
个方法是必须要调用的，否则无法得到返回值，甚至会发生异常。
CallableStatement 接口中定义了很多 get 方法，用于获取存储过程返回
的值，根据值的
类型不同，你可以使用不同 get 方法，比如 getInt()、getString()、getDouble()等等。
我们看一下使用 CallableStatement 接口执行存储过程和函数的语法格
式。
存储过程:{call
[(,, ...)]}
函数:{?= call [(,, ...)]}
如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call
后面是过程名，
如果没有参数，可以省略小括号。
如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际
上这个问号就是一
个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同。
这样说起来可能比较抽象，我们通过代码来学习一下。
2.2执行存储过程
(1) 执行不带参但是有返回值的存储过程
存储过程代码如下:
create or replace procedure getNewsCount(v_totalCount out number) as
begin
select count(*) into v_totalCount from news_detail;
end;
该 存 储 过 程 为 查 询 新 闻 明 细 表 (news_detail) 中新 闻 的 总
记 录 数 ， 并 将 结 果 存 储 在
v_totalCount 中返回。执行该存储过程的代码如下:
//获取新闻总数量(执行存储过程)
public int getTotalCountProc(){
int totalCount=0;
CallableStatement proc=null;
String sql="{call getNewsCount(?)}";
getConnection();
try {
proc=conn.prepareCall(sql);
proc.registerOutParameter(1, Types.INTEGER);
proc.execute();
totalCount=proc.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return totalCount;
}
(2) 执行带参带返回值的存储过程
存储过程代码如下:
create or replace procedure getNewsCount(v_categoryId in number,v_title
in varchar2,v_totalCount out number) as begin
if (v_categoryId = 0) then
select count(*) into v_totalCount from news_detail
where title like '%'||v_title||'%';
else
select count(*) into v_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 int getTotalCount(int categoryId, String title) {
int iCount = 0;
CallableStatement proc = 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 (SQLException e) {
e.printStackTrace();
}
}
closeResource();
}
return iCount;
}
(3) 执行返回值为游标的存储过程
Employees 雇员表(name 雇员姓名;gender 雇员性别;borndate 雇员生日) 存储过程代码如下:
create or replace procedure find_emp3(emp_cursor out sys_refcursor) is
begin
open emp_cursor for select name,gender,borndate from employees;
end find_emp3;
该存储过程为查询所有雇员姓名、性别、生日的存储过程，结果以游标的形式返回。执行
该存储过程的代码如下:
String sql = "{call find_emp3(?)}"; Connection con = null;
CallableStatement cstmt = 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 replace function find_emp2(emp_no number) return varchar2 is
empname varchar2(20);
begin
select name into empname from employees where id=emp_no;
return empname;
exception
when no_data_found then
return '雇员编号未找到';
end find_emp2;
该函数功能为根据雇员 id 返回姓名。执行该函数的代码如下:
String sql = "{?=call find_emp2(?)}"; Connection con = null;
CallableStatement cstmt = 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 { //省略 }
浙公网安备 33021202002483