When row level trigger access the table on which it is based then mutation error will occur.To eliminates this error, use pl/sql table to store all values, and call this table in statement level trigger.
Diff between correlated subquery and normal subquery?
In correlated subquery, parent query executes first, then subquery exceutes.In Normal subquery, subquery executes first, then parent query executes.
Pragma serially reusable?
Normally when packaged procedure is called all the package is loaded into the memory and persists entire life of the db session.
When we used prgma serially reusable, it removes the package from the memory after exceution the package.
pragma exception_init
For some oracle errors , pl/sql defined oracle errors, for others we have to use when others or prgma exception_init to display error numbers and messages.
pragma autonomous transaction
it commits or rollback the particular transaction irrespective of the main transaction.
%type : Provids datatype of a table column;
Ex: col tab1.col%type;
%rowtype : Provids record type that represents a row in a table.
Ex : row emp%rowtype
Cursors: when query returns multiple rows we can explicitly declare a cursoor to process rows. We can pass parameters to cursors. We can use open-fetch-close or for loop are used to process the rows.
2 types : Implicit cursors, Explicit cursors
Implicit cursors : PL/sql implicitly declare a cursor for all dml statements and queries that return a single row.
Explicit cursor : If the queries return more than one row we have to use explicit cursor.
Cursor attributes
%found, %notfound, %isopen, %rowcount
%isopen : it returns true if cursor is already open otherwise returns false;
ex if c1%isopen then close c1; else open c1; end if;
in case of implicit cursor it always false because
oracle closes the sql cursor automatically after executing the sql stmt.
%rowcount : it reurns number of rows it effects for the last executed stmt.
Ex: delete from emp ;
if sql%rowcount > 10 then … end if;
if select into stmt returns more than one value of row count is 1;
%found : if insert ,delete, update, select stmt effected one or more rows it returns true. Other wise it yeilds false.
%notfound : it is just opposite of %found;
we can pass parameter to a cursor.
Declare
Cursor c1(deptc varchar2) is select * from emp where dept=deptc;
Begin
For I in c1(20) loop
…
end loop;
end;
Cursor variables
it holds memory location of the varibles.
It is not tied to specific query.
Once we define a cursor we can attach to any query.
We can pass it as parameter to stored procedures
its data type is REF CURSOr.
strong cursor type has return type.
Week cursor types has no retrurn type.
We can not declare a cursor variable in a package.
Cursor varibles do not have persistant state .
declare
Type empcurtype is ref cursor return emp%rowtype;
Emp_sv empcurtype;
Begin
If ch = 1 then
Open emp_sv for select * from emp;
If ch = 2 then
Open emp_sv for select * from dept;
End if;
Fetch empsv into :emprec
Close empsv;
We can not use cursor variables in a package.
we can not fetch a cursor variable on server side. We can fetch on client side.
The query associated with a cursor variable in open-for stmt cannot be for update.
Exceptions
When an error occurs, exception is raised, pl/sql stops execution and control transfers to the exception handilng part, there excution resumes.
Internal exceptions raise auomatically.
Ex:
no_data_found : when select into stmt returns no rows
Too_many_rows : if select into stmt returns more than one row;
Dup_val_on_index : when try to insert duplicate values on unque index column.
Zero_devide : when u try to devide by zero. Because result is undefined.
Cursor_already_open : raises when we try to open already open cursor;
Invalid_cursor : when try to close unopened cursor;
Invalid_number : if string does not a valid number;
Not_logged_on : if not connected to oracle
Login_defined : if u try to logging with invalid password
Timeout_on_resource if timeout occurs while oracle is waiting for resource.
Program_error: if pl/sql has internal problem
Storage_error: if pl/sql runs out of memory or memory corruption.
User defined exceptions
UnLike predefined exceptions user can define exception on his own.
These exceptions must be defined in declarative section.
we must be raised explicitly by RAISE stmt.
We can also define pre defined exceptions explicitly.
Ex : declare
Out_of_range exception: no need to define in declaration
Begin
If I = 10 then
Raise out_of_range; or raise invalid_number
End if;
Exception when out_of_range then
……
end;
PL/SQL defined some predefined exceptions for some oracle errors. For others we have to use when OTHERS or PRAGMA EXCEPTION_INIT handlers. In these we can use SQLERRM, SQLCODE functions to display the oracle error codes and messages.
Raise application error : when raise application error is called it ends the subprogram and returns the user defined error and message to the calling application. The calling application gets the pl/sql exception, which it can process using sqlerrm, sqlcode in OTHERS/ PRAGMA EXCEPTION_INIT HANDLERS.
If x= 10 then
Raise application_error(-100,’error in bank’);
End if;
When others then
Sqlerrm(sqlcode);
Return;
End;
create or replace PROCEDURE ya1svr1 is
s number(10);
v varchar2(10);
begin
BEGIN
select deptno into s
from emp
where emp_no='11';
if s is null then
raise_application_error(-20000,'Hello dept no is null');
end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
raise_application_error(-20000,'Hello dept no is null');
END;
-- return 1;
end;
/
SCOPE Exceptions defined in the block are local to that block and global to all its sub blocks. When the same exception is defined in the sub block it replaces the global exception. Enclosing blocks can not reference the exceptions declared in the sub blocks.
Propagation Whenever exception is raised PL/sql looks in to the current block, If it is not exists then it propagates to its successive enclosing blocks until handler is found. If there is no handlers then, it returns unhandled exception to the host environment.
RERAISE When u want to reraise the exception, handle it locally then pass it to the enclosed block exception.
Exceptions raised in declarations can not be handled
ex:constant Only one exception at a time can be active in exception handline part of a block.
Dynamic sql
we can execute all sql statements in pl/sql using execute immediate. We can change the some part of dml statements at run time by replacing the placeholder with bind variable.
Open-for
Execute immdiate(‘drop t able :tabname’) using x;
declare
type sv is REF CURSOR;
c1 sv;
a1 number(10);a2 varchar2(10);
sql_stmt varchar2(200);old_loc varchar2(15);kl number(10);
begin
execute immediate 'create table aa(eno number(10),name varchar2(10))';
execute immediate 'insert into aa values(''1'',''2'')';
execute immediate 'insert into aa values(''2'',''3'')';
open c1 for select eno,name from aa;
loop
fetch c1 into a1,a2;
exit when c1%notfound;
dbms_output.put_line(a2);
end loop;
close c1;
end;
/
Pragma Autonomous Txn:
We can commit/rollback the transaction irrespective of the main transaction.
Bulk binding
Binding improve the performance by reducing number of switches between sql and pl/sql.
For each iteration pl/sql sends sql statements to sql engine.To reduce the number of switches between sql and pl/sql we can use bulk binding.
Forall I in c1.first..c1.last
Insert into tmp values(C1(I),i.dd);
BULK COLLECT
We can use bulk collect to fetch all records at once to an array.
Declare
Type v_ary is table of number;
V_ary1 v_ary;
Begin
Select * bulk collect into v_ary1 from emp;
End;
declare
type tab_type is table of test%rowtype;
tab_arr1 tab_type;
begin
select * bulk collect into tab_arr1 from test;
FOR I IN TAB_arr1.FIRST..TAB_ARR1.LAST LOOP
dbms_output.put_line(tab_arr1(i). MAN_PAYH_LOC_CODE);
-- dbms_output.put_line(tab_arr1(i).empname);
-- dbms_output.put_line(tab_arr1(i).deptno);
end loop;
end;
/
BULK COLLECT WITH RETURNING INTO
It return the result of dml statements into an array.
Returning enmae,sal bulk collect into bi
NoCopy used in subprograms to pass parameters by reference
DB Triggers
DBT can be applied on tables, views and events of db.Database triggers fire implicitly when insert/update/delete statements issued against to the table.DBT does not fire on data which is already loaded into the table.We can disable or enable the triggers any time. Row level & Statement level triggers can be defined. Before and after can apply on both row level and statement level triggers.
Row level trigg’s fire once for each row effected by the triggering sql stmt
Statement level triggers fire only once regardless of the no of rows effected by the sql statement.
Create or replace trigger trg_name before or after insert or delete or update on table name
For each row
Begin
If inserting then … end if;
End;
INSTEAD OF trigger We can apply this on views. We can insert /update/delete rows directly on the underlying base tables of the views. We can not use this if the view consists of Distinct,group by, connect with, start clause of the query.
Subprograms :
subprograms are like pl/sql blocks, it can be stored in database.
2 types of subprograms . functions and procedures.
Function return a value.
Procedure does not return a value. We can get values throught out parameter.
But function can be used in select statements like normal functions.
We can use NOCOPY command to reference large objects.
Adv of subprograms : High productivity, better performance, memory saving,application integrity,tight security.
Subprogams has declarativepart, executable part, exception handling part.
Parameter modes :in, out, inout
In parameter used to pass values to subprograms we can not assign values to the in paramter.
Packaged sp: Sp defined in a package are called packaged subprograms.
Standalone sp: those defined independently is called standalone subprograms.
Local sp : those defined within another sp are called local sp ,which can not be refferenced by other applications. We can call subprograms with in DBT or another subprograms.
Functions return a value and procedures does not return a value. We can pass parameters.
PACKAGES:
Modularity:It groups logically related items, types, cursors, exceptions, procedures and functions.
Easier Application design:it has spec & body. Spec variables can be accessed any where in application. We can code and compile spec without body.That means interface items can be designed at the initial stage. Later we can code body.
Information Hiding:Spec contains public declarations, of course we can make it private also.
Added Functionality
These declarations are global to the packge.
Body has intialization part. Here we can declare private decalrations. It runs only once when the package is loaded into memory. These variables are private variables which are used only in the packaged procedures.
All packaged varibles persist entire duration of the session.
Better Performance : When we call packaged procedure first time, all package is loaded into memory. For forther calls no disk i/o is required.
If the package spec has only varibles, constants, exceptions then package body is not nessasary.
Interfaces between packages are simple.
Collections:
Collections are like 1-D arrays. It can have other collections/objects/records to make it multi dimentional.
Each element has uniquie subscript.
Collections can be passed as parameters to the function. Functions can return collections.
Types : index by tables, nested tables, varrays
Index by tables
used for temporary storage of data .we can not store in db.
We can make persistant for the life of db session by declaring the type in a package and assigning the values in a package body.
Its subscripts are either number or characters.
Nested tables:
Nested tables can be stored in db.
It has non consecutive subscripts.
No need to define size, size is automatically incremented by using EXTEND method.
Individual elements can be deleted.
Varrays:
Example :
declare
type sv1 is varray(100) of tb_stat_dolly_inventory_hist%rowtype;
sv sv1;
begin
select * bulk collect into sv from tb_stat_dolly_inventory_hist;
for i in 1..sv.count loop
insert into stat_dolly_inventory_hist_temp
values(sv(i).AUTOID,sv(i).CHANGE_TIME,sv(i).LOCATION,sv(i).EMPTY_TRAINS,
sv(i).AUTOMATIC,sv(i).USERNAME,sv(i).TIME_STAMP);
end loop;
end;
/
We can store varrays in db. It has consecutive subscripts.
Size must be defined at the time of creation of varray.
Individual elements can not be deleted.
create or replace procedure varray1 is
type type_1 is record (a number,
b varchar2(100),
c number,
d number);
type type_2 is table of type_1 index by binary_integer;
emp_varray type_2;
type sv_type1 is varray(100) of number;
type sv_type2 is varray(100) of varchar2(100);
type sv_type3 is varray(100) of number;
type sv_type4 is varray(100) of number;
sv1 sv_type1;
begin
select * bulk collect into emp_varray from emp2;
-- sv1 := emp_varray(i).a
execute immediate 'delete from temp_table';
for i in emp_varray.first..emp_varray.last loop
-- insert into sv1 values(emp_varray(i).a);
insert into temp_table values
(emp_varray(i).a,emp_varray(i).b,emp_varray(i).c,emp_varray(i).d);
dbms_output.put_line(emp_varray(i).a);
end loop;
end;
/
objects :objects are composit data structurs that group related data items. Once we create an object it becomes oracle data type.objects can also have methods. These methods are either static or instance methods
Guidelines to create an index:
1.Query should not select the rows greater than 15-20%.
2.index columns used for joining of multiple tables in where condition
3. Primary and unique keys automatically have indexes
4. Small tables do not require indexes.
5. when the cardinality is high or on unique columns we can use indexes.
6. when the column contains nulls and we are selecting only not null values then we can create index on that column.
Columns with the following characteristics are less suitable for indexing:
· The column has few distinct values (for example, a column for the sex of employees).
· There are many nulls in the column and you do not search on the non-null values.
How to increase the speed of the query: It depends on
Number of USERs logged in
Number of transactions going on
Nature of those transactions
Size of SGA , Rollback segments
Optimizer mode (Rule / Cost based)
Using of INDEX
Number of Soft Parse going on
The structure of the SELECT query itself
LONG and LONG RAW columns cannot be indexed.
The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.
Limit the Number of Indexes per Table
A table can have any number of indexes. However, the more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.
Thus, there is a trade-off between speed of retrieval for queries on a table and speed of accomplishing updates on the table. For example, if a table is primarily read-only, then more indexes might be useful; but, if a table is heavily updated, then fewer indexes might be preferable.
TABLE types
Regular table, Partitioned table,Index organized table,Cluster table,External table,Temporary table
Nested table,Index by table,Varrays
Table storage parameters
INITIAL It indicates the size of the first extent When segments is created.
NEXT It indicates the size of the next extent to be allocated for a
segment.
PCTINCREASE it indicates how much percentage of space to be incresed over
the last extent for the next extent.
MAXEXTENTS indicates maximum no of extents to be allocated for a segment.
MINEXTENTS Indicates minimum number of extents to be allocated when segment
is created first time.
PCTFREE indicates the percentage of space to be left for later updates.
PCTUSED Once free space in a block reaches PCTFREE , no new rows are inserted
until the free space reaches to PCTUSED.
INITRANS defaut value is 1 for tables and 2 for indexes.
Indicates minimum number of concurent transactions access the rows in a datablock
MAXTRANS Indicates maximum number of concurent transactions access the rows in a datablock. Maximum by default is 255.
Temporary Tables
Temporary tables are used to store temporary data.
Data in these tables are session or transaction specific.
Records will be automatically deleted from the tables when commit, ofcourse we can preserve the rOws also.
We can create views ,indexes,triggers on temporary table.
Dml stmts on temp table do not generate redologs for the data changeas.
Trucate removes data of the session only.
Temporary tables use temporary segments.
Temporary tables and their indexes do not atomatically allocate a segment when they are created. segments allocated when first insert is performed.
External tables:
External tables do not reside in database.only select ,sort,join data.
No dml operation performed on external table.indexes can not be created.
Create datafiles in some directory
Create or replace directory ext_tables as ‘c:\ext_tables’;
Create table sv_tbl(emp varchar(10,ddd number(10))
Organization external
(
type oracle_loader
default directory ext_tables
access parameters
(
records delimited by newline
fields terminated by ‘,’
missing field values are null)
location (emp_ext1.dat’,’emp_ext2.dat’)
)
reject limit unlimited
Partitioned table : Table has one or more partitions, each partition stores range of rows and each partition located in different table space.
Partition is useful for large tables can be queried and manipulated several processes simultaneously.
4 types of partitions
1.Range partitioned tables
2.Hash partition
3.List partition
4.composite partition
Partition key values are in range then we can choose range partion .
When we know the list of values and want to create separate partion for each value then we can create list partion.
When we do not know how much data be in range or sizes of the partion vary then we have to choose hash partion.
Composite: it chooses the range partion method to create partions and use hash partion method to create sub partions.
Regular Table
1. ROWID uniquely identifies a row; primary key can be optionally specified
2. Implicit ROWID column; allows building physical secondary indexes
3. ROWID based access
4. Sequential scan returns all rows
5. UNIQUE constraint and triggers allowed
6. A table can be stored in a cluster containing other tables.
7. Distribution, Replication, and Partitioning supported
Index-Organized Table
1. Primary key uniquely identifies a row; primary key must be specified
2. No implicit ROWID column; cannot have physical secondary indexes
3. Primary key based access
4. Full-index scan returns all rows in primary key order
5. UNIQUE constraint not allowed (Oracle8 restriction) but triggers are allowed
6. An index-organized table cannot be stored in a cluster.
7. Distribution, Replication, and Partitioning not supported (Oracle8 restriction)
Cluster tables
it contains one or more tables. One or more columns of these tables are common. Rows of one or more tables share the same value
Clusters have a Cluster key used to identify the rows that need to be stored together.
-- Columns defined in the create cluster make up the cluster key.
2 types of clusters index clusters, hash clusters
After creating the indexed cluster we have to create index on the cluster key before issue any dml stmts against a table in the cluster.
Schema
The collection of database objects that are owned by a particular user are referred to as that user’s schema. Every object in the database belongs to only one schema.
Force view when view create view with columns which are not in tables or we don’t have privileges then view can be created with compilation errors using force option. At the time of operations on view oracle dynamically compiles view.
When view is craeted with multiple tables we can change any one table columns at a time.
Order by clause can be used in views.
Inline views : it is not schema object. It is a subquery with an alias. That can use like a viw in sql stmt.
Materialized views it increase the speed of the query by precomputing the statistics of join and aggregating functions and store it in the database before executing the query.
Create materialized view emp_sumc
Enable query rewrite
As select deptno,sum(sal) from emp group by deptno;
Execute dbms_utility.analyze_schema(‘scott’,’estimate’);
Execute dbms_mview.refresh(‘emp_sum’);
Provide additional level of table security Restrict access to predetermined rows or columns
Hide data complexity Data from different tables can be combined as a single unit
Simplify commands for the user Rename column
Store complex queries
Present data in a different perspective from that of the base table.
Managing Synonyms
A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.
We can create public synonym to access by all users
Ex :create public synonym
Grant all on
Creating Synonyms
CREATE PUBLIC SYNONYM Public_emp FOR jward.Emp_tab;
Sequence
The sequence generator generates sequential numbers.
Once defined, a sequence can be made available to many users. Multiple users can access the same sequence.
When we call a nextval used multiple times in a row seq increments only once.
To get the values of the sequence we have to use nextval or currval psuedocolumns
Ex select
Select
Insert into emp(emp_no,sal) values(
The following sections explain how to create, alter, use, and drop sequences using SQL commands.
Drop seqence
Creating Sequences
Create sequence
Parameters to indicate
The starting point of the sequence,
The minimum and maximum values,
Interval between sequence values.
The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.
The CACHE option of the CREATE SEQUENCE command pre-allocates a set of sequence numbers and keeps them in memory so that they can be accessed faster. When the last of the sequence numbers in the cache have been used, another set of numbers is read into the cache.
Uses and Restrictions of NEXTVAL and CURRVAL
CURRVAL and NEXTVAL can be used in the following places:
· VALUES clause of INSERT statements
· The SELECT list of a SELECT statement
· The SET clause of an UPDATE statement
CURRVAL and NEXTVAL cannot be used in these places:
· A subquery
· A view's query or snapshot's query
· A SELECT statement with the DISTINCT operator
· A SELECT statement with a GROUP BY or ORDER BY clause
· A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
· The WHERE clause of a SELECT statement
· DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
The condition of a CHECK constraint
ROLLUP
USED TO CALCULATE SUBTOTALS at MULTIPLE LEVELS
IT ALSO CALCULATE GRAND TOTALS.
ROLLUP IS SIMPLE EXTENSION OF GROUP BY
EX : SELECT DEPTNO,JOB,SUM(SAL) FROM EMP
GROUP BY ROLLUP DEPTNO;
CUBE
used to calculate subtotals at multiple levels
it also calculate grand totals
it gives cross tabular report with a single stmt
SELECT DUPLICATE ROWS :
USE SELF JOIN, we must specify all columns that makes the row a duplicate in sql clause.
Select 1,2,3 from TA a
Where rowid > (select min(rowid) from TA b where b.1 = a.1 and b.2 = a.2 and b.3 = a.3);
Select all duplicate row
Select col1,col2 from emp a
Group by col1,col2
Having count(*) > 1;
Delete duplicate rows
1. Delete from ta a where a.rowid> (select b.rowid from ta b where b.1 = a.1 and b.2 = a.2 and b.3 = a.3);
2. Delete from emp e1 where rowed not in (select max(rowid) from emp e2 group by empno);
Highest 3 salary employees:
We can create a view using order by clause and also we can use inline. , after creating a view select rows where rownum <>Highest 3 salaries
Select empno,sal from (select empno,sal from emp order by desc) where rownum <3;>Second heighest salary
X : empno , salary
Select max(salary) from x where salary < (select max(salary)from x); Third heighest salary
Select max(salary) from x
where salary < (select max(salary) from x where salary < (select max(salary) from x)); second lowest salary
select min(salary) from x
where salary > (select min(salary) from x);
Truncate Table
It is a DDL command
We can not use where conditions
Data can not be rolled back (rollback segment will not be generated)
Corresponding indexes also be truncated
Delete triggers do not fire when this command is used.
A table that is beeing referenced by a foreign key can not be truncated
Indexes are used in Oracle to provide quick access to rows in a table.
Indexes provide faster access to data for operations that return a small portion of a table's rows.
Oracle does not limit the number of indexes you can create on a table. However, you should consider the performance benefits of indexes and the needs of your database applications to determine which columns to index.
Pseudo columns: rowid, rownum,currval,nextval,level.
Rowid : return a rowid of a row in a table.
Rownum : returns a number indicating the irder in which a row was selected from a table.
Level:
Normalization
Ist Normal Form : All key attributes defined. No repeating frames. All attributes depend on primary key.
2nd Normal Form: It includes no partial dependencies.
3rd Normal Form: It contains No transitive dependencies
No comments:
Post a Comment