You will create the SQL Scripts to create procedures to insert/ update data. The

Question

You will create the SQL Scripts to create procedures to insert/ update data. The following definitions specify the parameters that can be passed in. The underlined parameters are required.

Make sure that your procedure inserts records if the required parameters do not exist, but they update records if the required parameters do exist.

For example:

If SP_AddUpdateProject: passes in projectID “AA01” and it DOESN’T exists in the project table(s) , it will insert the values passed in.

If SP_AddUpdateProject: passes in projectID “AA01” and it DOES exists in the project table(s) , it will UPDATE the values passed in for the AA01 record.

Procedures Needed:

- SP_AddUpdateProject:

Adds/Updates a project with all the field information. o Parameters: projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus, projectTypeCode, projectedEndDate and projectManager

- SP_DeleteProject:

Deletes a project by the project Id. o Parameters: projectId

- SP_AddUpdateActity:

Adds/Updates activity with all the field information. o Parameters: activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate

- SP_DeleteActivity:

Deletes an activity by the activity Id and projectId. o Parameters: projectId, activityId

Explanation / Answer

HI, I have assumed the table name,columns name and its data type, please replace it accordingly with your tables structure.

SP_AddUpdateProject PROCEDURE

create or replace procedure SP_AddUpdateProject(
projectId IN varchar2,
projectName IN varchar2,
firmFedID IN varchar2,
fundedbudget IN number,
projectStartDate IN date,
projectStatus IN varchar2,
projectTypeCode IN varchar2,
projectedEndDate IN date,
projectManager IN varchar2
)

IS

V_EXISTS NUMBER:=0;
BEGIN
SELECT 1 INTO V_EXISTS
FROM PROJECT
WHERE PROJECT_ID = projectID;

-- V_EXISTS = 1 IF THE RECORDS EXISTS SO WE UPDATE THE DATA
-- AND WHEN RECORD DOESNT EXISTS THEN IT WIL THROW NO DATA FOUND EXCEPTION
-- SO WILL WRITE INSERT STATEMENT IN EXCEPTION BLOCK

IF(V_EXISTS = 1) THEN
UPDATE PROJECT
SET
PROJECT_NAME = projectName,
FIRM_ID = firmFedID ,
FUND_BUDGET=fundedbudget,
START_DATE = projectStartDate,
P_STATUS = projectStatus,
PROJECT_TYPE = projectTypeCode,
END_DATE = projectedEndDate,
PROJECT_MANAGER = projectManager
WHERE PROJECT_ID = PROJECTID;

COMMIT;
DBMS_OUTPUT.PUT_LINE(PROJECTID || ' UPDATED');
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO PROJECT
VALUES(
PROJECTID
projectName,
firmFedID ,
fundedbudget,
projectStartDate,
projectStatus,
projectTypeCode,
projectedEndDate,
projectManager);

COMMIT;
DBMS_OUTPUT.PUT_LINE(PROJECTID || ' INSERTED.');
END;

SP_DeleteProject PROCEDURE

CREATE OR REPLACE PROCEDURE SP_DeleteProject(
PROJECTID IN VARCHAR2) IS
BEGIN
DELETE FROM PROJECT
WHERE PROJECT_ID = PROJECTID;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO SUCH PROJECT EXISTS');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR.');
END;

SP_AddUpdateActity PROCEDURE:

CREATE OR REPLACE PROCEDURE SP_AddUpdateActity(
activityId IN VARCHAR2,
activityName IN VARCHAR2,
projectId IN VARCHAR2,
costToDate IN DATE,
activityStatus IN VARCHAR2,
startDate IN DATE,
endDateIN IN DATE)
IS
V_EXISTS NUMBER:=0;
BEGIN

SELECT 1 INTO V_EXISTS
FROM ACTIVITY
WHERE ACTIVITY_ID = ACTIVITYID;

-- HERE WE USED THE SAME LOGIC AS SP_ADDUPDATEPROJECT PROCEDURE

IF(V_EXISTS=1) THEN
UPDATE ACTIVITY
SET
ACTIVITY_NAME = activityName,
PROJECT_ID = projectId,
COST_TODATE = costToDate,
A_STATUS = activityStatus,
START_DATE = startDate,
END_DATE = endDate
WHERE ACTIVITY_ID =ACTIVITYID;

DBMS_OUTPUT.PUT_LINE(ACTIVITYID || ' UPDATED');
COMMIT;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO ACTIVITY
VALUES(
activityId,
activityName,
projectId,
costToDate,
activityStatus,
startDate,
endDateIN
);

DBMS_OUTPUT.PUT_LINE(ACTIVITYID || ' INSERTED');
COMMIT;
END;

SP_DeleteActivity PROCEDURE:

CREATE OR REPLACE PROCEDURE SP_DeleteActivity(
ACTIVITYID IN VARCHAR2,
PROJECTID IN VARCHAR2)
IS
DELETE FROM ACTIVITY
WHERE
ACTIVITY_ID = ACTIVIYTID
AND PROJECT_ID = PROJECTID;
COMMIT;
BEGIN
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.