本帖最后由 muzigui_cu 于 2013-2-5 22:56 编辑
create or replace procedure proc_countrecord(v_time varchar2)
is
v_sql varchar2(1024);
begin
v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")='||v_time;
execute immediate v_sql using v_time;
end;
/
SQL> create or replace procedure proc_countrecord(v_time varchar2)
2is
3v_sql varchar2(1024);
begin
45v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")='||v_time;
6execute immediate v_sql using v_time;
end;
/78
Procedure created.
SQL> exec proc_countrecord('13020510')
BEGIN proc_countrecord('13020510'); END;
*
ERROR at line 1:
ORA-00904: "YYMMDDHH24": invalid identifier
ORA-06512: at "TEST.PROC_COUNTRECORD", line 6
ORA-06512: at line 1
绑定变量要用占位符：
v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")=:v_time';
另外既然有输出，要带INTO:
execute immediate v_sql INTO V_TASK_ID using v_time;
你必需保证这个SELECT不会返回多行，否则得用BULK COLLECT INTO。
newkid 发表于 2013-2-6 00:21
绑定变量要用占位符：
v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")=:v_time';
...
谢谢，修改如下，执行还是有问题
create or replace procedure proc_countrecord(
v_time varchar2)as
type v_id is table of varchar2(20);
v_task_id v_id;
v_sql varchar2(512);
begin
v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")=:v_time';
execute immediate v_sql bulk collect into v_task_id using v_time;
end;
/
SQL> SQL> exec proc_countrecord('13020510')
BEGIN proc_countrecord('13020510'); END;
*
ERROR at line 1:
ORA-00904: "YYMMDDHH24": invalid identifier
ORA-06512: at "TEST.PROC_COUNTRECORD", line 8
ORA-06512: at line 1
zhjd4839 发表于 2013-2-6 18:15
to_char(end_time,"YYMMDDHH24")这个怎么能用双引号呢？是单引号的
v_sql:='select task_id from b where to_char(end_time,"YYMMDDHH24")=:v_time';
这个是动态SQL的转义，应该是对的吧，改成单引号编译都过不了
SQL> create or replace procedure proc_countrecord(
2v_time varchar2)as
3type v_id is table of varchar2(20);
v_task_id v_id;
45v_sql varchar2(512);
6begin
v_sql:='select task_id from b where to_char(end_time,'YYMMDDHH24')=:v_time';
78execute immediate v_sql bulk collect into v_task_id using v_time;
9end;
/ 10
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE PROC_COUNTRECORD:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/70 PLS-00103: Encountered the symbol "YYMMDDHH24" when expecting
one of the following:
* & = - + ;at in is mod remainder not rem
or != or ~= >=and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
The symbol "* was inserted before "YYMMDDHH24" to continue.
两个单引号，不是直接双引号，另外最好不要对列用函数，改写成> andshow error
Errors for PROCEDURE PROC_COUNTRECORD:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/1PL/SQL: Statement ignored
9/1PLS-00306: wrong number or types of arguments in call to
'PUT_LINE'
