CODEWITHSUNDEEP
oracle-databaseplsqlcursorprocedure
Velocity
Velocity

Reputation: 479

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

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

enter image description here

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

Destination table:

PROJECT_DATA

enter image description here

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 .

Upvotes: 0

Views: 2117

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

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;

Upvotes: 1

Related Questions