Oracle PL/SQL Procedure with for loop to insert/update

Question

I need to write PL/SQL procedure with cursor for loop to insert/update the data rowwise.

Data from staging table needs to be populated to main table.

It will first check if the project_id and department exist or not in main table,then it will insert/update accordingly.

(Merge cannot be used as per requirement)

So i have staging table which gets populated.

STAGE_PROJECT

So, if the project_id,department exist,contract and scope columns would get updated. Else the row would get inserted.

Destination table:

PROJECT_DATA

Example: for ERP and SAP, contract and scope would get updated and for DWH,since the project_id and department do not exists, row will get inserted .

As per what requirement? Is this some homework in an intro course in PL/SQL? If it is, you should say so. — user5683823
– user5683823, Commented Apr 12, 2021 at 15:15
And what exactly is your problem? Does the procedure not compile? Or do you have a question about the syntax? — D. Mika
– D. Mika, Commented Apr 12, 2021 at 15:21
Please show what you have tried and what you think is the problem with it? You may want to review How to ask a homework question. — Belayer
– Belayer, Commented Apr 12, 2021 at 16:13

Aman Singh Rajpoot · Accepted Answer · 2021-04-12 17:43:22Z

Hopes this helps

I created your tables.

CREATE TABLE STAGE_PROJECT
(
  PROJECT_ID NUMBER,
  DEPARTMENT VARCHAR2(30),
  CONTRACT VARCHAR2(30),
  "SCOPE" VARCHAR2(30),
  FINAL_DATE DATE

);
CREATE TABLE PROJECT_DATA
(
  PROJECT_ID NUMBER,
  DEPARTMENT VARCHAR2(30),
  CONTRACT VARCHAR2(30),
  "SCOPE" VARCHAR2(30),
  FINAL_DATE DATE

);

And inserted your data.

INSERT INTO PROJECT_DATA(PROJECT_ID, DEPARTMENT) VALUES (1 , 'ERP');
INSERT INTO PROJECT_DATA(PROJECT_ID, DEPARTMENT) VALUES (2 , 'SAP');

INSERT INTO STAGE_PROJECT(PROJECT_ID, DEPARTMENT, CONTRACT, SCOPE) VALUES (1 , 'ERP', 'NEW','FINAL');
INSERT INTO STAGE_PROJECT(PROJECT_ID, DEPARTMENT, CONTRACT, SCOPE) VALUES (2 , 'SAP', 'OLD','UPCOMING');
INSERT INTO STAGE_PROJECT(PROJECT_ID, DEPARTMENT, CONTRACT, SCOPE) VALUES (3 , 'DWH', 'NEW CONTRA','TARGET');

SELECT * FROM PROJECT_DATA;
SELECT * FROM STAGE_PROJECT;

This PLSQL code loop through your STAGE_PROJECT rows If rows found in the PROJECT_DATA it will update those rows otherwise, it will insert the row which is not found.

DECLARE
  CURSOR SPCUR IS SELECT * FROM STAGE_PROJECT;
  EX PLS_INTEGER;
BEGIN

  FOR STAGE_PROJECT_REC IN SPCUR
  LOOP

    SELECT COUNT(*) INTO EX FROM PROJECT_DATA WHERE
    PROJECT_ID = STAGE_PROJECT_REC.PROJECT_ID AND
    DEPARTMENT = STAGE_PROJECT_REC.DEPARTMENT;

    IF EX > 0 THEN
      UPDATE PROJECT_DATA SET CONTRACT = STAGE_PROJECT_REC.CONTRACT,
                              SCOPE = STAGE_PROJECT_REC.SCOPE,
                              FINAL_DATE = STAGE_PROJECT_REC.FINAL_DATE
      WHERE PROJECT_ID = STAGE_PROJECT_REC.PROJECT_ID AND
            DEPARTMENT = STAGE_PROJECT_REC.DEPARTMENT;
      ELSE

      INSERT INTO PROJECT_DATA(PROJECT_ID, DEPARTMENT, CONTRACT, SCOPE, FINAL_DATE)
        VALUES (STAGE_PROJECT_REC.PROJECT_ID, STAGE_PROJECT_REC.DEPARTMENT, STAGE_PROJECT_REC.CONTRACT, STAGE_PROJECT_REC.SCOPE, STAGE_PROJECT_REC.FINAL_DATE);

    END IF;

  END LOOP;
  COMMIT;

END;

Collectives™ on Stack Overflow

Oracle PL/SQL Procedure with for loop to insert/update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related