PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT * FROM TCF_TEST;
V V_VALUE
- ----------
O 7
X 6
Y 6
1
CREATE OR REPLACE TYPE SERCHARTYPE IS TABLE OF CHAR;
DECLARE
V_CNT SERCHARTYPE := SERCHARTYPE();
V_TEXT CHAR(20) := 'XXXYYYXXXYYYY';
V_DATA CHAR;
V_VALUE NUMBER;
BEGIN
FOR I IN 1..LENGTH(V_TEXT) LOOP
V_CNT.EXTEND;
V_CNT(I) := SUBSTR(V_TEXT,I,1);
END LOOP;
INSERT INTO TCF_TEST
SELECT COLUMN_VALUE, COUNT(*)
FROM TABLE(V_CNT)
GROUP BY COLUMN_VALUE;
END;
/
===================================================================================
CREATE TABLE TCF_TEST (V_DATA CHAR, V_VALUE NUMBER);
DECLARE
V_CNT SERCHARTYPE := SERCHARTYPE();
V_TEXT CHAR(20) := 'XXXYYYOOOOXXXYYYOOO';
V_DATA CHAR;
V_VALUE NUMBER;
BEGIN
FOR I IN 1..LENGTH(V_TEXT) LOOP
V_CNT.EXTEND;
V_CNT( I ) := SUBSTR(V_TEXT,I,1);
END LOOP;
INSERT INTO TCF_TEST
SELECT COLUMN_VALUE, COUNT(*)
FROM TABLE(V_CNT)
GROUP BY COLUMN_VALUE;
END;
/
找出某個字串的較量爭論體例
要在一段字串中
2.寫一個type function 來較量爭論
1.使用TRANSLATE
SELECT A.STATUS_FLAG翻譯社INSTR(A.STATUS_FLAG,'YYYYYY') STR_SC,
NVL(LENGTH(TRANSLATE(SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG翻譯社'YYYYYY')+6,(100-LENGTH(A.STATUS_FLAG))) , 'Y'||SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG,'YYYYYY')+6翻譯社(100-LENGTH(A.STATUS_FLAG))) ,'Y')),0) AS CNT_SC_AFTER翻譯社
NVL(SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG翻譯社'YYYYYY')+6,(100-LENGTH(A.STATUS_FLAG)))翻譯社'Z') AS AFTER_SC
FROM WMG_FUND_SC_CNT_REP_ME A
WHERE A.STATUS_FLAG LIKE '%YYYYYY%'
AND A.STATUS_FLAG LIKE '%X%'
SELECT LENGTH(TRANSLATE('AABBADDDDCCAAA'翻譯社 'A'||'AABBADDDDCCAAA','A')) FROM DUAL;
SELECT LENGTH(TRANSLATE('欄位_A'翻譯社 'Y'||'欄位_A','Y')) FROM DUAL;
留言列表