3 Replies Latest reply on Apr 15, 2015 3:26 PM by EdStevens

    store procedures

    899401

      what is the store procedures and why we use this

      i understand when i go through the statements but wants to know in what all scenarious it specfically used

       

      create table TABLE1( JOB_ID int, num_sp1 int, num_sp2 int, num_sp3 int, num_sp4 int );

      create table external_table as select * from TABLE1;

       

      CREATE OR REPLACE PROCEDURE sp_INSERT ( RECORD_COUNT OUT NUMBER )

      IS

      BEGIN  

       

        delete TABLE1;

        record_count := SQL%rowcount;

       

        INSERT INTO TABLE1

             ( JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4  )

        SELECT JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4

        FROM EXTERNAL_TABLE;

        COMMIT;

      END;

      /

        • 2. Re: store procedures

           

          what is the store procedures and why we use this

          i understand when i go through the statements but wants to know in what all scenarious it specfically used

           

          Sorry - that is NOT a sql developer question.

           

          If you need more help than the doc link provided then please mark the question ANSWERED and repost it in the Database General forum:

          General Database Discussions

          • 3. Re: store procedures
            EdStevens

            899401 wrote:

             

            what is the store procedures and why we use this

            i understand when i go through the statements but wants to know in what all scenarious it specfically used

             

            create table TABLE1( JOB_ID int, num_sp1 int, num_sp2 int, num_sp3 int, num_sp4 int );

            create table external_table as select * from TABLE1;

             

            CREATE OR REPLACE PROCEDURE sp_INSERT ( RECORD_COUNT OUT NUMBER )

            IS

            BEGIN 

             

              delete TABLE1;

              record_count := SQL%rowcount;

             

              INSERT INTO TABLE1

                   ( JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4  )

              SELECT JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4

              FROM EXTERNAL_TABLE;

              COMMIT;

            END;

            /

            A stored (not store) procedure is used whenever you need a controlled, repeatable process.