Post date: Feb 16, 2011 12:21:48 PM

Introduction

gr/* This is comment*/

--  This is another way of writing a comment

-- DQL

-- oracle Rows- Case senstive , colum name not case sensitive

Return in ‘C- Language’

DDL( create, alter , truncate, drop)

DML( insert, delete, update, merge)

DQL (select)

TCL(Grant, commit, rollback)

Select  first_name, salary from employees     // This is project in Relational algebra

select employee_id as "Employee No", last_name Surname, salary pay from employees 

 /*Give heading to the selected items

-- when you maintain space

-- as has optional

Select first_name || ' '|| last_name  from employees

Select first_name || ' '|| last_name Employee_Name  from employees // without Space

Select first_name || ' '|| last_name "Employee Name"  from employees // With space

-- Remove Duplicates

select distinct job_id from employees

19 rows selected.

-- Composite  entire combination is unique

select distinct job_id, department_id from employees;

-- If you Pk in table then

Select * from employees = select Distinct * from employees -- Both are give same output

-- Order by clause

-- this will display date in descending order of salary , Default is assending order

-- to make descending

select * from employees order by salary desc

-- if emp have same department id then it give preference to salary for descending order

select last_name, department_id, salary from employees order by department_id, salary

--order by

-- here order by 2 decide which value from the selected value

Select last_name, department_id, salary from employees order by department_id, salary;

select last_name, department_id, salary from employees order by 2;

-- If you have NULL value in descending then, I will comes last in the table

-- NULLs appear in last in order of colums

 Where Clause – filter the data produced by select

select last_name, department_id, salary from employees where salary>10000

-- between to achieve two condition ( Include boundary Operator)

select last_name, department_id, salary from employees where salary between 5000 and 8000

select last_name, department_id, salary from employees where salary >=5000 and salary <= 8000

-- Opposite of above table

select last_name, department_id, salary from employees where salary NOTbetween 5000 and 8000

-- Or Operator has to used to getting different things

select * from employees where employee_id =101 or employee_id= 105

(or)

select * from employees where employee_id in (101,105)

select * from employees where employee_id =101 or employee_id= 105

-- No rows are select because 101 not equal to 105

-- Date in between

select * from employees where Hire_date between '1-Jan-99' and '31-Dec-99'

-- like operator used for pattern matching

-- makes use of wild cards %_

-- Display emps whose first_name begins with d

select * from employees where first_name like 'D%' --% means 0 or more

select * from employees where first_name like '%e_' --_means one char only

-- name have 3 char

Select * from employees where first_name like ‘___’

--first name have 2nd char and last before char is ‘a’

select * from employees where first_name like '_a%a_'

-- if data have % sign like 50%

Select * from students_data where percentage like ‘%!%’ escape ‘!’;

-- if percentage like 50.66%

Select * from students_data where percentage like ‘%!%%’ escape ‘!’;

-- Calulate the annual compensation of each employee who receives a yearly bonus of 1000?

select first_name, salary, (salary*12)+1000 Annual from employees;

-- Calculate the annual compensation of each employee who receives a monthly bonus of 100?

select First_name, salary , 12*(salary+100) Monthly from employees;

-- And > OR

select last_name, job_id, salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;

select last_name, job_id, salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000;

-- null operator

select * from employees where commission_Pct is NULL;

-- not null

select * from employees where commission_Pct is not NULL;

or

select * from employees where not commission_Pct is NULL;

-- Literals apostitary problem (‘) add  one more

select first_name || ',It''s Manager_ID is '|| Manager_id from employees

--But in 10g we had another one solution by adding x] (quote)

select first_name || q'[ ,It's Manager_ID is ]'||Manager_id from employees

-- subsitution Var

select employee_id, last_name, salary from employees where salary> &Salary

select &c1, &c2 from &tab where &conditon

-- Set Off: don’t ask new and old

-- Set on: ast new and old

select &col, &c2 from &&tab

Functions

select months_between('01-sep-95','11-jan-94') from dual;

-- to convert character types

select to_char(sysdate, 'DDTH MONTH YYYY') from dual;

select to_char (salary, '$99,999.00') salary from employees where last_name= 'Ernst';

6,000 to $6,000

Next _MONTH FIRST MONDAY:

SELECT NEXT_DAY (LAST_DAY(SYSDATE)+1, 'MONDAY') FROM DUAL;

To convert exact date:

select to_date('01-02-01','dd-mm-yyfm') from dual; 01-FEB-01 

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

select last_name, hire_date, next_day(add_months(hire_date,6), 'MONDAY') "Review Date",

decode(to_char(add_months(hire_date,6), 'DY'),

'MON' , ' Monday, the'|| initcap( to_char(add_months(hire_date,6), 'ddspth'))||' of ' || initcap (to_char(add_months(hire_date,6), 'MONTH'))|| ' , ' || to_char(add_months(hire_date,6), 'YYYY'),

'Monday, the'||

initcap (to_char(next_day(add_months(hire_date,6), 'MONDAY'),'ddspth'))||' of ' || initcap (to_char(add_months(hire_date,6), 'MONTH'))|| ' , ' || to_char(add_months(hire_date,6), 'YYYY'))

REVIEW

from employees

--NVL- NULL Value Substitution

select last_name,salary, commission_pct, nvl (commission_pct,0) as modified_commission from employees

-- DECODE works like IF-THEN-ELSE

select last_name, salary, job_id, decode (job_id,

'IT_PROG', salary*1.15,

'ST_CLERK', salary*1.05,

Salary) NEW_SAL from employees

-- CASE Expression

select last_name, salary, job_id,

case job_id

                when 'IT_PROG' then salary*1.15

                when 'ST_CLERK' then salary*1.05

 else salary

end  NEW_SAL

 from employees

select last_name, hire_date, to_char(hire_date, 'DY') from employees order by (hire_date,'DY'), 6)

Group Function (Multiple Rows)

-- DAY 2

-- Result in one value for set of values

select max(last_name) Total from employees

-- its takes ASCII values

--Group function

Max,

Min,

Avg

Sum

Count

-- select all the rows

select count(*) from employees;

--select valid rows

select count(salary) from employees;

--don’t count duplicate error

select count (distinct salary) from employees;

-- Group by clause

-- Group salary of each department

select department_id, sum(salary) from employees group by department_id order by department_id

select department_id, sum(salary) from employees group by department_id order by sum(salary)

Group … having

-- having is like filter it is used when we are  in group

select department_id, sum(salary)  from employees group by department_id having department_id in (10,30,60,90) order by department_id

-- it filter 10,30,60,90 row from the grouped table

-- first perform where operation,

-- then it perform having operation

select department_id, sum(salary)  from employees where department_id>30 group by department_id having department_id in (10,30,60,90) order by department_id

-- where not used in group filer, having is used for that

select department_id, sum(salary)  from employees group by department_id

having sum(salary)>20000 order by department_id

-- execution order

select department_id, sum(salary) from employees

where department_id<=80                      

group by department_id

having sum(salary)>10000

order by sum(salary)

--1. From

--2. Where

--3. Group by

--4. (Agg function in select)

--5. Having

--6. Order by

--7. (select)

Select which department have maximum average salary (nested function)

select max(avg(salary)) from employees group by department_id

--selected valued should be group one, else its shows error

select department_id, job_id, sum(salary) from employees

group by department_id, job_id

order by department_id, job_id

/*columns that we select apart from group by function must be specified in group by clause, Reverse is not true*/

Joins

-- Display date from more than one table

Employees

Departments

Types

Natural Join

Inner join

Cross join

Outer join

            (Left outer join, Right outer join, full outer join)

Self join

Syntax:

Select table1.column, table2.column

From table1 join table2

on join_condition

--Inner Join (Normal) Default join

select employees.employee_id, departments.department_Name

from employees join departments

on employees.Department_id=departments.department_id

-- Using ALAIS

select e.employee_id, d.department_Name

from employees e

join departments d

on e.Department_id=d.department_id

-- Natural Join (Automatically system matching the two tables)

-- Nothing we can change this query

select E.employee_id, E.Last_name, D.Department_name from employees E Natural Join departments D;

(or)

select E.employee_id, E.Last_name, D.Department_name

from employees E Inner Join departments D

on e.department_id=d.department_id

and e.Manager_id=D.manager_id;

-- Joins By using clause ( Replace on condition)

select E.employee_id, E.Last_name, D.Department_name

from employees E Inner Join departments D

Using (department_id)

(or)

select e.employee_id, d.department_Name

from employees e

join departments d

on e.Department_id=d.department_id

--Important Alert Department_id SHOULD NOT HAVE ANY PREFIX IN SELECT

-- if making use of USING CLAUSE

select E.employee_id, E.Last_name, D.Department_name, department_id from employees E Inner Join departments D

Using (department_id, Manager_id)

-- if both table colum name is not same, then natural join is not working, but other’s are works

--LEFT OUTER JOIN( all data from left table, and some data  match with right table)

select E.last_name,D.department_name FROM employees E

LEFT OUTER JOIN departments D

ON E.department_id = D.department_id

select E.last_name,D.department_name FROM employees E

RIGHT OUTER JOIN departments D

ON E.department_id = D.department_id

select E.last_name,D.department_name FROM employees E

FULL OUTER JOIN departments D

ON E.department_id = D.department_id

-- Full outer join is called as 2 sided outer join

Equi join-> if a join condition use ‘=’ sing

Non equi join-> if join condition use any other than

‘=’

Cross Join

 --Products

select last_name, Department_name from employees

cross join departments;

--Joining 3 tables

select E.Employee_id, L.City, D.department_name

from employees e

join departments d

on e.department_id=d.department_id

join locations l

on d.location_id=l.location_id

-- selef join (Same Tabel)

select e.employee_id EID, e.first_name EMPLOYEE,

m.employee_id MID, m.first_name MANAGER

from employees e join employees m

on e.manager_id=m.employee_id

// ANSI where in join

select e.employee_id, d.department_Name

from employees e

join departments d

on e.Department_id=d.department_id

where d.department_id>100

(or)

//Non ANSI

select e.employee_id, d.department_Name

from employees e ,

departments d

where e.Department_id=d.department_id

and d.department_id>100

-- Sub queries

A subquery is a Select query that is nested inside a SELECT, INSERT UPDATE, or DELETE Statement, or inside another subquery.

select e.employee_id, e.salary, d.department_name from employees e

join departments d

on (e.department_id = d.department_id)

where e.salary =(select max(b.salary) from employees b where b.department_id = d.department_id);

select e.employee_id, e.salary from employees e

where e.salary =

  (select max(b.salary)

  from employees b

  where b.department_id =

    (select d.department_id

    from departments d

    where d.department_id  = e.department_id));

--Syntax

Select col1, col2 from table_name where col1=(Select Query)

--If query produce more than 1 value then its showed error

--Find the name of the person who has first joined the organization

select first_name from employees

where hire_date =(

                                                 select min( hire_date) from employees

                                                )

-- Find the 2nd highest salary of employees

select last_name, salary  from employees

where salary= (

                                select max(salary) from employees

                                where salary < (

                                                                                select max(salary) from employees

                                                                )

                                                )

Type of subqueries

select employee_id, last_name from employees where salary=(

select min(salary) from employees group by department_id)

It show’s error inner department id is have more value

select department_id, min(salary) from employees group by department_id

having min(salary)>

(select min(salary) from employees where department_id=50)

-- Sub query with Exists( return YES or NO)

-- if inner query return data the it returns  yes,

select * from employees where exists(select * from departments where department_name='IT2')

Pairwise comparision subquery

select first_name, employee_id, manager_id, department_id from employees where (manager_id, department_id)

in (select manager_id, department_id from employees where first_name='John')

18 rows selected.

If I want filter john means    

15 rows selected.

--Non_Pairewise comparision subquery

select first_name, employee_id, manager_id, department_id from employees

where manager_id in (select manager_id from employees where first_name='John')

and  department_id in (select department_id from employees where first_name='John') and first_name <>'John';

20 rows selected.

Find the name of employees whose salary is greater than the avg. salary of all employees.

select ename, sal from emp_data where sal> (select avg(sal) from emp_data)

--Find the names all employees whose salary is greater than the avg. salary of their respective --department

select avg(sal), deptid from emp_data group by deptid;

select ename, sal, deptid from emp_data where sal> any (select avg(sal) from emp_data group by deptid)

--Correlated Subquery(depends on row available in outer query)(Repeating sub query)

Ø  Correlated subqueries are used for row-by-row processing.

Ø  Each subquery is executed once for every row of the outer query.

Find all employees who earn more than the average salary in their department.

select * from emp_data e

where sal> (select avg(sal) from emp_data where deptid=e.deptid)

Display details of those employees who have changedjobs at least twice.

SELECT e.employee_id, last_name,e.job_id

FROM employees e

WHERE 2 <= (SELECT COUNT(*)

FROM job_history

WHERE employee_id = e.employee_id);

--with (split the query)

with test_query

as

(select employee_id, last_name, salary, salary+2000 as new_sal from employees)

select last_name, salary, new_sal, new_sal*12 annual_sal from test_query

--create table

create table myBoo (Eid varchar2(10),

Ename varchar2(50),

salary number,

dob date

)

create table myboo1 as select * from myboo

--Alter table

Alter table myboo add(Phone number(10))

--insert values

insert into myboo values ('&EID','&Ename',&sal,'&date',&Phone);

--Update

update myboo set salary=100000 where  eid='e4';

--commit

Save the work permanently in database

 --Rollback

Rollback

--Merging

--Combination of insert and update on a single table

merge into boo_a a

using boo_b b

ON (a.pname = b.pname)

 when matched then

update set a.qty=a.qty+b.qty

when not matched then

insert (a.pname, a.cost, a.qty) values

(b.pname, b.cost, b.qty);

--constraints

Primary key

Foreign key

Unique

Check

Not null

--Char is faster than varchar

--Dept_HC (parent)

Create table boo_dhc (did varchar2(5) constraint dpk primary key, dname varchar2(50))

--boo_ehc(child)

create table boo_ehc

(eid varchar2(5) constraint bepk primary key,

ename varchar2(20) constraint benull not null,

salary integer constraint bschk check (salary>25000),

mobile integer constraint bmuni unique,

gender char(1) constraints bgchk check( gender in ('M','F')),

Nationallity varchar2(10) default 'Indian',

ManagerID varchar2(5) constraints mfk references boo_ehc(eid),

deptID varchar2(3) constraint bdfk references boo_dhc(did))

-- constraints can be defined at

--column level

--Table level

-- To create composite primary key using table level constraint

create table boo_pord (pid varchar2(5),

oid varchar2(5),

constraint bcpk primary key(pid, oid));

--Alter table

alter table boo_dhc add deptsize integer

alter table boo_dhc modify (did varchar2(10));

alter table boo_dhc modify (did varchar2(1));

-- too small

alter table boo_dhc modify (did char2(10));

-- column type incompatible with referenced column type

-- Remove column

alter table boo_dhc drop column deptsize

-- constraint disable

alter table boo_ehc disable constraint bmuni;

alter table boo_ech drop constraint bmuni;

-- add keys

alter table boo_ehc add constraint dfk foreign key (did) references boo_dhc (deptid);

--delete

Delete boo_ehc

-- DML command need to have backup,

--Rollback is possible

--Delete can remove specific row or all rows

--Can have where clause

-- truncate

Truncate boo_ehc

-- DDL faster than delete,

--rollback is not possible,

 --truncate removes all rows from table

--cannot have where clause

--Create and copy all the data from employees;

Create table boo_emp as select * from employees

--If both table exist then only it will work

Insert into boo_emp select * from employees

--create table with out data.. i.e. only data structure

create table t1 as select * from employees where 1=0; -- (user false condition)

--Drop

Drop table t1;

Recylebin

show recyclebin

select * from "BIN$0L9ycGVlTAabiDJs0axUfA==$0"

--Flashback:

--Allows viewing or rewinding of data

--It will help to undo the wrong things that have happened.

--Advantage:

--Correcting user errors

--Recovering lost/corrupted data

flashback table t1 to before drop

-- suppose you create same table in database means, then use following database

Flashback table t1 to before drop rename

to t2;

--drop table permanent

Drop table t1 purge; --permanently remove

Drop table t1;

Purge table t1; --removes obj from bin also

Important hints

Not possible to perform ddl/dml in recyle bin

Constraints are get lossed;

DDL( create, alter , truncate, drop)

DML( insert, delete, update, merge)

DQL (select)

TCL

Transactions: (TCL)

--Group of activity should perform for single work to done

--ACID properties

-- A: Atomicity (Do all or nothing)

--C: Consistency (consistently updated both the side)

--I: Isolation (Each transaction should occurred with disturbed of other)

--D: Durability (Ensure)

Transaction begin with DML comments,

Ends with commit, rollback, DDL comment, automatically ends the transaction

--TCL( commit, rollback, savepoint)

update boo_dhc set did='D100' where did='D1'

--perform the transaction in block

savepoint s1;

update boo_dhc set did='D200' where did='D2'

--partial rollback

Rollback s1;

complete rollback till last

rollback;

when you change DML to DDL the automatically insert commit;

select * from boo_dhc;

insert into boo_dhc values ('D3','Finance');

 alter table boo_dhc add dsize integer;

insert into boo_dhc values ('D4','sales', 10);

 Rollback;

Used

--You use the SETUNUSED option to mark one or more columns as unused.

alter table boo_1 set unused column college

--drop unused column

alter table boo_1 drop unused column

Create view (same as employees table .. it have all the constraints)

create view eview as select * from employees where salary >6000;

--Rowid, Rowid used when you want to delete target rows.

select rowid, rownum, did, dname from boo_dhc

--View Virtual table

Referred as stored query

Adv:

Data Hiding

They do not store data

security

Can have different views of the same data.

Complexity hidden(Working with complex queries simplified)

--Syntax:

--Create view viewname as

--(Query)

create view bview as

(select * from boo_emp where salary >20000)

select * from bview;

-- DML operation is possible in view

update bview set id=101 where dept_id=100;

insert into bview values (120, 'Siva',980000,100, 12.01)

 Any changes done in view will be reflected in view & table both

& viceversa true;

Incase in view I have only two column out of 5 means, still I can update the views

insert into bview values (121, 20000);

select * from boo_emp

Check option

Create or replace view bview as select id, salary from boo_emp where salary >3000

with check option

insert into bview values (125, 2000)

ORA-01402: view WITH CHECK OPTION where-clause violation

insert into boo_emp values (126, 'Boobalan',2000, 100, 12.20)

-- it will not application in parent table

Force to create view without table

create or replace force view bb as select * from bbbbb

Warning: View created with compilation errors.

Select * from bb;

view "HR.BB" has errors

in feature I will create table bbbbb it will works properly

 Read only View

create or replace view bview as

(select * from boo_emp where salary >20000)

with read only

View are classified as

simple (view created with one base table)

Complex query: (More than one base table)

DmL cannot be performed on complex view if it affects more than one base table

create or replace view bcmx as

select e.last_name, d.department_name

from employees e inner join departments d

on (e.manager_id=d.manager_id)

If you delete table which have the view

It shows the error view "HR.BVIEW" has errors

But it will active when you create the same table again….

Sequences

Used to generate unique numbers automatically..

Used for auto numbering

Designed for specific schema

--------------------------------------

create sequence booseq

increment by 2

start with 100

maxvalue 105

cycle

insert into boos values (booseq.nextval, 'Siva', 20000);

insert into boos values (booseq.nextval, 'mmm', 3000);

insert into boos values (booseq.nextval, 'mirror', 4000);

insert into boos values (booseq.nextval, 'ss', 1000);

select * from boos;

Set Min Value:

create  sequence booseq1

increment by 2

start with 100

maxvalue 105

minvalue 95

cycle

nocache

truncate table  boos;

insert into boos values (booseq1.nextval, 'Siva', 20000);

insert into boos values (booseq1.nextval, 'mmm', 3000);

insert into boos values (booseq1.nextval, 'mirror', 4000);

insert into boos values (booseq1.nextval, 'ss', 1000);

9 rows selected.

Current sequential number:

select booseq1.currval from dual;

101

select booseq1.nextval from dual;

103

Synonym:

Alternative native for object

create synonym sboos for boo_emp

can I create more than 1 synonym for same object—Yes

select * from sboos

select * from boo_emp both are same

Drop the table , what happen to synonym?

Syn translation no long valid

Rename

Rename the column

alter table boo_ehc rename column ename to name;

Data dictionary Table

Show s all the views available in the database

select * from user_views

show sequences list

select * from user_sequences

synonyms list

select * from user_sysnonyms

select * from user_tables

Attribute of constraints

select * from user_constraints;

Its show position of column in that particular table

select * from user_cons_columns;

Meta data tabl0065 list with comments

select * from dictionary

Views, trigger, view and the object

select * from user_objects

Union (doesn’t allow duplicates)

Select list should be same in both the table

select employee_id, job_id from employees

union

select employee_id, job_id from job_history

Union All  (allow duplicates)

select employee_id, job_id from employees

union all

select employee_id, job_id from job_history

force without column name

select employee_id, job_id from employees

union

select employee_id, null  from job_history

order will come atlast when you dealing with union

select employee_id, job_id A

from employees

union all

select employee_id, job_id b

from job_history

order by employee_id asc

Intersect

select employee_id, job_id A

from employees

intersect

select employee_id, job_id b

from job_history

order by employee_id asc

Minus

The minus A query – B query

select employee_id, job_id A

from employees

Minus

select employee_id, job_id b

from job_history

order by employee_id

select 'sing' as "My Dream", 3 a_dummy from dual

union

select 'I''D like to teach', 1 a_dummy from dual

union

select 'The world to', 2 a_dummy from dual;

Column a_dummy noprint;

ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted

•ON DELETE SET NULL: Converts dependent foreign key values to null

Operation : RollUp & Cube()

Sub total and Grant total

Report preparation

select department_id, job_id, sum(salary) from employees

group by rollup (department_id, job_id) order by 1,2;

select department_id, job_id, sum(salary) from employees

group by cube (department_id, job_id)

Grouping

 Grouping is used to identify whether that null values is

belongs to original table or because of rollup

It return 0 when having values = Table have null

Return 1 if it is null == Roll up create null

select department_id, job_id, sum(salary),

grouping(department_id) GRP_DEPT,

grouping(job_id) GP_JOB

from employees

where department_id<50

group by rollup (department_id, job_id)

11 rows selected.

Group set

select department_id, null, job_id, sum(salary)

from employees

where department_id <30

group by department_id, job_id

union all

select null, manager_id, job_id, sum(salary)

from employees where department_id<30

group by job_id, manager_id

order by department_id, job_id;

(or)

select department_id, manager_id, job_id, sum(salary) from employees where department_id < 30 group by

grouping sets((department_id, job_id), (job_id,manager_id))

order by department_id, job_id;

Multi table inserts (managing large datasets)

Mostly used in data warehousing applications better performance

Normal

insert into bddd select * from employees

Insert All

Sysntax:

Insert into target1 select * from source

Types;

1. Uncondition Insert all:x

--create table bsal (empid number(6), hiredate date, sal number(8,2));

--create table bmgr (empid number(6), mgrid number(6), sal number(8,2));

--insert all

--into bsal values(employee_id, hire_date, sal)

--into bmgr values(employee_id, Manager_id ,sal)

--select employee_id, hire_date, manager_id , salary sal

--from employees

--where employee_id >200;

--commit;

--select * from bsal;

--select * from bmgr;

IT Conditional Insert all

insert all

when sal>10000 then

into bsal values (employee_id, hire_date, sal)

when manager_id> 200 then

into bmgr values (employee_id, manager_id, sal)

select employee_id, hire_date, manager_id, Salary sal

from employees

where employee_id> 200;

Insert first

insert first

when sal>10000 then

into bsal values (employee_id, hire_date, sal)

when manager_id> 200 then

into bmgr values (employee_id, manager_id, sal)

select employee_id, hire_date, manager_id, Salary sal

from employees

where employee_id> 200;

Pivot insert

Transform the date row format to column table enable different view for analyzing date;

--creating source

create table bsale (

eid integer,

week_id integer,

s_mon integer,

s_tue integer,

s_wed integer,

s_thu integer,

s_fri integer);

insert into bsale values (1,1, 1000, 2000, 3000, 1500, 500);

insert into bsale values (2,1, 1000, 2500, 1000, 1500, 200);

--creating target

create table bsum

(eid integer,

week_id integer,

sales integer

);

insert all

into bsum values (eid, week_id, s_mon)

into bsum values (eid, week_id, s_tue)

into bsum values (eid, week_id, s_wed)

into bsum values (eid, week_id, s_thu)

into bsum values (eid, week_id, s_fri)

select * from bsale;

System privilege

System privilege – create

Create user user_name

identified by password;

grant privilege [privilege name ]

to user [user|role, public..]

Create session

Create table

Create sequence

Create view333m

Create procedure

-- Scott able to perform following job only

Grant create session, create table,

                create sequence, create view

to scott;

Role

Create role manager;

Grant create table, create view

to manager;

Grant manager to bell, kochhe;

Chaning the password;

Alter user hr identified by password

Object privileges;

Grant object_pive

on Object

to {user|role|public}

[with grant option]

Grant select

on employees

to sue, rich;

Update:

Grant update (department_name, location_id)

on departments

to scott, manager;

Revoke

Opposite to grant

Revoke {privilege}

on object

from {user|role|public}

[cascade constraints];

Revoke select, insert

on departments

from scott;

select * from boo_dhc

grant select on boo_dhc to hr;

Logout:

Login HR

select * from scott.boo_dhc

synonym

create synonym bd for scott.boo_dhc

select * from bd

Logout:

Login Scott:

Revoke

revoke select

on boo_dhc

from hr;

NVL – it accepts 2 paramets.

If the 1st parameter is null then

It returns the 2nd;

NVL2 it accepts 3 parameters.

If the 1st parameter is null then

It returns the 3rd else the 2nd.

select last_name, salary, commission_pct,

nvl(commission_pct, 0),

nvl(commission_pct, 0)+salary Total,

nvl2(commission_pct, 'Sal+comm','comm') nvl2

from employees;

NULL IF ( ar1, ar2)

If ar1==ar2 retrun null

Else retrun ar1;

select first_name"expr1", last_name "expr2",

nullif (length(first_name), length(last_name)) REsult from employees

Can we drop a parent table, if there is a child table?

NO..

Disabel foreign key constraint in child

or

drop table parent cascade constraints;

Or

Drop table parent cascade constraint;

If you want to update one thing instead of other which is already exists in parent and child table

Default constraints;

set constraints all deferred; (allowed pk , fk values)

update  bbdt set deptno=50 where deptno=10;

but when we give commit that time pk should match with fk, else it show the error…

Monday, July 26, 2010

Create index index_name

ON table_name (column1, column2,…);

Alter

Start Monitoring

Alter index index_name monitoring usage;

Alter index index_name nomonitoring usage;          

Select * from v$object_usage;

Execution plan Tuesday, July 27, 2010

--SET AUTOTRACE ON;

--SET TIMING ON;

SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE employee_id =200;

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    16 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    16 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPLOYEE_ID"=200)

Statistics

----------------------------------------------------------

1 rows processed

Range Scan

SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE employee_id > 200;

6 rows selected.

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 603312277

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     6 |    96 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     6 |    96 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |     6 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPLOYEE_ID">200)

Statistics

----------------------------------------------------------

Order by some expensive cost

Rule base optimizer doesn’t have hash

SELECT last_name from employees;

create index job_dept

on employees(department_id, job_Id)

drop index job_dept;

Analyze statistic

Select * from user_tables;

ANALYZE TABLE employees

COMPUTE statistics

select table_name from user_tables where table_name='Employees';

------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                |     1 |   164 |     8  (13)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                 |                |     1 |   164 |     8  (13)| 00:00:01 |

|   2 |   NESTED LOOPS OUTER                |                |     1 |   160 |     7  (15)| 00:00:01 |

|   3 |    NESTED LOOPS OUTER               |                |     1 |   152 |     6  (17)| 00:00:01 |

|   4 |     NESTED LOOPS OUTER              |                |     1 |   147 |     6  (17)| 00:00:01 |

|   5 |      NESTED LOOPS                   |                |     1 |   136 |     5  (20)| 00:00:01 |

|   6 |       NESTED LOOPS                  |                |     1 |   133 |     4  (25)| 00:00:01 |

|   7 |        MERGE JOIN CARTESIAN         |                |     1 |   103 |     3  (34)| 00:00:01 |

|*  8 |         HASH JOIN                   |                |     1 |    68 |     1 (100)| 00:00:01 |

|*  9 |          FIXED TABLE FULL           | X$KSPPI        |     1 |    55 |     0   (0)| 00:00:01 |

|  10 |          FIXED TABLE FULL           | X$KSPPCV       |   100 |  1300 |     0   (0)| 00:00:01 |

|  11 |         BUFFER SORT                 |                |     1 |    35 |     3  (34)| 00:00:01 |

|* 12 |          TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    35 |     2   (0)| 00:00:01 |

|* 13 |           INDEX RANGE SCAN          | I_OBJ2         |     1 |       |     1   (0)| 00:00:01 |

|* 14 |        TABLE ACCESS CLUSTER         | TAB$           |     1 |    30 |     1   (0)| 00:00:01 |

|* 15 |         INDEX UNIQUE SCAN           | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |

|  16 |       TABLE ACCESS CLUSTER          | TS$            |     1 |     3 |     1   (0)| 00:00:01 |

|* 17 |        INDEX UNIQUE SCAN            | I_TS#          |     1 |       |     0   (0)| 00:00:01 |

|  18 |      TABLE ACCESS CLUSTER           | SEG$           |     1 |    11 |     1   (0)| 00:00:01 |

|* 19 |       INDEX UNIQUE SCAN             | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |

|* 20 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |     5 |     0   (0)| 00:00:01 |

|  21 |    TABLE ACCESS BY INDEX ROWID      | OBJ$           |     1 |     8 |     1   (0)| 00:00:01 |

|* 22 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |       |     0   (0)| 00:00:01 |

|  23 |   TABLE ACCESS CLUSTER              | USER$          |     1 |     4 |     1   (0)| 00:00:01 |

|* 24 |    INDEX UNIQUE SCAN                | I_USER#        |     1 |       |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Utility Plan

utlxplan

C:\oracle\product\10.2.0\rdbms

Explain plan

Explain plan

Set statement_id= 'QueryB'

Into Boobalan_plan_table

for select * from employees where department_id>20;

off the autotrace:

set autotrace off;

select * from Boobalan_plan_table;

display default recently excuted table

select plan_table_output from table(dbms_xplan.display('Boobalan_plan_table',null,'serial'));

PL/SQL Tuesday, July 27, 2010

PL- Procedural Language:

Ø  Write script to business logic

Ø  It is the procedural ( and sometimes object-oriented) programming extension to SQL, provided by Oracle, exclusively for Oracle

Ø  PL/SQL is a proprietary language not available outside the Oracle Database.

Ø  3GL that provides programming

Ø  Programming constructs including declaration, Loops, error handling, etc…

SQL doesn’t Support

Ø  Loop through records, manipulating them one at time

Ø  Keep code secure by offering encryption and storing code permanently on the server rather than the client handle exceptions.

Ø  Work with variables, parameters, collections, records, arrays, object, cursors, exceptions, etc..

Integration or procedural constructs with SQL

Imporved performance

Benefit of PL/SQL

Ø  Modularized program development

Ø  Integration with oracle tools

Ø  Portability

Ø  Exception handling

PL/SQL Block Structure:

DECLARE(optional)

Ø  Variables, cursors, user-defined exceptions

•BEGIN(mandatory)

Ø  SQL statements

Ø  PL/SQL statements

•EXCEPTION(optional)

Ø  Actions to perform when errors occur

•END; (mandatory)

Simple PL/SQL

Anonymous Block

begin

NULL;

end;

Block types:

Ø  Anonymous Block- Blocks which do not have name

Ø  Not stored in the database

Ø  They can call other programs, but they cannot be called themselves

Ø  Anonymous block are ideal for activities that you do not wish to repeat frequently

Ø  They are executed in session and cannot be called from another session.

Name Blocks (Opposite to anonymous block features)

Ø  Procedure

Dbms_output.put_line(‘Welcome’);

Ø  Function

Print the statements;

set serveroutput on;

begin

dbms_output.put_line('Muruga');

end;

With declaration

set serveroutput on;

declare v_name varchar2(30);

begin

v_name:=&'Name of User';

dbms_output.put_line('Hellow '|| v_name);

end;

 Get input from user

declare v_name varchar2(30);

begin

v_name:='&Name of User';

dbms_output.put_line('Hellow '|| v_name);

 end;  

input from table

begin

SELECT employees_id INTO eid

FROM employees

WHERE last_name ='Kochhar';

dbms_output.put_line(eid);

end;        

column name and variable same mean it will work

But don’t do that practically (should be kept different name)

declare employee_id number(6);

begin

select employee_id into employee_id

FROM employees

WHERE last_name ='Kochhar';

dbms_output.put_line(employee_id);

end;

Declaring variables with the %TYPE Attribute

Syntax (it has take data type of particular column automatically)

Var_name TableName.ColName%TYPE

set serveroutput on;

DECLARE

v_lastname employees.last_name%TYPE;

v_sal employees.salary%TYPE;

v_newsal v_sal%TYPE; --This can defined only after v_sal

BEGIN

SELECT last_name, salary INTO

v_lastname, v_sal

FROM employees

WHERE employee_id=100;

v_newsal:=v_sal+500;

dbms_output.put_line('Employee Name '|| v_lastname);

dbms_output.put_line('Employee Current Salary Rs.'|| v_sal);

dbms_output.put_line('Employee  New Salary Rs. '|| v_newsal);

End;

Write an anonymous block which takes deptid from user and display the highest sal for emp in that dept

DECLARE

v_did employees.department_id%TYPE;

v_sal employees.salary%TYPE;

BEGIN

V_did:= &Enter_DeptId;

SELECT department_id, max(salary) INTO

v_did, v_sal

FROM employees group by department_id having

department_id=v_did;

(or)

SELECT max(salary) INTO v_sal

FROM employees

WHERE department_id=v_did;

dbms_output.put_line('Employee DeptId '|| v_did);

dbms_output.put_line('Employee Maximum Salary Rs.'|| v_sAl);

End;

Guide Lines for working PL/SQL:

Ø  Follow naming conventions

Ø  Use meaning full names for variables.

Ø  Initialize variables designated as NOT NULL and CONSTANT;

Ø  Myname VARCHARA2(20):= ‘Jonhn’;

Ø  Myname VARCHAR2(20) DEFAULT ‘John’;

Bind Variables

Created with the VARIABLE  keyword

Ø  Outside of block also I can access that variable,

Ø  Referenced with a preceding colon

variable bb_emp number;

BEGIN

SELECT salary into :bb_emp

FROM employees where employee_id=178;

end;

/

select * from employees where salary=:bb_emp;

Auto Print is used print the output automatically

set autoprint on;

Controlling Flow of Execution(if …then… elsif.. then.. else… end if)

Set serveroutput on;

Declare v_sal number(10,2);

Begin

V_sal:=&salary;

If v_sal>15000 then –(it can execute more than one line)

dbms_output.Put_line ('Salary greater than 1500');

dbms_output.Put_line (‘2nd line to print’);

else

dbms_output.put_line('Salary less than 15000');

End if;

Dbms_output.put_line('This will happan');

End;

Set verify off;

Write a block which takes empid as user input and displays whether he is an expensive employee (sal>10000)

Set serveroutput on;

Set verify off;

Declare

v_sal number(10,2);

v_emp employees.last_name%TYPE;

v_check Boolean;

Begin

SELECT last_name, salary into v_emp, v_sal

from employees where employee_id= &Emp_id;

v_check:= v_sal> 10000;

if v_check then

dbms_output.Put_line (v_emp||' is an expensive employee salary is ='||v_sal);

else

dbms_output.Put_line (v_emp||'is an Non expensive employee salary is ='||v_sal);

End if;

End;

Null or un initialize variable as consider as empty

set serveroutput on;

declare

myage number;

chk varchar2(10):=NULL;

begin

if myage>11 then

dbms_output.put_line('I am a child');

else

dbms_output.put_line('I am not a child');

end if;

if  chk>11 then

dbms_output.put_line('I am a child');

else

dbms_output.put_line('I am not a child');

end if;

end;

output

I am not a child'

CASE

set serveroutput on;

declare

v_grade char(1);

BEGIN

v_grade:=UPPER('&Grade');

case v_grade

WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');

WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');

WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Average');

ELSE

DBMS_OUTPUT.PUT_LINE('Bad Input');

END CASE;

END;

Loops

Ø  Simple or basic loop(Post Test Loop)

Ø  When we want loop must happen at least once

Loop

Statements;

EXIT WHEN condition;

End loop;

WHILE LOOP(When I don’t know the iteration)

Ø  While loop (Pre Test Loop)

WHILE condition LOOP

Statements;

END LOOP;

FOR LOOP (Numbered Loop)(when I know the exact iteration)

For var IN LowerBound.. UpperBound LOOP

Statements;

END LOOP

Post-Test-LOOP

set serveroutput on;

DECLARE

v_counter number(3):=1;

BEGIN

  LOOP

    DBMS_OUTPUT.PUT_LINE(v_counter);

    v_counter:= v_counter+2;

    Exit when V_counter>5;

  END LOOP ;

END;

1,3,5

 WHILE LOOP

set serveroutput on;

DECLARE

v_counter number(3):=1;

BEGIN

   WHILE (v_counter<100) LOOP

    DBMS_OUTPUT.PUT_LINE(v_counter);

    v_counter:= v_counter+10;

  END LOOP ;

END;

1 11 21 31 41 51 61 71 81 91

FOR LOOP

set serveroutput on;

BEGIN

FOR v_counter in 1..10 LOOP

    DBMS_OUTPUT.PUT_LINE(v_counter);

  END LOOP ; 

END;

1 2 3 4 5 6 7 8 9 10

Ø  It allow initialize the object at loop;

Ø  It automatically increment by 1;

Ø  We can’t increment the count values

For loop with REVERSE

set serveroutput on;

BEGIN

FOR v_counter in reverse 1..10 LOOP

    DBMS_OUTPUT.PUT_LINE(v_counter);

  END LOOP ; 

END;

10 9 8 7 6 5 4 3 2 1

set serveroutput on;

DECLARE

v_outer number(3):=1;

v_inner number(4):=100;

BEGIN

  LOOP

  dbms_output.put_line('IN the outer loop');

  dbms_output.put_line(v_outer);

  v_outer:=v_outer+1;

  LOOP

  dbms_output.put_line('In the Inner LOOP');

  dbms_output.put_line(v_outer||v_inner);

  v_inner:=v_inner+100;

  EXIT WHEN v_inner>=300;

  END LOOP;

EXIT When v_outer>=5;

END LOOP;

END;

  IN the outer loop

1

In the Inner LOOP

2100

In the Inner LOOP

2200

IN the outer loop

2

In the Inner LOOP

3300

IN the outer loop

3

In the Inner LOOP

4400

IN the outer loop

4

In the Inner LOOP

5500

PL/SQL Date Types

Ø  Scalar (Single values)

Ø  Reference

Ø  Composite(Contain more than one values)

Ø  LOB

Find and explain different data types

Scalar

Ø  Character/ String

Ø  Number

Ø  BINARY_DOUBEL

Ø  BINARY FLOAT

Ø  BINARY_INTEGER

Ø  NUMBER

Ø  PLS_INTEGER

Ø  Boolean (accepts TRUE, FALSE and NULL values)

Ø  Date/Time (Include the DATE, TIMESTAMP, and INTERVAL type)

Ø  BINARY_DOUBLE, BINARY_FLOAT are New to 10g Realse1

BINARY_INTEGER is subtype of INTEGER

Ø  assigned values between - -2,147,483,647 to 2,147,483,647.

Ø  It was the only indexing datatype allowed for index-by tables before ora9i r2.

Ø  PLS_INTEGER is subtype of BINARY_INTEGER

Ø  again -2,147,483,647 to 2,147,483,647

Ø  PLS_INTEGER operations use machine arithmetic, so they are generally faster than NUMBER and INTEGER operations.

Ø   BINARY_INTEGER and PLS_INTEGER are identical in Ora10g.

Single Precision

The IEEE single precision floating point standard representation requires a 32 bit word, which may be represented as numbered from 0 to 31, left to right.

Ø  The first bit is the sign bit, S,

Ø  the next eight bits are the exponent bits, 'E', and

Ø  the final 23 bits are the fraction 'F':

S EEEEEEEE FFFFFFFFFFFFFFFFFFFFFFF 0 1 8 9 31

Double Precision

The IEEE double precision floating point standard representation requires a 64 bit word, which may be represented as numbered from 0 to 63, left to right.

Ø  The first bit is the sign bit, S,

Ø  the next eleven bits are the exponent bits, 'E', and

Ø  the final 52 bits are the fraction 'F':

S EEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 0 1 11 12 63

Ø  These types are generally used for scientific calculations

Ø  Results in better performance

Ø   

Ø  BINARY _DOUBLE; double precision floating-point type

Ø  BINARY_FLOAT: single precision floating-point type.

Ø  Boolean (accepts TRUE, FALSE, and NULL Values)

Ø  Date/Time(include the DATE, TIMESTAMP and INTERVAL type)

Different between those data types

Composite

Differ from scalar types in that they have internal components.

Contain multiple scalar variables that are referred to as attributes

Types

Ø  RECORDS

Ø  NESTED TABLE

Ø  INDEX-BY TABLES

Ø  VARRAYS

LOB

Ø  Oracle provide LOB(large object)data types to work with binary and character data upto

Ø  4GB  in oracle 10g

Ø  In 10gR1, LOB can store from 8 to 128 TB

Ø  The data stored in LOB’s can be manipulated piecewise where

Ø  LONG and LONG RAW must be manipulated in their entirety.

LONG and LONG RAW  data types are not recommended as of 10g

RECOMMENDED NU ORACLE

Ø  BINARY_INTEGER

PLS_INTEGER

BOOLEAN

BINARY_FLOAT

BINARY_DOUBLE

Composite Data Types:

Ø  PL/SQL records

Ø  PL/SQL collection

Ø  INDEX BY

Ø  NESTED TABLE

Ø  VARRAY

PL/SQL Records:

Ø  Data types: Records

Ø  A record type is a programming structure that mirrors a single row in a table

Ø  Record are structures.

Ø  Structure are set of variable types that are grouped together.

Defining Record Types;

Ø  Using %ROWTYPE attribute;

Ø  Using explicit definition in the declaration section of a PL/SQL program

Anchored Types:

%TYPE (map single column)

Ø  It anchors a variable to one column in the table

%ROWTYPE (map entire row of table)

Ø  In anchors the variable to all columns in the table

Assignment: when you create a variable, is that variable map all the constraints  in that  table column?  No, it copy only the data type

Fields in a record and corresponding columns in a row have the same names and data types. However, fields in a %ROWTYPE record do not inherit constraints, such as the NOT NULL column or check constraint, or default values

Ø  %ROWTYPE:

Sysntax:

Var_Name_Table_Name%ROWTYPE;

Erec Employees%ROWTYPE; --(it contain one row structure of the table)

set serveroutput on;

declare

emprec employees%rowtype;

begin

select * into emprec

FROM employees

where employee_id=200;

dbms_output.put_line('Employee ID: ' ||emprec.employee_id);

dbms_output.put_line('Employee Name: ' ||emprec.first_name);

end;

want to retrieve required column name only means:

select employee_id, salary into emprec.employee_id, emprec.salary

FROM employees

where employee_id=200;

Define 2 records (One for employees, one from departments)

Take emplno as user input

Display out: Ename, Salary, DeptName

set serveroutput on;

declare

emprec employees%rowtype;

deprec departments%rowtype;

begin

select e.employee_id,e.salary, d.department_name into

emprec.employee_id, emprec.salary ,deprec.department_name

FROM employees e join departments d

on(e.department_id=d.department_id)

where e.employee_id=&emp_id;

dbms_output.put_line('Employee ID: ' ||emprec.employee_id);

dbms_output.put_line('Employee Salary: ' ||emprec.salary);

dbms_output.put_line('Employee name: ' ||deprec.department_name);

end;

Defining record Types explicitly as PL/SQL structures

Ø  In above I need to define two row type for use two database, so avoid that we have use type structure

set serveroutput on;

declare

TYPE myrec_type IS RECORD --Define a record type.

(

empno NUMBER(5),

ename varchar2(20),

sal number(10,2),

dname varchar2(30)

);

v_rec myrec_type; --Define a variable of the record type

begin

select e.employee_id,e.first_name,e.salary, d.department_name into

v_rec

FROM employees e join departments d

on(e.department_id=d.department_id)

where e.employee_id=&emp_id;

dbms_output.put_line('Employee ID: ' ||v_rec.empno);

dbms_output.put_line('Employee ID: ' ||v_rec.ename);

dbms_output.put_line('Employee Salary: ' ||v_rec.sal);

dbms_output.put_line('Employee name: ' ||v_rec.dname);

end;

Exception Handling

Exception: Errors

Ø  Compile Time – Syntax error

Ø  Run Time

Exception handling : handles run time errors

Ø  Predefined

Ø  User Defined Exception

DECLARE

BEGIN

EXCEPTION

                --Exception handlers

                WHEN exception_name THEN

                Statement;

End;

Example of exception handling

set serveroutput on;

DECLARE

n number;

BEGIN

n:=10/0;

dbms_output.put_line('WILL I BE CALLED');

EXCEPTION

WHEN ZERO_DIVIDE THEN

dbms_output.put_line('CANT PERFORM THIS');

End;

More than one row

set serveroutput on;

DECLARE

lname varchar2(15);

BEGIN

select last_name into lname

from employees

where first_name='John';

dbms_output.put_line('The employee Name := '||lname);

EXCEPTION

WHEN TOO_MANY_ROWS THEN—(you doesn’t know the error name mean put OTHERS)

dbms_output.put_line('More than one row');

End;

Exceptions

Ø  ZERO_DIVE (Division by Zero)

Ø  TOO_MANY_ROWS(A Select into statement maches more than one row)

Ø  DUP_VAL_ON_INDEX (unique constraint violated)

Ø  VALUE_ERROR (Truncation, arithmetic or conversion error)

Ø  INVALID_CURSOR(lllegal cursor operation)

Ø  OTHER (Catches all exceptions)

User Defined Exceptions

set serveroutput on;

DECLARE

v_empno employees.employee_id%type;

v_sal employees.salary%type;

v_highsal EXCEPTION;

BEGIN

select employee_id,salary into v_empno, v_sal

from employees

where employee_id= &empid;

if v_sal>10000 then

raise v_highsal;

end if;

dbms_output.put_line('Will this Print');

EXCEPTION

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('More than one row');

WHEN v_highsal THEN

dbms_output.put_line('Salary greater that 10000');

End;

Thursday, July 29, 2010

Exception propagation:

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('I AM MAIN BLOCK');

  BEGIN

  DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK');

  n:=10/0;

  DBMS_OUTPUT.PUT_LINE('WILL I BE CALLED');

  EXCEPTION  —(Suppose it will be here then out exception will happen)

  WHEN ZERO_DIVIDE THEN

  DBMS_OUTPUT.PUT_LINE('CANNOT DIVIDE BY ZERO');

  END; --Sub-BLock Ends

DBMS_OUTPUT.PUT_LINE('I AM BACK IN MAIN BLOCK');

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('TOLD YOU, CANT DO THIS');

END;

I AM MAIN BLOCK

I AM INNER BLOCK

CANNOT DIVIDE BY ZERO

I AM BACK IN MAIN BLOCK

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('I AM MAIN BLOCK');

  BEGIN

  DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK');

  n:=10/0;

  DBMS_OUTPUT.PUT_LINE('WILL I BE CALLED');

  EXCEPTION

  WHEN OTHERS THEN—INNER ONLY HAAPEN

 DBMS_OUTPUT.PUT_LINE('CANNOT DIVIDE BY ZERO');

  END; --Sub-BLock Ends

DBMS_OUTPUT.PUT_LINE('I AM BACK IN MAIN BLOCK');

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('TOLD YOU, CANT DO THIS');

END;

I AM MAIN BLOCK

I AM INNER BLOCK

CANNOT DIVIDE BY ZERO

I AM BACK IN MAIN BLOCK

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

n:=1;

DBMS_OUTPUT.PUT_LINE('I AM MAIN BLOCK');

  BEGIN

  DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK');

  n:=10/0;

  DBMS_OUTPUT.PUT_LINE('WILL I BE CALLED');

  EXCEPTION

  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('CANNOT DIVIDE BY ZERO');

  n:=10/5; --(if I correct my mistake here mean also output will come)

  END; --Sub-BLock Ends

DBMS_OUTPUT.PUT_LINE('I AM BACK IN MAIN BLOCK');

DBMS_OUTPUT.PUT_LINE('n= '||n);

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('TOLD YOU, CANT DO THIS');

END;

I AM MAIN BLOCK

I AM INNER BLOCK

CANNOT DIVIDE BY ZERO

I AM BACK IN MAIN BLOCK

n= 2

Assignment: can I have nested in exceptions;

Exception

Begin

Expection

End

End;

DECLARE

BEGIN

END;

BEGIN

END;

Trapping Non_predefined oracle server error

PRAGMA EXCEPTION_INIT

Non_Predefined Error can be handled

Return error by using error number

ORA-02292: integrity constraint (HR.DFK) violated - child record found

SET SERVEROUTPUT ON;

DECLARE

v_deptid NUMBER(6);

User_excp EXCEPTION;

PRAGMA EXCEPTION_INIT(User_excp,-02292);

BEGIN

  v_deptid:= &deptid;

  DELETE FROM mydept WHERE did=v_deptid;

EXCEPTION

  WHEN User_excp THEN

  DBMS_OUTPUT.PUT_LINE

  ( 'its depends on myemp table');

END;    -- Answer:=its depends on myemp table

Non_predefined Error[PRAGMA]

Ø  Declare the name of the exception in the declarative section:

Syntax:

                Exception EXCEPTION;

In the syntax, exception is the name of the excetption

Ø  2. associate the decared exception with thestandard Oracle server error number using the PRAGMA EXCEPTION_INIT function.

Syntax

PRAGMA EXCEPTION_INIT(exception, error_number);

In the systax, exception is the previously declared exception and error_number is a standard oracle server error number.

Ø  3. Reference the declared exception with the corresponding exception-handling routine.

Ø   

Ø   

Function for trapping exception(capture error number)(Important)

Ø  SQLCODE: Return the numeric value for the error code

Ø  SQLERRM: Return the message associated with the error number

DECLARE

v_deptid NUMBER(6);

v_errno NUMBER(10);

v_err_msg VARCHAR2(1000);

BEGIN

DELETE FROM mydept WHERE

did=&Dept_id;

EXCEPTION

  WHEN OTHERS THEN

  v_errno:=SQLCODE;

  v_err_msg:= SQLERRM;

  INSERT INTO boo_error VALUES(v_errno, v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Error Occured: Please check BOO_ERROR Table');

END;

Error Occured: Please check BOO_ERROR Table

Raise application Error(Instead of put line)

Cursors

Cursors are usally used to hold the data that is retrieved from table(s) and perform actions on that data one row at a time

An Explicit cursor is usefull, when it is required to process individual records from a database table

Types

Ø  Implicit Cursor

Ø  Explicit Cursor

Ø  If oracle opens a cursor for its internal processing it is known as an implicit cursor,

Ø  User-Defined cursor is known as Explicit cursor.

Ø 

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cur IS

SELECT employee_id,last_name

FROM employees

WHERE department_id=30;

empno employees.employee_id%TYPE;

lname employees.last_name%TYPE;

BEGIN

OPEN emp_cur;

FETCH emp_cur INTO empno, lname;

dbms_output.put_line(empno||' is '||lname);

CLOSE emp_cur;

END;

114 is Raphaely

Each fetch made for one row

FETCH emp_cur INTO empno, lname;

dbms_output.put_line(empno||' is '||lname);

To fetch all the data

LOOP

FETCH emp_cur INTO empno, lname;

EXIT WHEN emp_cur%NOTFOUND;

dbms_output.put_line(empno||' is '||lname);

END LOOP;

114 is Raphaely

115 is Khoo

116 is Baida

117 is Tobias

118 is Himuro

119 is Colmenares

GET particular rows

LOOP

FETCH emp_cur INTO empno, lname;

EXIT WHEN emp_cur%ROWCOUNT = 4;

dbms_output.put_line(empno||' is '||lname);

END LOOP;

114 is Raphaely

115 is Khoo

116 is Baida

Get only 10 rows, exist before it doesn’t have enough values

LOOP

FETCH emp_cur INTO empno, lname;

EXIT WHEN emp_cur%ROWCOUNT>10 OR

emp_cur%NOTFOUND;

dbms_output.put_line(empno||' is '||lname);

END LOOP;

Cursor with ROWTYPE

SET SERVEROUTPUT ON;

DECLARE

CURSOR emp_cur IS

SELECT employee_id,last_name

FROM employees

WHERE department_id=30;

emp_record emp_cur%ROWTYPE; --composite row type variable

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur INTO emp_record;-- put into record

EXIT WHEN emp_cur%NOTFOUND;

dbms_output.put_line(emp_record.employee_id||' is '||emp_record.last_name);

END LOOP;

CLOSE emp_cur;

END;

114 is Raphaely

115 is Khoo

116 is Baida

117 is Tobias

118 is Himuro

119 is Colmenares

CURSORS WITH PARAMETS:

SET SERVEROUTPUT ON;

DECLARE

CURSOR emp_cur(deptno NUMBER) IS -- Cursor with arguments

SELECT employee_id,last_name

FROM employees

WHERE department_id=deptno;

emp_record emp_cur%ROWTYPE; --composite row type variable

did number;

BEGIN

did:=&Deparment_id;

OPEN emp_cur(did); --call the cursor with arguments

LOOP

FETCH emp_cur INTO emp_record;

EXIT WHEN emp_cur%NOTFOUND;

dbms_output.put_line(emp_record.employee_id||' is '||emp_record.last_name);

END LOOP;

CLOSE emp_cur;

END;

Cursor FOR LOOPS

Syntax:

For record_name IN Cursor_name LOOP

                Statments1;

End Loop

Ø  Implicit open, fetch, close

Ø  Don’t declaration of cursor variable taken care

SET SERVEROUTPUT ON;

DECLARE

CURSOR emp_cur IS -- Cursor with arguments

SELECT employee_id,last_name

FROM employees

WHERE department_id=30;

--boo_record emp_cur%ROWTYPE; --No need to declare this one

BEGIN

FOR boo_record In emp_cur LOOP – It declare automatically

DBMS_OUTPUT.PUT_LINE(boo_record.employee_id ||''||boo_record.last_name);

END LOOP;

END;

114Raphaely

115Khoo

116Baida

117Tobias

118Himuro

119Colmenares

Cursor For LOOPs using sub queries

It’s also working like you seen before

BEGIN

FOR boo_record In ( SELECT employee_id,last_name

FROM employees

WHERE department_id=30 ) LOOP

DBMS_OUTPUT.PUT_LINE(boo_record.employee_id ||''||boo_record.last_name);

END LOOP;

END;

Cursor With FOR UPDATE (Alternative of Explicit)

Ø  Use explicit locking to deny access to other sessions for the duration of a transaction.

Ø  Lock the rows before the update or delete.

SELECT...

FROM...

FOR UPDATE [OF column_reference][NOWAIT | WAIT n];

DECLARE

CURSOR emp_cur IS -- Cursor with arguments

SELECT eid, ename FROM myemp FOR UPDATE;

empno myemp.eid%TYPE;

ename myemp.ename%TYPE;

BEGIN

OPEN emp_cur;

FETCH emp_cur INTO empno, ename;

DBMS_OUTPUT.PUT_LINE(empno ||''||ename);

FETCH emp_cur INTO empno, ename;

DBMS_OUTPUT.PUT_LINE(empno ||''||ename);-- now cursor in 2nd row

update myemp

set eid=200

WHERE current of emp_cur;

Close emp_cur;

END;

Go and update the 2nd row of the table

WHERE CURRENT OF Clause

Ø  Position updates or positioned deletes

WHERE CURRENT OF cursor ;

Ø  Use cursors to update or delete the current row.

Ø  Include the FORUPDATEclause in the cursor query to lock the rows first.

Ø  Use the WHERECURRENTOFclause to reference the current row from an explicit cursor.

UPDATE employees

SET salary = ...

WHERE CURRENT OFemp_cursor;

Assign : what the values of %ISOPEN attribute in implicit cursor, is it true or false? FALSE (Always false, because its happen automatically)

2. Name Block(Sub Programs)

Ø  Procedure

Ø  Function

Ø  Optional declaration section(Without Declare)

Ø  Executable session Must:

Ø  Different between Blocks

Ø   

What is a procedure?

Ø  Is a type of subprogram that performs an action (DML )

Ø  Can be stored in the database as a schema object

Ø  Promotes reusability and maintainability

Ø  SHOW ERROR for compilation Error

Don’t write declare

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter1 [mode]datatype1,

parameter2 [mode]datatype2, ...)]

IS|AS

[local_variable_declarations; …]

BEGIN--actions;

END [procedure_name];

Parameter

Ø  Pass or communicate data between the caller and the subprogram

TYPE

IN (Default)- provide value for a subprogram to process

OUT- parameter retruns value to the caller

An INOUTparameter supplies an input value, which may be returned (output) as a modified value

Formal Parameter

Local variables declared in the parameter list of a subprogram specification

CREATE PROCEDURE raise_sal(id NUMBER,sal NUMBER) ISBEGIN ...

END raise_sal;

ACTUAL Parameter

Literal values, variables, and expressions used in the parameter list of the called subprogram

emp_id:= 100;

raise_sal(emp_id, 2000)

Parameter Mode(Formal only)

CREATE PROCEDURE procedure(param[mode]datatype)

Benefits of subprograms

Ø  Easy to maintain

Ø  Improved data security and integrity

Ø  Improved performance

Ø  Improved code clarity

IN Procedures

CREATE OR REPLACE PROCEDURE boo_data

(eid IN employees.employee_id%TYPE)

 IS

emp_rec employees%ROWTYPE;

BEGIN

    SELECT * INTO emp_rec

    FROM employees

    WHERE employee_id=eid;

    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);

EXCEPTION

   WHEN NO_DATA_FOUND THEN

   DBMS_OUTPUT.PUT_LINE('Invalid EmpNo');

END boo_data;

set serveroutput on;

execute boo_data(300);

Invalid EmpNo

Call the procedure from outside

declare

v_no number(4):=&emp;

begin

boo_data(v_no);

end;

Call the procedure from the variable

set autoprint on;

variable g_empno number;

begin

:g_empno:=&empid;

end;

/

set serveroutput on;

execute boo_data(:g_empno);

DROP PROCEDURE procedure_name

OUT Procedures(1 one 3 out)

CREATE OR REPLACE PROCEDURE bout_data

(eid IN number, --Input from caller

P_name OUT varchar2, --send out (Unable to specify the size here)

p_sal  OUT varchar2, -- send out

p_job  OUT varchar2) --send out

 IS

emp_rec employees%ROWTYPE;

BEGIN

    SELECT last_name, salary, job_id INTO p_name, p_sal, P_job -- we need fill those out box

    FROM employees

    WHERE employee_id=eid;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

   DBMS_OUTPUT.PUT_LINE('Invalid EmpNo');

END bout_data;

/

To Call that procedure we need another one because we need to collect 3 parameter as out

declare

d_no number(4);

d_name varchar2(15);

d_sal varchar2(10);

d_job varchar2(10);

begin

d_no:=&EMPLOYEEID;

bout_data(d_no,d_name, d_sal,d_job);

DBMS_OUTPUT.PUT_LINE( d_name ||' Works in dept '||d_job|| ' Salary is '||d_sal);

end;

other way of calling that procedure

variable d_name varchar2(15);

variable d_sal varchar2(10);

variable d_job varchar2(10);

--d_no:=&EMPLOYEEID;

execute bout_data(100,:d_name, :d_sal,:d_job);

Friday, July 30, 2010

PROCEDURE WITH CURSOR

CREATE or REPLACE PROCEDURE p_print IS

CURSOR p_cur IS

Select last_name, salary  FROM employees;

fname employees.first_name%TYPE;--varibale type of column

sal employees.salary%TYPE;

BEGIN

open p_cur;

LOOP

  FETCH p_cur into fname,sal;

  Exit when p_cur%NOTFOUND;

  dbms_output.put_line(fname||' '||sal);

END LOOP;

close p_cur;

END;

/

execute p_print;

execute p_print();

Mostly Preferred way is Procedure, Cursor with ROWTYPE;

CREATE or REPLACE PROCEDURE p_print IS

CURSOR p_cur IS

Select last_name, salary  FROM employees; --Create a cursor

p_rec p_cur%ROWTYPE;  --Create Row type variable for cursor

BEGIN

open p_cur; --Open Cursor

LOOP

  FETCH p_cur into p_rec; --Put values into created record

  Exit when p_cur%NOTFOUND; --Exit when cursor value is not found

  dbms_output.put_line(p_rec.last_name||' '||p_rec.salary);

END LOOP;

close p_cur;

END;

Procedure doesn’t  affect Rollback or commit

Create table

CREATE TABLE p_emp (eid integer, ename varchar2(15), salary integer);

/*

CREATE or REPLACE PROCEDURE P_auto IS  --Create procedure to implement auto insert

BEGIN

insert into p_emp values(200, 'Pawan', 25000); --insert data

commit; --commit here

END;

/

Create anonyms row to call those Procedure

 Begin

 insert into p_emp values(500, 'Kishore',3000);

 p_auto();

 insert into p_emp values(700, 'Kishore',40000);

 rollback;

 end;

 /

 */

 select * from p_emp;

EID                    ENAME           SALARY                

---------------------- --------------- ----------------------

500                    Kishore         3000                  

200                    Pawan           25000  

A PRAGMA AUTOMOUS

A PRAGMA  is a compiler directive which in the case tell the complier that

this transaction is to be considered as AUTONOMOUS

CREATE or REPLACE PROCEDURE P_auto IS

PRAGMA AUTONOMOUS_TRANSACTION; --Same as above except this statements;

BEGIN

insert into p_emp values(200, 'Pawan', 25000);                        Child (commit will applicable for this only)

commit;

END;

/

 Begin

 insert into p_emp values(500, 'Kishore',3000);                        Parent (Autonomous(Keep it separate))

 p_auto();

 insert into p_emp values(700, 'Kishore',40000);

 rollback;

 end;

  /

 select * from p_emp;

EID                    ENAME           SALARY                

---------------------- --------------- ----------------------

200                    Pawan           25000      (Procedure value only shown here)

Functions

Can be called following

Ø  Select

Ø  Where having

Ø  Connect by , start with, order by, group by

Ø  Value, insert

Ø  Set , update

set serveroutput on;

CREATE OR REPLACE FUNCTION b_check(pid IN NUMBER)

RETURN NUMBER

IS

vsal NUMBER(10,2);

BEGIN

  SELECT salary into vsal FROM employees

  WHERE employee_id=pid;

  RETURN vsal;

END b_check;

/

To call above Function:

select b_check(100) from dual;

/

begin

dbms_output.put_line(b_check(100));

end;

set serveroutput on;

CREATE OR REPLACE FUNCTION  b_raise (pid IN NUMBER)

RETURN NUMBER -– if I used NUMBER(4,2) means it’s not working

IS

BEGIN

 RETURN pid*1.10;

  END b_raise;

/

begin

dbms_output.put_line(b_raise(100));

end

Ø  select b_raise(&sal) from dual; or execute b_raise(100); (or)

Ø  execute dbms_output.put_line(b_raise(500));

Ø  select b_raise(300) "The output is" from dual;

110;

DDL can perform in procedure;

select * from boo_emppp;

/*

create or replace procedure bpro

is

Begin

execute immediate 'drop table boo_emppp';

dbms_output.put_line('Table Dropped');

end

select b_check(100) from dual;

/

Begin

Update using function

update boo_emppp

set salary = b_raise(salary)

where employee_id = 200;

Invoke as part of a PL/SQL expression

Ø  Using a host variable to obtain the result:

VARIABLE salary NUMBER

EXECUTE :salary := get_sal(100)

Ø  Using a local variable to obtain the result:

DECLARE salemployees.salary%type;

BEGIN

sal:= get_sal(100); ...

END;

Ø  Use as a parameter to another subprogram

EXECUTE dbms_output.put_line(get_sal(100))

Ø  Use in a SQL statement (subject to restrictions)

SELECT job_id, get_sal(employee_id) FROM employees;

Collection  

Ø  A term which can have several different meanings;

V array fixed but, nested table extendable

Ø  A nested table, index-by table, or VARRAY datatype

Ø  A PL/SQL variable of type nested table, index-by table or VARRAY

Ø  A table column of type nested table or VARRAY

PLSQL TABLE OR ASSOCIATIVE ARRAY OR INDEX BY TABLE

Ø  Made up of Index column and data column.

Ø  In array you must start with 0 and follow continuous ordering.

Ø  In PLSQL table this is not mandatory.

To declare PLSQL table we first need to create the table type and then create a variable of table type

set serveroutput on;

DECLARE

TYPE ename_b IS

      TABLE OF VARCHAR2(50)       --Index data type column

      INDEX BY BINARY_INTEGER; -- Index column

      ename_t ename_b;-- create variable

      v_counter NUMBER(4);

  BEGIN

      FOR eno In 101..110 –-eno automatically declared

      LOOP

          SELECT first_name||' '||last_name

          INTO ename_t(eno)

          FROM employees

          WHERE employee_id=eno;

      END LOOP;

      FOR x IN ename_t.FIRST..ename_t.LAST  -- First and Last are inbuilt function

      LOOP

        DBMS_OUTPUT.PUT_LINE(ename_t(x));

      END LOOP;

  END; 

Neena Kochhar

Lex De Haan

Alexander Hunold

Bruce Ernst

David Austin

Valli Pataballa

Diana Lorentz

Nancy Greenberg

Daniel Faviet

John Chen

Ø  First: return available no null values

Ø  INDEX BY BINARY_INTEGER:

Ø  Binary integer takes less memory

Ø  Also calculations done using binary_integer are faster tah others.

Ø  It uses floating point arithmetic;

Monday, August 02, 2010

PACKAGES:

A package is a group of procedures, function, variable and SQL statements created as as single unit.

It used to store together related object.

Ø  Encapsulated related functionality into one individual unit.

Ø  Eg: A HR package can contain hiring and firing procedures, commission , and bonus functions

Ø  Enables overloading of procedures & function when required.

Ø  Improve performance by loading multiple objects/ subprograms held within into memory at once.

Overloading

Ø  Enhance performance

Ø  When pl/sql package construct is referenced for the first time, the whole package is loaded into memory.

Ø  Sequence acces to construct in the same packeage do not require dist input/ouput

Package consider of 2 parts (Public in nature)

Ø  A specification

Ø  A body(optional)

A package specification should contain procedure and function declarations.

A package specification does not contain any code.

Implementation of a procedure or function that is declared in package specification is done in package body.

Ø  Declaration in the package specifications are public by default.

Ø  Member within the body are private.

Advantages:

Ø  Group related items, types and subprograms as pl/sql module

Ø  When a procedure in a package is called entire package is loaded, through it happens to be expensive first time the response is faster for subsequent calls.

Ø  Package allows us to create types, variable and subprograms that are private or public

Name of Package

Ø  UTL_FILE: for reading & writing files from PL/SQL programs.

Ø  DB_SQL: to generates SQL statements dynamically used to create & deleted tables from with PL/SQL programs.

Ø  DBMS_JOB: for job scheduling

Ø  DBMS_OUTPUT: to generate output

Ø  DBMS_OUTPUT.ENABLE: enables output or displays data on screen

Ø  Equivalent is SQL plus command

Ø  SET SERVEROUTPUT ON:

Create package with function and procedure

CREATE or REPLACE package boo_pkg is

std_comm NUMBER:=10;

PROCEDURE hh_rest_comm(new_comm NUMBER);           --(it is PUBLIC because I can access from outside)

end boo_pkg;

Create PROCEDURE BODY