Transaction: us a logical unit of work that is composed of one or more DML or
DDL statements. A transaction must be either saved or rolled back.
COMMIT: when you manipulate the data all the DML operation affected in the
temporary area and database remains unaffected from all these changes. If you
want to store these changes to the database, you need to COMMIT the
transaction.
ROLLBACK: means undoing any changes that have occurred in the current
transaction. Oracle undoes some or all changes made by your session to
the database in the current transaction.
SAVEPOINT: is used to mark a specific point in current transaction. SAVEPOINT
gives a name to and mark a point in the processing of current transaction. It
allows you to ROLLBACK TO that point.
CURSOR
CURSOR provides you a way to select
multiple records from the database and process each record individually inside
PL/SQL program. The set of rows returned by multi-query is called result set of
active set. There are two types of cursors supported by PL/SQL.
1. Implicit
Cursor 2. Explicit Cursor
|
Implicit Cursor
|
Explicit Cursor
|
|
These cursors are maintained internally by PL/SQL.
These are opened and closed automatically when the query is executed.
|
These cursors are defined with a name and are
opened and closed explicitly in a PL/SQL program.
|
|
The cursor attributes are prefixed with SQL. (e.g.
SQL%FOUND). Because SQL is default name of implicit cursor.
|
The cursor attributes are prefixed with the cursor
name. (e.g. cur%found) where cur is the name of explicit cursor.
|
|
The cursor attribute %ISOPEN is always false.
Because cursor gets closed automatically after the statement is over.
|
The %ISOPEN attribute holds the value (TRUE/FALSE)
of the status of the cursor.
|
|
Only one row can be processed using the SELECT INTO
statement.
|
Any number of rows can be processed.
|
STEPS REQUIRED FOR EXPLICIT CURSOR:
1. Declare
the cursor: in this process, select statement
associated with cursor is defined. Syntax:
Cursor is select
statement;
2. Open the
cursor: in this process when cursor is opened the
select statement associated with cursor is executed. Syntax:
Open
[(parameter_list)];
3. Fetch rows
from the cursor: in this step, one row is retrieved
from the active set and stores it in variables for further processing. Syntax:
Fetch
into ;
4. Close the cursor: in this step the
cursor is closed. The close statement disables the cursor. You can reopen it.
Syntax: close
;
CURSOR
ATTRIBUTES: cursor has four attributes:
1.
%NOTFOUND Attribute: rows are fetched from the cursor’s active set one at a time. If the
last fetch returned a row %NOTFOUND evaluates to FALSE. If the last fetch
failed to return a row then %NOTFOUND evaluates to TRUE.
2.
%FOUND Attribute: is logical opposite of %NOTFOUND. %FOUND evaluates to TRUE if the
last fetch returned a row or FALSE if no row was returned.
3.
%ROWCOUNT Attribute: when cursor is opened, %rowcount is zero. When the records are
fetched from active set it is incremented by one each time. It returned how
many records are fetched from the active set.
4.
%ISOPEN Attribute: evaluates to TRUE if cursor is open, otherwise it evaluates to
FALE.
Write the
PL/SQL script to display the ename, job, sal and deptno from the emp table.
DECLARE
CURSOR CUR_EMP
IS SELECT ENAME, JOB, SAL, DEPTNO FROM EMP;
RS
CUR_EMP%ROWTYPE; --cursor-based
record
BEGIN
OPEN CUR_EMP;
LOOP
FETCH CUR_EMP
INTO RS;
EXIT WHEN
CUR_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RS.ENAME
|| ‘ ‘ || RS.SAL || ‘ ‘ RS.JOB || ‘ ‘|| RS.DEPTNO);
END LOOP;
CLOSE CUR_EMP;
END;
Write a PL/SQL script to increase the
salary as per following criteria:
SALARY AMT INCREMENTED BY
<1200 span="" style="mso-tab-count: 5;"> 8%
<2500 span="" style="mso-tab-count: 5;"> 12%
<4500 span="" style="mso-tab-count: 5;"> 15%
OTHERWISE 20%
DECLARE
CURSOR CUR_EMP
IS SELECT EMPNO, SAL FROM EMP;
RS
CUR_EMP%ROWTYPE;
BEGIN
OPEN CUR_EMP;
LOOP
FETCH CUR_EMP
INTO RS;
IF RS.SAL
<1200 span="" then="">
UPDATE EMP SET
SAL = SAL *1.08 WHERE EMPNO=RS.EMPNO;
ELSIF RS.SAL
<2500 span="" then="">
UPDATE EMP SET
SAL = SAL *1.12 WHERE EMPNO=RS.EMPNO;
ELSIF RS.SAL
<4500 span="" then="">
UPDATE EMP SET
SAL = SAL *1.15 WHERE EMPNO=RS.EMPNO;
ELSE
UPDATE EMP SET
SAL = SAL *1.2 WHERE EMPNO=RS.EMPNO;
END IF;
CLOSE CUR_EMP;
END;
Parameterized
Cursors: a cursor can receive values provided by
you, such cursors are known as Parameterized cursors.
Cursor cur_name (parameter datatype,…..)
is select statement;
We can provide value for these parameters
at the time of opening cursor as per following syntax:
Open
cur_name(value, …);
Write the
PL/SQL script to display the ename, job, sal of particular dept that is input
by user using parameter.
DECLARE
CURSOR
CUR_EMP(DNO NUMBER) IS SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = DNO;
RS
CUR_EMP%ROWTYPE; --cursor based
record
DEPT_NO NUMBER;
BEGIN
DEPT_NO :=
&DEPTNO;
OPEN
CUR_EMP(DEPT_NO);
LOOP
FETCH CUR_EMP
INTO RS;
EXIT WHEN
CUR_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RS.ENAME
|| ‘ ‘ || RS.SAL || ‘ ‘|| RS.JOB);
END LOOP;
CLOSE CUR_EMP;
END;
CURSOR FOR LOOP:
PL/SQL provides
a special kind of FOR loop to process the rows returned in an explicit cursor.
In a Cursor FOR Loop, a declared cursor is Opened, Fetched and Closed
automatically when all of the rows have been processed. Syntax:
FOR IN
LOOP
--STATEMENTS
TO BE EXECUTED
END LOOP;
Write a
PL/SQL script to display the name, salary and bonus (salary * .12) for each emp
using cursor for loop.
DECLARE
CURSOR CUR_EMP
IS SELECT ENAME, SAL, SAL *1.2 ‘BONUS’ FROM EMP;
BEGIN
FOR RS IN
CUR_EMP LOOP
DBMS_OUTPUT.PUT_LINE(RS.ENAME
|| ‘ ‘ || RS.SAL ||’ ‘ ||RS.BONUS) ;
END LOOP;
END;
CURSOR FOR LOOP
USING USB-QUERY:
Writing a
sub-query in place of cursor name in the cursor For Loop can do this. Syntax:
FOR IN LOOP
--STATEMENTS
TO BE EXECUTED
END LOOP;
Write a PL/SQL script to display the
name, salary for each emp using cursor for loop.
BEGIN
FOR RS IN
(SELECT ENAME, SAL FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(RS.ENAME
|| ‘ ‘ || RS.SAL) ;
END LOOP;
END;
