CURSOR:
Cursor is a variable, works in all rdms and use to store multiple rows i.e cursor is based on select statement.The data ,which is stored in cursor, can not be manipulated we can only fetch from top to bottom.
DECLARE
declare variables
------
CURSOR cursor_name is SELECT STATEMENT;
BEGIN
---------
--------
OPEN cursor_name;
For /while/do while/switch
LOOP
FETCH cursor_name into var_name;
--- dbms_.... output
END LOOP;
CLOSE cursor_name;
END;
/
Example:
declare
x number;
y number;
cursor c1 is select onum from orders;
begin
dbms_output.put_line('here');
open c1;
for x in 1..11
loop
fetch c1 into y;
dbms_output.put_line('hello '||y);
end loop;
close c1;
end;
/
CURSOR ATTRIBUTES:
%notfound
%found
%isopen
%rowcount
PARAMETERIZED CURSOR:
Declare
Cursor c1(sal number default 17000) is select*from employee where salary>sal;
Begin
For x in c1(&sal)
Loop
Dbms_output.put_line();
End loop;
End;
/
Cursor c1(sal number default 17000,name varchar2) is ……
Note:no need to define size if varchar2 here;
TYPES OF CURSOR:
1.EXPLICIT(programmer created)
2.IMPLICIT(oracle created)
The implicit cursor is used to check the status of last dml operation.
The name of the implicit cursor is sql
sql%notfound
sql%found
sql%rowcount
sql%isopen
explicit:
declare
x number;
begin
update employee set salary=salary+1000 where salary=&sal;
if sql%found then
x:=sql%rowcount;
dbms_output.put_line(x||‘rows updated’);
end if;
end;
/
Exception:
1.Exception is used to handle errors i.e error handler
2.all exceptions group together at the end of the block
There are 35767 errors in oracle
18 predefined exception
As
1. too_many_rows
2. no_data_found
3. value_error
4. invalid_number
5. zero_divide
6. others
declare
x employee%rowtype;
begin
select*into x from employee where salary=&sal;
dbms_output.put_line();
exception when too_many_rows then
dbms_output.put_line();
when no_data_found then
dbms_output.put_line(‘there is no record’);
end;
/
Explicitly raised:
Declare
X number;
abcd exception;
begin
select *into x from employee where eid=&eid;
if x.salary>10000 then
else
dbms_output.put_line(x.salary||’<10000’);
exception abcd
dbms……
end;
/
Comment:
Double desh(--) for single line
/* */ for multiline
Array:
1.Use to store multiple element of same datatype
2.indexing starts from elsewhere
3.subscript need not start from 0;
binary_integer
range from -2 billion to +2 billion
Array declaration:
Delare
Type T is table of number(6) indexby binary_integer;
y T;
I binary_integer:=1;
Begin
y(1):1000;
y(1):2000;
y(1):3000;
while i<4
loop
dbms_output.put_line(y(i));
i=i+1;
end loop;
end;
/
Stored procedure:
Procedures don’t return any values
create or replace procedure abc
is
begin
dbms_output.put_line('Hello');
end;
//
Then..
Sql>execute abc;
Parameterized Procedures:
in: used to send the value to procedure
out:used to get the value from procedure
inout:used to send and get value from and to procedure
create or replace procedure get_fees
(f out fees_details.fee%type,rl in fees_details.roll%type)
is
begin
select fee into f from fees_details where roll=rl;
end get_fees;
/
create or replace procedure sal_info(id in number,sal out number)
is
begin
if id>10 then
sal:=15;
elsif id>20 then
sal:=25;
end if;
end;
/
declare
re number;
begin
re:=12;
sal_info(re);
dbms_output.put_line('krrish'||''||re);
end;
/
create or replace procedure sal_info(sal in out number)
is
begin
if sal>10 then
sal:=sal+15;
elsif sal>20 then
sal:=sal+25;
end if;
end;
/
declare
re number;
begin
re:=12;
sal_info(re);
dbms_output.put_line('krrish'||''||re);
end;
/
Function:
Functions are sub-programs that returns a value when called
Create or replace function pqrst return number
Is
Begin
return 100;
end;
/
Sql> select*from where eid=pqrst;
Create or replace function addition(x number,y number) return number
Is
z number;
begin
z:=nvl(x,0)+nvl(y,0);
return z;
end;
/
Sql>select addition(salary,deptid) from employee;
Encryption of files:
sql>cmd
sql>type pqr.sql
sql>wrap iname=pqr.sql oname=pqr.plb
Delete:
sql>del pqr.sql
lockDemo.sql
begin
insert into demo values(‘Before Sleep’);
commit;
dbms_lock.sleep(10);
insert into demo values(‘after sleep’);
commit;
end;
/
Triggers:
Trigger is a procedural code which executes automatically in response to certain event on a particular table in the database.
Parts of a trigger:
There are three main parts of a trigger
1.trigger event: A trigger event can be on insert,on update,on delete
2.trigger constraint:
3.trigger action
Type of trigger:
1.row level trigger:fires once for each row affected by the dml operation.
2.statement level trigger:fires once for number of rows
3.before trigger:for only DML operation
4.after trigger:f or only DML operation
5.schema trigger:for DDL operation
. . . . . . .
Schema trigger:
Create or replace trigger Alert
Before LOGOFF on database
Begin
Insert into demo values(‘user logged off’);
End;
/
Shutdown/Startup:
1.Normal:oracle will wait for all active user to disconnect their session
2.immidiate:oracle will rollback active transactions and disconnect all active users
3.Abort:all user session will be terminated immediate uncommitted transaction will not be rollback i.e commit first and then disconnect
Create or replace trigger Alert before/after
Shutdown/startup on database
Begin insert into demo values(‘database is closed/opend’);
End;/
Savepoint:
Sql> insert query
Sql>savepoint abc;
Sql>update query
Sql>savepoint pqr;
Sql>delete query
Sql>savepoint rst;
Sql>rollback to pqr;
Package:
It is a collection of local procedures and functions group together related procedure and function into package
Pak.sql
Create or replace package p1
as
procedure one;
procedure two;
function three return number;
end;
/
. . . . . .
Pak_body.sql
Create or replace package body p1
As
Procedure one
Is
. . .
Begin
. . . .
End;
Procedure two
Is
. . .
Begin
. . . .
End;
function three return number
Is
. . .
Begin
. . . .
End;
/
H1.sql
Begin
p1.one;
P1.two;
End;
/
Select*from employee where eid=p1.three;
Index:
1.to sppedup the serach/select operation
2.no upper limit on number of index per table;
3.larger number of indexes slow would be DML operation
4.null values not store in index(a suggestion)
5.common field in join operation should always be indexed.
. . . . . . . . . . .
Sql>Desc user_indexes
. . . . . . . . . . .. .
Sql> create table pqm(p number,q number);
Sql> create index p_index on pqm;
Sql>select index_name,table_name,index_name from user_indexes where table_name=’pqr’;
………………………
Make your own constraint:
Sql>Add constraint column_name primery key(column_name);
-----------------
Sql>flashback table pqr tp before drop;(recycle bin)
Sql>pruge table pqr;(permanent delete)
Sql>drop table pqr purge;
Sql>select original_name,type,droptime from recyclebin;
No comments:
Post a Comment