prepare_procedure.sql

CREATE OR REPLACE PROCEDURE GET_STUDENT_Mobile_BY_ID 
(
  std_id IN NUMBER 
, std_mobile OUT NUMBER
) AS 
BEGIN
  select mobile into std_mobile from students where ID = std_id;
END GET_STUDENT_Mobile_BY_ID;
-----------------------------------------------------
CREATE OR REPLACE PROCEDURE GET_STUDENT_NAME_BY_ID 
(
  std_id IN NUMBER 
, std_name OUT varchar2
) AS 
BEGIN
  select name into std_name from students where ID = std_id;
END GET_STUDENT_NAME_BY_ID;
-----------------------------------------------------
CREATE OR REPLACE PROCEDURE GET_STUDENT_BY_COURSE_NUM 
(
  COURSE_NUM IN NUMBER 
, STUDENT OUT SYS_REFCURSOR
) AS 
BEGIN
  open STUDENT for 
  select id,name,mobile from STUDENTS
  where id in
  (
    select student_id from
    (
      select student_id, count(course_id) from STUDENT_COURSES
      group by student_id
      having count(course_id) = COURSE_NUM
    )
  );
END GET_STUDENT_BY_COURSE_NUM;
-----------------------------------------------------