sql - 内部变量立即执行

Question

我在 pl sql 中使用以下过程

CREATE OR REPLACE "MY_PROCEDURE" (v_id number, v_name varchar2) AS
     BEGIN
        EXECUTE IMMEDIATE ' CREATE TABLE T_TEMPO ( t_id number , t_name varchar2(250) , t_value number )';
        EXECUTE IMMEDIATE 'INSERT INTO T_TEMPO (t_id , t_name, t_value)
                           SELECT id , name , value
                           from TABLE_2
                           where TABLE_2.id = || v_id ||
                           AND TABLE_2.name = || v_name || ';

但这不起作用我有一个缺少表达式的错误，我想知道它不能评估立即执行中变量 v_id 和 v_name 的值。有人可以帮忙吗？

Answer 1

您需要引用这些值(必要时使用引号):

create or replace procedure my_procedure
    ( v_id number
    , v_name varchar2 )
as
begin
    execute immediate 'CREATE TABLE T_TEMPO (t_id number, t_name varchar2(250), t_value number )';
    execute immediate 'INSERT INTO T_TEMPO (t_id, t_name, t_value)
                       SELECT id, name, value
                       FROM table_2
                       WHERE table_2.id = ' || v_id ||
                     ' AND table_2.name = ''' || v_name || '''';
end my_procedure;

或者(通常更好)将它们作为绑定(bind)变量传递:

create or replace procedure my_procedure
    ( v_id number
    , v_name varchar2 )
as
begin
    execute immediate 'CREATE TABLE T_TEMPO (t_id number, t_name varchar2(250), t_value number )';
    execute immediate 'INSERT INTO T_TEMPO (t_id, t_name, t_value)
                       SELECT id, name, value
                       FROM table_2
                       WHERE table_2.id = :id
                       AND table_2.name = :name' using v_id, v_name;
end my_procedure;

顺便说一句，我会为变量使用 v 前缀，为参数使用 p。