How to count the number of line without repetition of a set value in a PROCEDURE

Question

Oracle SQL+ (PL/SQL) is used*

How do I count the number of ligne in a similar case, using a PROCEDURE:

Example of a table

CREATE TABLE Teacher
(name         CHAR(7)       NOT NULL,
 lastName     INTEGER       NOT NULL,
 teacherCode  CHAR(5)       NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (name)
);

CREATE TABLE Class
(acronym           CHAR(7)      NOT NULL,
 groupNum          INTEGER      NOT NULL,
 sessionCode       INTEGER      NOT NULL,
 teacherCode        CHAR(5)     NOT NULL,
CONSTRAINT P_Key PRIMARY KEY (acronym)
CONSTRAINT F_Key FOREIGN KEY (teacherCode)
);

I want to count the number of classes a particular teacher gave for each session.

Example of data in the table

Teacher
John Jonas JJ000 
zzzz zzzzz zzzzz
zzzz zzzzz zzzzz
...

Class
XXX1000 10 1000 JJ000
zzzzzzz zz zzzz zzzzz
XXX3333 16 2000 JJ000
zzzzzzz zz zzzz zzzzz
XXX2222 12 1000 JJ000
zzzzzzz zz zzzz zzzzz
XXX1000 13 2000 JJ000
zzzzzzz zz zzzz zzzzz
XXX9999 16 2000 JJ000

I would like to have the following print:

Code of the teacher: JJ000
Name:                John
Last name:           Jonas
Number of classes given in the session 1000: 2
Number of classes given in the session 2000: 3

Here's how my PROCEDURE looks so far:

CREATE OR REPLACE PROCEDURE numberOfGiverClasses
(codeOfTeacher Teacher.teacherCode%TYPE) IS
    v_teacherCode       Teacher.teacherCode%TYPE;
    v_name              Teacher.name%TYPE;
    v_lastName          Teacher.lastName%TYPE;
    v_nbGivenClasses    INTEGER;
    v_sessionCode       Class.sessionCode%TYPE;

BEGIN
    SELECT teacherCode, name, lastName
    INTO v_teacherCode, v_name, v_lastName
    FROM Teacher
    WHERE teacherCode = codeOfTeacher ;

    DBMS_OUTPUT.PUT('Code of the teacher: ');
    DBMS_OUTPUT.PUT_LINE(v_teacherCode);
    DBMS_OUTPUT.PUT('Name: ');
    DBMS_OUTPUT.PUT_LINE(v_name);
    DBMS_OUTPUT.PUT('Last name: ');
    DBMS_OUTPUT.PUT_LINE(v_lastName);

    ????

    DBMS_OUTPUT.PUT('Number of classes given in the session ');
    DBMS_OUTPUT.PUT(v_sessionCode);
    DBMS_OUTPUT.PUT(': ');
    DBMS_OUTPUT.PUT(v_nbGivenClasses);

END numberOfGiverClasses;

Answer 1

CREATE OR REPLACE PROCEDURE numberOfGiverClasses
(codeOfTeacher Teacher.teacherCode%TYPE) IS
    v_teacherCode       Teacher.teacherCode%TYPE;
    v_name              Teacher.name%TYPE;
    v_lastName          Teacher.lastName%TYPE;

    v_sessionCode       Class.sessionCode%TYPE;
    compteur            INTEGER;

    CURSOR pro_counter (codeOfTeacher  Teacher.teacherCode%TYPE) IS
        SELECT sessionCode, COUNT(groupnum) 
        FROM Class
        WHERE teacherCode= codeOfTeacher 
        GROUP BY sessionCode;

BEGIN
    SELECT teacherCode, name, lastName
    INTO v_teacherCode, v_name, v_lastName
    FROM Teacher
    WHERE teacherCode = codeOfTeacher ;

    DBMS_OUTPUT.PUT_LINE(rpad('Code of the teacher: ',25) || v_teacherCode);
    DBMS_OUTPUT.PUT_LINE(rpad('Name: ',25) || v_name);
    DBMS_OUTPUT.PUT_LINE(rpad('Last name: ',25) || v_lastName);

    OPEN cursor_q3(codeOfTeacher);

    LOOP
        FETCH pro_counter INTO v_sessionCode, compteur;

        EXIT WHEN pro_counter%NOTFOUND;

        DBMS_OUTPUT.PUT('Number of classes given in the session ');
        DBMS_OUTPUT.PUT(v_sessionCode);
        DBMS_OUTPUT.PUT(': ');
        DBMS_OUTPUT.PUT_LINE(compteur);
    END LOOP;
    CLOSE pro_counter ;

exception when others then
    dbms_output.put_line(sqlerrm);         
END numberOfGiverClasses;

Answer 2

You can achieve it using the GROUP BY in a single query as follows:

SQL> -- Your data
SQL> with teacher as
  2  (select 'John' name, 'Jonas' lastname, 'JJ000' teachercode from dual),
  3  class as
  4  (select 'XXX1000' acronym, 10 groupnum, 1000 sessioncode, 'JJ000' teachercode from dual union all
  5     select 'XXX3333', 16, 2000, 'JJ000' from dual union all
  6     select 'XXX2222', 12, 1000, 'JJ000' from dual union all
  7     select 'XXX1000', 13, 2000, 'JJ000' from dual union all
  8     select 'XXX9999', 17, 2000, 'JJ000' from dual)
  9  --
 10  -- actual query starts from here
 11  SELECT
 12      'Code of the teacher: ' || TEACHERCODE || CHR(10)
 13      || 'Name:               ' || NAME || CHR(10)
 14      || 'Last name:           ' || LASTNAME || CHR(10)
 15      || LISTAGG('Number of classes given in the session ' || SESSIONCODE || ' : ' || CNT, CHR(10))
 16              WITHIN GROUP(ORDER BY CNT)
 17  FROM ( SELECT T.TEACHERCODE, T.NAME, T.LASTNAME, C.SESSIONCODE, COUNT(1) AS CNT
 18          FROM TEACHER T JOIN CLASS C ON T.TEACHERCODE = C.TEACHERCODE
 19          GROUP BY T.TEACHERCODE, T.NAME, T.LASTNAME, C.SESSIONCODE
 20       )
 21  GROUP BY TEACHERCODE, NAME, LASTNAME;

'CODEOFTHETEACHER:'||TEACHERCODE||CHR(10)||'NAME:'||NAME||CHR(10)||'LASTNAME:'||
--------------------------------------------------------------------------------
Code of the teacher: JJ000
Name:               John
Last name:           Jonas
Number of classes given in the session 1000 : 2
Number of classes given in the session 2000 : 3


SQL>

Answer 3

Welcome to SO!

Firstly, there appears to be multiple issues with your schema/syntax:

1. Teacher.lastName is declared as INTEGER - should be varchar instead?
2. Class.acronym is defined as primary key but has duplicates based on your data - should groupNum be the primary key instead?
3. F_Key is missing the referencing column
4. teacherCode isn't defined as a primary key in Teacher table

I've created the below sample schema after fixing these issues. Please check if this is what you intended

create table teacher
(
   name          varchar2 (10) not null,
   lastname      varchar2 (10) not null,
   teachercode   varchar2 (10) not null,
   constraint primarykey primary key (teachercode)
);


create table class
(
   acronym       varchar2 (10) not null,
   groupnum      integer not null,
   sessioncode   integer not null,
   teachercode   varchar2 (10) not null,
   constraint p_key primary key (groupnum),
   constraint f_key foreign key (teachercode) references teacher (teachercode)
);


insert into teacher  select 'John', 'Jonas', 'JJ000' from dual;
commit;


insert into class
   select 'XXX1000' acronym, 10 groupnum, 1000 sessioncode, 'JJ000' teachercode from dual union all
   select 'XXX3333', 16, 2000, 'JJ000' from dual union all
   select 'XXX2222', 12, 1000, 'JJ000' from dual union all
   select 'XXX1000', 13, 2000, 'JJ000' from dual union all
   select 'XXX9999', 17, 2000, 'JJ000' from dual;
commit;


select * from teacher;

| NAME    |LASTNAME    |TEACHERCODE |
| John    |Jonas       |JJ000       |


select * from class;

|ACRONYM    |GROUPNUM   |SESSIONCODE    |TEACHERCODE    |
|XXX1000    |10         |1000           |JJ000          |
|XXX3333    |16         |2000           |JJ000          |
|XXX2222    |12         |1000           |JJ000          |
|XXX1000    |13         |2000           |JJ000          |
|XXX9999    |17         |2000           |JJ000          |

You may be able to print the count of classes per session using a cursor for loop inside the proc. You may modify the cursor as appropriate based on your requirement.

A sample is as follows ..

CREATE OR REPLACE PROCEDURE numberOfGiverClasses
(codeOfTeacher Teacher.teacherCode%TYPE) IS
    v_teacherCode       Teacher.teacherCode%TYPE;
    v_name              Teacher.name%TYPE;
    v_lastName          Teacher.lastName%TYPE;

BEGIN
    SELECT teacherCode, name, lastName
    INTO v_teacherCode, v_name, v_lastName
    FROM Teacher
    WHERE teacherCode = codeOfTeacher ;

    DBMS_OUTPUT.PUT_LINE(rpad('Code of the teacher: ',25) || v_teacherCode);
    DBMS_OUTPUT.PUT_LINE(rpad('Name: ',25) || v_name);
    DBMS_OUTPUT.PUT_LINE(rpad('Last name: ',25) || v_lastName);

    for o in (
    select sessioncode, count(groupnum) cnt from class
    where teachercode = v_teacherCode
    group by sessioncode
    ) loop
        DBMS_OUTPUT.PUT_LINE('Number of classes given in the session ' || o.sessionCode ||': ' ||o.cnt );
    end loop;

exception when others then
    dbms_output.put_line(sqlerrm);         
END numberOfGiverClasses;

Sample execution

set serveroutput on size unlimited
begin
numberOfGiverClasses('JJ000');
end;

Output:

Code of the teacher:     JJ000
Name:                    John
Last name:               Jonas
Number of classes given in the session 1000: 2
Number of classes given in the session 2000: 3

If this doesn't work, please enhance your question with additional details
If this suits your requirement, feel free to accept and vote