create or replace procedure P_DZ_DEL_MYEUSERNUM
(
i_sql in varchar2
)
is
V_sql varchar(100);
V_where varchar(150);
i number;
j number;
begin
i := 1;
j := 0;
select count(*) into j from t_dz_mye_usersinfo t
where to_char(t.insertdate,'yyyymmdd') < to_char(sysdate-1,'yyyymmdd');
V_sql := '';
V_sql := 'delete t_dz_mye_usersinfo t ';
V_where := 'where to_char(t.insertdate,''yyyymmdd'') < to_char(sysdate-1,''yyyymmdd'');';
dbms_output.put_line(V_sql);
V_sql := V_sql || V_where;
loop
exit when i >j;
execute immediate V_sql;
commit;
i := i + 1000;
end loop;
end;代码如上:查了一些关于变量绑定的例子,都没有搞定,请大家帮忙看看,时间紧,今天要搞定的,谢谢!
(
i_sql in varchar2
)
is
V_sql varchar(100);
V_where varchar(150);
i number;
j number;
begin
i := 1;
j := 0;
select count(*) into j from t_dz_mye_usersinfo t
where to_char(t.insertdate,'yyyymmdd') < to_char(sysdate-1,'yyyymmdd');
V_sql := '';
V_sql := 'delete t_dz_mye_usersinfo t ';
V_where := 'where to_char(t.insertdate,''yyyymmdd'') < to_char(sysdate-1,''yyyymmdd'');';
dbms_output.put_line(V_sql);
V_sql := V_sql || V_where;
loop
exit when i >j;
execute immediate V_sql;
commit;
i := i + 1000;
end loop;
end;代码如上:查了一些关于变量绑定的例子,都没有搞定,请大家帮忙看看,时间紧,今天要搞定的,谢谢!
一次delete语句就可以了,循环执行是什么意思?
把问题要表述明白才行。
(by the way:还有没有其它更好的办法)
v_sql 里面到底用了什么变量
'UPDATE t_store_in_instructions SET instruction_status = :p1 WHERE instruction_no = :p2 AND instruction_status = :p3'; EXECUTE IMMEDIATE v_sql
USING c_finish_status, p_ins, c_default_status;这样就使动态sql,里面绑定了3个变量
后来发现因为动态sql作为后解析,这样反而加大了系统负担
是不是该做个批解析 forall什么?
'UPDATE t_store_in_instructions SET instruction_status = :p1 WHERE instruction_no = :p2 AND instruction_status = :p3'; EXECUTE IMMEDIATE v_sql
USING c_finish_status, p_ins, c_default_status;p1,p2,p3就是绑定变量
具体的值对应变量c_finish_status, p_ins, c_default_status
execute immediate v_sql using p_a;这样就用到了绑定变量
(
i_sql in varchar2
)
is
V_sql varchar(100);
V_where varchar(150);
V_where2 varchar(150);
V_date varchar(100);
i number;
j number;
begin
i := 1;
j := 0;
V_date := 'to_char(sysdate-1,''yyyymmdd'')';
select count(*) into j from t_dz_mye_usersinfo t
where to_char(t.insertdate,'yyyymmdd') < to_char(sysdate-1,'yyyymmdd');
V_sql := '';
V_sql := 'delete t_dz_mye_usersinfo t ';
V_where := 'where to_char(t.insertdate,''yyyymmdd'') < :a;';
-- V_where2:= V_where || =f1;
dbms_output.put_line(V_sql);
V_sql := V_sql || V_where;
loop
exit when i >j;
-- execute immediate 'insert into t_dz_mye_usersinfo(usernum) values(:a)' using i;
execute immediate V_sql using V_date;
execute immediate V_sql;
commit;
i := i + 1000;
end loop;
end;感谢,按绑变量的写法改了,但死活它过不去
只是test过不了!
---
to_char(sysdate-1,'yyyymmdd')
你的v_date得知有问题吧
DECLARE
v_date DATE;
v_sql VARCHAR2(200);
v_count INTEGER;
BEGIN
v_sql:='select count(*) from t_store_out_instructions where instruction_date <=:p1';
EXECUTE IMMEDIATE v_sql INTO v_count USING SYSDATE-1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_count));
END;
结果(完全符合要求)
--65
(
i_sql in varchar2
)
is
V_sql varchar(100);
V_where varchar(150);
V_where2 varchar(150);
V_date varchar(100);
V_date2 varchar(100);
V_count varchar(8);
i number;
j number;
begin
i := 1;
j := 0;
V_date := 'to_char(sysdate-1,''yyyymmdd'')';
V_date2 := to_char(sysdate-1,'yyyymmdd');
select count(*) into j from t_dz_mye_usersinfo2 t
where to_char(t.ab,'yyyymmdd') < to_char(sysdate-1,'yyyymmdd');
V_sql := '';
V_sql := 'delete t_dz_mye_usersinfo2 t ';
V_where := 'where to_char(t.ab,''yyyymmdd'') < :a';
-- V_where2:= V_where || =f1;
dbms_output.put_line(V_sql);
V_sql := V_sql || V_where;
loop
exit when i >j;
-- execute immediate 'insert into t_dz_mye_usersinfo(usernum) values(:a)' using i;
execute immediate V_sql using V_date2;
-- execute immediate V_sql;
commit;
i := i + 1000;
end loop;
end;我那个:a是多了个分号,而V_date,V_date2都可以作为变量使用,再次感谢各位!