存储过程如何传变量到like下

存储过程中执行如下DDL语句
create or replace procedure etl_test(v_com varchar2) is

v_spname varchar2(40);
com varchar2(40);

begin
com := v_com;
v_spname := 'create table COM as select * from TESTUSERS where USERNAME like ''||:1||''%';
execute immediate v_spname
using in com;

end;

执行的时候报错

SQL> create or replace procedure etl_test(p_var varchar2)
2 is
3 v_spname varchar2(400);
4 begin
5 v_spname := 'create table com as select * from emp where ename like ' || chr(39) || p_var || '%' || chr(39);
6 execute immediate v_spname;
7 end;
8 /

Procedure created

SQL> exec etl_test('A');

PL/SQL procedure successfully completed

SQL> select * from com;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

巴特西

存储过程如何传变量到like下

最新文章

热门文章