动态SQL的例子:
CREATE OR REPLACE PROCEDURE demo
(tname in varchar2,cname in varchar2,cvalue in varchar2) AS
cursor_name INTEGER;
rows_processed INTEGER;
ps_sql varchar2(200);
BEGIN
cursor_name := dbms_sql.open_cursor();
ps_sql := 'create table ...........';
dbms_sql.parse(cursor_name, ps_sql ,dbms_sql.v7);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
dbms_output.put_line(sqlcode||sqlerrm);
END;
/
CREATE OR REPLACE PROCEDURE demo
(tname in varchar2,cname in varchar2,cvalue in varchar2) AS
cursor_name INTEGER;
rows_processed INTEGER;
ps_sql varchar2(200);
BEGIN
cursor_name := dbms_sql.open_cursor();
ps_sql := 'create table ...........';
dbms_sql.parse(cursor_name, ps_sql ,dbms_sql.v7);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
dbms_output.put_line(sqlcode||sqlerrm);
END;
/
我知道动态SQL的编写方法。
问题是怎么用游标,我要把动态查询结果保存到游标中。当我用游标的时候,parse说我的SQL语句不对。
游标可以任意拼接,没有问题的。我经常用。
http://www.csdn.net/Expert/TopicView1.asp?id=338638
其中,myCur是我在包头定义的一个Ref Cursor;
其他的,基本上都能看懂吧。 procedure GetCur (str in varchar2,aa in out myCur)
0A IS
s Varchar2(500);
v_cursorID Integer;
0A v_dummy Integer;
begin v_cursorID := DBMS_SQL.Open_Cursor;
s :='open aa for select rownum,UserName,Userid from PERSONINFO where userid = :strP';
DBMS_SQL.Parse (v_CursorID, s, DBMS_SQL.Native);
0A
DBMS_SQL.Bind_Variable(v_CursorID, ':strP', str);
v_dummy:= Dbms_Sql.Execute(v_CursorID); DBMS_SQL.Close_Cursor(v_CursorID);
Dbms_Output.Put_Line(aaRowcount); /*
EXCEPTION
when other then
DBMS_SQL.close_cursor(V_cursorID);
dbms_output.put_line('error');
*/
end Getcur;
反正,里面没有OA就是了。
( ps_clno in varchar2,
ps_phytable_s in varchar2,
ps_phytablemc_s in varchar2,
ps_pkey_s in varchar2,
ps_gxsjzd_s in varchar2,
ps_phytable_d in varchar2,
ps_phytablemc_d in varchar2,
ps_pkey_d in varchar2,
ps_gxsjzd_d in varchar2,
ps_wherecond in varchar2,
ps_whereconddesc in varchar2,
ps_wavefile in varchar2,
ps_tbdwbh in varchar2,
ps_sour_dest in varchar2,
ps_gxsjzd_type in varchar2,
ps_xh in varchar2
)
as
ps_error varchar2(2000);
ls_wherecond varchar2(1000);
ls_desttable varchar2(30);
ls_ph varchar2(20);
ls_temp varchar2(2000);
ls_sourls_t varchar2(30);
ls_sourzl_v varchar2(30);
lcu_cursor number;
lcu_cursor1 number;
lcu_cursor2 number;
li_return number;
ls_pkey varchar2(200);
ld_gxsj date;
ls_gxsj varchar2(30);
ln_count number;
BEGIN
ln_count := 0;
ls_sourls_t := 'T'||upper(ps_phytable_s||'_'||ps_phytable_d||'_'||ps_xh);
ls_sourzl_v := 'Z'||upper(ps_phytable_s||'_'||ps_phytable_d||'_'||ps_xh);
lcu_cursor1 := dbms_sql.open_cursor;
if ps_gxsjzd_type='0' then
ls_temp := 'insert into '||ls_sourls_t||' values(:ls_pkey,:ld_gxsj)';
else
ls_temp := 'insert into '||ls_sourls_t||' values(:ls_pkey,:ls_gxsj)';
end if;
dbms_sql.parse(lcu_cursor1,ls_temp,dbms_sql.v7);
lcu_cursor2 := dbms_sql.open_cursor;
ls_temp := 'delete from '||ls_sourls_t||' where s_pkey = :ls_pkey';
dbms_sql.parse(lcu_cursor2,ls_temp,dbms_sql.v7);
ls_temp := 'select '||ps_pkey_s||','||ps_gxsjzd_s||' from '||ls_sourzl_v;
lcu_cursor := dbms_sql.open_cursor;
dbms_sql.parse(lcu_cursor,ls_temp,dbms_sql.v7);
dbms_sql.define_column(lcu_cursor,1,ls_pkey,200);
if ps_gxsjzd_type='0' then
dbms_sql.define_column(lcu_cursor,2,ld_gxsj);
else
dbms_sql.define_column(lcu_cursor,2,ls_gxsj,30);
end if;
li_return := dbms_sql.execute(lcu_cursor);
loop
if dbms_sql.fetch_rows(lcu_cursor) = 0 then
exit;
else
begin
ln_count := ln_count + 1;
if ln_count > 250 then
commit;
ln_count := 0;
end if;
dbms_sql.column_value(lcu_cursor,1,ls_pkey);
if ps_gxsjzd_type='0' then
dbms_sql.column_value(lcu_cursor,2,ld_gxsj);
else
dbms_sql.column_value(lcu_cursor,2,ls_gxsj);
end if;
DBMS_SQL.BIND_VARIABLE(lcu_cursor2,'ls_pkey',ls_pkey);
li_return := dbms_sql.execute(lcu_cursor2);
DBMS_SQL.BIND_VARIABLE(lcu_cursor1,'ls_pkey',ls_pkey);
if ps_gxsjzd_type='0' then
DBMS_SQL.BIND_VARIABLE(lcu_cursor1,'ld_gxsj',ld_gxsj);
else
DBMS_SQL.BIND_VARIABLE(lcu_cursor1,'ls_gxsj',ls_gxsj);
end if;
li_return := dbms_sql.execute(lcu_cursor1);
exception when others then
rollback;
end ;
end if;
end loop;
commit;
dbms_sql.close_cursor(lcu_cursor);
dbms_sql.close_cursor(lcu_cursor1);
dbms_sql.close_cursor(lcu_cursor2);
exception when others then
if dbms_sql.is_open(lcu_cursor) then
dbms_sql.close_cursor(lcu_cursor);
end if;
if dbms_sql.is_open(lcu_cursor1) then
dbms_sql.close_cursor(lcu_cursor1);
end if;
if dbms_sql.is_open(lcu_cursor2) then
dbms_sql.close_cursor(lcu_cursor2);
end if;
rollback;
ps_error := substrb(sqlerrm,1,2000);
。。
end;
/
不过,
我的过程,是要把一个结果集放到游标里传出去的。如果,你有时间,能不能帮我测试一下。
只要把动态的组合查询(.. And ..)的结果放到Cursor中就行了。
不过,
我的过程,是要把一个结果集放到游标里传出去的。如果,你有时间,能不能帮我测试一下。
只要把动态的组合查询(.. And ..)的结果放到Cursor中就行了。
http://www.csdn.net/Expert/TopicView1.asp?id=861300
http://www.csdn.net/Expert/TopicView1.asp?id=861302
http://www.csdn.net/Expert/TopicView1.asp?id=861289顺便再问你一下,用动态游标的时候,怎么在SQL字符串中包含%,进行模糊查询,我加进%,提示说非法字符。