- CREATE TABLE SFZ_TEST_MANAGER_XG(
- yxgh VARCHAR2(100),
- ygxm VARCHAR2(100),
- position_name VARCHAR2(100)
- );
- CREATE TABLE SFZ_TEST_MANAGER_XG AS select T.CCUST_ID as yxgh,T.ACCS_NBR as ygxm,T.CCUST_NAME as position_name from HEMS_FILTER_CUST_LIST t where t.ccust_id is not null and t.accs_nbr is not null and t.ccust_name is not null;
-
- DROP TABLE SFZ_TEST_MANAGER_XG;
-
- select * from SFZ_TEST_MANAGER_XG;
- select count(*) from SFZ_TEST_MANAGER_XG;
-
- create or replace TYPE SFZ_MANAG_M_OBJ_TYPE AS OBJECT(
- yxgh VARCHAR2(100),
- ygxm VARCHAR2(100),
- position_name VARCHAR2(100));
-
- DROP TYPE SFZ_MANAG_M_OBJ_TYPE;
-
- select * from user_objects t where t.object_name not like '%SFZ_MANAG_M_OBJ_TYPE%' and t.object_type = 'TYPE';
-
- CREATE OR REPLACE TYPE SFZ_MANAG_M_TAB_TYPE IS TABLE OF SFZ_MANAG_M_OBJ_TYPE;
-
- DROP TYPE SFZ_MANAG_M_TAB_TYPE;
-
- create or replace procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out SFZ_MANAG_M_TAB_TYPE)
- is
- cursor cursor_cur_type is select * from SFZ_TEST_MANAGER_XG; --可以增加条件
- i NUMBER(8) :=1;
- BEGIN
- v_table:=SFZ_MANAG_M_TAB_TYPE();
- for cursor_record in cursor_cur_type loop
- v_table.EXTEND;
- v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE(cursor_record.yxgh,cursor_record.ygxm,cursor_record.position_name);
- --v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE('123','124','125');
- i:=i+1;
- end loop;
- end proc_sfz_proc_test;
-
- DROP PROCEDURE proc_sfz_proc_test;
- package com.zjhcsoft.test.utl;
-
- import java.sql.Connection;
- import java.sql.DriverManager;
-
- import oracle.jdbc.OracleCallableStatement;
- import oracle.jdbc.driver.OracleTypes;
- import oracle.sql.ARRAY;
- import oracle.sql.Datum;
- import oracle.sql.STRUCT;
-
- public class TestOracleProc {
-
- /**
- * @param args
- */
- public static void main(String[] args) {
- try {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- } catch (Exception e) {
- e.printStackTrace();
- }
-
- Connection conn = null;
- String DBurl = "jdbc:oracle:thin:@134.224.40.73:1521:orcl";
- try {
- conn = DriverManager.getConnection(DBurl, "hems", "hems");
- System.out.println("Getting Connection...");
- conn.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
-
- try {
- conn = DriverManager.getConnection(DBurl, "hems", "hems");
- long start = System.currentTimeMillis();
- OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall("{call proc_sfz_proc_test(?,?)}");
- cs.setString(1, "fruitking");
- cs.registerOutParameter(2, OracleTypes.ARRAY,"SFZ_MANAG_M_TAB_TYPE");
- cs.execute();
- long end = System.currentTimeMillis();
- System.out.println("this procedure consumes "+((end-start)/1000)+" excute time.");
- start = System.currentTimeMillis();
- ARRAY array = (ARRAY)cs.getArray(2);
- Datum[] datas = array.getOracleArray();
- if(datas.length>0){
- System.out.println("this procedure has "+datas.length+" data.");
- for (int i = 0; i < datas.length; i++){
- if(datas[i]!=null&&((STRUCT) datas[i])!=null){
- Datum[] personAttributes = ((STRUCT) datas[i]).getOracleAttributes();
- System.out.println("column"+(i+1)+":"+new String(personAttributes[0].getBytes())+", "+new String(personAttributes[1].getBytes())+", "+new String(personAttributes[2].getBytes()));
- }else{
- System.out.println("datas["+i+"] is null.");
- }
- }
- }else{
- System.out.println("this procedure is not result data...");
- }
- end = System.currentTimeMillis();
- System.out.println("show this procedure data consumes "+((end-start)/1000)+" excute time.");
- cs.close();
- conn.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
-
- }
- CREATE TABLE SFZ_TEST_MANAGER_XG(
- yxgh VARCHAR2(100),
- ygxm VARCHAR2(100),
- position_name VARCHAR2(100)
- );
- CREATE TABLE SFZ_TEST_MANAGER_XG AS select T.CCUST_ID as yxgh,T.ACCS_NBR as ygxm,T.CCUST_NAME as position_name from HEMS_FILTER_CUST_LIST t where t.ccust_id is not null and t.accs_nbr is not null and t.ccust_name is not null;
- DROP TABLE SFZ_TEST_MANAGER_XG;
- select * from SFZ_TEST_MANAGER_XG;
- select count(*) from SFZ_TEST_MANAGER_XG;
- create or replace TYPE SFZ_MANAG_M_OBJ_TYPE AS OBJECT(
- yxgh VARCHAR2(100),
- ygxm VARCHAR2(100),
- position_name VARCHAR2(100));
- DROP TYPE SFZ_MANAG_M_OBJ_TYPE;
- select * from user_objects t where t.object_name not like '%SFZ_MANAG_M_OBJ_TYPE%' and t.object_type = 'TYPE';
- CREATE OR REPLACE TYPE SFZ_MANAG_M_TAB_TYPE IS TABLE OF SFZ_MANAG_M_OBJ_TYPE;
- DROP TYPE SFZ_MANAG_M_TAB_TYPE;
- create or replace procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out SFZ_MANAG_M_TAB_TYPE)
- is
- cursor cursor_cur_type is select * from SFZ_TEST_MANAGER_XG; --可以增加条件
- i NUMBER(8) :=1;
- BEGIN
- v_table:=SFZ_MANAG_M_TAB_TYPE();
- for cursor_record in cursor_cur_type loop
- v_table.EXTEND;
- v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE(cursor_record.yxgh,cursor_record.ygxm,cursor_record.position_name);
- --v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE('123','124','125');
- i:=i+1;
- end loop;
- end proc_sfz_proc_test;
- DROP PROCEDURE proc_sfz_proc_test;
- package com.zjhcsoft.test.utl;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import oracle.jdbc.OracleCallableStatement;
- import oracle.jdbc.driver.OracleTypes;
- import oracle.sql.ARRAY;
- import oracle.sql.Datum;
- import oracle.sql.STRUCT;
- public class TestOracleProc {
- /**
- * @param args
- */
- public static void main(String[] args) {
- try {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- } catch (Exception e) {
- e.printStackTrace();
- }
- Connection conn = null;
- String DBurl = "jdbc:oracle:thin:@134.224.40.73:1521:orcl";
- try {
- conn = DriverManager.getConnection(DBurl, "hems", "hems");
- System.out.println("Getting Connection...");
- conn.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- try {
- conn = DriverManager.getConnection(DBurl, "hems", "hems");
- long start = System.currentTimeMillis();
- OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall("{call proc_sfz_proc_test(?,?)}");
- cs.setString(1, "fruitking");
- cs.registerOutParameter(2, OracleTypes.ARRAY,"SFZ_MANAG_M_TAB_TYPE");
- cs.execute();
- long end = System.currentTimeMillis();
- System.out.println("this procedure consumes "+((end-start)/1000)+" excute time.");
- start = System.currentTimeMillis();
- ARRAY array = (ARRAY)cs.getArray(2);
- Datum[] datas = array.getOracleArray();
- if(datas.length>0){
- System.out.println("this procedure has "+datas.length+" data.");
- for (int i = 0; i < datas.length; i++){
- if(datas[i]!=null&&((STRUCT) datas[i])!=null){
- Datum[] personAttributes = ((STRUCT) datas[i]).getOracleAttributes();
- System.out.println("column"+(i+1)+":"+new String(personAttributes[0].getBytes())+", "+new String(personAttributes[1].getBytes())+", "+new String(personAttributes[2].getBytes()));
- }else{
- System.out.println("datas["+i+"] is null.");
- }
- }
- }else{
- System.out.println("this procedure is not result data...");
- }
- end = System.currentTimeMillis();
- System.out.println("show this procedure data consumes "+((end-start)/1000)+" excute time.");
- cs.close();
- conn.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }