Re: Newbie to PL/SQL

Bret wrote:

> I am trying to learn how to write a Stored Proc in PL/SQL. I have
> some experience in SQL, but can't seem to get the syntax right in
> PL/SQL.
>
> Example:
>
> I want to pull records from one table where the id# matches the
> current id# in the main SQL. I know I could use a join, but it will
> limit the records from the other tables. I'm also not sure how to
> call a Stored Proc properly in PL/SQL. Any help would be appreciated.
> Thanks.
>
> Bret

First off ... buy a book. Second ... bookmark all of the URLs you see us refering poeple to and use them. But I would suggest that you start of learning this in the following manner.

1. Write the SQL and get it to execute from SQL*Plus.
2. Wrap your SQL into an anonymous block and execute it from SQL*Plus
3. Turn the anonymous block into a stored procedure and execute it from SQL*Plus

Lets do a simple one based on my assumption (probably incorrect) of what you are trying to do.

Step 1:

INSERT INTO table1
SELECT *
FROM table2;

Step 2:

BEGIN
   INSERT INTO table1
   SELECT *
   FROM table2;
END;
/

it executes itself when you hit the <ENTER> key following the forward slash

Step 3:

CREATE OR REPLACE PROCEDURE first_proc IS

BEGIN
   INSERT INTO table1
   SELECT *
   FROM table2;
END first_proc;
/

Then execute it.

SQL> exec first_proc

Obviously when you get into doing things with loops you can't test them first in SQL*Plus without an anonymous block. But you should always test the SQL statements for anything, cursors, inserts, whatever, outside of a stored procedure.

Daniel Morgan Received on Mon Oct 14 2002 - 12:32:14 CDT