What is a Cursor

A cursor is a temporary  context  area created in the system  when we executes  SQL

statements.

A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to hold  the data retrieved from the database, and

manipulate this data. A cursor can hold more than one row, but can process only one row

at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

There are two types of cursors - implicit and explicit cursor.

Implicit cursor: Oracle automatically  processes the information of SQL statement

executed. In this process, the user is not aware about  implicit cursor. These actions Oracle

automatically performs the OPEN, FETCH, and CLOSE operations.

Explicit cursor: Explicit  cursors are explicitly declared in the DECLARE section of the

PL/SQL block. This declaration allows to process each row of data as the cursor returns it.

In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the

programmer.

The process of working with an explicit cursor:

Declare: The cursor is initialised into temporary memory area.
Open: The cursor is opened which is declared, and the temporary memory area is allotted.
Fetch: Cursor which is declared and opened can now retrieve rows from data.
Close: The CLOSE statement disables the cursor, and releases the temporary memory area.





The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Explicite  cursor involves four steps:

1.Declaring the cursor for initializing in the memory
2. Opening the cursor for allocating memory
3.  Fetching the cursor for retrieving data
4.Closing the cursor to release allocated memory

--When the above code is executed at SQL prompt, it produces the following result:
declare
dno number;
eno employees.employee_id%Type;
fname employees.first_name%Type;
Jobid  employees.job_id%Type;
cursor emprec is select employee_id,first_name,job_id from employees
where department_id=&dno;
Begin
open emprec;
loop
fetch  emprec into  eno,fname,jobid ;
dbms_output.put_line('Empno '||eno||'  '||fname||'  '||' '||jobid);
exit when emprec%Notfound;
end loop;
close emprec;
End;
/


Enter value for dno: 30
old   7: where department_id=&dno;
new   7: where department_id=30;

Empno 114  Den   PU_MAN
Empno 115  Alexander   PU_CLERK
Empno 116  Shelli   PU_CLERK
Empno 117  Sigal   PU_CLERK
Empno 118  Guy   PU_CLERK
Empno 119  Karen   PU_CLERK
Empno 119  Karen   PU_CLERK

Read More

set serveroutput on

When we execute PL/SQL program  into Oracle engine so we always required to get output result and display
into the screen otherwise result can't be display.

Whenever we connect Oracle SQL (PL/SQL) at that time you must have to write the "set serveroutput on" command.


SQL> set serveroutput on

PL/SQL Comment you can write single line or either multiple line,

1.Multi-line comments and
2.Single line comments
SQL>-- Single Line Comment

SQL>/*  Multiline Comment line 1
Multiline Comment line2    */1

Read More

Differences Between Anonymous Blocks and Subprograms

1. Anonymous is unnamed plsql block

A stored procedure or a named block is a pl/sql block.

2. Anonymous cannot save in database,

oracle stores pl/sql block in the database.

3. We cannot call Anonymous

blocks. We can recall named block

Whenever program require it.

4 Anonymous Block cannot allow any mode of parameter.

Named block accepts the mode of

Parameter like in,inout,out. 

5 Anonymous blocks are to be compiled each time when we

Require it.

But once we compile a named pl/sql block it is

Permanently stored as p-code after compilation in the

Shared pool of the system global area.

6. Anonymous block: Starts with

                DECLARE or BEGIN.

                Named block: Starts with Header Block.

Like name of block, type of block, parameter.

 Named Block has NAME: like PROCEDURE, FUNCTION, and PACKAGES

7.The anonymous block statement is an executable statement

That can contain PL/SQL control statements and SQL

Statements. It can be used to implement procedural logic in

a scripting language.

named block or stored procedure is a pl/sql block that

oracle stores in the database and can be called by name

From any application examples are function, procedure and packages,etc..

8.Anonymous block are created on client and subprograms are stored on server. It is possible to call subprograms from anonymous block.

9.Anonymous blocks do not return values.

Subprograms called functions must return values.

10. Anonymous blocks cannot take parameters.

Subprograms can take parameters..

Syntax:-


Whenever you will execute the procedure nth time would appear the following message
and not showing any output anonymous block completed.

Anonymous Blocks
DECLARE(Optional)
Variables,Cursors,etc.
BEGIN(Mandatory)
SQL and PL/SQL cOMMANDS
EXCEPTION(Optional)
Exceptional_Handling actions;
END;(Mandatory)

11. example  Anonymous blocks

DECLARE

l_message

VARCHAR2 (100) := 'Hello World!';

BEGIN

DBMS_OUTPUT.put_line (l_message);

END;

 SUBPROGRAMS PROCEDURE

Create [Or Replace ] procedure name [parameters] is|as
(Mandatory)
Variables,Cursors,etc(Optional)
BEGIN   (Mandatory)
SQL & plsql STATEMENTS;
EXCEPTION(Optional)
When Exception_handling actions;
END[NAME] ; (Mandatory)

Let us take one example of Named Block given below.

CREATE OR REPLACE FUNCTION FINDAREA(LEN IN NUMBER, WID IN NUMBER)

RETURN NUMBER

AS VAREA NUMBER;

BEGIN

VAREA := LEN * WID;

RETURN VAREA;

END;

SQL>  SELECT FIND_AREA(25,15) AREA FROM DUAL;

AREA

375

               

Read More

Bind Variables

Suppose that you want to be to display the variables you  use in your PL/SQL subprograms in SQL*Plus or you can use the same variables in multiple subprograms.

If you declare a variable in a PL/SQL , We  cannot display that variable in SQL*Plus.Use a bind variable in PL/SQL to access the variable from SQL*Plus.

Bind variables are variables you generate  in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use this variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus.

Example 1:

VARIABLE G_NAME VARCHAR2(20)
VARIABLE G_SAL NUMBER

BEGIN
  SELECT LAST_NAME,SALARY
  INTO   :G_NAME,:G_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
END;
/
PRINT G_NAME
PRINT G_SAL


Example 2:

VARIABLE G_NAME VARCHAR2(20)
VARIABLE G_SAL NUMBER

BEGIN
  SELECT LAST_NAME,SALARY
  INTO   :G_NAME,:G_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
  DBMS_OUTPUT.PUT_LINE(:G_NAME||' GETS '||:G_SAL);
END;
/
PRINT G_NAME
PRINT G_SAL


Example 3:

DECLARE
  V_SAL NUMBER;
  V_FLAG BOOLEAN;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  IF V_SAL >= 10000 THEN
     V_FLAG := TRUE;
  ELSE
     V_FLAG := FALSE;
  END IF;

  IF V_FLAG=TRUE THEN
     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
  ELSE
     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
  END IF;
END;


Example 4:-


DECLARE
  V_SAL NUMBER;
  V_FLAG BOOLEAN;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  V_FLAG := (V_SAL>=10000);

  IF V_FLAG=TRUE THEN
     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
  ELSE
     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
  END IF;
END;

Read More