select * from B where id in(select id from A where status=1)
SQL> --建表,ststus 0:无效,1:有效 SQL> CREATE TABLE a(col_name VARCHAR2(30), status CHAR(1) DEFAULT '1');表已创建。SQL> CREATE TABLE b(id NUMBER, NAME VARCHAR2(20), age NUMBER(3));表已创建。SQL> --为a表插入数据 SQL> INSERT INTO a 2 SELECT t.COLUMN_NAME, MOD(rownum, 2) FROM user_tab_cols t WHERE t.TABLE_NAME = 'B';已创建3行。SQL> --为b表插入数据 SQL> insert into b values(1,'张三',25);已创建 1 行。SQL> insert into b values(2,'李四',30);已创建 1 行。SQL> insert into b values(3,'王五',18);已创建 1 行。SQL> commit;提交完成。SQL> select * from a;COL_NAME S ------------------------------ - ID 1 NAME 0 AGE 1SQL> select * from b; ID NAME AGE ---------- -------------------- ---------- 1 张三 25 2 李四 30 3 王五 18SQL> --创建存储过程 SQL> CREATE OR REPLACE PROCEDURE p_test(o OUT SYS_REFCURSOR) IS 2 sqlstr VARCHAR2(4000); 3 BEGIN 4 sqlstr := 'select '; 5 FOR c IN (SELECT col_name FROM a WHERE status = '1') LOOP 6 sqlstr := sqlstr || c.col_name || ','; 7 END LOOP; 8 sqlstr := rtrim(sqlstr, ',') || ' from b'; 9 OPEN o FOR sqlstr; 10 EXCEPTION 11 WHEN OTHERS THEN 12 OPEN o FOR 'select * from dual'; 13 END p_test; 14 /过程已创建。SQL> --调用存储过程 SQL> var o refcursor; SQL> exec p_test(:o);PL/SQL 过程已成功完成。SQL> print o; ID AGE ---------- ---------- 1 25 2 30 3 18SQL>
SQL> CREATE TABLE a(col_name VARCHAR2(30), status CHAR(1) DEFAULT '1');表已创建。SQL> CREATE TABLE b(id NUMBER, NAME VARCHAR2(20), age NUMBER(3));表已创建。SQL> --为a表插入数据
SQL> INSERT INTO a
2 SELECT t.COLUMN_NAME, MOD(rownum, 2) FROM user_tab_cols t WHERE t.TABLE_NAME = 'B';已创建3行。SQL> --为b表插入数据
SQL> insert into b values(1,'张三',25);已创建 1 行。SQL> insert into b values(2,'李四',30);已创建 1 行。SQL> insert into b values(3,'王五',18);已创建 1 行。SQL> commit;提交完成。SQL> select * from a;COL_NAME S
------------------------------ -
ID 1
NAME 0
AGE 1SQL> select * from b; ID NAME AGE
---------- -------------------- ----------
1 张三 25
2 李四 30
3 王五 18SQL> --创建存储过程
SQL> CREATE OR REPLACE PROCEDURE p_test(o OUT SYS_REFCURSOR) IS
2 sqlstr VARCHAR2(4000);
3 BEGIN
4 sqlstr := 'select ';
5 FOR c IN (SELECT col_name FROM a WHERE status = '1') LOOP
6 sqlstr := sqlstr || c.col_name || ',';
7 END LOOP;
8 sqlstr := rtrim(sqlstr, ',') || ' from b';
9 OPEN o FOR sqlstr;
10 EXCEPTION
11 WHEN OTHERS THEN
12 OPEN o FOR 'select * from dual';
13 END p_test;
14 /过程已创建。SQL> --调用存储过程
SQL> var o refcursor;
SQL> exec p_test(:o);PL/SQL 过程已成功完成。SQL> print o; ID AGE
---------- ----------
1 25
2 30
3 18SQL>