select c_evt_src_id,c_type_id,c_big_class_id,c_small_class_id,c_title,c_position,c_event_desc,c_send_from,c_send_to,c_send_body,c_feedback_tel,c_feedback_status,c_last_prodept_id_x
FROM dbo.t_um_evt_act_reply A,dbo.t_um_event B
WHERE
A.c_evt_id = B.c_id
and A.c_send_type = 1
AND B.c_status = 1
AND B.c_db_status = 0
AND B.c_db_create_date between '2009-02-01 00:00:00.000' AND '2009-02-28 23:59:59.000'
and b.c_big_class_id in (select c_id from dbo.t_um_big_class where c_type_id ='b40885c3-1f48-48dc-ae1e-001209c265f9' )
这是存储过程里的代码. 传过一些参数过来 Stp,Sta,DbSta,Sdate,Edate,Ctypeid
如果参数为空就不加入where条件里if Stp is not null then
|' and A.c_send_type=1'
is Ctypeid is not null then
|' and b.c_big_class_id in (select c_id from dbo.t_um_big_class where c_type_id ='|''''CtypeID'''''|')'最后就形成了上面的SQL 传递给Oracle执行 很显然没有绊定变量的.
象这种动态生成SQL 语句如何绊定变量的.?
l_empno NUMBER := 7782;
l_ename VARCHAR2(20);
l_sqlstr VARCHAR2(1000) := 'SELECT ename FROM emp WHERE empno = :1';
begin
execute immediate l_sqlstr INTO l_ename USING l_empno;
DBMS_OUTPUT.PUT_LINE(l_ename);
end;
v_sql varchar2(2000);
begin
v_sql := ' select c_evt_src_id,c_type_id,c_big_class_id,c_small_class_id,c_title,c_position,c_event_desc,c_send_from,c_send_to,c_send_body,c_feedback_tel,c_feedback_status,c_last_prodept_id_x
FROM dbo.t_um_evt_act_reply A,dbo.t_um_event B
WHERE A.c_evt_id = B.c_id ';
if stp is not null then
v_sql := v_sql || ' and a.c_send_type = ''' || stp || '''';
end if;
if sta is not null then
v_sql := v_sql || ' and b.c_status = ''' || sta || '''';
end if;
if dbsta is not null then
v_sql := v_sql || ' and b.c_db_status = ''' || dbsta || '''';
end if;
if sdate is not null and edate is not null then
v_sql := v_sql || ' and b.c_db_status bwtween ''' || sdate || '' ||
' and ''' || edate || '''';
end if; for rec_1 in v_sql loop
null;
end loop;
end;