CODEWITHSUNDEEP
plsql
Carlos Eduard
Carlos Eduard

Reputation: 23

Stored Procedure to display all the records in a table in PL/SQL

I really need your help. I'm trying to create a stored procedure in PL/SQL to count student by Location

I've done it using Cursor,even if it may sound stupid somehow. I don't know to do it using stored procedure. So here is the code I used to count student by location using cursor

// Codes to count by location using cursor

declare
s_location VARCHAR(15);
cnt NUMBER;
CURSOR curlocation IS
SELECT address,COUNT(address) AS cnt
FROM student
GROUP BY address;
BEGIN
OPEN curlocation;
LOOP
FETCH curlocation INTO s_location,cnt;
EXIT WHEN curlocation%NOTFOUND;
IF cnt=0 THEN
dbms_output.put_line('No Students For'||s_location);
ELSE
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line(cnt ||'  '||'Students For '|| s_location);
END IF;
END LOOP;
CLOSE curlocation;
END;

As you can imagine the output will be something like::

1 Students For ...

2 Students For ...

5 Students For ...

So I want to do something like this using stored procedure.

Thanks guyz

Upvotes: 0

Views: 1775

Answers (2)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

There's not much point in creating a procedure that returns a single OUT value. That should be a function instead:

CREATE OR REPLACE FUNCTION student_count_per_location (p_location IN VARCHAR2)
   RETURN NUMBER
   AUTHID DEFINER
AS
   x_count   NUMBER;
BEGIN
   SELECT COUNT (address)
     INTO x_count
     FROM student
    WHERE address = p_location;

   RETURN x_count;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (
         student_count_per_location ('LOCATION X')
      || '  '
      || 'Students For LOCATION_X');
END;
/

Upvotes: 0

mkuligowski
mkuligowski

Reputation: 1594

You can create a procedure and use your cursor like before:

create procedure count_and_print_students as
  l_location varchar(15);
  l_count number;

  cursor c_student_locations
  is
    select address, count(address) as cnt
    from student group by address;
begin
  open c_student_locations;
  loop
    fetch c_student_locations into l_location, l_count;
    exit
  when c_student_locations%notfound;
    if l_count = 0  then
      dbms_output.put_line('No Students For'||l_location);
    else
      dbms_output.put_line('--------------------------------------------');
      dbms_output.put_line(l_count ||'  '||'Students For '|| l_location);
    end if;
  end loop;
  close c_student_locations;
end;

and run it as:

DECLARE
BEGIN
count_and_print_students();
END;

Moreover, you can create the procedure for counting students per single location as argument like this:

create procedure count_students_per_location(p_location in varchar2, x_count out number) as
begin
  select count(address) 
  into x_count
  from student
  where address = p_location;
end;

So, it can be used in following way:

DECLARE
  l_count number;
BEGIN
  count_students_per_location('LOCATION_X',l_count);
  dbms_output.put_line(l_count ||'  '||'Students For LOCATION_X');
END;

Upvotes: 1

Related Questions