在存储过程里写动态SQL,定义一个变量v_str varchar2(3000);
传递的参数为:v_select,v_str1
在相应的位置写动态SQL:v_str := v_select||v_str1;
execute IMMEDIATE v_str1;注意:如果你的where id =1的值 是固定的,就可以用上面的语句写,如果不是(如ID=变量1)则可以这样写:execute immediate v_str1 using 变量1;
传递的参数为:v_select,v_str1
在相应的位置写动态SQL:v_str := v_select||v_str1;
execute IMMEDIATE v_str1;注意:如果你的where id =1的值 是固定的,就可以用上面的语句写,如果不是(如ID=变量1)则可以这样写:execute immediate v_str1 using 变量1;
---------- --------
1 aa
9 nn
1 bb
3 sadcreate or replace procedure test
(sqlstr in varchar2)
as
type ref_cursor is ref cursor;
cursor_me ref_cursor;
myid number(2);
myname varchar2(20);
mysql varchar2(2000);
begin
mysql := 'select id,'||sqlstr;
open cursor_me for mysql;
loop
fetch cursor_me into myid,myname;
exit when cursor_me%notfound;
dbms_output.put_line(myname);
end loop;
close cursor_me;
end test;
/
SQL>exec test('name from aa')
aa
nn
bb
sadPL/SQL procedure successfully completed.
mycursor v_cursor; --声明游标变量
v_code long;
v_temp long; --临时
v_temp1 long; --临时
v_temp2 long; --临时
v_temp3 long; --临时
v_temp4 long; --临时
v_sql long; --合计的最终sql
v_val integer; --某一月份的天数
v_code1 varchar2(30);
v_name varchar2(100);
begin
v_temp1 :=' ';
v_temp3 :=' ';
v_code :='select distinct a.wellname as wellname,a.WELLTEAMNO as zuanj,f_getcomname(a.comname) as gsname,f_db_get_jcbmname(a.qy) as qymc,f_db_get_jcbmname(a.qk) as qkmc,f_db_get_jcbmname(a.jb) as jbmc,a.designdeep as shejjs,a.completedeep as shijjs,f_dongt_sumsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''' ,5,1) as lossmoney,f_dongt_sumsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''' , 5,2) as lossjc,f_dongt_sumsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''' , 5,3) as accidentcount ';
open mycursor for select distinct sglbbm,f_db_get_jcbmname(sglbbm) from p_sgfzcl where sgsj >= to_date(as_date1,'yyyy-mm-dd') and sgsj <= to_date(as_date2,'yyyy-mm-dd') ;
loop
fetch mycursor into v_code1,v_name ;
exit when mycursor%NOTFOUND;
if v_code1 is null then
v_code1 :=' ' ;
v_name :='未命名';
end if;
v_temp1 :=v_temp1||', f_dongt_xxsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''','''||v_code1||''',5,1) as '||v_name;
v_temp3 :=v_temp3||', f_dongt_xxsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''','''||v_code1||''',5,2) as '||v_name;
end loop;
v_temp2 :=',f_dongt_sumsgtj(a.wellno,'''||as_date1||''', '''||as_date2||''' ,5,4) as losstime';
v_temp := v_temp1||v_temp2||v_temp3;
v_code := v_code||v_temp;
v_sql := v_code||' from c_well a,p_sgfzcl p where p.wellno=a.wellno and sgsj >= to_date('''||as_date1||''','''||'yyyy-mm-dd'') and sgsj <= to_date('''||as_date2||''','''||'yyyy-mm-dd'||''')';
close mycursor;
EXECUTE IMMEDIATE v_sql into v_sum;
as_resql :=v_sum;
EXCEPTION
WHEN OTHERS THEN
as_resql :='null'; --异常处理信息
end p_dtsgdttj_getsql;
用ref cursor...
v_in varchar2(100);
v_in:='parameter from Para where id=1';
open c_name for 'select id,'||v_in;
return c_name;
..
AS
sSQL Varchar2(200);
type ref_cursor is ref cursor;
cursor_me ref_cursor;
BEGIN
sSQL := 'select distinct ' || InputExpress || ' from app_para where 1=1'
open cursor_me for sSQL;
loop
FETCH cursor_me INTO RtnFlag;
end loop;
close cursor_me;
END;
用pl/sql编译错误,怎么回事,open cursor_me for sSQL;报错
sSQL := 'select distinct ' || InputExpress || ' from app_para where 1=1'
这后面少了一个分号了
sSQL := 'select distinct ' || InputExpress || ' from app_para where 1=1';