《oracle存储过程调用实践》由会员分享,可在线阅读,更多相关《oracle存储过程调用实践(6页珍藏版)》请在金锄头文库上搜索。
1、一直以来,存储过程就是一个心病啊,都不知道到底什么东西。不过幸好在别人的带领下,战胜了这个恐惧,算是小小的入门了。(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 argum
2、ents 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,可以用来定义访问变量的自定义类型,
3、但是返回的类型除了游标以外,都不能被外部,如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;这几种类型都可以作为返回值,而
4、且,后两种类型,常常可以以多维数组的形式返回。在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)
5、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即可。值得注意的是
6、,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,
7、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 replacePROCEDU
8、RE 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_
9、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
10、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 ProcedureTes
11、t 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 ClassNotFou
12、ndException, 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
13、); 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();