I am trying to use a cursor to identify the count for each job type. If possible I would like to just show the job type with highest count but also fine to have the output show the count for each job type. So far I have the below code but not sure how to create an output. Any help would be appreciated!
DECLARE
CURSOR job_cursor IS
SELECT job, count(job) from BANK_DATA group by job;
v_job job_cursor%rowtype;
BEGIN
OPEN job_cursor;
fetch job_cursor into v_job;
CLOSE JOB_CURSOR;
END;
Depending on what "show" means, one option is to simply display it on the screen.
Example is based on Scott's sample schema, its EMP table. CLERK and SALESMAN are the most frequent jobs, so - you'd want to display them both.
SQL> set serveroutput on
SQL> select job, count(*) cnt
2 from emp
3 group by job
4 order by cnt desc;
JOB CNT
--------- ----------
CLERK 4
SALESMAN 4
MANAGER 3
ANALYST 2
PRESIDENT 1
Here's how:
RANK analytic function which ... well, ranks jobs by their occurrencelistagg function aggregates them (i.e. "concatenates" values) dbms_output.put_lineSQL> declare
2 l_list varchar2(100);
3 begin
4 select listagg(job, ', ') within group (order by null)
5 into l_list
6 from (select job,
7 rank() Over (order by count(*) desc) rn
8 from emp
9 group by job
10 ) x
11 where x.rn = 1;
12
13 dbms_output.put_Line(l_list);
14 end;
15 /
CLERK, SALESMAN
PL/SQL procedure successfully completed.
SQL>
I have modified your code slightly to match your requirements.
SET SERVEROUTPUT ON; -- To enable printing on console
DECLARE
CURSOR job_cursor IS
SELECT job, count(job) as count_job from BANK_DATA group by job; --An alias has been added to count(job) for smooth functioning with cursor
v_job job_cursor%rowtype;
job_typ varchar(20); -- name of job with highest count
job_cnt number; -- frequency of job with highest count
BEGIN
job_typ := '';
job_cnt := 0;
--OPEN job_cursor; -- FOR Loop takes care of opening cursor so you don't need to explicitly do that via your code
--fetch job_cursor into v_job; -- FOR Loop takes care of fetching cursor so you don't need to explicitly do that via your code
FOR v_job IN job_cursor
LOOP
dbms_output.put_line( v_job.job || ' ' || v_job.count_job ); -- It prints all jobs with the count of each job type
IF(job_cnt<v_job.count_job) THEN
job_cnt:= v_job.count_job;
job_typ := v_job.job;
END IF;
END LOOP;
dbms_output.put_line( 'Highest counted job is ' || job_typ || ' ' || job_cnt); -- It prints job with the highest count
--CLOSE job_cursor; -- FOR Loop takes care of opening and closing cursor so you don't need to explicitly do that via your code
END;
Hope it helps! :)
You can simply use a cursor FOR LOOP statement if you need a cursor. Then you don't need a variable:
BEGIN
FOR ds IN (
SELECT job
FROM (
SELECT job, RANK() OVER (ORDER BY COUNT(1) DESC) as rnk
FROM bank_data
GROUP BY job
)
WHERE rnk = 1
)
LOOP
DBMS_OUTPUT.PUT_LINE(ds.job);
END LOOP;
END;
/
