1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Stored Procedures in Oracle

Stored Procedures
A stored procedure is a program written in SQL that is stored in a compiled form within the database. Stored procedures can optionally take arguments and/or return values. They can be used to perform table updates as well as retrieve data from tables. Table triggers, applications, or users can execute stored procedures. Stored procedures are usually written and owned by the DBA. It is important to understand the relationship between the owner of a stored procedure and the user who executes the procedure. In general, stored procedures execute on behalf of the user, but use the privileges of the creator. For example, suppose mary is the Employee_BT owner and the creator of the Employee_SP stored procedure. Employee_SP when executed modifies the entries in the Employee_BT table. If the user ed, who does not have privileges on mary?s employee table, executes the stored procedure, it will succeed and update a record in the employee table. The Oracle database knows that mary could perform updates to the employee table and carries that privilege over to ed. However, privileges granted via roles are not understood by stored procedures. The DBA cannot grant privileges to a role, grant that role to a user, and have the user successfully run the stored procedure with access determined by the role privileges. The best approach is for the schema owner (usually the DBA) to own all stored procedures.

Stored procedures used in EERSS guarantee that system users can perform certain actions (or certain sequence of actions) only. Thus they help to achieve the second goal of database security ? data accuracy and integrity. The following stored procedures are used by the EERSS:
    Create_New_Request_SP
    Update_Request_SP
    Approve_Request_SP

The Create_New_Request_SP stored procedure
This stored procedure validates the information submitted by the employee such as the project id (if there is one) and the format of the sum. The stored procedure also generates the new request id, calculates the date of the request, and matches the employee id with the login submitted by the employee. Because it is a new request, the ApprovedById field is blank, and RequestPaid is set to ?N?.

PROCEDURE CREATE_NEW_REQUEST_SP
-- Params: newSum - sum of the request, projID - projectId, equals 0 if no associated project id
(newSum IN NUMBER, projID IN NUMBER, returnStatus OUT VARCHAR2)
IS
newID NUMBER;
    empID NUMBER;
    empprojID NUMBER;
    projCount NUMBER;


BEGIN
-- find EmployeeId from Employee_BT table based on user login
SELECT EmployeeId INTO empID FROM Employee_BT
WHERE EmpDBName = 'test';

IF empID IS NULL THEN
            SELECT StatusTitle INTO returnStatus FROM QueryStatus
WHERE StatusId = 1;

ELSE
            -- If there is a project id, then validate that this employee worked on this project
             IF projID <> 0 THEN
                 SELECT count(*) INTO projCount FROM Client_Project_BT
                WHERE ProjectId = ProjID AND EmployeeId = empID;

                 IF projCount = 0 THEN
                    SELECT StatusTitle INTO returnStatus FROM QueryStatus
                    WHERE StatusId = 2;
                END IF;
            END IF;

             -- generate new RequestId
             SELECT Reim_Request_Seq.nextval INTO newId FROM dual;

            -- insert new request
             INSERT INTO Reim_Request_BT
             (RequestId, EmployeeId, RequestDate, RequestSum, RequestPaid, ProjectId)
            VALUES
             (newID, empID, sysdate, newSum, 'N', projID);
             COMMIT;

SELECT StatusTitle INTO returnStatus FROM QueryStatus
            WHERE StatusId = 0;

END IF;
END;

The Update_Request_SP stored procedure
This stored procedure validates that the request has not been reimbursed yet (if so, the update is rejected) and, if the request has been approved, then it sets the ApprovedById field back to blank field. This is done to guarantee that the updated request will not be reimbursed before the employee?s manager approves the updated sum of the request. The rest of the validation is the same as in the Create_New_Request_SP stored procedure.

The Approve_Request_SP stored procedure
This stored procedure validates that the current user is indeed the employee?s manager. If so, the ApprovedById field is set to the manager?s employee id.

Note that all stored procedure names end with ?_SP?. This again is part of the naming convention strategy adopted by the database development group.
1.1.1    Stored Procedures
A stored procedure is a program written in SQL that is stored in a compiled form within the database. Stored procedures can optionally take arguments and/or return values. They can be used to perform table updates as well as retrieve data from tables. Table triggers, applications, or users can execute stored procedures. Stored procedures are usually written and owned by the DBA. It is important to understand the relationship between the owner of a stored procedure and the user who executes the procedure. In general, stored procedures execute on behalf of the user, but use the privileges of the creator. For example, suppose mary is the Employee_BT owner and the creator of the Employee_SP stored procedure. Employee_SP when executed modifies the entries in the Employee_BT table. If the user ed, who does not have privileges on mary?s employee table, executes the stored procedure, it will succeed and update a record in the employee table. The Oracle database knows that mary could perform updates to the employee table and carries that privilege over to ed. However, privileges granted via roles are not understood by stored procedures. The DBA cannot grant privileges to a role, grant that role to a user, and have the user successfully run the stored procedure with access determined by the role privileges. The best approach is for the schema owner (usually the DBA) to own all stored procedures.

Stored procedures used in EERSS guarantee that system users can perform certain actions (or certain sequence of actions) only. Thus they help to achieve the second goal of database security ? data accuracy and integrity. The following stored procedures are used by the EERSS:
    Create_New_Request_SP
    Update_Request_SP
    Approve_Request_SP

The Create_New_Request_SP stored procedure
This stored procedure validates the information submitted by the employee such as the project id (if there is one) and the format of the sum. The stored procedure also generates the new request id, calculates the date of the request, and matches the employee id with the login submitted by the employee. Because it is a new request, the ApprovedById field is blank, and RequestPaid is set to ?N?.

PROCEDURE CREATE_NEW_REQUEST_SP
-- Params: newSum - sum of the request, projID - projectId, equals 0 if no associated project id
(newSum IN NUMBER, projID IN NUMBER, returnStatus OUT VARCHAR2)
IS
newID NUMBER;
    empID NUMBER;
    empprojID NUMBER;
    projCount NUMBER;


BEGIN
-- find EmployeeId from Employee_BT table based on user login
SELECT EmployeeId INTO empID FROM Employee_BT
WHERE EmpDBName = 'test';

IF empID IS NULL THEN
            SELECT StatusTitle INTO returnStatus FROM QueryStatus
WHERE StatusId = 1;

ELSE
            -- If there is a project id, then validate that this employee worked on this project
             IF projID <> 0 THEN
                 SELECT count(*) INTO projCount FROM Client_Project_BT
                WHERE ProjectId = ProjID AND EmployeeId = empID;

                 IF projCount = 0 THEN
                    SELECT StatusTitle INTO returnStatus FROM QueryStatus
                    WHERE StatusId = 2;
                END IF;
            END IF;

             -- generate new RequestId
             SELECT Reim_Request_Seq.nextval INTO newId FROM dual;

            -- insert new request
             INSERT INTO Reim_Request_BT
             (RequestId, EmployeeId, RequestDate, RequestSum, RequestPaid, ProjectId)
            VALUES
             (newID, empID, sysdate, newSum, 'N', projID);
             COMMIT;

SELECT StatusTitle INTO returnStatus FROM QueryStatus
            WHERE StatusId = 0;

END IF;
END;

The Update_Request_SP stored procedure
This stored procedure validates that the request has not been reimbursed yet (if so, the update is rejected) and, if the request has been approved, then it sets the ApprovedById field back to blank field. This is done to guarantee that the updated request will not be reimbursed before the employee?s manager approves the updated sum of the request. The rest of the validation is the same as in the Create_New_Request_SP stored procedure.

The Approve_Request_SP stored procedure
This stored procedure validates that the current user is indeed the employee?s manager. If so, the ApprovedById field is set to the manager?s employee id.

Note that all stored procedure names end with ?_SP?. This again is part of the naming convention strategy adopted by the database development group.

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy