How to write a PL/SQL procedure with x input parameters and input/ouput of x parameters combined

0
votes

I have to make a procedure that will create me autonomously a STUDENT_ID with the input parameters as Name, Surname, Gender, Date_Birth, State.

Example :

  • Name: John
  • Surname: Smith
  • Gender: M
  • Birth Date: 17/05/1996
  • state: California

Output:

STUDENT_ID is : JHN-STH-M-17596-CLFN

I put "-" to make it see better in the question but in the output it should be JHNSTHM17596CLFN

I have made 5 separate procedures that will calculate name, surname ecc.. I want to write a procedure that will calculate the STUDENT_ID using the procedures I made (in order) , and also have an "input" parameter and input/output "student" that will "print" the STUDENT_ID

procedure  student_id     (surname in varchar2,
                           name in varchar2,
                           gender in varchar2,
                           date_birth in varchar2,
                           state in varchar2) is
begin

....


dbms_output.put_line ('Student_ID is :');

This code is "supposed" to be the input parameter, I don't know if its written correctly

1
oracleplsqlplsqldeveloperplsql-package
I don't understand the expected output. where does the H come from? Also to convert "California" to "CLFN" (Which is a state), you would either have to create a lookup table, or have a CASE expression for every state. – OldProgrammer
@OldProgrammer H comes from joHn (name, I have made a procedure to calculate the name, where it will extract me only the first 3 consonant) , "CLFN" was an example (a bad one) of the input and the expected output, I have a procedure and a city table that I get reference from. All I want is an example of how I should write the procedure to calculate the student_id , included all the other procedures I have made to calculate name,surname ecc. – Silent Storm

1 Answers

1
votes

To me, it looks as if

  • you should "convert" procedures that calculate every part of the STUDENT_ID into functions
    • Why? Because - as it is now - procedures have to have an OUT parameter so that they could return what they calculated. And that's nothing but a function
  • pass pieces of information to each of them
  • get the result
  • concatenate result pieces into the final STUDENT_ID values

Something like this:

function f_name (par_name in varchar2) return varchar2 is
  retval varchar2(20);
begin
  retval := whatever code you have to find it
  return retval;
end;

function f_surname (par_surname in varchar2) return varchar2 is
  retval varchar2(20);
begin
  retval := whatever code you have to find it
  return retval;
end;

etc.

procedure student_id (par_surname in varchar2, par_name in varchar2, ...)
is
  l_student_id varchar2(30);
begin
  l_student_id := f_name   (par_name)    ||'-'||
                  f_surname(par_surname) ||'-'||
                  f_gender (par_gender)  ||'-'||
                  ...
                  f_state  (par_state);
                  
  dbms_output.put_line('Student_ID is: ' || l_student_id);
end;

Finally, as all those functions and procedure deal with the same problem, it would be nice to put them all into a package.