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.