七月 12, 2023
摘要:Oracle PL/SQL 是 SQL 的过程式扩展。PL/SQL 的程序结构将代码划分为由以下关键字区分的块:DECLARE
、BEGIN
、EXCEPTION
和END
。未命名的 PL/SQL 代码块(未作为过程、函数或包存储在数据库中的代码)称为匿名块。
Oracle 用法
匿名块作为 Oracle PL/SQL 的基本单元,包含以下代码部分:
- 声明性部分(可选)— 包含变量(名称、数据类型和初始值)。
- 可执行部分(必填) — 包含可执行语句(每个块结构必须至少包含一个可执行的 PL/SQL 语句)。
- 异常处理部分(可选)— 包含用于处理代码中的异常或错误的元素。
例子
Oracle 匿名块的简单结构。
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END;
/
hello world
PL/SQL procedure successfully completed.
Oracle PL/SQL 匿名块可以包含高级代码元素,例如函数、游标、动态 SQL 和条件逻辑。以下匿名块使用了游标、条件逻辑和异常处理。
SET SERVEROUTPUT ON;
DECLARE
v_sal_chk NUMBER;
v_emp_work_years NUMBER;
v_sql_cmd VARCHAR2(2000);
BEGIN
FOR v IN (SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS
EMP_NAME, HIRE_DATE, SALARY FROM EMPLOYEES)
LOOP
v_emp_work_years := EXTRACT(YEAR FROM SYSDATE) - EXTRACT (YEAR FROM v.hire_date);
IF v_emp_work_years >= 10 and v.salary <= 6000 then
DBMS_OUTPUT.PUT_LINE('Consider a Bonus for: ' || v.emp_name);
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('CODE ERR: ' || sqlerrm);
END;
/
前面的示例根据表EMPLOYEES
的列HIRE_DATE
计算每个雇员的工作年数。如果员工工作了十年或更长时间,工资为 6000 美元或更低,系统会打印消息“考虑奖金:<员工姓名>”。
有关详细信息,请参阅 Oracle 文档中的 PL/SQL 概述。
PostgreSQL 用法
PostgreSQL 支持类似于 Oracle 匿名块的功能。在 PostgreSQL 中,您可以使用 PL/pgSQL 的DO
语句将未存储在数据库中的 PL/pgSQL 代码作为独立代码段运行。
PL/pgSQL 是 ANSI SQL 的 PostgreSQL 扩展,具有许多与 Oracle PL/SQL 相似的元素。PostgreSQL 的DO
语法使用与 Oracle 匿名块类似的代码结构:
- 声明性部分(可选)。
- 可执行文件部分(必需)。
- 异常处理部分(可选)。
例子
PostgreSQL DO 简单的结构。
SET CLIENT_MIN_MESSAGES = 'debug';
-- Equivalent To Oracle SET SERVEROUTPUT ON
DO $$
BEGIN
RAISE DEBUG USING MESSAGE := 'hello world';
END $$;
DEBUG: hello world
DO
PostgreSQL PL/pgSQL DO
语句支持使用高级代码元素,如函数、游标、动态 SQL 和条件逻辑。
以下示例是从上一节中介绍的 Oracle “员工奖金” PL/SQL 匿名块示例转换而来的更复杂的 PL/pgSQL DO 代码结构:
DO $$
DECLARE
v_sal_chk DOUBLE PRECISION;
v_emp_work_years DOUBLE PRECISION;
v_sql_cmd CHARACTER VARYING(2000);
v RECORD;
BEGIN
FOR v IN
SELECT employee_id, CONCAT_WS('', first_name, ' ', last_name) AS emp_name, hire_date, salary FROM employees
LOOP
v_emp_work_years := EXTRACT (YEAR FROM now()) - EXTRACT (YEAR FROM v.hire_date);
IF v_emp_work_years >= 10 AND v.salary <= 6000 THEN
RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Consider a Salary Raise for: ',v.emp_name);
END IF;
END LOOP;
EXCEPTION
WHEN others THEN
RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'CODE ERR: ',SQLERRM);
END $$;
有关详细信息,请参阅 PostgreSQL 文档中的 DO。