JS代码
//拼接Sql语句 dsSub数据集,fileName 统计字段,dataType 字段类型 : 字符N,数字N
function uf_GetSqlCode(dataSet,fileName,dataType){
var strSql = "";
if(dataSet.recordCount == 0){
return "";
}else{
try{
for(var i = 0; i < dataSet.recordCount; i++){
strSql += dataSet.getValueAt(i,fileName) + ",";
}
return strSql;
}catch(e){
alert("生成Sql失败:" + e.message);
return "";
}
}
}
Oracle 字符分割自定义函数
CREATE OR REPLACE TYPE GYSK_STRSPLIT_TYPE IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION GYSK_STRSPLIT(
P_VALUE VARCHAR2, --字符串
P_SPLIT VARCHAR2 := ',' --分割字符
)
RETURN GYSK_STRSPLIT_TYPE
PIPELINED IS
V_IDX INTEGER;
V_STR VARCHAR2(1000);
V_STRS_LAST VARCHAR2(4000) := P_VALUE;
BEGIN
LOOP
V_IDX := INSTR(V_STRS_LAST,P_SPLIT);
EXIT WHEN V_IDX = 0;
V_STR := SUBSTR(V_STRS_LAST,1,V_IDX - 1);
V_STRS_LAST := SUBSTR(V_STRS_LAST,V_IDX + 1);
PIPE ROW(V_STR);
END LOOP;
PIPE ROW(V_STRS_LAST);
RETURN;
END GYSK_STRSPLIT;
应用
SELECT A.*
FROM GOODSDOC A
WHERE EXISTS(
SELECT 1
FROM (
SELECT COLUMN_VALUE AS GOODSID
FROM TABLE(GYSK_STRSPLIT('SPH00009316,SPH00009354'))
) M1
WHERE A.GOODSID = M1.GOODSID
)
;