3.2.10.1. Oceanbase-oracle
3.2.10.1.1. PL/SQL 匿名块
描述
PL/SQL 匿名块程序由三个块组成,即声明部分(DECLARE)、执行部分(BEGIN)和异常处理部分(EXCEPTION)。
三个部分的作用如下:
声明部分:在此声明 PL/SQL 用到的变量、类型、游标以及局部的存储过程和函数。
执行部分:显示执行过程及 SQL 语句,是程序的主要部分。
异常处理部分:显示错误处理。
语法
[DECLARE]
-- 声明部分 (可选)
BEGIN
-- 执行部分: 过程及 SQL 语句,即程序的主要部分
[EXCEPTION]
-- 异常处理部分: 错误处理
END;
示例
-- 转换前Oracle PL/SQL 匿名块:
DECLARE
v int;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
v int;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
3.2.10.1.2. 数据类型
基础类型转换 参见 数据类型转换映射表
PLSQL内置数据类型
PLS_INTEGER --> PLS_INTEGER
BINARY_INTEGER --> BINARY_INTEGER
SIMPLE_INTEGER --> SIMPLE_INTEGER
表名.列%type --> 表名.列%type
表名%rowtype --> 表名%rowtype
3.2.10.1.3. 声明
语法
variable_name [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression]
示例
-- 转换前Oracle PL/SQL:
DECLARE
c_MAX_SALARY CONSTANT NUMBER := 10000;
c_DEFAULT_NAME CONSTANT VARCHAR2(50) := 'Default Name';
v11 NUMBER := 100;
v12 PLS_INTEGER DEFAULT 200;
v13 BINARY_INTEGER := -300;
v14 SIMPLE_INTEGER DEFAULT 400;
V15 INT;
v21 CHAR DEFAULT 'A';
v22 VARCHAR2(2) := 'BC';
v23 NCHAR DEFAULT N'中';
v24 NVARCHAR2(10) := N'中文测试';
v33 LONG := 'This is a long text';
v34 CLOB;
v35 BLOB;
v41 BOOLEAN DEFAULT TRUE
v51 DATE := TO_DATE('2025-03-26', 'YYYY-MM-DD');
v52 TIMESTAMP := TO_TIMESTAMP('2025-03-26 09:58:00', 'YYYY-MM-DD HH24:MI:SS')
v61 mytable_03252.salary%TYPE;
v62 mytable_03252%ROWTYPE;
BEGIN
NULL;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
c_MAX_SALARY CONSTANT NUMBER := 10000;
c_DEFAULT_NAME CONSTANT VARCHAR2(50) := 'Default Name';
v11 NUMBER := 100;
v12 PLS_INTEGER DEFAULT 200;
v13 BINARY_INTEGER := -300;
v14 SIMPLE_INTEGER DEFAULT 400;
V15 INT;
v21 CHAR DEFAULT 'A';
v22 VARCHAR2(2) := 'BC';
v23 NCHAR DEFAULT N'中';
v24 NVARCHAR2(10) := N'中文测试';
v33 LONG := 'This is a long text';
v34 CLOB;
v35 BLOB;
v41 BOOLEAN DEFAULT TRUE
v51 DATE := TO_DATE('2025-03-26', 'YYYY-MM-DD');
v52 TIMESTAMP := TO_TIMESTAMP('2025-03-26 09:58:00', 'YYYY-MM-DD HH24:MI:SS')
v61 mytable_03252.salary%TYPE;
v62 mytable_03252%ROWTYPE;
BEGIN
NULL;
END;
3.2.10.1.4. 条件控制语句
3.2.10.1.4.1. IF 语句
语法
IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
示例
-- 转换前Oracle PL/SQL:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
END;
3.2.10.1.4.2. CASE 语句
语法
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
示例
-- 转换前Oracle PL/SQL 匿名块:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
3.2.10.1.5. 循环控制语句
3.2.10.1.5.1. LOOP
LOOP
-- 循环体
EXIT WHEN condition;
END LOOP;
示例
-- 转换前Oracle PL/SQL 匿名块:
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter value in LOOP loop: ' || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter value in LOOP loop: ' || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;
3.2.10.1.5.2. FOR LOOP
FOR variable IN [REVERSE] lower_bound..upper_bound LOOP
-- 循环体
END LOOP;
示例
-- 转换前Oracle PL/SQL 匿名块:
DECLARE
first INTEGER := 1;
last INTEGER := 10;
high INTEGER := 100;
low INTEGER := 12;
BEGIN
FOR j IN -5..5 LOOP
DBMS_OUTPUT.PUT_LINE (j);
END LOOP;
FOR k IN REVERSE first..last LOOP
DBMS_OUTPUT.PUT_LINE (k);
END LOOP;
FOR step IN 0..(TRUNC(high/low) * 2) LOOP
DBMS_OUTPUT.PUT_LINE (step);
END LOOP;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
first INTEGER := 1;
last INTEGER := 10;
high INTEGER := 100;
low INTEGER := 12;
BEGIN
FOR j IN -5..5 LOOP
DBMS_OUTPUT.PUT_LINE (j);
END LOOP;
FOR k IN REVERSE first..last LOOP
DBMS_OUTPUT.PUT_LINE (k);
END LOOP;
FOR step IN 0..(TRUNC(high/low) * 2) LOOP
DBMS_OUTPUT.PUT_LINE (step);
END LOOP;
END;
3.2.10.1.5.3. WHILE LOOP
WHILE condition LOOP
-- 循环体
END LOOP;
示例
-- 转换前Oracle PL/SQL 匿名块:
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('Counter value in WHILE loop: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
-- 转换后Oceanbase-Oracle 匿名块:
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('Counter value in WHILE loop: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
3.2.10.1.6. 注释
单行注释和多行注释转换时,会被丢掉,不影响程序的执行。
3.2.10.1.7. 游标
描述
游标相关语法
语法
声明:
CURSOR cursor_name(argName type[%type][,...]) IS select_statement;
打开:
OPEN cur_name(arg [, ...]);
获取游标:
FETCH cur_name INTO variable [,...];
关闭游标:
CLOSE cursor_name;
示例
-- 转换前Oracle PL/SQL 游标使用:
DECLARE
-- 1. 声明游标
CURSOR cur_employees(p_salary NUMBER, p_name VARCHAR2(50) ) IS
SELECT employee_id, name, salary
FROM employees
WHERE salary > p_salary and name != p_name;
-- 定义变量存储游标数据
v_emp_id NUMBER;
v_emp_name VARCHAR2(50);
v_salary NUMBER;
BEGIN
-- 2. 打开游标
OPEN cur_employees(5000, 'Bob');
-- 3. 循环提取数据
LOOP
FETCH cur_employees INTO v_emp_id, v_emp_name, v_salary;
EXIT WHEN cur_employees%NOTFOUND; -- 游标属性判断是否结束
DBMS_OUTPUT.PUT_LINE(
'员工ID: ' || v_emp_id ||
', 姓名: ' || v_emp_name ||
', 薪资: ' || v_salary
);
END LOOP;
-- 4. 关闭游标
CLOSE cur_employees;
END;
-- 转换后Oceanbase-Oracle 游标使用:
DECLARE
-- 1. 声明游标
CURSOR cur_employees(p_salary NUMBER, p_name VARCHAR2(50) ) IS
SELECT employee_id, name, salary
FROM employees
WHERE salary > p_salary and name != p_name;
-- 定义变量存储游标数据
v_emp_id NUMBER;
v_emp_name VARCHAR2(50);
v_salary NUMBER;
BEGIN
-- 2. 打开游标
OPEN cur_employees(5000, 'Bob');
-- 3. 循环提取数据
LOOP
FETCH cur_employees INTO v_emp_id, v_emp_name, v_salary;
EXIT WHEN cur_employees%NOTFOUND; -- 游标属性判断是否结束
DBMS_OUTPUT.PUT_LINE(
'员工ID: ' || v_emp_id ||
', 姓名: ' || v_emp_name ||
', 薪资: ' || v_salary
);
END LOOP;
-- 4. 关闭游标
CLOSE cur_employees;
END;
3.2.10.1.8. 动态SQL
描述
动态SQL相关语法
语法
EXECUTE IMMEDIATE dynamic_sql_string
[INTO variable [,....]]
[USING bind_arg [,...]]
[RETURNING INTO variable [,...]];
示例
-- 转换前Oracle PL/SQL 动态SQL:
DECLARE
l_sql VARCHAR2(200);
l_emp_id NUMBER := 1;
l_name VARCHAR2(50);
BEGIN
l_sql := 'SELECT name FROM employees WHERE employee_id = :id';
-- 绑定变量并执行查询
EXECUTE IMMEDIATE l_sql INTO l_name USING l_emp_id;
DBMS_OUTPUT.PUT_LINE('员工姓名: ' || l_name);
END;
DECLARE
l_sql VARCHAR2(200);
l_emp_id NUMBER := 300;
l_name VARCHAR2(50);
BEGIN
l_sql := 'UPDATE employees SET salary = salary * 1.1 WHERE employee_id = :1 RETURNING name INTO :2';
-- 绑定输入和输出变量
EXECUTE IMMEDIATE l_sql USING l_emp_id RETURNING INTO l_name;
DBMS_OUTPUT.PUT_LINE('更新后的员工姓名: ' || l_name);
END;
-- 转换后Oceanbase-Oracle 动态SQL:
DECLARE
l_sql VARCHAR2(200);
l_emp_id NUMBER := 1;
l_name VARCHAR2(50);
BEGIN
l_sql := 'SELECT name FROM employees WHERE employee_id = :id';
-- 绑定变量并执行查询
EXECUTE IMMEDIATE l_sql INTO l_name USING l_emp_id;
DBMS_OUTPUT.PUT_LINE('员工姓名: ' || l_name);
END;
DECLARE
l_sql VARCHAR2(200);
l_emp_id NUMBER := 300;
l_name VARCHAR2(50);
BEGIN
l_sql := 'UPDATE employees SET salary = salary * 1.1 WHERE employee_id = :1 RETURNING name INTO :2';
-- 绑定输入和输出变量
EXECUTE IMMEDIATE l_sql USING l_emp_id RETURNING INTO l_name;
DBMS_OUTPUT.PUT_LINE('更新后的员工姓名: ' || l_name);
END;
3.2.10.1.9. 异常
描述
异常相关语法
语法
plsql中异常处理:
EXCEPTION
WHEN ex_name_1 THEN statements_1 -- 异常处理程序
WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- 异常处理程序
WHEN OTHERS THEN statements_3 -- 异常处理程序
END;
自定义异常声明:
exception_name EXCEPTION;
自定义异常使用:
raise exception_name;
WHEN exception_name THEN ...
示例
-- 转换前Oracle PL/SQL 动态SQL:
DECLARE
-- 自定义异常
custom_exception EXCEPTION;
BEGIN
-- 显式抛出异常
RAISE custom_exception;
EXCEPTION
WHEN custom_exception THEN
DBMS_OUTPUT.PUT_LINE('捕获自定义异常: 业务逻辑错误');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其他未知错误: ' || SQLERRM);
END;
-- 转换后Oceanbase-Oracle 动态SQL:
DECLARE
-- 自定义异常
custom_exception EXCEPTION;
BEGIN
-- 显式抛出异常
RAISE custom_exception;
EXCEPTION
WHEN custom_exception THEN
DBMS_OUTPUT.PUT_LINE('捕获自定义异常: 业务逻辑错误');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其他未知错误: ' || SQLERRM);
END;
3.2.10.1.10. 创建函数
描述
定义一个新函数
语法
CREATE [OR REPLACE] FUNCTION function_name (argname IN|OUT|IN OUT datatype [, ...])
RETURN rettype
IS
-- 声明部分 (可选)
BEGIN
-- 执行部分: 过程及 SQL 语句,即程序的主要部分
[EXCEPTION]
-- 异常处理部分: 错误处理
END;
示例
-- 转换前Oracle PL/SQL 函数:
CREATE FUNCTION process_data (
in_val IN VARCHAR2,
out_val OUT NUMBER,
inout_val IN OUT DATE
) RETURN BOOLEAN IS
BEGIN
out_val := LENGTH(in_val);
inout_val := '20221212';
RETURN TRUE;
END;
-- 转换后Oceanbase-Oracle 函数:
CREATE FUNCTION process_data (
in_val IN VARCHAR2,
out_val OUT NUMBER,
inout_val IN OUT DATE
) RETURN BOOLEAN IS
BEGIN
out_val := LENGTH(in_val);
inout_val := '20221212';
RETURN TRUE;
END;
3.2.10.1.11. 创建存储过程
描述
定义一个新存储过程
语法
CREATE [OR REPLACE] PROCEDURE procedure_name (argname IN|OUT|IN OUT datatype [, ...])
IS
-- 声明部分 (可选)
BEGIN
-- 执行部分: 过程及 SQL 语句,即程序的主要部分
[EXCEPTION]
-- 异常处理部分: 错误处理
END;
示例
-- 转换前Oracle PL/SQL 存储过程:
CREATE OR REPLACE PROCEDURE calculate_stats (
p_input IN NUMBER, -- 输入参数
p_output OUT NUMBER, -- 输出参数
p_inout IN OUT VARCHAR2 -- 输入输出参数
) IS
BEGIN
p_output := p_input * 2; -- 修改输出参数
p_inout := UPPER(p_inout); -- 修改输入输出参数
END calculate_stats;
-- 转换后Oceanbase-Oracle 存储过程:
CREATE OR REPLACE PROCEDURE calculate_stats (
p_input IN NUMBER, -- 输入参数
p_output OUT NUMBER, -- 输出参数
p_inout IN OUT VARCHAR2 -- 输入输出参数
) IS
BEGIN
p_output := p_input * 2; -- 修改输出参数
p_inout := UPPER(p_inout); -- 修改输入输出参数
END calculate_stats;
3.2.10.1.12. 创建触发器
描述
定义一个新触发器
语法
CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [[ OF column_name [, ... ] ]| DELETE }
ON table_name
[ REFERENCING {OLD [AS] old | NEW [AS] new ]
[FOR EACH ROW ]
[ WHEN condition ]
[DECLARE]
-- 声明部分 (可选)
BEGIN
-- 执行部分: 过程及 SQL 语句,即程序的主要部分
[EXCEPTION]
-- 异常处理部分: 错误处理
END;
示例
-- 转换前Oracle PL/SQL 触发器:
CREATE OR REPLACE TRIGGER trg_emp_salary_limit
BEFORE UPDATE OF salary ON employees
REFERENCING OLD AS prev NEW AS curr
FOR EACH ROW
WHEN (curr.salary > prev.salary * 1.5) -- 使用别名
DECLARE
errmsg VARCHAR2(10) := '薪资涨幅超过50%';
BEGIN
RAISE_APPLICATION_ERROR(-20002, errmsg);
END;
-- 转换后Oceanbase-Oracle 触发器:
CREATE OR REPLACE TRIGGER trg_emp_salary_limit
BEFORE UPDATE OF salary ON employees
REFERENCING OLD AS prev NEW AS curr
FOR EACH ROW
WHEN (curr.salary > prev.salary * 1.5) -- 使用别名
DECLARE
errmsg VARCHAR2(10) := '薪资涨幅超过50%';
BEGIN
RAISE_APPLICATION_ERROR(-20002, errmsg);
END;
3.2.10.1.13. 创建包
3.2.10.1.13.1. 创建包规范
描述
定义一个包规范
语法
CREATE [ OR REPLACE ] PACKAGE package_name AS
item_list[, item_list ...]
END [package_name];
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
function_declaration:
FUNCTION function_name [(parameter_list)] RETURN datatype;
procedure_declaration:
PROCEDURE procedure_name [(parameter_list)]
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration:
CURSOR name [(parameter_list)] IS select_statement;
item_declaration:
variable_declaration
record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN datatype ];
variable_declaration:
varname [CONSTANT] datatype [ [ NOT NULL ] := expr ]
示例
-- 转换前Oracle PL/SQL 包规范:
CREATE OR REPLACE PACKAGE emp_pkg IS
-- 过程声明
PROCEDURE hire_employee (
p_name IN VARCHAR2,
p_salary IN NUMBER
);
-- 函数声明
FUNCTION get_employee_count RETURN NUMBER;
END emp_pkg;
-- 转换后Oceanbase-Oracle 包规范:
CREATE OR REPLACE PACKAGE emp_pkg IS
-- 过程声明
PROCEDURE hire_employee (
p_name IN VARCHAR2,
p_salary IN NUMBER
);
-- 函数声明
FUNCTION get_employee_count RETURN NUMBER;
END emp_pkg;
3.2.10.1.13.2. 创建包体
描述
定义一个包体
语法
CREATE [ OR REPLACE ] PACKAGE BODY package_name AS
item_list[, item_list ...]
END [package_name];
item_list:
[
function_definition |
procedure_definition |
type_definition |
cursor_declaration |
item_declaration
]
function_definition:
FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype [IS | AS]
[declare_section] body;
procedure_definition:
PROCEDURE procedure_name [(parameter_declaration[, ...])] [IS | AS]
[declare_section] body;
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration:
CURSOR name [(parameter_list)] IS select_statement;
item_declaration:
variable_declaration
record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN datatype ];
variable_declaration:
varname [CONSTANT] datatype [ [ NOT NULL ] := expr ]
示例
-- 转换前Oracle PL/SQL 包体:
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
-- 私有变量(仅包体内可见)
v_total_employees NUMBER := 0;
-- 实现过程
PROCEDURE hire_employee (
p_name IN VARCHAR2,
p_salary IN NUMBER
) IS
BEGIN
INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
v_total_employees := v_total_employees + 1;
END hire_employee;
-- 实现函数
FUNCTION get_employee_count RETURN NUMBER IS
BEGIN
RETURN v_total_employees;
END get_employee_count;
END emp_pkg;
-- 转换后Oceanbase-Oracle 包体:
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
-- 私有变量(仅包体内可见)
v_total_employees NUMBER := 0;
-- 实现过程
PROCEDURE hire_employee (
p_name IN VARCHAR2,
p_salary IN NUMBER
) IS
BEGIN
INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
v_total_employees := v_total_employees + 1;
END hire_employee;
-- 实现函数
FUNCTION get_employee_count RETURN NUMBER IS
BEGIN
RETURN v_total_employees;
END get_employee_count;
END emp_pkg;