当前位置: 代码迷 >> Oracle技术 >> 传入表名,字段名,条件,返回结果集的function或者proc,该怎么处理
  详细解决方案

传入表名,字段名,条件,返回结果集的function或者proc,该怎么处理

热度:75   发布时间:2016-04-24 08:30:20.0
传入表名,字段名,条件,返回结果集的function或者proc

SQL code
--表名,字段名,条件,三个参数,返回结果集的函数或者存储过程,怎么写??create table emp (empno number(4) constraint EMP_pk primary key, ename varchar2(9), job varchar2(9), mgr number(4), sal number(7,2), comm number(7,2)); insert into emp(empno,ename,job,mgr,sal,COMM) values(7369,'smith','clerk',7902,800,20);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7499,'allen','salseman',7698,1600,300);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7521,'WARD','salseman',7698,1250,500);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7566,'JONES','MANAGER',7839,2975,20);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7645,'MARTIN','SALESMAN',7698,1250,1400);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7698,'BLACK','MANAGER',7839,2850,20);  insert into emp(empno,ename,job,mgr,sal,COMM) values(7844,'URNER','SALESMAN',7698,1500,0);--TABLE_NAME= emp --COLUMN_NAME=job--FILTER= 1=1--这样的这三个参数传入应该输出全部的job字段数据的


------解决方案--------------------
sql = 'select '||NVL(COLUMN_NAME,'*')||' FROM '||TABLE_NAME||' WHERE '||NVL(FILTER,'1=1');
EXECUTE IMMEDIATE sql;


过程里面用这个
------解决方案--------------------
SQL code
create or replace procedure sp_chk_data_test(  Result out varchar2,  Table_name  in varchar2,  Column_name in varchar2,  Filter_value in varchar2  )isV_SQL varchar2(2000);BEGIN     --V_SQL = 'select '||NVL(COLUMN_NAME,'*')||' FROM '||TABLE_NAME||' WHERE '||NVL('||Filter_value||',''1=1'');  V_SQL := 'insert into temp_a SELECT ENAME FROM EMP ';  EXECUTE IMMEDIATE V_SQL;  for my_cur in ( SELECT ename FROM temp_a ) loop    RESULT := RESULT|| my_cur.ENAME || ',' ;  end loop;end sp_chk_data_test;
  相关解决方案