variables - 为什么用变量名替换其值导致 “' xxx'应该被声明为“错误”？

Question

我编写了以下PL/SQL块:

declare 
    v_name varchar2(20);
    v_sal integer;
begin
    EXECUTE IMMEDIATE ('CREATE TABLE EMP5 (name varchar2(20),sal integer,primary key(name))');
    dbms_output.put_line('Enter name : ');
    v_name:=&v_name;
    dbms_output.put_line('salary : ');
    v_sal :=&v_sal;
    insert into emp5 (name,sal) values (v_name,v_sal);
end;
/

然后我得到以下编译器输出:

Enter a valeur for v_name : dhia
old 6 : v_name:=&v_name;
new 6 : v_name:=dhia;
Enter a valeur for v_sal : 10
old 8 : v_sal :=v_&sal;
new 8 : v_sal :=10;
v_name:=dhia;
      *
ERREUR at line 6 :
ORA-06550: line 6, colon 7 :
PLS-00201: the identifier 'DHIA' should be declared
ORA-06550: line 6, colon 1 :
PL/SQL: Statement ignored
ORA-06550: line9, colon 13 :
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, colon 1 :
PL/SQL: SQL Statement ignored

我的问题是，为什么脚本中的变量“名称”被其值“dhia”替换为该错误，以及如何解决该错误？

Answer 1

您不应以这种方式使用变量，预期的行为是用其值替换它们，变量不应与列名相同，请尝试以下操作:

declare 
varName varchar2(20);
varSal integer;
sql_stmt varchar2(200);
begin
EXECUTE IMMEDIATE ('CREATE TABLE EMP5 (name varchar2(20),sal integer,primary key(name))');
dbms_output.put_line('Enter name : ');
varName :=&varName;
dbms_output.put_line('salary : ');
varSal :=&varSal;
sql_stmt := 'INSERT INTO EMP5 VALUES (:1, :2)';
EXECUTE IMMEDIATE sql_stmt USING varName, varSal; 
end;
/