Oracle SQL 與 PL/SQL 語法完整速查手冊

PL/SQL 區塊結構

DECLARE
  v_name VARCHAR2(30) := 'Jack';
BEGIN
  SELECT v_name FROM dual;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('有異常');
END;

流程控制語句

IF-THEN-ELSIF-ELSE

DECLARE
  v_num1 NUMBER;
  v_num2 NUMBER;
  v_result VARCHAR2(10);
BEGIN
  IF v_num1 IS NULL OR v_num2 IS NULL THEN
    v_result := 'Undefined';
  ELSIF v_num1 > v_num2 THEN
    v_result := 'num1 is big';
  ELSE
    v_result := 'num2 is big';
  END IF;
END;

CASE 表達式

DECLARE
  grade CHAR := 'A';
  remark VARCHAR2(20);
BEGIN
  CASE grade
    WHEN 'A' THEN remark := 'is Excellent';
    WHEN 'B' THEN remark := 'is Good';
  END CASE;
END;

迴圈結構

FOR LOOP

DECLARE
  total INTEGER := 0;
BEGIN
  FOR i IN 1..19 LOOP
    total := total + 1;
  END LOOP;
END;

LOOP ... EXIT WHEN

DECLARE
  v_count INTEGER := 1;
BEGIN
  LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count >= 10;
  END LOOP;
END;

WHILE LOOP

DECLARE
  v_num1 NUMBER := 10;
BEGIN
  WHILE v_num1 > 1 LOOP
    v_num1 := v_num1 + 1;
  END LOOP;
END;

動態 SQL (EXECUTE IMMEDIATE)

語法結構：

EXECUTE IMMEDIATE dynamic_sql
  [INTO {define_variable [, ...] | record}]
  [USING [IN | OUT | IN OUT] bind_argument [, ...]]
  [{RETURNING | RETURN} INTO bind_argument [, ...]];

常見用法範例

執行 DDL

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id INTEGER, name VARCHAR2(20))';
END;

執行匿名 PL/SQL 區塊

DECLARE
  plsql VARCHAR2(200);
BEGIN
  plsql := 'DECLARE systime VARCHAR2(20); BEGIN SELECT TO_CHAR(SYSDATE, ''dd-mm-yyyy day'') INTO systime FROM dual; DBMS_OUTPUT.PUT_LINE(''當前日期是：''||systime); END;';
  EXECUTE IMMEDIATE plsql;
END;

綁定變數執行 DML

DECLARE
  plsql VARCHAR2(200);
  t_name VARCHAR2(20) := 'Jock';
  t_id INTEGER := 1002;
BEGIN
  plsql := 'INSERT INTO temp_table VALUES (:1, :2)';
  EXECUTE IMMEDIATE plsql USING t_name, t_id;
END;

異常處理

DECLARE
  temp_ex EXCEPTION;
  t_num INTEGER;
BEGIN
  SELECT COUNT(id) INTO t_num FROM temp_table WHERE id = '1031';
  IF t_num >= 1 THEN
    RAISE temp_ex;
  END IF;
  DBMS_OUTPUT.PUT_LINE('該用戶不存在');
EXCEPTION
  WHEN temp_ex THEN
    DBMS_OUTPUT.PUT_LINE('該用戶已經存在');
END;

游標操作

隱式游標屬性

• %FOUND / %NOTFOUND
• %ROWCOUNT
• %ISOPEN

顯式游標完整流程

DECLARE
  fname VARCHAR2(20);
  lname VARCHAR2(20);
  CURSOR c_student IS
    SELECT firstname, lastname FROM student WHERE id = '1001';
BEGIN
  OPEN c_student;
  FETCH c_student INTO fname, lname;
  IF c_student%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('沒有找到記錄');
  ELSE
    DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname);
  END IF;
  CLOSE c_student;
END;

游標 FOR 迴圈 (自動開啟/關閉/提取)

DECLARE
  fname VARCHAR2(20);
  lname VARCHAR2(20);
  CURSOR t_student IS
    SELECT firstname, lastname FROM student WHERE id < 1001;
BEGIN
  FOR stus IN t_student LOOP
    fname := stus.firstname;
    lname := stus.lastname;
    DBMS_OUTPUT.PUT_LINE('姓名：' || fname || ' ' || lname);
  END LOOP;
END;

預存程序與函數

建立無參預存程序

CREATE OR REPLACE PROCEDURE showInfo AS
BEGIN
  SELECT * FROM student;
END;

建立帶參預存程序

CREATE OR REPLACE PROCEDURE showInfo (Major IN VARCHAR2) AS
BEGIN
  SELECT * FROM student WHERE major = Major;
END;

建立函數

CREATE OR REPLACE FUNCTION getCount (Major IN VARCHAR2) RETURN NUMBER AS
  f_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO f_count FROM students WHERE major = Major;
  RETURN f_count;
END;

套件 (Package)

套件規格

CREATE OR REPLACE PACKAGE emp_package AS
  PROCEDURE my_proc (
    lend_num VARCHAR2,
    lend_name VARCHAR2,
    lend_sex VARCHAR2,
    major VARCHAR2
  );
END emp_package;

套件主體

CREATE OR REPLACE PACKAGE BODY emp_package AS
  PROCEDURE my_proc (
    lend_num VARCHAR2,
    lend_name VARCHAR2,
    lend_sex VARCHAR2,
    major VARCHAR2
  ) IS
  BEGIN
    INSERT INTO emp(lnum, lname, lsex, major)
    VALUES (lend_num, lend_name, lend_sex, major);
  END my_proc;
END emp_package;

視圖、序列、資料庫連結、索引

視圖

CREATE OR REPLACE VIEW v_student AS SELECT * FROM student;
DROP VIEW v_student;

序列

CREATE SEQUENCE seq_Id
  MINVALUE 1 MAXVALUE 1000
  START WITH 1 INCREMENT BY 1 CACHE 20;

資料庫連結

CREATE DATABASE LINK link_goods
  CONNECT TO scott IDENTIFIED BY scott
  USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.34)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Orcl)))';

索引

CREATE UNIQUE INDEX i_id ON student(id);
ALTER INDEX i_id REBUILD STORAGE (INITIAL 1M NEXT 512K);
DROP INDEX i_id;