CS405 - Database Programming using Oracle 11g
Final Term short Notes
Made by Muhammad Ayaz
Join My Study group for Assignment Quiz and exam Files
Group Link
Click to Join
Simple Loop
The simple loop is different from other loops available in other programming languages; simple
loop consists of a structure to repeat statements
Without exit condition considers to be an infinite loop.
Required to exit the loop
DECLARE
rep number(10):=0;
BEGIN
LOOP
if rep >=5 then
dbms_output.put_line ('Value of Rep: ' || rep);
EXIT;
END IF;
rep:=rep+1;
END LOOP;
END;
While Loop
First check the condition and then other loop we be executed
DECLARE
Muhammad Ayaz 03429311964
counter number (4) := 1;
BEGIN
WHILE counter <= 8 loop
dbms_output.put_line (counter);
IF counter=4 then
Exit; -- Loop will be terminated
END IF;
counter := counter +1;
End Loop;
END
Muhammad Ayaz 03429311964
Do-While Loop
A DO-While loop is a statement that executes a statement at least once and then repeatedly executes
the block, or not, depending on a given condition at the end of the block.
We not write the Do keyword in this just we write the loop like simple Loop
Not required to exit the will automatically end the loop if the condition is false
Implementing Do While Loop
DECLARE
current_val number (10):=0;
BEGIN
LOOP
dbms_output.put_line ('Value of count is : ' || current_val);
current_val:=current_val+1;
exit when current_val> 10 ;
END LOOP;
END;
Numeric FOR Loop
Muhammad Ayaz 03429311964
A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to
execute a specific number of times. It’s called Numeric because it requires an integer as its terminating
point.
Starting limit to upper limit
Not jump in the value in the for loop
If the first value of the for loop is less then the upper value the control move to the for loop
body
DECLARE
i NUMBER:= 100;
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line ( ' Value of I : ' ||i);
i:=i+1; --Error
END LOOP;
end;
FOR Loop with Reverse Option
By default, iteration proceeds from the initial value to the final value, generally upward from the lower
bound to the higher bound. You can reverse this order by using the REVERSE keyword.
DECLARE
loop_start Integer: = 1;
BEGIN
FOR i IN REVERSE loop_start..5
LOOP
DBMS_OUTPUT.PUT_LINE ('Loop counter is ' || i);
END LOOP;
Muhammad Ayaz 03429311964
END;
Continue When Statement
Continue-When statement is also use to unconditionally exit from current iteration of the loop. As in the
case of continuous statement, statements after continues-when statement is also skipped and the
control is transferred to the next iteration.
DECLARE
x NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
CONTINUE WHEN x < 3;
DBMS_OUTPUT.PUT_LINE
('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END
Nested Loops Loop
can be nested with any other or same type of loops. A nested loop is a loop within a loop, an inner loop
within the body of an outer one. How this works is that the first pass of the outer loop triggers the inner
Muhammad Ayaz 03429311964
loop, which executes to completion. Then the second pass of the outer loop triggers the inner loop
again.
The nested loop or inner loop run completely in a time
BEGIN
FOR v_outerloopcounter IN 1..2
LOOP
FOR v_innerloopcounter IN 1..4
LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || v_outerloopcounter);
DBMS_OUTPUT.PUT_LINE(' Inner Loop counter is ' || v_innerloopcounter);
END LOOP;
END LOOP;
END;
Cursor In SQL
Cursor is a Temporary memory or Temporary work Station
A SQL Cursor is a database object used to retrieve the Data from a Reset set one Row at a time
Types of Cursor
Implicit cursor
This Cursor is Created Automatically by Oracle Server Whenever SQL Statement are executed
And the user is unware
Explicit Cursor
Are defined by a programmer for going more control over the context Area and Cursor Attribute
Step To create a cursor
Muhammad Ayaz 03429311964
1 Declare the cursor
2 open the Cursor
3 Fetch
4 Close the Cursor
5 De allocating cursor
Declare Cursor_name Scroll For
Select *from Table_name
Method of Cursor
Next for the Next Row
Prior For the Previous Row
First Fort the First Row
Last For the Last Row
Absolute to Find the Specific Record
Relative n
For example
Fetch First From Cursor_name
Fetch Prior From Cursor_Name
Fetch last From Cursor_Name
Attribute
% Found
Is Return true if Next Row is Available in Cursors to process
Muhammad Ayaz 03429311964
%NOT Found
The opposite of Found Next value check if Find false if not found the return True
% Isopen
A cursor is not when is Already open Check open or not check Statements
% RowCount
Return numeric value count How Many Record well change the count of the DML command
return only last action
How many row well affected in the last DML command they find the count of the Row
DECLARE
cursor c1 is select hiredate, deptno,job from emp where job like ('%MAN%');
doh [Link]%type;
dno [Link]%type;
jd [Link]%type;
BEGIN
open c1;
LOOP
fetch c1 into doh,dno,jd;
dbms_output.put_line('Date of hiring: ' || doh );
dbms_output.put_line('Department no: ' || dno );
dbms_output.put_line('Job: ' || jd );
EXIT WHEN c1%notfound;
END LOOP;
END;
Muhammad Ayaz 03429311964
Errors
Errors can be defined as compile time syntax issues
Exceptions
exception is a PL/SQL error that is raised during program
Need for Exception Handling
Exception cause the program to terminate the program abnormally and that is not practically possible to
foresee all such problematic events.
How Exceptions Handlin Works
Whenever a run-time error is generated, an exception is raised. The flow or the normal execution of the
program stops. Control is transferred to the EXCEPTION block of the PL/SQL where the necessary actions
are defined to handle exception.
Types of Exceptions
Internal Exceptions: These are pre-defined and they are executed in case of violations of any database
rule by the program. Internal exceptions are raised automatically.
User-Defined: PL/SQL also allows user to define their own exceptions according to the need of the
program. This type of exceptions is not raised automatically and user should raise it.
Below is the basic syntax of an exception:
Declare Section
All the declarations
Begin
Executable statements
Exception Section – One per block
Muhammad Ayaz 03429311964
Exception handling statements
End
Example
Declare
name varchar2 (30):='';
Begin
SELECT ename into name
FROM emp
WHERE empno=9882;
dbms_output.put_line(Name);
Exception
WHEN no_data_found then
dbms_output.put_line('Record not matched');
WHEN others then
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
End;
User Defined Exception?
Any exception defined by the user in order to avoid any specific problematic situation or event are
known as User Defined Exceptions. User must have to define them explicitly as they are not available by
default in the system.
Muhammad Ayaz 03429311964
The syntax of user defined exception is as follows:
DECLARE
user_define_exception_name EXCEPTION;
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;
Write a PL/SQL block to insert row in emp table, while inserting data in emp table if salary
is
less than minimum salary then raise an exception other.
Solution:
DECLARE
low_sal EXCEPTION;
min_sal NUMBER:= 10000;
new_sal NUMBER:= 8000;
BEGIN
INSERT INTO EMP(EMPNO, DEPTNO, SAL)
VALUES (4000,20,new_sal);
IF new_sal < min_sal THEN
RAISE low_sal;
END IF;
Muhammad Ayaz 03429311964
EXCEPTION
WHEN low_sal THEN
DBMS_OUTPUT.PUT_LINE ('Salary is less than '||min_sal);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END
Raise_Application_Error
It’s a built-in procedure lets you issue user-defined ORA- error messages from stored subprograms. That
way, you can report errors to your application and avoid returning unhandled exceptions.
Raise vs Raise_Application_Error
Raise is used to call pre-defined or user-defined exception while on the other hand
Raise_application_erorr let the developer show the customized message with number. PL/SQL Block
terminates the processing when there is some error. If we want to raise an exception and change the
path of processing developer can place RAISE statements. By Raise statement, developer can raise user
defined exceptions
Declare
name [Link]%type;
salary [Link]%type;
Begin
select ename, sal into name, salary from emp where empno=7369;
if salary <5000 then
Raise_application_error(-20030, ‘Invalid salary’);
else
dbms_output.put_line('Valid Salary');
Muhammad Ayaz 03429311964
End if;
Exception
when others then
dbms_output.put_line(SQLERRM);
End;
. What is exception_init Pragma
The EXCEPTION_INIT pragma associates a user-defined exception name with an error code. Ora – Error
no can be intercepted and specific handler can be written. In other words, it allows you to handle the
Oracle predefined message by your own message which means you can instruct compiler to associate
the specific message to oracle predefined message at compile time.
DECLARE
user_define_exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number)
What is Collection?
A collection is an ordered group of elements, all of the same type. It is a general concept that
encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that
determines its position in the collection and data is accessed through index which can be random.
Although collection can hold multiple data but still size of collection is dynamic
Associative arrays
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a
corresponding value in the array. The key can be an integer or a string. Assigning a value using a key for
the first time adds that key to the associative array
Muhammad Ayaz 03429311964
Records
Records is a composite datatype which means that it can hold more than piece of imformation to save
it a time .
Types of Record
Table Base
Cursor Base
User Defined
Table Based
Declare
Emp-rec emp% rowtype;
Begin
Select * into emp_rec From emp
Where emno = 89;
Dbms_output.put_line(emp_rec.Name || emp_rec.job);
Exception when no-data-found Then
Dbms_output.put_line(‘noting’);
End;
What is Sub-Program
Subprogram created to perform a particular task. These subprograms are combined to form larger
programs
What is Procedure?
Muhammad Ayaz 03429311964
Procedure is a database object and a type of subprogram which is created to perform a certain task.
Procedure can performs one or more tasks
Procedure may or may not return value
Procedures are normally used for executing business logic.
CREATE OR REPLACE PROCEDURE name_proc
AS
cursor c1 is select * from emp where ename like ('%AK%');
c2 c1%rowtype;
BEGIN
loop
fetch c1 into c2;
dbms_output.put_line ([Link]);
exit when c1%notfound;
end loop;
END name_proc;
Output of the Program:
Procedure Created
Calling the Procedure:
Begin
Name_proc;
End;
two parameters procedure
IN Parameter
An IN parameter lets you pass a value to the Procedure.
It is a read-only parameter.
Inside the subprogram, an IN parameter acts like a constant. It cannot be
Muhammad Ayaz 03429311964
assigned a value
Out Parameter
It returns value to the calling program.
Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference
the value after assigning it.
Function
Function is a database object and a type of subprogram which is created to perform a certain task. A
function is a named PL/SQL Block which is similar to a procedure
It can receive zero or more parameters as an input.
It can return value to the calling environment.
Returning value is Mandatory. In other words, Procedure may or may not return
value
whereas function should return one value
Function are normally used for computation
Function vs. Procedure
Function vs. Procedure comparison, there are following points:
Both are Database Objects.
Both can receive one or more parameters
Procedure can performs one or more tasks whereas function
performs a specific task.
Procedure may or may not return value whereas function
should return one value.
Functions are normally used for computation whereas
procedures are normally used for
executing business logic
Muhammad Ayaz 03429311964
CREATE [OR REPLACE] FUNCTION function_name [ (parameter
[,parameter]) ]
RETURN return_datatype
BEGIN
< function_body >
Return value
END function_name;
Trigger
A trigger is a database object that automatically executes a specified action or set of actions in
response to certain database events, such as inserting, updating, or deleting data from a table
CREATE TRIGGER Trigger_name
AFTER INSERT
ON Table_Name
FOR EACH ROW
BEGIN
INSERT INTO Backup_Table (:[Link], :[Link], :[Link]);
END;
1. Row Level Triggers :
Fired for each row affected by the triggering statement.
For instance, in an UPDATE statement that modifies multiple rows, a row-level
trigger is invoked once for each row modified.
Muhammad Ayaz 03429311964
If the triggering statement doesn't affect any rows, the row-level trigger is not
executed for that statement.
2. Statement Level Triggers :
Fired once on behalf of the triggering statement, regardless of the number of
rows affected.
For example, if a DELETE statement removes several rows from a table, a
statement-level DELETE trigger is triggered only once, regardless of the number
of rows deleted
Dropping the Triggers
The syntax is as follows:
drop trigger Trigger_Name;
What is package?
It is Database Object which group together different Database Objects. groups logically related PL/SQL
types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in
the database.
Step-1:
CREATE PACKAGE Name of Package AS
Prototype of Database Object (Function, Procedure, Trigger)
End Package Name
Step-2:
CREATE PACKAGE Body Name AS
Implemtation of Prototype of Database Object (Function, Procedure, Trigger) defined in
Package Specification
End Database Object Name
Join My Study group for Assignment Quiz and
End Package Name
exam Files
Group Link
Muhammad Ayaz 03429311964
Click to Join