Database Reference
In-Depth Information
We always want to have a view of our table that is our primary reference to the table, so we create
one with the code in Listing 9-9. If we are granting and using a view, then we can change the table (or
tables) it refers to while maintaining the view, and keep from breaking any code.
Listing 9-9.
Create a View of Employee Mobile Phone Numbers Table
CREATE OR REPLACE VIEW v_emp_mobile_nos AS SELECT * FROM hr.emp_mobile_nos;
INSERT INTO hr.v_emp_mobile_nos
( employee_id, user_id, com_pager_no, sms_phone_no, sms_carrier_cd )
VALUES ( 300, 'OSUSER', '12345', '8005551212', 'Verizon' );
COMMIT;
Insert mobile numbers for our example user ID, OSUSR, into the table. Note that
employee_id
300 is
what we forced when we updated the sequence and inserted our example user in Chapter 7. While we're
here, let's also insert a record for you. Use an insert statement like that just given, substituting the
user_id
you use to log in (perhaps to Windows) and your pager number, cell phone number, and carrier
code (whatever name you will use for all cell phones from the same provider you use). You will also need
to insert a record in the
HR.EMPLOYEES
table for you (first name, last name, and e-mail address.) Use the
commands in Listing 9-10 as templates.
Listing 9-10.
Template Commands to Create Employees and Add Their Mobile Numbers
INSERT INTO hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(hr.employees_seq.NEXTVAL, 'First', 'Last', 'EMAddress',
'800.555.1212', SYSDATE, 'SA_REP', 5000, 0.20, 147, 80);
INSERT INTO hr.v_emp_mobile_nos
( employee_id, user_id, com_pager_no, sms_phone_no, sms_carrier_cd )
VALUES ( (
select employee_id from hr.employees where
first_name = 'First' and last_name = 'Last'
), 'UserID', '12345', '8005551212', 'Verizon' );
COMMIT;
Be sure to
COMMIT
the inserts and updates you've made in order to make them visible to other
sessions and other users.
