存储过程学习

存储过程练习： (日期类型为dd-M月-yyyy)，注意commit

1，参数为数组，没返回值：（例子为多条记录多表级联删除）

①．定义类型

create or replace type id_string_array as varray(10) of varchar2(50);     ②.写存储过程

create or replace procedure CONTRACT_C_delete(

p_varlist in id_string_array) is

 

begin

  for x in 1..p_varlist.count loop

      delete from ext_cproduct_c where CONTRACT_ID=p_varlist(x);

       delete from Contract_Product_c where CONTRACT_ID=p_varlist(x);

       delete from CONTRACT_C where contract_id=p_varlist(x);

  end loop;

  commit;

end CONTRACT_C_delete;

③.调用

call CONTRACT_C_delete(id_string_array('8','9'))

Java调用： (spring jdbcTemplate调用)

public void delete(String[] ids) {

StringBuffer sb=new StringBuffer();

sb.append("call CONTRACT_C_delete(id_string_array(");

for(int i=0;i<ids.length;i++){

sb.append("'");

String id=ids[i];

sb.append(id);

if(i!=ids.length-1){

sb.append("',");

}else{

sb.append("'");

}

}

String sql=sb.toString();

this.jdbcTemplate.execute(sql+"))");

}

2.参数为对象，没返回值：（例子为插入数据）

①．定义对象类型

CREATE TYPE factory_type AS OBJECT (

   FACTORY_ID  VARCHAR2(40) ,

   FULL_NAME  VARCHAR2(200)  ,

   FACTORY_NAME  VARCHAR2(30)  ,

   CONTRACTOR  VARCHAR2(20)  ,

   PHONE  VARCHAR2(20)  ,

   MOBILE  VARCHAR2(20)  ,

   FAX  VARCHAR2(20)  ,

   CNOTE  VARCHAR2(600)  ,

   ORDER_NO  integer  ,

   STATE  integer 

);

   ②.定义一个对象类型的数组对象

CREATE TYPE factory AS TABLE OF factory_type;

   ③.写存储过程

CREATE OR REPLACE PROCEDURE factory_c_insert (d in factory)

AS

f factory_type;

BEGIN

FOR i IN d.FIRST()..d.LAST()

LOOP

  f:=d(i);

INSERT INTO factory_c (FACTORY_ID,FULL_NAME,FACTORY_NAME,CONTRACTOR,PHONE,MOBILE,FAX,CNOTE,ORDER_NO,STATE)

VALUES (d(i).FACTORY_ID,d(i).FULL_NAME,d(i).FACTORY_NAME,d(i).CONTRACTOR,d(i).PHONE,d(i).MOBILE,d(i).FAX,d(i).CNOTE,d(i).ORDER_NO,d(i).STATE);

END LOOP;

exception when others then  

 raise;

END factory_c_insert;

④．Java调用

Connection con = null;

CallableStatement cstmt = null;

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:JKDB", "jkda", "jk");

List<Factory> orderList = new ArrayList<Factory>();

//for (int i = 0; i < 10000; i++) {//一万条插入测试

orderList.add(new Factory(UUID.randomUUID().toString(), "fullName","factoryName", "contractor", "phone",

"mobile", "fax", "cnote", 1,2));

//}

StructDescriptor recDesc = StructDescriptor.createDescriptor(

"FACTORY_TYPE", con);

ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();

for (Factory ord : orderList) {

Object[] record = new Object[10];

record[0] = ord.getId();

record[1] = ord.getFullName();

record[2] = ord.getFactoryName();

record[3] = ord.getContractor();

record[4] = ord.getPhone();

record[5] = ord.getMobile();

record[6] = ord.getFax();

record[7] = ord.getCnote();

record[8] = ord.getOrderNo();

record[9] = ord.getState();

STRUCT item = new STRUCT(recDesc, con, record);

pstruct.add(item);

}

ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("FACTORY",

con);

ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());

cstmt = con.prepareCall("{call factory_c_insert(?)}");

cstmt.setArray(1, vArray);

cstmt.execute();

con.commit();

3.参数为基本类型，返回值为结果集：（例子为查询一条记录）

①．定义包

 create or replace package factory_c_package as  

        type factory_c_cursor is ref cursor;  

        end factory_c_package;

②．存储过程

create or replace procedure factory_c_selectOne(fid in varchar2,factoryObject out factory_c_package.factory_c_cursor) is  

        begin  

        open factoryObject for select * from factory_c where factory_id=fid;  

        end factory_c_selectOne;  

    ③.JdbcTemplate调用

public void test5() {   

      List resultList = (List) jdbcTemplate.execute(   

         new CallableStatementCreator() {   

            public CallableStatement createCallableStatement(Connection con) throws SQLException {   

               String storedProc = "{call factory_c_selectOne(?,?)}";// 调用的sql   

               CallableStatement cs = con.prepareCall(storedProc);   

               cs.setString(1, "factoryId");// 设置输入参数的值   

               cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型   

               return cs;   

            }   

         }, new CallableStatementCallback() {   

            public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {   

               List resultsMap = new ArrayList();   

               cs.execute();   

               ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值   

               while (rs.next()) {// 转换每行的返回值到Map中   

                  Map rowMap = new HashMap();   

                  rowMap.put("id", rs.getString("FACTORY_ID"));   

                  rowMap.put("name", rs.getString("FACTORY_NAME"));   

                  resultsMap.add(rowMap);   

               }   

               rs.close();   

               return resultsMap;   

            }   

      });   

      for (int i = 0; i < resultList.size(); i++) {   

         Map rowMap = (Map) resultList.get(i);   

         String id = rowMap.get("id").toString();   

         String name = rowMap.get("name").toString();   

         System.out.println("id=" + id + ";name=" + name);   

      }   

    }