Chapter Fifteen PL/SQL (Procedural Language)

1
Chapter FifteenPL/SQL (Procedural Language)

• Objective
• Introduction to PL/SQL Programming

2
PL/SQL

• SQL (Structured Query Language)
• PL/SQL (Procedural Language) Oracle language for
  stored procedures
• Characteristics of PL/SQL
• Highly structured, accessible
• Standard language for Oracle development
• Embedded language
• Powerful error handling
• Call Java class from PL/SQL

3
PL/SQL

• Why PL/SQL
• Programs are portable Applications written in
  Pl/SQL can run on any operating systems
  platforms
• Support for Object Oriented programming
• Group of statements execute at one time
• Modularized program development
• Error handling
• Supports
• Declaration
• Print Output
• Define functions Procedures.
• Cursors

4
Example

• DECLARE
• S_GPA NUMBER(3,1)
• BEGIN
• SELECT GPA INTO S_GPA
• FROM Student
• WHERE ID 1111
• FOR UPDATE OF GPA
• S_GPA S_GPA 0.75
• UPDATE Student
• SET GPA S_GPA
• WHERE ID 1111
• COMMIT
• END

5
Scripting Languages

• Programming Languages
• Java,C,
• Scripting Languages
• PL/SQL, Perl, PHP, Unix Shell, Python, Javascript
• Properties of scripting languages
• Interpreted
• Do not require functions/procedures
• Weakly typed

6
PL/SQL Symbols

• Comments
• --
• / /
• rem
• BEGIN END (not )
• AND OR (not , )
• End Statement
• Host Variable Indicator

7
PL/SQL Symbols

• Attribute Indicator
• Cursor attributes ISOPEN
• Declaration Attributes ROWTYPE
• Remote Access Indicator
• _at_
• Assignment Operator

8
The Basics

• Identifiers constants, variables, exceptions,
  cursors, cursor variables, subprograms, packages
• Reserved words
• Literals Numeric -10
• Character
• String Hello world
• Boolean TRUE
• Date 2002-11-12
• Date Time 2002-11-12 120102

9
Declarations

• Id CONSTANT datatype NOT NULL DEFAULT
  Expres
• Example
• DECLARE
• Birthday Date
• Mid_Name CharM
• Temp SMALLINT0
• Flag BOOLEANFALSE
• Pi CONSTANT NUMBER(9,7) 3.1415927
• P1 REAL NOT NULL 3
• P2 REAL3.15
• P3 REALP1P2
• BEGIN
• NULL
• END

10
Declaration

• DECLARE
• A INTEGER0
• B INTEGER DEFAULT 10
• C NUMBER(3,2) NOT NULL 1.0
• D POSITIVE0
• E ATYPE
• My_ID Student.IDTYPE
• My_Rec StudentROWTYPE

11
Declaration

• DECLARE
• Name varchar2(40) Lory Latoo
• UpperName Name TYPE UPPER(Name)
• LowerName Name TYPE LOWER(Name)
• BEGIN
• --print the name
• DBMS_OUTPUT.PUT_LINE(Name UpperName
  LowerName)
• END

12
Notes

• No forward references.
• No multiple declarations
• a,b,c NUMBER(4)
• All variables with no assigned values are set to
  null.
• No constraint is inherited from table declaration.

13
Identifiers

• Names
• User Identifiers
• 1-30 characters
• Start with an alphabetic character
• Followed by alphabet, digit, _
• Unique
• Not reserved
• Not case sensitive
• ,

14
Types of Variables

• PL/SQL Variables
• Scalar
• Composite
• Reference
• LOB
• Non-PL/SQL Variables
• Host language variables (Bond Variables)

15
Data Types

• Scalar Types
• Characters
• Numbers
• Boolean
• Date
• COMPOSITE TYPES
• RECORD
• TABLE
• VARRAY
• Reference Types
• REF CURSOR
• REF object_type
• LOB Types
• BFILE
• BLOB
• CLOB
• NCLOB

16
Data Types

• Scalar Types
• Character
• CHAR (Size CHARBYTE)
• CHAR
• CHAR(25)
• CHAR (25 BYTE)
• MaxSize is 32767
• VARCHAR2 (MaxSize CHARBYTE)

(continued)
17
Predefined Data Types (Scalar Types)
LONG LONG RAW NCHAR NVARCHAR2

• CHAR
• CHARACTER
• STRING
• VARCHAR
• VARCHAR2

18
Predefined Data Types (Scalar Types)

• Number
• BINARY_INTEGER
• (-231 To 231)
• NATURAL
• POSITIVE
• POSITIVEN
• SIGNTYPE
• PLS_INTEGER
• (-230 To 231)

NUMBER (Precision, Scale) Number Number(5)
DEC DECIMAL NUMERIC DOUBLE
PRECISION FLOAT INT INTEGER SMALLINT REAL
(continued)
19
Predefined Data Types (Scalar Types)

• BOOLEAN
• TRUE, FALSE, NULL
• DATE
• MyBirthdate DATE DATE 2001-08-12
• (4712BC to DEC 31, 9999 AD)
• Today Date SYSDATE

20
Predefined Data Types (Scalar Types)

• TIMESTAMP
• Checkout TIMESTAMP
• TIMESTAMP 2001-08-12 082544
• DECLARE
• checkout TIMESTAMP
• BEGIN
• checkout 2001-08-12 082544
  DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout))
• END

21
Timestamp With Time Zone

• T1 TIMESTAMP with Time Zone 2003-01-01 90000
  800
• T2 TIMESTAMP with Time Zone 2003-01-01
  120000 500
• (9am Pacific Standard Time 1200pm Eastern
  Standard Time)
• Function CURRENT_TIMESTAMP( )

22
Intervals

• INTERVAL YEAR To MONTH
• INTERVAL DAY To SECOND

23
INTERVALs

• DECLARE
• worked INTERVAL YEAR(3) TO MONTH
• BEGIN
• worked INTERVAL '121-3' YEAR TO MONTH
• worked '121-3' -- implicit conversion from
  character type worked INTERVAL '121' YEAR --
  Can specify just the years
• worked INTERVAL '3' MONTH -- Can specify
  just the months
• END /

24
Intervals

• DECLARE
• Service INTERVAL YEAR TO MONTH
• Yearofservice NUMBER
• Monthofservice NUMBER
• end_date DATA
• start_date DATE
• BEGIN
• SELECT L_date, S_date INTO end_date,
  start_date FROM faculty WHERE id11111
• Service (end_date start_date) YEAR To
  MONTH
• Yearofservice EXTRACT (YEAR FROM Service)
• Monthofservice EXTRACT (MONTH FROM Service)
• DBMS_OUTPUT.PUT_LINE (Yearofservice -
  Monthofservice )
• END
• /

25
Predefined Data types (Scalar Types)

• ROWID
• SELECT ROWID, Name
• FROM Student
• WHERE ID1111
• ROWID Name
• OOOOOOFFFBBBBBBRRR JOHN SMITH
• Object No.
• File No.
• Block No.
• Row No.
• UROWID Logical position of a row
• ROWIDTOCHAR( ROWID)

26
Predefined Data types (Scalar Types)

• Example
• SELECT ROWID
• FROM Student
• WHERE ROWIDTOCHAR( ROWID) LIKE SAAb

27
Predefined Data types (Scalar Types)

• 6. ROWNUM
• SELECT Name, GPA
• FROM Student
• WHERE ROWNUMlt5
• ORDER BY GPA

28
Predefined Data Types (Scalar Types)

• 7. TYPE
• Name Student.nameType
• balance Number(6,2)
• Newbalance balanceType
• Mybalance balanceType20

29
Predefined Data types (Scalar Types)

• 8. User defined data types
• SUBTYPE .. IS ..
• DECLARE
• SUBTYPE MyNumber is NUMBER(5)
• Max MyNumber
• BEGIN
• MyNumber 11111

30
Predefined Data Types

• Composite Types (Chapter 16)
• RECORD
• TABLE
• VARRAY
• C. Reference Types (Chapter 18)
• REF CURSOR
• REF object_type

31
Predefined Data Types

• LOB Types (Long OBject) (Max 4 Gig)
• Stores block of unstructured data
• BFILE Binary File
• BLOB Binary Object
• CLOB Block of CHAR
• NCLOB Block of N CHAR

32
Constants

• Total CONSTANT NUMBER(2) 45
• Total NUMBER(2) DEFAULT 45
• Syntax
• Identifier CONSTANT datatype NOT NULL
  expression
• Identifier datatype NOT NULL DEFAULT
  expression

33
General Guidelines

• Statements can continue over several lines.
• Lexical units can be separated by
• Space
• Delimiters
• Identifiers
• Literals
• Comments
• Case Sensitive
• PL/SQL Terminate With /

(continued)
34
General Guidelines

• Functions in SQL that will not work with PL/SQL
• DECODE
• Group Functions

35
Block Structure

• DECLARE
• variables, constants, cursors
• BEGIN
• SQL PL/SQL statements
• EXCEPTION --optional
• END
• / --to execute

36
Block

• PL/SQL code is grouped into structures called
  blocks.
• If a block of code does not have a name, it is
  called Anonymous Block.
• A Block contains
• Declaration
• Executable Commands
• Exception Handling
• Named Blocks
• Procedures
• Functions

37
Program Constructs

• Blocks can be used in
• Anonymous block
• Application trigger
• Database trigger
• Stored Procedure or Function
• Application Procedure or Function
• Packaged Procedure or Function

38
Example

• DECLARE
• S_GPA NUMBER(3,1)
• BEGIN
• SELECT GPA INTO S_GPA
• FROM Student
• WHERE ID 1111
• IF S_GPA gt 3 THEN
• INSERT INTO Tempfile1
• VALUES (S_GPA)
• ELSE
• INSERT INTO Tempfile2
• VALUES (S_GPA)
• END IF
• COMMIT
• END

39
Expressions Hierarchical Operations

• ( )
• -
• /
• -
• ! ltgt lt gt IS NULL, LIKE, BETWEEN, IN
• NOT
• AND
• OR

(Continued)
40
Expressions

• DECLARE
• A NUMBER (10)
• B CHAR(5)
• C CHAR(20)
• D BOOLEAN
• BEGIN
• A 234-2
• B abcde

(continued)
41
Expressions

• -- Character Operations
• C B xy -- Concatenation
• -- Boolean Operations
• D B C
• D NOT TRUE
• -- Like, Between, In
• END

42
Non-PL/SQL Variables (Bind Variables)

• VARIABLE salary NUMBER
• DECLARE
• A NUMBER25000
• BEGIN
• salary A
• END
• /

43
Non-PL/SQL Variables (Bind Variables)

• You can print value of the bind variable using
  PRINT
• SQLgt PRINT salary
• SALARY
• ----------
• 25000

44
Interaction with The Oracle Server

• Retrieve data from a database
• SELECT List
• INTO Variable
• FROM Table
• WHERE Condition

(continued)
45
Interaction with The Oracle Server

• Example
• DECLARE
• V_Name VARCHAR(10)
• V_ID NUMBER(6)
• V_GPA NUMBER(3,1)
• BEGIN
• SELECT Name, ID, GPA
• INTO V_Name, V_ID, V_GPA
• FROM Student
• WHERE ID 1111
• END
• /

(continued)
46
Interaction with The Oracle Server

• DECLARE
• V_Name Student.NameTYPE
• V_ID Student.IDTYPE
• V_GPA Student.GPATYPE
• R_Data StudentROWTYPE

47
INSERT INTO

• DECLARE
• V_Name Student.NameTYPE
• V_ID Student.IDTYPE
• V_GPA Student.GPATYPE
• BEGIN
• SELECT Name, ID, GPA
• INTO V_Name, V_ID, V_GPA
• FROM Student
• WHERE ID 1111
• INSERT INTO TempTable (T_Name, T_ID, T_ GPA)
• VALUES (V_Name, V_ID, V_GPA)
• END
• /

48
UPDATE INTO

• DECLARE
• V_Name Student.NameTYPE
• V_ID Student.IDTYPE
• V_GPA Student.GPATYPE
• BEGIN
• SELECT Name, ID, GPA
• INTO V_Name, V_ID, V_GPA
• FROM Student
• WHERE ID 1111
• UPDATE TempTable
• SET T_NameV_Name
• WHERE T_ID1111
• END
• /

49
DELETE FROM

• DECLARE
• V_Name Student.NameTYPE
• V_ID Student.IDTYPE
• V_GPA Student.GPATYPE
• BEGIN
• SELECT Name, ID, GPA
• INTO V_Name, V_ID, V_GPA
• FROM Student
• WHERE ID 1111
• DELETE FROM TempTable
• WHERE T_Name V_Name
• END
• /

50
RETURNING

• DECLARE
• V_rowid ROWID
• V_Name Student.NameTYPE
• V_ID Student.IDTYPE
• V_GPA Student.GPATYPE
• BEGIN
• SELECT Name, ID, GPA
• INTO V_Name, V_ID, V_GPA
• FROM Student
• WHERE ID 1111
• INSERT INTO TempTable (T_Name, T_ID, T_ GPA)
• VALUES (V_Name, V_ID, V_GPA)
• RETURNING ROWID INTO V_rowid
• DBMS_OUTPUT.PUT_LANE(New record is inserted in
  row V_rowid)
• END
• /

51
Ambiguous Names

• ltltProgramgtgt
• DECLARE
• ID NUMBER(5) 111111
• BEGIN
• DELETE FROM Student ID
• WHERE ID Program.ID
• .
• END

52
SCOPE OF VARIABLES

• DECLARE
• V_sal NUMBER(6,2) 54000
• V_Com NUMBER(6,2) V_sal.015
• V_text VARCHAR2(60)
• BEGIN
• V_sal NUMBER(6,2) 60000
• V_Com NUMBER(6,2) V_sal.025
• BEGIN
• DBMS_OUTPUT.PUT_LINE(V_sal)
• END
• DBMS_OUTPUT.PUT_LINE(V_sal)
• END

53
Selection Statements

• IF-THEN
• Must end with END IF (not ENDIF)
• Use ELSIF (not ELSE IF)
• Can be nested

54
Selection Statements

• IF Condition THEN action1 END IF
• IF Condition THEN action1 ELSE action2 END
  IF
• IF Condition THEN action1 ELSIF Condition2 .
  END IF
• IF BooleanExpression THEN
• Statement(s)
• ELSIF BooleanExpression THEN
• Statement(s)
• ELSE
• Statement(s)
• END IF

55
Example

• DECLARE
• A NUMBER(1) 3
• B NUMBER(1) 4
• C VARCHAR2(10)
• Name VARCHAR2(10)
• Job VARCHAR2(10)
• Dept NUMBER(1)
• Salary NUMBER(8,2)
• Office NUMBER(3)

(continued)
56
Example

• BEGIN
• IF A IS NULL THEN
• C Empty
• END IF
• IF AgtB THEN
• CLarge A
• ELSE
• C Small a
• END IF
• IF Name MARY THEN
• Job Manager
• Dept5
• Salary B_Salary 0.21
• Office 231
• END IF
• END

57
Example

• IF salary gt 20000 AND salary lt 100000
• THEN
• salary salary 1000
• ELSIF salary gt10000 AND salary lt 20000
• THEN
• salary salary 500
• ELSIF salary gt5000 THEN
• salary salary 100
• END IF

58
Multiple Selection

• CASE
• WHEN Major COSC THEN
• courseCOSC100
• WHEN MajorMATH THEN
• courseMATH120
• WHEN Major ENGL THEN
• courseENGL101
• WHEN Major PHYS THEN
• coursePHYS220
• ELSE course unknown --optional
• END CASE
• -- CASE_NOT_FOUND ( )

59
Case Statements

• CASE Major
• WHEN COSC THEN
• courseCOSC100
• WHEN MATH THEN
• courseMATH120
• WHEN ENGL THEN
• courseENGL101
• WHEN PHYS THEN
• coursePHYS220
• ELSE
• courseunknown --optional
• END CASE

60
Case Statements

• course
• CASE
• WHEN MajorCOSC THEN COSC100
• WHEN MajorMATH THEN MATH120
• WHEN MajorENGL THEN ENGL101
• WHEN MajorPHYS THEN PHYS220
• ELSE unknown
• END CASE

61
Example

• CASE
• WHEN salary gt 20000 AND salary lt100000 THEN
• salary salary 1,000
• WHEN salary gt10000 AND salary lt 20000 THEN
• salary salary 500
• WHEN salary gt 5000 THEN
• salary salary 100
• END CASE

62
Case Statement in SQL

• Case statements are supported in ORACLE SQL
• SELECT
• CASE
• WHEN..THEN
• ELSE.
• END
• FROM student

63
Loops

• Process multiple records within a single PL/SQL
  block
• Types of loops
• Simple
• EXIT or EXIT WHEN
• FOR loop
• WHILE loop

64
Iteration Statements

• LOOP
• Statement(s)
• END LOOP
• Example
• DECLARE
• Counter BINARY_INTERGER 0
• BEGIN
• LOOP
• CounterCounter 1
• IF Counter 10 THEN
• EXIT -- or EXIT WHEN Counter10
• END IF
• END LOOP
• END

(continued)
65
Iteration Statements

• WHILE Condition LOOP
• Statement(s)
• END LOOP
• Example
• DECLARE
• Counter BINARY_INTERGER 0
• BEGIN
• WHILE counterltgt10 LOOP
• CounterCounter1
• END LOOP

(continued)
66
Iteration Statements

• FOR Index IN REVERSE
• lowExpression..highExpression LOOP
• Statement(s)
• END LOOP
• Example
• FOR Counter IN 1..3 LOOP
• --Executes 3 times (1,2,3)
• END LOOP

(continued)
67
Iteration Statements

• Example
• FOR Counter IN 3..3 LOOP
• -- Executes one time (3)
• END LOOP
• FOR Counter IN REVERSE 1..3 LOOP
• NULL -- Executes one time ( 3,2,1)
• END LOOP

(continued)
68
Iteration Statements

• GOTO
• GOTO Label
• IF A20 THEN
• GOTO Label_1
• END IF
• ltltLabel_1gtgt
• INSERT INTO STUDENT ( )
• VALUES ( )

69
Use of Labels

• ltltLabel_1gtgt
• DECLARE
• A NUMBER (10)
• B CHAR(5)
• BEGIN
• DECLARE
• B CHAR(20)
• C DATE
• D BOOLEAN
• BEGIN
• GOTO Label_1
• END
• END

70
Use of Labels

• Case, Loop, If, and For blocks can be labeled
• ltltLabel_Namegtgt
• CASE
• .
• .
• .
• END CASE Label_Name
• ltltLabel_Namegt
• LOOP
• .
• .
• .

71
GOTO

• GOTO statement can not branch into
• IF
• CASE
• LOOP
• Sub-block
• GOTO statement can not branch from one IF
  statement to another. (CASE)
• GOTO statement can not branch from an outer block
  into a sub-block (begin-end).
• .