| connstr函数：
CREATE OR REPLACE TYPE CONNSTRIMPL AS OBJECT
(
CURRENTSTR VARCHAR2(4000),
CURRENTSEPRATOR VARCHAR2(8),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT CONNSTRIMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT CONNSTRIMPL,VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN CONNSTRIMPL,RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT CONNSTRIMPL,CTX2 IN CONNSTRIMPL) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY CONNSTRIMPL IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT CONNSTRIMPL)
RETURN NUMBER IS
BEGIN
SCTX := CONNSTRIMPL('',',');------------------字符串分割符，修改替换成你需要的
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT CONNSTRIMPL, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF SELF.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := VALUE;
ELSE
SELF.CURRENTSTR := SELF.CURRENTSTR ||CURRENTSEPRATOR || VALUE;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN CONNSTRIMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.CURRENTSTR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT CONNSTRIMPL, CTX2 IN CONNSTRIMPL) RETURN NUMBER IS
BEGIN
IF CTX2.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := SELF.CURRENTSTR;
ELSIF SELF.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := CTX2.CURRENTSTR;
ELSE
SELF.CURRENTSTR := SELF.CURRENTSTR || CURRENTSEPRATOR || CTX2.CURRENTSTR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
CREATE OR REPLACE FUNCTION CONNSTR (INPUT VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING CONNSTRIMPL;
/ SELECT distinct a.cartonlotid,'' as componentid,'' as currentcompid,
connstr (DISTINCT CAST (a.device AS VARCHAR2 (100))) device,
connstr (DISTINCT CAST (a.fplversion AS VARCHAR2 (100))) fplversion,
connstr (DISTINCT CAST (a.grade AS VARCHAR2 (100))) grade,
connstr (DISTINCT CAST (a.outgrade AS VARCHAR2 (100))) outgrade,
connstr (DISTINCT CAST (a.quantity AS VARCHAR2 (100))) quantity,
connstr (DISTINCT CAST (a.yearmonth AS VARCHAR2 (100))) yearmonth,
connstr (DISTINCT CAST (a.userid AS VARCHAR2 (100))) userid,
connstr (DISTINCT CAST (a.updatetime AS VARCHAR2 (100))) updatetime,
connstr (DISTINCT CAST (a.wo AS VARCHAR2 (100))) wo,
connstr (DISTINCT CAST (a.lotid AS VARCHAR2 (100))) lotid,
connstr (DISTINCT CAST (a.customer AS VARCHAR2 (100))) customer,
connstr (DISTINCT CAST (w.po AS VARCHAR2 (100))) po,
connstr
(DISTINCT CAST ( DECODE (SUBSTR (a.yearmonth, 1, 1),
'M', 'C',
'N', 'D',
'O', 'E',
'P', 'F',
'Q', 'G',
'R', 'H',
'S', 'I',
'T', 'J',
'U', 'K'
)
|| SUBSTR (a.yearmonth, 2, 1)
|| a.grade
|| a.fplversion
|| DECODE (INSTR (a.device, '-'),
0, '00',
SUBSTR (a.device,
-2,
INSTR (a.device, '-') - 1
)
) AS VARCHAR2 (100)
)
) batchid,
'{1}' AS stackno
FROM dm_epd_carton_info_all a, mes_wpc_wo w
WHERE a.cartonlotid = {0}
AND w.wo(+) = a.firstwo
GROUP BY a.cartonlotid,
a.userid,
a.outgrade,
a.quantity,
a.yearmonth,
a.grade,
a.updatetime,
a.fplversion,
a.customer |
