--都不没有包含的,如果要包含sal>=2000,sal<1000就可以了 set serveroutput on; create or replace procedure p_t as begin for rec in (select job, sum(case when sal>2000 then 1 else 0 end) over2000, sum(case when sal<1000 then 1 else 0 end ) below1000 from emp group by job) loop dbms_output.put_line('job_name:'||rec.job||' over 2000:'||rec.over2000||' below 1000:'||rec.below1000); end loop; end; / exec p_t;
一个语句就可以了. select job, sum(case when sal>2000 then 1 else 0 end) sal_above_2000, sum(case when sal<1000 then 1 else 0 end) sal_below_1000 from emp group by job;
CREATE OR REPLACE PROCEDURE TEST2(REFCURSOR OUT SYS_REFCURSOR) AS BEGIN OPEN REFCURSOR FOR SELECT T.JOB, SUM(CASE WHEN SAL > 2000 THEN 1 ELSE 0 END) SAL_ABOVE_2000, SUM(CASE WHEN SAL < 1000 THEN 1 ELSE 0 END) SAL_BELOW_1000 FROM SCOTT.EMP T GROUP BY JOB ORDER BY JOB; END TEST2;
CREATE OR REPLACE PROCEDURE TEST2(REFCURSOR OUT SYS_REFCURSOR) AS BEGIN OPEN REFCURSOR FOR SELECT T.JOB, SUM(CASE WHEN SAL > 2000 THEN 1 ELSE 0 END) SAL_ABOVE_2000, SUM(CASE WHEN SAL < 1000 THEN 1 ELSE 0 END) SAL_BELOW_1000 FROM SCOTT.EMP T GROUP BY JOB ORDER BY JOB; END TEST2;
--都不没有包含的,如果要包含sal>=2000,sal<1000就可以了
set serveroutput on;
create or replace procedure p_t
as
begin
for rec in (select job,
sum(case when sal>2000 then 1 else 0 end) over2000,
sum(case when sal<1000 then 1 else 0 end ) below1000
from emp group by job)
loop
dbms_output.put_line('job_name:'||rec.job||' over 2000:'||rec.over2000||' below 1000:'||rec.below1000);
end loop;
end;
/
exec p_t;
select job,
sum(case when sal>2000 then 1 else 0 end) sal_above_2000,
sum(case when sal<1000 then 1 else 0 end) sal_below_1000
from emp group by job;
CREATE OR REPLACE PROCEDURE TEST2(REFCURSOR OUT SYS_REFCURSOR) AS
BEGIN
OPEN REFCURSOR FOR
SELECT T.JOB,
SUM(CASE
WHEN SAL > 2000 THEN
1
ELSE
0
END) SAL_ABOVE_2000,
SUM(CASE
WHEN SAL < 1000 THEN
1
ELSE
0
END) SAL_BELOW_1000
FROM SCOTT.EMP T
GROUP BY JOB
ORDER BY JOB;
END TEST2;
CREATE OR REPLACE PROCEDURE TEST2(REFCURSOR OUT SYS_REFCURSOR) AS
BEGIN
OPEN REFCURSOR FOR
SELECT T.JOB,
SUM(CASE
WHEN SAL > 2000 THEN
1
ELSE
0
END) SAL_ABOVE_2000,
SUM(CASE
WHEN SAL < 1000 THEN
1
ELSE
0
END) SAL_BELOW_1000
FROM SCOTT.EMP T
GROUP BY JOB
ORDER BY JOB;
END TEST2;