Updating views in oracle

To create a view, you use the SQL syntax: • The view query itself is saved in the database, but it is not actually run until it is called with another SELECT statement.For this reason, the view does not take up any disk space for data storage, and it does not create any redundant copies of data that is already stored in the tables that it references (which are sometimes called the base tables of the view).To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view.The information displayed by this view is meaningful only for inherently updatable views.

Once stored, the view can be used again and again, rather than re-writing the same query many times.A view is actually a composition of a table in the form of a predefined SQL query.A view can contain all rows of a table or select rows from a table.Does it mean delete all employees that are in department LCR?Does it mean set to null the dept_code for employees in department LCR?For a view to be inherently updatable, the following conditions must be met: CREATE TABLE employee ( employee_no VARCHAR2(8), last_name VARCHAR2(25) NOT NULL, first_name VARCHAR2(10) NOT NULL, dept_code VARCHAR2(3) NOT NULL, active_flag VARCHAR2(1) DEFAULT 'Y', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE permission_code ( pcode VARCHAR2(2), pcode_description VARCHAR2(40) NOT NULL, mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_role ( dept_code VARCHAR2(3), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_permission ( employee_no VARCHAR2(8), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE dept_code ( INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('5001', 'Mark', 'Townsend', 'LCR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('3996', 'Dacko', 'Carol', 'ESR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('6842', 'Morgan', 'Daniel', 'ADM', 'Y'); INSERT INTO permission_code VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CL', 'CLASS CODES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CR', 'CREWS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CT', 'CREW TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ADM', 'ADMINISTRATION'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('COO', 'COORDINATOR'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESE', 'ELECTRICAL SERVICE'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESR', 'ELECTRICAL SERVICE REP'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ENG', 'ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('LCR', 'LINE CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('MCR', 'METER CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('NWE', 'NETWORK ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('SKA', 'SKETCH ARTIST'); INSERT INTO user_role (dept_code, pcode, access_level) SELECT r.dept_code, p.pcode, 'R' FROM dept_code r, permission_code p; INSERT INTO user_permission (employee_no, pcode, access_level) SELECT e.employee_no, r.pcode, r.access_level FROM employee e, user_role r WHERE e.dept_code = r.dept_code; COMMIT; CREATE OR REPLACE VIEW role_permission_view AS SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level FROM user_role r, permission_code p WHERE r.pcode = p.pcode; desc role_permission_view col data_type format a15 SELECT column_name, data_type, data_length FROM user_tab_cols WHERE table_name = 'ROLE_PERMISSION_VIEW'; col type format a30 SELECT column_name, , e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description FROM employee e, user_role r, user_permission u, permission_code p WHERE e.dept_code = r.dept_code AND e.employee_no = u.employee_no AND r.pcode = u.pcode AND r.pcode = p.pcode ORDER BY 1,3; desc employee_permission_view SELECT column_name, ON role_permission_view FOR EACH ROW DECLARE x INTEGER; BEGIN SELECT COUNT(*) INTO x FROM permission_code WHERE pcode = : NEW.pcode; IF x = 0 THEN INSERT INTO permission_code (pcode, pcode_description, mod_user_id, mod_user_date) VALUES (: NEW.pcode, 'New Code', USER, SYSDATE); END IF; SELECT COUNT(*) INTO x FROM dept_code WHERE dept_code = : NEW.dept_code; IF x = 0 THEN INSERT INTO dept_code (dept_code, dept_name) VALUES (: NEW.dept_code, 'New Dept'); END IF; INSERT INTO user_role (dept_code, pcode, mod_user_id) VALUES (: NEW.dept_code, : NEW.pcode, 'Morgan'); INSERT INTO test (test) VALUES ('Z'); END ioft_insert_role_perm; / SELECT * FROM permission_code WHERE pcode = 'DM'; SELECT * FROM dept_code WHERE dept_code = 'DAN'; SELECT * FROM user_role WHERE dept_code = 'DAN'; SELECT * FROM test; ON role_permission_view FOR EACH ROW BEGIN UPDATE user_role SET access_level = : NEW.access_level, mod_user_id = USER, mod_user_date = SYSDATE WHERE dept_code = : OLD.dept_code AND permission_code = : OLD.permission_code; END ioft_role_perm; / SELECT trigger_name, trigger_type, action_type, description FROM user_triggers; SELECT * FROM employee_permission_view; UPDATE role_permission_view SET access_level = 'W' WHERE dept_code = 'SKA'; SELECT * FROM employee_permission_view; UPDATE employee_permission SET access_level = 'Z';/* what does it mean to delete LCR from employee_permission_view?

129

Leave a Reply