--没有太多好的,固定定义,但是有些的函数参数是比较灵活的,可以综合利用 CREATEORREPLACETYPE power_idxtype_im ASOBJECT ( curnum NUMBER, howmany NUMBER, cacheflag INTEGER, scan_curnum NUMBER, scan_fetchnumNUMBER, STATICFUNCTION ODCIGetInterfaces(ifclistOUT sys.ODCIObjectList) RETURNNUMBER, STATICFUNCTION ODCIIndexCreate(ia sys.ODCIIndexInfo, parms VARCHAR2, env sys.ODCIEnv) RETURNNUMBER, STATICFUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv) RETURNNUMBER, STATICFUNCTION ODCIIndexStart(sctx INOUT power_idxtype_im, ia sys.ODCIIndexInfo, op sys.ODCIPredInfo, qi sys.ODCIQueryInfo, strt VARCHAR2, stop VARCHAR2, env sys.ODCIEnv) RETURNNUMBER, MEMBERFUNCTION ODCIIndexFetch( selfINOUT POWER_IDXTYPE_IM, nrows NUMBER, rids OUT sys.ODCIRidList, env sys.ODCIEnv) RETURNNUMBER, MEMBERFUNCTION ODCIIndexClose(env sys.ODCIEnv)RETURNNUMBER, STATICFUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBER, STATICFUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBER, STATICFUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBER, STATICFUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, expversion VARCHAR2, newblock OUTPLS_INTEGER, env sys.ODCIEnv) RETURNVARCHAR2 ); / CREATEORREPLACETYPEBODY power_idxtype_im IS STATICFUNCTION ODCIGetInterfaces(ifclistOUT sys.ODCIObjectList) RETURNNUMBERIS BEGIN ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2')); return ODCIConst.Success; END ODCIGetInterfaces; STATICFUNCTION ODCIIndexCreate(ia sys.ODCIIndexInfo, parms VARCHAR2, env sys.ODCIEnv) RETURNNUMBERIS l_schemanameVARCHAR2(32):=''; l_tablenameVARCHAR2(32):=''; l_fieldnameVARCHAR2(32):=''; l_indexnameVARCHAR2(32):=''; l_categoryNUMBER:=0; l_isuniqueNUMBER:=0; l_tablepartitionVARCHAR2(32):=''; l_indexpartitionVARCHAR2(32):=''; BEGIN sys.ODCIIndexInfoDump(ia); IF UPPER(parms)='UNIQUE'THEN l_isunique:=1; ENDIF; l_schemaname:=ia.IndexCols(1).TableSchema; l_tablename:=ia.IndexCols(1).TableName; l_fieldname:=replace(ia.IndexCols(1).ColName,'"',''); l_indexname:=ia.IndexName; POWER_PKG.ODC_Print('SCHEMA:'||l_schemaname); POWER_PKG.ODC_Print('TABLE:'||l_tablename); POWER_PKG.ODC_Print('FIELD:'||l_fieldname); POWER_PKG.ODC_Print('INDEX NAME:'||l_indexname); IF UPPER(parms)='NODATA'THEN --不生成索引数据,直接返回成功 POWER_PKG.ODC_Print('ODCIIndexCreate<<<<<'); RETURN ODCICONST.SUCCESS; ELSE --底层函数索引的名为随机字符串 POWER_PKG.Proc_AddNativeIndex( in_SchemaName => l_schemaname, in_TableName => l_tablename, in_FieldName => l_fieldname, in_Unique => l_isunique, in_Indexname => ''); ENDIF; RETURN ODCICONST.SUCCESS; END ODCIIndexCreate; STATICFUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv) RETURNNUMBERIS l_exec_sql VARCHAR2(2000); l_schemanameVARCHAR2(32):=''; l_tablenameVARCHAR2(32):=''; l_fieldnameVARCHAR2(32):=''; l_indexnameVARCHAR2(32):=''; --cnum INTEGER; --junk INTEGER; BEGIN -- Construct the SQL statement. POWER_PKG.ODC_Print('sys.ODCIIndexInfoDump(ia);'); sys.ODCIIndexInfoDump(ia); --sys.Odcienvdump(env); l_schemaname:=ia.IndexCols(1).TableSchema; l_tablename:=ia.IndexCols(1).TableName; l_fieldname:=replace(ia.IndexCols(1).ColName,'"',''); l_indexname:=ia.IndexName; POWER_PKG.ODC_Print('SCHEMA:'||l_schemaname); POWER_PKG.ODC_Print('TABLE:'||l_tablename); POWER_PKG.ODC_Print('FIELD:'||l_fieldname); POWER_PKG.ODC_Print('INDEX NAME:'||l_indexname); --先干掉这个domainIndex l_exec_sql:='drop index '||l_schemaname||'.'||l_indexname; executeimmediate l_exec_sql; --然后跟着干掉这个函数索引 POWER_PKG.Proc_DropNativeIndex(in_SchemaName => l_schemaname, in_TableName => l_tablename); RETURN ODCICONST.SUCCESS; END ODCIIndexDrop; STATICFUNCTION ODCIIndexStart(sctx INOUT power_idxtype_im, ia sys.ODCIIndexInfo, op sys.ODCIPredInfo, qi sys.ODCIQueryInfo, strt VARCHAR2, stop VARCHAR2, env sys.ODCIEnv) RETURNNUMBERIS cnum INTEGER; rid ROWID; nrows INTEGER; relop VARCHAR2(2); stmt VARCHAR2(1000); conditionstrvarchar2(100):=''; l_schemanameVARCHAR2(32):=''; l_tablenameVARCHAR2(32):=''; l_fieldnameVARCHAR2(32):=''; BEGIN --完成一些初如化工作 POWER_PKG.ODC_Print('ODCIIndexStart>>>>>'); sys.ODCIIndexInfoDump(ia); POWER_PKG.ODC_Print('start key : ' || strt); POWER_PKG.ODC_Print('stop key : ' || stop); POWER_PKG.ODC_Print('dump ENV,dump op,dump qi'); --sys.Odcienvdump(env); sys.ODCIPredInfoDump(op); --sys.Odciqueryinfodump(qi); --获取相关的模式名、表名、字段名 l_schemaname:=ia.IndexCols(1).TableSchema; l_tablename:=ia.IndexCols(1).TableName; l_fieldname:=replace(ia.IndexCols(1).ColName,'"',''); --接下来才是真正的开工 --如查是精确查询 IF (bitand(op.Flags, ODCIConst.PredExactMatch) = ODCIConst.PredExactMatch) THEN conditionstr := POWER_PKG..encode(strt); stmt := 'select /*+First_rows */rowid from '||l_schemaname||'.'||l_tablename||' where ' ||'POWER_PKG.FUNC_INDEX('||l_fieldname||')='''||conditionstr||''''; POWER_PKG.ODC_Print(stmt); cnum := dbms_sql.open_cursor; dbms_sql.parse(cnum, stmt, dbms_sql.native); -- Set context as the cursor number. sctx := POWER_IDXTYPE_IM(cnum,0,0,0,0); ELSE --如果是模糊查询,而且当前只支持到这个前缀的查询 IF (bitand(op.Flags, ODCIConst.PredPrefixMatch) = ODCIConst.PredPrefixMatch) THEN conditionstr:=substr(strt,1,length(strt)-1); conditionstr := COFFER.odc_pack_fi.encode(conditionstr); stmt :='select /*+First_rows */rowid from '||l_schemaname||'.'||l_tablename||' where ' ||'POWER_PKG.FUNC_INDEX('||l_fieldname||') like '''||conditionstr||'%'''; POWER_PKG.ODC_Print(stmt); cnum := dbms_sql.open_cursor; dbms_sql.parse(cnum, stmt, dbms_sql.native); -- Set context as the cursor number. sctx := POWER_IDXTYPE_IM(cnum,0,0,0,0); ELSE POWER_PKG.ODC_Print('暂时只支持等值与模糊查询,其他条件查询,自己去查相关资料'); ENDIF; ENDIF; POWER_PKG.ODC_Print('native searchSQL='||stmt); RETURN ODCICONST.SUCCESS; END ODCIIndexStart; MEMBERFUNCTION ODCIIndexFetch(selfINOUT POWER_IDXTYPE_IM, nrows NUMBER, rids OUT sys.ODCIRidList, env sys.ODCIEnv) RETURNNUMBERIS cnum INTEGER; idx INTEGER := 1; rlist sys.ODCIRidList := sys.ODCIRidList(); done boolean := FALSE; l_rid ROWID:=''; rid_tab DBMS_SQL.Varchar2_Table; r_num INTEGER:=0; counter INTEGER:=1; l_totalcostINTEGER:=0; l_getnum_constINTEGER:=200; BEGIN POWER_PKG.ODC_Print('ODCIIndexFetch>>>>>'); POWER_PKG.ODC_Print('Nrows : ' || round(nrows)); cnum := self.curnum; --官方方式 /* WHILE not done LOOP if idx > nrows then done := TRUE; else rlist.extEND; if dbms_sql.fetch_rows(cnum) > 0 then dbms_sql.column_value_rowid(cnum, 1, rlist(idx)); idx := idx + 1; else rlist(idx) := null; done := TRUE; END if; END if; END LOOP; */ --coffer方式 IFself.howmany =0THEN dbms_sql.define_array(cnum,1, rid_tab, l_getnum_const,1); --dbms_sql.define_array(cnum, 1, rid_tab, nrows, 1); r_num := DBMS_SQL.EXECUTE(cnum); --DBMS_OUTPUT.put_line('r_num='||r_num); ENDIF; r_num := DBMS_SQL.FETCH_ROWS(cnum); --Odc_Pack_Util.ODC_Print('num:'||r_num,1); IF r_num = l_getnum_constTHEN rlist.extend(r_num); ELSE rlist.extend(r_num+1); --rlist(r_num+1):=NULL; ENDIF; DBMS_SQL.COLUMN_VALUE(cnum,1, rid_tab); --COFFER.ODC_PACK_UTIL.ODC_Print('rid_tab:'||rid_tab.count||' '||rid_tab.first); for iin1..r_numloop rlist(i) := rid_tab(i+SELF.howmany); --rlist(i) := rid_tab(i); --COFFER.ODC_PACK_UTIL.ODC_Print('rlist(i):'||rlist(i)); endloop; --IF r_num != l_getnum_const THEN --rlist(r_num+1):=NULL; --ELSE SELF.howmany :=SELF.howmany + r_num; --end coffer type rids := rlist; RETURN ODCICONST.SUCCESS; END ODCIIndexFetch; MEMBERFUNCTION ODCIIndexClose(env sys.ODCIEnv)RETURNNUMBERIS cnum INTEGER; BEGIN POWER_PKG.ODC_Print('ODCIIndexClose>>>>>'); cnum := self.curnum; dbms_sql.close_cursor(cnum); RETURN ODCICONST.SUCCESS; END ODCIIndexClose; STATICFUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBERAS cid INTEGER; i BINARY_INTEGER; nrows INTEGER; stmt VARCHAR2(1000); BEGIN POWER_PKG.ODC_Print(' '); sys.ODCIIndexInfoDump(ia); -- Construct the statement,just do nothing RETURN ODCICONST.SUCCESS; END ODCIIndexInsert; STATICFUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBERAS cid INTEGER; stmt VARCHAR2(1000); nrows INTEGER; BEGIN POWER_PKG.ODC_Print(' '); sys.ODCIIndexInfoDump(ia); -- Construct the statement,just do nothing RETURN ODCICONST.SUCCESS; END ODCIIndexDelete; STATICFUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2, env sys.ODCIEnv) RETURNNUMBERAS cid INTEGER; cid2 INTEGER; stmt VARCHAR2(1000); stmt2 VARCHAR2(1000); nrows INTEGER; i NUMBER; BEGIN POWER_PKG.ODC_Print(' '); sys.ODCIIndexInfoDump(ia); -- Delete old entries,just do nothing RETURN ODCICONST.SUCCESS; END ODCIIndexUpdate; STATICFUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, expversion VARCHAR2, newblock OUTPLS_INTEGER, env sys.ODCIEnv) RETURNVARCHAR2IS BEGIN -- Let getversion do all the work since it has to maintain state across calls. RETURN power_pkg.getversion(ia.IndexSchema, ia.IndexName, newblock); EXCEPTION WHENOTHERSTHEN RAISE; END ODCIIndexGetMetaData; END; / |