在Oracle用REGEXP_SUBSTR分割字串並做動態SQL

2013-01-12

12543
0

Dotblogs 的標籤：Oracle ,隨手記

正好又做了一個類似的需求，所以乾脆筆記一下，才不會每次要寫又要去查官網。

CREATE OR REPLACE FUNCTION FN_TEST_REG_SUBSTR_DYNAMIC_SQL(I_USER SITE_INFO.SITE_ID%TYPE,
                                               I_DEPTSTR    VARCHAR2)
  RETURN VARCHAR2 AS
  V_SQL     VARCHAR2(500) := '';
  V_UPBOUND NUMBER := 10;
  V_VAR     VARCHAR2(1000) := '';
  V_CSR SYS_REFCURSOR;
BEGIN
  FOR CNT IN 1 .. V_UPBOUND LOOP
    SELECT REGEXP_SUBSTR(I_DEPTSTR, '[^,]+', 1, CNT) INTO V_VAR FROM DUAL;
    IF V_VAR IS NULL THEN
      EXIT;
    END IF;
    IF LENGTH(V_SQL) > 0 THEN 
      V_SQL := V_SQL || ' OR '; 
    END IF;
    V_SQL := V_SQL || 'T.DEPTID LIKE ''' || V_VAR || '''';
  END LOOP;
  V_SQL := 'SELECT COUNT(1) FROM USERS T WHERE T.ID = ''' || I_USER || ''' AND (' || V_SQL || ')';
  OPEN V_CSR FOR V_SQL;
  FETCH V_CSR INTO V_VAR;
  CLOSE V_CSR;
  RETURN V_VAR;
  --傳入：I_USER := 'LEO', I_DEPTSTR = 'AA%,BCD%,CCC%';
  --結果 SQL：SELECT COUNT(1) FROM USERS T WHERE T.ID = 'LEO' AND (T.DEPTID LIKE 'AA%' OR T.DEPTID LIKE 'BCD%' OR T.DEPTID LIKE 'CCC%')
END FN_TEST_REG_SUBSTR_DYNAMIC_SQL;

REGEXP_SUBSTR 設定用 , 號分隔，因為一次只會抓一個，所以透過 FOR 迴圈抓，同時組成一個動態 SQL 要用的字串。

動態 SQL 的部分，這邊我用的是 SYS_REFCURSOR 的方式，然後因為只會取回一個值，就直接抓了回傳。

參考資料：

A Dynamic SQL Scenario Using Native Dynamic SQL

Database SQL Reference - REGEXP_SUBSTR

--------
沒什麼特別的~
不過是一些筆記而已

回首頁