Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Working with Cursors in a Procedure

The_Cute_DBAMay 27 2022

I am working on a few codes for testing and also for learning. I would like to present some facts. I understand that one may say it's easily done by regular insert SQL, but I am trying to understand converting PL/SQL to a stored procedure (and packages...) as we are making some changes to make our codes portable. And I am helping out in this effort. :)

--Create table command
create table sampletab
(
name varchar2(20),
age number,
address varchar2(20)
);

-- Sample Data for my table
insert into sampletab values ('Ana',10, 'Japan');
insert into sampletab values ('John',12, 'India');
insert into sampletab values ('Maria',13, 'Sweden');
insert into sampletab values ('James',14, 'Australia');
insert into sampletab values ('Susan',15, 'Germany');
insert into sampletab values ('Mary',16, 'US');

commit;

I have my working code below which inserts the records into my custom table.

DECLARE 
   CURSOR names_cur is 
select * from sampletab;
   names_rec names_cur%rowtype;


begin
    open names_cur;
    loop
    FETCH names_cur into names_rec;
    EXIT WHEN names_cur%notfound;
    dbms_output.put_line (names_rec.name||' '||names_rec.age||' '||names_rec.address);
    insert into sampletab values (names_rec.name, names_rec.age, names_rec.address);
    end loop;
    commit;
    close names_cur;
End;

I would like to ask for some help and maybe sample codes on how I can convert this pure PL/SQL code into a stored procedure.
I am also wondering if it is possible to have one single procedure (or package) that can create the table and then load the data?
I only know simple procedure creation like the one below, but I do not know yet how to deal with cursors or records.

create or replace procedure test_proc is
   var1 varchar2(4000);
BEGIN
   var1:='create table test_proc_tab (
          id number,
          name varchar2(20))';
   EXECUTE IMMEDIATE var1;
end test_proc;

I hope you can give me some tips and guidance.

This post has been answered by Frank Kulash on May 27 2022
Jump to Answer
Comments
Post Details
Added on May 27 2022
11 comments
32,176 views