PLSQL Reference for DBA

PL/SQL stands for Procedural Language/SQL.PL/SQL extends SQL by adding control Structures found in other

procedural language.PL/SQL combines the flexibility of SQL with Powerful feature of 3rd generation Language.

The procedural construct and database access Are present in PL/SQL.PL/SQL can be used in both in database

in Oracle Server and in Client side application development tools.

  1. Advantages of PL/SQL

Support for SQL, support for object-oriented programming,, better performance, portability, higher

productivity, Integration with Oracle

a] Supports the declaration and manipulation of object types and collections.

b] Allows the calling of external functions and procedures.

c] Contains new libraries of built in packages.

d] with PL/SQL , an multiple sql statements can be processed in a single command line statement.

 

 

  1. Declaring PL/SQL Variables

Example of variable declarations:

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) :=  ‘Atlanta’;

c_comm CONSTANT NUMBER := 1400;

v_job VARCHAR2(9);

v_count BINARY_INTEGER := 0;

v_total_sal NUMBER(9,2) := 0;

v_orderdate DATE := SYSDATE + 7;

c_tax_rate CONSTANT NUMBER(3,2) := 8.25;

v_valid BOOLEAN NOT NULL := TRUE;

 

Assigning value to the variable:

v_hiredate := ’01-JAN-2001’;

v_ename := ’Maduro’;

v_city  VARCHAR2(30) NOT NULL := ’Oxford’

v_mgr NUMBER(6) DEFAULT 100;

 

Working with Boolean:

declare
v_flag1 boolean:=FALSE;
v_flag2 boolean:=TRUE;
begin
if(v_flag1) then
dbms_output.put_line(‘FALSE’);
end if;
if(v_flag2)
then
dbms_output.put_line(‘TRUE’);
end if;
if(v_flag1=FALSE) then
dbms_output.put_line(‘explicit FALSE’);
end if;
end;
/

 

Declaring Variables  with the %TYPE Attribute:

v_name           employees.last_name%TYPE;

v_balance        NUMBER(7,2);

v_min_balance    v_balance%TYPE := 10;

 

Using %ROWTYPE :

DECLARE
dept_rec dept%ROWTYPE;

BEGIN
SELECT * INTO dept_rec FROM dept WHERE deptno = 30;

END;

DECLARE
dept_rec1 dept%ROWTYPE;
dept_rec2 dept%ROWTYPE;
CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
dept_rec3 c1%ROWTYPE;
BEGIN

dept_rec1 := dept_rec2;

However, because dept_rec2 is based on a table and dept_rec3 is based on a
cursor, the following assignment is not allowed:

dept_rec2 := dept_rec3; — not allowed

 

Using Bind Variables:

Syntax:

plus > VARIABLE g_bar VARCHAR2(30)

plus > ACCEPT p_foo PROMPT ‘enter the value required’

You can reference host variables in PL/SQL statements *unless* the statement is in a procedure, function or package.
This is done by prefixing with & (to read the variable) or prefix with: (writing to the variable)

Examples:
DECLARE
— Declare individual variables:
v_ename emp.ename%TYPE;
v_balance NUMBER(7,2);
v_min_bal v_balance%TYPE := 10;
— Declare RECORD TYPE variable:
TYPE job_type IS RECORD
(jobid    NUMBER(7,2),
jobname  t_jobs.jb_name%Type);
job_record job_type;
— Declare a variable based on SQL*Plus Bind variable
v_amount NUMBER(6,2) := &p_foo
— Assign value to a SQL*Plus variable from a PL/SQL variable
BEGIN
:g_bar := v_amount *12

To reference a bind variable in PL/SQL, you must
prefix its name with a colon (:).

VARIABLE g_salary NUMBER
BEGIN
SELECT salary
INTO: g_salary
FROM employees
WHERE employee_id = 178;
END;
/

PRINT g_salary

 

Using Aliases:

 

Select-list items fetched from a cursor associated with %ROWTYPE must have simple

names or, if they are expressions, must have aliases. In the following example, you

use an alias called wages:

 

DECLARE
CURSOR my_cursor IS
SELECT sal + NVL(comm, 0) wages, ename FROM emp;
my_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec;
EXIT WHEN my_cursor%NOTFOUND;
IF my_rec.wages > 2000 THEN
INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename);
END IF;
END LOOP;
CLOSE my_cursor;
END;

  1. PLSQL DATA TYPES

 

VARCHAR2:
Storing character data as Varchar2 will save space:

Store ‘SMITH’ not ‘SMITH     ‘

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows – because CHAR columns are fixed width they are not affected by this – so less DBA effort is required to maintain performance.

PLS_INTEGER
When retrieving data for a NUMBER column, consider (if you can) using the PL/SQL datatype: PLS_INTEGER for better performance.

PLS_INTEGER values require less storage than NUMBER values

Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic.

Although PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable

LONG
You should start using BLOB instead of LONG

The LONG and LONG RAW datatypes are supported only for backward compatibility with existing applications. For new applications, use CLOB or NCLOB in place of LONG, and BLOB or BFILE in place of LONG RAW

INTEGER
This ANSI datatype will be accepted by Oracle – it is actually a synonym for NUMBER(38)

The FLOAT datatype
This ANSI datatype will be accepted by Oracle – Very similar to NUMBER it stores zero, positive, and negative floating-point numbers

The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38

Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)

{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}

Example

The value 7,456,123.89 will display as follows

NUMBER(9)     7456124

NUMBER(9,1)   7456123.9

NUMBER(*,1)   7456123.9

NUMBER(9,2)   7456123.89

NUMBER(6)    [not accepted exceeds precision]

NUMBER(7,-2)  7456100

NUMBER        7456123.89

FLOAT         7456123.89

FLOAT(12)     7456000.0

Oracle stores all numeric data in variable length format.

Storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.

It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the logic and maths still make sense. (This is unlikely to be the case)

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
FROM cols WHERE table_name = ‘Your_Table’;

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

CREATE TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

— Standard logical values: 1=True and 0=False
insert into my_demo values(525, ‘1’);
insert into my_demo values(526, ‘0’);

SELECT accountcode, decode(postableYN,1,’True’,0,’False’) FROM my_demo;
or
SELECT accountcode, decode(postableYN,1,’Oui’,0,’Non’) FROM my_demo;

Comparison with other RDBMS’s

int10 int6 int1 char(n) blob XML
Oracle 9 NUMBER(10) NUMBER(6) NUMBER(1) VARCHAR2(n) BLOB XMLType
MS SQL Server 2005 NUMERIC(10) NUMERIC(6) TINYINT VARCHAR(n) IMAGE XML
Sybase system 10 NUMERIC(10) NUMERIC(6) NUMERIC(1) VARCHAR(n) IMAGE
MS Access (Jet) Long Int or Double Single Byte TEXT(n) LONGBINARY
TERADATA INTEGER DECIMAL(6) DECIMAL(1) VARCHAR(n) VARBYTE(20480)
DB2 INTEGER DECIMAL(6) DECIMAL(1) VARCHAR(n) VARCHAR(255)
RDB INTEGER DECIMAL(6) DECIMAL(1) VARCHAR(n) LONG VARCHAR
INFORMIX INTEGER DECIMAL(6) DECIMAL(1) VARCHAR(n) BYTE
RedBrick integer int int char(n) char(1024)
INGRES INTEGER INTEGER INTEGER VARCHAR(n) VARCHAR(1500)

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement – these limits vary considerably between products and versions.

 

  1. Printing in PL SQL

DBMS_OUTPUT.PUT_LINE:

  • An Oracle-supplied packaged procedure
  • An alternative for displaying data from a PL/SQL

block

  • Must be enabled in iSQL*Plus with

SET SERVEROUTPUT ON

DEFINE p_annual_sal = 60000 — do not work in PL/SQL developer
DECLARE
dbms_output.enable(100000); — do not work in PL/SQL developer, option is provided to explicitly
alternate the buffer size (in output tab)
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE (’The monthly salary is ’ || TO_CHAR(v_sal));
END;
/

  1. PL/SQL Block Syntax and Guidelines

Literals

– Character and date literals must be enclosed in

single quotation marks.

v_name := ‘Henderson’;

– Numbers can be simple values or scientific

notation.

  • A slash ( / ) runs the PL/SQL block in a script file

or in some tools such as iSQL*PLUS.

 

Using Literals

declare
begin
dbms_output.put_line (‘debasis’s shirt is good’);
end;
/

declare
begin
dbms_output.put_line (q'[debasis’s shirt is good]’);
end;
/

 

  1. Commenting Code

 

  • Prefix single-line comments with two dashes (–).
  • Place multiple-line comments between the symbols

/* and */.
Example:
DECLARE

v_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_sal := :g_monthly_sal * 12;
END;  — This is the end of the block

 

  1. SQL Functions in PL/SQL:

 Examples

  • Build the mailing list for a company.

v_mailing_address := v_name||CHR(10)||

v_address||CHR(10)||v_state||

CHR(10)||v_zip;

  • Convert the employee name to lowercase.

v_ename := LOWER(v_ename);

 

  1. Data type Conversion

 

  • Convert data to comparable data types.
  • Mixed data types can result in an error and affect

performance.

  • Conversion functions:

– TO_CHAR

– TO_DATE

– TO_NUMBER

DECLARE

v_date DATE := TO_DATE(’12-JAN-2001’, ’DD-MON-YYYY’);

  1. This statement produces a compilation error if the

variable v_date is declared as a DATE data type.

v_date := ’January 13, 2001’;

  1. To correct the error, use the TO_DATE conversion

function.

v_date := TO_DATE (’January 13, 2001’,’ Month DD, YYYY’);

 

  1. Variable Visibility

Class Exercise

<<outer>>

DECLARE
V_SAL NUMBER(7,2) := 60000;
V_COMM NUMBER(7,2) := V_SAL * .20;
V_MESSAGE VARCHAR2(255) := ’ eligible for commission’;
BEGIN
DECLARE
V_SAL NUMBER(7,2) := 50000;
V_COMM   NUMBER(7,2) := 0;
V_TOTAL_COMP NUMBER(7,2) := V_SAL + V_COMM;
BEGIN
V_MESSAGE := ’CLERK not’||V_MESSAGE;
outer.V_COMM := V_SAL *.30
END;
V_MESSAGE := ’SALESMAN’||V_MESSAGE;
END;

A better example

SET SERVEROUTPUT ON

DECLARE
v_visible VARCHAR2(30);
v_hidden VARCHAR2(30);
BEGIN
v_visible := ‘v_visible in the outer block’;
v_hidden := ‘v_hidden in the outer block’;

DBMS_OUTPUT.PUT_LINE(‘*** OUTER BLOCK ***’);
DBMS_OUTPUT.PUT_LINE(v_visible);
DBMS_OUTPUT.PUT_LINE(v_hidden);
DBMS_OUTPUT.PUT_LINE(‘     ‘);

DECLARE
v_hidden NUMBER(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘*** INNER BLOCK ***’);
v_hidden := ‘v_hidden in the inner block’;
DBMS_OUTPUT.PUT_LINE(v_hidden);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(‘v_hidden of type VARCHAR2 was…hidden’);
END;
END;
Output:-

*** OUTER BLOCK ***
v_visible in the outer block
v_hidden in the outer block
*** INNER BLOCK ***
v_hidden of type VARCHAR2 was…hidden

PL/SQL procedure successfully completed.

  1.  PLSQL Variable Scope

declare
— begin first block
l_text varchar2(20);
begin
l_text := ‘First Block’;
dbms_output.put_line(l_text);
declare
— begin second block
l_more_text varchar2(20);
begin
l_more_text := ‘Second Block’;
dbms_output.put_line(l_more_text);
end; — end second block
end; — end first block
/
First Block
Second Block

PL/SQL procedure successfully completed.

Identifiers defined in the declaration of the child block are only in scope and visible within the child block itself.

declare
l_parent_number number;
begin
— l_parent_number is visible and in scope
l_parent_number := 1;

declare
l_child_number number := 2;
begin
— l_child_number is visible and in scope
dbms_output.put_line(‘parent + child = ‘ ||
to_char(l_parent_number + l_child_number));
end;

— l_child_number is now not visible nor in scope:
l_child_number := 2;
end;
/
l_child_number := 2;
*
ERROR at line 16:
ORA-06550: line 16, column 3:
PLS-00201: identifier ‘L_CHILD_NUMBER’ must be declared
ORA-06550: line 16, column 3:
PL/SQL: Statement ignored

 

  1. Operators in PL/SQL

 Examples:

  • Increment the counter for a loop.

v_count := v_count + 1;

  • Set the value of a Boolean flag.

v_equal := (v_n1 = v_n2);

  • Validate if an employee number contains a value.

v_valid := (v_empno IS NOT NULL);

  1. CONTROL STRUCTURE

 

IF-THEN Statement

IF sales > quota THEN
Compute_ bonus (empid);
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
You might want to place brief IF statements on a single line, as in
IF x > y THEN high: = x; END IF;

NULL as a statement:

IF report.selection = ‘DETAIL’
THEN NULL; — Do nothing
ELSE Exec_detail_report;
END IF;

IF-THEN-ELSE Statement

Example:

IF trans_type = ’CR’ THEN
UPDATE accounts SET balance = balance + credit WHERE …
ELSE
UPDATE accounts SET balance = balance – debit WHERE …
END IF;

 

Nested if else:

IF trans_type = ’CR’ THEN
UPDATE accounts SET balance = balance + credit WHERE …
ELSE
IF new_balance >= minimum_balance THEN
UPDATE accounts SET balance = balance – debit WHERE …
ELSE
RAISE insufficient_funds;
END IF;
END IF;

 

IF-THEN-ELSIF Statement

Example:

BEGIN

IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
INSERT INTO payroll VALUES (emp_id, bonus, …);
END;

 

CASE Statement

 

By if-else statement:

 

IF grade = ’A’ THEN
dbms_output.put_line(’Excellent’);
ELSIF grade = ’B’ THEN
dbms_output.put_line(’Very Good’);
ELSIF grade = ’C’ THEN
dbms_output.put_line(’Good’);
ELSIF grade = ’D’ THEN
dbms_output. put_line(’Fair’);
ELSIF grade = ’F’ THEN
dbms_output.put_line(’Poor’);
ELSE
dbms_output.put_line(’No such grade’);
END IF;

 

By case:

declare
grade char(1):=’A’;
begin
CASE grade
WHEN ‘A’ THEN dbms_output.put_line(‘Excellent’);
WHEN ‘B’ THEN dbms_output.put_line(‘Very Good’);
WHEN ‘C’ THEN dbms_output.put_line(‘Good’);
WHEN ‘D’ THEN dbms_output.put_line(‘Fair’);
WHEN ‘F’ THEN dbms_output.put_line(‘Poor’);
ELSE dbms_output.put_line(‘No such grade’);
END CASE;
end;
/

Search case:

declare
grade char(1):=’A’;
begin
CASE
WHEN grade=’A’ THEN dbms_output.put_line(‘Excellent’);
WHEN grade=’B’ THEN dbms_output.put_line(‘Very Good’);
WHEN grade=’C’ THEN dbms_output.put_line(‘Good’);
WHEN grade=’D’ THEN dbms_output.put_line(‘Fair’);
WHEN grade=’F’ THEN dbms_output.put_line(‘Poor’);
ELSE dbms_output.put_line(‘No such grade’);
END CASE;
end;
/

  1. General loop control

 

GENERAL LOOP
LOOP

IF credit_rating < 3 THEN

EXIT; — exit loop immediately
END IF;
END LOOP;
— control resumes here

 

LOOP WITH EXIT-WHEN

LOOP
FETCH c1 INTO …
EXIT WHEN c1%NOTFOUND; — exit loop if condition is true

END LOOP;
CLOSE c1;

 WHILE LOOP

Example1:

WHILE total <= 25000 LOOP

SELECT sal INTO salary FROM emp WHERE …
total := total + salary;
END LOOP;

Example2:

i := 1;
sum := 0;
while ( i < 1000) loop
sum := sum + i;
i := 2*i;
end loop;

To ensure that a WHILE loop executes at least once, use an initialized Boolean
variable in the condition, as follows:

done := FALSE;
WHILE NOT done LOOP
sequence_of_statements
done := boolean_expression;
END LOOP;

 

FOR LOOP

 

for i in 1..10 loop   –increment i
dbms_output.putline (‘i= ‘|| i);
sum := sum + i;
end loop;

 

for i in reverse 1..10 loop   –decrement i
dbms_output.putline(‘i= ‘|| i);
sum := sum + i;
end loop;

 

  1. Interaction Between PL/SQL and Oracle

 

CURSORS

Oracle allocates an area of memory known as context area for the processing of SQL statements. The

pointer that points to the context area is a cursor.

TYPES

1 ] Static Cursor

  • EXPLICIT
  • IMPLICIT

2] Dynamic Cursors

3] REF Cursors.

 

Merits

1] Allowing to position at specific rows of the result set.

2] Returning one row or block of rows from the current position in the result set.

3] Supporting data modification to the rows at the current position in the result set.

 

TYPES

1] STATIC CURSOR

SQL statement is determined at design time.

A] EXPLICIT

EXPLICIT CURSOR — Multiple row SELECT statement is called as an explicit cursor.

Usage – If the SELECT statement returns more that one row then explicit cursor should be used.

Steps

  • Declare a cursor
  • Open a cursor
  • Fetch data from the cursor
  • Close the cursor

Syntax

CURSOR <CURSOR NAME> IS <SELECT STATEMENT>

OPEN <CURSOR NAME>

FETCH <CURSOR NAME> INTO <COLUMN NAMES>

CLOSE <CURSOR NAME>

EXPLICIT CURSOR ATTRIBUTES

  • %FOUND (Returns true if the cursor has a value)
  • %NOTFOUND (Returns true if the cursor does not contain any value)
  • %ROWCOUNT (Returns the number of rows selected by the cursor)
  • %ISOPEN (Returns the cursor is opened or not)

CURSOR FOR LOOPS

A cursor FOR loop implicitly opens a cursor, fetches the rows and closes the cursor automatically.

SYNTAX

FOR <RECORD NAME> IN <CURSOR NAME> LOOP

STATEMENTS

END LOOP;

To refer an element of the record use <record name.column name>

 

Opening a Cursor

DECLARE
CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000;

BEGIN
OPEN c1;

END;

 

Passing Cursor Parameters

DECLARE
emp_name emp.ename%TYPE;
salary emp.sal%TYPE;
CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT …
any of the following statements opens the cursor:
OPEN c1(emp_name, 3000);
OPEN c1(’ATTLEY’, 1500);
OPEN c1(emp_name, salary);

 

Fetching with a Cursor

FETCH c1 INTO my_empno, my_ename, my_deptno;

 

The query can reference PL/SQL variables within its scope. However, any variables

in the query are evaluated only when the cursor is opened. In the following

example, each retrieved salary is multiplied by 2, even though factor is

incremented after every fetch:

 

DECLARE
my_sal emp.sal%TYPE;
my_job emp.job%TYPE;
factor INTEGER := 2;
CURSOR c1 IS SELECT factor*sal FROM emp WHERE job = my_job;
BEGIN

OPEN c1; — here factor equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; — does not affect FETCH
END LOOP;
END;

 

Fetching Bulk Data with a Cursor

 

The BULK COLLECT clause lets you bulk-bind entire columns of Oracle data (see

“Retrieving Query Results into Collections with the BULK COLLECT Clause” on

page 5-46). That way, you can fetch all rows from the result set at once. In the

following example, you bulk-fetch from a cursor into two collections:

 

DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
nums NumTab;
names NameTab;
CURSOR c1 IS SELECT empno, ename FROM emp WHERE job = ’CLERK’;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO nums, names;

CLOSE c1;

END;

EXPLICIT CURSOR WITH FOR LOOP

 

DECLARE
CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP;
BEGIN
FOR ROW1 IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(ROW1.EMPNO);
DBMS_OUTPUT.PUT_LINE(ROW1.ENAME);
END LOOP;
END;

EXPLICIT CURSOR WITH FOR LOOP USING SUBQUERIES

BEGIN
FOR ROW1 IN (SELECT EMPNO,ENAME,SAL FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(ROW1.EMPNO);
DBMS_OUTPUT.PUT_LINE(ROW1.ENAME);
END LOOP;
END;

EXPLICIT CURSOR WITH FOR LOOP USING PARAMETER

 

PL/SQL also allows you to pass parameters into cursors. The same rationale for using parameters in modules applies to parameters for cursors:

  • A parameter makes the cursor more reusable.
  • A parameter avoids scoping problems.

Let’s take a look at the difference between parameterized and unparameterized cursors first:

 

— First cursor

CURSOR joke_cur IS
SELECT name, category, last_used_date FROM joke WHERE category = ‘POLITICIAN’;
— Second cursor
CURSOR joke_cur IS
SELECT name, category, last_used_date FROM joke WHERE category = ‘HUSBAND’;

 

–Parameterized Cursor — Generalizing Cursors with Parameters

DECLARE
CURSOR joke_cur(category_in VARCHAR2) IS
SELECT name, category, last_used_date FROM joke WHERE category = UPPER(category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur(‘husband’); — Or OPEN joke_cur(‘politician’);
FETCH joke_cur INTO joke_rec;

END;

DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
’Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;

 

EXPLICIT CURSOR ROW POSITIONING

 

DECLARE
CURSOR C1 IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP ORDER BY sal;
ENO EMP.EMPNO%TYPE;
EMPNAME EMP.ENAME%TYPE;
SAL1 EMP.SAL%TYPE;
DNO EMP.DEPTNO%TYPE;
BEGIN
OPEN C1;
LOOP
EXIT WHEN C1%ROWCOUNT=5 OR C1%NOTFOUND;
FETCH C1 INTO ENO,EMPNAME,SAL1,DNO;
DBMS_OUTPUT.PUT_LINE(‘EMPNO ‘ || ENO ||’ EMPNAME ‘ || EMPNAME ||’ SALARY ‘ || SAL1);
INSERT INTO EMP51 VALUES(ENO,EMPNAME,SAL1,DNO);
COMMIT;
END LOOP;
CLOSE C1;
END;

 

IMPLICIT CURSOR

 

An IMPLICIT cursor is associated with any SQL DML statement that does not have a explicit cursor

associated with it.

 

This includes :

” All INSERT statements

” All UPDATE statements

” All DELETE statements

IMPLICIT CURSOR ATTRIBUTES

” SQL%FOUND (Returns true if the DML operation is valid)

” SQL%NOTFOUND (Returns true if the DML operation is invalid)

” SQL%ROWCOUNT (Returns the no. of rows affected by the DML operation)

 

SQL%FOUND – IMPLICIT

 

BEGIN
UPDATE EMP51 SET SAL=SAL+50.50 WHERE DEPTNO=&Department_No;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid Salary Updation’ );
ELSif SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Sucessfully Updated’ );
END IF;
END;

 

SQL%ROWCOUNT – IMPLICIT

 

BEGIN
UPDATE EMP51 SET SAL=SAL+50.50 WHERE DEPTNO=&DNO;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||’ Rows Sucessfully Updated ‘ );
ELSif SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||’ Rows Sucessfully Updated ‘ );
END IF;
END;

 

EXPLCIT CURSOR – UPDATION

DECLARE
DNO EMP.DEPTNO%TYPE:=&DEPT_NO;
ENO EMP.EMPNO%TYPE;
ENAME1 EMP.ENAME%TYPE;
ESAL EMP.SAL%TYPE;
CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO=DNO;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO ENO,ENAME1,ESAL;
EXIT WHEN C1%NOTFOUND;
IF ESAL<1200 THEN
UPDATE EMP SET COMM=SAL*.6 WHERE DEPTNO=DNO AND (SAL<1200);
ELSIF ESAL>1200 AND ESAL<=1500 THEN
UPDATE EMP SET COMM=SAL*.4 WHERE DEPTNO=DNO AND (SAL>1200 AND SAL<=1500);
ELSE
UPDATE EMP SET COMM=SAL*.5 WHERE DEPTNO=DNO AND (SAL>1500);
END IF;
DBMS_OUTPUT.PUT_LINE(ENO);
DBMS_OUTPUT.PUT_LINE(ENAME1);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
CLOSE C1;
END;

 

DYNAMIC CURSOR

 

Dynamic Cursor can be used along with DBMS_SQL package .A SQL statement is dynamic, if it is constructed

at run time and then executed.

 

REF CURSOR

 

Declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where

REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype

REF CURSOR.

To execute a multi-row query, Oracle opens an unnamed work area that stores

processing information. To access the information, you can use an explicit cursor, which

names the work area. Or, you can use a cursor variable, which points to the work area.

Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various

clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work

area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle

Server can all refer to the same work area.

a] Strong Cursor – Whose return type specified.

b] Week Cursor – Whose return type not specified.

Ref Cursor

Ref are the best way of uniqely referring to object instances.It is only supported way of getting at OID

( object identifier ).It is a effecient and light means of passing object information as a information

Dref

It is the ‘deference operator.Like VALUE,it return the value of an object,Unlike value.

Dref’s input is a REF to an column in a table and you want reterive the target instead of the pointer,you

DREF.

Example of weak and strong ref cursor
With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.

The strong ref_cursor and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace package REFCURSOR_PKG as

TYPE WEAK8i_REF_CURSOR IS REF CURSOR;

TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

end REFCURSOR_PKG;

 

The pl/sql procedure that returns a ref-cursor looks like this:

 

/** until Oracle 9 */

create or replace procedure test( p_deptno IN number
, p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from   emp
where  deptno = p_deptno;
end test;

Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning

REF_CURSOR.

/** From Oracle 9 */

create or replace procedure test( p_deptno IN number
, p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from   emp
where  deptno = p_deptno;
end test;

/* Strong type */

create or replace procedure test( p_deptno IN number
, p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from   emp
where  deptno = p_deptno;
end test;

 

create or replace procedure test_proc(ref_c out sys_refcursor) is
type v_ref is REF CURSOR RETURN EMP%ROWTYPE;
c_ref v_ref;
begin
open c_ref for select * from emp;
ref_c:=c_ref;
end;

 

Selecting the ref_cursor from JDBC

To get the cursor from Java you can use the following JDBC-code:

public void method() throws SQLException{
Connection conn = getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
int deptno = 10;
Object temp;
try{
cstmt = conn.prepareCall(“begin  test(?,?); end;”);
cstmt.setInt(1, deptno);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
while (rs.next()){
for (int j=0;j< columnCount;j++){
temp = rs.getObject(j+1);
}
}
} finally {
if (!rs==null){
rs.close();
}
if (!stmt==null){
stmt.close();
}
if (!conn==null){
conn.close();
}
}
}

Calling ref-cursor from pl/sql

create or replace procedure test_call is

c_cursor REFCURSOR_PKG.STRONG REF_CURSOR;
r_emp    c_emp%rowtype;
begin
test(10,c_cursor);
loop
fetch c_cursor into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.name);
end loop;
close c_cursor;
end test_call;

 

  1. Exception Handling in PLSQL

 

Use of user defined exception

 

declare
v_ename varchar2(10);
single_row exception;
begin
select ename into v_ename from emp where deptno=20 and rownum=1;
if(v_ename=’SMITH’) then
raise single_row;
end if;
exception
when single_row then
dbms_output.put_line(‘FETCHED ROW IS SMITH’);
end;

 

declare
v_ename varchar2(10);
–single_row exception;
begin
select ename into v_ename from emp where deptno=20 ;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘More rows’);
when others then
dbms_output.put_line(sqlerrm||’Other error’);
end;

declare
v_ename varchar2(10);
–single_row exception;
begin
select ename into v_ename from emp where deptno=20 ;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘More rows’||sqlcode);
when others then
dbms_output.put_line(sqlerrm||sqlcode);
end;

 

declare
v_ename varchar2(10);
–single_row exception;
begin
select ename into v_ename from emp where deptno=21 ;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘More rows’||sqlcode);
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
end;
declare
v_ename varchar2(10);
–single_row exception;
begin
select ename into v_ename from emp where deptno=21 ;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(‘More rows’||sqlcode);
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
declare
v_ename varchar2(10);
begin
select ename into v_ename from emp where deptno=20 and rownum=1;
if(v_ename=’SMITH’) then
raise_application_error(-20000,’Employee name is SMITH’);
end if;
end;
declare
v_ename varchar2(10);
test_error exception;
pragma exception_init(test_error,-1422);
begin
select ename into v_ename from emp where deptno=20 ;
exception
when test_error then
dbms_output.put_line(‘test error’);
end;

 

declare
p_ename varchar2(10);
single_row exception;
begin
select ename into p_ename from emp where deptno=20 and rownum=1;
–raise single_row;
declare
v_ename varchar2(10);
single_row exception;
begin
select ename into v_ename from emp where deptno=20 and rownum=1;
if(v_ename=’SMITH’) then
raise globals.single_row;
end if;
end;
exception
when no_data_found then
dbms_output.put_line(‘NO DATA A’);
when globals.single_row then
dbms_output.put_line(‘FETCHED ROW IS SMITH B’);
when others then
dbms_output.put_line(sqlerrm||’B’);
end;

  1. EXAMPLE OF VARRAYS

 

VARRAY INITILAZATION

Example 1:

declare
v_temp  product%rowtype;
counter number(10) := 0;
type v_prod is varray(4) of product%rowtype;——–Declaration of varray
v_product v_prod := v_prod();——-Initialization of varray.
cursor c_product is
select * from product;
begin
open c_product;
loop
fetch c_product
into v_temp;
exit when counter = 4;
v_product.extend;———–Allocation of space for varray.
counter := counter + 1;
dbms_output.put_line(counter);
v_product(counter) := v_temp;
end loop;
close c_product;
counter := 1;
loop
dbms_output.put_line(v_product(counter).project_number);
counter := counter + 1;
exit when counter = 5;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Example 2:

declare
— v_temp  product%rowtype;
counter number(10) := 0;
type v_prod is varray(4) of product%rowtype;——–Declaration of varray
v_product v_prod := v_prod();——-Initialization of varray.
begin
for i in (select * from product where rownum<=4) loop
v_product.extend;
v_product(v_product.count):=i;
end loop;
counter := 1;
loop
dbms_output.put_line(v_product(counter).project_number);
counter := counter + 1;
exit when counter = 5;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

 

Another Example

declare
— v_temp  product%rowtype;
counter number(10) := 0;
type v_prod is varray(4) of product%rowtype;——–Declaration of varray
v_product v_prod := v_prod();——-Initialization of varray.
begin
for i in (select * from product where rownum<=4) loop
v_product.extend;
v_product(v_product.count):=i;
end loop;
for i in  v_product.first..v_product.last loop
dbms_output.put_line(v_product(i).project_number);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Example 3:

declare
counter number(10) := 0;
type v_prod is varray(4) of varchar2(10);
v_product v_prod := v_prod(‘NULL’,’NULL’,’NULL’,’NULL’);—Initialize and assigned value.
begin
loop
exit when counter = 4;
–v_product.extend;——–Extend is not needed if already intialized it.
counter := counter + 1;
dbms_output.put_line(counter);
v_product(counter) := ‘A’;
end loop;
counter := 1;
loop
dbms_output.put_line(v_product(counter));
counter := counter + 1;
exit when counter = 5;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Example 4:

declare
type v_temp is varray(4) of number(10);
vv_temp v_temp:=v_temp();
begin
vv_temp.extend(4);
vv_temp(1):=1;
vv_temp(2):=2;
vv_temp(3):=3;
–vv_temp(4):=4;
dbms_output.put_line(vv_temp(3));
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Example 5:

create or replace type v_tab is varray(4) of number(10);

create table v_table(col1 v_tab);

insert into v_table values(v_tab(1,2,3,4))

select * from v_table;

select column_value from the(select cast(col1 as v_tab) from v_table);

 

  1. EXAMPLE OF NESTED TABLE

NESTED TABLE  INITILAZATION

Example 1:

declare
v_temp  product%rowtype;
counter number(10) := 0;
type v_prod is table of product%rowtype;——–Declaration of varray
v_product v_prod := v_prod();——-Initialization of varray.
cursor c_product is
select * from product;
begin
open c_product;
loop
fetch c_product
into v_temp;
exit when counter = 4;
v_product.extend;———–Allocation of space for varray.
counter := counter + 1;
dbms_output.put_line(counter);
v_product(counter) := v_temp;
end loop;
close c_product;
counter := 1;
loop
dbms_output.put_line(v_product(counter).project_number);
counter := counter + 1;
exit when counter = 5;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

 

EXAMPLE 2

 

Create a table with NESTED TABLE column:

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
NESTED TABLE col1 STORE AS col1_tab;
Insert data into table:

INSERT INTO nested_table VALUES (1, my_tab_t(‘A’));
INSERT INTO nested_table VALUES (2, my_tab_t(‘B’, ‘C’));
INSERT INTO nested_table VALUES (3, my_tab_t(‘D’, ‘E’, ‘F’));
COMMIT;

 

Select from nested table:

SQL> SELECT * FROM nested_table;
ID COL1
———- ————————
1 MY_TAB_T(‘A’)
2 MY_TAB_T(‘B’, ‘C’)
3 MY_TAB_T(‘D’, ‘E’, ‘F’)
Unnesting the subtable:

SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
ID COLUMN_VALUE
———- ————————
1 A
2 B
2 C
3 D
3 E
3 F
6 rows selected.

A more complicated multi-column nested table where customers can have multiple addresses:

CREATE TYPE address_t AS OBJECT (
street  VARCHAR2(30),
city    VARCHAR2(20),
state   CHAR(2),
zip     CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
custid  NUMBER,
address address_tab )

NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (1,
address_tab(
address_t(‘101 First’, ‘Redwood Shores’, ‘CA’, ‘94065’),
address_t(‘123 Maple’, ‘Mill Valley’,    ‘CA’, ‘90952’)
)                );

 

  1. EXAMPLE OF ASSOCIATIVE ARRAY

declare
v_temp  product%rowtype;
counter number(10) := 0;
type v_prod is table of product%rowtype index by binary_integer;——–Declaration of varray
v_product v_prod ;–:= v_prod();——-Initialization of varray.
cursor c_product is
select * from product where rownum<5;
begin
counter:=1;
for i in c_product loop
v_product(counter):=i;
counter:=counter+1;
end loop;
counter:=1;
loop
dbms_output.put_line(v_product(counter).project_number);
counter := counter + 1;
exit when counter = 5;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

 

create or replace procedure price_update is
v_count number(10);
v_temp  number(10);
t1 number(10);
t2 number(10);
counter number(10);
cursor c_pp is
select  project_number,price
from product_price pp
where price_type_code = ‘NET’
and country_code = ‘US’
and currency_code = ‘USD’
and division = ‘MHHE’
and country_sold_in = ‘US’;
TYPE pnum IS TABLE OF product.project_number%TYPE INDEX BY PLS_INTEGER;
TYPE pprice IS TABLE OF product_price.price%TYPE INDEX BY PLS_INTEGER;
v_pnum pnum;
v_pprice pprice;
begin
dbms_output.enable(10000);
t1:=dbms_utility.get_time;
SELECT /*+ USE_HASH(P PP) */ COUNT(1) into v_count
FROM
PRODUCT P, PRODUCT_PRICE PP WHERE PP.PRICE_TYPE_CODE = ‘NET’ AND
PP.COUNTRY_CODE = ‘US’ AND PP.CURRENCY_CODE = ‘USD’ AND PP.DIVISION =
‘MHHE’ AND PP.COUNTRY_SOLD_IN = ‘US’ AND P.PROJECT_NUMBER =
PP.PROJECT_NUMBER;
dbms_output.put_line(‘Total number of records to be updated ‘ || v_count);
counter:=1;
for i in c_pp loop
v_pnum(counter):=i.project_number;
v_pprice(counter):=i.price;
counter:=counter+1;
end loop;

FORALL i IN 1 .. v_count — use FORALL statement
update product p set net_price=v_pprice(i) where p.project_number=v_pnum(i);

/*
FOR i IN 1..l_data.COUNT loop
update +parallel  product p set net_price = l_data(i).price
where p.project_number = l_data(i).project_number;
v_temp := i;
if (mod(v_temp, 1000) = 0) then
dbms_output.put_line(‘number of record processed ‘ || v_temp);
end if;
END LOOP;
dbms_output.put_line(‘Total number of records  updated ‘ || l_data.COUNT);*/

t2:=dbms_utility.get_time;
dbms_output.put_line((t2-t1)/100);
exception
when others then
dbms_output.put_line(substr(sqlerrm, 1, 254));
end price_update;

Leave a Reply

Your email address will not be published. Required fields are marked *