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