/* * VERSION: 12.4.0 * LAST MODIFIED TIME: 2024-02-19 */ ----管理员工具包------------------ CREATE OR REPLACE PACKAGE SYSDBA.DBMS_DBA IS PROCEDURE KILL_TRANS(NODEID INTEGER,TRANID BIGINT); PROCEDURE KILL_SESSION(NODEID INTEGER,SESSID INTEGER); PROCEDURE KILL_SESSION_TRANS(NODEID INTEGER,SESSID INTEGER); END SYSDBA.DBMS_DBA; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_DBA IS PROCEDURE KILL_TRANS(NODEID INTEGER,TRANID BIGINT) IS LANGUAGE "C" NAME "BLDIN_KILL_TRANS"; PROCEDURE KILL_SESSION(NODEID INTEGER,SESSID INTEGER) IS LANGUAGE "C" NAME "BLDIN_KILL_SESSION"; PROCEDURE KILL_SESSION_TRANS(NODEID INTEGER,SESSID INTEGER) IS LANGUAGE "C" NAME "BLDIN_KILL_SESSION_TRANS"; END SYSDBA.DBMS_DBA; / /***********创建DBMS_OUTPUT包*****************************/ CREATE OR REPLACE PACKAGE SYSDBA.DBMS_OUTPUT IS PROCEDURE PUT_LINE(str VARCHAR); END SYSDBA.DBMS_OUTPUT; / ----包体 CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_OUTPUT IS PROCEDURE PUT_LINE(str VARCHAR) IS BEGIN send_msg(str); END; END SYSDBA.DBMS_OUTPUT; / /***********创建DBMS_INFO包(用于查询系统信息)***************/ ----包头 CREATE PACKAGE SYSDBA.DBMS_INFO IS SUBTYPE FileInfo IS RECORD(IS_DIR BOOLEAN,DB_PATH VARCHAR(256),OS_PATH VARCHAR(256)); SUBTYPE FInfoTab IS TABLE OF FileInfo; FUNCTION FILE_LIST(_dir VARCHAR) RETURN FInfoTab; END SYSDBA.DBMS_INFO; / ----包体 CREATE PACKAGE BODY SYSDBA.DBMS_INFO IS FUNCTION FILE_LIST(_dir VARCHAR) RETURN FInfoTab IS LANGUAGE "C" NAME "BLDIN_FILE_LIST"; END SYSDBA.DBMS_INFO; / ----网络测试包------------------ CREATE OR REPLACE PACKAGE SYSDBA.DBMS_TEST IS FUNCTION TEST_PING_PONG1(TARG_NID INTEGER,PACK_NUM INTEGER) RETURN INTEGER; FUNCTION TEST_PING_PONG2(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER; FUNCTION TEST_MSG_SEND1(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER; FUNCTION TEST_MSG_SEND2(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER; FUNCTION TEST_RPC_SEND(TARG_NID INTEGER,SEND_SIZE INTEGER) RETURN INTEGER; END SYSDBA.DBMS_TEST ; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_TEST IS FUNCTION TEST_PING_PONG1(TARG_NID INTEGER,PACK_NUM INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_TEST_PING_PONG1"; FUNCTION TEST_PING_PONG2(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_TEST_PING_PONG2"; FUNCTION TEST_MSG_SEND1(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_TEST_MSG_SEND1"; FUNCTION TEST_MSG_SEND2(TARG_NID INTEGER,PACK_SIZE INTEGER,PACK_NUM INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_TEST_MSG_SEND2"; FUNCTION TEST_RPC_SEND(TARG_NID INTEGER,SEND_SIZE INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_TEST_RPC_SEND"; END SYSDBA.DBMS_TEST ; / -----统计信息设置包------------- CREATE OR REPLACE PACKAGE SYSDBA.DBMS_STAT IS PROCEDURE SET_STAT_INFO(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR,MAX_VAL VARCHAR,MIN_VAL VARCHAR,REPET_RATE DOUBLE,DISPERSION DOUBLE,NON_NULL_RATE DOUBLE DEFAULT 0.0); PROCEDURE ANALYZE_TABLE(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR,SAMPLE_RATE INTEGER,HSITOGRAM_NUM INTEGER); PROCEDURE SET_ANALYZE_SCHEME(TABLE_NAME VARCHAR,SCHE_NAME VARCHAR,ENABLE BOOL); PROCEDURE SET_ANALYZE_PARAM(TABLE_NAME VARCHAR,MODE TINYINT,THRESHOLD TINYINT,LEVEL TINYINT); PROCEDURE SET_ANALYZE_OPTIMIZE(TABLE_NAME VARCHAR,PERIOD SMALLINT,MODE TINYINT); PROCEDURE SET_ANALYZE_COLUMNS(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR,STATUS TINYINT); FUNCTION GET_STAT_INFO(TABLE_NAME VARCHAR) RETURN VARCHAR; END SYSDBA.DBMS_STAT ; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_STAT IS PROCEDURE SET_STAT_INFO(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR, MAX_VAL VARCHAR,MIN_VAL VARCHAR,REPET_RATE DOUBLE,DISPERSION DOUBLE,NON_NULL_RATE DOUBLE DEFAULT 0.0) IS LANGUAGE "C" NAME "BLDIN_SET_STAT_INFO"; PROCEDURE ANALYZE_TABLE(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR,SAMPLE_RATE INTEGER,HSITOGRAM_NUM INTEGER) IS LANGUAGE "C" NAME "BLDIN_ANALYZE_TABLE"; PROCEDURE SET_ANALYZE_SCHEME(TABLE_NAME VARCHAR,SCHE_NAME VARCHAR,ENABLE BOOL) IS LANGUAGE "C" NAME "BLDIN_SET_ANALYZE_SCHEME"; PROCEDURE SET_ANALYZE_PARAM(TABLE_NAME VARCHAR,MODE TINYINT,THRESHOLD TINYINT,LEVEL TINYINT) IS LANGUAGE "C" NAME "BLDIN_SET_ANALYZE_PARAM"; PROCEDURE SET_ANALYZE_OPTIMIZE(TABLE_NAME VARCHAR,PERIOD SMALLINT,OPTI_MODE TINYINT) IS LANGUAGE "C" NAME "BLDIN_SET_ANALYZE_OPTIMIZE"; PROCEDURE SET_ANALYZE_COLUMNS(TABLE_NAME VARCHAR,FIELD_NAME VARCHAR,STATUS TINYINT) IS LANGUAGE "C" NAME "BLDIN_SET_ANALYZE_COLUMNS"; FUNCTION GET_STAT_INFO(TABLE_NAME VARCHAR) RETURN VARCHAR IS LANGUAGE "C" NAME "BLDIN_GET_STAT_INFO"; END SYSDBA.DBMS_STAT ; / ----系统导入包，使用流方式快速导入数据，主要导入完成需要手动创建索引------- CREATE OR REPLACE PACKAGE SYSDBA.DBMS_IMPORT IS /* 列分隔符格式数据导入 */ FUNCTION IMPORT_TABLE_FROM_TXT(TABLE_NAME VARCHAR, /* 表名 */ MODE INTEGER, /* 追加模式 1:append 2:replace 异常值：append*/ FLD_SEPARATOR VARCHAR, /* 列分隔符（仅支持单字符） */ REINDEX BOOL) /* 已暂未使用 */ RETURN BIGINT; /* 返回导入行数 */ /* 长度指示格式数据导入 */ FUNCTION IMPORT_TABLE_FROM_NET(TABLE_NAME VARCHAR, /* 表名 */ MODE INTEGER, /* 追加模式 1:append 2:replace 异常值：append*/ REINDEX BOOL) /* 已暂未使用 */ RETURN BIGINT; /* 返回导入行数 */ END SYSDBA.DBMS_TEST; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_IMPORT IS FUNCTION IMPORT_TABLE_FROM_TXT(TABLE_NAME VARCHAR,MODE INTEGER,FLD_SEPARATOR VARCHAR,REINDEX BOOL) RETURN BIGINT IS LANGUAGE "C" NAME "BLDIN_IMPORT_TABLE_FROM_TXT"; FUNCTION IMPORT_TABLE_FROM_NET(TABLE_NAME VARCHAR,MODE INTEGER,REINDEX BOOL) RETURN BIGINT IS LANGUAGE "C" NAME "BLDIN_IMPORT_TABLE_FROM_NET"; END SYSDBA.DBMS_IMPORT ; / /***********创建DBMS_SCHEDULER包(用于查询系统信息)***************/ ----包头 CREATE OR REPLACE PACKAGE SYSDBA.DBMS_SCHEDULER AUTHID CURRENT_USER IS PROCEDURE CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL); PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2); PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN TIMESTAMP); PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN PLS_INTEGER); PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN BOOLEAN); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN VARCHAR2); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN VARCHAR2); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN TINYINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN TINYINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN SMALLINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN SMALLINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN INTEGER); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN INTEGER); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN BIGINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN BIGINT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN FLOAT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN FLOAT); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DOUBLE); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DOUBLE); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN NUMERIC); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN NUMERIC); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN BOOLEAN); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN BOOLEAN); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN TIME); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN TIME); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DATE); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DATE); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DATETIME); PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DATETIME); PROCEDURE RUN_JOB ( job_name IN VARCHAR2, use_current_session IN BOOLEAN DEFAULT TRUE); PROCEDURE ENABLE ( name IN VARCHAR2); PROCEDURE DISABLE ( name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE); PROCEDURE DROP_JOB ( job_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE); FUNCTION JOB_NEXT_RUNTIME( job_name IN VARCHAR2, last_rt IN DATETIME) RETURN DATETIME; FUNCTION NEXT_RUNTIME( repeat_interval IN VARCHAR2, last_rt IN DATETIME) RETURN DATETIME; END SYSDBA.DBMS_SCHEDULER; / ----包体 CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_SCHEDULER IS PROCEDURE CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_CREATE_JOB"; PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2) IS LANGUAGE "C" NAME "BLDIN_SET_SET_STR_ATTRIBUTE"; PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN TIMESTAMP) IS LANGUAGE "C" NAME "BLDIN_SET_SET_DT_ATTRIBUTE"; PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN PLS_INTEGER) IS LANGUAGE "C" NAME "BLDIN_SET_SET_I4_ATTRIBUTE"; PROCEDURE SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN BOOLEAN) IS LANGUAGE "C" NAME "BLDIN_SET_SET_BOOL_ATTRIBUTE"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN VARCHAR2) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_STR_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN VARCHAR2) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_STR_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN INTEGER) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I4_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN INTEGER) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I4_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN TINYINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I1_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN TINYINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I1_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN SMALLINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I2_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN SMALLINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I2_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN BIGINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I8_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN BIGINT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_I8_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN FLOAT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_R4_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN FLOAT) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_R4_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DOUBLE) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_R8_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DOUBLE) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_R8_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN NUMERIC) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_NUM_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN NUMERIC) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_NUM_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN BOOLEAN) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_BOOL_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN BOOLEAN) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_BOOL_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN TIME) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_TIME_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN TIME) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_TIME_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DATE) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_DATE_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DATE) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_DATE_ARG_BY_NAME"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN DATETIME) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_DT_ARG_BY_ORD"; PROCEDURE SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN DATETIME) IS LANGUAGE "C" NAME "BLDIN_SET_JOB_DT_ARG_BY_NAME"; PROCEDURE RUN_JOB ( job_name IN VARCHAR2, use_current_session IN BOOLEAN DEFAULT TRUE) IS LANGUAGE "C" NAME "BLDIN_RUN_JOB"; PROCEDURE ENABLE ( name IN VARCHAR2) IS LANGUAGE "C" NAME "BLDIN_ENABLE_JOB"; PROCEDURE DISABLE ( name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE) IS LANGUAGE "C" NAME "BLDIN_DISABLE_JOB"; PROCEDURE DROP_JOB ( job_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE) IS LANGUAGE "C" NAME "BLDIN_DROP_JOB"; FUNCTION JOB_NEXT_RUNTIME( job_name IN VARCHAR2, last_rt IN DATETIME) RETURN DATETIME IS LANGUAGE "C" NAME "BLDIN_GET_JOB_NEXT_RUNTIME"; FUNCTION NEXT_RUNTIME( repeat_interval IN VARCHAR2, last_rt IN DATETIME) RETURN DATETIME IS LANGUAGE "C" NAME "BLDIN_GET_NEXT_RUNTIME"; END SYSDBA.DBMS_SCHEDULER; / /**************创建DBMS_SQL包********************/ CREATE OR REPLACE PACKAGE SYSDBA.DBMS_SQL AUTHID CURRENT_USER IS --参数描述信息结构 SUBTYPE TParaInfo IS RECORD(ParaName VARCHAR(128), --参数名 ParaNo INTEGER, --编号 ParaTid INTEGER, --类型ID ParaScale INTEGER, --精度标度 BindState INTEGER); --绑定状态(0:未绑定 1:已绑定) SUBTYPE TParams IS TABLE OF TParaInfo; --结果字段描述信息结构 SUBTYPE TResInfo IS RECORD(ResName VARCHAR(128), --输出字段名 ResTid INTEGER, --输出字段类型 ResScale INTEGER, --输出精度标度 ResFlag INTEGER, --输出附加标志 TargTid INTEGER, --接收变量的类型ID TargScale INTEGER); --接收变量的类型精度标度 SUBTYPE TResults IS TABLE OF TResInfo; --游标对象结构 SUBTYPE TCursor IS RECORD(StmtState INTEGER, --当前状态 StmtType INTEGER, --parse返回的语句类型 StmtSQL VARCHAR(2000), --SQL语句 ObjPtr VARCHAR(20), --内部对象名(归系统使用) ParaInfos TParams, --参数描述表 ResInfos TResults); --输出描述表 SUBTYPE CursorVarr IS VARRAY(100) OF TCursor; --常量定义 NATIVE INTEGER :=1; TYPE_BOOL INTEGER :=2; TYPE_I1 INTEGER :=3; TYPE_I2 INTEGER :=4; TYPE_I4 INTEGER :=5; TYPE_I8 INTEGER :=6; TYPE_NUMERIC INTEGER :=7; TYPE_R4 INTEGER :=8; TYPE_R8 INTEGER :=9; TYPE_DATE INTEGER :=10; TYPE_TIME INTEGER :=11; TYPE_TIMEZ INTEGER :=12; TYPE_DATETIME INTEGER :=13; TYPE_DATETIMEZ INTEGER :=14; TYPE_INTERVAL_Y INTEGER :=15; TYPE_INTERVAL_Y2M INTEGER :=16; TYPE_INTERVAL_M INTEGER :=17; TYPE_INTERVAL_D INTEGER :=18; TYPE_INTERVAL_D2H INTEGER :=19; TYPE_INTERVAL_H INTEGER :=20; TYPE_INTERVAL_D2M INTEGER :=21; TYPE_INTERVAL_H2M INTEGER :=22; TYPE_INTERVAL_MI INTEGER :=23; TYPE_INTERVAL_D2S INTEGER :=24; TYPE_INTERVAL_H2S INTEGER :=25; TYPE_INTERVAL_M2S INTEGER :=26; TYPE_INTERVAL_S INTEGER :=27; TYPE_ROWVER INTEGER :=28; TYPE_GUID INTEGER :=29; TYPE_STR INTEGER :=30; TYPE_CHAR INTEGER :=30; TYPE_VARCHAR INTEGER :=30; TYPE_CLOB INTEGER :=32; TYPE_BINARY INTEGER :=33; TYPE_BLOB INTEGER :=34; TYPE_ROWID INTEGER :=45; --SQL状态 STMT_STATE_OPENED INTEGER := 1; STMT_STATE_PARSED INTEGER := 2; STMT_STATE_BINDED INTEGER := 3; STMT_STATE_DEFINED INTEGER := 4; STMT_STATE_EXCUTED INTEGER := 5; STMT_STATE_FETCHED INTEGER := 6; --游标对象数组 Cursors CursorVarr; FUNCTION STROF_CURSOR(CurNo INTEGER) RETURN VARCHAR(5000); FUNCTION OPEN_CURSOR() RETURN INTEGER; FUNCTION IS_OPEN(c IN INTEGER) RETURN BOOLEAN; FUNCTION PARSE(CurNo INTEGER,StmtSQL VARCHAR,SqlType INTEGER) RETURN INTEGER; FUNCTION EXECUTE(CurNo INTEGER) RETURN INTEGER; FUNCTION FETCH_ROWS(CurNo INTEGER) RETURN INTEGER; FUNCTION CLOSE_CURSOR(CurNo INTEGER) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BOOLEAN) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TINYINT) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val SMALLINT) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTEGER) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BIGINT) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val FLOAT) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DOUBLE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val NUMERIC) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val VARCHAR) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val CLOB) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BLOB) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TIME) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TIME WITH TIME ZONE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATETIME) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATETIME WITH TIME ZONE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL YEAR) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL YEAR TO MONTH) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MONTH) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO HOUR) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO MINUTE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR TO MINUTE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MINUTE) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO SECOND) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR TO SECOND) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MINUTE TO SECOND) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL SECOND) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val GUID) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BINARY) RETURN INTEGER; FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val ROWID) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BOOLEAN) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TINYINT) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT SMALLINT) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTEGER) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BIGINT) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT FLOAT) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DOUBLE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT NUMERIC) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT VARCHAR) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT CLOB) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BLOB) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TIME) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TIME WITH TIME ZONE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME WITH TIME ZONE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR TO MONTH) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MONTH) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO HOUR) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO MINUTE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO MINUTE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO SECOND) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO SECOND) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE TO SECOND) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL SECOND) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT GUID) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BINARY) RETURN INTEGER; FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT ROWID) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BOOLEAN) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TINYINT) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT SMALLINT) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTEGER) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BIGINT) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT FLOAT) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DOUBLE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT NUMERIC) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT VARCHAR) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT CLOB) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BLOB) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TIME) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TIME WITH TIME ZONE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME WITH TIME ZONE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR TO MONTH) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MONTH) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO HOUR) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO MINUTE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO MINUTE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO SECOND) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO SECOND) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE TO SECOND) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL SECOND) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT GUID) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BINARY) RETURN INTEGER; FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT ROWID) RETURN INTEGER; END SYSDBA.DBMS_SQL; / /**************创建DBMS_SQL包体********************/ CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_SQL IS FUNCTION BLDIN_PARSE(sql_str VARCHAR,sys_obj OUT VARCHAR,paras_info OUT TParams,targs_info OUT TResults) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_PARSE"; FUNCTION BLDIN_EXECUTE(ObjName VARCHAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_EXECUTE"; FUNCTION BLDIN_FETCH_ROWS (ObjName VARCHAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_FETCH"; FUNCTION BLDIN_CLOSE_CURSOR(Cur VARCHAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_CLOSE_STMT"; FUNCTION BLDIN_PARA_NAME_CMP(sor_name VARCHAR,targ_name VARCHAR) RETURN BOOLEAN IS LANGUAGE "C" NAME "BLDIN_PARA_NAME_CMP"; FUNCTION BLDIN_BIND_BOOL(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val BOOLEAN) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_BOOL(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val BOOLEAN) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_I1(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val TINYINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_I1(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val TINYINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_I2(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val SMALLINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_I2(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val SMALLINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_I4(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_I4(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_I8(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val BIGINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_I8(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val BIGINT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_R4(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val FLOAT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_R4(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val FLOAT) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_R8(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val DOUBLE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_R8(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val DOUBLE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_Str(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val VARCHAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_Str(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val VARCHAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_CLOB(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val CLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_CLOB(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val CLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_BLOB(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val BLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_BLOB(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val BLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_T(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val TIME) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_T(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val TIME) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_TZ(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val TIME WITH TIME ZONE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_TZ(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val TIME WITH TIME ZONE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_D(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val DATE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_D(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val DATE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_DT(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val DATETIME) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_DT(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val DATETIME) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_DTZ(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val DATETIME WITH TIME ZONE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_DTZ(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val DATETIME WITH TIME ZONE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IY(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL YEAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IY(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL YEAR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IY2M(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL YEAR TO MONTH) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IY2M(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL YEAR TO MONTH) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IM(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL MONTH) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IM(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL MONTH) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_ID(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL DAY) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_ID(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL DAY) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_ID2H(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL DAY TO HOUR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_ID2H(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL DAY TO HOUR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IH(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL HOUR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IH(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL HOUR) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_ID2M(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL DAY TO MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_ID2M(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL DAY TO MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IH2M(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL HOUR TO MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IH2M(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL HOUR TO MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IMI(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IMI(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL MINUTE) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_ID2S(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL DAY TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_ID2S(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL DAY TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IH2S(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL HOUR TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IH2S(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL HOUR TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IM2S(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL MINUTE TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IM2S(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL MINUTE TO SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_IS(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val INTERVAL SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_IS(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val INTERVAL SECOND) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_N(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val NUMERIC) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_N(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val NUMERIC) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_GUID(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val GUID) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_GUID(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val GUID) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_BINARY(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val BINARY) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_BINARY(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val BINARY) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_BIND_ROWID(obj_name VARCHAR,para_no INTEGER,para_tid INTEGER,para_val ROWID) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND"; FUNCTION BLDIN_NAME_BIND_ROWID(obj_name VARCHAR,para_name VARCHAR,para_tid INTEGER,para_val ROWID) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BIND_BY_NAME"; FUNCTION BLDIN_DEFINE_COLUMN (ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_DEFINE_COLUMN"; FUNCTION BLDIN_COLUMN_VALUE_BOOL(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN BOOLEAN IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_I1(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN TINYINT IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_I2(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN SMALLINT IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_I4(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_I8(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN BIGINT IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_R4(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN FLOAT IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_R8(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN DOUBLE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_N(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN NUMERIC IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_STR(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN VARCHAR IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_CLOB(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN CLOB IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_BLOB(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN BLOB IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_T(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN TIME IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_TZ(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN TIME WITH TIME ZONE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_D(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN DATE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_DT(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN DATETIME IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_DTZ(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN DATETIME WITH TIME ZONE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IY(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL YEAR IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IY2M(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL YEAR TO MONTH IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IM(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL MONTH IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_ID(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL DAY IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_ID2H(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL DAY TO HOUR IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IH(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL HOUR IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_ID2M(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL DAY TO MINUTE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IH2M(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL HOUR TO MINUTE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IMI(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL MINUTE IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_ID2S(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL DAY TO SECOND IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IH2S(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL HOUR TO SECOND IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IM2S(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL MINUTE TO SECOND IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_IS(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN INTERVAL SECOND IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_GUID(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN GUID IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_BINARY(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN BINARY IS LANGUAGE "C" NAME "BLDIN_GET"; FUNCTION BLDIN_COLUMN_VALUE_ROWID(ObjName VARCHAR,ColNo INTEGER,var_tid INTEGER,var_scale INTEGER) RETURN ROWID IS LANGUAGE "C" NAME "BLDIN_GET"; /******************检查序号范围***********/ FUNCTION CHECK_CURNO_RANGE(CurNo INTEGER) RETURN INTEGER IS BEGIN IF (CurNo ISNULL OR CurNo<1 OR CurNo>100) THEN RAISE_APPLICATION_ERROR(-1002,'游标序号错误'); RETURN -1; END IF; END; /******************检查存在性***********/ FUNCTION CHECK_CURNO_EXIST(CurNo INTEGER) RETURN INTEGER IS BEGIN IF cursors(CurNo).StmtState ISNULL THEN RAISE_APPLICATION_ERROR(-1003,'序号为'||CurNo||'的游标不存在'); RETURN -1; ENDIF; END; /******************检查语句状态***********/ FUNCTION CHECK_CURNO_STATE(CurNo INTEGER,STATE INTEGER) RETURN INTEGER IS BEGIN IF STATE = STMT_STATE_PARSED AND cursors(CurNo).StmtState < STMT_STATE_PARSED THEN RAISE_APPLICATION_ERROR(-1005,'序号为'||CurNo||'的游标未经过parse阶段'); RETURN -1; ELSIF STATE = STMT_STATE_EXCUTED AND cursors(CurNo).StmtState < STMT_STATE_EXCUTED THEN RAISE_APPLICATION_ERROR(-1005,'序号为'||CurNo||'的游标未经过execute阶段'); RETURN -1; ELSIF STATE = STMT_STATE_FETCHED AND cursors(CurNo).StmtState < STMT_STATE_FETCHED THEN RAISE_APPLICATION_ERROR(-1005,'序号为'||CurNo||'的游标未经过fetch阶段'); RETURN -1; ENDIF; END; /******************检查序号**************/ FUNCTION CHECK_CURNO(CurNo INTEGER,STATE INTEGER) RETURN INTEGER IS BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 CHECK_CURNO_EXIST(CurNo); --检查语句状态 CHECK_CURNO_STATE(CurNo,STATE); END; /******************检查序号范围和存在性**************/ FUNCTION CHECK_CURNO_RANGE_AND_EXIST(CurNo INTEGER) RETURN INTEGER IS BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 CHECK_CURNO_EXIST(CurNo); END; /******************检查参数是否已绑定**************/ FUNCTION CHECK_PARA_BIND_STATE(Paras TParams) RETURN INTEGER IS para TParaInfo; BEGIN --遍历参数表检查所有参数是否都已绑定 IF(Paras NOTNULL AND Paras.count>0) THEN FOR i IN 1 .. Paras.COUNT LOOP para:=Paras(i); if(para ISNULL OR para.BindState=0) THEN RAISE_APPLICATION_ERROR(-1006, '参数'||para.ParaName||'未绑定'); ENDIF; END FOR; ENDIF; END; /******************设置绑定参数的绑定状态，更新绑定参数的类型**************/ FUNCTION SET_BIND_PARA_INFO(Paras IN OUT TParams,ParaName VARCHAR,ParaTid INTEGER) RETURN INTEGER IS para_no INTEGER; BEGIN --按名给参数表中对应的参数标记绑定位，并更新绑定的参数类型。 IF(Paras NOTNULL AND Paras.count>0) THEN para_no:=0; FOR i IN 1 .. Paras.COUNT LOOP if(Paras(i) NOTNULL AND (BLDIN_PARA_NAME_CMP(Paras(i).ParaName,ParaName) OR BLDIN_PARA_NAME_CMP(Paras(i).ParaName,(':'||ParaName)))) THEN Paras(i).BindState:=1; Paras(i).ParaTid:=ParaTid; para_no:=i; EXIT; ENDIF; END FOR; IF(para_no=0) THEN RAISE_APPLICATION_ERROR(-1007,'绑定参数对象'||ParaName||'不存在'); ENDIF; ENDIF; END; /******************生成编号为CurNo的Cursor的文本信息,用于支持调试***********/ FUNCTION STROF_CURSOR(CurNo INTEGER) RETURN VARCHAR(5000) IS cur TCursor; str VARCHAR(5000); para TParaInfo; targ TResInfo; BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 CHECK_CURNO_EXIST(CurNo); cur:=cursors(CurNo); --输出状态,内部对象地址,SQL语句等 str:='Cursor{' || NEWLINE || ' state=' || cur.StmtState || ' ' || 'ObjPtr=' || cur.ObjPtr || NEWLINE || ' SQL=' || cur.StmtSQL || NEWLINE; --输出参数信息 IF(cur.ParaInfos NOTNULL AND cur.ParaInfos.count>0) THEN str:=str || ' Paras[' || NEWLINE; FOR i IN 1 .. cur.ParaInfos.COUNT LOOP para:=cur.ParaInfos(i); str:=str || ' (' || para.ParaName || ' ' || para.ParaNo || ' ' || para.ParaTid || ' ' || para.ParaScale || ')' || NEWLINE; END FOR; str:=str || ' ]' || NEWLINE; ENDIF; --输出结果集字段信息 IF(cur.ResInfos NOTNULL AND cur.ResInfos.count>0) THEN str:=str || ' Targets[' || NEWLINE; FOR i IN 1 .. cur.ResInfos.COUNT LOOP targ:=cur.ResInfos(i); str:=str || ' (' || targ.ResName || ' ' || targ.ResTid || ' ' || targ.ResScale || ' ' || targ.ResFlag || ')'||NEWLINE; END FOR; str:=str || ' ]' || NEWLINE; ENDIF; --返回结果 RETURN str; END; /*****************打开一个游标(分配一个游标对象,返回游标编号)**************/ FUNCTION OPEN_CURSOR() RETURN INTEGER IS i INTEGER; BEGIN /*从数组中找出空值项,并在此生成cursor对象*/ FOR i IN 1...100 LOOP IF (Cursors(i).StmtState ISNULL) THEN Cursors(i).StmtState := STMT_STATE_OPENED; RETURN i; END IF; END FOR; RAISE_APPLICATION_ERROR(-1001,'打开游标太多(最多100个)'); RETURN -1; END; /*****************关闭一个游标*****************************/ FUNCTION CLOSE_CURSOR(CurNo INTEGER) RETURN INTEGER IS BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 CHECK_CURNO_EXIST(CurNo); --调用内部函数关闭内核对象 IF (cursors(CurNo).ObjPtr NOTNULL) THEN BLDIN_CLOSE_CURSOR(cursors(CurNo).ObjPtr); ENDIF; cursors(CurNo).StmtState := NULL; cursors(CurNo).StmtSQL := NULL; cursors(CurNo).ObjPtr := NULL; cursors(CurNo).ParaInfos := NULL; cursors(CurNo).ResInfos := NULL; RETURN 1; END; /*****************检查一个游标是否处于开启状态***************************/ FUNCTION IS_OPEN(CurNo IN INTEGER) RETURN BOOLEAN IS BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 IF (cursors(CurNo).StmtState IS NULL) THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; /********************为Cursor设置一个SQL语句,并分析此语句*****************/ FUNCTION PARSE(CurNo INTEGER,SqlStr VARCHAR,SqlType INTEGER) RETURN INTEGER IS cur TCursor; BEGIN --检查序号范围 CHECK_CURNO_RANGE(CurNo); --检查存在性 CHECK_CURNO_EXIST(CurNo); --设置SQL语句 cursors(CurNo).StmtSQL:=SqlStr; --调用内核执行语句分析与规划 cursors(CurNo).StmtType := BLDIN_PARSE(SqlStr,cursors(CurNo).ObjPtr,cursors(CurNo).ParaInfos,cursors(CurNo).ResInfos); --修改cursor状态 IF(cursors(CurNo).StmtType>0) THEN cursors(CurNo).StmtState := STMT_STATE_PARSED; --表示已分析,但未绑定变量 ELSE RAISE_APPLICATION_ERROR(-1004,'未知的SQL语句'); END IF; --返回语句类型 RETURN cursors(CurNo).StmtType; END; /**************执行游标语句*************/ FUNCTION EXECUTE(CurNo INTEGER) RETURN INTEGER IS cur_name VARCHAR; ret INTEGER; BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); CHECK_PARA_BIND_STATE(cursors(CurNo).ParaInfos); ret := BLDIN_EXECUTE(cursors(CurNo).ObjPtr); cursors(CurNo).StmtState := STMT_STATE_EXCUTED; RETURN ret; END; /****************向前抓取一行*************/ FUNCTION FETCH_ROWS(CurNo INTEGER) RETURN INTEGER IS ret INTEGER; BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_EXCUTED); ret := BLDIN_FETCH_ROWS(cursors(CurNo).ObjPtr); cursors(CurNo).StmtState := STMT_STATE_FETCHED; RETURN ret; END; /*********************绑定BOOLEAN型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BOOLEAN) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_BOOL); RETURN BLDIN_NAME_BIND_BOOL(cursors(CurNo).ObjPtr,col_name,TYPE_BOOL,Val); END; /*********************绑定Tinyint型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TINYINT) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_I1); RETURN BLDIN_NAME_BIND_I1(cursors(CurNo).ObjPtr,col_name,TYPE_I1,Val); END; /*********************绑定SmallInt型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val SMALLINT) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_I2); RETURN BLDIN_NAME_BIND_I2(cursors(CurNo).ObjPtr,col_name,TYPE_I2,Val); END; /*********************绑定Integer型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTEGER) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_I4); RETURN BLDIN_NAME_BIND_I4(cursors(CurNo).ObjPtr,col_name,TYPE_I4,Val); END; /*********************绑定BigInt型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BIGINT) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_I8); RETURN BLDIN_NAME_BIND_I8(cursors(CurNo).ObjPtr,col_name,TYPE_I8,Val); END; /*********************绑定Float型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val Float) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_R4); RETURN BLDIN_NAME_BIND_R4(cursors(CurNo).ObjPtr,col_name,TYPE_R4,Val); END; /*********************绑定Double型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DOUBLE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_R8); RETURN BLDIN_NAME_BIND_R8(cursors(CurNo).ObjPtr,col_name,TYPE_R8,Val); END; /*********************绑定Numeric型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val NUMERIC) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_NUMERIC); RETURN BLDIN_NAME_BIND_N(cursors(CurNo).ObjPtr,col_name,TYPE_NUMERIC,Val); END; /*********************绑定CLOB型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val CLOB) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_CLOB); RETURN BLDIN_NAME_BIND_CLOB(cursors(CurNo).ObjPtr,col_name,TYPE_CLOB,Val); END; /*********************绑定BLOB型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BLOB) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_BLOB); RETURN BLDIN_NAME_BIND_BLOB(cursors(CurNo).ObjPtr,col_name,TYPE_BLOB,Val); END; /*********************绑定VARCHAR型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val VARCHAR) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_VARCHAR); RETURN BLDIN_NAME_BIND_STR(cursors(CurNo).ObjPtr,col_name,TYPE_VARCHAR,Val); END; /*********************绑定Time型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TIME) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_TIME); RETURN BLDIN_NAME_BIND_T(cursors(CurNo).ObjPtr,col_name,TYPE_TIME,Val); END; /*********************绑定 TIME WITH TIME ZONE 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val TIME WITH TIME ZONE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_TIMEZ); RETURN BLDIN_NAME_BIND_TZ(cursors(CurNo).ObjPtr,col_name,TYPE_TIMEZ,Val); END; /*********************绑定Date型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_DATE); RETURN BLDIN_NAME_BIND_D(cursors(CurNo).ObjPtr,col_name,TYPE_DATE,Val); END; /*********************绑定DateTime型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATETIME) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_DATETIME); RETURN BLDIN_NAME_BIND_DT(cursors(CurNo).ObjPtr,col_name,TYPE_DATETIME,Val); END; /*********************绑定DATETIME WITH TIME ZONE型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val DATETIME WITH TIME ZONE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_DATETIMEZ); RETURN BLDIN_NAME_BIND_DTZ(cursors(CurNo).ObjPtr,col_name,TYPE_DATETIMEZ,Val); END; /*********************绑定 INTERVAL YEAR 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL YEAR) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_Y); RETURN BLDIN_NAME_BIND_IY(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_Y,Val); END; /*********************绑定 INTERVAL YEAR TO MONTH 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL YEAR TO MONTH) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_Y2M); RETURN BLDIN_NAME_BIND_IY2M(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_Y2M,Val); END; /*********************绑定 INTERVAL MONTH 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MONTH) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_M); RETURN BLDIN_NAME_BIND_IM(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_M,Val); END; /*********************绑定 INTERVAL DAY 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_D); RETURN BLDIN_NAME_BIND_ID(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_D,Val); END; /*********************绑定 INTERVAL DAY TO HOUR 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO HOUR) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_D2H); RETURN BLDIN_NAME_BIND_ID2H(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_D2H,Val); END; /*********************绑定 INTERVAL HOUR 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_H); RETURN BLDIN_NAME_BIND_IH(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_H,Val); END; /*********************绑定 INTERVAL DAY TO MINUTE 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO MINUTE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_D2M); RETURN BLDIN_NAME_BIND_ID2M(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_D2M,Val); END; /*********************绑定 INTERVAL HOUR TO MINUTE 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR TO MINUTE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_H2M); RETURN BLDIN_NAME_BIND_IH2M(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_H2M,Val); END; /*********************绑定 INTERVAL MINUTE 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MINUTE) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_MI); RETURN BLDIN_NAME_BIND_IMI(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_MI,Val); END; /*********************绑定 INTERVAL DAY TO SECOND 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL DAY TO SECOND) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_D2S); RETURN BLDIN_NAME_BIND_ID2S(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_D2S,Val); END; /*********************绑定 INTERVAL HOUR TO SECOND 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL HOUR TO SECOND) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_H2S); RETURN BLDIN_NAME_BIND_IH2S(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_H2S,Val); END; /*********************绑定 INTERVAL MINUTE TO SECOND 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL MINUTE TO SECOND) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_M2S); RETURN BLDIN_NAME_BIND_IM2S(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_M2S,Val); END; /*********************绑定 INTERVAL SECOND 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val INTERVAL SECOND) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_INTERVAL_S); RETURN BLDIN_NAME_BIND_IS(cursors(CurNo).ObjPtr,col_name,TYPE_INTERVAL_S,Val); END; /*********************绑定 GUID 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val GUID) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_GUID); RETURN BLDIN_NAME_BIND_GUID(cursors(CurNo).ObjPtr,col_name,TYPE_GUID,Val); END; /*********************绑定 BINARY 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val BINARY) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_BINARY); RETURN BLDIN_NAME_BIND_BINARY(cursors(CurNo).ObjPtr,col_name,TYPE_BINARY,Val); END; /*********************绑定 ROWID 型参数**********************/ FUNCTION BIND_VARIABLE(CurNo INTEGER,ColName VARCHAR,Val ROWID) RETURN INTEGER IS col_name VARCHAR:=UPPER(ColName); BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_PARSED); SET_BIND_PARA_INFO(cursors(CurNo).ParaInfos,col_name,TYPE_ROWID); RETURN BLDIN_NAME_BIND_ROWID(cursors(CurNo).ObjPtr,col_name,TYPE_ROWID,Val); END; /***********定义输出类型Boolean***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BOOLEAN) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_BOOL,-1); END; /***********定义输出类型TinyInt***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TINYINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_I1,-1); END; /***********定义输出类型SmallInt***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT SMALLINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_I2,-1); END; /***********定义输出类型Integer***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTEGER) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_I4,-1); END; /***********定义输出类型Bigint***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BIGINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_I8,-1); END; /***********定义输出类型Float***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT FLOAT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_R4,-1); END; /***********定义输出类型Double***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DOUBLE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_R8,-1); END; /***********定义输出类型NUMERIC***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT NUMERIC) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_NUMERIC,-1); END; /***********定义输出类型Char***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT CHAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_STR,-1); END; /***********定义输出类型Varchar***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT VARCHAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_STR,-1); END; /***********定义输出类型CLOB***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT CLOB) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_CLOB,-1); END; /***********定义输出类型BLOB***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BLOB) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_BLOB,-1); END; /***********定义输出类型TIME***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TIME) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_TIME,-1); END; /***********定义输出类型TIME WITH TIME ZONE ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT TIME WITH TIME ZONE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_TIMEZ,-1); END; /***********定义输出类型Date***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_DATE,-1); END; /***********定义输出类型Datetime***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_DATETIME,-1); END; /***********定义输出类型DATETIME WITH TIME ZONE***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME WITH TIME ZONE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_DATETIMEZ,-1); END; /***********定义输出类型 INTERVAL YEAR ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_Y,-1); END; /***********定义输出类型 INTERVAL YEAR TO MONTH ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR TO MONTH) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_Y2M,-1); END; /***********定义输出类型 INTERVAL MONTH ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MONTH) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_M,-1); END; /***********定义输出类型 INTERVAL DAY ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D,-1); END; /***********定义输出类型 INTERVAL DAY TO HOUR ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO HOUR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2H,-1); END; /***********定义输出类型 INTERVAL HOUR ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H,-1); END; /***********定义输出类型 INTERVAL DAY TO MINUTE ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2M,-1); END; /***********定义输出类型 INTERVAL HOUR TO MINUTE ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H2M,-1); END; /***********定义输出类型 INTERVAL MINUTE ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_MI,-1); END; /***********定义输出类型 INTERVAL DAY TO SECOND ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2S,-1); END; /***********定义输出类型 INTERVAL HOUR TO SECOND ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H2S,-1); END; /***********定义输出类型 INTERVAL MINUTE TO SECOND ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_M2S,-1); END; /***********定义输出类型 INTERVAL SECOND ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_S,-1); END; /***********定义输出类型 GUID ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT GUID) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_GUID,-1); END; /***********定义输出类型 BINARY ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT BINARY) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_BINARY,-1); END; /***********定义输出类型 ROWID ***********/ FUNCTION DEFINE_COLUMN(CurNo INTEGER,ColNo INTEGER,Var OUT ROWID) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); RETURN BLDIN_DEFINE_COLUMN(cursors(CurNo).ObjPtr,ColNo,TYPE_ROWID,-1); END; /***********取BOOLEAN字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BOOLEAN) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_BOOL(cursors(CurNo).ObjPtr,ColNo,TYPE_BOOL,-1); RETURN 1; END; /***********取TinyInt字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TINYINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_I1(cursors(CurNo).ObjPtr,ColNo,TYPE_I1,-1); RETURN 1; END; /***********取SmallInt字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT SMALLINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_I2(cursors(CurNo).ObjPtr,ColNo,TYPE_I2,-1); RETURN 1; END; /***********取Integer字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTEGER) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_I4(cursors(CurNo).ObjPtr,ColNo,TYPE_I4,-1); RETURN 1; END; /***********取BigInt字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BIGINT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_I8(cursors(CurNo).ObjPtr,ColNo,TYPE_I8,-1); RETURN 1; END; /***********取Float字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT FLOAT) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_R4(cursors(CurNo).ObjPtr,ColNo,TYPE_R4,-1); RETURN 1; END; /***********取DOUBLE字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DOUBLE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_R8(cursors(CurNo).ObjPtr,ColNo,TYPE_R8,-1); RETURN 1; END; /***********取Numeric字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT NUMERIC) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_N(cursors(CurNo).ObjPtr,ColNo,TYPE_NUMERIC,-1); RETURN 1; END; /***********取Char字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT CHAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_STR(cursors(CurNo).ObjPtr,ColNo,TYPE_STR,-1); RETURN 1; END; /***********取Varchar字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT VARCHAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_STR(cursors(CurNo).ObjPtr,ColNo,TYPE_STR,-1); RETURN 1; END; /***********取CLOB字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT CLOB) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_CLOB(cursors(CurNo).ObjPtr,ColNo,TYPE_CLOB,-1); RETURN 1; END; /***********取BLOB字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BLOB) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_BLOB(cursors(CurNo).ObjPtr,ColNo,TYPE_BLOB,-1); RETURN 1; END; /***********取TIME字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TIME) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_T(cursors(CurNo).ObjPtr,ColNo,TYPE_TIME,-1); RETURN 1; END; /***********取TIME WITH TIME ZONE字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT TIME WITH TIME ZONE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_TZ(cursors(CurNo).ObjPtr,ColNo,TYPE_TIMEZ,-1); RETURN 1; END; /***********取DATE字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_D(cursors(CurNo).ObjPtr,ColNo,TYPE_DATE,-1); RETURN 1; END; /***********取DateTime字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_DT(cursors(CurNo).ObjPtr,ColNo,TYPE_DATETIME,-1); RETURN 1; END; /***********取DATETIME WITH TIME ZONE字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT DATETIME WITH TIME ZONE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_DTZ(cursors(CurNo).ObjPtr,ColNo,TYPE_DATETIMEZ,-1); RETURN 1; END; /***********取 INTERVAL YEAR 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IY(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_Y,-1); RETURN 1; END; /***********取 INTERVAL YEAR TO MONTH 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL YEAR TO MONTH) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IY2M(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_Y2M,-1); RETURN 1; END; /***********取 INTERVAL MONTH字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MONTH) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IM(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_M,-1); RETURN 1; END; /***********取 INTERVAL DAY 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_ID(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D,-1); RETURN 1; END; /***********取 INTERVAL DAY TO HOUR 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO HOUR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_ID2H(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2H,-1); RETURN 1; END; /***********取 INTERVAL HOUR 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IH(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H,-1); RETURN 1; END; /***********取 INTERVAL DAY TO MINUTE 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_ID2M(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2M,-1); RETURN 1; END; /***********取 INTERVAL HOUR TO MINUTE 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IH2M(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H2M,-1); RETURN 1; END; /***********取 INTERVAL MINUTE 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IMI(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_MI,-1); RETURN 1; END; /***********取 INTERVAL DAY TO SECOND 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL DAY TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_ID2S(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_D2S,-1); RETURN 1; END; /***********取 INTERVAL HOUR TO SECOND字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL HOUR TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO(CurNo,STMT_STATE_FETCHED); Var:= BLDIN_COLUMN_VALUE_IH2S(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_H2S,-1); RETURN 1; END; /***********取 INTERVAL MINUTE TO SECOND字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL MINUTE TO SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); Var:= BLDIN_COLUMN_VALUE_IM2S(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_M2S,-1); RETURN 1; END; /***********取 INTERVAL SECOND字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT INTERVAL SECOND) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); Var:= BLDIN_COLUMN_VALUE_IS(cursors(CurNo).ObjPtr,ColNo,TYPE_INTERVAL_S,-1); RETURN 1; END; /***********取 GUID字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT GUID) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); Var:= BLDIN_COLUMN_VALUE_GUID(cursors(CurNo).ObjPtr,ColNo,TYPE_GUID,-1); RETURN 1; END; /***********取 BINARY 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT BINARY) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); Var:= BLDIN_COLUMN_VALUE_BINARY(cursors(CurNo).ObjPtr,ColNo,TYPE_BINARY,-1); RETURN 1; END; /***********取 ROWID 字段值***************/ FUNCTION COLUMN_VALUE(CurNo INTEGER,ColNo INTEGER,Var OUT ROWID) RETURN INTEGER IS BEGIN --检查序号 CHECK_CURNO_RANGE_AND_EXIST(CurNo); Var:= BLDIN_COLUMN_VALUE_ROWID(cursors(CurNo).ObjPtr,ColNo,TYPE_ROWID,-1); RETURN 1; END; BEGIN Cursors.extend(100); END SYSDBA.DBMS_SQL; / /*************创建CTX_DOC包******************************/ CREATE OR REPLACE PACKAGE "SYSDBA"."CTX_DOC" AUTHID CURRENT_USER COMMENT '全文检索支持包' IS SUBTYPE TOKEN_REC IS RECORD(TOKEN VARCHAR(64),OFFSET INTEGER,LENGTH INTEGER); SUBTYPE TOKEN_REC2 IS RECORD(TOKEN VARCHAR(64),REPET_N INTEGER,LENGTH INTEGER); SUBTYPE TOKEN_TAB IS TABLE OF TOKEN_REC; SUBTYPE TOKEN_TAB2 IS TABLE OF TOKEN_REC2; SP_MAX_LEN CONSTANT INTEGER := 0; --按最长词划分 SP_MIN_LEN CONSTANT INTEGER := 1; --按最短词划分 SP_SUB_WORD1 CONSTANT INTEGER := 2; --结果包括大词的所有子词 SP_SUB_WORD2 CONSTANT INTEGER := 4; --结果包括大词有非头部子词 SP_ASCII CONSTANT INTEGER := 8; --结果包括ascii串 SP_DIGIT CONSTANT INTEGER := 16; --结果包括数字串 SPILIT_POLICY INTEGER:= 0; --MAX_LEN VOCA_NAME VARCHAR(20):='STD_VOC'; KEY_TYPE VARCHAR(10):='PRIMARY'; PROCEDURE SET_KEY_TYPE(key_type IN VARCHAR2); --设置键类型 PROCEDURE SET_SPLIT_POLICY(_policy IN INTEGER);--设置分词策略 PROCEDURE TOKENS(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN OUT TOKEN_TAB); PROCEDURE TOKENS(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN VARCHAR2, query_id IN NUMBER DEFAULT 0); PROCEDURE TOKENS(content IN VARCHAR2, restab IN OUT TOKEN_TAB); PROCEDURE TOKENS(content IN CLOB, restab IN OUT TOKEN_TAB); PROCEDURE TOKENS2(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN OUT TOKEN_TAB); PROCEDURE TOKENS2(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN VARCHAR2, query_id IN NUMBER DEFAULT 0); PROCEDURE TOKENS2(content IN VARCHAR2, restab IN OUT TOKEN_TAB); PROCEDURE TOKENS2(content IN CLOB, restab IN OUT TOKEN_TAB); END "SYSDBA"."CTX_DOC"; / /*************创建CTX_DOC包体******************************/ CREATE OR REPLACE PACKAGE BODY "SYSDBA"."CTX_DOC" IS PROCEDURE SET_KEY_TYPE(_key_type IN VARCHAR2) IS BEGIN key_type:=_key_type; END; PROCEDURE SET_SPLIT_POLICY(_policy IN INTEGER) IS BEGIN SPILIT_POLICY:=_policy; END; PROCEDURE BLDIN_TOKENS( voca_name IN VARCHAR2, content IN VARCHAR, restab IN OUT TOKEN_TAB, sp_policy IN INTEGER) IS LANGUAGE "C" NAME "BLDIN_TOKENS1"; PROCEDURE BLDIN_TOKENS2( voca_name IN VARCHAR2, content IN VARCHAR, restab IN OUT TOKEN_TAB, sp_policy IN INTEGER) IS LANGUAGE "C" NAME "BLDIN_TOKENS2"; PROCEDURE TOKENS(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN OUT TOKEN_TAB) IS BEGIN NULL; END; PROCEDURE TOKENS(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN VARCHAR2, query_id IN NUMBER DEFAULT 0) IS BEGIN NULL; END; PROCEDURE TOKENS(content IN VARCHAR2, restab IN OUT TOKEN_TAB) IS BEGIN BLDIN_TOKENS(voca_name,content,restab,SPILIT_POLICY); END; PROCEDURE TOKENS(content IN CLOB, restab IN OUT TOKEN_TAB) IS BEGIN BLDIN_TOKENS(voca_name,content,restab,SPILIT_POLICY); END; PROCEDURE TOKENS2(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN OUT TOKEN_TAB) IS BEGIN NULL; END; PROCEDURE TOKENS2(index_name IN VARCHAR2, textkey IN VARCHAR2, restab IN VARCHAR2, query_id IN NUMBER DEFAULT 0) IS BEGIN NULL; END; PROCEDURE TOKENS2(content IN VARCHAR2, restab IN OUT TOKEN_TAB) IS BEGIN BLDIN_TOKENS2(voca_name,content,restab,SPILIT_POLICY); END; PROCEDURE TOKENS2(content IN CLOB, restab IN OUT TOKEN_TAB) IS BEGIN BLDIN_TOKENS2(voca_name,content,restab,SPILIT_POLICY); END; END "SYSDBA"."CTX_DOC"; / /***********创建DBMS_LOB包(用于支持大对象操作)***************/ ----包头 CREATE OR REPLACE PACKAGE SYSDBA.DBMS_LOB IS FUNCTION GETLENGTH(lob_loc BLOB) RETURN INTEGER; FUNCTION GETLENGTH(lob_loc CLOB) RETURN INTEGER; PROCEDURE READ(lob_loc BLOB,AMOUNT INTEGER,OFFSET INTEGER, OUT_BUFF OUT VARCHAR); PROCEDURE READ(lob_loc CLOB,AMOUNT INTEGER,OFFSET INTEGER, OUT_BUFF OUT VARCHAR); END SYSDBA.DBMS_LOB; / ----包体 CREATE PACKAGE BODY SYSDBA.DBMS_LOB IS FUNCTION GETLENGTH(lob_loc BLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_BLOB_GETLENGTH"; FUNCTION GETLENGTH(lob_loc CLOB) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_CLOB_GETLENGTH"; PROCEDURE READ(lob_loc BLOB,AMOUNT INTEGER,OFFSET INTEGER, OUT_BUFF OUT VARCHAR) IS LANGUAGE "C" NAME "BLDIN_BLOB_READ"; PROCEDURE READ(lob_loc CLOB,AMOUNT INTEGER,OFFSET INTEGER, OUT_BUFF OUT VARCHAR) IS LANGUAGE "C" NAME "BLDIN_CLOB_READ"; END SYSDBA.DBMS_LOB; / -----UTL_RAW包-------------------- CREATE PACKAGE SYSDBA.UTL_RAW IS big_endian CONSTANT PLS_INTEGER := 1; little_endian CONSTANT PLS_INTEGER := 2; machine_endian CONSTANT PLS_INTEGER := 3; FUNCTION BIT_AND (r1 IN RAW, r2 IN RAW) RETURN RAW; FUNCTION BIT_COMPLEMENT (r1 IN RAW, r2 IN RAW) RETURN RAW; FUNCTION BIT_OR (r1 IN RAW, r2 IN RAW) RETURN RAW; FUNCTION BIT_XOR (r1 IN RAW, r2 IN RAW) RETURN RAW; FUNCTION CAST_FROM_BINARY_DOUBLE (n IN BINARY_DOUBLE, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW; FUNCTION CAST_FROM_BINARY_FLOAT (n IN BINARY_FLOAT, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW; FUNCTION CAST_FROM_BINARY_INTEGER (n IN BINARY_INTEGER, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW; FUNCTION CAST_FROM_NUMBER (n IN NUMBER) RETURN RAW; FUNCTION CAST_TO_BINARY_DOUBLE (r IN RAW,endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_DOUBLE; FUNCTION CAST_TO_BINARY_FLOAT (r IN RAW, endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_FLOAT; FUNCTION CAST_TO_BINARY_INTEGER (r IN RAW, endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_INTEGER; FUNCTION CAST_TO_NUMBER (r IN RAW) RETURN NUMBER; FUNCTION CAST_TO_NVARCHAR2 (r IN RAW) RETURN NVARCHAR2; FUNCTION CAST_TO_RAW (c IN VARCHAR2) RETURN RAW; FUNCTION CAST_TO_VARCHAR2 (r IN RAW) RETURN VARCHAR2; FUNCTION COMPARE (r1 IN RAW,r2 IN RAW,pad IN RAW DEFAULT NULL) RETURN INTEGER; FUNCTION CONCAT (r1 IN RAW DEFAULT NULL, r2 IN RAW DEFAULT NULL, r3 IN RAW DEFAULT NULL, r4 IN RAW DEFAULT NULL, r5 IN RAW DEFAULT NULL, r6 IN RAW DEFAULT NULL, r7 IN RAW DEFAULT NULL, r8 IN RAW DEFAULT NULL, r9 IN RAW DEFAULT NULL, r10 IN RAW DEFAULT NULL, r11 IN RAW DEFAULT NULL, r12 IN RAW DEFAULT NULL) RETURN RAW; FUNCTION CONVERT(r IN RAW,to_charset IN VARCHAR2,from_charset IN VARCHAR2) RETURN RAW; FUNCTION COPIES (r IN RAW,n IN INTEGER) RETURN RAW; FUNCTION LENGTH (r IN RAW) RETURN INTEGER; FUNCTION OVERLAY(overlay_str IN RAW,target IN RAW, pos IN BINARY_INTEGER DEFAULT 1, len IN BINARY_INTEGER DEFAULT NULL, pad IN RAW DEFAULT NULL) RETURN RAW; FUNCTION REVERSE (r IN RAW) RETURN RAW; FUNCTION SUBSTR (r IN RAW,pos IN BINARY_INTEGER,len IN BINARY_INTEGER DEFAULT NULL) RETURN RAW; FUNCTION TRANSLATE (r IN RAW,from_set IN RAW,to_set IN RAW) RETURN RAW; FUNCTION TRANSLITERATE (r IN RAW,to_set IN RAW DEFAULT NULL,from_set IN RAW DEFAULT NULL,pad IN RAW DEFAULT NULL) RETURN RAW; FUNCTION XRANGE (start_byte IN RAW DEFAULT NULL,end_byte IN RAW DEFAULT NULL) RETURN RAW; END SYSDBA.UTL_RAW; / CREATE PACKAGE BODY SYSDBA.UTL_RAW IS FUNCTION BIT_AND (r1 IN RAW, r2 IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_BIT_AND"; FUNCTION BIT_COMPLEMENT (r1 IN RAW, r2 IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_COMPLEMENT"; FUNCTION BIT_OR (r1 IN RAW, r2 IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_BIT_OR"; FUNCTION BIT_XOR (r1 IN RAW, r2 IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_BIT_XOR"; FUNCTION CAST_FROM_BINARY_DOUBLE (n IN BINARY_DOUBLE, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_DOUBLE2RAW"; FUNCTION CAST_FROM_BINARY_FLOAT (n IN BINARY_FLOAT, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_FLOAT2RAW"; FUNCTION CAST_FROM_BINARY_INTEGER (n IN BINARY_INTEGER, endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_INTEGER2RAW"; FUNCTION CAST_FROM_NUMBER (n IN NUMBER) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_NUMERIC2RAW"; FUNCTION CAST_TO_BINARY_DOUBLE (r IN RAW,endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_DOUBLE IS LANGUAGE "C" NAME "BLDIN_RAW2DOUBLE"; FUNCTION CAST_TO_BINARY_FLOAT (r IN RAW, endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_FLOAT IS LANGUAGE "C" NAME "BLDIN_RAW2FLOAT"; FUNCTION CAST_TO_BINARY_INTEGER (r IN RAW, endianess IN BINARY_INTEGER DEFAULT 1) RETURN BINARY_INTEGER IS LANGUAGE "C" NAME "BLDIN_RAW2INTEGER"; FUNCTION CAST_TO_NUMBER (r IN RAW) RETURN NUMBER IS LANGUAGE "C" NAME "BLDIN_RAW2NUMERIC"; FUNCTION CAST_TO_NVARCHAR2 (r IN RAW) RETURN NVARCHAR2 IS LANGUAGE "C" NAME "BLDIN_RAW2NVARCHAR2"; FUNCTION CAST_TO_RAW (c IN VARCHAR2) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_VARCHAR22RAW"; FUNCTION CAST_TO_VARCHAR2 (r IN RAW) RETURN VARCHAR2 IS LANGUAGE "C" NAME "BLDIN_RAW2VARCHAE2"; FUNCTION COMPARE (r1 IN RAW,r2 IN RAW,pad IN RAW DEFAULT NULL) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_RAW_COMPARE"; FUNCTION CONCAT (r1 IN RAW DEFAULT NULL, r2 IN RAW DEFAULT NULL, r3 IN RAW DEFAULT NULL, r4 IN RAW DEFAULT NULL, r5 IN RAW DEFAULT NULL, r6 IN RAW DEFAULT NULL, r7 IN RAW DEFAULT NULL, r8 IN RAW DEFAULT NULL, r9 IN RAW DEFAULT NULL, r10 IN RAW DEFAULT NULL, r11 IN RAW DEFAULT NULL, r12 IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_CONCAT"; FUNCTION CONVERT(r IN RAW,to_charset IN VARCHAR2,from_charset IN VARCHAR2) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_CONVERT"; FUNCTION COPIES (r IN RAW,n IN INTEGER) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_COPYS"; FUNCTION LENGTH (r IN RAW) RETURN INTEGER IS LANGUAGE "C" NAME "BLDIN_RAW_LENGTH"; FUNCTION OVERLAY(overlay_str IN RAW,target IN RAW, pos IN BINARY_INTEGER DEFAULT 1, len IN BINARY_INTEGER DEFAULT NULL, pad IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_OVERLAY"; FUNCTION REVERSE (r IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_REVERSE"; FUNCTION SUBSTR (r IN RAW,pos IN BINARY_INTEGER,len IN BINARY_INTEGER DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_SUBSTR"; FUNCTION TRANSLATE (r IN RAW,from_set IN RAW,to_set IN RAW) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_TRANSLATE"; FUNCTION TRANSLITERATE (r IN RAW,to_set IN RAW DEFAULT NULL,from_set IN RAW DEFAULT NULL,pad IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_TRANSLITERATE"; FUNCTION XRANGE (start_byte IN RAW DEFAULT NULL,end_byte IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_RAW_XRANGE"; END SYSDBA.UTL_RAW; / CREATE OR REPLACE PACKAGE SYSDBA.DBMS_REPLICATION IS PROCEDURE CREATE_MODIFY_SOURCE(SCHEMA_NAME VARCHAR,TABLE_NAME VARCHAR); PROCEDURE DROP_MODIFY_SOURCE(SCHEMA_NAME VARCHAR,TABLE_NAME VARCHAR); PROCEDURE CREATE_SUBSCRIBER(SUBSCRIBER_NAME VARCHAR,USEER_NAME VARCHAR); PROCEDURE DROP_SUBSCRIBER(SUBSCRIBER_NAME VARCHAR,USEER_NAME VARCHAR); PROCEDURE SWITCH_FILE(); PROCEDURE POLL_MODIFY_DATA(SUBSCRIBER_NAME VARCHAR,PARTI_NO INTEGER,FILTER VARCHAR,POLL_LEN BIGINT,KEEP_POS BOOLEAN,FNO INTEGER DEFAULT -1,FPOS BIGINT DEFAULT 0,RET_TYPE INTEGER DEFAULT 0); PROCEDURE DUMP_TABLE( TABLE_NAME VARCHAR, --表名 FILTER VARCHAR, --where 条件(不写where关键字) PATH VARCHAR, --导出数据 INSERT SQL 的数据库端输出文件(NULL时不输出到文件) IS_SEND BOOLEAN DEFAULT false, -- 是否将数据 INSERT SQL的结果集 返回客户端 IS_DUMP_META BOOLEAN DEFAULT false);-- 是否在数据前输出表定义DDL END SYSDBA.DBMS_REPLICATION; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_REPLICATION IS PROCEDURE CREATE_MODIFY_SOURCE(SCHEMA_NAME VARCHAR,TABLE_NAME VARCHAR) IS LANGUAGE "C" NAME "BLDIN_CREATE_MODIFY_SOURCE"; PROCEDURE DROP_MODIFY_SOURCE(SCHEMA_NAME VARCHAR,TABLE_NAME VARCHAR) IS LANGUAGE "C" NAME "BLDIN_DROP_MODIFY_SOURCE"; PROCEDURE CREATE_SUBSCRIBER(SUBSCRIBER_NAME VARCHAR,USEER_NAME VARCHAR) IS LANGUAGE "C" NAME "BLDIN_CREATE_SUBSCRIBER"; PROCEDURE DROP_SUBSCRIBER(SUBSCRIBER_NAME VARCHAR,USEER_NAME VARCHAR) IS LANGUAGE "C" NAME "BLDIN_DROP_SUBSCRIBER"; PROCEDURE SWITCH_FILE() IS LANGUAGE "C" NAME "BLDIN_SWITCH_FILE"; PROCEDURE POLL_MODIFY_DATA(SUBSCRIBER_NAME VARCHAR,PARTI_NO INTEGER,FILTER VARCHAR,POLL_LEN BIGINT,KEEP_POS BOOLEAN,FNO INTEGER DEFAULT -1,FPOS BIGINT DEFAULT 0,RET_TYPE INTEGER DEFAULT 0) IS LANGUAGE "C" NAME "BLDIN_POLL_MODIFY_DATA"; PROCEDURE DUMP_TABLE(TABLE_NAME VARCHAR,FILTER VARCHAR,PATH VARCHAR,IS_SEND BOOLEAN DEFAULT false,IS_DUMP_META BOOLEAN DEFAULT false) IS LANGUAGE "C" NAME "BLDIN_DUMP_TABLE"; END SYSDBA.DBMS_REPLICATION; / CREATE OR REPLACE PACKAGE SYSDBA.DBMS_CRYPTO IS -- hash算法 HASH_MD4 INTEGER := 1; HASH_MD5 INTEGER := 2; HASH_SH1 INTEGER := 3; -- 消息认证码hash方式 HMAC_MD5 INTEGER := 1; HMAC_SH1 INTEGER := 2; -- 块加密算法 ENCRYPT_DES INTEGER := 1; -- 0x0001 ENCRYPT_3DES_2KEY INTEGER := 2; -- 0x0002 ENCRYPT_3DES INTEGER := 3; -- 0x0003 ENCRYPT_AES INTEGER := 4; -- 0x0004 ENCRYPT_PBE_MD5DES INTEGER := 5; -- 0x0005 ENCRYPT_AES128 INTEGER := 6; -- 0x0006 ENCRYPT_AES192 INTEGER := 7; -- 0x0007 ENCRYPT_AES256 INTEGER := 8; -- 0x0008 -- 块加密模式 CHAIN_CBC INTEGER := 256; -- 0x0100 CHAIN_CFB INTEGER := 512; -- 0x0200 CHAIN_ECB INTEGER := 768; -- 0x0300 CHAIN_OFB INTEGER := 1024; -- 0x0400 -- 块加密填充方式 PAD_PKCS5 INTEGER := 4096; -- 0x1000 PAD_NONE INTEGER := 8192; -- 0x2000 PAD_ZERO INTEGER := 12288; -- 0x3000 PAD_SELF INTEGER := 16384; -- 0x4000 -- 流加密算法 ENCRYPT_RC4 INTEGER := 129; -- 0x0081 -- 常用加密套件 DES_CBC_PKCS5 INTEGER := ENCRYPT_DES+ CHAIN_CBC+ PAD_PKCS5; DES3_CBC_PKCS5 INTEGER := ENCRYPT_3DES+ CHAIN_CBC+ PAD_PKCS5; AES_CBC_PKCS5 INTEGER := ENCRYPT_AES+ CHAIN_CBC+ PAD_PKCS5; --加密接口 FUNCTION Encrypt (src IN RAW,typ IN INTEGER, key IN RAW,iv IN RAW DEFAULT NULL) RETURN RAW; FUNCTION Encrypt (src IN VARCHAR,typ IN INTEGER, key IN VARCHAR,iv IN VARCHAR DEFAULT NULL) RETURN RAW; PROCEDURE Encrypt (dst IN OUT BLOB,src IN BLOB,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL); PROCEDURE Encrypt (dst IN OUT BLOB,src IN CLOB ,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL); --解密接口 FUNCTION Decrypt (src IN RAW,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL)RETURN RAW; FUNCTION Decrypt (src IN RAW,typ IN INTEGER,key IN VARCHAR,iv IN VARCHAR DEFAULT NULL)RETURN VARCHAR; PROCEDURE Decrypt (dst IN OUT BLOB,src IN BLOB,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL); PROCEDURE Decrypt (dst IN OUT CLOB,src IN BLOB,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL); --hash 接口 FUNCTION Hash (src IN RAW,typ IN INTEGER)RETURN RAW ; FUNCTION Hash (src IN BLOB,typ IN INTEGER)RETURN RAW ; FUNCTION Hash (src IN CLOB,typ IN INTEGER)RETURN RAW ; --消息认证码 FUNCTION Mac (src IN RAW,typ IN INTEGER,key IN RAW)RETURN RAW; FUNCTION Mac (src IN BLOB,typ IN INTEGER,key IN RAW)RETURN RAW; FUNCTION Mac (src IN CLOB,typ IN INTEGER,key IN RAW)RETURN RAW; --随机函数 FUNCTION RandomBytes (number_bytes IN INTEGER) RETURN RAW; FUNCTION RandomNumber RETURN NUMBER; FUNCTION RandomInteger RETURN INTEGER; END SYSDBA.DBMS_CRYPTO; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_CRYPTO IS FUNCTION Encrypt (src IN VARCHAR,typ IN INTEGER, key IN VARCHAR,iv IN VARCHAR DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_ENCRYPT1"; FUNCTION Decrypt (src IN RAW,typ IN INTEGER,key IN VARCHAR,iv IN VARCHAR DEFAULT NULL) RETURN VARCHAR IS LANGUAGE "C" NAME "BLDIN_DECRYPT1"; FUNCTION Encrypt (src IN RAW,typ IN INTEGER, key IN RAW,iv IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_ENCRYPT1"; FUNCTION Decrypt (src IN RAW,typ IN INTEGER,key IN RAW,iv IN RAW DEFAULT NULL) RETURN RAW IS LANGUAGE "C" NAME "BLDIN_DECRYPT1"; END SYSDBA.DBMS_CRYPTO; / CREATE OR REPLACE PACKAGE "SYSDBA"."DBMS_BACKUP" COMMENT '备份应用程序包' IS /** 参数: planname 备份计划名称 plantype 备份类型 系统备份（BAK）or 逻辑备份（EXP） path 备份文件输出路径，暂时只支持服务器端配置路径，后续考虑支持备到客户端 starttime 计划开始时间 period 计划执行周期，分钟为单位 **/ PROCEDURE Create_Plan(planname VARCHAR, plantype VARCHAR, path VARCHAR, starttime DATETIME, period INTERVAL DAY TO MINUTE); /** 参数: planname 备份计划名称 ena true活动态，false禁用态（系统有且仅有一个计划为活动态） **/ PROCEDURE Enable_Plan(planname VARCHAR, ena BOOLEAN); /** 参数: planname 备份计划名称，不可删除活动态计划，删除时同时删除计划下所有项目 **/ PROCEDURE Drop_Plan(planname VARCHAR); /** 系统备份（BAK）项创建接口,包含增量和全备方式 参数: planname 备份计划名称 filename 备份数据文件名 time_offset 相对于计划开始时间的时间偏移（分钟为单位），不能超过计划周期 iperiod 计划执行周期，分钟为单位,全备时此参数为NULL **/ PROCEDURE Add_Backup_Item(planname VARCHAR,filename VARCHAR, time_offset INTERVAL DAY TO MINUTE); PROCEDURE Add_Backup_Inc_Item(planname VARCHAR,time_offset INTERVAL DAY TO MINUTE,iperiod INTERVAL DAY TO MINUTE DEFAULT NULL); /** 逻辑备份（EXP）项创建接口,包含库级和表级 参数: planname 备份计划名称 obj_name 库名或表名 obj_type 备份对象类型 取值（DATABASE,TABLE) filename 备份数据文件名（必须包含路径信息） time_offset 相对于计划开始时间的时间偏移（分钟为单位），不能超过计划周期 **/ PROCEDURE Add_Export_Item(planname VARCHAR,obj_name VARCHAR, obj_type VARCHAR, filename VARCHAR,time_offset INTERVAL DAY TO MINUTE); /** 文件项操作添加接口 参数: planname 备份计划名称 filename 备份数据文件名（必须包含路径信息） bak_count 备份文件保留份数 optype 备份文件处理方式DELETE或MOVE dstpath MOVE 类型是的目标路径 **/ PROCEDURE Add_File_Item(planname VARCHAR,filename VARCHAR, bak_count INTEGER,optype VARCHAR,dstpath VARCHAR DEFAULT NULL); /** 删除系统备份计划子项 参数: planname 备份计划名称 item_n 子项编号（创建时自动生成） **/ PROCEDURE Drop_Item(planname VARCHAR, item_n INTEGER); /** 删除逻辑备份子项和文件操作子项 参数: planname 备份计划名称 objtype 对象类型 取值（'DATABASE','TABLE','FILE'） **/ PROCEDURE Alter_Plan_Del_Item(planname VARCHAR,objtype VARCHAR,objname VARCHAR); /** 设置计划当前项目 参数: planname 备份计划名称 curr_item 当前项目编号 **/ PROCEDURE alter_modify_curr_item(planname VARCHAR,curr_item INTEGER); /** 设置下次运行时间 参数: planname 备份计划名称 curr_item 当前项目编号 next_run_time 下次运行时间 istart_t 增量开始时间 **/ PROCEDURE alter_modify_run_time(planname VARCHAR,curr_item INTEGER,next_run_time DATETIME,istart_t DATETIME DEFAULT NULL); /** 备份计划执行函数 **/ PROCEDURE Run(); /** 日志文件删除接口，暂未使用 **/ PROCEDURE Del_xfn(); PROCEDURE FILE_OPTION(PATH VARCHAR,OBJ_NAME VARCHAR,BAK_COUNT INT,OP_TYPE VARCHAR,PLAN_NAME VARCHAR,ITEM_NO INT); END "SYSDBA"."DBMS_BACKUP"; / CREATE OR Replace Package Body "DBMS_BACKUP" IS /************创建备份计划**********/ PROCEDURE Create_Plan(planname VARCHAR, plantype VARCHAR, path VARCHAR, starttime DATETIME, period INTERVAL DAY TO MINUTE) IS LANGUAGE "C" NAME "BLDIN_BAK_CREATE_PLAN"; /*************设置备份计划的活动标志************/ PROCEDURE Enable_Plan(planname VARCHAR, b boolean) IS LANGUAGE "C" NAME "BLDIN_BAK_MODIFY_PLAN_STATE"; /************删除备份计划*******************/ PROCEDURE Drop_Plan(planname VARCHAR) IS LANGUAGE "C" NAME "BLDIN_BAK_DROP_PLAN"; /************增加全备份项目*************/ PROCEDURE Add_Backup_Item(planname VARCHAR, filename VARCHAR, time_offset INTERVAL DAY TO MINUTE) IS LANGUAGE "C" NAME "BLDIN_BAK_ADD_BAK_ITEM"; /************增加增量备份项目*************/ PROCEDURE Add_Backup_Inc_Item(planname VARCHAR, time_offset INTERVAL DAY TO MINUTE, iperiod INTERVAL DAY TO MINUTE DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_BAK_ADD_INC_ITEM"; /************增加导出项目*************/ PROCEDURE Add_Export_Item(planname VARCHAR, obj_name VARCHAR, obj_type VARCHAR, filename VARCHAR, time_offset INTERVAL DAY TO MINUTE) IS LANGUAGE "C" NAME "BLDIN_BAK_ADD_EXP_ITEM"; /************增加文件操作项目***************/ PROCEDURE Add_File_Item(planname VARCHAR, filename VARCHAR, bak_count INTEGER, optype VARCHAR, dstpath VARCHAR DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_BAK_ADD_FILE_ITEM"; /************删除项目****************/ PROCEDURE Drop_Item(planname VARCHAR, item_n INTEGER) IS LANGUAGE "C" NAME "BLDIN_BAK_DROP_ITEM_BY_NO"; /****************************按名删除table,database,file子项函数************************************/ PROCEDURE Alter_Plan_Del_Item(planname VARCHAR, objtype VARCHAR, objname VARCHAR) IS LANGUAGE "C" NAME "BLDIN_BAK_DROP_ITEM_BY_OBJNAME"; /****************************设置计划当前项目************************************/ PROCEDURE alter_modify_curr_item(planname VARCHAR, curr_item INTEGER) IS LANGUAGE "C" NAME "BLDIN_BAK_MODIFY_PLAN_CURR_ITEM"; /****************************设置下次运行时间************************************/ PROCEDURE alter_modify_run_time(planname VARCHAR, curr_item INTEGER, next_run_time DATETIME, istart_t DATETIME DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_BAK_MODIFY_ITEM_RUN_TIME"; /*************生成文件的时间后缀****************/ FUNCTION append_file_tail(srcpathname varchar) RETURN VARCHAR IS pos INTEGER; tail VARCHAR; str_year VARCHAR; str_month VARCHAR; str_day VARCHAR; str_hour VARCHAR; str_minute VARCHAR; str_second VARCHAR; str_ext VARCHAR; dstpathname VARCHAR; BEGIN if not FILE_EXISTS(srcpathname) then RETURN NULL; end if; tail := to_char(sysdate); str_year := substr(tail, 1, 4); str_month := substr(tail, 6, 2); str_day := substr(tail, 9, 2); str_hour := substr(tail, 12, 2); str_minute := substr(tail, 15, 2); str_second := substr(tail, 18, 2); tail := str_year || str_month || str_day || str_hour || str_minute || str_second; pos := POSITION('.' IN REVERSE_STR(srcpathname)); if pos > 0 then str_ext := tailing(srcpathname, pos - 1); dstpathname := heading(srcpathname, len(srcpathname) - pos); else dstpathname := srcpathname; end if; dstpathname := dstpathname || '_' || tail || '.' || str_ext; --send_msg(dstpathname); RETURN dstpathname; END; /**********************取得文件末尾的创建时间********************/ FUNCTION get_file_create_time(filename VARCHAR, compfilename VARCHAR) RETURN DATETIME IS dt DATETIME; dt_str VARCHAR; str_year VARCHAR; str_month VARCHAR; str_day VARCHAR; str_hour VARCHAR; str_minute VARCHAR; str_second VARCHAR; pos INTEGER; BEGIN --在文件名中，取得时间串 dt_str := substr(filename, len(compfilename) + 2, 14); if len(dt_str) != 14 then dt_str := '99991231235959'; end if; str_year := substr(dt_str, 1, 4); str_month := substr(dt_str, 5, 2); str_day := substr(dt_str, 7, 2); str_hour := substr(dt_str, 9, 2); str_minute := substr(dt_str, 11, 2); str_second := substr(dt_str, 13, 2); dt_str := str_year || '-' || str_month || '-' || str_day || ' ' || str_hour || ':' || str_minute || ':' || str_second; dt := To_date(dt_str, 'yyyy-mm-dd hh24:mi:ss'); RETURN dt; END; /************取得指定路径下，匹配指定部分开始字符的最早的一个文件*********/ FUNCTION get_first_file(path VARCHAR,filename VARCHAR,bak_count out integer) RETURN VARCHAR IS str_name varchar; str_ext varchar; str_filename varchar; str_fileext varchar; pos integer; create_dt datetime; ret_filename varchar; dt_str varchar; BEGIN bak_count := 0; ret_filename := NULL; --检测路径存在 if not dir_exists(path) then RAISE_APPLICATION_ERROR(-30012, '目标路径' || path || '不存在.'); end if; --分离指定文件名的名字和后缀 pos := POSITION('.' IN REVERSE_STR(filename)); if pos > 0 then str_ext := tailing(filename, pos - 1); str_name := heading(filename, len(filename) - pos); end if; create_dt := To_date('9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'); FOR r IN (select * from TABLE(DBMS_INFO.FILE_LIST(path))) LOOP if not r.is_dir then pos := position('/' IN REVERSE_STR(r.db_path)); if pos > 0 then str_filename := tailing(r.db_path, pos - 1); else str_filename := r.db_path; end if; pos := POSITION('.' IN REVERSE_STR(str_filename)); if pos > 0 then str_fileext := tailing(str_filename, pos - 1); str_filename := heading(str_filename, len(str_filename) - pos); end if; pos := position(str_name in str_filename); if pos = 1 and '_' = substr(str_filename, len(str_name) + 1, 1) and str_ext = str_fileext then bak_count := bak_count + 1; if create_dt > get_file_create_time(str_filename, str_name) then create_dt := get_file_create_time(str_filename, str_name); ret_filename := r.db_path; end if; end if; end if; END FOR; RETURN ret_filename; END; /*****************************处理重命名操作********************************/ PROCEDURE rename_op(path VARCHAR, fname VARCHAR) IS pos INTEGER; srcfilename VARCHAR; dstfilename VARCHAR; BEGIN --参数检测 IF path ISNULL THEN RAISE_APPLICATION_ERROR(-30020, '参数path不能为空'); END IF; IF fname ISNULL THEN RAISE_APPLICATION_ERROR(-30020, '参数fname不能为空'); END IF; --处理路径 pos := position('/' in reverse_str(path)); IF pos = 1 THEN srcfilename := path; ELSE srcfilename := CONCAT(path, '/'); END IF; srcfilename := CONCAT(srcfilename, fname); dstfilename := append_file_tail(srcfilename); IF dstfilename is not null then if not RENAME_FILE(srcfilename, dstfilename) then RAISE_APPLICATION_ERROR(-30015,'重命名文件' || srcfilename || '失败.'); end if; end if; END; PROCEDURE Del_xfn() as path_str varchar; xfn_str varchar; min_xfn integer; path_len integer; xfn integer; BEGIN select MIN_RESTORE_XFN into min_xfn from sys_ctl_vars; for r in (select db_path from TABLE(DBMS_INFO.FILE_LIST('/ARCH')) ORDER BY DB_PATH) loop path_str := r.db_path; --取得归档号 path_len := len(path_str); path_str := heading(path_str, path_len - 4); xfn_str := tailing(path_str, path_len - 4 - len('/HOME/ARCH/REDO')); xfn := atol(xfn_str); --如果归档号小于最小安全号，则删除归档文件 if xfn < min_xfn then DROP_FILE(r.db_path); end if; end for; END; PROCEDURE FILE_OPTION (PATH VARCHAR,OBJ_NAME VARCHAR,SAVE_BAK INT,OP_TYPE VARCHAR,PLAN_NAME VARCHAR,ITEM_NO INT,PATH_BAK VARCHAR) AS srcfilename VARCHAR; bak_count int; pos INTEGER; tmp_item_no INTEGER; s_t DATETIME; bakpathname VARCHAR; dstfilename VARCHAR; BEGIN --处理删除文件操作 IF UPPER(OP_TYPE) = 'DELETE' THEN --先重命名 rename_op(PATH, OBJ_NAME); --删除操作 srcfilename := get_first_file(PATH, OBJ_NAME, bak_count); if srcfilename notnull and bak_count > SAVE_BAK then if not DROP_FILE(srcfilename) then RAISE_APPLICATION_ERROR(-30016,'删除文件' || srcfilename || '失败.'); end if; end if; --处理移动文件操作 ELSIF UPPER(OP_TYPE) = 'MOVE' THEN --先重命名 rename_op(PATH, OBJ_NAME); --移动操作 srcfilename := get_first_file(PATH, OBJ_NAME, bak_count); if srcfilename notnull and bak_count > SAVE_BAK then pos := POSITION('/' IN REVERSE_STR(srcfilename)); if pos > 0 then dstfilename := tailing(srcfilename, pos - 1); end if; if '/' = TAILING(PATH_BAK, 1) then dstfilename := CONCAT(PATH_BAK, dstfilename); else dstfilename := CONCAT('/', dstfilename); dstfilename := CONCAT(PATH_BAK, dstfilename); end if; if not RENAME_FILE(srcfilename, dstfilename) then RAISE_APPLICATION_ERROR(-30017,'移动文件' || srcfilename || '失败.'); end if; end if; END IF; END;--end file_op /************备份计划的执行体(由JOB管理器调用)****************/ PROCEDURE Run() IS mark_str VARCHAR; sql_str VARCHAR; tv INTERVAL DAY TO MINUTE; nt DATETIME; tmp_item_no INTEGER; s_t DATETIME; bakpathname VARCHAR; option_type varchar; BEGIN --提取活动计划中到执行时间的子项 FOR r IN (SELECT * FROM DBA_BACKUP_ITEMS R1 ,DBA_BACKUP_PLANS R2 WHERE r2.ENABLE=true AND NEXT_RUN_T < SYSDATE AND R1.PLAN_NAME=R2.PLAN_NAME ORDER BY R1.PLAN_NAME, ITEM_NO) LOOP IF r.op_type='BACKUP' THEN option_type:='BACKUP SYSTEM'; ELSIF r.op_type='BACKUP_INC' THEN option_type:='BACKUP SYSTEM INCREMENT'; ELSIF r.op_type='EXPORT' AND r.OBJ_TYPE = 'DATABASE' THEN option_type:='EXPORT DATABASE'; ELSIF r.op_type='EXPORT' AND r.OBJ_TYPE = 'TABLE' THEN option_type:='EXPORT TABLE'; END IF; ----每次备份前进行文件重名处理，并根据文件处理项进行处理,增量除外 IF R.OP_TYPE = 'BACKUP' or R.OP_TYPE='EXPORT' THEN FILE_OPTION(r.PATH,r.FILE_NAME,r.FILE_KEEP_CNT,r.file_op,r.plan_name,r.item_no,r.FILE_MOVE_PATH); nt := r.NEXT_RUN_T; while nt <= SYSDATE LOOP nt := nt + r.PERIOD; END LOOP; --修改下次执行时间(无论成功失败,均修改执行时间) dbms_backup.alter_modify_curr_item(r.PLAN_NAME, r.ITEM_NO); dbms_backup.alter_modify_run_time(r.PLAN_NAME, r.ITEM_NO, nt); ELSE nt := r.NEXT_RUN_T; s_t := r.ISTART_T; WHILE nt <= SYSDATE LOOP nt := nt + r.IPERIOD; IF nt > s_t + r.PERIOD THEN s_t := s_t + r.PERIOD; nt := s_t + r.TIME_OFF; END IF; END LOOP; dbms_backup.alter_modify_curr_item(r.PLAN_NAME, r.ITEM_NO); dbms_backup.alter_modify_run_time(r.PLAN_NAME, r.ITEM_NO, nt, s_t); END IF; bakpathname := r.PATH; IF '/' != TAILING(bakpathname, 1) THEN bakpathname := CONCAT(bakpathname, '/'); END IF; bakpathname := CONCAT(bakpathname, r.FILE_NAME); IF r.OP_TYPE = 'BACKUP' THEN sql_str := 'BACKUP SYSTEM TO ''' || bakpathname || ''' online;'; ELSIF r.OP_TYPE = 'BACKUP_INC' THEN sql_str := 'BACKUP SYSTEM INCREMENT APPEND TO ''' || bakpathname || ''' online;'; ELSIF r.OP_TYPE = 'EXPORT' AND r.OBJ_TYPE = 'DATABASE' THEN sql_str := 'BACKUP DATABASE TO ''' || bakpathname || ''';'; ELSIF r.OP_TYPE = 'EXPORT' AND r.OBJ_TYPE = 'TABLE' THEN sql_str := 'BACKUP TABLE ' || r.OBJ_NAME || ' APPEND TO ''' || bakpathname || ''';'; ELSE CONTINUE; END IF; BEGIN EXECUTE IMMEDIATE sql_str; END;--end execute END FOR; END; --end Run END DBMS_BACKUP; / CREATE OR REPLACE PACKAGE SYSDBA.DBMS_METADATA IS FUNCTION GET_DDL(obj_name IN VARCHAR) RETURN CLOB; FUNCTION GET_DDL(obj_type IN VARCHAR,obj_name IN VARCHAR,schema_name IN VARCHAR DEFAULT NULL) RETURN CLOB; FUNCTION GET_DDL_INDEX(tab_name IN VARCHAR, idx_name IN VARCHAR DEFAULT 'ALL') RETURN CLOB; END SYSDBA.DBMS_METADATA; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_METADATA IS FUNCTION GET_DDL(obj_name IN VARCHAR) RETURN CLOB IS LANGUAGE "C" NAME "BLDIN_GET_DDL"; FUNCTION GET_DDL(obj_type IN VARCHAR,obj_name IN VARCHAR,schema_name IN VARCHAR DEFAULT NULL) RETURN CLOB IS LANGUAGE "C" NAME "BLDIN_GET_DDL_P3"; FUNCTION GET_DDL_INDEX(tab_name IN VARCHAR, idx_name IN VARCHAR DEFAULT 'ALL') RETURN CLOB IS LANGUAGE "C" NAME "BLDIN_GET_DDL_INDEX"; END SYSDBA.DBMS_METADATA; / ----------数据库通用工具包(定义)------------------ CREATE OR REPLACE PACKAGE SYSDBA.DBMS_UTILITY IS FUNCTION GET_TIME() RETURN BIGINT; FUNCTION FORMAT_ERROR_STACK() RETURN VARCHAR(5000); FUNCTION FORMAT_ERROR_BACKTRACE() RETURN VARCHAR(5000); PROCEDURE OPEN_TABLE(name IN VARCHAR, --表名 （'*' 当前库下所有表，'schema_name.*' 指定模式下所有表，'tab_name' 当前库 模式下的表） node_id IN INTEGER) --节点id （'0' 当前节点， '-1' 所有节点， '大于0' 指定节点） AUTHID USER; END SYSDBA.DBMS_UTILITY; / ----------数据库通用工具包(实现)------------------ CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_UTILITY IS FUNCTION GET_TIME() RETURN BIGINT IS LANGUAGE "C" NAME "BLDIN_GET_TIME"; FUNCTION FORMAT_ERROR_STACK() RETURN VARCHAR(5000) IS BEGIN return SQLERRM; END; FUNCTION FORMAT_ERROR_BACKTRACE() RETURN VARCHAR(5000) IS LANGUAGE "C" NAME "BLDIN_FORMAT_ERROR_BACKTRACE"; PROCEDURE OPEN_TABLE(name IN VARCHAR, node_id IN INTEGER) IS LANGUAGE "C" NAME "BLDIN_OPEN_TABLE"; END SYSDBA.DBMS_UTILITY; / CREATE OR REPLACE PACKAGE DBMS_RANDOM AS -- Seed with a binary integer PROCEDURE SEED(val IN INTEGER); -- Seed with a string (up to length 2000) PROCEDURE SEED(val IN VARCHAR2); -- Get a random 18-digit precision number, 0.0 <= value < 1.0 FUNCTION VALUE RETURN NUMBER(38,18); -- get a random Oracle number x, low <= x < high FUNCTION VALUE (low IN NUMBER, high IN NUMBER) RETURN NUMBER(38,18); -- get a random number from a normal distribution FUNCTION normal RETURN NUMBER ; -- get a random string FUNCTION STRING (opt char, len NUMBER) /* "opt" specifies that the returned string may contain: 'u','U' : upper case alpha characters only 'l','L' : lower case alpha characters only 'a','A' : alpha characters only (mixed case) 'x','X' : any alpha-numeric characters (upper) 'p','P' : any printable characters */ RETURN VARCHAR2 ; -- string of characters -- Obsolete, just calls seed(val) PROCEDURE INITIALIZE(val IN INTEGER); -- Obsolete, get integer in ( -power(2,31) <= random < power(2,31) ) FUNCTION RANDOM RETURN INTEGER; TYPE NUM_ARRAY IS TABLE OF NUMBER(38,38) INDEX BY INTEGER; END DBMS_RANDOM; / CREATE OR REPLACE PACKAGE BODY DBMS_RANDOM AS mem num_array; -- big internal state hidden from the user counter INTEGER := 55; -- counter through the results saved_norm NUMBER := NULL; -- unused random normally distributed value need_init BOOLEAN := TRUE; -- do we still need to initialize -- Seed the random number generator with a INTEGER PROCEDURE SEED(val IN INTEGER) IS BEGIN SEED(TO_CHAR(val)); END SEED; -- Seed the random number generator with a string. PROCEDURE SEED(val IN VARCHAR2) IS junk VARCHAR2(2000); piece VARCHAR2(20); randval NUMBER(38,37); mytemp NUMBER(38,0); vatemp NUMBER(38,14); j INTEGER; BEGIN need_init := FALSE; saved_norm := NULL; counter := 0; junk := val; FOR i IN 0..54 LOOP piece := SUBSTR(junk,1,19); randval := 0; j := 1; -- convert 19 characters to a 38-digit number FOR j IN 1..19 LOOP randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0); END LOOP; -- try to avoid lots of zeros randval := randval*1e-38+i*.01020304050607080910111213141516171819; mem(i) := randval - TRUNC(randval); -- we've handled these first 19 characters already; move on junk := SUBSTR(junk,20); END LOOP; randval := mem(54); FOR j IN 0..10 LOOP FOR i IN 0..54 LOOP -- barrelshift mem(i-1) by 24 digits vatemp := randval * 1e24; mytemp := TRUNC(randval); randval := (randval - mytemp) + (mytemp * 1e-38); -- add it to mem(i) randval := mem(i)+randval; IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; -- record the result mem(i) := randval; END LOOP; END LOOP; END seed; -- give values to the user -- Delayed Fibonacci, pilfered from Knuth volume 2 FUNCTION VALUE RETURN NUMBER(38,18) IS randval NUMBER(38,18); BEGIN counter := counter + 1; IF counter >= 55 THEN -- initialize if needed IF (need_init = TRUE) THEN SEED(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') || USER || USERENV('SESSIONID')); ELSE -- need to generate 55 more results FOR i IN 0..30 LOOP randval := mem(i+24) + mem(i); IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; mem(i) := randval; END LOOP; FOR i IN 31..54 LOOP randval := mem(i-31) + mem(i); IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; mem(i) := randval; END LOOP; END IF; counter := 0; END IF; RETURN mem(counter)*mem(counter)*mem(counter); END VALUE; -- Random 38-digit number between LOW and HIGH. FUNCTION VALUE ( low in NUMBER, high in NUMBER) RETURN NUMBER(38,18) is val numeric(38,18); BEGIN val := VALUE(); val := val*(high-low); RETURN val+low; END VALUE; -- Random numbers in a normal distribution. -- Pilfered from Knuth volume 2. FUNCTION NORMAL RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1 v1 NUMBER(38,18); v2 NUMBER(38,18); r2 NUMBER(38,18); fac NUMBER(38,18); BEGIN IF saved_norm is not NULL THEN -- saved from last time v1 := saved_norm; -- to be returned this time saved_norm := NULL; ELSE r2 := 2; -- Find two independent uniform variables WHILE r2 > 1 OR r2 = 0 LOOP v1 := value(); v1 := v1 + v1 - 1; v2 := value(); v2 := v2 + v2 - 1; r2 := v1*v1 + v2*v2; -- r2 is radius END LOOP; -- 0 < r2 <= 1: in unit circle /* Now derive two independent normally-distributed variables */ fac := sqrt(-2*ln(r2)/r2); v1 := v1*fac; -- to be returned this time saved_norm := v2*fac; -- to be saved for next time END IF; RETURN v1; END NORMAL; -- Random string. Pilfered from Chris Ellis. FUNCTION STRING (opt char, len NUMBER) RETURN VARCHAR2 is -- string of characters optx char (1) := lower(opt); rng NUMBER; tmp NUMBER(38,18); n INTEGER; ccs VARCHAR2 (128); -- candidate character subset xstr VARCHAR2 (4000) := NULL; BEGIN IF optx = 'u' THEN -- upper case alpha characters only ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; rng := 26; ELSIF optx = 'l' THEN -- lower case alpha characters only ccs := 'abcdefghijklmnopqrstuvwxyz'; rng := 26; ELSIF optx = 'a' THEN -- alpha characters only (mixed case) ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || 'abcdefghijklmnopqrstuvwxyz'; rng := 52; ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper) ccs := '0123456789' || 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; rng := 36; ELSIF optx = 'p' THEN -- any printable char (ASCII subset) ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' || 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' || 'abcdefghijklmnopqrstuvwxyz' || '{|}~' ; rng := 95; ELSE ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; rng := 26; -- default to upper case END IF; FOR i IN 1 .. least(len,4000) LOOP /* Get random integer within specified range */ tmp := rng * value; n := TRUNC(tmp) + 1; /* Append character to string */ xstr := xstr || SUBSTR(ccs,n,1); END LOOP; RETURN xstr; END STRING; -- For compatibility with 8.1 PROCEDURE INITIALIZE(val IN INTEGER) IS BEGIN SEED(to_char(val)); END INITIALIZE; -- For compatibility with 8.1 -- Random INTEGER, -power(2,31) <= Random < power(2,31) -- Delayed Fibonacci, pilfered from Knuth volume 2 FUNCTION RANDOM RETURN INTEGER IS BEGIN RETURN TRUNC(Value*4294967296)-2147483648; END RANDOM; END DBMS_RANDOM; / CREATE OR REPLACE PACKAGE SYSDBA.DBMS_JOB IS PROCEDURE SUBMIT(job OUT INTEGER,what IN VARCHAR,next_date IN DATETIME DEFAULT sysdate,pop_interval IN VARCHAR DEFAULT NULL,no_parse IN BOOLEAN DEFAULT FALSE,instance IN INTEGER DEFAULT 0,force IN BOOLEAN DEFAULT FALSE); PROCEDURE RUN(job INTEGER,force IN BOOLEAN DEFAULT FALSE); PROCEDURE REMOVE(job INTEGER); END SYSDBA.DBMS_JOB; / CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_JOB IS PROCEDURE SUBMIT(job OUT INTEGER,what IN VARCHAR,next_date IN DATETIME DEFAULT sysdate,pop_interval IN VARCHAR DEFAULT NULL,no_parse IN BOOLEAN DEFAULT FALSE,instance IN INTEGER DEFAULT 0,force IN BOOLEAN DEFAULT FALSE) IS name varchar; BEGIN name := dbms_random.string('u',20); DBMS_SCHEDULER.CREATE_JOB(name,'stored_procedure',what,0,next_date,pop_interval,'2099-12-31 00:00:00','JOB_CLASS',force,FALSE,NULL); SELECT JOB_ID INTO job FROM ALL_JOBS WHERE JOB_NAME=name; END; PROCEDURE RUN(job INTEGER,force IN BOOLEAN DEFAULT FALSE) IS name varchar; BEGIN SELECT JOB_NAME INTO name FROM ALL_JOBS WHERE JOB_ID=job; DBMS_SCHEDULER.RUN_JOB(name,force); END; PROCEDURE REMOVE(job INTEGER) IS name varchar; BEGIN SELECT JOB_NAME INTO name FROM ALL_JOBS WHERE JOB_ID=job; DBMS_SCHEDULER.DROP_JOB(name,TRUE); END; END SYSDBA.DBMS_JOB; / ----------SYNONYM DEFINE------------- CREATE PUBLIC SYNONYM RECYCLEBIN FOR USER_RECYCLEBIN; / /** 使用时开启 CREATE OR REPLACE PACKAGE SYSDBA.DBMS_SESSION IS PROCEDURE SET_CONTEXT(context_name VARCHAR,attribute VARCHAR,value VARCHAR,username VARCHAR DEFAULT NULL,client_id VARCHAR DEFAULT NULL); END SYSDBA.DBMS_SESSION; ----包体 CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_SESSION IS PROCEDURE SET_CONTEXT(context_name VARCHAR,attribute VARCHAR,value VARCHAR,username VARCHAR DEFAULT NULL,client_id VARCHAR DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_SET_CONTEXT"; END SYSDBA.DBMS_SESSION; CREATE OR REPLACE PACKAGE SYSDBA.DBMS_RLS IS CONTEXT_SENSITIVE integer :=1; PROCEDURE ADD_POLICY( object_schema IN VARCHAR, object_name IN VARCHAR, policy_name IN VARCHAR, function_schema IN VARCHAR, policy_function IN VARCHAR, statement_types IN VARCHAR, update_check IN BOOLEAN DEFAULT FALSE, enable IN BOOLEAN DEFAULT TRUE, static_policy IN BOOLEAN DEFAULT FALSE, policy_type IN BINARY_INTEGER DEFAULT NULL, long_predicate IN BOOLEAN DEFAULT FALSE, sec_relevant_cols IN VARCHAR2 DEFAULT NULL, sec_relevant_cols_opt IN BINARY_INTEGER DEFAULT NULL); PROCEDURE DROP_POLICY( object_schema IN VARCHAR, object_name IN VARCHAR, policy_name IN VARCHAR); END SYSDBA.DBMS_RLS; ----包体 CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_RLS IS PROCEDURE ADD_POLICY( object_schema IN VARCHAR, object_name IN VARCHAR, policy_name IN VARCHAR, function_schema IN VARCHAR, policy_function IN VARCHAR, statement_types IN VARCHAR, update_check IN BOOLEAN DEFAULT FALSE, enable IN BOOLEAN DEFAULT TRUE, static_policy IN BOOLEAN DEFAULT FALSE, policy_type IN BINARY_INTEGER DEFAULT NULL, long_predicate IN BOOLEAN DEFAULT FALSE, sec_relevant_cols IN VARCHAR2 DEFAULT NULL, sec_relevant_cols_opt IN BINARY_INTEGER DEFAULT NULL) IS LANGUAGE "C" NAME "BLDIN_ADD_POLICY"; PROCEDURE DROP_POLICY( object_schema IN VARCHAR, object_name IN VARCHAR, policy_name IN VARCHAR) IS LANGUAGE "C" NAME "BLDIN_DROP_POLICY"; END SYSDBA.DBMS_RLS; **/