PLSQL Procedural Language extensions to SQL

1
PL/SQL (Procedural Language extensions to SQL)

• Prepared by Manoj Kathpalia
• Edited by M V Ramakrishna

2
Outline

• Purpose of PL/SQL.
• PL/SQL block, its structure and types.
• PL/SQL Syntax.

3
Purpose of PL/SQL

• Procedural programming language used to access an
  Oracle database.
• Designed to overcome SQL's inability to handle
  control aspects of database interaction.
• Extends SQL by adding procedural language
  constructs, such as
• - Variables and types.

4

• - Control structures (IF-THEN-ELSE
• statements and loops).
• - Procedures and functions.
• Procedural constructs are integrated seamlessly
  with Oracle SQL, resulting in a structured,
  powerful language.
• Well-suited for designing complex applications.

5
PL/SQL Block

• Block is a basic unit in PL/SQL.
• All PL/SQL programs consist of blocks and each
  block performs a logical function in the program.
• Blocks can be nested within each other or can
  occur sequentially.

6
PL/SQL Block Structure

• DECLARE
• (Declarative section)
• BEGIN
• (Executable section)
• EXCEPTION
• (Exception handling section)
• END
• / (/ at the end of a block tells Oracle to run
  the block)

7
Types of PL/SQL Blocks

• as Anonymous blocks which are compiled and run
  when loaded.
• as Triggers to maintain integrity.
• as Subprograms (Procedures and Functions) stored
  within the database that can be executed many
  times.
• as Packages, a named declarative section for
  storing related objects that can include
  procedures, functions and variables.

8
PL/SQL SyntaxDeclaration

• Variables are declared in the declarative section
  of the block.
• Variable declaration examples
• v_student_id CHAR(8)
• v_lastname VARCHAR2(25)
• v_capacity NUMBER(3) 200
• ( is used to initialize variables )

9
Types

• Basic PL/SQL types are
• NUMBER(P,S) holds numeric value
• (where P is the precision and S is the scale)
• VARCHAR2(L) holds strings or character data
• (where L is the maximum length of the variable)
• CHAR(L) for fixed-length character strings
• DATE for storing date and time information
• BOOLEAN can hold TRUE, FALSE or NULL only

10
Declaring variables with TYPE attribute

• TYPE attribute is used when a PL/SQL variable is
  going to be used to manipulate the data stored in
  the database.
• The variable should have the same type as the
  table column (field).
• Specifying TYPE attribute takes care.

11

• For example, if the variable is used in the
  PL/SQL program to store the phone number of a
  student, following declaration can be used
• v_phone STUDENT.phoneTYPE
• Instead of, say
• v_phone CHAR(10)

12
PL/SQL SyntaxControl Structures

• IF-THEN-ELSE
• IF boolean_expression1 THEN
• sequence_of_statements1
• ELSIF boolean_expression2 THEN
• sequence_of_statements2
• ELSE
• sequence_of_statements3
• END IF

13

• LOOPS
• Simple Loops
• LOOP
• sequence_of_statements
• END LOOP
• EXIT WHEN condition is used to exit the loop

14

• WHILE Loops
• WHILE condition LOOP
• sequence_of_statements
• END LOOP
• EXIT or EXIT WHEN will also be used to exit the
  loop

15

• FOR Loops
• FOR loop_counter IN REVERSE low_bound ..
  high_bound
• LOOP
• sequence_of_statements
• END LOOP

16
Comments

• Single-line comments
• DECLARE
• v_title CHAR(2) -- title can be Mr or Ms
• Multi-line comments
• DECLARE
• / I am a multi-line
• comment /

17

• -- Command to display the output at the SQL
  prompt
• SET SERVEROUTPUT ON
• -- This PL/SQL block accepts a number from the
  terminal, and prints out its square.
• -- You can try this from sql
• -- It has no exception handling code.
• DECLARE
• num1 NUMBER(3)
• num1_sq NUMBER(6)
• BEGIN
• -- prompts user for a value for 'anytemp'
• num1 anytemp
• num1_sq num1 num1
• DBMS_OUTPUT.PUT_LINE('THE SQUARE OF ' num1
  ' IS ' num1_sq)
• END
• /

18
(No Transcript)
19
(No Transcript)
20
(No Transcript)