1 用 update 多字段批量更新
update dog qq set(b,c)=
(select b,c from cat tt where tt.a=qq.a)
上面的括号很重要,不能省略。
如果是t-sql就不能这样写。需要有一个from dog的语句。相比来说麻烦了点儿
2 用目表生成新表并拷贝数据
create table horse as select * from dog
3 从一个表取数据插入到另一个表
insert into dog select * from cat
4 存储过程中执行一个ddl语句
dbms_utility.exec_ddl_statement('grant mx_admin_role to '||p_username);
5 执行一个动态的dml语句的方法
----------------------------
---将归档的明细数据copy到 mx_mxb_committed_t_200中
---参数说明:
--pd_id_list 产品id的列表 例如:'22,34,35,36,37,38,39,40'
----------------------------
procedure mx_copy_to_200
(
pd_id_list in varchar2
)
is
v_cursor number;
v_numrows number;
s varchar2(800);
s1 varchar2(250);
s2 varchar2(250);
begin
s:='insert into mxk_200 ';
s:= s || ' select q.*,'''','''','''','''' from mxk q ';
s:= s || ' where mxb_id in ';
s1:= 'select b.mxb_id from mx_pd_mxb_syn a,mxk b ';
s1:= s1 || ' where a.p_id in (' || pd_id_list || ') and a.p_lth_id=b.m_mxb_id';
s2:= 'select d.mxb_id from mx_pd_mxb_syn c,mxk_200 d ';
s2:= s2 || ' where c.p_id in (' || pd_id_list || ') and c.p_lth_id=d.m_mxb_id';
s:= s || '(' || s1 ||' minus ' || s2 || ')';
v_cursor:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor,s,DBMS_SQL.V7);
v_numrows:=DBMS_SQL.EXECUTE(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
end mx_copy_to_200;
---将归档的明细数据copy到 mx_mxb_committed_t_200中
---参数说明:
--pd_id_list 产品id的列表 例如:'22,34,35,36,37,38,39,40'
----------------------------
procedure mx_copy_to_200
(
pd_id_list in varchar2
)
is
v_cursor number;
v_numrows number;
s varchar2(800);
s1 varchar2(250);
s2 varchar2(250);
begin
s:='insert into mxk_200 ';
s:= s || ' select q.*,'''','''','''','''' from mxk q ';
s:= s || ' where mxb_id in ';
s1:= 'select b.mxb_id from mx_pd_mxb_syn a,mxk b ';
s1:= s1 || ' where a.p_id in (' || pd_id_list || ') and a.p_lth_id=b.m_mxb_id';
s2:= 'select d.mxb_id from mx_pd_mxb_syn c,mxk_200 d ';
s2:= s2 || ' where c.p_id in (' || pd_id_list || ') and c.p_lth_id=d.m_mxb_id';
s:= s || '(' || s1 ||' minus ' || s2 || ')';
v_cursor:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor,s,DBMS_SQL.V7);
v_numrows:=DBMS_SQL.EXECUTE(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
end mx_copy_to_200;