能否再详细一些 declarev_c_table_name VARCHAR2(20):='TISERV_SERV'; v_c_sql VARCHAR2(1000);begin v_c_sql :='select * from '||v_c_table_name;
open cur_rec for v_c_sql;
FOR rec in cur_rec loop dbms_output.put_line('1'); end loop ;end;是否这样,可是编译都过不了。
你的 cur_rec 要预先定义的,首先 create or replace package cursor_type is type t_ref_cursor is ref cursor; end cursor_type; 然后,要在过程中定义 cur_rec cursor_type.t_ref_cursor; 后面才能用 open cur_rec for v_c_sql;
-----------------------动态游标---------------------- CREATE OR REPLACE PROCEDURE test_dynamic_cursor( is_city_level NUMBER ) AS v_cursor_id INTEGER; v_selectstmt VARCHAR2(200); vs_city_code CHAR(3); v_dummy INTEGER; BEGIN --open the cursor for processing v_cursor_id := dbms_sql.open_cursor;--create the query string v_selectstmt := 'select city_code from ctiy_code_table where city_level = :m ';--parse the query dbms_sql.parse(v_cursor_id,v_selectstmt,dbms_sql.v7);--bind the input variables dbms_sql.bind_variable(v_cursor_id,':m',is_city_level);--define the output variables dbms_sql.define_column(v_cursor_id,1,vs_city_code,3);v_dummy :=dbms_sql.execute(v_cursor_id);LOOP IF dbms_sql.fetch_rows(v_cursor_id) = 0 THEN EXIT; END IF;
INSERT INTO test_test VALUES(vs_city_code,'test'); END LOOP; dbms_sql.close_cursor(v_cursor_id); COMMIT;EXCEPTION WHEN OTHERS THEN dbms_sql.close_cursor(v_cursor_id); RAISE; END test_dynamic_cursor;
declarev_c_table_name VARCHAR2(20):='TISERV_SERV'; v_c_sql VARCHAR2(1000); type mycursor is ref cursor; v_sor mycursor; begin v_c_sql :='select * from '||v_c_table_name;
open v_sor for v_c_sql; loop fetch v_sor into ....; exit when v_sor%notfound; dbms_output.put_line('1'); end loop ; close v_sor; end;
declarev_c_table_name VARCHAR2(20):='TISERV_SERV';
v_c_sql VARCHAR2(1000);begin v_c_sql :='select * from '||v_c_table_name;
open cur_rec for v_c_sql;
FOR rec in cur_rec loop
dbms_output.put_line('1');
end loop ;end;是否这样,可是编译都过不了。
create or replace package cursor_type is
type t_ref_cursor is ref cursor;
end cursor_type;
然后,要在过程中定义 cur_rec cursor_type.t_ref_cursor;
后面才能用
open cur_rec for v_c_sql;
CREATE OR REPLACE PROCEDURE test_dynamic_cursor(
is_city_level NUMBER
)
AS
v_cursor_id INTEGER;
v_selectstmt VARCHAR2(200);
vs_city_code CHAR(3);
v_dummy INTEGER;
BEGIN
--open the cursor for processing
v_cursor_id := dbms_sql.open_cursor;--create the query string
v_selectstmt := 'select city_code from ctiy_code_table where city_level = :m ';--parse the query
dbms_sql.parse(v_cursor_id,v_selectstmt,dbms_sql.v7);--bind the input variables
dbms_sql.bind_variable(v_cursor_id,':m',is_city_level);--define the output variables
dbms_sql.define_column(v_cursor_id,1,vs_city_code,3);v_dummy :=dbms_sql.execute(v_cursor_id);LOOP
IF dbms_sql.fetch_rows(v_cursor_id) = 0 THEN
EXIT;
END IF;
dbms_sql.column_value(v_cursor_id,1,vs_city_code);
INSERT INTO test_test VALUES(vs_city_code,'test');
END LOOP;
dbms_sql.close_cursor(v_cursor_id);
COMMIT;EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(v_cursor_id);
RAISE;
END test_dynamic_cursor;
v_c_sql VARCHAR2(1000);
type mycursor is ref cursor;
v_sor mycursor;
begin v_c_sql :='select * from '||v_c_table_name;
open v_sor for v_c_sql;
loop
fetch v_sor into ....;
exit when v_sor%notfound;
dbms_output.put_line('1');
end loop ;
close v_sor;
end;