Oracle PL/SQL全方位詳解

一、PL/SQL簡介

PL/SQL是Oracle資料庫中的一種編程語言，全稱為「Procedural Language/Structured Query Language」，它是在SQL語言的基礎上發展而來的。PL/SQL具有SQL語言的優點，同時又支持過程化編程，可以開發複雜的、具有自主邏輯的程序。在Oracle的資料庫中，PL/SQL可以用來編寫存儲過程、觸發器、函數、包等。

以下是一個簡單的PL/SQL語句的例子：

DECLARE
  v_num NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_num FROM emp;
  IF v_num > 100 THEN
    DBMS_OUTPUT.PUT_LINE('The total number of employees exceeds 100');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The total number of employees is '||v_num);
  END IF;
END;

二、PL/SQL的數據類型

PL/SQL的數據類型包括數值型、字元型、日期型等，以下是介紹其中幾種數據類型的特點：

1. 數值型：包括整型（INTEGER）、浮點型（FLOAT）等，可以進行基本的數學運算。

2. 字元型：包括定長字元型（CHAR）和變長字元型（VARCHAR2）等，在進行資料庫存儲時需要注意不同類型的字元型的存儲方式。

3. 日期型：包括DATE和TIMESTAMP等，可以進行日期和時間的計算和比較。以下是一個日期型的例子：

DECLARE
  v_date DATE := TO_DATE('2021-01-01', 'YYYY-MM-DD');
BEGIN
  DBMS_OUTPUT.PUT_LINE('The date is '||v_date);
END;

三、PL/SQL的控制結構

PL/SQL的控制結構包括分支語句、循環語句等。

1. 分支語句：包括IF-THEN、IF-THEN-ELSE、CASE等，以下是一個IF-THEN-ELSE結構的例子：

DECLARE
  v_num NUMBER := 100;
BEGIN
  IF v_num > 100 THEN
    DBMS_OUTPUT.PUT_LINE('The number is greater than 100');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The number is less than or equal to 100');
  END IF;
END;

2. 循環語句：包括WHILE、FOR LOOP等，以下是一個WHILE循環的例子：

DECLARE
  v_i NUMBER := 1;
BEGIN
  WHILE v_i <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_i);
    v_i := v_i + 1;
  END LOOP;
END;

四、PL/SQL的異常處理

PL/SQL中的異常處理機制可以在程序運行時出現錯誤時進行處理，避免程序的崩潰。以下是異常處理的基本代碼結構：

DECLARE
  v_num NUMBER := 0;
BEGIN
  SELECT 100/v_num INTO v_num FROM DUAL;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Divide by zero');
END;

五、PL/SQL的函數和存儲過程

PL/SQL可以用來編寫函數和存儲過程，以下是一個存儲過程的例子：

CREATE OR REPLACE PROCEDURE get_emp_name (
  p_empno IN NUMBER,
  p_ename OUT VARCHAR2
)
IS
BEGIN
  SELECT ename INTO p_ename FROM emp WHERE empno = p_empno;
END;

六、PL/SQL的包

包是PL/SQL中一種高級的程序模塊，可以將相關的函數和存儲過程進行封裝和管理。以下是一個簡單的包的例子：

CREATE OR REPLACE PACKAGE my_package AS
  PROCEDURE p1;
  FUNCTION f1 RETURN NUMBER;
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package AS
  PROCEDURE p1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from procedure p1');
  END;

  FUNCTION f1 RETURN NUMBER IS
  BEGIN
    RETURN 100;
  END;
END my_package;

七、PL/SQL和Oracle雙機查詢

在Oracle資料庫中，PL/SQL可以與Oracle雙機查詢結合使用，進行複雜的數據查詢和處理。以下是一個簡單的代碼示例：

DECLARE
  CURSOR c1 IS SELECT * FROM emp;
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  FOR r1 IN c1 LOOP
    SELECT empno, ename INTO v_empno, v_ename FROM emp WHERE deptno = r1.deptno;
    DBMS_OUTPUT.PUT_LINE('Empno: '||v_empno||', Ename: '||v_ename);
  END LOOP;
END;

八、PL/SQL和Oracle動態SQL

PL/SQL可以通過使用Oracle動態SQL語句，來進行動態的查詢、更新等操作。以下是一個動態SQL的例子：

DECLARE
  v_deptno NUMBER := 10;
  v_sql VARCHAR2(200);
BEGIN
  v_sql := 'SELECT * FROM emp WHERE deptno = :1';
  EXECUTE IMMEDIATE v_sql USING v_deptno;
END;

九、PL/SQL與其他編程語言的互操作性

PL/SQL可以與其他編程語言進行互操作，例如Java、C++等。以下是一個Java程序調用PL/SQL的例子：

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class JdbcTest {
  public static void main(String[] args) {
    Connection conn = null;
    CallableStatement stmt = null;

    try {
      OraclDataSource ds = new OracleDataSource();
      ds.setUser("username");
      ds.setPassword("password");
      ds.setURL("jdbc:oracle:thin:@hostname:1521:orcl");
      conn = ds.getConnection();

      stmt = conn.prepareCall("{ call get_emp_name(?, ?) }");
      stmt.setInt(1, 7369);
      stmt.registerOutParameter(2, Types.VARCHAR);
      stmt.execute();

      String ename = stmt.getString(2);
      System.out.println("Employee name: "+ename);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException se) { }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException se) { }
      }
    }
  }
}