Question

1. Create a procedure named DisplayInfo which takes customer name as a parameter and displays information of that customer. (database – sql_store)

Answer 1

Answer)

The proceduredure was created in Oracle Database Software.

Code

SQL Code: Before executiing the procedure the below table was created and following rows are inserted.

create table customer(cid int,cn varchar(20),city varchar(20));
insert into customer values(1001,'Jones','Tokyo');
insert into customer values(1002,'Martin','London');
insert into customer values(1003,'Ruby','texas');
insert into customer values(1004,'Ruby','Sydney');

Procedure Code

create or replace procedure proc1(n varchar) as
   ----Cursor is a variable which holds all the rows in memory
   cursor c1 is select * from customer where cn=n;
   ----The table has three fields, so three variables are declared
   ----%type indicates same datatype in the table
   id customer.cid%type;
   na customer.cn%type;
   cy customer.city%type;
   ------Procedure started
   begin
       ------cursor opened
       open c1;
      
       ----The data is displayed
       loop
           -----The value of the cursor is fetched in id,na and cy variable
           fetch c1 into id,na,cy;
           exit when c1%notfound;
           dbms_output.put_line(id||' '||na||' '||cy);
          
       end loop;
-----Procedure ended
end;

Screenshot

Before writing the code type

set serveroutput on