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

Views in Oracle

Views
A view is a description of how data is to be retrieved from the underlying tables. It does not store data but it is treated as though if it were a table in SQL statements. Views are used to perform the following actions:
?    Limit the rows accessible to a user (row-level security)
?    Limit specific columns accessible to a user (column-level security)
?    Pre-join several tables (removing the requirement that the user understand the complexity of joining tables)

The views used in the EERSS are for row-level security. Views help to achieve the first goal of database security ? secrecy and confidentiality.

EERSS uses the following views:
?    Employee_Reim_Requests_V
?    Client_Reim_Requests_V

Having ?_V? in the view name identifies this database entity as a view.

The Employee_Reim_Requests_V View
Each employee is only allowed to see her reimbursement requests. Managers can see all requests submitted by all employees in their group.

CREATE OR REPLACE VIEW Employee_Reim_Requests_V AS
    SELECT rr.*
    FROM Reim_Request_BT rr, Employee_BT e
    WHERE rr.EmployeeId = e.EmployeeId
         AND e.EmpDBName = user
UNION
    
SELECT rr.*
FROM Reim_Request_BT rr, Employee_BT e, Dept_Manager_BT dm
WHERE rr.ApprovedBy = dm.ManagerId
AND rr.EmployeeId = e.EmployeeId
AND e.DeptId = dm.DepartmentId
AND dm.ManagerId =
(SELECT ManagerId
FROM Dept_Manager_BT dm1, Employee_BT e1
WHERE e1.EmpDBName = user
AND e1.EmployeeId = dm1.ManagerId)

Note that the script uses the pseudo column ?user?. This is one of several virtual columns available for use by anybody. The value of ?user? is always the login name.

The Client_Reim_Requests_V View
Each outside client is only allowed to view the reimbursement requests submitted by employees who worked on its projects.

SELECT rr.*
FROM Reim_Request_BT rr, Client_Project_BT cp, Client_BT c
WHERE rr.ProjectId = cp.ProjectId
AND cp.ClientId = c.ClientId
AND cp.EmployeeId = rr.EmployeeId
AND c.ClientDBName = user

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