小青蛙oracle跟踪,Oracle 存储过程

一、存储过程结构：

1.创建语句：

create [or replace] procedure 存储过程名(入参 [in|out] 入参类型[,入参 [in|out] 入参类型])

is

定义变量区

变量名 变量类型[:=变量值];

begin

PL/SQL 程序块

Exception

When 具体异常/自定义异常 then

处理逻辑

When others then

其他异常处理逻辑

Rollback; --回滚没有commit的数据

end[存储过程名];

2.结构解析：

我们对存储过程的结构进行进一步的解释：

a) 使用"[]"包含的语句是可写可不写的，写或不写大部分情况下都是有区别的，比如or replace， 看单词含义就知道，如果写了这个语句，系统存在同名存储过程，执行该创建语句将会覆盖原有存储过程。

b) 参数: 参数名 [in|out] 参数类型

--in表示入参,在存储过程中不可赋值;类似于java方法中的final 入参；

--out表示出参,在传入存储过程之前的任何赋值都无效,如果在存储过程中未被赋值,则出参为空；

--都有表示即为入参也为出参,可以理解在java中传入方法的变量,存储过程中可以使用,也可以改变其值；

--可以同时使用,也可以单独使用,也可以不使用,不使用默认为in；

c) 定义变量区：

在存储过程中使用的变量，必须在此定义(隐式游标除外)并且指定宽度，定义变量大致是这个结构：变量名 变量类型[:=变量值]；适用于大部分数据库已有的变量类型，如varchar2，number，date等等，但是当游标等特殊数据时，会使用到特定结构，这个后续增加章节细说。

d) PL/SQL程序块

也叫逻辑处理模块，是由各种语法组成、对数据库对象进行操作的模块，后面会细说。

e) 异常处理模块

异常处理模块由固定语法组成，可以捕获系统的具体异常(如no_data_found,too_many_rows等等)，也可以在变量区定义自定义异常，在程序块中主动抛出，在异常处理模块捕获：

定义异常变量： v_exception excetion;

程序块中抛出： raise v_exception;

异常处理模块捕获： when v_exception then dbms_output.putline("抛出异常");

f) 结束语句 由关键词end [存储过程名] 结束存储过程。

3.包和存储过程

包也是oracle中的一种对象类型，它可以包含存储过程，自定义类型，自定义游标等等，拓展存储过程的用法。

后续再增加章节细说。

二、存储过程中使用的语法

1.变量赋值语句:

a)变量名:=变量值;

b) select XX into 变量名 from tableName where ...;

c) 动态语句赋值: execute immediate '只查询出一个值的SQL语句'  into 变量名;

2.条件语句(注意Oracle中的相等条件是用单独一个等号"=",如 if v_num = 0 then ...)

a) if 条件 [and|or 条件] then

逻辑处理语句;

elsif 条件 [and|or 条件] then

逻辑处理语句;

else

逻辑处理语句;

end if;

b)case 变量

when 变量值 then

逻辑处理语句;

when  变量值 then

逻辑处理语句;

end case;

c) case when 条件 [and|or 条件] then 逻辑处理语句; end case;

3.循环语句

a) 普通for循环(其实这是一个隐式游标)

for i in 0..100

loop

dbms_output.putline(i);

end loop;

b)普通while循环

while 条件 [and|or 条件]

loop

逻辑处理;

end loop;

c) 隐式游标循环

for data in select * from tableName

loop

dbms_output.putline(data.colName||'---'||data.colName);

end loop;

4.普通DML语句

直接写就可以了，但是如果要提交的话需要在语句结束后加上commit;

5.DDL语句

存储过程中是不可以直接执行DDL语句的，但是可以通过动态语句来执行：

execute immediate 'DDL语句字符串';

如果语句中含有 “ ' ” 单引号在拼写的时候 使用 '' 即可

如:

6.调用存储过程

declare

定义出参,入参

begin

存储过程名(出入参);

end;

三、java中调用

在java中调用存储过程和调用其他SQL方式一样，唯一不同的是语句，存储过程的语句是 call 存储过程名(?,?)

我这里只说明一下基本的数据库连接三对象的操作，由于现在基本上没有人使用这个来对数据库进行操作，都是使用各框架封装好的，所以各框架对存储过程的调用请自行查询:

1.无返回值的存储过程

public static void main(String[] args ){

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:test";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

CallableStatement cstmt = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "user", "password");

CallableStatement proc = null;

proc = conn.prepareCall("{ call 存储过程名(?,?) }");

//设置入参

proc.setString(1, "100");

proc.setString(2, "TestOne");

//执行

proc.execute();

}catch (Exception e) {

e.printStackTrace();

}finally{

//各种close

}

}

2.有返回值的存储过程(非列表)

public static void main(String[] args ){

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:test";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "user", "password");

CallableStatement proc = null;

proc = conn.prepareCall("{ call 存储过程名(?,?) }");

//设置入参

proc.setString(1, "100");

//设置出参类型

proc.registerOutParameter(2, Types.VARCHAR);

//执行

proc.execute();

//获取出参值

String testPrint = proc.getString(2);

System.out.println(testPrint);

}catch (Exception e) {

e.printStackTrace();

}finally{

//各种close

}

}

注意，这里的proc.getString(2)中的数值2是指存储过程第二个参数。

基本的存储过程使用差不多就是这些了，下面写一个testProcedure来说明一下：

create or replace procedure testProcedure(p_date varchar2,o_code out varchar2)

is

--定义变量 可以直接初始化

v_date date:=to_date(p_date,'yyyyMMdd');

--后续可操作变化

v_message varchar2(4000);

v_step varchar2(100);

v_tablename varchar2(200):='test_user';

v_flag number:=1;

v_exception exception;

begin

v_step:='1';

--调试输出

DBMS_OUTPUT.put_line(to_char(v_date,'yyyy-MM-dd HH24:mi:ss'));

v_message:='Hello World!';

--循环

v_step:='2';

for i in 0..100

loop

DBMS_OUTPUT.put_line(v_message||' '||i);

end loop;

while v_flag<100

loop

v_flag:=v_flag+1;

DBMS_OUTPUT.put_line(v_message||'v_flag '||v_flag);

end loop;

case v_step

when '2'then

DBMS_OUTPUT.put_line(v_message||''||2);

when '3'then

DBMS_OUTPUT.put_line(v_message||''||3);

end case;

case when v_step='2' then

DBMS_OUTPUT.put_line(v_message||''||2);

end case;

v_step:='3';

select count(*) into v_flag from user_tables where table_name = v_tablename;

--动态语句

v_step:='4';

execute immediate 'select count(*) from user_tables where table_name=upper('''||v_tablename||''')' into v_flag;

if v_flag=0 and v_flag!=1 then

--不存在就创建表

v_step:='5';

execute immediate 'create table '||v_tablename||'(id varchar2(32),username varchar2(100),password varchar2(32))';

o_code:='创建表~';

else

--存在就删掉表

v_step:='6';

execute immediate 'drop table '||v_tablename;

o_code:='删除表~';

raise v_exception;

end if;

Exception

when v_exception then

v_message:= sqlcode || sqlerrm;

dbms_output.put_line('测试一下自定义异常报错捕获');

dbms_output.put_line(v_message);

When others then

v_message:= sqlcode || sqlerrm;

dbms_output.put_line('在第'||v_step||'步报错:');

dbms_output.put_line(v_message);

Rollback;

end testProcedure;

这个存储过程包含了一些基本语法和操作，可以用来做参考