oracle存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1[model] datatype1, parameter2 [model] datatype2..)]IS[AS]BEGIN    PL/SQL;END [procedure_name];

说明：

　　1. parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型

　　2. 定义存储过程的参数时，只能指定数据类型，不能指定数据长度

　　3. IS/AS用于开始PL/SQL代码块

　　4. 创建存储过程时，既可以指定参数也可以不指定任何参数；

　　5. 存储过程参数：1）输入参数 IN   　IN用于接收调用环境的输入参数（创建存储过程时，输入参数的IN可以省略）

　　　　　　　　　　 2) 输出参数 OUT 　OUT用于将输出数据传递到调用环境

　　　　　　　　　　 3) 输入输出参数（IN OUT）其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境

2）删除存储过程

DROP PROCEDURE procedure_name;

3)编译存储过程

ALTER PROCEDURE procedure_name COMPILE

三、存储过程调用

1）说明：

　　1.在PL/SQL中可以直接引用存储过程(在SQL*PLUS中调用存储过程时需要使用call或者execute命令);

　　2.当调用存储过程时，如果无参数，那么直接引用存储过程名；如果有输入参数，则需提供输入参数数值；如果有输出参数，需要使用变量接收输出结果；

　　3.参数传递时有位置传递，名称传递和组合传递三种方法，三种参数传递方式如下：

DECLAREv_para1            varchar2(10);    v_para2        nvarchar2(10);v_para3            varchar2(30);    v_para4            varchar2(30);BEGIN     v_para1 := ‘123‘;       v_para2 := ‘456‘;       v_para4 := ‘789‘;       USP_Learing(v_para1,v_para2,v_para3,v_para4);  --位置传递　　　USP_Learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4);  --值传递 　　USP_Learing(v_para1,v_para2,p_para3=>v_para3,p_para4=>v_para4);  --组合传递dbms_output.put_line(v_para3);  dbms_output.put_line(v_para4);END;   

2）存储过程调用例子

CREATE OR REPLACE PROCUDURE print_Time ISBEGIN    DBMS_OUTPUT.PUT_LINE(SYSDATE);END print_time;

　　1.pl/sql中直接在pl/sql代码块中调用 print_time()即可

　　2.sql*plus中  EXEC print_time();

四、存储过程中常用数据类型

1）记录（RECORD）（单行多列）

2) 表（TABLE）（多行多列）

3) 嵌套表（table）（多行多列）

4）变长数组（VARRY）（多行单列）

5）Common Table Expression (CTE)

五、存储过程中事务处理

1）事务说明：

　　1.事务用于确保数据的一致性，有一组相关的DML语句组成，改组DML语句所执行的操作要么全部确认，要么全部取消。

　　2.当执行事务操作DML时，oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加锁，以防止其他事务在该行上执行DML操作

　　3.当执行事务提交或者事务回滚时，oracle会确认事务变化或者回滚事务、结束事务、山粗保存点、释放锁。

　　4. 提交事务（commit）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变

　　5.保存点（savepoint）在当前事务中，标记事务的保存点

　　6. 回滚操作（rollback）回滚整个事务，删除该事务中所有保存点，释放锁，丢弃所有未决的数据改变
　　7. ROLLBACK TO SAVEPOINT 回滚到指定的保存点

2）存储过程中事务说明：

　　1.尽可能的让事务持续的越短越好

　　2.在事务中尽可能的存取最少的数据量

3）实例

CREATE OR REPLACE PROCEDURE trancProIS BEGIN    INSERT INTO tab1 VALUES(‘AA‘,‘1212‘,‘1313‘);    COMMIT;    SAVEPOINT s1;    INSERT INTO tab1  VALUES(‘BB‘,‘1414‘,‘1515‘);    DBMS_TRANSACTION.SAVEPOINT(‘s2‘);    UPDATE tab1 SET SNO=‘1515‘ WHERE ID=‘BB‘;    COMMIT;    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO SAVEPOINT s1;    RAISE_APPLICATION_ERROR(-20010,‘ERROR:违反唯一索引约束‘);    WHEN OTHERS THEN ROLLBACK;END trancPro;

CREATE OR REPLACE PROCUDURE print_Time ISBEGIN    DBMS_OUTPUT.PUT_LINE(SYSDATE);END print_time;

CREATE OR REPLACE PROCEDURE para_Procedure(        para1　　　　　　　　varchar2 :=‘paraString1‘,        para2　　　　　　　　varchar2 default ‘paraString2‘,        para3  out      　 varchar2,        para4  in out　　　 varchar2)ISBEGINDECLARE       para5               varchar2(20);BEGIN       para5 := ‘输入输出参数：‘|| para4;       para3 := ‘输出参数：‘ || para1 || para2;       para4 :=para5;       dbms_output.put_line(para5);　　　　dbms_output.put_line(‘para4 is‘||para4);END;END para_Procedure;

String oracleDriverName = "oracle.jdbc.driver.OracleDriver";         // 以下使用的Test就是Oracle里的表空间        String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";        Connection myConnection = null;        try {            Class.forName(oracleDriverName);        } catch (ClassNotFoundException ex) {            ex.printStackTrace();        }        try {            myConnection = DriverManager.getConnection(oracleUrlToConnect,                    "xxxx", "xxxx");//此处为数据库用户名与密码         } catch (Exception ex) {            ex.printStackTrace();        }        try {                         CallableStatement proc=null;            proc=myConnection.prepareCall("{call xs_proc(?,?)}");            proc.setString(1, "zhangsan");            proc.registerOutParameter(2, Types.NUMERIC);            proc.execute();            String teststring=proc.getString(2);            System.out.println(teststring);         } catch (Exception ex) {            ex.printStackTrace();        }