菲力貓的程式設計

UPDATE table1 a
SET table1_column = (SELECT table2_column
FROM table2
WHERE ID = a.ID)

Adding column to a table :
    ALTER TABLE table_name
     ADD column_name column-definition;

For example:
    ALTER TABLE supplier
     ADD supplier_name  varchar2(50);

Drop an Index :

    DROP INDEX index_name;

Drop a Primary Key :
    ALTER TABLE table_name
    drop CONSTRAINT constraint_name;

Disable a Primary Key :
    ALTER TABLE table_name
    disable CONSTRAINT constraint_name;

Enable a Primary Key :
    ALTER TABLE table_name
    enable CONSTRAINT constraint_name;

使用Translate() 符合條件會回傳null:
ex :
    LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')));     return 1
    LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')));     return 2
    LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')));     return null
    LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));     return null

或是自行建立一個 isNumeric() :
CREATE OR REPLACE FUNCTION isNumeric (str IN VARCHAR2)
    RETURN NUMBER
IS
    v_str VARCHAR2 (1000);
BEGIN
    IF str IS NULL
    THEN
       RETURN 0;
    ELSE
       v_str := TRANSLATE (str, '.0123456789', '.');

       IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
       THEN
          RETURN 1;
       ELSE
          RETURN 0;
       END IF;
    END IF;
END isnumeric;

MS Sql 的 Select into ，在PL SQL 也是有的.... 順便記下 PL SQL的insert into

1. PL SQL 的Select into
CREATE TABLE [New Table Name] AS SELECT * FROM [Table Name]

2. PL SQL 的 insert into select
INSERT ALL INTO [Table Name 1] SELECT * FROM [Table Name 2]

OR

INSERT INTO [Table Name 1] ([Column Name]+)
SELECT  [Column Name]+ FROM [Table Name 2]

nvl(expr1,expr2) - 檢查null，回傳參數1 or 2
ex: select nvl('a','b') from dual  ==> a
      select nvl(null,'b') from dual  ==> b

to_number(string1) - 轉換文字型態為數字(varchar to number)
ex: select to_number('234') from dual  ==> 234

to_date(string1,[foramt_mark],[nls_language]) - 轉換文字型態為日期格式(varchar to date)
ex: to_date('2009/07/09', 'yyyy/mm/dd') ==> 2009/7/9
      to_date('20090709', 'yyyymmdd') ==> 2009/7/9
      to_date('200907'),'yyyymm') ==> 2009/7/1

last_day(date1) - 日期月份的最後一天
ex: select last_day(sysdate) from dual ==> 月底，系統本月的最後一天
      select last_day(add_months(sysdate,1)) from dual ==> 下個月月底
      select last_day(sysdate)+1 from dual ==> 下個月1號

decode(條件, 條件1, 結果1, [條件2, 結果2]... [, default] ) - 條件等於條件1則傳回結果1，條件2則結果2，類似MSSQL 的 Case When應用。
ex:  select decode('C','a1','a2','b1','b2','c1') from dual ==> c1
       UPDATE UPDDATE= decode(PRICE,InputPRICE,UPDDATE,SYSDATE) ==> 我常用的語法，當輸入價格不同時，才更新Update

cast(column_name as DataType) - 變更欄位資料型態與長度
ex:  select cast('1000' as number) from dual
       select cast('abc' as varchar2(100)) from dual

instr(string1,string2) - 查詢字串位置
ex: select INSTR('aaabbbcdddeee','c') from dual ==> 7

參考來源 http://www.techonthenet.com/oracle/index.php

之前重裝了Oracle clent 10g，結果因為沒有完全移除乾淨，造成一堆問題，把完整移除的方法貼上來。

    * Uninstall all Oracle components using the Oracle Universal Installer (OUI).
    * Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
    * Delete any references to Oracle services left behind in the following part of the registry:
      HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
      It should be pretty obvious which ones relate to Oracle.
    * Reboot your machine.
    * Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
    * Delete the "C:\Program Files\Oracle" directory.
    * Empty the contents of your "c:\temp" directory.
    * Empty your recycle bin.