我的目的就是希望可以在存储过程的参数中传递表的名称,然后在存储过程内定义游标的时候,根据表的名字来查询,这个可以做到吗?如下:create or replace procedure case_to_province(table_name in varchar2, id_name in varchar2) as
cursor cur is
select * from table_name;begin for rec in cur loop ……我希望可以实现类似这种语句片段的效果,当然,上面的写法是错误的。还请大家给与帮助,非常感谢。
cursor cur is
select * from table_name;begin for rec in cur loop ……我希望可以实现类似这种语句片段的效果,当然,上面的写法是错误的。还请大家给与帮助,非常感谢。
OPEN cur FOR strCursorSql;
loop
Fetch cur into secondFieldValue;
Exit when cur%notfound;
...
end loop;
CREATE OR REPLACE PROCEDURE TEST_1 (P_IN_TBNAME IN VARCHAR2) IS
REF_CUR SYS_REFCURSOR ;
V_SQL VARCHAR2(2000) ;
V_ROWID ROWID ;
BEGIN
V_SQL := 'SELECT ROWID FROM '|| P_IN_TBNAME ;
OPEN REF_CUR FOR V_SQL ;
LOOP
FETCH REF_CUR INTO V_ROWID;
EXIT WHEN REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ROWID) ;
END LOOP ;
--business process
CLOSE REF_CUR ;
EXCEPTION
WHEN OTHERS THEN
--exception handle
RAISE ;
END ;
/
SQL> CREATE TABLE TST1 (X INT) ;
Table created
SQL> INSERT INTO TST1 VALUES (1) ;
1 row inserted
SQL> SET SERVEROUTPUT ON ;
SQL> EXEC TEST_1('TST1') ;
AAATeuAAHAAAAuHAAA
PL/SQL procedure successfully completed
根据LZ的意思大概改成这样子。。
create or replace procedure case_to_province(table_name in varchar2, id_name in varchar2)
declare cur is ref cursor ;
v_id table_name.id%type;
test_cursor cur;
begin
open test_cursor for select id from table_name where name=id_name; loop
fetch test_coursor in v_id;
exit when test_cursor%not found;
end loop;
end;
/