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); --需要的结果就是使用动态游标,然后表名,字段名,条件作为参数的存储过程,输出结果count(*)/sum(字段)的结果,比如字段为comm,那么输出结果为:7/2260
--示例 select sum(col1) from tab1,tab2 where tab1.col2=tab2.col2 and col3='xxx' --参数: --TABLE_NAME= tab1,tab2 --COLUMN_NAME=col1 --FILTER= tab1.col2=tab2.col2 and col3='xxx' --表可能多个,但字段必定唯一
create or replace procedure sp_test_wq(iv_table_name in varchar2, iv_column_name in varchar2,iv_where in varchar2) is type t_cur is ref cursor; cur t_cur; vn_result number; begin open cur for 'select count(1)/sum(' || iv_column_name || ') from ' || iv_table_name || ' ' || iv_where; fetch cur into vn_result; dbms_output.put_line(vn_result); end sp_test_wq;
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);
--需要的结果就是使用动态游标,然后表名,字段名,条件作为参数的存储过程,输出结果count(*)/sum(字段)的结果,比如字段为comm,那么输出结果为:7/2260
--示例
select sum(col1) from tab1,tab2 where tab1.col2=tab2.col2 and col3='xxx'
--参数:
--TABLE_NAME= tab1,tab2
--COLUMN_NAME=col1
--FILTER= tab1.col2=tab2.col2 and col3='xxx'
--表可能多个,但字段必定唯一
create or replace procedure sp_test_wq(iv_table_name in varchar2,
iv_column_name in varchar2,iv_where in varchar2)
is
type t_cur is ref cursor;
cur t_cur;
vn_result number;
begin
open cur for 'select count(1)/sum(' || iv_column_name || ') from ' ||
iv_table_name || ' ' || iv_where;
fetch cur into vn_result;
dbms_output.put_line(vn_result);
end sp_test_wq;