--表名,字段名,条件,三个参数,返回结果集的函数或者存储过程,怎么写??
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字段数据的
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字段数据的
EXECUTE IMMEDIATE sql;
过程里面用这个
create or replace procedure sp_chk_data_test
(
Result out varchar2,
Table_name in varchar2,
Column_name in varchar2,
Filter_value in varchar2
)
is
V_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;