Oracle DBMS_ASSERT

 

Oracle DBMS_ASSERT

https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_assert.htm

The DBMS_ASSERT package provides an interface to validate properties of the input value.

檢核 變數啦!!!

Oracle Alternative Quoting Mechanism (''Q'') for String Literals

 

Oracle Alternative Quoting Mechanism (''Q'') for String Literals

https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

Oracle Database offers the ability, in both SQL and PL/SQL, to specify our own user-defined delimiters for string literals. Here's how it works: you prefix your literal with the letter "q". Then you type a single quote, followeed by your starting delimiter for the literal. Then you type your literal string without having to double up on your single quote characters. When you have typed in your full literal, terminate it with your ending delimiter, followed by a single quote. In other words, you will follow this general format: q'[your string here]' where "[" represents (and certainly could be used for) the starting delimiter, and "]" represents the ending delimiter. Oracle automatically recognizes "paired" delimiters, such as [], {}, (), and <>. If you want to use some other character as your start delimiter and it doesn't have a "natural" partner for termination, you must use the same character for start and end delimiters. Finally, if you choose a character for a delimiter and it appears in your string immediately before a single quotation mark, Oracle will be unhappy and raise an error.

Oracle 數據庫在 SQL 和 PL/SQL 中都提供了為字符串文字指定我們自己的用戶定義分隔符的能力。它是這樣工作的：你在你的文字前加上字母“q”。然後鍵入一個單引號，然後是文字的起始分隔符。然後你輸入你的文字字符串，而不必在你的單引號字符上加倍。當你輸入完整的文字後，用你的結束分隔符終止它，然後是一個單引號。換句話說，您將遵循以下通用格式： q'[your string here]' 其中“[”表示（當然可以用於）起始分隔符，“]”表示結束分隔符。 Oracle 自動識別“成對的”分隔符，例如 []、{}、() 和 <>。如果您想使用其他字符作為起始分隔符並且它沒有“自然”夥伴來終止，則您必須對起始和結束分隔符使用相同的字符。最後，如果您選擇一個字符作為分隔符並且它出現在您的字符串中的單引號之前，Oracle 將不高興並引發錯誤。

翻譯

使用 q'! !' 不需要用 '' 替代 '

Oracle sql 抓取 result set 後的指定筆數

 Oracle sql 抓取 result set 後的指定筆數

select *
from mytable
--第一筆不要
offset 1 rows
--只要三筆
fetch next 3 rows only

參考

Oracle PL/SQL: 取得排序後的第 n ~ m 筆資料

https://tomkuo139.blogspot.com/2009/06/plsql-n-m.html

PL/SQL - 身分證檢查

 

PL/SQL - 身分證檢查

http://my-plsql.blogspot.com/2009/04/plsql_22.html

參考修改成 Function

CREATE OR REPLACE FUNCTION CHK_IDN_VALID (
    p_idn IN VARCHAR2
) RETURN BOOLEAN IS
    p_chkflg_out BOOLEAN;
BEGIN
    DECLARE
        myid          VARCHAR2(10); --身分證號碼
        temp          PLS_INTEGER;
        combine_varchar2  VARCHAR2(11) := NULL;
        --權重
        weight_varchar2   VARCHAR2(11) := '19876543211';

        FUNCTION get_prechar (
            v_pre IN VARCHAR2
        ) RETURN VARCHAR2 IS

            TYPE charTable is table of varchar2(10);
            t_charTable charTable := charTable('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');
            TYPE numberTable is table of varchar2(10);
            t_numberTable numberTable := numberTable('10','11','12','13','14','15','16','17','18','19    20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35');


        BEGIN
            FOR i IN 1..26 LOOP
                IF (t_charTable(i) = v_pre) THEN
                    RETURN t_numberTable(i);
                END IF;
            END LOOP;
        END get_prechar;

    BEGIN
        myid := p_idn;
        p_chkflg_out := false;

        --長度檢查
        IF (LENGTH(myid) <> 10) THEN
            RETURN p_chkflg_out;
        END IF;
        --第一碼檢查
        temp := ascii(substr(myid, 1, 1));
        IF (temp <65 OR temp > 90) THEN
            RETURN p_chkflg_out;
        END IF;

        --第二碼檢查
        temp := to_number(substr(myid, 2, 1));
        IF temp NOT IN ( 1, 2 ) THEN
           RETURN p_chkflg_out;
        END IF;

        temp := 0;
        combine_varchar2 := get_prechar(substr(myid, 1, 1)) || substr(myid, 2, 9);

        FOR i IN 1..11 LOOP
            temp := temp + (to_number(substr(combine_varchar2, i, 1)) * to_number(substr(weight_varchar2, i, 1)));
        END LOOP;

        IF (MOD(temp, 10) = 0) THEN
            p_chkflg_out := true;
        ELSE
            p_chkflg_out := false;
        END IF;

        RETURN p_chkflg_out;
    EXCEPTION
        WHEN OTHERS THEN
           Dbms_Output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           RETURN p_chkflg_out;
    END;
END chk_idn_valid;

-- Created on 2021/10/21 by SSC24 
set SERVEROUTPUT ON
declare 
  -- Local variables here
  i integer;
  idn2 varchar2(2);
  idn8 varchar2(8);
  idn varchar2(10);
begin
  -- Test statements here
  FOR i in 0..99 LOOP
    idn8 := 'F1252912';
    idn2 := lpad(i,2,'0');
    idn := idn8 || idn2;
    IF nps.CHK_IDN_VALID (idn) THEN
      Dbms_Output.put_line(idn);
    END IF;
  END LOOP;
end;

F125291209
F125291218
F125291227
F125291236
F125291245
F125291254
F125291263
F125291272
F125291281
F125291290

已順利完成 PL/SQL 程序.

Oracle PL/SQL 筆記

Oracle PL/SQL 筆記

[Oracle] 開發 & 使用 Stored Procedures

http://godleon.blogspot.com/2008/12/oracle-stored-procedures.html

[Tips]運用PL/SQL撰寫procedure/function/package

http://auneths.blogspot.com/2013/11/tipsplsqlprocedurefunction.html

Oracle Procedure和Function的差異

https://matthung0807.blogspot.com/2017/09/oracle-procedurefunction.html