oracle 存储过程调用实践.docx
一直以来，存储过程就是一个心病啊，都不知道到底什么东西。不过幸好在别人的带领下，战胜了这个恐惧，算是小小的入门了。（oracle的sql叫做pl-sql，在语法上和m-sql有一些不同的地方。而且，由于java和Oracle的密切关系，所以oracle较高版本的pl-sql中也出现一些类似于面向对象编程的语法结构。）现在把我遇到的各种错误列出来，当然我有log，要不然我都记不住了。1. ORA-00921: unexpected end of SQL command基本sql错误2. bad SQL grammar基本sql错误3. wrong number or types of arguments in call to *调用存储过程传入的参数不对4. weblogic.jdbc.wrapper.Array_oracle_sql_ARRAY 使用的是weblogic容器提供的ARRAY，不能直接转化成ORACLE的ARRAY，但是可以用java.sql下面的接口。5. identifier * must be declared 数据库中没有定义这个名字的存储过程6. ORA-03115: unsupported network datatype or representation 存储过程传入的参数类型不支持 在pl-sql中有package，可以用来定义访问变量的自定义类型，但是返回的类型除了游标以外，都不能被外部，如jdbc来访问。都会提示类型不知道的错误，不管你有没有加上表空间的名字。而这个时候需要用全局的类型来定义。全局的类型，主要分为三种：1. create or replacetype obj_1 as object (* number, * number,* varchar2(36), * varchar2(150);2. create or replacetype tab_1 as table of obj_1;3. create or replacetype array_1AS VARRAY(21) OF obj_1;这几种类型都可以作为返回值，而且，后两种类型，常常可以以多维数组的形式返回。在java中可以这样取值： proc.execute(); java.sql.Array arr = proc.getArray(2); Object strcs = (Object) arr.getArray(); Object objs; for (Object strct : strcs) objs = (java.sql.Struct) strct).getAttributes(); bean = new *(); bean.set*(BigDecimal) objs0).intValue(); bean.set*(BigDecimal) objs1).intValue(); bean.set*(String) objs2); bean.set*(String) objs3); beans.add(bean); 这里要注意，虽然利用的是Oracle的table或者varray,但是返回类型都用java.sql.Array接口来表示就好了，不要用具体的实现类，因为实现类通常在服务器的环境中，本地的classpath中根本没有配置。如果存储过程中的有多个查询，比如两个，第一个查询查询一个list出来，第二个查询要遍历这个list，分别查询，然后组装成一个ARRAY的形式，就可以利用上面的三个类型中的1，2或者1,3即可。值得注意的是，oracle的面向对象特点，所以，当你要在ARRAY扩充之后插入一行时，可以先利用对象的构造函数new一次行对象。如 new oj_1(f1,f2,f3),这是它默认的构造函数，你也可以自定义函数。现在有两个表A（id varchar(15)，c_id varchar(20)）其中id是主键B(c_id varchar(20),c_name1 varchar(100),c_name2 varchar(100),c_name3 varchar(120),date.)其中c_id是主键假设现在有一个查询，首先从A表中通过id查询一列的c_id然后利用c_id查询出对应的c_id,c_name1,c_name2.-创建对象create or replacetype MY_INFO as object (c_id varchar(20), c_name1 varchar(100),c_name2 varchar(100);-创建对象数组create or replacetype MY_ARRAY AS VARRAY(21) OF MY_INFO;-创建包create or replacepackage CUST_PACKAGE as type MY_CURSOR is ref cursor; end CUST_PACKAGE;-创建存储过程create or replacePROCEDURE MY_PRO( in_id IN VARCHAR2, RESULTLIST OUT MY_ARRAY)AS SQL_STR1 VARCHAR2(1024); SQL_STR2 VARCHAR2(1024); TMP_ID_LIST MY_PACKAGE.MY_CURSOR; TMP_ID A.ID%TYPE; TMP_RESULT MY_PACKAGE.MY_CURSOR; MY_INFO MY_INFO; NAME_1 varchar(100); NAME_2 varchar(100); C_ID varchar(20); I NUMBER :=1;BEGIN OPEN TMP_ID_LIST FOR SELECT id from A where id=in_id; -新建一个ARRAY，有点类似java中的新建ArrayList RESULTLIST:= new MY_ARRAY(); LOOP FETCH TMP_ID_LIST INTO TMP_ID; EXIT WHEN TMP_ID_LIST%NOTFOUND; OPEN TMP_RESULT FOR select c_id,name_1,name2 from B where id_c=TEM_ID ; LOOP FETCH TMP_RESULT INTO c_id,name_1,name_2; EXIT WHEN TMP_RESULT%NOTFOUND; -新建一个对象，类似于java中的用带参数的构造函数新建一个对象 MY_INFO := new MY_INFO(UNDEALS,DEALED,ORGID,AREANAME); -数组扩容 RESULTLIST.EXTEND; -为指定小标赋值，有点类似于java中的List的add Resultlist(I):=MY_Info; END LOOP; CLOSE TMP_RESULT; I:=I+1; END LOOP; CLOSE TMP_ID_LIST;END MY_Pro;在java中可以如此调用：public class ProcedureTest private static String driver = "oracle.jdbc.driver.OracleDriver" private static String strUrl = "jdbc:oracle:thin:localhost:1521:ORCL" private static ResultSet rs = null; private static Connection conn = null; private static CallableStatement proc = null; private void testBefore() throws ClassNotFoundException, SQLException, NamingException Class.forName(driver); conn = DriverManager.getConnection(strUrl, "user", "password"); private void testAfter() throws SQLException conn.close(); Test public void CUST_INFO() try testBefore(); proc = conn.prepareCall(" call MY_PRO(?,?) "); proc.setString(1, "12345"); proc.registerOutParameter(2, java.sql.Types.ARRAY, "MY_ARRAY"); proc.execute(); java.sql.ARRAY arr = (java.sql.ARRAY) proc.getArray(2); Object strcs = (Object) arr.getArray(); for(Object strc:strcs) Object objs=(java.sql.STRUCT)strc).getAttributes(); System.out.println(objs0+" "+objs1+" "+objs2); testAfter(); catch (Exception e) e.printStackTrace();
