过程中的创建视图中缺少末尾的单引号

Question

我有一个存储过程，其中需要将字符串参数传递给创建视图，我很难将字符串用单引号括起来

EXECUTE IMMEDIATE 
'CREATE VIEW view_Exec_Data as
Select * from Employees
where exec_id='''' ||To_NChar(EID)||'''';  --EID is input parameter and value will be 9DE4D0106D1F390EE0

以上查询生成为

EXECUTE IMMEDIATE 
'CREATE VIEW view_Exec_Data as
Select * from Employees
where exec_id='9DE4D0106D1F390EE0; 

结尾缺少一句引号，不确定我做错了什么。

Answer 1

我们缺少一些信息；例如，我想知道您为什么使用TO_NCHAR。。。你真的需要它吗？

下面是一个假设employees表如下所示的示例：

SQL> create table employees (exec_id varchar2(30), name varchar2(30));
Table created.
SQL> insert into employees values ('9DE4D0106D1F390EE0', 'Littlefoot');
1 row created.
SQL> select * From employees;
EXEC_ID                        NAME
------------------------------ ------------------------------
9DE4D0106D1F390EE0             Littlefoot

创建视图的过程。我建议不要那样做。你真的想拥有zillion视图吗？每个EXEC_ID有一个视图被用作参数？这样做的目的是什么？你为什么不简单的

select * from employees where exec_id = :par_eid;

不管怎样，现在开始吧：为了简化许多单引号，我使用了q-quoting mechanism。

SQL> create or replace procedure p_crv (par_eid in employees.exec_id%type)
2  is
3    l_str varchar2(200);
4  begin
5    l_str := q'[CREATE or replace VIEW view_Exec_Data as
6                Select * from Employees
7                where exec_id= to_nchar(']' || par_eid || q'[')]';
8
9    -- when using dynamic SQL, **ALWAYS** check whether command is properly written
10    dbms_output.put_line(l_str);
11
12    -- if it looks OK, then execute it
13    execute immediate l_str;
14  end;
15  /
Procedure created.
SQL> set serveroutput on
SQL> exec p_crv('9DE4D0106D1F390EE0');
CREATE or replace VIEW view_Exec_Data as
Select * from Employees
where exec_id= to_nchar('9DE4D0106D1F390EE0')
PL/SQL procedure successfully completed.
SQL> select * From view_exec_data;
EXEC_ID                        NAME
------------------------------ ------------------------------
9DE4D0106D1F390EE0             Littlefoot
SQL>

---

如果你不需要to_nchar，它会变得更简单：

SQL> create or replace procedure p_crv (par_eid in employees.exec_id%type)
2  is
3    l_str varchar2(200);
4  begin
5    l_str := q'[CREATE or replace VIEW view_Exec_Data as
6                Select * from Employees
7                where exec_id= ']' || par_eid || q'[']';
8
9    -- when using dynamic SQL, **ALWAYS** check whether command is properly written
10    dbms_output.put_line(l_str);
11
12    -- if it looks OK, then execute it
13    execute immediate l_str;
14  end;
15  /
Procedure created.
SQL> exec p_crv('9DE4D0106D1F390EE0');
CREATE or replace VIEW view_Exec_Data as
Select * from Employees
where exec_id= '9DE4D0106D1F390EE0'
PL/SQL procedure successfully completed.
SQL> select * From view_exec_data;
EXEC_ID                        NAME
------------------------------ ------------------------------
9DE4D0106D1F390EE0             Littlefoot
SQL>

Answer 2

您可以使用：

EXECUTE IMMEDIATE 
'CREATE VIEW view_Exec_Data as
Select * from Employees
where exec_id=To_NChar(''' || EID ||''')'; 
--                     ^ 3 (')       ^ 3 + 1 (')

更新

为我工作。见此：

SQL> set serverout on
SQL> declare
2  EID varchar2(100) := '9DE4D0106D1F390EE0';
3  begin
4  DBMS_OUTPUT.PUT_LINE('CREATE VIEW view_Exec_Data as
5        Select * from Employees
6              where exec_id=To_NChar(''' || EID ||''')');
7  end;
8  /
CREATE VIEW view_Exec_Data as
Select * from Employees
where
exec_id=To_NChar('9DE4D0106D1F390EE0')
PL/SQL procedure successfully completed.
SQL>