本文介绍了一种在PLSQL中实现ResultSet类的方法，用于处理查询结果集，支持获取不同类型的数据，如字符串、数字等，并提供了诸如next、close等方法。
PLSQL中执行"SELECT" 查询子句时,一般步骤是:
- 打开游标: open cursor for sql
- fetch cursor into ...
- close cursor
但是如果查询取出的域过多,那么fetch into 后面的变量就会很多。另外,还有一种情况: 查询出的结果中,有可能我们需要的只是其中的一部分。此时，fetch cursor into 的使用较为繁琐。于是考虑将java中的resultset类移植过来。
Types ResultSet, ResultSetBulk and ResultSetMetaData used for PLSQL CURSOR
CREATE OR REPLACE TYPE BODY ResultSet IS
CONSTRUCTOR FUNCTION ResultSet (p_crsr IN SYS_REFCURSOR ) RETURN SELF AS RESULT IS BEGIN result_set_init (p_crsr ); RETURN; END;
MEMBER PROCEDURE result_set_init (p_crsr IN SYS_REFCURSOR ) IS
rc SYS_REFCURSOR ; desctab DBMS_SQL.DESC_TAB3 ; varchar2_col VARCHAR2 (32767); type_name VARCHAR2 (32767); BEGIN
rc := p_crsr ;
SELF.curid := DBMS_SQL.to_cursor_number (rc );
SELF.exhausted := 0;
SELF.current_row_number := 0;
SELF.col_names := Varchar2Table ();
SELF.col_types := IntegerTable ();
SELF.obj_type_names := Varchar2Table ();
DBMS_SQL.describe_columns3 (SELF.curid , SELF.colnum , desctab );
FOR i IN 1..SELF.colnum
LOOP
SELF.col_types.extend ;
SELF.col_types (i ) := desctab (i ).col_type;
SELF.col_names.extend ;
SELF.col_names (i ) := upper (desctab (i ).col_name);
SELF.obj_type_names.extend ;
IF desctab (i ).col_type = 109 THEN SELF.obj_type_names (i ) := desctab (i ).col_type_name; END IF;
SELF.define_col_by_type_number (i , desctab (i ).col_type);
END LOOP;
END;
MEMBER FUNCTION next (SELF IN OUT ResultSet ) RETURN BOOLEAN AS
result BOOLEAN := TRUE; BEGIN
IF DBMS_SQL.fetch_rows (SELF.curid ) <= 0 THEN result := FALSE; SELF.exhausted := 1; ELSE
SELF.current_row_number := SELF.current_row_number + 1; END IF;
RETURN result ;
END;
MEMBER PROCEDURE close (SELF IN OUT NOCOPY ResultSet ) AS
BEGIN IF DBMS_SQL.is_open (SELF.curid ) THEN DBMS_SQL.close_cursor (SELF.curid ); END IF; END;
MEMBER FUNCTION isClosed (SELF IN OUT ResultSet ) RETURN BOOLEAN AS BEGIN RETURN (NOT DBMS_SQL.is_open (SELF.curid )); END;
MEMBER FUNCTION findColumn (p_col_name VARCHAR2 ) RETURN INTEGER AS BEGIN RETURN SELF.get_valid_col_idx (p_col_name ); END;
MEMBER FUNCTION getRow (SELF IN OUT ResultSet ) RETURN INTEGER AS BEGIN RETURN SELF.current_row_number ; END;
MEMBER FUNCTION getFetchSize (SELF IN OUT ResultSet ) RETURN INTEGER AS BEGIN RETURN 1; END;
MEMBER PROCEDURE setFetchSize (SELF IN OUT ResultSet , p_new_fetch_size INTEGER ) AS
rs_wrong_fetch_size EXCEPTION; BEGIN
IF p_new_fetch_size < 1 THEN
log_debug ('ERROR: Fetch size should be greater or equal 1. Found: ' || p_new_fetch_size ); RAISE rs_wrong_fetch_size; END IF;
log_debug ('WARNING: non-bulk ??ID-VAR $?rs-type-name cannot change fetch size.');
END;
MEMBER FUNCTION isBeforeFirst (SELF IN OUT ResultSet ) RETURN BOOLEAN AS BEGIN RETURN ( SELF.current_row_number = 0); END;
MEMBER FUNCTION isAfterLast (SELF IN OUT ResultSet ) RETURN BOOLEAN AS BEGIN RETURN ( SELF.exhausted = 1); END;
MEMBER FUNCTION getBoolean (SELF IN ResultSet , p_col_idx INTEGER ) RETURN BOOLEAN AS
result BOOLEAN ; rs_wrong_type EXCEPTION; BEGIN
CASE SELF.col_types (p_col_idx )
WHEN 1 THEN
CASE SELF.getString (p_col_idx ) WHEN '0' THEN result := FALSE; WHEN '1' THEN result := TRUE; ELSE RAISE rs_wrong_type;
END CASE;
WHEN 2 THEN
CASE SELF.getNumber (p_col_idx ) WHEN 0 THEN result := FALSE; WHEN 1 THEN result := TRUE; ELSE RAISE rs_wrong_type;
END CASE;
ELSE RAISE rs_wrong_type; END CASE;
RETURN result ;
END;
MEMBER FUNCTION getBoolean (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN BOOLEAN AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getBoolean (col_idx ); END;
MEMBER FUNCTION getBigDecimal (SELF IN ResultSet , p_col_idx INTEGER ) RETURN NUMBER AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getBigDecimal (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN NUMBER AS
BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getByte (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getByte (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getBlob (SELF IN ResultSet , p_col_idx INTEGER ) RETURN BLOB AS
b BLOB ; rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 113 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , b ); RETURN b ; ELSE
RAISE rs_wrong_type; END IF;
END;
MEMBER FUNCTION getBlob (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN BLOB AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getBlob (col_idx ); END;
MEMBER FUNCTION getString (SELF IN ResultSet , p_col_idx INTEGER ) RETURN VARCHAR2 AS
result VARCHAR2 (32766); BEGIN
CASE SELF.col_types (p_col_idx )
WHEN 1 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , result );
WHEN 2 THEN DECLARE n NUMBER; BEGIN DBMS_SQL.column_value (SELF.curid , p_col_idx , n ); result := TO_CHAR (n ); END;
WHEN 12 THEN DECLARE d DATE ; BEGIN DBMS_SQL.column_value (SELF.curid , p_col_idx , d ); result := TO_CHAR (d ); END;
WHEN 112 THEN
DECLARE c CLOB ; BEGIN DBMS_SQL.column_value (SELF.curid , p_col_idx , c ); result := DBMS_LOB.substr (c , 32766, 1); END;
WHEN 113 THEN
DECLARE b BLOB ; BEGIN
DBMS_SQL.column_value (SELF.curid , p_col_idx , b ); result := UTL_RAW.cast_to_varchar2 (DBMS_LOB.substr (b , 32766, 1));
END;
WHEN 180 THEN DECLARE t TIMESTAMP; BEGIN DBMS_SQL.column_value (SELF.curid , p_col_idx , t ); result := TO_CHAR (t ); END;
ELSE DBMS_SQL.column_value (SELF.curid , p_col_idx , result ); END CASE;
RETURN result ;
END;
MEMBER FUNCTION getString (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN VARCHAR2 AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getString (col_idx ); END;
MEMBER FUNCTION getShort (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getShort (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getInt (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getInt (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getLong (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getLong (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER AS BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getFloat (SELF IN ResultSet , p_col_idx INTEGER ) RETURN FLOAT AS BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getFloat (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN FLOAT AS BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getDouble (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DOUBLE PRECISION AS
BEGIN RETURN SELF.getNumber (p_col_idx ); END;
MEMBER FUNCTION getDouble (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DOUBLE PRECISION AS
BEGIN RETURN SELF.getNumber (p_col_name ); END;
MEMBER FUNCTION getDate (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DATE AS
d DATE ; rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 12 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , d ); RETURN d ; ELSE RAISE rs_wrong_type;
END IF;
END;
MEMBER FUNCTION getDate (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DATE AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getDate (col_idx ); END;
MEMBER FUNCTION getTime (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DATE AS BEGIN RETURN SELF.getDate (p_col_idx ); END;
MEMBER FUNCTION getTime (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DATE AS BEGIN RETURN SELF.getDate (p_col_name ); END;
MEMBER FUNCTION getTimestamp (SELF IN ResultSet , p_col_idx INTEGER ) RETURN TIMESTAMP AS
t TIMESTAMP; rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 180 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , t ); RETURN t ; ELSE
RAISE rs_wrong_type; END IF;
END;
MEMBER FUNCTION getTimestamp (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN TIMESTAMP AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getTimestamp (col_idx ); END;
MEMBER FUNCTION getClob (SELF IN ResultSet , p_col_idx INTEGER ) RETURN CLOB AS
c CLOB ; rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 112 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , c ); RETURN c ; ELSE
RAISE rs_wrong_type; END IF;
END;
MEMBER FUNCTION getClob (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN CLOB AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getClob (col_idx ); END;
MEMBER FUNCTION getObject (SELF IN ResultSet , p_col_idx INTEGER ) RETURN ANYDATA AS
a ANYDATA := NULL; expr VARCHAR2 (32767); rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 109 THEN
BEGIN
expr := 'DECLARE col_val ' || obj_type_names (p_col_idx ) || '; ';
expr := expr || 'BEGIN
DBMS_SQL.column_value(:1, :2, col_val);
:3 := ANYDATA.convertObject(col_val);
END;';
EXECUTE IMMEDIATE expr USING IN SELF.curid , IN p_col_idx , OUT a ;EXCEPTION
WHEN OTHERS THEN log_debug ('Failed to get object of type: ' || SELF.obj_type_names (p_col_idx )); END;
ELSE RAISE rs_wrong_type; END IF;
RETURN a ;
END;
MEMBER FUNCTION getObject (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN ANYDATA AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getObject (col_idx ); END;
MEMBER FUNCTION getMetaData (SELF IN ResultSet ) RETURN ResultSetMetaData AS
BEGIN RETURN ResultSetMetaData (SELF.colnum , SELF.col_names , SELF.col_types ); END;
MEMBER FUNCTION getNumber (SELF IN ResultSet , p_col_idx INTEGER ) RETURN NUMBER AS
n NUMBER; rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 2 THEN DBMS_SQL.column_value (SELF.curid , p_col_idx , n ); RETURN n ; ELSE RAISE rs_wrong_type;
END IF;
END;
MEMBER FUNCTION getNumber (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN NUMBER AS
col_idx INTEGER ; BEGIN col_idx := SELF.get_valid_col_idx (upper (p_col_name )); RETURN SELF.getNumber (col_idx ); END;
MEMBER FUNCTION get_valid_col_idx (p_col_name VARCHAR2 ) RETURN INTEGER IS
col_idx INTEGER ; rs_wrong_column_name EXCEPTION; BEGIN
col_idx := index_of_col_name (p_col_name );
IF col_idx < 1 THEN log_debug ('Wrong column name: ' || upper (p_col_name )); RAISE rs_wrong_column_name; END IF;
RETURN col_idx ;
END;
MEMBER FUNCTION index_of_col_name (p_col_name VARCHAR2 ) RETURN INTEGER AS
idx INTEGER := 0; upper_col_name VARCHAR (32767) := upper (p_col_name ); BEGIN
FOR i IN 1..SELF.col_names.count LOOP IF SELF.col_names (i ) = upper_col_name THEN idx := i ; EXIT; END IF; END LOOP;
RETURN idx ;
END;
MEMBER PROCEDURE define_col_by_type_number (p_col_idx INTEGER , p_col_type_number INTEGER ) AS
BEGIN
CASE p_col_type_number
WHEN 1 THEN DECLARE vc2 VARCHAR2 (32767); BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , vc2 , 32767); END;
WHEN 2 THEN DECLARE n NUMBER; BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , n ); END;
WHEN 12 THEN DECLARE d DATE ; BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , d ); END;
WHEN 112 THEN DECLARE c CLOB ; BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , c ); END;
WHEN 113 THEN DECLARE b BLOB ; BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , b ); END;
WHEN 180 THEN DECLARE t TIMESTAMP; BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , t ); END;
WHEN 109 THEN
DECLARE expr VARCHAR2 (32767); BEGIN
expr := 'DECLARE col_val ' || obj_type_names (p_col_idx ) || '; ';
expr := expr || 'BEGIN
DBMS_SQL.define_column(:1, :2, col_val);
END;';
EXECUTE IMMEDIATE expr USING IN SELF.curid , IN p_col_idx ;EXCEPTION
WHEN OTHERS THEN
DECLARE vc2 VARCHAR2 (32767); BEGIN
log_debug ('Unsupported Object type: ' || obj_type_names (p_col_idx ));
DBMS_SQL.define_column (SELF.curid , p_col_idx , vc2 , 32767);
END;
END;
ELSE DECLARE vc2 VARCHAR2 (32767); BEGIN DBMS_SQL.define_column (SELF.curid , p_col_idx , vc2 , 32767); END; END CASE;
END;
END ;
/CREATE OR REPLACE TYPE ResultSet FORCE AS OBJECT
(curid INTEGER , colnum INTEGER , exhausted INTEGER , current_row_number INTEGER , col_names Varchar2Table , col_types IntegerTable ,
obj_type_names Varchar2Table , CONSTRUCTOR FUNCTION ResultSet (p_crsr IN SYS_REFCURSOR ) RETURN SELF AS RESULT,
MEMBER PROCEDURE result_set_init (p_crsr IN SYS_REFCURSOR ), MEMBER FUNCTION next (SELF IN OUT ResultSet ) RETURN BOOLEAN ,
MEMBER PROCEDURE close (SELF IN OUT NOCOPY ResultSet ), MEMBER FUNCTION findColumn (p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION isClosed (SELF IN OUT ResultSet ) RETURN BOOLEAN , MEMBER FUNCTION getRow (SELF IN OUT ResultSet ) RETURN INTEGER ,
MEMBER FUNCTION getFetchSize (SELF IN OUT ResultSet ) RETURN INTEGER ,
MEMBER PROCEDURE setFetchSize (SELF IN OUT ResultSet , p_new_fetch_size INTEGER ),
MEMBER FUNCTION isBeforeFirst (SELF IN OUT ResultSet ) RETURN BOOLEAN ,
MEMBER FUNCTION isAfterLast (SELF IN OUT ResultSet ) RETURN BOOLEAN ,
MEMBER FUNCTION getBoolean (SELF IN ResultSet , p_col_idx INTEGER ) RETURN BOOLEAN ,
MEMBER FUNCTION getBoolean (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN BOOLEAN ,
MEMBER FUNCTION getBigDecimal (SELF IN ResultSet , p_col_idx INTEGER ) RETURN NUMBER,
MEMBER FUNCTION getBigDecimal (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN NUMBER,
MEMBER FUNCTION getBlob (SELF IN ResultSet , p_col_idx INTEGER ) RETURN BLOB ,
MEMBER FUNCTION getBlob (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN BLOB ,
MEMBER FUNCTION getByte (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER ,
MEMBER FUNCTION getByte (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION getDate (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DATE ,
MEMBER FUNCTION getDate (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DATE ,
MEMBER FUNCTION getDouble (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DOUBLE PRECISION,
MEMBER FUNCTION getDouble (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DOUBLE PRECISION,
MEMBER FUNCTION getShort (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER ,
MEMBER FUNCTION getShort (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION getInt (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER ,
MEMBER FUNCTION getInt (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION getLong (SELF IN ResultSet , p_col_idx INTEGER ) RETURN INTEGER ,
MEMBER FUNCTION getLong (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION getFloat (SELF IN ResultSet , p_col_idx INTEGER ) RETURN FLOAT ,
MEMBER FUNCTION getFloat (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN FLOAT ,
MEMBER FUNCTION getString (SELF IN ResultSet , p_col_idx INTEGER ) RETURN VARCHAR2 ,
MEMBER FUNCTION getString (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN VARCHAR2 ,
MEMBER FUNCTION getTime (SELF IN ResultSet , p_col_idx INTEGER ) RETURN DATE ,
MEMBER FUNCTION getTime (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN DATE ,
MEMBER FUNCTION getTimestamp (SELF IN ResultSet , p_col_idx INTEGER ) RETURN TIMESTAMP,
MEMBER FUNCTION getTimestamp (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN TIMESTAMP,
MEMBER FUNCTION getClob (SELF IN ResultSet , p_col_idx INTEGER ) RETURN CLOB ,
MEMBER FUNCTION getClob (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN CLOB ,
MEMBER FUNCTION getObject (SELF IN ResultSet , p_col_idx INTEGER ) RETURN ANYDATA ,
MEMBER FUNCTION getObject (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN ANYDATA ,
MEMBER FUNCTION getMetaData (SELF IN ResultSet ) RETURN ResultSetMetaData ,
MEMBER FUNCTION getNumber (SELF IN ResultSet , p_col_idx INTEGER ) RETURN NUMBER,
MEMBER FUNCTION getNumber (SELF IN ResultSet , p_col_name VARCHAR2 ) RETURN NUMBER,
MEMBER FUNCTION get_valid_col_idx (p_col_name VARCHAR2 ) RETURN INTEGER ,
MEMBER FUNCTION index_of_col_name (p_col_name IN VARCHAR2 ) RETURN INTEGER ,
MEMBER PROCEDURE define_col_by_type_number (p_col_idx INTEGER , p_col_type_number INTEGER ))
NOT FINAL ;
/CREATE OR REPLACE TYPE BODY ResultSetBulk IS
CONSTRUCTOR FUNCTION ResultSetBulk (p_crsr IN SYS_REFCURSOR , p_fetch_size IN INTEGER ) RETURN SELF AS RESULT IS
BEGIN setFetchSize (p_fetch_size ); result_set_init (p_crsr ); RETURN; END;
OVERRIDING MEMBER PROCEDURE result_set_init (p_crsr IN SYS_REFCURSOR ) IS
rc SYS_REFCURSOR ; desctab DBMS_SQL.DESC_TAB3 ; varchar2_col VARCHAR2 (32767); type_name VARCHAR2 (32767); BEGIN
rc := p_crsr ;
SELF.curid := DBMS_SQL.to_cursor_number (rc );
SELF.exhausted := 0;
SELF.current_row_number := 0;
SELF.fetch_idx := 0;
SELF.fetch_count := 0;
SELF.fetches_number := -1;
SELF.col_names := Varchar2Table ();
SELF.col_types := IntegerTable ();
SELF.col_to_table_arr_idx := IntegerTable ();
SELF.cached_varchar2_tables := Varchar2TableArray ();
SELF.cached_number_tables := NumberTableArray ();
SELF.cached_date_tables := DateTableArray ();
SELF.cached_clob_tables := ClobTableArray ();
SELF.cached_blob_tables := BlobTableArray ();
SELF.cached_timestamp_tables := TimestampTableArray ();
DBMS_SQL.describe_columns3 (SELF.curid , SELF.colnum , desctab );
FOR i IN 1..SELF.colnum
LOOP
SELF.col_types.extend ;
SELF.col_types (i ) := desctab (i ).col_type;
SELF.col_names.extend ;
SELF.col_names (i ) := upper (desctab (i ).col_name);
self.define_col_by_type_number (i , desctab (i ).col_type);
END LOOP;
END;
OVERRIDING MEMBER FUNCTION next (SELF IN OUT ResultSetBulk ) RETURN BOOLEAN AS
result BOOLEAN := TRUE; BEGIN
IF SELF.fetch_idx >= SELF.fetch_count THEN
SELF.fetch_idx := 0;
IF SELF.fetch_count > 0 AND SELF.fetch_count < SELF.fetch_size THEN SELF.fetch_count := 0; ELSE
SELF.fetch_count := DBMS_SQL.fetch_rows (SELF.curid );
SELF.fetches_number := SELF.fetches_number + 1;
update_cached_rows ();
END IF;
END IF;
IF SELF.fetch_count <= 0 THEN result := FALSE; SELF.exhausted := 1; ELSE
SELF.fetch_idx := SELF.fetch_idx + 1; SELF.current_row_number := SELF.current_row_number + 1; END IF;
RETURN result ;
END;
OVERRIDING MEMBER FUNCTION getFetchSize (SELF IN OUT ResultSetBulk ) RETURN INTEGER AS BEGIN RETURN SELF.fetch_size ; END;
OVERRIDING MEMBER PROCEDURE setFetchSize (SELF IN OUT ResultSetBulk , p_new_fetch_size INTEGER ) AS
rs_wrong_fetch_size EXCEPTION; BEGIN
IF p_new_fetch_size <= 0 THEN
log_debug ('ERROR: Fetch size should be greater or equal 1. Found: ' || p_new_fetch_size ); RAISE rs_wrong_fetch_size; END IF;
SELF.fetch_size := p_new_fetch_size ;
END;
OVERRIDING MEMBER FUNCTION getBlob (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN BLOB AS
rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 113 THEN
RETURN SELF.cached_blob_tables (SELF.col_to_table_arr_idx (p_col_idx ))(SELF.fetch_idx ); ELSE RAISE rs_wrong_type; END IF;
END;
OVERRIDING MEMBER FUNCTION getString (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN VARCHAR2 AS
result VARCHAR2 (32766); table_idx INTEGER := SELF.col_to_table_arr_idx (p_col_idx ); BEGIN
CASE SELF.col_types (p_col_idx )
WHEN 1 THEN result := SELF.cached_varchar2_tables (table_idx )(SELF.fetch_idx );
WHEN 2 THEN result := TO_CHAR (SELF.getNumber (p_col_idx ));
WHEN 12 THEN result := TO_CHAR (SELF.getDate (p_col_idx ));
WHEN 112 THEN result := DBMS_LOB.substr (SELF.getClob (p_col_idx ), 32766, 1);
WHEN 113 THEN result := UTL_RAW.cast_to_varchar2 (DBMS_LOB.substr (SELF.getBlob (p_col_idx ), 32766, 1));
WHEN 180 THEN result := TO_CHAR (SELF.getTimestamp (p_col_idx ));
ELSE result := SELF.cached_varchar2_tables (table_idx )(SELF.fetch_idx ); END CASE;
RETURN result ;
END;
OVERRIDING MEMBER FUNCTION getDate (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN DATE AS
rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 12 THEN RETURN SELF.cached_date_tables (SELF.col_to_table_arr_idx (p_col_idx ))(SELF.fetch_idx );
ELSE RAISE rs_wrong_type; END IF;
END;
OVERRIDING MEMBER FUNCTION getTimestamp (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN TIMESTAMP AS
rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 180 THEN
RETURN SELF.cached_timestamp_tables (SELF.col_to_table_arr_idx (p_col_idx ))(SELF.fetch_idx ); ELSE RAISE rs_wrong_type;
END IF;
END;
OVERRIDING MEMBER FUNCTION getClob (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN CLOB AS
rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 112 THEN
RETURN SELF.cached_clob_tables (SELF.col_to_table_arr_idx (p_col_idx ))(SELF.fetch_idx ); ELSE RAISE rs_wrong_type; END IF;
END;
OVERRIDING MEMBER FUNCTION getObject (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN ANYDATA AS
rs_unsupported_operation EXCEPTION; BEGIN RAISE rs_unsupported_operation; END;
OVERRIDING MEMBER FUNCTION getNumber (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN NUMBER AS
rs_wrong_type EXCEPTION; BEGIN
IF SELF.col_types (p_col_idx ) = 2 THEN
RETURN SELF.cached_number_tables (SELF.col_to_table_arr_idx (p_col_idx ))(SELF.fetch_idx ); ELSE RAISE rs_wrong_type; END IF;
END;
OVERRIDING MEMBER PROCEDURE define_col_by_type_number (p_col_idx INTEGER , p_col_type_number INTEGER ) AS
expr VARCHAR2 (32767); BEGIN
expr := 'BEGIN
DBMS_SQL.define_array(:1, :2, col, :3, 1);
:4 .extend(:3);
:5 .extend;
:5(:5 .count) := :4;
:6 .extend;
:6(:6 .count) := :5 .count;
END;';
CASE p_col_type_number
WHEN 1 THEN
DECLARE varchar2_table Varchar2Table := Varchar2Table (); BEGIN
expr := 'DECLARE col DBMS_SQL.varchar2_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT varchar2_table , IN OUT cached_varchar2_tables , IN OUT
col_to_table_arr_idx ;
END;
WHEN 2 THEN
DECLARE number_table NumberTable := NumberTable (); BEGIN
expr := 'DECLARE col DBMS_SQL.number_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT number_table , IN OUT cached_number_tables , IN OUT col_to_table_arr_idx ;
END;
WHEN 12 THEN
DECLARE date_table DateTable := DateTable (); BEGIN
expr := 'DECLARE col DBMS_SQL.date_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT date_table , IN OUT cached_date_tables , IN OUT col_to_table_arr_idx ;
END;
WHEN 112 THEN
DECLARE clob_table ClobTable := ClobTable (); BEGIN
expr := 'DECLARE col DBMS_SQL.clob_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT clob_table , IN OUT cached_clob_tables , IN OUT col_to_table_arr_idx ;
END;
WHEN 113 THEN
DECLARE blob_table BlobTable := BlobTable (); BEGIN
expr := 'DECLARE col DBMS_SQL.blob_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT blob_table , IN OUT cached_blob_tables , IN OUT col_to_table_arr_idx ;
END;
WHEN 180 THEN
DECLARE timestamp_table TimestampTable := TimestampTable (); BEGIN
expr := 'DECLARE col DBMS_SQL.timestamp_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT timestamp_table , IN OUT cached_timestamp_tables , IN OUT
col_to_table_arr_idx ;
END;
ELSE
DECLARE varchar2_table Varchar2Table := Varchar2Table (); BEGIN
log_debug ('WARNING: Unsupported type in ResultSet. DBMS_SQL type: ' || p_col_type_number );
expr := 'DECLARE col DBMS_SQL.varchar2_table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , p_col_idx , self.fetch_size , IN OUT varchar2_table , IN OUT cached_varchar2_tables , IN OUT
col_to_table_arr_idx ;
END;
END CASE;
END;
MEMBER PROCEDURE update_cached_rows (SELF IN OUT ResultSetBulk ) AS
expr VARCHAR2 (32767); shift_coef INTEGER := SELF.fetch_size * SELF.fetches_number ; BEGIN
FOR idx IN 1..SELF.colnum
LOOP
expr := 'BEGIN
DBMS_SQL.column_value(:1, :2, table_from);
table_to := :3;
FOR i IN 1..table_from.count LOOP
table_to(i) := table_from(i + :4);
END LOOP;
:3 := table_to;
END;';
CASE SELF.col_types (idx )
WHEN 1 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.varchar2_table; table_to Varchar2Table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_varchar2_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
WHEN 2 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.number_table; table_to NumberTable;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_number_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
WHEN 12 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.date_table; table_to DateTable;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_date_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
WHEN 112 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.clob_table; table_to ClobTable;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_clob_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
WHEN 113 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.blob_table; table_to BlobTable;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_blob_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
WHEN 180 THEN
BEGIN
expr := 'DECLARE table_from DBMS_SQL.timestamp_table; table_to TimestampTable;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_timestamp_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
ELSE
BEGIN
expr := 'DECLARE table_from DBMS_SQL.varchar2_table; table_to Varchar2Table;' || expr ;
EXECUTE IMMEDIATE expr USING
self.curid , idx , IN OUT SELF.cached_varchar2_tables (SELF.col_to_table_arr_idx (idx )), shift_coef ;
END;
END CASE;
END LOOP;
END;
END ;
/CREATE OR REPLACE TYPE ResultSetBulk FORCE UNDER ResultSet
(fetch_size INTEGER , fetch_idx INTEGER , fetch_count INTEGER , fetches_number INTEGER , col_to_table_arr_idx IntegerTable ,
cached_varchar2_tables Varchar2TableArray , cached_number_tables NumberTableArray , cached_date_tables DateTableArray ,
cached_clob_tables ClobTableArray , cached_blob_tables BlobTableArray , cached_timestamp_tables TimestampTableArray ,
CONSTRUCTOR FUNCTION ResultSetBulk (p_crsr IN SYS_REFCURSOR , p_fetch_size INTEGER ) RETURN SELF AS RESULT,
OVERRIDING MEMBER PROCEDURE result_set_init (p_crsr IN SYS_REFCURSOR ),
OVERRIDING MEMBER FUNCTION next (SELF IN OUT ResultSetBulk ) RETURN BOOLEAN ,
OVERRIDING MEMBER FUNCTION getFetchSize (SELF IN OUT ResultSetBulk ) RETURN INTEGER ,
OVERRIDING MEMBER PROCEDURE setFetchSize (SELF IN OUT ResultSetBulk , p_new_fetch_size INTEGER ),
OVERRIDING MEMBER FUNCTION getBlob (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN BLOB ,
OVERRIDING MEMBER FUNCTION getString (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN VARCHAR2 ,
OVERRIDING MEMBER FUNCTION getDate (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN DATE ,
OVERRIDING MEMBER FUNCTION getTimestamp (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN TIMESTAMP,
OVERRIDING MEMBER FUNCTION getClob (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN CLOB ,
OVERRIDING MEMBER FUNCTION getObject (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN ANYDATA ,
OVERRIDING MEMBER FUNCTION getNumber (SELF IN ResultSetBulk , p_col_idx INTEGER ) RETURN NUMBER,
OVERRIDING MEMBER PROCEDURE define_col_by_type_number (p_col_idx INTEGER , p_col_type_number INTEGER ),
MEMBER PROCEDURE update_cached_rows (SELF IN OUT ResultSetBulk )) ;
/CREATE OR REPLACE TYPE BODY ResultSetMetaData IS
MEMBER FUNCTION getColumnCount (SELF IN ResultSetMetaData ) RETURN INTEGER AS BEGIN RETURN SELF.col_number ; END;
MEMBER FUNCTION getColumnName (SELF IN ResultSetMetaData , col_id INTEGER ) RETURN VARCHAR2 AS
BEGIN RETURN SELF.col_names (col_id ); END;
MEMBER FUNCTION getColumnType (SELF IN ResultSetMetaData , col_id INTEGER ) RETURN INTEGER AS
BEGIN RETURN SELF.convertToJDBCType (SELF.col_types (col_id )); END;
MEMBER FUNCTION convertToJDBCType (SELF IN ResultSetMetaData , p_type INTEGER ) RETURN INTEGER AS
jdbc_type INTEGER ; BEGIN
CASE p_type
WHEN 1 THEN jdbc_type := 12;
WHEN 2 THEN jdbc_type := 2;
WHEN 12 THEN jdbc_type := 91;
WHEN 109 THEN jdbc_type := 2000;
WHEN 112 THEN jdbc_type := 2005;
WHEN 113 THEN jdbc_type := 2004;
WHEN 180 THEN jdbc_type := 93;
ELSE jdbc_type := 12; END CASE;
RETURN jdbc_type ;
END;
END ;
/CREATE OR REPLACE TYPE ResultSetMetaData FORCE AS OBJECT
(col_number INTEGER , col_names Varchar2Table , col_types IntegerTable ,
MEMBER FUNCTION getColumnCount (SELF IN ResultSetMetaData ) RETURN INTEGER ,
MEMBER FUNCTION getColumnName (SELF IN ResultSetMetaData , col_id INTEGER ) RETURN VARCHAR2 ,
MEMBER FUNCTION getColumnType (SELF IN ResultSetMetaData , col_id INTEGER ) RETURN INTEGER ,
MEMBER FUNCTION convertToJDBCType (SELF IN ResultSetMetaData , p_type INTEGER ) RETURN INTEGER ) ;
/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
