Question

oracle 11g 2nd edition

pl/sql

Assignment 5-10: Returning a Record Create a procedure named DDPROJ_SP that retrieves project information for a specific project based on a project ID. The procedure should have two parameters: one to accept a project ID value and another to return all data for the specified project. Use a record variable to have the procedure return all database column values for the selected project. Test the procedure with an anonymous block.

Answer 1

Any doubt please comment me.

Answer)

----I created a project table

create table project(pid int,pname varchar(30),psdate date,pedate date,mid int);

insert into project values (1,'Java','23-apr-2019','23-oct-2019',1001);
insert into project values (2,'C++','25-apr-2019','26-oct-2019',1001);

commit;

----Procedure create with id as IN variable, and ptab as OUT variable.
----IN variable is to send data inside the procedure and OUT variable is for
----return the value from a procedure.

----Procedure created

create or replace procedure DDPROJ_SP(id int,ptab out project%rowtype)
is
begin
----when the id matches it returns it return the whole row into
----record type variable ptab
select * into ptab from project where pid=id;
end;

set serveroutput on

----Anonymous block for testing

declare
----Declare a recordtype variable which hold the record from the procedure
prec project%rowtype;
begin
----execute the procedure
DDPROJ_SP(1,prec);
----display the values when returned
dbms_output.put_line(prec.pid||' '||prec.pname||' '||prec.mid);
end;

Screenshot