DBMS_ASSERT

DBMS_ASSERT.ENQUOTE_LITERAL => 回傳字串前後加上 ' 

DBMS_ASSERT.ENQUOTE_NAME => 回傳字串前後加上 " 

DBMS_ASSERT.SQL_OBJECT_NAME => 尋找是否存在符合的物件 

 select table_name, dbms_assert.enquote_literal(table_name) , dbms_assert.enquote_name(table_name) from all_tables where rownum <= 1 

 

DECLARE
  vobject1 VARCHAR2(80) := 'dual';
  vobject2 VARCHAR2(80) := 'dualx';
  vresult1 VARCHAR(80);
  vresult2 VARCHAR(80);
BEGIN
  BEGIN
    vresult1 := dbms_assert.sql_object_name(vobject1);
    IF vresult1 IS NOT NULL THEN
      dbms_output.put_line('Found Object:' || vresult1);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(dbms_utility.format_error_stack);
  END;

  BEGIN
    vresult2 := dbms_assert.sql_object_name(vobject2);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(dbms_utility.format_error_stack);
  END;
END;

[Performance] cursor loop vs collection forall

 --建立暫存表格

CREATE TABLE cux_objects AS SELECT owner, object_name name, object_type type FROM all_objects WHERE 1 = 0 

--測試 1 (Cursor & Loop)  => about 40 sec

DECLARE

  vOwner      all_objects.owner%TYPE;

  vObjectName all_objects.object_name%TYPE;

  vObjectType all_objects.object_type%TYPE;

  vStart      DATE;

  vEnd        DATE;

  CURSOR curObject IS

    SELECT owner,

           object_name,

           object_type

    FROM   all_objects;

BEGIN

  vStart := SYSTIMESTAMP;

  OPEN curObject;

  LOOP

    FETCH curObject

      INTO vOwner,

           vObjectName,

           vObjectType;

    EXIT WHEN curObject%NOTFOUND;

    INSERT INTO cux_objects

    VALUES

      (vOwner,

       vObjectName,

       vObjectType);

  END LOOP;

  CLOSE curObject;

  COMMIT;

  vEnd := SYSTIMESTAMP;

  dbms_output.put_line((vEnd - vStart) * 86400);

END;

--測試 2 (Collection & forall)  => about 6 sec

DECLARE

  TYPE tOwner IS TABLE OF all_objects.owner%TYPE;

  TYPE tObjectName IS TABLE OF all_objects.object_name%TYPE;

  TYPE tObjectType IS TABLE OF all_objects.object_type%TYPE;

  vOwner      tOwner;

  vObjectName tObjectName;

  vObjectType tObjectType;

  vStart      DATE;

  vEnd        DATE;

BEGIN

  vStart := SYSTIMESTAMP;

  SELECT owner,

         object_name,

         object_type

  BULK   COLLECT

  INTO   vOwner,

         vObjectName,

         vObjectType

  FROM   all_objects;

  FORALL idx IN vObjectName.first .. vObjectName.last

    INSERT INTO cux_objects

    VALUES

      (vOwner(idx),

       vObjectName(idx),

       vObjectType(idx));

  COMMIT;

  

  vEnd := SYSTIMESTAMP;

  dbms_output.put_line ((vEnd - vStart) * 86400);

END;

Functions in the WITH Clause (for 12c release 1)

 範例 : 

WITH

 FUNCTION getProduct(pInteger IN NUMBER) RETURN NUMBER IS 

 BEGIN 

    RETURN pInteger * pInteger;

 END getProduct;

SELECT LEVEL , getProduct (LEVEL)  from dual

CONNECT BY LEVEL <= 10 

--Using dynamic SQL 

DECLARE

  vSql     VARCHAR2(32767);

  vCursor  SYS_REFCURSOR;

  vMyValue NUMBER;

BEGIN

  vSql := 'WITH

           FUNCTION getProduct(pInteger IN NUMBER) RETURN NUMBER IS 

           BEGIN 

              RETURN pInteger * pInteger;

           END getProduct;

          SELECT  getProduct (LEVEL) myValue  from dual

          CONNECT BY LEVEL <= 10';

  OPEN vCursor FOR vSql;

  LOOP

    FETCH vCursor

      INTO vMyValue;

    EXIT WHEN vCursor%NOTFOUND;

    dbms_output.put_line('My Value => ' || vMyValue);

  END LOOP;

  CLOSE vCursor;

END;

How To Convert Number into Words using Oracle SQL Query

參考網址

String to Vector

範例 : 

DECLARE

  TYPE rVectorTabType IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;

  xVectorTab rVectorTabType;

  vDelimiter      VARCHAR2(1) := ':';

  vStringIn       VARCHAR2(32767) := 'A:B:C:D:E';

  vCounter        NUMBER;

  vDelimiterIndex NUMBER;

BEGIN

  vDelimiterIndex := INSTR(vStringIn, vDelimiter, 1);

  vCounter        := 0;

  WHILE vDelimiterIndex > 0

  LOOP

    xVectorTab(vCounter) := SUBSTR(vStringIn, 1, vDelimiterIndex - 1);

    vStringIn := SUBSTR(vStringIn, vDelimiterIndex + 1);

    vDelimiterIndex := INSTR(vStringIn, vDelimiter, 1);

    vCounter := vCounter + 1;

  END LOOP;

  xVectorTab(vCounter) := vStringIn;

  

  dbms_output.put_line('Table Count : ' || xVectorTab.count);

  FOR i IN xVectorTab.first .. xVectorTab.last LOOP 

    dbms_output.put_line('Element(' || i || ') =>' ||xVectorTab(i)); 

  END LOOP;

END;

結果 : 

Table Count : 5

Element(0) =>A

Element(1) =>B

Element(2) =>C

Element(3) =>D

Element(4) =>E

Lock Handle

範例 : 

[建立Package Body]

CREATE OR REPLACE PACKAGE BODY cux_lock AS

  FUNCTION getHandle

  (

    pLockName       IN VARCHAR2,

    pExpirationSecs IN NUMBER DEFAULT 60

  ) RETURN VARCHAR2 IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    vLockHandle VARCHAR2(128);

  BEGIN

    dbms_lock.allocate_unique(lockname => pLockName,

                              lockhandle => vLockHandle,

                              expiration_secs => pExpirationSecs);

    RETURN vLockHandle;

  END getHandle;

  PROCEDURE RequestLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  ) IS

    vLockStatus NUMBER;

  BEGIN

    vLockstatus := dbms_lock.request(lockhandle => getHandle(pLockName),

                                     lockmode => dbms_lock.x_mode,

                                     timeout => dbms_lock.maxwait,--wait forever

                                     release_on_commit => FALSE);

    CASE vLockstatus

      WHEN 0 THEN

        vMessage := NULL;

      WHEN 2 THEN

        vMessage := 'Dead lock - Detected';

      WHEN 4 THEN

        vMessage := 'Lock already obtained';

      ELSE

        vMessage := 'Request lock failed : ' || vlockstatus;

    END CASE;

  END requestLock;

  PROCEDURE ReleaseLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  ) IS

    vLockStatus NUMBER;

  BEGIN

    vLockstatus := dbms_lock.release(lockhandle => getHandle(pLockName));

    IF vLockStatus > 0 THEN 

       vMessage := 'Release lock failed : ' || vLockstatus;

    END IF;

  END ReleaseLock;

END cux_lock;

[建立Package]

CREATE OR REPLACE PACKAGE cux_lock AS

  PROCEDURE RequestLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  );

  PROCEDURE ReleaseLock

  (

    pLockName IN VARCHAR2,

    vMessage  OUT VARCHAR2

  );

END cux_lock;

=> Session 1 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.RequestLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;

=> Session 2 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.RequestLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;

=> Session  1 

DECLARE

 xMessage VARCHAR2(2000);

BEGIN

 cux_lock.ReleaseLock(pLockName => 'MyLock1',vMessage => xMessage);    

 dbms_output.put_line(xMessage);          

END;

遞迴應用範例

DECLARE

  vInteger PLS_INTEGER := 3;

  

  FUNCTION FnRecursive(pInteger IN NUMBER) RETURN NUMBER IS

    vTotal NUMBER := 0;

  BEGIN

    vTotal := vTotal + pInteger;

    IF pInteger > 0 THEN

      vTotal := vTotal + FnRecursive(pInteger - 1);

    END IF;

    RETURN(vTotal);

  END FnRecursive;

BEGIN

  dbms_output.put_line(FnRecursive(vInteger));

END;

取得特定分隔內容資料

1 => 

 FUNCTION temp

  (

    pString IN VARCHAR2,

    pLevel  IN NUMBER,

    pSymble IN CHAR DEFAULT ','

  ) RETURN VARCHAR2 IS

    vString        VARCHAR2(2000);

    vPositionStart NUMBER;

    vPositionEnd   NUMBER;

  BEGIN

    vString        := pSymble || pString || pSymble;

    vPositionStart := instr(vString, pSymble, 1, pLevel);

    vPositionEnd   := instr(vString, pSymble, 1, pLevel + 1);

    RETURN substr(vString, (vPositionStart + 1),

                  vPositionEnd - (vPositionStart + 1));

  EXCEPTION

    WHEN OTHERS THEN

      RETURN NULL;

  END temp;

2 => 

FUNCTION temp

  (

    pString IN VARCHAR2,

    pLevel  IN NUMBER

  ) RETURN VARCHAR2 IS

    vSymble dbms_utility.number_array;

    vLength NUMBER;

    vCount  NUMBER := 0;

    vOutException EXCEPTION;

  BEGIN

    vLength := length(pString);

    FOR i IN 1 .. vLength

    LOOP

      IF SUBSTR(pString, i, 1) = ',' THEN

        vCount := vCount + 1;

        vsymble(vCount) := i;

      END IF;

    END LOOP;

    IF pLevel > vsymble.count + 1 THEN

      RAISE vOutException;

    END IF;

    IF pLevel = 1 THEN

      RETURN SUBSTR(pString, 1, vsymble(1) - 1);

    END IF;

    IF pLevel = vsymble.count + 1 THEN

      RETURN SUBSTR(pString, vsymble(vsymble.count) + 1);

    END IF;

    RETURN SUBSTR(pString, vsymble(pLevel - 1) + 1,

                  vsymble(pLevel) - vsymble(pLevel - 1) - 1);

  EXCEPTION

    WHEN vOutException THEN

      RETURN 'Out of Array';

  END temp;

3 => 

FUNCTION temp

(

  pString IN VARCHAR2,

  pLevel  IN NUMBER,

  pSymble IN CHAR DEFAULT ','

) RETURN VARCHAR2 IS

  vString VARCHAR2(200);

BEGIN

  SELECT str

  INTO   vString

  FROM   (SELECT LEVEL AS pos,

                 regexp_substr(pString, '(.*?)(,|$)', 1, LEVEL, NULL, 1) AS str

          FROM   dual

          CONNECT BY LEVEL <= regexp_count(pString, pSymble) + 1)

  WHERE  pos = pLevel;

  RETURN vString;

EXCEPTION

  WHEN OTHERS THEN

    RETURN NULL;

END temp;

[DBMS_SQL] part 1

Create Table & Drop Table by DBMS_SQL => 

DECLARE

  vCursor       NUMBER;

  vCreateString VARCHAR(200);

  vDropString   VARCHAR2(200);

BEGIN

  vCursor     := dbms_sql.open_cursor;

  vDropString := 'Drop Table cux_sql';

  --drop table

  BEGIN

    dbms_sql.parse(vCursor, vDropString, dbms_sql.native);

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line(dbms_utility.format_error_stack);

  END;

  --create table 

  vCreateString := 'Create Table cux_sql

                    (

                      a varchar2(10),

                      b varchar2(10)

                    )';

  dbms_sql.parse(vCursor, vCreateString, dbms_sql.native);

  dbms_sql.close_cursor(vCursor);

EXCEPTION

  WHEN OTHERS THEN

    dbms_sql.close_cursor(vCursor);

    dbms_output.put_line(dbms_utility.format_error_stack);

END;

[Function] NTILE

範例 : 

select NTILE(2) over (order by rownum) from dual 

connect by rownum <= 10

結果 : 

1

1

1

1

1

2

2

2

2

2

TRUNC & ROWNUM

TRUNC(ROWNUM/2,-4)

每兩萬條區分一個群組

結論 : 用於報表轉出Excel的分頁挺有用處的 

Over / Partition 累計

x    y             取得累計數,依據x群組重新計算

-----------

A   100  --> 100

A   200  --> 300 

A   300  --> 600 

B   200  --> 200 

B   400  --> 600 

SELECT x, SUM(y) OVER (PARTITION BY x ORDER BY x,y)

FROM demo 

ORDER BY x,y

結論  :  某些情況下,直接在SQL下取得累計數是必須的, 

例 : Reporting Service 取得累計後無法在累計數量上做其他判斷 (First / Last / Count ...) , 

此時將SQL直接帶入就解決了

另 SUM 改為其他匯總函數也是可以的, 

例 : Count(y) OVER (PARTITION BY x ORDER BY x,y),其用途再累計x群組的累計筆數

REVERSE

SELECT REVERSE('ABC') FROM dual

result : CBA 

  

BEGIN 

  FOR v_counter IN REVERSE 1..5 LOOP 

     dbms_output.put_line('Counter:' || v_counter);

  END LOOP;

END;

result : 

5 

4 

3
2 

1 

USER_

select * from user_tab_columns --Columns

select * from user_table --TABLE

select * from user_sequences --SEQUENCE

select * from user_views --VIEW

select * from user_indexes -- INDEX

select * from user_mviews --Materialized Vies

結論 : 多數資訊都可透過USER_ 查詢到

PL/SQL - 身分證檢查

set serveroutput on; 

declare 

   myid varchar2(10):= 'A123456789'; --身分證號碼

   temp pls_integer;

   combine_char varchar2(9):= null;

  function get_prechar(v_pre in char) return number is

    type char_varray is varray(26) of char; 

    varray_char char_varray := char_varray('A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','X','Y','W','Z','I','O');

  begin 

    for i in 1..varray_char.limit loop 

        if varray_char(i) = v_pre then 

           return i + 9;

        end if;

    end loop;    

  end get_prechar;

begin 

  --第一碼檢查

  temp := ascii(substr(myid,1,1)); 

  if temp <> 90 then

     dbms_output.put_line('第一碼非大寫英文字母');

  end if;

  

  --第二碼檢查

  temp := to_number(substr(myid,2,1));

  if temp not in (1,2) then 

     dbms_output.put_line('第二碼有誤');

  end if;

  

  temp := 0;

  combine_char := substr(get_prechar(substr(myid,1,1)),2,1) || substr(myid,2,8);

  for i in 1..9 loop 

      temp := temp + substr(combine_char,i,1) * (10-i);

  end loop;

  temp := temp +  to_number(substr(get_prechar(substr(myid,1,1)),1,1));

  if (mod(temp,10) = 10-substr(myid,10,1)) then 

     dbms_output.put_line('Valid');

  else 

     dbms_output.put_line('Invalid');

  end if;

  

exception 

   when others then 

      dbms_output.put_line('Invalid');

end;

相關連結 : 檢查規則

結論 : 寫得沒甚麼結構...><"

PL/SQL - 統一編號檢查

set serveroutput on;
declare
  check_flg BOOLEAN:= FALSE;
  tax_ref PLS_INTEGER;
  e_null EXCEPTION;
  e_eight_char EXCEPTION;
  num_sum PLS_INTEGER:=0;
  --邏輯Function
  FUNCTION addition(v_index IN PLS_INTEGER,v_num IN PLS_INTEGER) RETURN PLS_INTEGER IS
    a_int PLS_INTEGER:=0;
  BEGIN
     CASE
       WHEN v_index = (7) THEN
          a_int := v_num * 4;
       WHEN v_index in (2,4,6) THEN
          a_int := v_num * 2;
       ELSE
          a_int := v_num;
     END CASE;
     --return a_int;
     RETURN trunc(a_int / 10) + MOD(a_int,10);
  END addition;
BEGIN
  tax_ref := to_number('86533770');
  --空值檢查
  IF tax_ref IS NULL THEN
     RAISE e_null;
  END IF;
  --8碼檢查
  IF length(tax_ref) <> 8 THEN
     RAISE e_eight_char;
  END IF;
  --邏輯檢查
  FOR i IN 1..length(tax_ref) LOOP
      num_sum := num_sum + addition(i,substr(tax_ref,i,1));
  END LOOP;
  if mod(num_sum,10) = 0 then
     check_flg := TRUE;
  elsif substr(tax_ref,6,1) = 7 then
     if mod(num_sum,9) = 0 then
       check_flg := TRUE;
     end if;
  end if;
  --顯示是否通過檢查
  if check_flg = TRUE then
     dbms_output.put_line('True');
  else
     dbms_output.put_line('False');
  end if;
--例外處理
EXCEPTION
  WHEN e_null THEN
    dbms_output.put_line('空值');
  WHEN e_eight_char THEN
    dbms_output.put_line('並非8碼數字');
  WHEN OTHERS THEN
    dbms_output.put_line('錯誤統編');
END;

Trim

select trim(' A999999A ') from dual 

--捨去前後空白

select trim(leading 'A' from 'A99999A') from dual 

return : 99999A

-- 捨去字串前頭的A

select trim(trailing 'A' from 'A99999A') from dual 

return : A99999

-- 捨去字串後頭的A

select trim('A' from 'A99999A') from dual 

return : 99999

-- 捨去字串前後的A

結論 : Trim不只取消前後的空白,也可自行帶入需要取消的字元

Null & Function

與Null打交道

Nvl(p1,p2) 

if p2 is null then 

   return p2

else

   return p1

end if

Nvl2(p1,p2,p3) 

if p1 is null then 

   return p3

else 

   return p2 

end if

Decode(p1,null,p2,p3)

if p1 is null then 

   return p2 

else 

   return p3 

end if

Coalesce(p1,p2,p3,p4....) 

if p1 is not null then 

   return p1

elsif p2 is not null then 

   return p2

elsif p3 is not null then 

   return p3

elsif p4 is not null then 

   return p4

.....

   

NullIf(p1,p2)

if p1 = p2 then 

   return null 

else

   return p1

end if;

  

Case 

select 
  cast 
  when p1 is null then 
     'null'
  else
     'not null'
  end case
from dual 

總結 : 與null有關的函數不少,端看需求如何

Nvl2

nvl2(p1,p2,p3) 

表示如下 

if p1 is null then 

  return p3

else 

  return p2 

end if

例 : Nvl2(null,'A','B') 

結果 : B

例 : Nvl2('Test','A','B') 

結果 : A

結論 : 表示式同decode(p1,null,p3,p2)  

兩者間我倒覺得Decode比較看得清楚明白些

NullIf

NullIf(p1,p2) =>  表示如下

if p1 = p2 then 

   return null 

else

   return p1 

end if

例 : nullif(10 , 20) 

結果 : 10 

例 : nullif('test','test1') 

結果 : test 

結論 : 我想這種邏輯可用在除法上頭

例  : 100 / p1  (若p1為0,則發生錯誤 : Divisor is Equal to Zero)

若以下列方式改寫,可避免錯誤

100 / nullif(p1,0) (若p1為0 , 則此描述所得的值為null)