PL/SQL

1
PL/SQL

• Scripting in Oracle
• everything you wanted to know but were afraid to
  ask.

Haley Miller
2
Questions I will answer

• What is PL/SQL?
• What are its Advantages
• What are its components?
• What is the difference between procedures and
  functions?

• How is SQL incorporated into PL/SQL
• What does the code actually look like?
• Where did you get all this information?

3
What is PL/SQL?

• Oracle-specific SQL-based language
• conglomeration of SQL and a traditional
  programming languages
• allows loops, declarations, branches to
  subroutines and assignments
• procedural, transactional processing language
  that extends oracle and allows data manipulation
  not possible in SQL alone

4
Advantages (abridged)

• Gives programmer control
• can create constraints other than those allowed
  in SQL
• Oracle allows SOME control relating to values
  entered into the tables, but some applications
  may require other constraints
• relate to another record
• that apply only in a certain instance (if
  fieldAx, then fieldB gt90)
• organizes repetitive tasks in to logical units

5
Programmer control(continued)

• extended error handling
• more specific error messages
• subroutines in case of error
• on this error and if valuegtx then do SubroutineA
• on this error and if valueltx then so SubroutineB
• flow control
• specify forms and events based on input or
  actions
• triggers
• subprograms that the database executes
  automatically in response to specified events.

6
Advantages continued (abridged)

• Easy on the server
• The PL/SQL engine can process an entire blocks of
  code at once even one with multiple SQL
  statements in one call.
• store compiled code directly in the database.
• This enables any number of applications or users
  to share the same functions and procedures once
  a given block of code is loaded into memory, any
  number of users can use the same copy of it
  simultaneously (although behavior is as though
  each user had her own copy)

7
Structure and Syntax

• Three sections
• Declarative
• Executable
• Exception-handling
• this example
• a declarative section and an executable block
  consisting of 2 sub-blocks

8
The DECLARE Section

• define local variables, constants, types,
  exceptions, and nested subprograms. PL/SQL has a
  forward declaration, but you can use it only for
  subprograms. Therefore, you must define all
  variables, constants, and types before
  referencing them.
• begins with the keyword DECLARE and ends when the
  keyword BEGIN signals the arrival of the
  EXECUTABLE section

9
The EXECUTABLE Section

• actual code that the block executes. This is the
  only part of the block that must always be
  present.
• SQL statements written here
• Begins with the word Begin and ends with the word
  end
• May have several begin/end statements with in sub
  blocks

10
The EXECUTABLE Section.

• PL/SQL supports the following kinds of
  flow-control statements
• IF statements
• Basic loops
• FOR loops
• WHILE loops
• GOTO statements

11
SQL within PL/SQL

• Placed in the exectution section
• very few changes
• Insert, select, update, delete etc are all used
  here along with variables, conditions

12
The EXCEPTION Section

• For handling runtime errors and warnings.

13
Procedures Vs. Functions

• Two types of blocks
• function is used in an expression and returns a
  value to that expression
• computes a value as part of the expression,
  contains a return value and MUST return a value
• procedure is invoked as a standalone statement
  and passes values to the calling program only
  through parameters

14

15
Parameters

• Used to pass to and from the calling environment
• Three modes
• In - contains a value passed from the calling
  environment
• Out - contains a value returned to the calling
  environment
• In/Out - contains a value passed to and returned
  to the calling environment often values is
  modified buy subprogram

16

17
Structure of Procedure and Functions

• PROCEDURE procedure_name (param_name datatype,
  param_name datatype...) IS local
  declarations BEGIN executable code
  EXCEPTION END
• FUNCTION function_name (param_name, param_name
  datatype...) RETURN datatype IS local
  declarations BEGIN executable code
  EXCEPTION local exception handlers END

18
A REALLY SIMPLE Example

• A simple example
• Commit
• this line of code commits the record in a given
  form to a table. It can be hooked to a button on
  the form and is called a trigger

19
Example Insert

• Values from the declare section (in brown) a
  inserted in to the table via the executable
  section

20
Example Delete with constant

• Close_stat is a constant specifed in the declare
  section and called in the Where clause of the
  Delete Statement

21
A word about limitations

• PL/SQL elements must exist at run-time
• the is not a DDL (data definition language)
• this example is not correct

22
References

• Overview of the Oracle7 Server, SQL, and PL/SQL
• http//www.hawkesbury.uws.edu.au8901
  /ows-adoc/ap1serv.htm
• The ORACLE PL/SQL CBT in Ileanas office
• installed in KitKat in Mc104
• NetGs PL/SQL CBT