如题,需要向存储过程传输/传出自定义类型的数据。
比如我有如下的类型:
CREATE TYPE BT_DATE_TAB AS TABLE OF DATE; / CREATE TYPE BT_FLOAT_TAB AS TABLE OF NUMBER(20, 6); / CREATE TYPE BT_STRING_TAB AS TABLE OF VARCHAR2(100); / CREATE TYPE BT_INTEGER_TAB AS TABLE OF NUMBER(20); / CREATE TYPE BT_CLOB_TAB AS TABLE OF CLOB; / CREATE TYPE BT_BLOB_TAB AS TABLE OF BLOB; /
CREATE OR REPLACE TYPE BT_SUB_DATA AS OBJECT ( ASTRING VARCHAR2(60), -- 字符型数据, should not be NULL ASTRINGARRAY BT_STRING_TAB, -- 字符串数组, should not be NULL AINTEGER NUMBER(8), -- 数值型数据, should not be NULL AINTEGERARRAY BT_INTEGER_TAB, -- 数值数组, should not be NULL AFLOAT NUMBER(8,3), -- 浮点型数据, should not be NULL AFLOATARRAY BT_FLOAT_TAB, -- 浮点型数组, should not be NULL ADATE DATE, -- 日期型数据, should not be NULL ADATEARRAY BT_DATE_TAB, -- 日期型数组, should not be NULL ACLOB CLOB -- 大字段数据, should not be NULL ); / CREATE OR REPLACE TYPE BT_SUB_DATA_TAB IS TABLE OF BT_SUB_DATA; / -- BO Sub_Data: declaration end.
-- BO Name: Main_Query. -- BO Description: 动作. -- Create Date: 2006/12/31 9:23:28.
CREATE OR REPLACE TYPE BT_MAIN_QUERY AS OBJECT ( QUERYFLAG VARCHAR2(4), -- 查询类型, should not be NULL QUERYVALUE VARCHAR2(20), -- 查询条件, should not be NULL ARRAYDATA BT_SUB_DATA_TAB, -- 数组返回, should not be NULL SINGLEDATA BT_SUB_DATA, -- 单个返回, should not be NULL TOTALPAGE NUMBER(8), -- 总页数, should not be NULL CURRENTPAGE NUMBER(8), -- 当前页数, should not be NULL PAGELENGTH NUMBER(8), -- 每页个数, should not be NULL TOTALCOUNT NUMBER(8) -- 记录总数, should not be NULL ); / CREATE OR REPLACE TYPE BT_MAIN_QUERY_TAB IS TABLE OF BT_MAIN_QUERY; /
然后根据这几个类型创建一个简单查询的存储过程。
CREATE OR REPLACE PACKAGE MYTEST IS
-- Author : Hibiki -- Created : 2006-6-6 14:53:04 -- Purpose : Auto generated source for services by Kirikawa Module Designer
PROCEDURE MAIN_QUERY(vo_Main_Query IN OUT BT_MAIN_QUERY, vo_OperationStatus OUT NUMBER, vo_OperationStatusDesc OUT VARCHAR2); --动作
END MYTEST; / CREATE OR REPLACE PACKAGE BODY MYTEST IS
PCK_NAME CONSTANT VARCHAR2(50) := 'MYTEST ';
PROCEDURE MAIN_QUERY(vo_Main_Query IN OUT BT_MAIN_QUERY, vo_OperationStatus OUT NUMBER, vo_OperationStatusDesc OUT VARCHAR2) AS --动作
PRC_NAME CONSTANT VARCHAR2(50) := 'MAIN_QUERY ';
step NUMBER;
BEGIN
step := 1; -- begin to work -----------------
-- test common filling ******************************************************* vo_Main_Query.SINGLEDATA.ASTRING := '馬鹿 '; vo_Main_Query.SINGLEDATA.AINTEGER := 149478; vo_Main_Query.SINGLEDATA.AFLOAT := 149.478; vo_Main_Query.SINGLEDATA.ADATE := TO_DATE( '1995-10-12 ', 'yyyy-mm-dd '); vo_Main_Query.SINGLEDATA.ACLOB := '猪头 '; -- test array filling ******************************************************** vo_Main_Query.SINGLEDATA.ASTRINGARRAY.Extend(2); vo_Main_Query.SINGLEDATA.ASTRINGARRAY(1) := '马鹿0 '; vo_Main_Query.SINGLEDATA.ASTRINGARRAY(2) := '马鹿1 '; vo_Main_Query.SINGLEDATA.AINTEGERARRAY.Extend(2); vo_Main_Query.SINGLEDATA.AINTEGERARRAY(1) := 2323; vo_Main_Query.SINGLEDATA.AINTEGERARRAY(2) := 4545; vo_Main_Query.SINGLEDATA.AFLOATARRAY.Extend(2); vo_Main_Query.SINGLEDATA.AFLOATARRAY(1) := 1238.90; vo_Main_Query.SINGLEDATA.AFLOATARRAY(2) := -234.45; vo_Main_Query.SINGLEDATA.ADATEARRAY.Extend(2); vo_Main_Query.SINGLEDATA.ADATEARRAY(1) := TO_DATE( '1996-10-12 ', 'yyyy-mm-dd '); vo_Main_Query.SINGLEDATA.ADATEARRAY(2) := TO_DATE( '1997-10-12 ', 'yyyy-mm-dd '); -- test sql multi-filling *********************************************************** SELECT BT_SUB_DATA( t.table_name, BT_STRING_TAB(), t.pct_free, BT_INTEGER_TAB(), t.pct_used, bt_float_tab(), t.last_analyzed, bt_date_tab(), t.tablespace_name ) BULK COLLECT INTO vo_Main_Query.ARRAYDATA FROM all_tables t; -- test sql singal-filling *********************************************************** SELECT BT_SUB_DATA( t.table_name, BT_STRING_TAB(), t.pct_free, BT_INTEGER_TAB(), t.pct_used, bt_float_tab(), t.last_analyzed, bt_date_tab(), t.tablespace_name ) INTO vo_Main_Query.SINGLEDATA FROM all_tables t WHERE t.TABLE_NAME = 'DUAL '; SELECT COUNT(*) INTO vo_Main_Query.TOTALCOUNT FROM all_tables; -- end --------------------------- step := 10; vo_OperationStatus := 0; vo_OperationStatusDesc := 'アホ '; --COMMIT;
EXCEPTION WHEN OTHERS THEN vo_OperationStatus := 109521; vo_OperationStatusDesc := SQLERRM; --dbms_output.put_line( 'Error occurs at step ' ¦ ¦ TO_CHAR(step) ¦ ¦ ' of ' ¦ ¦ PCK_NAME ¦ ¦ '. ' ¦ ¦ PRC_NAME); --ROLLBACK; END;
BEGIN NULL; END MYTEST; /
如上,构建了一个简单填充;问题是c#中如何传入和传出自定义的TYPE的数据?
只知道java中是用MutableStruct构建对等结构以后再在绑定变量时使用setObject 而不是setParameter做的;并且声明时声明为OracleType.STRUCT(c#里面没这个??!)
delphi中使用DOA或者ODAC均是以原型的TOraObject/TOracleObject和TOraArray等来 构建对等结构的;
有那位高人给指点一下,c#该怎么做?
|