By using instead of trigger we can execute insert command on a complex view Ex

By using instead of trigger we can execute insert

This preview shows page 99 - 103 out of 108 pages.

By using instead of trigger we can execute insert command on a complex view Ex : Page 99 of 108
Image of page 99
COMPLEX VIEW Create view vv1 As select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc from emp e1 dept d where e.deptno = d.deptno; View created Select * from vv1; * Instead of trigger example : Create or replace trigger trg9 instead of insert on vv1 for each row Begin Insert into dept values (:new.deptno, :new.dname, :new.loc); Insert into emp(empno, ename, sal, deptno) values (:new.empno, :new.ename, :new.sal, :new.deptno); END; / Ex : insert into vv1 values (555,’ddd’,2000,60,’ADMIN’,’HYD’); --- valid Note : :new and :old qualifiers can be used only in the row level trigger. * Abstract datatypes : Abstract data types are consists of one or more subtypes. Rather than being constrained to the standard oracle data types of number, date and varchar2 data types can more accurately describe your data. Ex : SQL>create type address_ty5 as object (street varchar(20), city varchar2(10), state char(10), pin number); / Type created SQL> create type person_ty5 as object (name varchar2(20), Address address_ty5); / Page 100 of 108
Image of page 100
Type created SQL> create table customer5 (customer_ID number(3), Person person_ty5); SQL> insert into customer5 values (1,person_ty5(‘hari’,address_ty5(‘#102 lokhanadala’,’mumbai’,’MH’,10101))); SQL> select customer_ID, c.person.name from customer c; * Nested table : Nested table is a collection of rows, represented as a column with in the main table. For each record with in the main table, the nested table may contain multiple rows. In one sense, it’s a way of storing a one-to-many relationship with in one table. SQL> create or replace type emp_ty5 as object (desg varchar2(20), dname varchar2(20), doj date); / Type created SQL> created type emp_nt5 as table of emp_ty5; / Table created SQL> created table emp data5 (ename varchar2(20), details emp_nt5) nested table details store as emp_nt_tab5); Table created SQL> set describe depth2 SQL> desc empdata5 Name Type Page 101 of 108
Image of page 101
Ename Varchar2(20); Details Emp_nt Desg Varchar2(20) Dname Varchar2(20) Doj Date SQL> insert into empdata5 values (‘Raju’, emp_nt5(‘clerk’,’sales’,’12-sep-05’), emp_ty5(‘Asst’,’mrket’,’15-oct-04’), emp_ty5(‘mngr’,’sales’,’13-aug-09’))); SQL> select * from emp data5; * VARRAYS : varrays can also be used to create one-to-many relationship with in the table. * creating varray : Ex : create type dependent_brithdate_t5 as varray(10) of date; Using varray in table : Create table employee5( id number, name varchar2(20), dependent ages dependent-brithdate-t5); inserting row into table : insert into employee5 values(42,’Arun’, dependent_brithdate_t5 (’12-jan-1765’,’04-jul-1977’,’11-mar-2021’); *** Differences between nested tables and varrays *** Nested tables Varrays 1. There is no restriction on size. 1. We need to define the maximum size. 2. Data is stored in special auxiliary 2. Data is stored inline to the rest of the tables called as store tables. table data.
Image of page 102
Image of page 103

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture