SQL Functions,
Records, Table and
Cursor
By Nidhi Bhatnagar
Objectives
Using SQL Functions in PL-SQL
Record – Structure like type in PL-SQL
TABLE – Array like type in PL-SQL
Cursor – Concept
Cursor – Implicit and Explicit
Cursor – Different attributes
Explicit Cursor and basic steps
Using SQL Functions in PL-
SQL
Functions like Substr, Instr, Concat, Length
i.e all string and conversion functions work
the same way in PL SQL as in SQL.
Examples :
V_Name := Concat(v_firstname, v_lastname);
V_nm_length := Length (V_name) ;
Using SQL Functions in PL-
SQL
However functions like Decode cannot be
used with PL SQL variables.
Functions like max , min avg are aggregate
functions cannot be used directly with PL
SQL variable
Examples : Following are Invalid or not allowed
v_dept := Decode(v_detpno,10,’Ten’,20,’Twenty’,’Thirty’);
v_max := max (10,20,30) ;
Using SQL Functions in PL-
SQL
Important thing here is that you can still use
all the SQL function , if you writing SQL query
in PL SQL i.e with Select Query
Only place some of these aggregate
functions( Max , Min ) etc cannot be used is
with PL SQL variables or conditional
statements (IF .. Then ) etc .
Example
Example 16 Sample16.SQL
Example 17 Sample17.SQL
Record
Objects of type RECORD are called records.
Records have uniquely named components, which can belong to
different datatypes.
For example, suppose you have different kinds of data about an
employee such as name, salary, hire date, and so on. This data
is dissimilar in type but logically related.
A record that contains such components as the name, salary,
and hire date of an employee would let you treat the data as a
logical unit.
Record
In short Record in PL SQL is similar to
Structure in ‘C’ Language.
A variable of record type basically have
subparts and that we can refer with dot
operator.
Example : v_emp.sal , v_emp.ename etc
Record
Working with Record is a two step process.
Declare a Record Type
Declare variables of Record type
Once this variable is created then you can
work with them like a normal variables.
Record
Declaring a Record Type and its variables.
Declare
Type t_emp is Record
(
Empno number(4)
Name varchar2(25),
Salary number(7,2),
);
-- Now declaring variables
v_emp1 t_emp;
v_emp2 t_emp
So we have v_emp1 , v_emp2 variables of type t_emp , so we
can refer to its parts as v_emp1.Name, v_emp1.salary,
v_emp1.empno
Record
In this case in our begin part of the block. We can use them
like normal variables
Begin
v_emp1.empno := &empno;
Select ename , sal into v_emp1.name, v_emp1.sal
from Emp where empno = v_emp1.empno;
End;
Record
We can assign one Record variable of same type to another
Begin
v_emp1.empno := &empno;
Select ename , sal into v_emp1.name, v_emp1.sal
from Emp where empno = v_emp1.empno;
v_emp2 := v_emp1 ;
End;
Here v_emp2.empno , v_emp2.name, v_emp2.sal will have
same value as v_emp1.empno , v_emp1.name and v_emp1.sal
Example
Example 18 Sample18.SQL
Example 19 Sample19.SQL
Table
PL/SQL provides a composite datatype named
TABLE.
Objects of type TABLE are called PL/SQL tables,
which are modeled as (but not the same as)
database tables.
PL/SQL tables have only one column and give you
array-like access to rows.
The column can belong to any scalar type (such as
CHAR, DATE, or NUMBER).
Table
In short Table in PL SQL is similar to Array in ‘C’
Language.
Difference here is that Array is un-dimensioned ,
what that means is in ‘C’ array subscript position
can be from 0 to any positive number. But in PLSQL
array subscript can be any integer –ve, 0 or +ve
value and in any order.
Example : v_emp(-43), v_emp(0), v_emp(56) ,
v_emp(1) , v_emp(-23) etc
Table
Working with Table is a two step process.
Declare a Table Type
Declare variables of Table type
Once this variable is created then you can
work with them like an array.
Table
Declaring a Table Type and its variables.
DECLARE
--Declaring type
TYPE NumTabTyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
-- Declaring Variable
vnum numTabtyp ; -- This will make vnum as an array of numbers
BEGIN
vnum(0) := 100;
vnum(-43) := 4500;
vnum(25) := 4300;
END;
Here because of writing Index by Binary_Integer we can have
subscript of an array as negative , 0 or positive value.
Binary_Integer should always be written
Table – Few examples
An Array for character values with width for each string as 15
TYPE tCharTyp IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
v_str tcharTyp;
An Array for numbers
TYPE NumTabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_num NumTabtyp;
An Array for empname
TYPE tename IS TABLE OF emp.ename%Type INDEX BY BINARY_INTEGER;
v_name tename;
Note : In PLSQL Table i.e Array you can store any number of
values no restriction or the upper limit
Table - Functions
PL SQL provides with some functions that you can use with table
only.
First: Gets the index or position of first element in array.
Last : Gets the index or position of last element in array.
Next (n) : Gets the index or position of element next to element at position
‘n’ in array.
Prior (n) : Gets the index or position of element Prior to element at position
‘n’ in array.
Delete (n): Deletes the element at position or Index ‘n’ in the array.
Count: count the total number of elements in the array
Example
Example 20 Sample20.SQL
Example 21 Sample21.SQL
Example 22 Sample22.SQL
Cursors
When you execute a SQL statement from PL/SQL,
the Oracle RDBMS assigns a private work area for
that statement.
The work area contains information about the SQL
statement and the set of data returned or affected
by that statement.
The PL/SQL cursor is a mechanism by which you
can name that work area and manipulate the
information within it.
Cursors
In its simplest form, you can think of a cursor
as a pointer into a table in the database.
For example, the following cursor declaration
associates the entire employee table with the
cursor named employee_cur:
CURSOR employee_cur IS
SELECT * FROM employee;
Cursors
In PLSQL only way we can refer to a SQL query
that returns muliple rows is through cursors.
Imagine the query in the last slide returned
following rows.
Work Area
Employee_cur is
a pointer to this
work area and 101 Rajesh 5000 10
data and our 102 Suresh 4500 20
plsql program can
refer to this rows
one by one using
103 Nilesh 4000 10
Data
this cursor
104 Jitesh 3500 20 (Active
Set)
Cursors – Types
Implict Cursor : Managed by Oracle itself
internally
Explicit Cursor: It is entirely managed by user
in a pl-sql program and to refer to any multi
row query in PL SQL program it is used.
Cursors – Types
Implict Cursor : This cursor is managed by Oracle
itself. Whenever we fire any SQL query from SQL
prompt and it returns multiple rows ,oracle internally
uses Implicit cursor.
We do not have any access to open , close and
manipulate this cursor, its oracle internal process.
Only thing we can do with Implict cursor is we can
see the number of rows affected by last operation.
The name of this cursor is fixed it is SQL
Cursors – Types
Explicit Cursor : This cursor is the only way
user can use a query in PL-SQL program that
returns multiple rows.
Any name can be given to Cursor in the
program.
Working with this cursor is a four step
process.
Cursors – Types
If you are sure that any Select query can return
more then one row then do not directly write that in
Begin block , use Explicit cursor instead.
Four steps to work with Explicit cursor.
Declare cursor
Open cursor
Fetch rows
Close cursor
Explicit Cursor
Step1 : Declare a cursor
In this step we declare a cursor in the declare
section with the query that can return more
then one row.
Syntax :
Cursor <Cursor_Name> IS
<Query> ;
Explicit Cursor
Example:
Cursor c_emp IS
Select * from emp where sal > 4500;
This query can return more then one rows as
more then one person can have salary greater
then 4500
Explicit Cursor
Step2 : Open a cursor
In this step we open a cursor in the Begin Section of
the program
It is at this point the query inside the cursor get fired
and data is returned in the work area and cursor
point to the start of that work area.
Syntax :
Open <Cursor_Name> ;
Explicit Cursor
Example:
Open c_emp;
Explicit Cursor
Step3 : Fetch the rows
This step actually moves cursor from one row to another
It is called in a loop that many times as many rows we have.
You cannot move backward.
To move backward you have to close and open the cursor again.
After every fetch cursor moves to new row so after every fetch we
should take that data in variables and use them to display or manipulate
in a program.
Syntax
Loop
Fetch <Cursor_Name> INTO <Variables> ;
Exit when <Condition>
End Loop;
Explicit Cursor
Example:
Loop
Fetch c_emp INTO v_empno, v_ename, v_sal ;
Exit When c_emp%NotFound;
End Loop;
Actually as many columns are there in Select Query
in the declaration of cursor that many variables are
required here after INTO to take the values.
%NOTFOUND is a attribute discussed later.
Explicit Cursor
Example:
Loop
Fetch c_emp INTO v_emp ;
Exit When c_emp%NotFound;
End Loop;
We can also use a Record type or %RowType variable here. Here
v_emp is:
v_emp Emp%Rowtype;
%NOTFOUND is a attribute discussed later.
Explicit Cursor
Step4 : Close a cursor
In this step we Close a cursor in the Begin Section
of the program
It is at this point the work area no longer contains
data and cursor now points to nothing.
Syntax :
Close <Cursor_Name> ;
Explicit Cursor
Example:
Close c_emp;
Cursors - Summary
In case of Implicit cursor the open , close fetch
everything is managed by oracle.
This is the way any query in oracle works , even
when we give query from SQL prompt then also we
do not write a cursor but implicit cursor is managed
by Oracle for that.
Explict cursor however we have to take care of all
the steps.
Cursors attributes in short…
%ISOPEN is used if we want to check whether a
particular cursor is successfully opened or not.
%Found returns true or false based on Fetch is
successful or not.
%NotFound does the same work but returns reverse
results.
%Rowcount returns how many rows are fetched at
any point.
Cursors Attributes
Oracle provides four attributes to help working with Cursor
Attribute Explanation
%ISOPEN - Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND - Returns INVALID_CURSOR if cursor is declared, but not open; or if
cursor has been closed.
- Returns NULL if cursor is open, but fetch has not been executed.
- Returns TRUE if a successful fetch has been executed.
- Returns FALSE if no row was returned.
%NOTFOUND - Returns INVALID_CURSOR if cursor is declared, but not open; or if
cursor has been closed.
- Return NULL if cursor is open, but fetch has not been executed.
- Returns FALSE if a successful fetch has been executed.
- Returns TRUE if no row was returned.
%ROWCOUNT - Returns INVALID_CURSOR if cursor is declared, but not open; or if
cursor has been closed.
- Returns the number of rows fetched.
Cursors attributes
To use this attributes we simply have to say:
Cursor_Name%Attribute ;
Example:
c_emp%Found;
c_emp%NotFound;
c_emp%RowCount;
c_emp%IsOpen
Cursors attributes
In case of Implicit cursor we say:
SQL%Attribute ;
Example:
SQL%Found;
SQL%NotFound;
SQL%RowCount;
SQL%IsOpen
Example
Example 23 Sample23.SQL
Example 24 Sample24.SQL
Example 25 Sample25.SQL
Example 26 Sample26.SQL
