1)
Declare (optional): we can declare variables, cursors and exceptions
2)
Begin (mandatory): we can write the code to perform an action
3)
Exception (optional): we can write the code to perform an action when error
occurs
4)
End (mandatory): it is an end of the block
Ex:
Declare
v1
number: =2;
v2
number;
v3
number;
Begin
v2:=3;
v3:=v1+v2;
dbms_output.put_line
('sum of v1 & v2 is:'||v3);
end;
SET SERVEROUTPUT ON: It is sql*plus command which will take the result from the
buffer and prints on the screen.
DBMS_OUTPUT.PUT_LINE: DBMS_OUTPUT is a package and PUT_LINE is a procedure take the
result from the program and prints on the buffer.
Types of variables:
Host variable: we
can pass the values at run time it declares with ‘&’.
Ex: declare
Nam varchar2 (10);
NUM NUMBER;
Begin
Select
empno into num from EMP where ename='&nam'; --host variable
DBMS_OUTPUT.PUT_LINE (' id is '||NUM);
End;
Bind variable: it
is the variable which u can declare in calling environment declares with ‘:’
Ex: declare
nam varchar2 (10):='SMITH';
begin
Select JOB into:
num1 from EMP where ename=NAM;
dbms_output.put_line (:num1);
End;
/
Types of conditions:
If then end if: Ex: declare
v_sal number;
v_empno
number:=&empno;
begin
select
sal into v_sal from emp where empno=v_empno;
if
v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status
is low');
end
if;
if
v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high
sal');
end
if;
end;
/
If then elsif end if:
Ex: declare
v_sal
number;
v_empno
number:=&empno;
begin
select
sal into v_sal from emp where empno=v_empno;
if
v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status
is low');
elsif
v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high
sal');
end
if;
end;
/
If then else end if: Ex: declare
v_sal
number;
v_empno
number:=&empno;
begin
select
sal into v_sal from emp where empno=v_empno;
if
v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status
is low');
elsif
v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high
sal');
else
dbms_output.put_line(v_empno||'great
sal');
end
if;
end;
/
Basic:
While:
For:
Cursor for loop: cursor has been called in to the loop
CURSOR: it is nothing but oracle server opens the sql area in order to
execute he sql statements. This sql area is called as cursor. The data in the
cursor is called active set. Cursor will perform the following tasks
-->
It opens the sql area in the memory
-->
Sql area is populated with the data requested by the query
-->
Data is processed as per our requirement
-->
Close the cursor or sql area when processing is done.
Cursors
are of two types
a.
implicit cursor: we cannot control the cursor
b.
explicit cursor: we can control the cursor if select statement
returns more than one row then we use explicit cursor
Cursor attributes are
i)
% is open
ii)
% found
iii)
% notfound
iv)
% rowcount
REFCURSOR: It is a dynamic cursor by using only one cursor to more than
one select statement.
EXCEPTIONS (ERRORS): we get the errors at two times
i)
Compilation time: we can’t handle these type of errors programmatically we need
to
Correct the
code.
ii) Runtime errors: any
programming language must have ability to handle the errors and ecall from them. These are of 3 types
i) Predefined oracle server errors:
don’t declare and allow the oracle server to raise Them implicitly.
ii) Non predefined oracle server errors:
declare and allow the oracle server to raise
Them implicitly.
iii) user defined errors: user can declare
the errors and has to raise them explicitly.
PRAGMA: It is the non predefined exception compiler directly serves
the instructions to the compiler when ever compiler is compiling the code.
RAISE APPLICATION ERROR: It is the user exception looks like oracle predefined error
and it terminate the program the error number should between -20,000 to -20,998
it prints the message and stop the program.
Types of subprograms:
i)
Procedures: it is used to perform an action here we can also define the
parameters and parameters are of 3 types
‘In’ (default): we can pass the values from
calling environment to program it can accept parameters.
‘Out’: we can pass the values from
program to calling environment it can accept parameters.
In out’: we can pass the values from
calling environment to program or program to calling Environment.
ii)
Functions: it returns only one value it is used most probably for
calculating purpose it can accept Parameters.
iii)
Triggers: procedures and functions we need to call explicitly but triggers
can be raised
Automatically when ever
particular event occurs it won’t accept parameters.
Types of triggers
DML triggers: it can be
raised on DML statements
Instead of triggers: used
to modify the complex views.
System Triggers: it can be
raised on two events DDL and Database events.
iv)
Packages: we can group the subprograms it has two parts package body and
package specification
Package specification:
contains the procedures, functions and variables declarations
Package body:
contains the code for the subprograms which are defined in the package Specifications.
With
out package body there can be package specification but not vice versa
Difference between procedures and functions:
Procedure functions
1) in order to perform an action 1) it is
used to compute a value
2) it may or may not return value 2) it has to
return value (only one value)
No comments:
Post a Comment