Terry的Blog

SQL and PL/SQL
 
Oracle 的內置函數提供了全角和半角字符轉換的功能:
SELECT TO_SINGLE_BYTE('１２３ａｂｃＸＹＺ') FROM dual -- 123abcXYZ
SELECT TO_MULTI_BYTE('123abcXYZ') FROM dual -- １２３ａｂｃＸＹＺ

一些PL/SQL的小特性:

DECLARE
    -- Local variables here
    i   INTEGER;
    idx VARCHAR2(2000);
   
    TYPE word_list IS TABLE OF idx%TYPE INDEX BY idx%TYPE;
    the_list word_list;

    wChar VARCHAR2(2000);

    wTmp varchar2(2000);
    num1 NUMBER(12);
   
    chr1 CHAR(5);
   
    FUNCTION tstChar(inputChr IN CHAR) RETURN NUMBER IS
    BEGIN
        dbms_output.put_line('|' || inputChr || '|');
        RETURN 1;
    END;
BEGIN
    -------------------------------------------------------------
    --               Max Min value
    -------------------------------------------------------------
    num1 := greatest(100,200,300,210,120,99);
    dbms_output.put_line(num1); -- 300
   
    wTmp := greatest('abc','bcd','cde','xxx','zzz','yyy');
    dbms_output.put_line(wTmp); -- zzz
   
    wTmp := least('abc','bcd','cde','xxx','zzz','yyy');
    dbms_output.put_line(wTmp); -- abc
   
    -------------------------------------------------------------
    --               substr
    -------------------------------------------------------------   
    wTmp := 'abcdefg';
    wTmp := substr(wTmp,-3,2);
    dbms_output.put_line(wTmp); -- ef
   
    -------------------------------------------------------------
    --               lpad
    -------------------------------------------------------------   
    wTmp := lpad(wTmp,10,'?');
    dbms_output.put_line(wTmp); -- ????????ef

    wTmp := NULL;
    wTmp := lpad(wTmp,10,'?');
    dbms_output.put_line(wTmp); -- null

    -------------------------------------------------------------
    --               length of char
    -------------------------------------------------------------   
    chr1 := 'aaa';
    num1 := tstChar(chr1);
    
    -------------------------------------------------------------
    --               table indexed by varchar2
    -------------------------------------------------------------   
    dbms_output.put_line('------ test 1 collection index by varchar2------');
    the_list('key1') := 'value1';
    the_list('key2') := 'value2';
    the_list('key3') := 'value3';
    the_list('key0') := NULL;

    idx := the_list.FIRST();

    WHILE idx IS NOT NULL LOOP
        Dbms_Output.Put_line(idx || '   ' || the_list(idx));
        idx := the_list.NEXT(idx);
    END LOOP;

    IF the_list.EXISTS('aaa') THEN
        Dbms_Output.Put_line('aaa');
    ELSE
        Dbms_Output.Put_line('no aaa');
    END IF;

    Dbms_Output.Put_line(the_list.COUNT);
    the_list.delete('key1');
    Dbms_Output.Put_line(the_list.COUNT);

    -------------------------------------------------------------
    --               handle null
    -------------------------------------------------------------   
    dbms_output.put_line('------ test 2  handle null------');
    idx := NULL;
    idx := CASE WHEN idx IS NULL THEN 'is null' WHEN idx IS NOT NULL THEN 'is not null' ELSE 'else' END;
    Dbms_Output.Put_line(idx);

    idx := NULL;
    SELECT decode(idx, NULL, 'is null', 'is not null', 'else')
      INTO idx
      FROM dual;
    Dbms_Output.Put_line(idx);

    idx := NULL;
    idx := REPLACE('old_string_1', NULL, 'replace null');
    Dbms_Output.Put_line(idx);

    idx := REPLACE('old_string_2', '_', NULL);
    Dbms_Output.Put_line(idx);

    idx := 'apple' || NULL || NULL || 'sauce';
    Dbms_Output.Put_line(idx);

    -------------------------------------------------------------
    --               動態sql
    -------------------------------------------------------------   
    wChar := 'select to_char(sysdate,''yyyy/mm/dd'') from dual ';
   
    execute immediate wChar into idx;
    Dbms_Output.Put_line('test execute immediate = ' || idx);
   

    -------------------------------------------------------------
    --               隱式光標
    -------------------------------------------------------------   

--    for rec in (select *  from mststrc21) loop
--        Dbms_Output.Put_line('rec = ' || rec.pantpatternno);
--    end loop;
   
    -------------------------------------------------------------
    --               trim()
    -------------------------------------------------------------   
    --Trim(Leading|Trailing|Both trim_character from trim_source)
    idx := TRIM(' abc ');
    Dbms_Output.Put_line('|' || idx || '|');
   
    idx := TRIM('　abc　'); -- full space
    Dbms_Output.Put_line('|' || idx || '|');
   
    idx := TRIM(leading '　' FROM '　abc　'); -- full space
    Dbms_Output.Put_line('|' || idx || '|');
   
    idx := TRIM(leading 'ab' FROM 'abc');
    Dbms_Output.Put_line('|' || idx || '|'); -- ORA-30001: 切捨てセットの文字は1つにする必要があります
   
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        Dbms_Output.Put_line('NO_DATA_FOUND');   
        Dbms_Output.Put_line(SQLCODE);   
        Dbms_Output.Put_line(SQLERRM);
    WHEN TOO_MANY_ROWS THEN
        Dbms_Output.Put_line('TOO_MANY_ROWS');   
        Dbms_Output.Put_line(SQLCODE);   
        Dbms_Output.Put_line(SQLERRM);
    WHEN OTHERS THEN
        Dbms_Output.Put_line('OTHERS');   
        Dbms_Output.Put_line(SQLCODE);   
        Dbms_Output.Put_line(SQLERRM);   
END;