漫活部落

1. PL/SQL語法格式

declare
  -- 宣告變數/cursor
begin
  -- 程式執行/邏輯
exception
  -- 例外錯誤處理
end;

2. 變數宣告與初始值

declare
  v_count1 int; -- 宣告 v_count1變數為 int型式
  v_count2 int := 0; -- 宣告 v_count2變數為 int並設定初始值為0
  v_string varchar2(200) := 'SYSTEM'; -- 宣告 v_string變數為 varchar2，長度為200並設定初始值為 'SYSTEM'
  u_id     dba_users.user_id%type := 1; -- 宣告 u_name類型同表 dba_users.user_id欄位類型，並初始值為1
  u_name   dba_users.username%type; -- 宣告 u_name類型同表 dba_users.username欄位類型
  u_status dba_users.account_status%type; -- 宣告 u_name類型同表 dba_users.account_status欄位類型
begin
  select 1 into v_count1 from dual; -- 利用 select查詢出來結果，存入 v_count1變數
  select username, account_status into u_name, u_status from dba_users where user_id = u_id; -- 將查詢出來結果存入 u_name, u_status變數，並用 u_id變數做條件查詢
  --
exception
  --
end;

3. 程式執行/邏輯

declare
  v_count int := 0;
begin
  select count(1) into v_count from dba_users; -- 將查出來的數量存入 v_count變數
  if v_count > 10 then -- if邏輯判斷
    dbms_output.put_line('User counts over 10:' || v_count); -- 利用 dbms_output來輸出訊息，文字連結使用||符號
    execute immediate 'insert into check_accounts values (sysdate, '|| v_count ||')'; -- 執行 insert sql
  end if;
  begin -- 外層程式執行/邏輯部份，可再包一個PLSQL架構，程式出錯時，只會在內部，而不影響外層
    --
  exception
    --
  end;
exception
  --
end;

4. 例外錯誤處理 (僅列出常見的錯誤項目)

declare
  --
begin
  ...
  raise cust_exception;
exception
  when no_data_found then -- 找不到資料時
    dbms_output.put_line('no_data_found:' || sqlerrm);
  when too_many_rows then -- 回傳不止一筆資料時
    dbms_output.put_line('too_many_rows:' || sqlerrm);
  when zero_divide then -- 除數為0時
    dbms_output.put_line('zero_divide:' || sqlerrm);
  when invalid_number then -- 當字串轉為數字但無法成為有效數值時
    dbms_output.put_line('invalid_number:' || sqlerrm);
  when cust_exception then -- 自定義 exception
    dbms_output.put_line('cust_exception: custom excpetion content');
  when others then -- 以上狀況無法解決的其他狀況 (放到最後)
    dbms_output.put_line('others:' || sqlerrm);
end;

5. 資料處理 cursor query

declare
  cursor q_users is -- cursor查詢的 query內容
    select username, account_status 
    from dba_users;
begin
  for rec in q_users -- 以LOOP循環方式，逐筆處理
  loop
    if rec.account_status = 'LOCKED' then
      dbms_output.put_line('Username: ' || rec.username || ' is locked.');
    else
      dbms_output.put_line('Username: ' || rec.username || ' is normal.');
    end if;
  end loop;
exception
  when others then
    dbms_output.put_line('others:' || sqlerrm);
end;

6. 邏輯與判斷 (if & case)

declare
  cursor q_users is
    select username, account_status 
    from dba_users;
begin
  for rec in q_users
  loop
    if rec.username = 'SYS' then -- 用 if 來判斷資料
      pass;
    else
      case rec.account_status -- 用 case 選擇資料處理
        when 'LOCKED' then
          dbms_output.put_line('Username: ' || rec.username || ' is locked.');
        when 'EXPIRED' then
          dbms_output.put_line('Username: ' || rec.username || ' is normal.');
        when 'OPEN' then
          dbms_output.put_line('Username: ' || rec.username || ' is normal.');
      end case;
    end if;
  end loop;
exception
  when others then
    dbms_output.put_line('others:' || sqlerrm);
end;

 

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com