通过对Oracle存储过程的学习与研究,并记录了下来,后面会有例子。
首先还是先看看基础语法吧:
Oracle存储过程
一:oracle 存储过程的基本语法
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
--xxx
END 存储过程名字
【注意:】如果没有or replace语句,则仅仅是新建一个存储过程。如果系统存在该存储过程,则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个,如果系统中有此存储过程则把原来删除掉,重新创建一个存储过程。
参数的数据类型只需要指明类型名即可,不需要指定宽度。
参数的宽度由外部调用者决定。
过程可以有参数,也可以没有参数
变量声明块:紧跟着的as (is )关键字,可以理解为pl/sql的declare关键字,用于声明变量。
变量声明块用于声明该存储过程需要用到的变量,它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
过程语句块:从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
异常处理块:关键字为exception ,为处理语句产生的异常。该部分为可选
结束块:由end关键字结果。
2.SELECT INTO STATEMENT
将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子:
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.变量赋值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
二:关于oracle存储过程的若干问题备忘
1.在oracle中,数据表别名不能加as,如:
select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into,正确编译
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into,编译报错,提示:Compilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement
3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no data found"异常。
可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错,提示
ORA-01422:exact fetch returns more than requested number of rows
5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键
);
如果在存储过程中,使用如下语句:
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
if fcount is null then
fcount:=0;
end if;
这样就一切ok了。
6.Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
CallableStatement cs = session
.connection()
.prepareCall("{call modifyapppnumber_remain(?)}");
cs.setString(1, foundationid);
cs.execute();
return null;
}
});
例子:
首先建表:
create table tab_1
(
id varchar(11) primary key,
name varchar(10),
password varchar(10)
)
添加数据:
insert into TAB_1 (ID, NAME, PASSWORD)
values ('1', '小米', '123');
insert into TAB_1 (ID, NAME, PASSWORD)
values ('2', '小明', '452');
insert into TAB_1 (ID, NAME, PASSWORD)
values ('3', '小华', '145');
无返回值:
**过程测试一**
create or replace procedure test is
begin
Update tab_1 t set t.name='小明' where t.name='小华'
end test
【经过测试发现:create or replace procedure test is这里可以使用’is’ 或者’as’】
报错:
PROCEDURE ZYSMS.TEST 编译错误
错误:PL/SQL: ORA-00933: SQL 命令未正确结束
行:3
文本:Update tab_1 t set t.name='小李' where t.name='小明'
错误:PL/SQL: SQL Statement ignored
行:3
文本:Update tab_1 t set t.name='小李' where t.name='小明'
错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
行:4
文本:end test
解决过程:是由于更新SQL语句后没有加分号”;”
接着执行:
create or replace procedure test is
begin
Update tab_1 t set t.name='小明' where t.name='小华';
end test
报错:PROCEDURE ZYSMS.TEST 编译错误
错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
符号 ";" 被替换为 "end-of-file" 后继续。
行:4
文本:end test
解决过程:由于在end test(当前你的存储过程名) 后没有加结束的分号”;”。
运行:编译成功!
Test存储过程代码:
create or replace procedure test is
begin
update tab_1 t set t.name='小明' where t.name='小华';
end test;
测试代码:
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,name, password);
CallableStatement proc = null;
proc = conn.prepareCall("{ call test() }"); //设置存储过程
proc.execute();//执行
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
运行结果:
**过程测试二**
带返回值:
【1】返回指定参数:
Test2存储过程代码:
create or replace procedure test2(var_id in number, var_out_name out varchar2) as
begin
select t.name into var_out_name from tab_1 t where t.id=var_id;
end test2;
测试代码:
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test2(?,?) }”); //设置存储过程
proc.setString(1, “2”);//设置第一个参数输入参数
proc.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
proc.execute();//执行
String var_out_name = proc.getString(2);//获得输出参数
System.out.println(“名字 : “+var_out_name);
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
【注意事项:】
1.对于存储过程的输入参数,使用setXXX;对于输出参数,使用registerOutParameter
,问号的顺序要对应,同时需要考虑类型。
2.取出存储过程返回值的方法是CallableStatement提供的getXX()注意输出参数的位置,
同时也需要考虑输出参数的类型。
运行结果:名字 : 小华
**过程测试三**
【2】返回列表
由于Oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用package了,所以要分为两部分:
步骤一:新建一个程序包
create or replace package TESTPACKAGE as
type Test_CURSOR is ref cursor;
end TESTPACKAGE;
步骤二:到这可以创建存储过程:
create or replace procedure test3(p_cursor out TESTPACKAGE.Test_CURSOR) as
begin
open p_cursor for select * from tab_1;
end test3;
测试代码:
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test3(?) }”); //设置存储过程
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println(” ID: ” + rs.getString(1) + ” 名字: “+rs.getString(2)+” 密码: “+rs.getString(3));
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
运行结果:
ID: 1 名字: 小米 密码: 123
ID: 2 名字: 小华 密码: 452
ID: 3 名字: 小组 密码: 145
**过程测试四**
存储过程加入IF语句:
存储过程Test5:
先创建包:【在下篇文章详细介绍Oracle-Package】
create or replace package TESTPACKAGE as
type Test_CURSOR is ref cursor;
end TESTPACKAGE;
再建存储过程:
create or replace procedure test5(var_id in varchar2,var_outs out TESTPACKAGE.Test_CURSOR) as
id varchar2(10):=’01’;
begin
if id=var_id then
begin
open var_outs for select * from tab_1;
end;
end if;
end test5;
测试代码:
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,name,password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test5(?,?) }”); //设置存储过程
proc.setString(1,”01”);
proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();//执行
rs=(ResultSet) proc.getObject(2);
while(rs.next()){
System.out.println(” ID: ” + rs.getString(1) + ” 名字: “+rs.getString(2)+” 密码: “+rs.getString(3));
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
这里id值设置为“01”【正确值】时运行结果:
这里id值设置为“02”【错误值】时运行结果:
运行报:Ref 游标无效
**过程测试五**
调存储过程时如果传List集合测试:
test6存储过程:
【1】CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF VARCHAR2(32);
【2】create or replace procedure test6(arr_t in tables_array) as
begin
for i in arr_t.first .. arr_t.last loop
-- insert into test_table values(arr_t(i));
update tab_1 t set t.password='001' where t.id=arr_t(i);
end loop;
commit;
end test6;
【3】测试代码:
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,name,password);
CallableStatement proc = null;
proc = conn.prepareCall("{ call test6(?) }"); //设置存储过程
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY",conn);
List list=new ArrayList<>();
list.add("1");
list.add("3");
ARRAY array = new ARRAY(descriptor,conn,list.toArray());
proc.setArray(1, array);
proc.execute();//执行
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
运行结果:
我的技术群这里给大家分享下:472148690
在下一篇我会分享一个大批量数据插入的性能测试。