Oracle PL/SQL 基礎用法 教學 (basic plsql)

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_name類型同表 dba_users.user_id欄位類型，並初始值為1
  u_id     dba_users.user_id%type := 1; 
  -- 宣告 u_name類型同表 dba_users.username欄位類型
  u_name   dba_users.username%type; 
  -- 宣告 u_name類型同表 dba_users.account_status欄位類型
  u_status dba_users.account_status%type; 
begin
  -- 利用 select查詢出來結果，存入 v_count1變數
  select 1 into v_count1 from dual; 
  -- 將查詢出來結果存入 u_name, u_status變數，並用 u_id變數做條件查詢
  select username, account_status 
  into u_name, u_status 
  from dba_users where user_id = u_id; 
  --
exception
  --
end;

3. 程式執行/邏輯

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

7. Shell script搭PL/SQL範例 (新增Tablespace Datafile)

#!/bin/sh
FILEPATH="/ORACLE/oradata/ORCL/datafile/data"
ADDCOUNT=10

sqlplus -s / as sysdba << EOF
set serveroutput on;
set line 200;
declare
  j varchar2(5) := '';
  cs number := 0;
  cb number := 0;
  ce number := 0;
begin
  --Query File Counts
  select count(*) into cs from dba_data_files where tablespace_name = 'DATA' ;
  --Calc File Start Number
  select cs + 1 into cb from dual;
  --Cale File End Number
  select cb + ${ADDCOUNT} - 1 into ce from dual;
  
  for i in cb..ce
  lopp
    if length(i) = 1 then
      j := '0'||i;
    else
      j := i;
    end if;
    execute immediate 'alter tablespace data add datafile '||'''${FILEPATH}'||j||'.dbf'||''' size 32767M';
    dbms_output.put_line('alter tablespace data add datafile '||'''${FILEPATH}'||j||'.dbf'||''' size 32767M');
  end loop;
exception when others then
  dbms_output.put_line( 'Error: ' || sqlerrm);
end;
/
EOF

8. 新增多個 Tablespace Datafile

declare
 v_c_ts_l varchar2(100):= 'TBS_201412,TBS_201410,TBS_201408,TBS_201406,TBS_201404,TBS_201402';
 v_c    number := 6; --DATAFILEs
 v_c_ts varchar2(100);
begin
for rec in (select regexp_substr(v_c_ts_l, '(.*?)(,|$)', 1, level, null, 1) name from dual connect by level <= regexp_count(v_c_ts_l, ',')+1)
loop
  v_c_ts := rec.name;
  --Create Tablespace
  execute immediate 'create tablespace '||v_c_ts;
  --Add datafile
  for i in 1..v_c
  loop
    execute immediate 'alter tablespace '||v_c_ts||' add datafile';
  end loop;
  --Resize Datafile
  for i in (select file_id from dba_Data_files where tablespace_name = v_c_ts)
  loop
    execute immediate 'alter database datafile '||i.file_id||' resize 32767M';
  end loop;
end loop;
end;
/