CASE()| Multiple inserts| MERGE| How to create user defined datatype & Table

 

CASE Function

Ø  It can be used to perform if then else logic in SQL.

Ø  These are two types

1)     Simple case expression

2)     Search case expression

Ø  1. Simple case expression

Select empno, ename, CASE deptno

When 10 then ‘ACCOUNTING’

When 20 then ‘RESEARCH’

When 30 then ‘SALES’

When 40 then ‘OPERATION’

Else ‘others’

End Department_name from emp;

Ø  2. Search case expression

Select empno, ename, job, sal,

Case

When job=’MANAGER’ then ‘MGR’

When sal=3000 then ‘hipay’

When job=’SALESMAN’ then ‘sman’

Else ‘not specified’

End Employee_name from emp;

Multiple inserts

Ø  Insert all

When deptno<20 then into d1 values (deptno, dname, loc)

When deptno>20 and deptno<=30 then into d2 values (deptno, dname, loc)

Else into d3 values (deptno, dname, loc)

Select * from dept d;

 

Commit;

DML MERGE statement

à It is used to select rows from one or more sources for update or insert into one or more tables at a time.

à Merge into EBONUS b

USING (select empno, sal, deptno from emp where deptno=30) s ON (b.empno=s.empno)

When matched then

Update set b.bonus=b.bonus+s.sal*0.1

Delete where s.sal>4000

When not matched then

Insert (b.empno, b.bonus) values (s.empno, s.sal*0.1)

Where s.sal<=4000;

Commit:

CHAPTER 8-Object oriented concepts in oracle

Creation of user defined object data type

Ø  Create or replace type addr_type 

As  

Object (Hno varchar2(30), Streetname varchar2(30), City varchar2(20), Pincode number (7));

Ø  Create or replace type PF_type

As

Object (PFnumber number (20), Amt number (12,2));

Ø  Above statements create the user defined object datatype as addr_type, PF_type in the data dictionary called user_types.

Ø  These datatypes are also called as collection in oracle. This collection is reusable when ever the same datatype collection is required by the project.

Ø  Creating table with user defined object data type

Create table Employee (Ecode number (3), Name varchar2 (30), Address addr_type, Basic number (12,2), PF PF_type);

Ø  How to Insert data into table Employee with user defined object datatype

Insert into Employee (100, ‘kamal’, addr_type (12, ‘Reddynagar’, ‘Vinukonda’, 522647), 8000, PF_type (1564235,450));

Commit;

Ø  How to select data from object table

Select * from Employee;

Select Address from Employee;

Select e.Address, e.Address.city from Employee e;

Ø  How to update data in object table

Update Employee e set Basic=Basic+5000 where e.Address.city=’Vinukonda’;

Ø  How to delete data in object table

Delete from Employee e where e.Address.city=’Vinukonda’;

Creating user defined object table

Ø  Create type Info as object (Code number (10), Name varchar2(30), Hno varchar2(30), Street varchar2(30), City varchar2(20), Pin number (7));

Ø  Create table Emp_info of Info;

Here Info is object by using this we can create Emp_info table.

Ø  Inserting values into object table

Method 1

Insert into Emp_info values (1001, ’Ramu’, ‘2-34’, ‘SR Nagar’, ‘Hyderabad’, 522001);

Method 2

Insert into Emp_info values (Info (1002, ‘Raju’, ‘1-102’, ‘SR Nagar’, ‘Hyderabad’, 522001));

REF () clause

Ø  It is used to create reference to existing row object. It is like link to display result.

Ø  Select REF (e) from Emp_info e where City=’Hyderabad’;