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

Introduction to PL/SQL PowerPoint PPT Presentation


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

Introduction to PL/SQL. Part I. Objectives. SQL Vs. PL/SQL PL/SQL Fundamentals PL/SQL Block Structure PL/SQL Control Structures Database Interaction in PL/SQL Exception Handling PL/SQL. SQL Vs. PL/SQL. SQL No Procedural Capability Time Consuming Processing

Download Presentation

Introduction to 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


Introduction to pl sql

Introduction to PL/SQL

Part I


Objectives

Objectives

  • SQL Vs. PL/SQL

  • PL/SQL Fundamentals

  • PL/SQL Block Structure

  • PL/SQL Control Structures

  • Database Interaction in PL/SQL

  • Exception Handling PL/SQL


Sql vs pl sql

SQL Vs. PL/SQL

  • SQL

    • No Procedural Capability

    • Time Consuming Processing

    • No Error Handling Procedures

    • No Sharing of code

    • Low performance


Sql vs pl sql1

SQL Vs. PL/SQL

  • PL/SQL

    • Highly structured, readable, and accessible

    • Error Handling Procedures

    • Facilitate Sharing of code

    • Improved Transaction Performance

    • Portable and standard Code


Pl sql program blocks

PL/SQL Program Blocks

  • Comments:

    • Not executed by interpreter

    • Enclosed between /* and */

    • On one line beginning with --


Executing a pl sql program in sql plus

Executing a PL/SQL Program in SQL*Plus

  • Create program in text editor

  • Paste into SQL*Plus window

  • Press Enter, type / then enter to execute


Fundamentals of pl sql

Fundamentals of PL/SQL

  • Full-featured programming language

  • An interpreted language

  • Type in editor, execute in SQL*Plus


Variables and data types

Variables and Data Types

  • Variables

    • Used to store numbers, character strings, dates, and other data values

    • Avoid using keywords, table names and column names as variable names

    • Must be declared with data type before use: variable_name data_type_declaration;

      stu_id CHAR(9);


Scalar data types

Scalar Data Types

  • Represent a single value


Scalar data types1

Scalar Data Types


Assignment statements

Assignment Statements

Assigns a value to a variable

variable_name := value;

Value can be a literal:

current_s_first_name := 'John';

Value can be another variable:

current_s_first_name := s_first_name;

num1:=num1+num2+(num3*3);school:=‘kv2ocfspn’;flag:=TRUE;

11


Declare variable

Declare Variable

  • Syntax

    identifier [CONSTANT] datatype [NOT NULL]

    [:= | DEFAULT expr];

  • Examples

    Declare

    birthdayDATE;

    age NUMBER(2) NOT NULL := 27;

    nameVARCHAR2(13) := 'Levi';

    magicCONSTANT NUMBER := 77;

    validBOOLEAN NOT NULL := TRUE;


Composite and reference variables

Composite and Reference Variables

  • Composite variables

    • RECORD: contains multiple scalar values, similar to a table record

    • TABLE: tabular structure with multiple columns and rows

    • VARRAY: variable-sized array

  • Reference variables

    • Directly reference a specific database field or record and assume the data type of the associated field or record

    • %TYPE: same data type as a database field

    • %ROWTYPE: same data type as a database record


Record

Record

  • DECLARE

  • TYPE sailor_record_type IS RECORD

  • (sname VARCHAR2(10),

  • sid VARCHAR2(9),

  • ageNUMBER(3),

  • rating NUMBER(3));

  • sailor_recordsailor_record_type;

  • ...

  • BEGIN

  • Sailor_record.sname:=‘peter’;

  • Sailor_record.age:=45;


Introduction to pl sql

  • CREATE TABLE Books (

    book_id INTEGER,

    isbn VARCHAR2(13),

    title VARCHAR2(100),

    summary VARCHAR2(2000),

    author VARCHAR2(200),

    date_published DATE,

    page_count NUMBER);


Record1

Record

  • Declare

    my_book books%ROWTYPE;

    BEGIN

    SELECT *

    INTO my_book

    FROM books

    WHERE title=‘Oracle PL/SQL Programming’;

    DBMS_OUTPUT.PUT_LINE(my_book.summary);


Declaring variables with the type attribute

Declaring Variables with the %TYPE Attribute

  • fav_boatVARCHAR2(30);

    my_fav_boatfav_boat%TYPE := 'Pinta';

  • book_name books.title%TYPE;


Arithmetic operators

Arithmetic Operators


Pl sql data conversion functions

PL/SQL Data Conversion Functions


Manipulating character strings with pl sql

Manipulating Character Strings with PL/SQL

  • To concatenate two strings in PL/SQL, you use the double bar (||) operator:

    • new_string := string1 || string2;

  • To remove blank leading spaces use the LTRIM function:

    • string := LTRIM(string_variable_name);

  • To remove blank trailing spaces use the RTRIM function:

    • string := RTRIM(string_variable_name);

  • To find the number of characters in a character string use the LENGTH function:

    • string_length := LENGTH(string_variable_name);


Manipulating character strings with pl sql1

Manipulating Character Strings with PL/SQL

  • To change case, use UPPER, LOWER, INITCAP

  • INSTR function searches a string for a specific substring:

    • start_position := INSTR(original_string, substring);

  • SUBSTR function extracts a specific number of characters from a character string, starting at a given point:

    • extracted_string := SUBSTR(string_variable, starting_point, number_of_characters);


Pl sql decision control structures

PL/SQL Decision Control Structures

  • Use IF/THEN structure to execute code if condition is true

    • IF condition THEN

      commands that execute if condition is TRUE;

      END IF;

  • If condition evaluates to NULL it is considered false

  • Use IF/THEN/ELSE to execute code if condition is true or false

    • IF condition THEN

      commands that execute if condition is TRUE;

      ELSE

      commands that execute if condition is FALSE;

      END IF;

  • Can be nested – be sure to end nested statements


If else example

IF/ELSE Example

  • DECLAREa NUMBER;b NUMBER;BEGINSELECT e, f INTO a, b FROM T1 WHERE e>1;IF b=1 THEN INSERT INTO T1 VALUES(b,a);ELSE INSERT INTO T1 VALUES(b+10,a+10);END IF;END;

    /


Pl sql decision control structures1

PL/SQL Decision Control Structures

  • Use IF/ELSIF to evaluate many conditions:

    • IF condition1 THEN

      commands that execute if condition1 is TRUE;

      ELSIF condition2 THEN

      commands that execute if condition2 is TRUE;

      ELSIF condition3 THEN

      commands that execute if condition3 is TRUE;

      ...

      ELSE

      commands that execute if none of the

      conditions are TRUE;

      END IF;


If elsif example 1

IF/ELSIF Example 1

. . .

IF rating > 7 THEN

v_message := 'You are great';

ELSIF rating >= 5 THEN

v_message := 'Not bad';

ELSE

v_message := 'Pretty bad';

END IF;

. . .


If elsif example 2

IF/ELSIF Example 2


Introduction to pl sql

DECLARE

male_avg number;

female_avg number;

BEGIN

SELECT AVG(months_between(employment_date, birth_date)/12)

INTO male_avg

FROM Employee

WHERE gender=‘M’;

SELECT AVG(months_between(employment_date, birth_date)/12)

INTO female_avg

FROM Employee

WHERE gender=‘F’;

IF (male_avg > female_avg) THEN

DBMS_OUTPUT.PUT_LINE(‘Males have the greater avg hiring age’);

DBMS_OUTPUT.PUT_LINE(‘With the avg age of ‘ || to_char(male_avg));

IF(male_avg > female_avg + 10) THEN

DBMS_OUTPUT.PUT_LINE(‘The male average is greater than 10 years’);

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE(‘Females have the greater avg hiring age’);

DBMS_OUTPUT.PUT_LINE(‘With the avg age of ‘ || to_char(female_avg));

END IF;

END;

/


Complex conditions

Complex Conditions

  • Created with logical operators AND, OR and NOT

  • AND is evaluated before OR

  • Use () to set precedence


Using sql queries in pl sql programs

Using SQL Queries in PL/SQL Programs

  • Action queries can be used as in SQL*Plus

  • May use variables in action queries

  • DDL commands may not be used in PL/SQL


Loops

Loops

  • Program structure that executes a series of program statements, and periodically evaluates an exit condition to determine if the loop should repeat or exit

  • Pretest loop:evaluates the exit condition before any program commands execute

  • Posttest loop:executes one or more program commands before the loop evaluates the exit condition for the first time


Loops1

Loops

  • PL/SQL has 3 loop structures

    • loop … end loop;

    • while condition loop … end loop;

    • for variable in low_bound . . upper_bound loop … end loop;


The loop exit loop

The LOOP...EXIT Loop

LOOP

[program statements]

IF condition THEN

EXIT;

END IF;

[additional program statements]

END LOOP


Introduction to pl sql

/*program to find the sum of first 10 natural numbers */Declare nsum number(6):=0; i number:=1;Begin Loop nsum:=nsum+ i; i:=i+1; if i>10 then Exit; End If; End Loop; dbms_output.put_line (‘The sum is:’ || nsum);End;

/


The loop exit when loop

The LOOP...EXIT WHEN Loop

LOOP

program statements

EXIT WHEN condition;

END LOOP;


Introduction to pl sql

/* Program to find the sum of first 10 natural number */DECLARE nsum nubmer(6):=0; i number:=1; BEGIN LOOP nsum:=nusm+1; i:=i+1; EXIT WHEN i=11; -- Because I will process till 10 END LOOP;dbms_output.put_line(‘The sum is: ‘|| nsum);END;

/


The while loop

The WHILE...LOOP

WHILE condition LOOP

program statements

END LOOP;


Introduction to pl sql

/* Program to find the sum of first 10 natural numbers */DECLARE nsum number(6):=0; i number:=1;BEGIN WHILE i<11 LOOP --because it will execute till 10 nsum:=nsum+i; i:=i+1; END LOOP;DBMS_OUTPUT.PUT_LINE (‘The sum is :’||nsum);

END;

/


The numeric for loop

The Numeric FOR Loop

FOR counter_variable IN start_value .. end_value

LOOP

program statements

ENDLOOP;


Introduction to pl sql

/* Program to find the sum of first 10 natural numbers */DECLARE nsum number:=0; i number:=1;BEGIN FOR i IN 1..10 LOOP nsum:=nsum + i; END LOOP;DBMS_OUTPUT.PUT_LINE (‘The sum is: ’ || nsum);End;

/


The continue statement

The CONTINUE Statement

BEGIN

FOR i_index IN 1..10 LOOP

CONTINUE WHEN MOD(i_index, 2) = 0;

DBMS_OUTPUT.PUT_LINE(‘Loop index = ‘ ||

TO_CHAR(l_index));

END LOOP;

END;

/


Cursors

Cursors

  • Pointer to a memory location that the DBMS uses to process a SQL query

  • Use to retrieve and manipulate database data


Implicit cursor

Implicit Cursor


Using an implicit cursor

Using an Implicit Cursor

  • Executing a SELECT query creates an implicit cursor

  • To retrieve it into a variable use INTO:

    • SELECT field1, field2, ...

      INTO variable1, variable2, ...

      FROM table1, table2, ...

      WHERE join_ conditions

      AND search_condition_to_retrieve_1_record;

  • Can only be used with queries that return exactly one record


Using an implicit cursor1

Using an Implicit Cursor

DECLARE

v_sname VARCHAR2(10);

v_rating NUMBER(3);

BEGIN

SELECTsname, rating

INTOv_sname, v_rating

FROMSailors

WHEREsid = '112';

END;

/

  • INTO clause is required.

  • Query must return exactly one row.

  • Otherwise, a NO_DATA_FOUND or TOO_MANY_ROWS exception is thrown.


Explicit cursor

Explicit Cursor

  • Use for queries that return multiple records or no records

  • Must be explicitly declared and used


Using an explicit cursor

Using an Explicit Cursor

  • Declare the cursor

    • CURSOR cursor_name IS select_query;

  • Open the cursor

    • OPEN cursor_name;

  • Fetch the data rows

    • LOOP

      FETCH cursor_name INTO variable_name(s);

      EXIT WHEN cursor_name%NOTFOUND;

  • Close the cursor

    • CLOSE cursor_name;


Introduction to pl sql

Using an Explicit Cursor

sailorData is a variable that can hold a ROW from the sailors table

DECLARE

CURSOR c IS SELECT * FROM sailors;

sailorData sailors%ROWTYPE;

BEGIN

OPEN c;

FETCH c INTO sailorData;

Here the first row of sailors is inserted into sailorData


Example

Example

DECLARE

Pi CONSTONT NUMBER(8,7) := 3.1415926;

area NUMBER(14,2);

cursor rad_cursor IS SELECT * FROM Rad_Vals;

rad_value rad_cursor%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursorinto rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius, area);

close rad_cursor;

END;

/

Rad_Vals

Rad_cursor

fetch

Rad_val

AREAS

Radius

Area

3

28.27


Introduction to pl sql

DECLARE

cursor rad_cursor is select * from RAD_VALS;

rad_value rad_cursor%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius,

area);

DECLARE

cursor rad_cursor is select* from RAD_VALS;

rad_value RAD_VALS.radius%TYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val,2);

insert into AREAS values (rad_val, area);

1

4

2

3

DECLARE

cursor rad_cursor is select * from RAD_VALS;

rad_valueRAD_VALS%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius, area);

DECLARE

cursor rad_cursor is selectradiusfrom

RAD_VALS;

rad_value RAD_VALS.radius%TYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val,2);

insert into AREAS values (rad_val, area);


Introduction to pl sql

DECLARE

oldest_birth_date DATE;

lname Employee.last_name%TYPE;

fname Employee.first_name%TYPE;

CURSOR find_old_b_day IS SELECT min(birth_date) FROM Employee;

CURSOR id_employee IS SELECT last_name, first_name

FROM Employee

WHERE birth_date = oldest_birth_date;

BEGIN

OPEN find_old_b_day;

FETCH find_old_b_day INTO oldest_birth_date;

CLOSE find_old_b_day;

OPEN id_employee;

FETCH id_employee into lname, fname;

CLOSE id_employee;

DBMS_OUTPUT.PUT_LINE(‘The oldest employee is’ || lname || ‘, ‘ || fname);

END

/

The oldest employee is Johnson, Andrew


Explicit cursor attributes

Explicit Cursor Attributes

  • Obtain status information about a cursor.

Attribute Type Description

%ISOPEN Boolean Evaluates to TRUE if the cursor is open.

%NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row.

%FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row; complement of %NOTFOUND

%ROWCOUNT Number Evaluates to the total number of rows returned so far.


Introduction to pl sql

DECLARE

oldest_birth_date DATE;

lname Employee.last_name%TYPE;

fname Employee.first_name%TYPE;

CURSOR find_old_b_day IS SELECT min(birth_date) FROM Employee;

CURSOR id_employee IS SELECT last_name, first_name

FROM Employee

WHERE birth_date = oldest_birth_date;

BEGIN

IF NOT find_old_b_day%ISOPEN THEN

OPEN find_old_b_day;

END IF;

FETCH find_old_b_day INTO oldest_birth_date;

IF find_old_b_day%ISOPEN THEN

CLOSE find_old_b_day;

END IF

OPEN id_employee;

FETCH id_employee into lname, fname;

CLOSE id_employee;


Explicit cursor with rowtype

Explicit Cursor with %ROWTYPE


Cursor for loop

Cursor FOR Loop

  • Automatically opens the cursor, fetches the records, then closes the cursor

  • FOR variable_name(s) IN cursor_name LOOP

    processing commands

    END LOOP;

  • Cursor variables cannot be used outside loop


Loops simple loop

Loops: Simple Loop

create table number_table(

num NUMBER(10)

);

DECLARE

i number_table.num%TYPE := 1;

BEGIN

LOOP

INSERT INTO number_table

VALUES(i);

i := i + 1;

EXIT WHEN i > 10;

END LOOP;

END;


Loops simple cursor loop

Loops: Simple Cursor Loop

create table number_table(

num NUMBER(10)

);

DECLARE

cursor c is select * from number_table;

cVal c%ROWTYPE;

BEGIN

open c;

LOOP

fetch c into cVal;

EXIT WHEN c%NOTFOUND;

insert into doubles values(cVal.num*2);

END LOOP;

close c;

END;


Loops for loop

Loops: FOR Loop

DECLARE

inumber_table.num%TYPE;

BEGIN

FOR i IN 1..10 LOOP

INSERT INTO number_table VALUES(i);

END LOOP;

END;

Notice that i is incremented automatically


Loops for cursor loops

Loops: For Cursor Loops

DECLARE

cursor c is select * from number_table;

BEGIN

for num_row in c loop

insert into doubles_table

values(num_row.num*2);

end loop;

END;

/

Notice that a lot is being done implicitly: declaration of num_row, open cursor, fetch cursor, the exit condition


Loops while loop

Loops: WHILE Loop

DECLARE

TEN number:=10;

inumber_table.num%TYPE:=1;

BEGIN

WHILE i <= TEN LOOP

INSERT INTO number_table

VALUES(i);

i := i + 1;

END LOOP;

END;


Using cursor for loop

Using Cursor FOR Loop


Handling runtime errors in pl sql programs

Handling Runtime Errors in PL/SQL Programs

  • Runtime errors cause exceptions

  • Exception handlers exist to deal with different error situations

  • Exceptions cause program control to fall to exception section where exception is handled


Predefined exceptions

Predefined Exceptions


Introduction to pl sql

DECLARE

lname employee.last_name%TYPE;

BEGIN

select last_name into lname from employee;

dbms_output.put_line(lname);

END;

/

What may go wrong?


Introduction to pl sql

DECLARE

num_row number_table%ROWTYPE;

BEGIN

select *

into num_row

from number_table;

dbms_output.put_line(1/num_row.num);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('No data!');

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('Too many!');

WHENOTHERSTHEN

dbms_output.put_line(‘Error’);

END;

/


Using sqlcode and sqlerrm

Using SQLCODE and SQLERRM

  • SQLCODE is a function that returns the numeric error number.

  • SQLERRM is a character string that contains a description of the error


Introduction to pl sql

BEGIN

insert into employee (last_name)

values(‘PETERSEN’);

EXCEPTION

when others then

dbms_output.put_line(to_char(SQLCODE);

dbms_output.put_line(SQLERRM);

END;

/

-1400

ORA-01400: mandatory (NOT NULL) column is missing or NULL dring insert


Undefined exceptions

Undefined Exceptions

  • Less common errors

  • Do not have predefined names

  • Must declare your own name for the exception code in the declaration section

    • DECLARE

      e_exception_name EXCEPTION;

      PRAGMA EXCEPTION_INIT(e_exception_name,

      -Oracle_error_code);


Introduction to pl sql

DECLARE

not_null_vio exception;

pragma exception_init(not_null_vio, -1500);

BEGIN

insert into employee (last_name) values (‘Peterson’);

EXCEPTION

when not_null_vio then

dbms_output.put_line(‘Not null value needed!’);

END;

/


User defined exceptions

User-Defined Exceptions

  • Not a real Oracle error

  • Use to enforce business rules


Introduction to pl sql

DECLARE

e_number1 EXCEPTION;

cnt NUMBER;

BEGIN

select count(*)

into cnt

from number_table;

IF cnt = 1 THEN RAISE e_number1;

ELSE dbms_output.put_line(cnt);

END IF;

EXCEPTION

WHEN e_number1 THEN

dbms_output.put_line('Count = 1');

END;

/


Defining your own error messages

Defining Your Own Error Messages

  • The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms.

    raise_application_error(      error_number, message[, {TRUE | FALSE}]);

    where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long.


Introduction to pl sql

DECLARE

num_tables NUMBER;

BEGIN

SELECT COUNT(*) INTO num_tables

FROM USER_TABLES;

IF num_tables < 1000 THEN

/* Issue your own error code (ORA-20101) with your own error

message. */

raise_application_error(-20101, 'Expecting at least 1000

tables');

ELSE

NULL; -- Do the rest of the processing.

END IF;

END;

/


Summary

Summary

  • PL/SQL is a programming language for working with an Oracle database

  • Scalar, composite and reference variables can be used

  • The IF/THEN/ELSE decision control structure allows branching logic

  • Five loop constructs allow repeating code

  • Cursors are returned from queries and can be explicitly iterated over

  • Exception handling is performed in the exception section. User defined exceptions help to enforce business logic


  • Login