Re: How do I start a PL/SQL program?

From: <Jiri.Felcman_at_noofs.abb.no>
Date: Mon, 20 Sep 1999 07:53:00 GMT
Message-ID: <7s4p4p$1oe$1@nnrp1.deja.com>

In article <qLtE3.95$Xa7.1748_at_client>,
  "andres" <andres_valdes_at_hotmail.com> wrote:
> Hello:
> I'm just starting to learn PL/SQL, I got my first book 'oracle ps/sql
> programming'. Right there at the start of the book the author shows an
> example of what a procedure looks like.
> I got Oracle 8.0.5 on a linux server, and I created the tables that
the
> example in the book uses. I wrote the example exactly as is in the
book
> using vi (unix text editor) but I don't know what to do with the
program. I
> use sqlpus, and connected as the owner of the table. The I typed:
> @program.sql
> And then the prompt of sqlplus is replaced by numbers that grow
sequentially
> every time I press ENTER
> The program is this:
>
> PROCEDURE maintain_company
> (action_in IN VARCHAR2,
> id_in IN NUMBERL,
> name_in IN VARCHAR2 := NULL)
> IS
> BEGIN
> IF action_in='DELETE'
> THEN
> DELETE FROM COMPANYINFO WHERE company_id =
> --COMPANY INFO IS CREATED IN MY SERVER
> ELSIF
> action_in = 'INSERT'
> THEN
> INSERT INTO company (company_id, name)
> VALUES (id_in, name_in);
> END IF;
> END;
> /
>
> Thanks for your help
> andres
>

One advice - from the beginning - use packages for all functions and procedures: Then your first package with your procedure would look like as follows:

CREATE OR REPLACE PACKAGE my_first_package IS
PROCEDURE maintain_company

         (action_in IN VARCHAR2,
         id_in IN NUMBER,
         name_in IN VARCHAR2 := NULL);

--this is procedure type definition (header) END my_first_package;
/

CREATE OR REPLACE PACKAGE BODY my_first_package IS
PROCEDURE maintain_company

         (action_in IN VARCHAR2,
         id_in IN NUMBER,
         name_in IN VARCHAR2 := NULL) IS

--this will be procedure body
BEGIN

         IF action_in='DELETE'
         THEN
          DELETE FROM COMPANYINFO WHERE company_id =
        --COMPANY INFO IS CREATED IN MY SERVER
         ELSIF
          action_in = 'INSERT'
         THEN
          INSERT INTO company (company_id, name)
          VALUES (id_in, name_in);
         END IF;

END maintain_company;

END my_first_package;
/

Then call the procedure:

exec my_first_package.maintain_company('p1',2,'p3');

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 20 1999 - 02:53:00 CDT