create or replace procedure test_jay_01
as
sql_txt varchar2(1000);
sql_txt_columns varchar2(1000);
sql_txt_columns_update varchar2(1000);
sql_txt_columns_insert varchar2(1000);begin
sql_txt_columns :='STUDENT_NEW_JAY.name,STUDENT_NEW_JAY.age,STUDENT_NEW_JAY.class';
sql_txt_columns_update := 'STUDENT_NEW_JAY.age=STUDENT_JAY.age,STUDENT_NEW_JAY.class=STUDENT_JAY.class';
sql_txt_columns_insert := 'STUDENT_JAY.name,STUDENT_JAY.age,STUDENT_JAY.class'; sql_txt := 'merge into STUDENT_NEW_JAY using STUDENT_JAY on(STUDENT_NEW_JAY.name=STUDENT_JAY.name)
when matched then update set ' || sql_txt_columns_update|| ' when not matched then insert
(' || sql_txt_columns ||') values(' || sql_txt_columns_insert || ') where STUDENT_JAY.name=张三';
execute immediate sql_txt;
commit;
exception
when others then
rollback;
end;
我使用merge into插入数据时想加入限制条件,这里比方说name为张三的记录,这个条件应该怎么加呢,我上面的写法是不对的。
as
sql_txt varchar2(1000);
sql_txt_columns varchar2(1000);
sql_txt_columns_update varchar2(1000);
sql_txt_columns_insert varchar2(1000);begin
sql_txt_columns :='STUDENT_NEW_JAY.name,STUDENT_NEW_JAY.age,STUDENT_NEW_JAY.class';
sql_txt_columns_update := 'STUDENT_NEW_JAY.age=STUDENT_JAY.age,STUDENT_NEW_JAY.class=STUDENT_JAY.class';
sql_txt_columns_insert := 'STUDENT_JAY.name,STUDENT_JAY.age,STUDENT_JAY.class'; sql_txt := 'merge into STUDENT_NEW_JAY using STUDENT_JAY on(STUDENT_NEW_JAY.name=STUDENT_JAY.name)
when matched then update set ' || sql_txt_columns_update|| ' when not matched then insert
(' || sql_txt_columns ||') values(' || sql_txt_columns_insert || ') where STUDENT_JAY.name=张三';
execute immediate sql_txt;
commit;
exception
when others then
rollback;
end;
我使用merge into插入数据时想加入限制条件,这里比方说name为张三的记录,这个条件应该怎么加呢,我上面的写法是不对的。
begin
v_name := '张三';
') where STUDENT_JAY.name=:p';
execute immediate sql_txt using v_name;
where STUDENT_JAY.name=''张三''';
比如说:
where 当前时间-3天<=STUDENT_JAY.createtime<=当前时间+3天,
好像不能直接写sysdate-3
可以直接写sysdate-3, sysdate+3,意思就是当前时间后3天,前3天。
sys@ORCL1> select count(*) from emp where hiredate<(sysdate-3); COUNT(*)
----------
14
为啥不能直接写???是你的条件有问题。where STUDENT_JAY.createtime>=sysdate-3 and STUDENT_JAY.createtime<=sysdate+3