LightDB23.2新特性Oracle模式支持plsql execute immediate语法

EXECUTE IMMEDIATE 是 Oracle PL/SQL 中用于动态执行 SQL 语句或 PL/SQL 块的语法。它主要用于在运行时构造和执行 SQL 查询，这在静态 SQL 无法满足需求时非常有用。EXECUTE IMMEDIATE 与 PostgreSQL 的 EXECUTE 语句有一定相似性，但它们在具体使用和语法上有所不同。

SQL> CREATE TABLE employees (
  2      id number PRIMARY KEY,
  3      name VARCHAR(50)
  4  );
Table created.
INSERT INTO employees VALUES (1,'John');

INSERT INTO employees VALUES (2,'Jane');

SQL> INSERT INTO employees VALUES (3,'Alice');

SQL> commit;
Commit complete.

Oracle中返回的结果

DECLARE
  2     sql_query VARCHAR2(100);
  3     emp_name  VARCHAR2(50);
  4     emp_id    NUMBER := 101;
  5  BEGIN
  6     sql_query := 'SELECT name FROM employees WHERE id = :id';
  7     EXECUTE IMMEDIATE sql_query INTO emp_name USING emp_id;
  8     
  9     DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

-- 执行
SQL> set serveroutput on 
DECLARE
   sql_query VARCHAR2(100);
SQL>   2    3     emp_name  VARCHAR2(50);
  4     emp_id    NUMBER := 1;
  5  BEGIN
  6     sql_query := 'SELECT name FROM employees WHERE id = :id';
  7     EXECUTE IMMEDIATE sql_query INTO emp_name USING emp_id;
  8     
  9     DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
 10  END;
 11  /
Employee name is: John

PL/SQL procedure successfully completed.

LightDB Oracle模式中测试结果

创建测试表

test@test=# CREATE TABLE employees (
test@test(#     id number PRIMARY KEY,
test@test(#     name VARCHAR(50)
test@test(# );
CREATE TABLE
test@test=# INSERT INTO employees VALUES (1,'John');
INSERT 0 1
test@test=# INSERT INTO employees VALUES (2,'Jane');
INSERT 0 1
test@test=# INSERT INTO employees VALUES (3,'Alice');
INSERT 0 1
test@test=# select * from employees;
 id | name  
----+-------
  1 | John
  2 | Jane
  3 | Alice
(3 rows)
## 1、使用plpgsql语法
test@test=# DECLARE
test@test$#    sql_query VARCHAR2(100);
test@test$#    emp_name  VARCHAR2(50);
test@test$#    emp_id    NUMBER := 101;
test@test$# BEGIN
test@test$#    sql_query := 'SELECT name FROM employees WHERE id = :id';
test@test$#    EXECUTE IMMEDIATE sql_query INTO emp_name USING emp_id;
test@test$#    
test@test$#    DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
test@test$# END;
test@test$# /
ERROR:  query returned no rows
CONTEXT:  PL/oraSQL function inline_code_block line 7 at EXECUTE
test@test=# 


test@test=# CREATE OR REPLACE FUNCTION get_employee_name(p_employee_id INTEGER)
test@test-# RETURNS VARCHAR AS $$
test@test$# DECLARE
test@test$#     sql_query TEXT;
test@test$#     emp_name  VARCHAR;
test@test$# BEGIN
test@test$#     sql_query := 'SELECT name FROM employees WHERE id = $1';
test@test$#     EXECUTE sql_query INTO emp_name USING p_employee_id;
test@test$# 
test@test$#     RAISE NOTICE 'Employee name is: %', emp_name;
test@test$#     RETURN emp_name;
test@test$# END;
test@test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

test@test=# select get_employee_name(1) from dual;
NOTICE:  Employee name is: John
 get_employee_name 
-------------------
 John
(1 row)

2、使用plorasql语法

test@test=# SELECT dbms_output.serveroutput('true');
 serveroutput 
--------------
 
(1 row)

test@test=# DECLARE                                 
   sql_query VARCHAR2(100);
   emp_name  VARCHAR2(50);
   emp_id    NUMBER := 1;
BEGIN
   sql_query := 'SELECT name FROM employees WHERE id = :id';
   EXECUTE IMMEDIATE sql_query INTO emp_name USING emp_id;
   
   DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
END;
/
Employee name is: John
DO