我的目的就是希望可以在存储过程的参数中传递表的名称,然后在存储过程内定义游标的时候,根据表的名字来查询,这个可以做到吗?如下:
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
……
我希望可以实现类似这种语句片段的效果,当然,上面的写法是错误的。还请大家给与帮助,非常感谢。
------解决方案--------------------
strCursorSql := 'select distinct ' || secondField || ' from ' || strTable;
OPEN cur FOR strCursorSql;
loop
Fetch cur into secondFieldValue;
Exit when cur%notfound;
...
end loop;
------解决方案--------------------
- SQL code
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的意思大概改成这样子。。
- SQL code
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; beginopen 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;/