Pl sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 73

PL/SQL PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on
  • Presentation posted in: General

PL/SQL. PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages. PL/SQL bridges the gap between database technology and procedural programming languages.

Download Presentation

PL/SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Pl sql

PL/SQL


Introduction

PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

PL/SQL bridges the gap between database technology and procedural programming languages.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of-control statements to process the data.

You can also declare constants and variables, define procedures and functions, and trap runtime errors.

Introduction


Advantages of pl sql

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL

Support for object-oriented programming

Better performance

Higher productivity

Full portability

Tight integration with Oracle

Tight security

Advantages of PL/SQL


What is block structure

PL/SQL is a block-structured language.

blocks are basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program .

Can contain any number of nested sub-blocks.

A block (or sub-block) lets you group logically related declarations and statements.

you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.

What is Block Structure?


Components of pl sql block

PL/SQL block has three parts:

a declarative part

executable part

exception-handling part.

Only the executable part is required. The order of the parts is logical.

First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part.

Exceptions raised during execution can be dealt with in the exception-handling part.

Components of PL/SQL Block


Building blocks of pl sql

Declaration Part

Example:

declare a variable of type NUMBER to store the quantity of tennis rocket in stock.

Execution Part

Example:

retrieves the quantity of items on hand from a database table named inventory.

If the quantity is greater than zero

updates the table

inserts a purchase record into another table named purchase_record.

Otherwise,

inserts an out-of-stock record into the purchase_record table.

Building Blocks of PL/SQL


Overview of pl sql

DECLARE

qty_on_hand NUMBER(5);

BEGIN

SELECT quantity INTO qty_on_hand FROM inventory

WHERE product = 'TENNIS RACKET‘;

IF qty_on_hand > 0 THEN

UPDATE inventory SET quantity = quantity - 1

WHERE product = 'TENNIS RACKET';

INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE);

ELSE

INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE);

END IF;

COMMIT;

END;

Overview of PL/SQL


Variables and constants

PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used.

However, forward references are not allowed. So, you must declare a constant or variable before referencing it in other statements, including other declarative statements.

Variables and Constants


Declaring variables

Variables can have any SQL datatype, such as

CHAR

DATE

NUMBER

Or any PL/SQL datatype, such as:

BOOLEAN

BINARY_INTEGER

Example

part_no NUMBER(4);

in_stock BOOLEAN;

Declaring Variables


Examples of identifiers

Some examples of identifiers follow:

X

t2

phone#

credit_limit

LastName

oracle$number

Examples of Identifiers


Literals

Numeric Literals: 34569-89

Character Literals : 'Z' '%' '7' 'z' '('

String Literals

'Hello, world!‘

'XYZ Corporation'

All string literals except the null string ('') have datatype CHAR.

Boolean Literals

TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value).

Boolean literals are values, not strings.

Literals


Literals1

Datetime Literals

'1998-12-25‘

'1997-10-22 13:01:01'

Literals


Comments

Single-Line Comments

-- begin processing

Multi-line Comments

Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines.

Comments


Scope and visibility

An identifier is visible only in the regions from which you can reference the identifier.

Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks.

Scope and Visibility


Assigning values

One way to assign values to variables is to use the assignment operator (:=):

Examples

tax := price * tax_rate;

valid_id := FALSE;

bonus := current_salary * 0.10;

wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;

Assigning Values


Assigning values1

Another way is by fetching database values into it.

Example: compute a 10% bonus when you select the salary of an employee and store the value into the the variable bonus.

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

Assigning Values


Assigning values2

The third way is by passing it as an OUT or INOUT parameter to a subprogram.

Example of INOUT parameter:

DECLARE

my_sal REAL(7,2);

PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...

BEGIN

SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- assigns a new value to my_sal

Assigning Values


Declaring constants

You must add the keyword CONSTANT and assign a value to the constant.

In the following example, you declare a constant named credit_limit:

credit_limit CONSTANT REAL := 5000.00;

Declaring Constants


Using default

You can use the keyword DEFAULT instead of the assignment operator to initialize variables.

blood_type CHAR := 'O';

can be rewritten as follows:

blood_type CHAR DEFAULT 'O';

Use DEFAULT for variables that have a typical value.

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Using DEFAULT


Using not null

You can declare a variable as NOTNULL:

acct_id INTEGER(4) NOT NULL := 9999;

The NOTNULL constraint must be followed by an initialization clause:

Using NOT NULL


Operators

Arithmetic

+- / * ** (exponent)

Comparison

=, <, >, <+, >=, <>, !=, IS NULL, LIKE, BETWEEN IN

Logical:

AND, OR, NOT

Other:

|| (concatenation),

Operators


Pl sql data types

Predefined Data Types

User-Defined Data Types

Predefined Data types

A scalar type

NUMBER, CHAR, VARCHAR2, NCHAR, NVARCHAR2, …

A composite type

RECORD

A reference type

REF

PL/SQL Data types


Pl sql

A percent sign that precedes a table attribute serves as a type identifier.

In other words, the %TYPE provides the datatype of a database column.

Example:

Consider column named title in a table named books.

To declare a variable named my_title that has the same datatype as column title, use:

my_title books.title%TYPE;

%TYPE


Pl sql

Declaring my_title with %TYPE has two advantages.

You need not know the exact datatype of title.

If you change the database definition of title the datatype of my_title changes accordingly at run time.

%TYPE


If elsif else

IF clause defines what to do if the conditions is TRUE the ELSE clause defines what to do if the condition is false or null.

IF condition1 THEN

sequence_of_statements1

ELSIF condition2 THEN sequence_of_statements2

ELSE

sequence_of_statements3

END IF;

IF - ELSIF - ELSE


If elsif else1

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;

IF – ELSIF - ELSE


If then else

DECLARE

acct_balance NUMBER(11,2);

acct CONSTANT NUMBER(4) := 3;

debit_amt CONSTANT NUMBER(5,2) := 500.00;

BEGIN

SELECT bal INTO acct_balance FROM accounts

WHERE account_id = acct

FOR UPDATE OF bal;

IF acct_balance >= debit_amt THEN

UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct;

ELSE

INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds');

END IF;

COMMIT;

END; 

IF-THEN-ELSE


Case statement

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;

CASE Statement


Case statement1

Using the following syntax, the CASE statement can evaluate a condition and returns a value for each case:

CASE

WHEN shape = 'square' THEN area := side * side;

WHEN shape = 'circle' THEN

BEGIN

area := pi * (radius * radius); DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');

END;

WHEN shape = 'rectangle' THEN area := length * width;

ELSE

BEGIN

DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape); RAISE PROGRAM_ERROR;

END;

END CASE;

Case Statement


Iterative control

LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords ENDLOOP after the last statement in the sequence.

Example:

 LOOP

-- sequence of statements

END LOOP;

Iterative Control


Simple loop

LOOP

...

IF credit_rating < 3 THEN

EXIT; -- exit loop immediately

END IF;

END LOOP;

SIMPLE LOOP


Using exit when

LOOP

...

EXIT WHEN credit_rating < 3 ;

END LOOP;

USING EXIT - WHEN


While loop

WHILE condition LOOP

sequence_of_statements

END LOOP;

Example:

Declare

counter number (5):=0;

Begin

WHILE counter < 3 LOOP

dbms_output.put_line(counter);

counter:=counter+1;

END LOOP;

END;

WHILE-LOOP


For loop

The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range.

For example, the following loop inserts 500 numbers and their square roots into a database table:

FOR num IN 1..500 LOOP

INSERT INTO roots VALUES (num, SQRT(num));

END LOOP; 

FOR LOOP


Pl sql

GOTO transfers the control to a statement preceded by a label, unconditionally.

IF rating > 90 THEN

GOTO calc_raise;

END IF;

...

<<calc_raise>>

IF job_title = 'SALESMAN' THENZ

amount := commission * 0.25;

ELSE amount := salary * 0.10;

END IF;

Not allowed to branch into an if, loop, or an inner block.

GOTO


Subprograms

PL/SQL has two types of subprograms

procedures

functions,

Subprograms are named PL/SQL blocks that can take parameters and be invoked.

Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part.

Subprograms


Procedures

[CREATE [OR REPLACE]]

PROCEDURE procedure_name[(parameter[, parameter]...)]

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

Procedures


Example of a procedure

CREATE or REPLACE PROCEDURE valid_bonus(emp_id NUMBER) AS

bonus NUMBER :=100;

BEGIN

SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

IF bonus IS NULL THEN

DBMS_OUTPUT.PUT_LINE('Bonus is NULL');

ELSE

DBMS_OUTPUT.PUT_LINE('Bonus is: '|| bonus);

END IF;

END valid_bonus;

/

Example of a Procedure


Another example

CREATE OR REPLACE

PROCEDURE raise_salary (emp_id emp.empno%TYPE, amount REAL) IS

current_salary REAL;

BEGIN

CURRENT_SALARY := 1000;

SELECT sal INTO current_salary FROM emp WHERE empno = emp_id;

IF current_salary IS NULL THEN

DBMS_OUTPUT.PUT_LINE('SALARY IS NULL');

ELSE

UPDATE emp SET sal = sal + amount WHERE empno = emp_id;

END IF;

END raise_salary;

/

Another Example


Calling procedures

A procedure can be called within a PL/SQL blocks. For example, you might call the procedure raise_salary as follows:

DECLARE

XNUMBER := 7369;

YNUMBER:= 123;

BEGIN

Raise_salary(X, Y);

END;

/

Procedures also can be called with an EXEC command, or as standalone statement within a block:

EXEC raise_salary(7369, 123);

Calling Procedures


Another example1

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS

old_balance REAL;

new_balance REAL;

BEGIN

SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id;

new_balance := old_balance - amount;

IF new_balance < 0 THEN

EXIT;

ELSE

UPDATE accts SET bal = new_balance WHERE acct_no = acct_id;

END IF;

END debit_account;

Another Example


Functions

A function is a subprogram that computes a value.

Functions and procedures are similar, except that functions have a RETURN clause.

[CREATE [OR REPLACE ] ]

FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN datatype {IS | AS}

[ local declarations ]

BEGIN

executable statements

[ EXCEPTION

exception handlers ]

END [ name ];

/

Functions


Example of a function

CREATE OR REPLACE

FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS

min_sal REAL;

max_sal REAL;

BEGIN

SELECT losal, hisal INTO min_sal, max_sal FROM sals

WHERE job = title;

RETURN (salary >= min_sal) AND (salary <= max_sal);

END sal_ok;

Example of a Function


Calling funtions

A user defined function can be called in an SQL statements similar to built-in functions, or inside other expressions:

SELECT sal_ok (sal) FROM emp

WHERE empno = 7396;

Calling Funtions


Subprogram parameter modes

The three parameter modes, IN (the default), OUT, and IN OUT, can be used with anysubprogram.

A void using the OUT and IN OUT modes with functions.

The purpose of a function is to take zero or more arguments (actual parameters) and return a single value.

Subprogram Parameter Modes


In mode

An IN parameter lets you pass values to the subprogram being called.

An IN parameter acts like a constant.

Example: A compilation error:

PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS

BEGIN

IF amount < 888 THEN

amount := amount + 5; -- causes compilation error

END IF;

...

END debit_account;

IN Mode


Out mode

OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable:

An OUT actual parameter can have a value before the subprogram is called.

However, when you call the subprogram, the value is lost.

The actual parameter of an out parameter cannot be a constant or an expression. The following statement fails:

myfucntion(x + 2);

OUT MODE


Out mode example

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS

hire_date DATE;

BEGIN

SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id;

IF bonus IS NULL THEN

Dbms_output.put_line(‘bonus is null’);

END IF;

IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN

bonus := bonus + 500;

END IF;

END calc_bonus;

Out Mode - Example


In out mode

An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

An IN OUT parameter acts like an initialized variable.

It can be assigned a value andits value can be assigned to another variable.

IN OUT Mode


Cursors

Oracle cursors are work areas to allow to execute SQL statements and store processing information.

There are two kinds of cursors:

Implicit

explicit

PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.

Cursors


Declaring a cursor

You must declare a cursor before referencing it in other statements.

When you declare a cursor, you name it and associate it with a specific query.

Syntax:

CURSOR cursor_name IS select_statement;

Note: Cursor name is not a PL/SQL variable. Means you can not assign values to it, or use it in an expression .

Declaring a Cursor


Declaring a cursor1

For example, you might declare cursors named c1 and c2, as follows:

DECLARE CURSOR c1 IS

SELECT empno, ename, job, sal

FROM emp

WHERE sal > 2000;

Declaring a Cursor


Result set

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria.

An explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.

Result Set


Result set1

Statements OPEN, FETCH, and CLOSE statements to control a cursor.

The OPEN executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row.

The FETCH statement retrieves the current row and advances the cursor to the next row.

When the last row has been processed, the CLOSE statement disables the cursor.

Result Set


Opening a cursor

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria.

Example:

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

BEGIN

OPEN c1;

...

END;

Opening a Cursor


Fetching with a cursor

FETCH statement retrieves the rows in the result set one at a time.

Each fetch retrieves the current row and then advances the cursor to the next row in the result set.

Example:

FETCH c1 INTO my_empno, my_ename, my_deptno;

A typical use of FETCH:

LOOP

FETCH c1 INTO …. ;

EXITWHENc1%NOTFOUND;

END LOOP;

Fetching with a Cursor


Example

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;

LOOP

FETCH c1 INTO my_sal;

EXIT WHEN c1%NOTFOUND;

factor := factor + 1;

END LOOP;

END;

Example


Using different intos

You can also use a different INTO list on separate fetches with the same cursor.

Example:

DECLARE CURSOR c1 IS SELECT ename FROM emp;

name1 emp.ename%TYPE;

name2 emp.ename%TYPE;

name3 emp.ename%TYPE;

BEGIN

OPEN c1;

FETCH c1 INTO name1; -- this fetches first row

FETCH c1 INTO name2; -- this fetches second row

FETCH c1 INTO name3; -- this fetches third row ...

CLOSE c1;

END;

Using different INTOs


What if fetch fails

FETCH statement fails to return a row, if tries to read after the last row.

In this case, no exception is raised.

To detect the failure, you must use the cursor attribute:

%FOUND

%NOTFOUND.

What-If FETCH Fails?


Closing a cursor

To close a cursor use:

CLOSE cursor_name;

CLOSE c1;

The CLOSE statement disables the cursor, and the result set becomes undefined.

Once a cursor is closed, you can reopen it.

Any other operation on a closed cursor raises the predefined EXCEPTION:

INVALID_CURSOR.

Closing a Cursor


Records

In PL/SQL, records are used to group data.

A record consists of a number of related fields in which data values can be stored

Example:

DECLARE

TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);

TYPE MeetingTyp IS RECORD (

date_held DATE,

duration TimeRec,

location VARCHAR2(20),

purpose VARCHAR2(50)

);

Records


Rowtype

The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

Columns in a row and corresponding fields in a record have the same names and data types.

%ROWTYPE


Example1

DECLARE

dept_rec dept%ROWTYPE;

In this example a record named dept_rec is declared that Its fields have the same names and data types as the columns in the dept table.

Now you can access the deptno value by:

my_deptno Number

my_deptno := dept_rec.deptno;

Example


Example2

If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use %ROWTYPE to declare a record that stores the same information, as follows:

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE;

The last line declares a record variable that represents a row fetched from the emp table

Example


Example contd

Now you can assign the value in the ename column of the emp table to the ename field of emp_rec, the value in the sal column is assigned to the sal field, and so on, by following statement:

FETCH c1 INTO emp_rec;

Example (contd.)


Subqueries in cursors

Consider the following query with a subquery in declaration of a cursor:

DECLARE CURSOR c1 IS

SELECT empno, ename FROM emp WHERE deptno IN

(SELECT deptno FROM dept WHERE loc <> 'CHICAGO');

Using a subquery in the FROM clause:

DECLARE CURSOR c1 IS

SELECT t1.deptno, dname, "STAFF"

FROM dept t1, (SELECT deptno, COUNT(*) "STAFF"

FROM emp GROUP BY deptno) t2 WHERE t1.deptno = t2.deptno

AND "STAFF" >= 5;

Subqueries in Cursors


Cursor for loops

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements.

A cursor FOR loop implicitly declares its loop index as a RECORD(%ROWTYPE) that represents a row fetched from the database.

Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record.

Then closes the cursor when all rows have been processed.

Cursor FOR Loops


Example3

In the following example, the cursor FOR loop implicitly declares emp_rec as a record:

DECLARE

salary_total NUMBER;

CURSOR c1 IS

SELECT ename, sal, hiredate, deptno FROM emp;

BEGIN

salary_total := 0;

FOR emp_rec IN c1 LOOP

salary_total := salary_total + emp_rec.sal;

END LOOP;

DBMS_OUTPUT.PUT_LINE('The total salay is: ' || SALARY_TOTAL);

END;

Example


Another example2

Compute results from an experiment, then store the results in a temporary table.

DECLARE

result temp.col1%TYPE;

CURSOR c1 IS

SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;

BEGIN

FOR c1_rec IN c1 LOOP

result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);

INSERT INTO temp VALUES (result, NULL, NULL);

END LOOP;

COMMIT;

END;

Another Example


Using subqueries instead of cursors

You need not declare a cursor because PL/SQL lets you substitute a subquery. The following cursor FOR loop calculates a bonus, then inserts the result into a database table:

DECLARE

bonus REAL;

BEGIN

FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP

bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);

INSERT INTO bonuses VALUES (emp_rec.empno, bonus);

END LOOP;

COMMIT;

END;

Using Subqueries Instead of Cursors


Cursor variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query.

But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query.

Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database.

This gives you more flexibility and a convenient way to centralize data retrieval.

Cursor Variables


Example4

You open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters.

The following procedure opens the cursor variable generic_cv for the chosen query:

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade;

END IF;

...

END;

Example


Built in packages

PL/SQL provides several built-in packages.

Two useful packages are:

dbms_output

dbms_sql (used for dynamic SQL)

Built-in Packages


  • Login