Slide 1 of 36

1
From Prof. Dullea CSC8490 Introduction to
PL/SQL

• Dr. James Dullea
• professor_at_comcast.net
  

Module 01-9 Revised June 12, 2005
2
Introduction to PL/SQL

• What is PL/SQL
• Why PL/SQL
• Kinds of PL/SQL BLOCKS
• Anonymous or Named Blocks
• Named Blocks (Stored procedures, Funtions,
  Triggers)
• Exceptions
• Assignments (2)

3
What is PL/SQL

• PL/SQL is a sophistical programming language used
  to access an Oracle database from a various
  environments.
• PL/SQL stands for Procedural Language/SQL.
• It extends SQL by adding constructs found in
  other procedural languages, such as loops,
  conditional statements, declared variables,
  accessing individual records one at a time, and
  many others.

4
Why use PL/SQL

• Compared to SQL, PL/SQL has the procedural
  constructs that are useful to express a desired
  process from start to end.
• One block of PL/SQL code can bundled several SQL
  statements together as a single unit. Making less
  network traffic and improving application
  performance.
• PL/SQL can be integrated with other languages,
  such as Java, to take advantage of the strongest
  features of both languages.

5
Kinds of PL/SQL BLOCKS
The basic unit in any PL/SQL PROGRAM is a BLOCK.
All PL/SQL programs are composed of a single
block or blocks that occur either sequentially
or nested within another block. There are two
kinds of blocks Anonymous blocks are generally
constructed dynamically and executed only once by
the user. It is sort of a complex SQL
statement. Named blocks are blocks that have a
name associated with them, are stored in the
database, and can be executed again and again,
can take in parameters, and can modify and
existing database.
6
Structure of Anonymous Block

• DECLARE
• / Declare section (optional). /
• BEGIN
• / Executable section (required). /
• EXCEPTION
• / Exception handling section (optional). /
• END -- end the block (do not forget the
  in the end.)
• /

7
Example of Anonymous Block

• SET SERVEROUTPUT ON
• DECLARE
• v_last_name VARCHAR2(15)
• v_salary employee.salaryTYPE
• CURSOR c_query IS
• SELECT lname, salary FROM employee
• BEGIN
• OPEN c_query
• LOOP
• FETCH c_query INTO v_last_name, v_salary
• IF v_salary gt 40000 THEN
• DBMS_OUTPUT.PUT_LINE (v_last_name'
  'v_salary)
• END IF
• EXIT WHEN c_queryNOTFOUND
• END LOOP
• CLOSE c_query
• END
• /

8
PL/SQL Build-In Utility PackagesDBMS_OUTPUT.PUT_L
INE

• SET SERVEROUTPUT ON to allow output to be
  displayed to the screen
• DBMS_OUTPUT.PUT_LINE
• Usage
• DBMS_OUTPUT.PUT_LINE ( Argument )
• Argument tendS to resemble the concatenated
  arguments of the SELECT clause in an SQL query.
• If the argument is not initialized, then a NULL
  VALUE will be displayed.

9
PL/SQL Build-In Utility Packages Example

• SET SERVEROUTPUT ON
• DECLARE
• v_name VARCHAR2(30)
• v_title VARCHAR2(20)
• BEGIN
• v_name 'James Dullea'
• v_title 'Research Scientist'
• DBMS_OUTPUT.PUT_LINE(v_name' works as a
  'v_title)
• END
• /

10
Variables and Types

• A variable is a storage location that can be read
  from or assigned to by the program.
• It is declared in the declarative section within
  a PL/SQL block.
• v_last_name VARCHAR2(15)
• Every variable has a specific data type
  associated with it. Variables can be associated
  with a table structure.
• v_salary employee.salaryTYPE

11
Data Types
Scalar type Numeric INT, DEC,NUMBER,BINARY_INTEGER Character CHAR, CHARACTER, STRING, VARCHAR, VARCHAR2 Boolean TRUE, FALSE. Date DATE
Composite types RECORD, TABLE, VARRAY
Reference types CURSORS, OBJECT TYPES
Lob types BFILE, LOB, CLOB, NLOB
12
CURSORS

• A cursor is used to process a single row 'at a
  time' from multiple rows retrieved from the
  database .
• Cursors are declared in the Declaration Section.
• CURSOR c_query IS
• SELECT lname, salary FROM employee
• The cursor can be declared for complex queries
  involving joins and conditions.
• Cursors must be OPENed to be accessed and CLOSEd
  before ending the program.
• OPEN c_query CLOSE c_query

13
CURSORS

• The FETCH statement is used to retrieve the
  output of a single record from the CURSOR SELECT
  statement INTO associate variables.
• FETCH c_query INTO v_last_name, v_salary
• Cursors can be opened and closed more than once
  in a block and if the a WHERE statement exists,
  the values of the binding variables can be
  modified.
• Cursor FOR loop is a special type of for loop
  which the SQL cursor operations are carried out
  implicitly.

14
Conditional Statements

• Conditional Processing
• The specified
• conditions are
• evaluated by the
• system and the result
• determines which
• sequence of
• statements is to be
• carried out.

• IF ltboolean expressiongt THEN
• ltsequence of statementsgt
• END IF
• --------------------------------------------------
  -------------------------------------------
• IF ltboolean expressiongt THEN
• ltsequence of statementsgt
• ELSE
• ltsequence of statementsgt
• END IF

15
Conditional Statements (ELSIF)
IF ltboolean expressiongt THEN ltsequence of
statementsgt ELSIF ltboolean expressiongt
THEN ltsequence of statementsgt ELSIF ltboolean
expressiongt THEN ltsequence of statementsgt ELSIF
ltboolean expressiongt THEN ltsequence of
statementsgt ELSE ltsequence of statementsgt END IF
16
Loop Structures

• Unconstrained loops
• WHILE loop
• FOR loop
• GOTO ltltLABELgtgt

17
Unconstrained Loops

• LOOP
• ltsequence of statementsgt
• EXIT WHEN ltconditiongt
• ltsequence of statementsgt
• END LOOP

18
WHILE LOOP

• WHILE ltconditiongt
• LOOP
• ltstatementsgt
• END LOOP
• Note The loop will continue to process as
  long as the condition is TRUE or an EXIT (or EXIT
  WHEN) statement is encountered.

19
FOR LOOP

• FOR ltloop_countergt IN REVERSE
• ltlow boundgt..lthigh boundgt
• LOOP
• ltsequence of statementsgt
• END LOOP

20
GOTO statement

• GOTO label
• The label is defined in the block by
• being enclosed in double angle brackets.
• Example
• LOOP
• ltsequence of statementsgt
• IF ltconditiongt THEN
• GOTO get_out_of_loop
• ltsequence of statementsgt
• END LOOP
• ltltget_out_of_loopgtgt

21
NAMED BLOCKS

• The following are types of NAMED BLOCKS
• Stored Procedures
• Similar to an anonymous block except it can be
  stored in the database, can accept parameters,
  and can be executed over and over again (with
  different parameters)
• Functions
• Type of named blocks that is executed within a
  DML or SQL statement. It may take in one or more
  parameters and RETURNs only one value back to the
  calling application.
• Triggers
• A named block that executes only when an
  associated DML statement is executed, such as an
  INSERT, UPDATE, or DELETE statement.

22
Block Structure for PL/SQL Subprograms
(Procedures or Functions)

• Program Comments (optional)
• Header (mandatory)
• ISAS (mandatory)
• Declaration Section
• BEGIN (mandatory)
• Executable Section
• EXCEPTION (optional)
• Exception Section
• END (mandatory)
• /

23
Block Structure for PL/SQL subprogramsComplet
ed procedure example

• CREATE OR REPLACE PROCEDURE AddDepartment
• /Procedure to add rows In the COMPANY.department
  table /
• (p_DepartmentName IN department.dnameTYPE,
• p_DepartmentNumber IN department.dnumberTYPE,
  
• p_DepartmentManager IN department.mgrssnTYPE,
• p_ManagerStartDate IN department.mgrstartdate
  TYPE) AS
• BEGIN
• INSERT INTO DEPARTMENT(dname, dnumber, mgrssn,
  mgrstartdate)
• VALUES
• (p_DepartmentName, p_DepartmentNumber,
  p_DepartmentManager, p_ManagerStartDate)
• COMMIT
• END AddDepartment
• /
•  

24
Execution of a Stored Functions

• EXEC AddDepartment ('Info Center', 43,
  '888665555', '28-JUN-2005')

25
Syntax of a Stored Functions

• CREATE OR REPLACE FUNCTION function_name
• (parameters IN datatypes)
• RETURN datatype
• ISAS
• Declaration Section
• BEGIN
• Executable Section
• RETURN variable_name
• EXCEPTION (optional)
• Exception Section
• END

26
Example of a Stored Functions

• Given the salary of an employee, calculate the
  state tax of 2.8 from the salary and return it.

27
Example of a Stored Functions

• CREATE OR REPLACE FUNCTION CalcStateTax (
• p_salary IN employee.salaryTYPE)
• RETURN NUMBER
• AS
• BEGIN
• RETURN (p_salary 0.028)
• END CalcStateTax
• /

28
Execution of a Stored Functions

• SELECT fname' 'lname AS "EMPLOYEE",
• CalcStateTax(salary) AS "STATE TAX"
• FROM employee

29
Execution of a Stored Functions

• EMPLOYEE STATE TAX
• ------------------------------- ----------
• James Borg 1540
• Jennifer Wallace 1204
• Franklin Wong 1120
• John Smith 840
• Alicia Zelaya 700
• Ramesh Narayan 1064
• Joyce English 700
• Ahmad Jabbar 700
• 8 rows selected.

30
What is a Trigger

• Similar to stored procedures and functions.
• Contains a Declaration, Executable, and Exception
  sections
• Differences
• Triggers are not executed explicitly, they are
  implicitly execute when a triggering event
  occurs. (This is called firing the trigger)
• Triggers do not accept parameters
• Triggering events are fired by DML Statements (
  INSERTs, UPDATEs, or DELETEs) against tables or
  views AND certain system events

31
Why Use Triggers

• Complex integrity constraints are not always
  possible through declarative constraints enabled
  at table creation time, such as salary may not be
  lowered.
• Auditing information, such as who updated an
  employee's salary, may be required. Remember
  triggers happen at the basic DML level.
• Triggers can signal other application that action
  needs to take place when changes are made to a
  table. Example, update employee statistics
  contained in another table.

32
Block Structure for a PL/SQL Trigger

• CREATE OR REPLACE TRIGGER trigger_name
• AFTER BEFORE INSTEAD OF a_trigger_event
• ON table_name (or view_name)
• FOR EACH ROWWHEN trigger_condition
• DECLARE (optional)
• BEGIN (mandatory)
• Executes only when trigger_condition is TRUE on
  a ROW LEVEL TRIGGER
• EXCEPTION (optional)
• Exception Section
• END (mandatory)
• NOTE a_trigger_event may be any combination of
  an INSERT, DELETE, and/or UPDATE on a table or
  view

33
Errors and Error Handling

• Errors can be classified into two types
• 1) Compile-Time errors and warnings.
• After compiling use the command
• SHOW ERRORS
• use SET ECHO ON to see statement
  numbers
• 2) Run-Time errors occur during execution
  and throw exception that can be handled by the
  program.

34
Error Handling

• When errors occur during the execution,
  control will be branched to the exception
  handling section.
• A corresponding error handler will be found
  to deal with the error.

35
Practice 1

• Use the Company database schema, write a stored
  procedure to add an employee to the employee
  table, using parameters to input the data. Use
  your name and following information to test the
  procedure.
• FNAME use your first name
• MINIT use your middle init or a blank
  space
• LNAME use your last name
• SSN make up a 9 digit number
• BDATE use your birthday (be careful of
  the date format)
• STREET make up data
• CITY Villanova
• STATE PA
• ZIP use the Villanova zip code
• SEX M or F
• SALARY 38000
• SUPERSSN 333445555
• DNO 5

36
Practice 2

• Write a function (called GetDay) that will take
  in a date as a parameter and return the actual
  name of the day for that date. Use the function
  to solve the following problem.
• Using the data in the employee table from
  Assignment 1, write an SQL statement or an
  anonymous block (containing the above function
  GetDay) that uses your first and last name in a
  where clause to access the record and returns the
  actual day of the week that you were born.
  Hint GetDay(bdate)