I am trying to create a procedure in oracle pl/sql but cant really put in the exact logic for what I am trying to do

Maria Zafar 0
Viewed 38

When it takes input , email is given and all other parameters are null it should show all records of that email. Similarly with other three input paramters.

If all inputs are null then it should show all records of the student table.

create or replace procedure get_student_records(
    pis_email in varchar2,
    pis_rollno in number,
    pis_dept in varchar,
    pi_class_id in number
)
IS    
begin
    
end get_student_records;

Tables to be accessed :

CREATE TABLE student (
    s_id     NUMBER(10) NOT NULL,
    s_rollno NUMBER(10) NOT NULL,
    s_name   VARCHAR2(20) NOT NULL,
    s_dept   VARCHAR2(10),
    s_email  VARCHAR2(25),
    class_id NUMBER(10),
    CONSTRAINT student_pk PRIMARY KEY ( s_rollno ),
    CONSTRAINT fk_class FOREIGN KEY ( class_id )
        REFERENCES class ( class_id )
);

CREATE TABLE class (
    class_id   NUMBER(10) NOT NULL,
    class_name VARCHAR2(10) NOT NULL,
    dept_id    NUMBER(10),
    CONSTRAINT class_pk PRIMARY KEY ( class_id ),
    CONSTRAINT fk_depts FOREIGN KEY ( dept_id )
        REFERENCES dept ( dept_id )
);

CREATE TABLE dept (
    dept_id   NUMBER(10) NOT NULL,
    dept_name VARCHAR2(50) NOT NULL,
    CONSTRAINT depts_pk PRIMARY KEY ( dept_id )
);
plsql oracle sql
1 Answers

There is a trick you can use -- lets say in_email, in_name are parameters and the fields are name and email -- the query looks like this:

 SELECT *
 FROM table as t
 WHERE COALESCE(in_name,t.name) = name AND COALESCE(in_email,t.email) = email

The way this works -- when the parameter is null the statement is true (so all are found -> it is a wildcard. When there is a value, it only matches on the exact value.

Hogan0
Related