Oracle10g Notes...
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;
AVG(SAL)
30000
40000
DEPTID
d1
d2
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;
EMPLOYEE_ID
200
LAST_NAME
Whalen
SALARY
4400
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
CREATE OR replace package BODY boo_pkg
IS
Function used by package procedure
FUNCTION Validat(comm NUMBER) RETURN BOOLEAN -- ( PRIVATE because it is hidden )
IS
max_comm employees.commission_pct%TYPE;
BEGIN
SELECT max(commission_pct) INTO max_comm
FROM employees;
RETURN (comm BETWEEN 0.0 AND max_comm);
END validat;
Create body of procedure
PROCEDURE hh_rest_comm(new_comm NUMBER)
IS
BEGIN
if validat(new_comm) THEN
std_comm:= new_comm;
dbms_output.put_line(std_comm);
ELSE
RAISE_application_error(-20210,'Bad Commission');
END if;
end hh_rest_comm;
end boo_pkg;
/
set serveroutput on;
execute boo_pkg.hh_rest_comm(.15);
.15
Defining Your own error messages(-20000 TO 20999)
Ø And message is a character string up to 2048 byte long
Procedure RAISE_APPLICATION_ERROR
Systax:
Raise_application_error(error_number, message, [TRUE|FALSE]);
Ø If the option third parameter is TRUE,
Ø The error is placed on the stack of previous errors.
Ø If the option parameter is FALSE (the default)
Ø The error replaces all previous errors.
Ø RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to quality references to it.
Forward Declaration
CREATE OR replace package BODY boo_pkg
IS
FUNCTION Validat(comm NUMBER) RETURN BOOLEAN; -- FORWARE DECLARTION
PROCEDURE hh_rest_comm(new_comm NUMBER)
IS
BEGIN
if validat(new_comm) THEN
std_comm:= new_comm;
dbms_output.put_line(std_comm);
ELSE
RAISE_application_error(-20210,'Bad Commission');
END if;
end hh_rest_comm;
FUNCTION Validat(comm NUMBER) RETURN BOOLEAN
IS
max_comm employees.commission_pct%TYPE;
BEGIN
SELECT max(commission_pct) INTO max_comm
FROM employees;
RETURN (comm BETWEEN 0.0 AND max_comm);
END validat;
end boo_pkg;
/
set serveroutput on;
execute boo_pkg.hh_rest_comm(.1);
.1
Overloading:
More than one subprogram can be created with the same name within a package but having different parameters
Oracle itself make extensinve of over loading
Eg: to_char
Convert both number & dates to character format
Restriction on overloading:
The datatype of atleast one of the paramerters of the overloaded function or procedure must differ
Parameter cannot be distinguished with IN & OUT modes
Overloaded fns must differ by more than their return types.
Return does not have size like number(10)
example
create or replace package boo_over
is
function add(pnum1 number, pnum2 number) return number;
function add(pstr1 varchar2, pstr2 varchar2) return varchar2;
function add(pstr1 varchar2, pnum1 number) return varchar2;
end boo_over;
/
create or replace package body boo_over
is
function ADD(pnum1 number, pnum2 number)
return number
is
begin
return pnum1+pnum2;
end add;
function add(pstr1 varchar2, pstr2 varchar2)
return varchar2
is
begin
return pstr1||' '||pstr2;
end add;
function add(pstr1 varchar2, pnum1 number)
return varchar2
is
f_v_str varchar2(200);
begin
for i in 1..pnum1 loop
f_v_str := pstr1||' '||f_v_str;
end loop;
return f_v_str;
end add;
FUNCTION ADD(pnum1 number,pnum2 number,pnum3 number)return number
is
begin
return pnum1+pnum2+pnum3;
end add;
end boo_over;
/
set serveroutput on;
execute dbms_output.put_line (boo_over.add(10,5));
execute dbms_output.put_line (boo_over.add('Hello','Muruga'));
execute dbms_output.put_line (boo_over.add('PLSQL',10));
15
Hello Muruga
PLSQL PLSQL PLSQL PLSQL PLSQL PLSQL PLSQL PLSQL PLSQL PLSQL
select * from user_procedures ;
select * from user_source;
Retrieve the Data from employees Using Package Cursor;
CREATE OR REPLACE PACKAGE boo_cur
IS
procedure firstrow;
procedure secondrow;
end boo_cur;
/
CREATE OR REPLACE PACKAGE BODY boo_cur
IS
cursor c1
IS
SELECT employee_id from employees order by employee_id desc; --CREATING IN BODY
procedure firstrow
IS
v_empid NUMBER(4);
BEGIN
dbms_output.put_line('PROCES1 STARAT');
IF NOT C1%ISOPEN THEN -- ISOPEN not (false)
OPEN c1;
END IF;
FOR I IN 1..3 LOOP
fetch c1 INTO v_empid;
dbms_output.put_line(v_empid);
END LOOP;
-- LOOP
-- fetch c1 into v_empid;
-- dbms_output.put_line(v_empid);
-- exit when c1%rowcount>=3;
-- end loop;
dbms_output.put_line('Process1 Ends');
end firstrow;
procedure secondrow
IS
v_empid NUMBER(4);
BEGIN
dbms_output.put_line('PROCESS 2 STARAT');
IF NOT C1%ISOPEN THEN -- ISOPEN not (false)
OPEN c1;
END IF;
FOR I IN 4..6 LOOP
fetch c1 INTO v_empid;
dbms_output.put_line(v_empid);
END LOOP;
-- LOOP
-- fetch c1 into v_empid;
-- dbms_output.put_line(v_empid);
-- exit when c1%rowcount>=6;
-- end loop;
CLOSE c1;
dbms_output.put_line('Process 2 Ends');
end secondrow ;
end boo_cur;
/
set serveroutput on;
execute boo_cur.firstrow;
execute boo_cur.secondrow;
PROCES1 STARAT
206
205
204
Process1 Ends
anonymous block completed
PROCESS 2 STARAT
203
202
201
Process 2 Ends
Dynamic SQL
It’s designed on User.. decided at runtime;
The dbms_sql package is used to write dynamic sql
(use dynamic SQL to create a SQL statement whose structure may change during run time;
Most PL/SQL programs do a specific, predictable job.
Using the Execute immediate
Ø I s executed with Native Dynamic SQL statement or the DBMS_sqL package
Ø The dbms_sql package is used to write dynamic sql
Dynamic SQL with a DDL statement
Use dynamic SQL to create a SQL statement whose structure may change during run time. Dynamic SQL:
Ø •Is constructed and stored as a character string within the application
Ø •Is a SQL statement with varying column data, or different conditions with or without placeholders (bind variables)
Ø •Enables data-definition, data-control, or session-control statements to be written and executed from PL/SQL
Ø •Is executed with Native Dynamic SQL statements or the DBMS_SQLpackage
NATIVE DYNAMIC
Provides native support for dynamic SQL directly in the PL/SQL language
•Provides the ability to execute SQL statements whose structure is unknown until execution time
•Is supported by the following PL/SQL statements:
•EXECUTE IMMEDIATE
•OPEN-FOR
•FETCH
•CLOSE
The execute immediate statement prepares(pares) and immediately executed a dynamic SQL statements or an anonymous PL/SQL block
CREATE PROCEDURE create_table(
table_name VARCHAR2, col_specs VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '||table_name||
' (' || col_specs || ')';
END;
TRIGGERS(only used in condition procedure, not calculation)
Ø PL/SQL Block which gets triggered when a triggering event occurs on a triggering table.
Ø They are implicitly called.
Ø Called when a certain event (DML) happens on a table
Ø Procedure explicitly called whereas triggers are implicitly called.
Ø Integrity:
Ø Reference integrity
Ø Domain integrity
Ø User defined integrity
I can t use the trigger for calculation, because, I cant able to call it…
Ø They are meant for user defined integrity
Ø They do not take parameters.
Ø They do not return a value.
Ø Cannot be called explicitly.
Ø Cannto use commit, rollback, save point (will create but show error at run back)
SYNTAX
CREATE OR REPLACE TRIGGER trigger_name
After|Before Insert|update|delete
On table_name|view_name
For each row --Row LEVEL Trigger
[When condition]
PL/SQL BLOCK
END;
Types
STATEMENT LEVEL TRIGGER (default)
ROW LEVEL TRIGGER
STATEMENT LEVEL TRIGGER ( NO FOR LOOP)
Ø Give 1 entry for entire modification
Trigger when modify the data
CREATE OR REPLACE TRIGGER B_MAIN
BEFORE INSERT OR DELETE OR UPDATE
ON b_emp
BEGIN
insert into baudit values (user, sysdate, 'Try to modify it');
end;
update b_emp set salary = 1000
where department_id= 20;
/
Ø select * from baudit ;
Ø UNAME LTIME OPERATION
Ø ------------------------------ ------------------------- --------------------------------------------------
Ø HR 03-AUG-10 10.58.53.000000000 AM Try to modify it
NOW modifying trigger to row level trigger
CREATE OR REPLACE TRIGGER B_MAIN_row
BEFORE INSERT OR DELETE OR UPDATE
ON b_emp
for each row
BEGIN
insert into baudit values (user, sysdate, 'Try to modify it');
--commit;
end;
update b_emp set salary = 1000
where department_id= 20;
/
select * from baudit ; (generate commands for each rows)
UNAME LTIME OPERATION
------------------------------ ------------------------- --------------------------------------------------
HR 03-AUG-10 11.03.23.000000000 AM Try to modify it
HR 03-AUG-10 11.03.23.000000000 AM Try to modify it
HR 03-AUG-10 11.03.23.000000000 AM Try to modify it
TYPES of DML Trigger;
A statement triiger:
Ø Execute once for the triggering event
Ø Is the default type of trigger
Ø Fires once even if no rows are affected at all
A Row trigger
Ø Executed once for each row affected by the triggering event
Ø Is not executed if the triggering event does not affected any row
Ø Is `
SECURE DATABASE WHEN THOSE CONDITON
CREATE OR REPLACE TRIGGER BbSECURE
BEFORE INSERT OR UPDATE OR DELETE ON B_EMP
BEGIN
if (to_char(sysdate, 'DY') IN ('SAT','SUN','MON','TUE')) OR
(to_char(sysdate, 'HH24:MI') NOT BETWEEN '8:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may NOT modify employees table');
end if;
end;
/
My Datatable Get Secure When I Remove If Condition
Check trigger
select * from user_triggers where trigger_name = 'BBSECURE';
select * from user_objects where object_type='TRIGGER'
USING CONDITION PREDICATES (INSERTING, UPDATING, DELETING)
CREATE OR REPLACE TRIGGER bmaintain
BEFORE INSERT OR DELETE OR UPDATE
ON bemp
BEGIN
if inserting THEN
insert into baudit values (user, sysdate, 'Insert into Table');
elsif Updating('Salary') THEN
insert into baudit values (user, sysdate, 'Salary Updating into Table');
elsif Updating Then
insert into baudit values (user, sysdate, 'Updating into Table');
else
insert into baudit values (user, sysdate, 'Delete into Table');
end if;
end;
/
select * from baudit;
update bemp set salary = 1000
where department_id= 20;
UNAME ITIME OPERATION
------------------------------ ------------------------- --------------------------------------------------
HR 03-AUG-10 12.07.54.000000000 PM Salary Updating into Table
USING OlD and NEW qualifiers
(cannot use in statement level trigger)
Ø USER ‘:’ for every old and new column
Logging a user’s activity against the given table
The trigger records the values of several columns both before and after the data changes by using the OLD and NEW qualifiers with th respective column name;
create table b_audit
(uname varchar2(20),
tran_time timestamp,
oldeid number(6),
neweid number(6),
oldsal number(6),
newsal number(6),
oldjob number(6),
newjob number(6));
CREATE OR REPLACE TRIGGER bmaintain
AFTER INSERT OR DELETE OR UPDATE ON BEMP
FOR EACH ROW
BEGIN
INSERT INTO B_AUDIT
VALUES (USER, SYSDATE, :OLD.employee_id, :NEW.employee_id, :OLD.salary, :NEW.salary, null,null);
END;
/
update bemp set salary = 1000
where department_id= 80;
select * from b_audit;
Error starting at line 12 in command:
update bemp set salary = 1000
where department_id= 80
Error report:
SQL Error: ORA-01722: invalid number
ORA-06512: at "HR.BMAINTAIN", line 2
ORA-04088: error during execution of trigger 'HR.BMAINTAIN'
01722. 00000 - "invalid number"
*Cause:
*Action:
TRIGGER BMAINTAIN compiled
34 rows updated
UNAME TRAN_TIME OLDEID NEWEID OLDSAL NEWSAL OLDJOB NEWJOB
-------------------- ------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
HR 03-AUG-10 12.41.22.000000000 PM 145 145 14000 1000
HR 03-AUG-10 12.41.22.000000000 PM 146 146 13500 1000
Use referencing in new and old
CREATE OR REPLACE TRIGGER bmaintain
AFTER INSERT OR DELETE OR UPDATE ON BEMP
referencing new as bnew old as bold
FOR EACH ROW
BEGIN
INSERT INTO B_AUDIT
VALUES (USER, SYSDATE, :bold.employee_id, :bnew.employee_id, :bold.salary, :bnew.salary, null,null);
END;
/
CALL PROCEDURE INSIDE THE TRIGGER;
create or replace procedure bpro
as
begin
raise_application_error(-20001,'How dare u modified the sal');
end;
/
CREATE OR REPLACE TRIGGER booproc
after update of salary
on bemp
for each row
begin
bpro;
end;
/
update bemp set salary = 100
where department_id= 80;
T_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------------------- -------------------- ------------------------- ------------------------- -------------------- ------------------------- ---------- ---------------------- ---------------------- ---------------------- ----------------------
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-96 SA_MAN 1000 0.4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-97 SA_MAN 1000 0.3 100 80
RESTRICTING A ROW TRIGGER;
CEREATE A TRIGGER ON BOOK LIST TABLE WHICH GETS TRIGGRED WHEN PRICE IS INCREASE BY ATLEASE 10%. THE TRIGGER SHOULD MAKE ENTRYIN A LOG RECORDING X
create table bbooks (uname varchar2(10), tdate timestamp, oprice number, nprice number);
create table bblist as select * from book_list;
select * from bblist;
create or replace trigger bupda
before update of price on bblist
FOR each row
when (new.price/old.price>=1.1)
begin
insert into bbooks values (USER, sysdate, :old.price, :new.price);
end;
/
--insert into bblist values(1, 'programmer', 3000);
update bblist set price = 3000 where bno =1;
BNO BNAME PRICE
---------------------- --------------- ----------------------
1 C 150
2 C++ 300
3 ORACLE 400
1 programmer 150
1 programmer 150
Update log
select * from bbooks;
UNAME TDATE OPRICE NPRICE
---------- ------------------------- ---------------------- ----------------------
HR 03-AUG-10 04.02.36.000000000 PM 150 200
HR 03-AUG-10 04.02.36.000000000 PM 150 200
HR 03-AUG-10 04.02.36.000000000 PM 150 200
Mutating table:
Ø a table that is currently being modified by an uupdate, deleted or insert statements.
Ø A table mutation error will result if you create a trigger on a table that attempts to examine the current table
Ø Error come only in row level triggers.\
Ø Trigger Timing
Ø Before: execute the tigger body before the triggering DML event on a table.
Ø After- execute the trigger body after the trigger DML event on a table.
Ø Instead of- execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable
Instead of doing insert you have to trigger the body.. don’t do it , show error
Ø
Ø
CREATE TABLE bnew_emps AS
SELECT employee_id,last_name,salary,department_id FROM employees;
CREATE TABLE bnew_depts AS
SELECT d.department_id,d.department_name
FROM departments d;
CREATE VIEW bemp_details_view AS
SELECT e.employee_id, e.last_name, e.salary,
e.department_id, d.department_name
FROM bnew_emps e join bnew_depts d
ON e.department_id = d.department_id;
CREATE TRIGGER bMODIFY_COMPLEX_VIEW
INSTEAD OF INSERT ON emp_details_view
BEGIN
INSERT INTO new_emps values(:new.employee_id,:new.last_name,:new.salary,:new.department_id);
INSERT INTO new_depts values(:new.department_id,:new.department_name);
END;
NO COPY Hint
Ø Is a hint that enable the PL/SQL compiler to pass OUT and INOUT parameter by reference, as opposed to passing by value
Ø Enhance performance by reducing overhead when passing parameters.
create or replace procedure RAISE_SAL02
(EMP_ID number, AMOUNT number)
is
begin
DBMS_OUTPUT.PUT_LINE('In proc');
DBMS_OUTPUT.PUT_LINE(EMP_ID||' '||AMOUNT);
update MYEMP set SALARY=SALARY+AMOUNT
where EMPLOYEE_ID=EMP_ID;
end raise_Sal02;
-----------------------------------------------------------
set SERVEROUTPUT on;
declare
EMP_NUM number(6):=120;
BONUS number(6):=50;
begin
RAISE_SAL02(AMOUNT=>BONUS,EMP_ID=>EMP_NUM);
DBMS_OUTPUT.PUT_LINE('done1');
RAISE_SAL02(EMP_ID=>EMP_NUM, AMOUNT=>BONUS);
DBMS_OUTPUT.PUT_LINE('Done2');
RAISE_SAL02(EMP_NUM, AMOUNT=>BONUS);
end;
---------------------
NOCOPY Hint
---------------------
Is a hint that enables the PL/SQL compiler to pass OUT and INOUT
parameters by reference, as opposed to passing by value
Enhances performance by reducing overhead
when passing parameters.
create or replace procedure DO_SOMETHING_MR
(
N1 in number,
N2 in OUT number,
N3 in OUT NOCOPY number)
is
begin
N2:=20;
DBMS_OUTPUT.PUT_LINE(N1);
n3:=30;
DBMS_OUTPUT.PUT_LINE(N1);
end;
--------------------
Change argument procedure
Dependent (Procedure )and referenced (table)objects;
Some object reference other objects as part of their definition
A procedure or function can directly or indirectly
(through an intermediate view, procedure, function or packaged procedure or function) reference the following objecs:
Tables
View
Sequences
(select * from USER_dependencies)
Minimize dependency failure by:
Declaring records with the %ROWTYPE attribute
Declaring variable with the %TYPE attribute
Querying with the SELECT * notation
INCLUDING
Standard packge (dbms_standard) raise
Index by table: index column and data colum
Isolation level:
Ø Read committed
Ø Read uncommitted
Ø Repeatable read
Ø Sterilization
Ø Join is faster than subquery..
ADVANCED REFERENCE:
CREATE TRIGGER
Purpose
Use the CREATE TRIGGER statement to create and enable a database trigger, which is:
· A stored PL/SQL block associated with a table, a schema, or the database or
· An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
Oracle Database automatically executes a trigger when specified conditions occur.
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTERTABLE statement.
Prerequisites
Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
· To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
· To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
· In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
Syntax
create_trigger::=
Description of the illustration create_trigger.gif
DML_event_clause ::=
Description of the illustration DML_event_clause.gif
Description of the illustration referencing_clause.gif
Semantics
OR REPLACE
Specify OR REPLACE to re-create the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it.
schema
Specify the schema to contain the trigger. If you omit schema, then Oracle Database creates the trigger in your own schema.
trigger
Specify the name of the trigger to be created.
If a trigger produces compilation errors, then it is still created, but it fails on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.
Note:
If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE.
BEFORE
Specify BEFORE to cause the database to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.
Restrictions on BEFORE Triggers
· You cannot specify a BEFORE trigger on a view or an object view.
· You can write to the :NEW value but not to the :OLD value.
AFTER
Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.
Restrictions on AFTER Triggers
· You cannot specify an AFTER trigger on a view or an object view.
· You cannot write either the :OLD or the :NEW value.
Note:
When you create a materialized view log for a table, Oracle Database implicitly creates an AFTER ROW trigger on the table. This trigger inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the materialized view.
INSTEAD OF
Specify INSTEAD OF to cause Oracle Database to fire the trigger instead of executing the triggering event. INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.
If the view belongs to a hierarchy, then the trigger is not inherited by subviews.
Note:
Oracle Database fine-grained access control lets you define row-level security policies on views. These policies enforce specified rules in response to DML operations. If an INSTEAD OF trigger is also defined on the view, then the database will not enforce the row-level security policies, because the database fires the INSTEAD OF trigger instead of executing the DML on the view.
Restrictions on INSTEAD OF Triggers
· INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
· You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.
Note:
You can create multiple triggers of the same type (BEFORE, AFTER, or INSTEAD OF) that fire for the same statement on the same table. The order in which Oracle Database fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same statement, then combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.
DML_event_clause
The DML_event_clause lets you specify one of three DML statements that can cause the trigger to fire. Oracle Database fires the trigger in the existing user transaction.
DELETE
Specify DELETE if you want the database to fire the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table.
INSERT
Specify INSERT if you want the database to fire the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table.
UPDATE
Specify UPDATE if you want the database to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified after OF. If you omit OF, then the database fires the trigger whenever an UPDATE statement changes a value in any column of the table or nested table.
For an UPDATE trigger, you can specify object type, varray, and REF columns after OF to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
Note:
Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle Database to fire triggers defined on the table containing the columns or the attributes.
Restrictions on Triggers on UPDATE Operations
· You cannot specify UPDATE OF for an INSTEAD OF trigger. Oracle Database fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view.
· You cannot specify a nested table or LOB column in the UPDATE OF clause.
See Also:
AS subquery clause of CREATE VIEW for a list of constructs that prevent inserts, updates, or deletes on a view
Performing DML operations directly on nested table columns does not cause Oracle Database to fire triggers defined on the table containing the nested table column.
ddl_event
Specify one or more types of DDL statements that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. You can create BEFORE and AFTER triggers for these events. Oracle Database fires the trigger in the existing user transaction.
Restriction on Triggers on DDL Events
You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.
The following ddl_event values are valid:
ALTER
Specify ALTER to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary.
Restriction on Triggers on ALTER Operations
The trigger will not be fired by an ALTER DATABASE statement.
ANALYZE
Specify ANALYZE to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.
ASSOCIATE STATISTICS
Specify ASSOCIATE STATISTICS to fire the trigger whenever the database associates a statistics type with a database object.
AUDIT
Specify AUDIT to fire the trigger whenever the database tracks the occurrence of a SQL statement or tracks operations on a schema object.
COMMENT
Specify COMMENT to fire the trigger whenever a comment on a database object is added to the data dictionary.
CREATE
Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.
Restriction on Triggers on CREATE Operations
The trigger will not be fired by a CREATE DATABASE or CREATE CONTROLFILE statement.
DISASSOCIATE STATISTICS
Specify DISASSOCIATE STATISTICS to fire the trigger whenever the database disassociates a statistics type from a database object.
DROP
Specify DROP to fire the trigger whenever a DROP statement removes a database object from the data dictionary.
GRANT
Specify GRANT to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.
NOAUDIT
Specify NOAUDIT to fire the trigger whenever a NOAUDIT statement instructs the database to stop tracking a SQL statement or operations on a schema object.
RENAME
Specify RENAME to fire the trigger whenever a RENAME statement changes the name of a database object.
REVOKE
Specify REVOKE to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.
TRUNCATE
Specify TRUNCATE to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.
DDL
Specify DDL to fire the trigger whenever any of the preceding DDL statements is issued.
database_event
Specify one or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, Oracle Database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).
SERVERERROR
Specify SERVERERROR to fire the trigger whenever a server error message is logged.
The following errors do not cause a SERVERERROR trigger to fire:
· ORA-01403: no data found
· ORA-01422: exact fetch returns more than requested number of rows
· ORA-01423: error encountered while checking for extra rows in exact fetch
· ORA-01034: ORACLE not available
· ORA-04030: out of process memory when trying to allocate string bytes (string, string)
LOGON
Specify LOGON to fire the trigger whenever a client application logs onto the database.
LOGOFF
Specify LOGOFF to fire the trigger whenever a client application logs off the database.
STARTUP
Specify STARTUP to fire the trigger whenever the database is opened.
SHUTDOWN
Specify SHUTDOWN to fire the trigger whenever an instance of the database is shut down.
SUSPEND
Specify SUSPEND to fire the trigger whenever a server error causes a transaction to be suspended.
ON table | view
The ON clause lets you determine the database object on which the trigger is to be created. Specify the schema and table or view name of one of the following on which the trigger is to be created:
· Table or view
· Object table or object view
· A column of nested-table type
If you omit schema, then Oracle Database assumes the table is in your own schema. You can create triggers on index-organized tables.
Restriction on Schema
You cannot create a trigger on a table in the schema SYS.
NESTED TABLE Clause
Specify the nested_table_column of a view upon which the trigger is being defined. Such a trigger will fire only if the DML operates on the elements of the nested table.
Restriction on Triggers on Nested Tables
You can specify NESTED TABLE only for INSTEAD OF triggers.
DATABASE
Specify DATABASE to define the trigger on the entire database. The trigger fires whenever any database user initiates the triggering event.
SCHEMA
Specify SCHEMA to define the trigger on the current schema. The trigger fires whenever any user connected as schema initiates the triggering event.
referencing_clause
The referencing_clause lets you specify correlation names. You can use correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.
· If the trigger is defined on a nested table, then OLD and NEW refer to the row of the nested table, and PARENT refers to the current row of the parent table.
· If the trigger is defined on an object table or view, then OLD and NEW refer to object instances.
Restriction on the referencing_clause
The referencing_clause is not valid with INSTEAD OF triggers on CREATE DDL events.
FOR EACH ROW
Specify FOR EACH ROW to designate the trigger as a row trigger. Oracle Database fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition.
Except for INSTEAD OF triggers, if you omit this clause, then the trigger is a statement trigger. Oracle Database fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
INSTEAD OF trigger statements are implicitly activated for each row.
Restriction on Row Triggers
This clause is valid only for DML event triggers, not for DDL or database event triggers.
WHEN Clause
Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. See the syntax description of condition in Chapter 6, " Conditions". This condition must contain correlation names and cannot contain a query.
The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause.
Restrictions on Trigger Conditions
· If you specify this clause for a DML event trigger, then you must also specify FOR EACH ROW. Oracle Database evaluates this condition for each row affected by the triggering statement.
· You cannot specify trigger conditions for INSTEAD OF trigger statements.
· You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger condition.
pl/sql_block
Specify the PL/SQL block that Oracle Database executes to fire the trigger.
The PL/SQL block of a database trigger can contain one of a series of built-in functions in the SYS schema designed solely to extract system event attributes. These functions can be used only in the PL/SQL block of a database trigger.
Restrictions on Trigger Implementation
· The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.
· You can reference and use LOB columns in the trigger action inside the PL/SQL block. You can modify the :NEW values but not the :OLD values of LOB columns within the trigger action.
call_procedure_statement
The call_procedure_statement lets you call a stored procedure rather than specifying the trigger code inline as a PL/SQL block. The syntax of this statement is the same as that for CALL , with the following exceptions:
· You cannot specify the INTO clause of CALL, because it applies only to functions.
· You cannot specify bind variables in expr.
· To reference columns of tables on which the trigger is being defined, you must specify :NEW and :OLD.
Examples
Creating a DML Trigger: Examples
This example shows the basic syntax for a BEFORE statement trigger. You would write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements could be issued.
CREATE TRIGGER schema.trigger_name
BEFORE
DELETE OR INSERT OR UPDATE
ON schema.table_name
pl/sql_block
Oracle Database fires such a trigger whenever a DML statement affects the table. This trigger is a BEFORE statement trigger, so the database fires it once before executing the triggering statement.
The next example shows a partial BEFORE row trigger. The PL/SQL block might specify, for example, that an employee's salary must fall within the established salary range for the employee's job:
CREATE TRIGGER hr.salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
FOR EACH ROW
WHEN (new.job_id <> 'AD_VP')
pl/sql_block
Oracle Database fires this trigger whenever one of the following statements is issued:
· An INSERT statement that adds rows to the employees table
· An UPDATE statement that changes values of the salary or job_id columns of the employees table
salary_check is a BEFORE row trigger, so the database fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT statement.
salary_check has a trigger condition that prevents it from checking the salary of the administrative vice president (AD_VP).
Creating a DDL Trigger: Example
This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.
CREATE TRIGGER audit_db_object AFTER CREATE
ON SCHEMA
pl/sql_block
Calling a Procedure in a Trigger Body: Example
You could create the salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure hr.salary_check, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:
CREATE TRIGGER hr.salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
FOR EACH ROW
WHEN (new.job_id <> 'AD_VP')
CALL check_sal(:new.job_id, :new.salary, :new.last_name);
The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.
Creating a Database Event Trigger: Example
This example shows the basic syntax for a trigger to log all errors. The hypothetical PL/SQL block does some special processing for a particular error (invalid logon, error number 1017). This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution, such as unsuccessful logon.
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
<special processing of logon error>
ELSE
<log error number>
END IF;
END;
Creating an INSTEAD OF Trigger: Example
In this example, an oe.order_info view is created to display information about customers and their orders:
CREATE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view. The PL/SQL trigger implementation is shown in italics.
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
Creating a SCHEMA Trigger: Example
The following example creates a BEFORE statement trigger on the sample schema hr. When a user connected as hr attempts to drop a database object, the database fires the trigger before dropping the object:
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
Assignment
1. Explore data types in oracle and among the five aggregate functions which function works on which data type?
2. In Natural Join is it necessary that column names and data types should be same?
Yes. Necessary
3. ON DELETE CASCADE
Work on ON DELETE CASCADE
ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE)
/*Deletes the dependent rows in the child table when a row in the parent table is deleted*/
--create table boor1 (Eid integer, did integer constraints bopk primary
--key);
--create table boor2 (Dname varchar2(50),Did integer );
--desc boor2;
--alter table boor2 add constraints bofk foreign key(did)
--references boor1 on delete cascade
--insert into boor1 values (1, 10);
--insert into boor1 values (2, 21);
--insert into boor2 values('EEE',10);
select * from boor1;
select * from boor2;
EID
2
1
DID
21
10
DNAME
EEE
DID
10
--delete from boor1 where did=10
EID
2
DID
21
If we delete parent table that will affect child table too
4. When you create a table from an existing table will the constraints the get copied ?
No
5. Dropping a table that goes in recycle bin. Can you create a table with the same name now?
Yes.
6. Can you perform DML on object in recycle bin.
Cannot perform DML. Only DQL possible
7. What happens to constraints if object is dropped and flashbacked.
Constraints will not be changed
8.Is dropping & flashback applicable to only tables?
No. cluster also possible
drop view aa;
flashback index aa to before drop;
9. Display the fifth highest salary. Use pseudo columns
select E1.SALARY from employees E1
where 5 = (select count(distinct(E2.salary))
from employees E2 where E1.salary<=E2.salary)
10. Is it possible both Read only and with check?
No
11. Can you alter a view? Add new column
NO
12. Can you truncate a view?
No – table and cluster only possible to truncate
13. after delete row -> sequence -> if its in 97 then next value 99
create sequence bs
increment by 2
start with 100
maxvalue 105
minvalue 95
cycle
nocache
if we delete last one 99 ,
again I add one new row seqid is 101
Truncate -> then nextval will come
Yes
14. Can view be created on view?
Yes
--create or replace view bb as
--select last_name, salary from employees;
--create or replace view bbb as
--select last_name from bb;
15. Can we have more than 1 synonym for same object?
Yes
--create or replace synonym b1 for employees
--create or replace synonym b2 for employees
16. Drop the table, what happens to synonym?
NO
Synonym translation no longer valid
17. Types of Synonyms?
Ø Private synonym is confined to a particular user.
Ø Public can be used by all users.
18. How to modify column of a table using RENAME?
ALTER TABLE admin_emp_dept
RENAME COLUMN comm TO commission;
19. Can I use out parameter in function
Yes
20. can I use out parameter in function
YES
21. Can I use DML in function calling-
YES
22. Different between procedure and function;
23. Can we use DDL in procedure
DDL,DML,TCL allowed
24. Can we use TCL(Commit, rollback) in proc & Fn.
Ø Procedure TCL Possible
Ø But function not possible
25. Can proc all a function(User defined & System Defined
YES
26. Can function call a procedure
NO