declare v_sql varchar2(1000);
begin
v_sql:='insert into tablename1 select .... from tablename2 group by '|| groupby ;
execute immediate v_sql;
end;
begin
v_sql:='insert into tablename1 select .... from tablename2 group by '|| groupby ;
execute immediate v_sql;
end;
execute immediate
另外,insert into table可以嗎?我沒試過。
用execute immediate可以嗎?我說的是select語句。
還有別的辦法嗎?
不能用insert into 變量,我查出來的結果不止一條數據
---------------------------------------------------
那就用cursorcreate or replace package types
as
type rc is ref cursor;
end;
/CREATE OR REPLACE PROCEDURE get_groupcol_value
( v_colname1 IN varchar2(20),
v_colname2 IN varchar2(20),
v_colname3 IN varchar2(20),
v_colsum IN varchar2(20),
v_groupcol IN varchar2(30),
p_cursor IN OUT types.rc)
IS
begin
v_sql:='select ' || v_colname1 || ',' || v_colname2 || ',sum(' || col3
|| v_colsum || ') group by '|| v_groupcol ;
open p_cursour for v_sql; EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
End;
/sqlplus中调用:
variable c refcursor
exec get_groupcol_value('col1','col2','col3','col1,col2',:c);
print c;
v_sql varchar2(200);