Oracle10g PL/SQL Programming V1.0

(1)

Oracle10g PL/SQL

Programming

(2)

Legal Notice

Copyrights

No part of this publication may be reproduced, distributed or displayed in any form or by any means, or stored in a database, retrieval system or other media without the prior written permission of SkillBuilders, Inc.

Trademarks

The product/service names mentioned herein are manufacturer/publisher trademarks and are used only for purposes of identification.

Microsoft, Microsoft Windows 95, Microsoft Windows 98, Microsoft Windows NT, and Microsoft Windows XP are registered trademarks of Microsoft Corporation. Netscape, Netscape Navigator, and the Netscape logo are registered trademarks of Netscape Communications Corporation in the United States and other countries. Oracle, Oracle8i, Oracle9i and Oracle10g are registered trademarks of Oracle Corporation.

All other brand, product and service names are the trademarks of their respective companies or owners as mentioned herein.

Disclaimer

Information within this publication is subject to change without notice. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. The author and SkillBuilders, Inc. make no representation or warranty with respect to the contents herein and specifically disclaims any implied warranties of fitness for any particular purpose. SkillBuilders, Inc. disclaims all liability for any direct, indirect, incidental or consequential, special or exemplary damages resulting from the use of the information in this document or from the use of any products described in this document.

(3)

Copyright  2006 SkillBuilders V1.0

3 days

Description This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.

Learning how to create efficient, scalable PL/SQL programs is an important objective of this course. To meet that objective, the course includes instruction on such things as the proper use of bind variables, bulk processing, pipelining, benchmarking different formulations of a routine and profiling PL/SQL programs to identify the code that should be tuned.

Hands-on workshops constitute approximately 50% of the class. As with all of our courses, this class is highly customizable to your specific training requirements.

Audience Developers and Analysts. Database Administrators who know a programming language will also benefit.

Prerequisites You must possess strong programming skills to benefit from this class. (We will not teach you the basics of programming such as logic flow and conditional logic. We will, however, teach you how to do it efficiently in PL/SQL.) Experience with a procedural language (e.g. Java, C or COBOL), SQL and SQL*Plus is required.

Next Courses Advanced PL/SQL Programming, Advanced Queries for Oracle10g Databases, Oracle10g SQL Tuning for Developers and DBA’s, Oracle10g Database Administration

Objectives After successfully completing this course, you will be able to:

• Code efficient, scalable PL/SQL programs that include common programming constructs such as data-typing, variable assignment, flow control, cursor handling, bulk processing, array processing and error handling

• Create PL/SQL stored procedures that accept and return values or sets of values • Manage stored procedure dependencies and privileges

• Create PL/SQL functions, including powerful table functions and pipelined table functions

• Create database triggers for auditing, complex business rule support, simple replication and more

• Create and maintain simple Oracle packages

• Use the UTL_FILE package to read and write to operating system files. • Use the UTL_MAIL package to send email from an Oracle database • Use the DBMS_PROFILER package to profile and tune PL/SQL programs • Do simple benchmarking of PL/SQL code with the DBMS_UTITLITY package

(4)

What is PL/SQL? ...1.3

Why Use PL/SQL? ...1.4

PL/SQL Program Structure...1.6

Anonymous Blocks...1.7

Compile Errors...1.8

Output to SQL*Plus...1.9

Procedures...1.10

Procedure Compile Errors ...1.11

Functions...1.13

Packages...1.14

Data Dictionary...1.16

Triggers...1.17

Tools for Development ...1.19

Working in SQL*Plus...1.20

Introduction to PL/SQL Workshops ...1.21

2. Language Fundamentals... 2.1

Lesson Objectives ...2.2

Statements ...2.3

PL/SQL Symbols ...2.4

Quoting Mechanism...2.5

Common PL/SQL Datatypes ...2.6

Declaring Variables… ...2.8

%TYPE ...2.11

PL/SQL Records ...2.12

Records: %ROWTYPE...2.13

Programmer Defined Records ...2.14

Language Fundamentals Workshop A...2.17

Variable Scope ...2.18

Nested Blocks ...2.19

Functions...2.20

Regular Expressions ...2.21

IF Statement ...2.23

CASE ...2.24

Simple CASE...2.25

Searched CASE...2.26

Simple Loops ...2.27

Nested Loops ...2.28

Numeric FOR Loop ...2.29

PL/SQL Arrays ...2.30

(5)

Simple Array Example...2.31

Array Methods ...2.32

Language Fundamentals Workshop B ...2.34

3. PL/SQL and SQL: Basics ... 3.1

Lesson Objectives ...3.2

SELECT INTO Statement ...3.3

Implicit Cursor Loops ...3.5

Basics Workshop A (Cursor Loop) ...3.6

DML in PL/SQL ...3.7

Cursor Attributes...3.8

Embedding DDL...3.9

Basics Workshop B (NDS) ...3.10

4. PL/SQL and SQL: Cursors ... 4.1

Lesson Objectives ...4.2

What is a Cursor?...4.3

Cursor Processing ...4.4

Cursor Attributes...4.7

Cursors Workshop A ...4.8

Dynamic Cursors ...4.9

Dynamic Cursor Example...4.10

Variable Scope & Cursors ...4.11

Problem Example...4.12

Cursor Parameters...4.13

Cursor Record ...4.14

Cursor FOR LOOP ...4.15

Cursor FOR LOOP Example ...4.16

Cursors Optional Workshop B...4.18

Referencing the Current Row ...4.19

FOR UPDATE Example...4.20

Cursors Optional Workshop C...4.23

5. PL/SQL and SQL: Bulk Processing ... 5.1

Lesson Objectives ...5.2

Bulk Processing ...5.3

Bulk Collect ...5.4

Bulk Collect: Multiple Rows ...5.5

Bulk Collect: Multiple Arrays ...5.6

Bulk Collect: Array of Records ...5.7

Bulk Collect: FETCH with LIMIT ...5.8

Bulk Processing Workshop A ...5.9

(6)

Summary ...5.18

Bulk Processing Workshop B ...5.19

6. Procedures ... 6.1

Lesson Objectives ...6.2

What is a Procedure? ...6.3

Abbreviated Syntax...6.4

Simple Example ...6.5

Review: Compile Errors ...6.6

Compiler Warnings…...6.7

Procedure Signatures ...6.11

Calling Procedures ...6.12

Procedure Synonyms ...6.13

Referencing Parameters by Name...6.14

Returning Sets: REF CURSOR ...6.15

Dropping Procedures ...6.16

Dependencies ...6.17

ALTER COMPILE ...6.18

Privileges ...6.19

Execute Privilege ...6.20

The Data Dictionary...6.21

Procedures Workshop ...6.22

7. Functions... 7.1

Lesson Objectives ...7.2

What is a Function? ...7.3

Abbreviated Syntax...7.4

Simple Example ...7.5

Calling Functions...7.6

The Data Dictionary...7.7

Functions Workshop A ...7.8

Table Functions...7.9

Building a Table Function ...7.10

Using a Table Function...7.12

Pipelined Functions...7.14

Pipelined Example ...7.15

Test Data Generator ...7.16

Functions Workshop B ...7.19

(7)

8. Error Handling... 8.1

Lesson Objectives ...8.2

Types of Errors ...8.3

Runtime Errors...8.4

Exception Concepts ...8.5

Predefined Named Exceptions...8.8

Syntax ...8.9

Handling Exceptions...8.10

Recovering from Errors ...8.11

Logging Exceptions… ...8.12

Debugging with FORMAT_ERROR_BACKTRACE ...8.14

RAISE_APPLICATION_ERROR ...8.15

RAE Example ...8.16

Error Handling Workshop ...8.17

Raising Exceptions ...8.18

Raising User Exceptions ...8.19

EXCEPTION_INIT ...8.21

Summary ...8.23

9. Packages ... 9.1

Lesson Objectives ...9.2

Concepts...9.3

Package Benefits...9.4

Package Benefits Diagram...9.5

Package Contents...9.6

Package Specification Syntax ...9.7

Package Specification Example...9.8

Package Body Syntax ...9.9

Package Body Example ...9.10

Privileges ...9.11

Calling Packaged Objects ...9.12

Initialization Code...9.13

Session Variables...9.14

Package Body Variables ...9.15

Packages Workshop A ...9.16

Package Overloading ...9.17

Overloading Example ...9.18

Packages Workshop B ...9.19

Compiling Packages ...9.20

DROP Package ...9.21

Packages Workshop C ...9.22

Supplied Packages ...9.23

Summary ...9.24

© 2006 SkillBuilders, Inc.

V1.0

(8)

Trigger Execution ...10.5

Create Trigger Syntax ...10.6

Row Trigger ...10.7

:OLD Reference Variable ...10.8

:NEW Reference Variable ...10.9

Trigger Attributes ...10.11

Audit Trigger ...10.13

Derived Value Trigger ...10.14

Restrictions ...10.15

Security Privileges ...10.16

Triggers Workshop A ...10.17

Table Trigger ...10.19

Triggers Workshop B...10.20

Autonomous Transactions ...10.21

Autonomous Trigger...10.22

Triggers Workshop C...10.23

INSTEAD OF Triggers...10.24

Triggers Workshop D ...10.26

11. File I/O Using UTL_FILE ... 11.1

Lesson Objectives ...11.2

UTL_FILE Concepts ...11.3

Setup ...11.4

Unix File Permissions ...11.5

Opening Files ...11.6

Closing Files ...11.8

Reading Files ...11.10

Reading Files Example ...11.11

Writing Files ...11.14

Writing Files - PUT ...11.15

Writing Files - PUT_LINE ...11.16

Writing Files - NEW_LINE...11.17

Writing Files - PUTF ...11.18

Writing Files Example ...11.19

UTL_FILE Workshop...11.20

12. Using DBMS_JOB ... 12.1

Lesson Objectives ...12.2

What is DBMS_JOB? ...12.3

(9)

© 2006 SkillBuilders, Inc.

V1.0

Setup ...12.4

DBMS_JOB.SUBMIT...12.5

SUBMIT Example ...12.6

Running a Job ...12.7

Cleaning Up Broken Jobs ...12.8

Removing a Job ...12.9

Changing Job Parameters...12.10

Viewing Job Information ...12.11

Using DBMS_JOB Workshop...12.12

13. Profile and Tune... 13.1

Lesson Objectives ...13.2

Introduction to Tuning PL/SQL...13.3

DBMS_PROFILER ...13.4

Profiler Setup ...13.5

Using Profiler...13.6

Testing ...13.7

Reporting ...13.8

Benchmarking ...13.9

Profile & Tune PLSQL Workshop ...13.10

14. Miscellaneous 10g Enhancements... 14.1

Lesson Objectives ...14.2

Performance: Compiler…...14.3

Optimization Comparison…...14.7

Native Compilation Review...14.9

Native Compilation: New Stuff ...14.10

Conditional Compilation...14.11

Introducing UTL_MAIL...14.13

UTL_MAIL SET UP ...14.14

UTL_MAIL EXAMPLE...14.15

Nested Table Enhancements… ...14.16

New and Revised Packages ...14.19

Lesson Summary...14.20

(10)

SKILLBUILDERS

Lesson 1

Introduction to PL/SQL

A programmer’s introduction to the

what, why, when and where of

(11)

1.2

Lesson Objectives

¾ What is PL/SQL?

¾ What is it good for?

¾ Basic structure of a PL/SQL program

¾ Simple examples of common PL/SQL objects

¾ Anonymous block

¾ Procedure ¾ Function ¾ Package ¾ Trigger

¾ Tips for working in SQL*Plus

In-depth lessons dedicated to these subjects later in this

(12)

What is PL/SQL?

¾ PL/SQL = Procedural Language extensions for SQL

¾ Proprietary language for Oracle database

¾ 3

rd

_{generation procedural language}

¾ variable definition, assignment ¾ conditional processing

¾ IF and CASE ¾ looping constructs ¾ error handling

¾ Seamless integration of SQL, SQL functions

PL/SQL is a procedural language for the ORACLE database. PL/SQL stands for Procedural Language extensions to SQL. It is a proprietary language; i.e. a PL/SQL program will not run on a SQL Server database.

As a 3rd _{generation language, it provides many of the standard capabilities you would expect}

including:

¾ Variable definition and assignment

¾ Conditional processing (IF and CASE statements) ¾ Loop constructs

¾ Error handing

It provides seamless integration (embedding) of SQL and SQL functions.

(13)

1.4

Why Use PL/SQL?...

¾ Efficient data manipulation

¾

Good for lots of SQL, limited procedural code

¾

Tight integration with SQL

¾ Embed in PL/SQL statements ¾ Lots of work done automatically

¾Open / close queries and cursors ¾

Compatible datatypes

¾ No conversions

¾ Not great for lots of complex procedural code

¾

However, consider Native Compilation

Because of the tight integration of SQL and PL/SQL, PL/SQL is very good at data manipulation. So if you are coding a routine that contains lots of SELECT, UPDATE, DELETE and INSERT statements, with relatively little procedural code, PL/SQL is a good choice. In this course you will see how PL/SQL does many things automatically for you; e.g. open and close cursors.

Conversely, if you writing complex procedural code that will be repeatedly executed, Java or C might be a better choice. However, with the PL/SQL Native Compilation feature, PL/SQL is becoming a consideration even for computationally intensive routines.

(14)

...Why Use PL/SQL?

¾ Simple language

¾

Learn quickly

¾

Only need SQL*Plus to develop & deploy

¾ Portable within Oracle database

¾

No code change to migrate to another Oracle

server

¾ Even on different platform

¾ Callable from any client

¾

Java, .NET, COBOL

¾

Anyone who can connect to DB can call

PL/SQL is relatively simple to learn and you’ll only need SQL*Plus to develop, test and deploy PL/SQL programs.

PL/SQL programs are portable to any Oracle database running on any platform. Rarely, if ever, will code changes be required. An exception would be if specific OS paths or commands are hard-coded within a program.

PL/SQL programs are callable from any type of client. If the client can connect to the database, it can call a PL/SQL procedure or function – and that procedure can return a value or result set to the caller.

(15)

PL/SQL Program Structure

¾ PL/SQL is a block-structured language

¾ 3 possible blocks

¾ Only BEGIN block required

DECLARE

variables; constants; cursors;

BEGIN PL/SQL and SQL statements; EXCEPTION exception handlers; END; DECLARE

variables; constants; cursors;

BEGIN PL/SQL and SQL statements; EXCEPTION exception handlers; END; Declarative Executable Error Handling Terminate each statement or declaration with semi-colon

PL/SQL is a block-structured language. Each PL/SQL program consists of up to 3 sections, or blocks. The declarative block contains all declarations for variables, constants and cursors. The BEGIN block contains the main body of PL/SQL and SQL statements; it is the only required section. Finally, the exception block is where all errors (exceptions in PL/SQL) can be trapped and resolved, i.e. “handled”.

(16)

Anonymous Blocks

¾ Execute block from SQL*Plus

SQL> @copycust 3 SQL> declare

2 v_custno number := &1 ; 3 begin

4 INSERT INTO cust_history 5 SELECT *

6 FROM customer

7 WHERE cust_no = v_custno; 8 end;

9 /

old 2: v_custno number := &1 ; new 2: v_custno number := 3 ; PL/SQL procedure successfully completed. SQL> @copycust 3

SQL> declare

2 v_custno number := &1 ; 3 begin

4 INSERT INTO cust_history 5 SELECT *

6 FROM customer

7 WHERE cust_no = v_custno; 8 end;

9 /

old 2: v_custno number := &1 ; new 2: v_custno number := 3 ; PL/SQL procedure successfully completed.

Copy customer to the history table

Don’t forget the slash

Seamless integration of SQL

Anonymous Blocks are called anonymous because they are not stored on the database – thus they do not have a name. They are also sometimes referred to as unnamed blocks. However, as this example illustrates, they can be stored in an operating system file (“COPYCUST.SQL”) Anonymous blocks are usually executed under SQL*PLUS to:

¾ update table data ¾ produce reports ¾ build database objects

Typically, the code for an anonymous block is placed in a .SQL script file. Note that we follow our anonymous block with a slash (/) on a line by itself. When we run the script from SQL*Plus (with the “at” sign), the anonymous block is read into the SQL*Plus buffer and scanned for SQL*Plus substitution variables. The slash tells SQL*Plus to send the contents of the SQL*Plus buffer to the server for execution.

Note that the SQL*Plus buffer can hold one and only one SQL command or PL/SQL block.

I have used a SQL*Plus substitution variable in place of the hard coded customer number. Thus, in this case, this PL/SQL block must be executed from SQL*Plus. Only SQL*Plus understands SQL*Plus substitution variables!

SQL*Plus Tips

Optionally, use SET ECHO OFF to suppress the display of the anonymous block when executed from the SQL*Plus command prompt.

Optionally, use SET VERIFY OFF to suppress the display of the “old” and “new” substitution messages.

Supplemental Notes

Note that the example shown here is for educational purposes only – specifically to illustrate the block structure of a PL/SQL program. The SQL statement within the BEGIN block could more easily be executed without being coded within the confines of a PL/SQL block.

(17)

1.8

Compile Errors

SQL> @copycust 6

INSRET INTO cust_history *

ERROR at line 4:

ORA-06550: line 4, column 10:

PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

:= . ( @ % ;

PLS-00103: Encountered the symbol "END" SQL> @copycust 6

INSRET INTO cust_history *

ERROR at line 4:

PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

:= . ( @ % ;

PLS-00103: Encountered the symbol "END"

“*” will be somewhere near clause that caused

error

While the error message has identified the correct line (line 4 contains INSERT spelled incorrectly), note the misleading error message.

Later we will see that compile errors in triggers, stored procedures, and stored functions are not automatically displayed by SQL*Plus. You must specifically display them yourself with the SQL*Plus SHOW ERRORS command.

(18)

Output to SQL*Plus

¾ Use DBMS_OUTPUT.PUT_LINE to display

text on screen

SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('Hello World') ; 3 end ; 4 / Hello World

PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('Hello World') ; 3 end ; 4 / Hello World

PL/SQL procedure successfully completed.

PL/SQL may display output to SQL*Plus by using the Oracle supplied package procedure called DBMS_OUTPUT.PUT_LINE. This procedure displays text on the output device (console) in SQL*Plus.

In order for the output from DBMS_OUTPUT.PUT_LINE to be seen, the SET SERVEROUTPUT on SQL*Plus command must be executed. You may want to consider putting this command in your LOGIN.SQL file as this setting reverts to the default (off) when the session ends.

(19)

Procedures

SQL> @procedure1 SQL> create procedure p1 as 2 begin 3 dbms_output.put_line('Hello World'); 4 end; 5 / Procedure created. SQL> exec p1 Hello World

PL/SQL procedure successfully completed. SQL> @procedure1 SQL> create procedure p1 as 2 begin 3 dbms_output.put_line('Hello World'); 4 end; 5 / Procedure created. SQL> exec p1 Hello World

Procedure is now compiled and stored in database

Call the procedure with the SQL*Plus EXECUTE command

Procedures and functions are callable subprograms which are also compiled and stored permanently in the Oracle server. (The main difference between procedures and functions is that stored functions must return a value to the caller. Otherwise, they are basically the same. You will learn much more about this later in this course.)

In the example shown above, I coded a simple stored procedure in file “PROCEDURE1.SQL”. The procedure is created with the CREATE PROCEDURE statement (refer to the Oracle9i SQL

Reference for more information on this statement; you will learn a lot more about this statement

later in this course). When I run the file, it compiles and stores the stored procedure in the database. Note that the name of the procedure is “p1”; the name of the OS file that contains the source code is “PROCEDURE1.SQL”. I use the SQL*Plus EXECUTE command (“exec p1”) to call the stored procedure.

(20)

Procedure Compile Errors

SQL> drop procedure p1; Procedure dropped. SQL> @procedure1 SQL> create procedure p1 as 2 begin 3 dbms_output.put_line('Hello World') 4 end; 5 /

Warning: Procedure created with compilation errors. SQL> show errors

Errors for PROCEDURE P1: LINE/COL ERROR

--- ---4/1 PLS-00103: Encountered the symbol "END" when expecting SQL> drop procedure p1; Procedure dropped. SQL> @procedure1 SQL> create procedure p1 as 2 begin 3 dbms_output.put_line('Hello World') 4 end; 5 /

Warning: Procedure created with compilation errors. SQL> show errors

Errors for PROCEDURE P1: LINE/COL ERROR

--- ---4/1 PLS-00103: Encountered the symbol "END" when expecting

Drop procedure before trying to recreate, or use “or

replace” on create

Use the SQL*Plus SHOW ERRORS

command

Compile errors generated by the CREATE PROCEDURE (and CREATE FUNCTION, CREATE TRIGGER) statements are not automatically displayed on the SQL*Plus screen. These errors are stored in a data dictionary view called USER_ERRORS. They can be displayed by querying this view or, as shown in this slide, by using the SQL*Plus SHOW ERRORS command. The complete text of the error message is:

LINE/COL ERROR

---

---4/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ;

The symbol ";" was substituted for "END" to continue.

The problem is a missing semi-colon at the end of line 3.

(21)

Note that instead of using DROP PROCEDURE you can add the “OR REPLACE” option to the CREATE PROCEDURE command:

SQL> @p1

SQL> create or replace procedure p1 as 2 begin

3 dbms_output.put_line('Hello World'); 4 end;

5 /

Procedure created.

The DROP statement can be used to drop most Oracle procedural objects, e.g. DROP FUNCTION, DROP TRIGGER, DROP PACKAGE, DROP PACKAGE BODY etc.

(22)

Functions

SQL> @function1

SQL> create or replace function f1 2 return char 3 as 4 begin 5 return ('test'); 6 end; 7 / Function created.

SQL> select f1() from dual; F1()

---test

SQL> @function1

SQL> create or replace function f1 2 return char 3 as 4 begin 5 return ('test'); 6 end; 7 / Function created.

SQL> select f1() from dual; F1() ---test Must identify datatype function will return

Call function from an SQL or PL/SQL

statement

A PL/SQL function is very similar to a procedure; it is just that the function always returns a value to the caller and is invoked semantically different from the procedure. Whereas the procedure invocation is a statement in itself, the function is called within the context of another PL/SQL or SQL statement.

(23)

1.14

Packages...

¾ Package is a group of PL/SQL objects

SQL> create or replace package p1 as

2 function f1 return char; 3 procedure proc1;

4 end; 5 /

Package created.

SQL> create or replace package p1 as 2 function f1 return char; 3 procedure proc1;

4 end; 5 /

Package created.

SQL> create or replace package body p1 as 2 function f1 return char

3 as 4 begin 5 return ('test'); 6 end; 7 8 procedure proc1 as 9 begin 10 dbms_output.put_line('Hello World'); 11 end; 12 end; 13 /

SQL> create or replace package body p1 as 2 function f1 return char

3 as 4 begin 5 return ('test'); 6 end; 7 8 procedure proc1 as 9 begin 10 dbms_output.put_line('Hello World'); 11 end; 12 end; 13 /

A package is a group of PL/SQL objects often including procedures and functions. There are many benefits of packaging, which we will discuss in a subsequent lesson dedicated to packages. For now, it is just important to understand that PL/SQL objects can be packaged – as opposed to standalone objects.

(24)

...Packages

¾ Qualify packaged objects with package name

SQL> select p1.f1 from dual;

F1

---test

SQL> select p1.f1 from dual; F1

---test

(25)

1.16

Data Dictionary

¾ Dictionary records existence of PL/SQL

objects

SQL> select object_name, object_type from user_objects 2 where object_type in ('PROCEDURE', 'FUNCTION'); OBJECT_NAME OBJECT_TYPE

--- ---F1 FUNCTION

P1 PROCEDURE

SQL> select object_name, object_type from user_objects 2 where object_type in ('PROCEDURE', 'FUNCTION'); OBJECT_NAME OBJECT_TYPE

--- ---F1 FUNCTION

P1 PROCEDURE

The existence of PL/SQL objects (procedures, functions, packages, etc) is recorded in the data dictionary. USER_OBJECTS contains one row for each object.

(26)

Triggers...

¾ Code tied to a table

¾ Executes automatically when DML executes

SQL> @trigger1

SQL> create trigger customer_name

2 before update or insert on customer 3 for each row

4 begin

5 /* convert character values to upper case */ 6 :new.lastname := upper( :new.lastname ); 7 :new.firstname := upper( :new.firstname ); 8 dbms_output.put_line('trigger fired'); 9 end;

10 /

Trigger created. SQL> @trigger1

SQL> create trigger customer_name

2 before update or insert on customer 3 for each row

4 begin

5 /* convert character values to upper case */ 6 :new.lastname := upper( :new.lastname ); 7 :new.firstname := upper( :new.firstname ); 8 dbms_output.put_line('trigger fired'); 9 end; 10 / Trigger created. Trigger tied to CUSTOMER table

Use the UPPER function to convert characters inserted into the database

Triggers are, usually, code associated with a table and are executed automatically when an appropriate SQL statement is issued against the table. There are also triggers which are associated with system events such as Startup and Shutdown or DDL such as CREATE and DROP statements. Other triggers can be created which are associated with user events such as Logon or Logoff. Triggers are compiled and stored permanently in the Oracle server. Triggers are often used to:

¾ Enforce complex business and/or integrity rules ¾ Audit modifications to a table

¾ Derive column values ¾ Maintain mirror tables

This trigger, called “customer_name”, will execute whenever an INSERT or UPDATE statement is executed on the DAVE.CUSTOMER table (the schema of the compiler is used for the table, unless qualified or a synonym exists).

(27)

1.18

...Triggers

SQL> insert into customer

2 (cust_no, firstname, lastname) 3 values

4 (3423, 'dave', 'anderson'); trigger fired

1 row created.

SQL> select firstname, lastname 2 from customer

3 where cust_no = 3423; FIRSTNAME LASTNAME

--- ---DAVE ANDERSON

SQL> insert into customer

2 (cust_no, firstname, lastname) 3 values

4 (3423, 'dave', 'anderson'); trigger fired

1 row created.

SQL> select firstname, lastname 2 from customer 3 where cust_no = 3423; FIRSTNAME LASTNAME --- ---DAVE ANDERSON Verification that trigger executed Characters are converted to upper case

We see that the PUT_LINE procedure displayed the text “trigger fired” when the INSERT statement executed. This is purely diagnostics. And we see that the trigger did indeed work; the characters have been converted to upper case characters.

(28)

Tools for Development

¾ SQL*Plus is a common tool

¾ Included with all Oracle database products

¾ Procedure Builder

¾ Sophisticated IDE GUI tool ¾ An extra cost product

¾ Sophisticated debugging features ¾ Breakpoints

¾ stepping through code ¾ variable examination, etc.

¾ TOAD

¾ Developer and DBA tool

SQL*Plus is the traditional tool for PL/SQL development as it is included with all versions of the Oracle database. You may choose to use a more robust development tool, like Procedure Builder, which provides a graphical development environment. This product has to be purchased separately but may be worth the extra cost as it provides many sophisticated debugging features and a better overall working environment for your development efforts.

There are many third party tools also available such as TOAD from Quest Software. Free limited feature versions of this product are available at www.toadsoft.com.

(29)

1.20

Working in SQL*Plus

¾ SET ECHO ON

¾ Show compiled source on your screen

¾ SET SERVEROUTPUT ON

¾ PUT_LINE calls will be displayed after program returns control to SQL*Plus

¾ Put in LOGIN.SQL

¾ SHOW ERRORS

¾ Error messages will refer to a line and column number ¾ Oracle Error messages manual is available to help

Debugging your PL/SQL programs can be tedious if you are uncertain of where the error messages are pointing to. The simplest way to see the errors is to SET ECHO ON prior to the compile of your source. You will have the program source echoed on your screen with line numbers. If there are any error messages, it will be simple to ascertain where the error occurred. (You can also get the source line number from your USER_SOURCE library. You cannot just count lines in your original source file because the PL/SQL compiler does not count blank lines and some comment lines.)

I recommend that you put the SET ECHO ON and SERT SERVEROUTPUT commands in your LOGIN.SQL file. This file, when placed in your working directory, will be automatically executed when you start SQL*Plus. Refer to the sample LOGIN.SQL file supplied with this course for and example.

For anonymous blocks, the error will be displayed when you try to execute the code. For procedures, functions, packages and triggers, remember to use the SHOW ERRORS command to display the error messages.

(30)

Workshops

¾ Hello World

¾ Setup

Workshop – Hello World

1. No course is complete without the infamous “Hello World!” application. So let’s create an

anonymous PL/SQL block to print the string “Hello World!” to the screen. Code the PL/SQL

block in a script, and test it by running the script.

2. Convert the anonymous block into a stored procedure. Compile and test the procedure. 3. Convert the stored procedure into a function. Compile and test the function.

4. Drop your standalone versions of your procedure and function. Package and test the packaged versions.

Workshop – Setup

¾ Run the supplied scripts DDL.SQL and INSERT.SQL to create a series of test tables that will be needed as you progress through the rest of the course.

(31)

SKILLBUILDERS

Lesson 2

Language Fundamentals

A programmer’s introduction to the

fundamental language elements in

(32)

Lesson Objectives

¾ Statements

¾ Symbols

¾ Datatypes

¾ Variable declaration

and assignment

¾ Declaring PL/SQL

Records

¾ Variable scope

¾ Nested Blocks

¾ IF

¾ CASE

¾ Looping constructs

¾ Introduction to Arrays

To some extent, this is a reference chapter. You’ll see relatively large lists of statements, datatypes and symbols. I recommend that you do not attempt to remember each one as you progress through this chapter. We’ll incorporate them into examples in the remainder of the course.

However, do use this lesson to acquire a solid grasp of the basic format of the IF, CASE, loop statements, and a good understanding of arrays. But here again, you’ll see these statements used throughout the rest of the course.

(33)

Statements

¾ CASE ¾ := ¾ IF ¾ LOOPs ¾ EXIT ¾ TYPE ¾ FORALL ¾ OPEN FETCH CLOSE ¾ CURSOR ¾ procedure_call; ¾ EXECUTE IMMEDIATE ¾ RETURN ¾ RAISE ¾ NULL ¾ GOTO ¾ SELECT INTO ¾ INSERT ¾ DELETE ¾ UPDATE ¾ LOCK TABLE ¾ MERGE ¾ CASE ¾ ROLLBACK ¾ SAVEPOINT ¾ SET TRANSACTION ¾ AUTONOMOUS_ TRANSACTION ¾ EXCEPTION_INIT ¾ RESTRICT_ REFERENCES

PL/SQL

SQL

Pragmas

Mini-Workshop Mini-Workshop

Shown here is a list of commonly used statements that can be used within a PL/SQL program. Refer to the Oracle9i PL/SQL User's Guide and Reference for a complete list and description of each statement.

Throughout the remainder of this course you will learn the purpose of and see examples of many of these statements.

You may be wondering, “where are the rest of the SQL statements?” DDL statements such as CREATE, GRANT, DROP etc. Well, these are DDL statements and cannot be coded directly within PL/SQL. Use the EXECUTE IMMEDIATE statement to embed DDL statements.

Mini-Workshop

Access chapter 13 of the Oracle9i PL/SQL User's Guide and Reference (R2) and review the examples of the following LOOP statements:

¾ basic_loop_statement ¾ for_loop_statement ¾ cursor_for_loop_statement

(34)

PL/SQL Symbols

Symbol

Purpose

+ - * / Addition, Subtraction, Multiplication, Division

:= Assignment operator

= Equal to relation

>, < Greater than/less than relation

<= >= Less than/greater than or equal to <> != ~= ^= Not equal operators

; Statement terminator

, Item separator

” Quoted identifier

' Character string identifier -- Single line comment.

/* */ Beginning / ending of multi-line comment

List continues List continues

PL/SQL symbols continued:

Symbol Purpose

. Component selector

( ) Used to control precedence. 5* (2+2)

% Prefix for PL/SQL attributes. emp.name%TYPE

.. Range operator for loops. FOR x in 1..5 LOOP

@ Used to reference remote databases.

select * from emp@hq;

: Used to prefix a host variable.

=> The “arrow”. Use in procedure calls to assign value to a parameter.

|| Concatenation operator.

<< >> Label.

Refer to chapter 2 of the Oracle9i PL/SQL User’s Guide and Reference for complete details on the PL/SQL symbols (see “delimiters”).

(35)

2.5

Quoting Mechanism

¾ Like SQL, PL/SQL supports a new technique

for denoting quotes

LINUX> declare

2 v1 varchar2(100); 3 begin

4 v1 := q'[Peg's birthday present]'; 5 insert into t values(v1);

6 end; 7 /

PL/SQL procedure successfully completed. LINUX> declare

2 v1 varchar2(100); 3 begin

4 v1 := q'[Peg's birthday present]'; 5 insert into t values(v1);

6 end; 7 /

PL/SQL procedure successfully completed._{LINUX> select * from t;} C1

---Peg's birthday present LINUX> select * from t; C1

---Peg's birthday present

10g

Oracle10g introduces a new technique for identifying enclosing quotes (delimiters) in text literals. It is supported in SQL statements and PL/SQL statements. This makes coding strings that contain single quotation marks cleaner and less error-prone.

Use the letter “q” (not case-sensitive) followed by the open quote and the quote delimiter character. The Oracle10g SQL Reference states that the quote delimiter character can be any character; however, I find it easiest to use a character that is not used in the literal itself.

If the quote delimiter character is [, {, <, or (, then the closing delimiter must be ], }, > or ), respectively. Otherwise use the same exact character as the opening and closing quote delimiter character.

Refer to the section on “Literals” in the Oracle10g SQL Reference manual for more information on what Oracle calls the “alternative quoting mechanism”.

(36)

Common PL/SQL Datatypes

¾ number[(precision,scale)]

¾ char[(length)]

¾ varchar2(length)

¾ date

¾ timestamp

¾ timestamp with time [local]

zone

¾ boolean

¾ ref cursor

¾ exception

¾ record

¾ table

¾ raw(length)

¾ long raw

¾ long

¾ blob

¾ clob

¾ nclob

¾ bfile

¾ rowid

PL/SQL datatypes define the format of a variable, constant or parameter (parameters are used to pass values to and from a procedure or function).

The slide contains a list of commonly used PL/SQL datatypes. In addition to the examples throughout the remainder of this course, refer to Chapter 3 of the PL/SQL User’s Guide and

Reference for a complete list and description of the datatypes.

Here is a brief description of the datatypes listed here:

¾ NUMBER. Specify number of digits (precision) and number of decimal places (scale) if you want to store fixed point numbers. Omit precision and scale for floating point numbers. NUMBER has many subtypes such as DECIMAL and INTEGER; refer to the Oracle documentation for more information.

¾ CHAR is fixed length character data. The default length of a CHAR variable is 1, the maximum length is 32767. The database character set determines the internal representation.

¾ VARCHAR2 is variable length character data. Maximum length is 32767. For better performance, Oracle pre-allocates 2000 bytes of storage for all VARCHAR2 definitions less than 2000 bytes (it eliminates “reallocations as different sized strings are allocated to it”, Tom Kyte, May 2004, http://asktom.oracle.com.)

¾ DATE holds a date/time value.

¾ TIMESTAMP contains a date/time value with fractional microseconds.

(37)

¾ BOOLEAN variables contains TRUE, FALSE, or NULL and are use in conditional logic, e.g. CASE and IF statements. e.g. IF VALID_ORDER THEN….

¾ EXCEPTION is used to define a user-defined exception name, for example “customer_not_found”. Refer to the “Error Handling” lesson for more information.

¾ REF CURSOR is a pointer to a memory cursor. Useful for returning sets of rows to callers / clients.

¾ RECORD defines a composite type; a collection of other scalar or composite types.

¾ TABLE defines a PL/SQL unbounded array, officially called an “index-by table” or “associative array”. Refer the examples later in this lesson for more information.

¾ RAW and LONG RAW are binary data. LONG RAW has been deprecated in favor of BLOB. ¾ BLOB is best to use for large binary objects.

¾ LONG and CLOB are large character data objects, better to use newer CLOB. ¾ NCLOB is national character set long character object.

¾ BFILE is an external binary file. ¾ ROWID is a pointer to a table row.

(38)

Declaring Variables…

¾ Specify the following:

¾ name ¾ datatype

¾ nullability (optional) ¾ initial value (optional)

SQL> declare

2 v_lastname varchar2(20) not null := 'SMITH'; 3 begin

4 If V_Lastname = 'SMITH' then null; end if; 5 end;

6 /

SQL> declare

2 v_lastname varchar2(20) not null := 'SMITH'; 3 begin

4 If V_Lastname = 'SMITH' then null; end if; 5 end;

6 /

NULL is the no-op statement NULL is the no-op

statement Variable name is

not case sensitive Variable name is

not case sensitive

Comparison is case sensitive Comparison is case sensitive Initial value Initial value

Variables are declared in the DECLARE section of the program. Required are name and datatype. The name, like all Oracle names, must follow these rules:

¾ 1 – 30 alpha-numeric characters ¾ 1st_{character must be alphabetic}

¾ Valid special characters are _, $ and # ¾ Avoid reserved words such as SELECT

In this example, V_LASTNAME is defined with the NOT NULL clause, so it can not contain a null value. An exception is raised if an initial value is not assigned (see the next page for an example). Therefore we must assign the variable an initial value ('SMITH‘ in this example). This is done with the assignment operator (:=) or the DEFAULT keyword.

“NULL” is the PL/SQL no-operation statement. I use it here (see line 4) simply because the BEGIN block is required, yet I do not want it to do anything – I simply wanted to demonstrate the declaration and use of a variable.

(39)

SQL> declare

2 x number not null; 3 begin

4 null; 5 end; 6 /

x number not null; *

ERROR at line 2:

PLS-00218: a variable declared NOT NULL must have an initialization assignment

(40)

…Declaring Variables

SQL> declare

2 v_hiredate1 date;

3 v_hiredate2 date := sysdate; 4 v_total_sales number(8,2); 5 v_total_value1 number(8,2) := 0; 6 v_total_value2 number(8,2) default 0; 7 v_order_valid boolean := false;

8 v_lastname varchar2(20) not null := 'Smith'; 9 c_co_name constant varchar2(20) := 'SkillBuilders'; 10 begin

11 null; 12 end; 13 /

PL/SQL procedure successfully completed. SQL> declare

2 v_hiredate1 date;

3 v_hiredate2 date := sysdate; 4 v_total_sales number(8,2); 5 v_total_value1 number(8,2) := 0; 6 v_total_value2 number(8,2) default 0; 7 v_order_valid boolean := false;

8 v_lastname varchar2(20) not null := 'Smith'; 9 c_co_name constant varchar2(20) := 'SkillBuilders'; 10 begin

11 null; 12 end; 13 /

The examples define the following variables:

1. V_HIREDATE1 as a DATE object. The default “value” is NULL. 2. V_HIREDATE2 as a DATE initialized to the current date in SYSDATE.

3. V_TOTAL_SALES as a NUMBER that can hold 8 total digits with 2 digits after the decimal. Will accept range of values from -999999.99 to 999999.99. The default “value” in this variable is NULL.

4. V_TOTAL_VALUE1 as a NUMBER that can hold 8 total digits with 2 digits after the decimal. The default value is 0.

5. V_TOTAL_VALUE2 as a NUMBER that can hold 8 total digits with 2 digits after the decimal. The default value is also 0.

6. V_ORDER_VALID is a BOOLEAN initialized to FALSE. It can be used in CASE or IF statements, e.g. IF v_order_valid THEN …

7. V_LASTNAME is a variable length character field up to 20 characters that cannot be NULL and is initialized to 'SMITH'.

8. C_CO_NAME is a CONSTANT. Constants have their values defined when the variable is defined, and the value not be changed (an exception occurs if an attempt is made to change the value).

(41)

%TYPE

SQL> declare 2 v1 customer.lastname%type; 3 begin

4 select lastname into v1 5 from customer 6 where cust_no = 1; 7 dbms_output.put_line(v1); 8 end; 9 / Jones

SQL> declare

2 v1 customer.lastname%type; 3 begin

4 select lastname into v1 5 from customer 6 where cust_no = 1; 7 dbms_output.put_line(v1); 8 end; 9 / Jones

Datatype variable using table column Datatype variable using table column

Use SELECT INTO to retrieve 1 row Use SELECT INTO

to retrieve 1 row

%TYPE is a great way to datatype a variable. Oracle will use the datatype of the table column, variable or cursor coded in the %TYPE specification. (You’ll learn more about cursors later.) It is flexible; if the datatype changes, the new datatype will be picked up when the code is recompiled –

without a source code change.

In this example variable V1 gets the same exact datatype as the LASTNAME column of the CUSTOMER table.

This example includes the SELECT INTO statement (a form of implicit cursor). Some form of SELECT is required to access values in a database table (i.e. you cannot just compare to a table value in an IF or CASE statement without first selecting the data). You’ll learn more about SELECT INTO and other means of retrieving data later in this course.

(42)

PL/SQL Records

¾ A record is a collection of related fields

¾ Similar structure to table row

¾ 4 ways to define records

¾

Table-based

¾ table-name%ROWTYPE ¾

Programmer-defined

¾

Cursor-based

¾ cursor-name%ROWTYPE

¾

Implicit with cursor FOR LOOP

These are discussed later

A PL/SQL record provides a way to deal with separate, but related, fields (variables) as a single unit. A record often has a similar structure to a row in a database table.

There are four ways to define PL/SQL records. We will see examples of table-based and programmer-defined records in the following pages.

See the supplied script DEFINING_PLSQL_RECORDS.SQL for examples of the four techniques for declaring variables.

(43)

Records: %ROWTYPE

¾ Define with

%ROWTYPE

¾ Record

matches row

¾ 9i supports

record-based

DML

SQL> DECLARE 2 cust_rec customer%rowtype; 3 BEGIN 4 cust_rec.cust_no := 234; 5 cust_rec.lastname := 'Anderson'; 6 cust_rec.firstname := 'Dave'; 7 INSERT INTO customer

8 VALUES cust_rec; 9 END;

10 /

PL/SQL procedure successfully completed. SQL> DECLARE 2 cust_rec customer%rowtype; 3 BEGIN 4 cust_rec.cust_no := 234; 5 cust_rec.lastname := 'Anderson'; 6 cust_rec.firstname := 'Dave'; 7 INSERT INTO customer

8 VALUES cust_rec; 9 END;

10 /

A table-based record is defined with the %ROWTYPE attribute and creates a records that matches the structure of the table row. i.e., the record has the same number of fields as columns in the table and the fields have the same name and datatype.

A PL/SQL record defined with %ROWTYPE:

¾ contains the same number of variables as columns in the table ¾ all datatypes match

¾ field names are the same as column names

As we see in this example, the fields in the record are referenced with “dot notation”, for example “cust_rec.lastname”. Then we can use the entire record in the INSERT (and UPDATE) statements. As of Oracle9i, a table-based record is a convenient structure for DML operations. Note that the VALUES clause in the INSERT statement references the record – eliminating the need to code each variable. The lack of parenthesis is correct syntax when a record is used.

(44)

Programmer Defined Records...

¾ Define with the TYPE IS RECORD statement

¾ Each field explicitly defined

¾ Two steps

¾ Define record type with TYPE IS RECORD ¾ Define instance of record using type

TYPE type_name IS RECORD

(field1 datatype1 {NOT NULL} [:= expr1], field2 datatype2 {NOT NULL} [:= expr2] ...);

record_name type_name;

TYPE type_name IS RECORD

(field1 datatype1 {NOT NULL} [:= expr1], field2 datatype2 {NOT NULL} [:= expr2] ...);

record_name type_name;

A programmer defined PL/SQL record is a record where each field is explicitly defined by the programmer. You must first define a record type. Then, you must declare a variable of that type.

The datatype of each field can be:

¾ Scalar types (NUMBER, VARCHAR2, DATE, etc.)

¾ %TYPE, %ROWTYPE or another programmer defined record (yes, Oracle supports nested records).

¾ Oracle objects (e.g. defined with CREATE TYPE object-name AS OBJECT) ¾ PL/SQL array (more on arrays later in this lesson).

¾ REF CURSOR, i.e. a pointer to a cursor. (more on REF CURSORS later in this lesson).

Parameter descriptions:

type_name Name of record type

field Name of field (variable) in record

datatype Datatype of field in record There is no limit on the number of fields within the record.

(45)

2.15

...Programmer Defined

Records...

CREATE OR REPLACE PACKAGE custpack AS TYPE cust_info_t IS RECORD

(cust_no NUMBER,

first_purchase DATE := SYSDATE); END;

/

CREATE OR REPLACE FUNCTION create_cust RETURN custpack.cust_info_t

IS

cust_rec custpack.cust_info_t; BEGIN

SELECT s1.NEXTVAL INTO cust_rec.cust_no FROM dual; RETURN(cust_rec);

END;

/ Example continues. . .

CREATE OR REPLACE PACKAGE custpack AS TYPE cust_info_t IS RECORD

(cust_no NUMBER,

first_purchase DATE := SYSDATE); END;

/

CREATE OR REPLACE FUNCTION create_cust RETURN custpack.cust_info_t

IS

cust_rec custpack.cust_info_t; BEGIN

SELECT s1.NEXTVAL INTO cust_rec.cust_no FROM dual; RETURN(cust_rec); END; / Example continues. . . Package contains a record type Package contains a record type Function returns a record Function returns a record

This example shows that a programmer-defined record type can be packaged. Packaging the record makes the record global, i.e. accessible to any user with execute privilege on the package. Next, we see that a function can return a record – see the RETURN(cust_rec) statement. (Later you will learn how to return sets of records!)

Here, I have packaged a record type called CUST_INFO_T. Then, I use that type in a function called CREATE_CUST. The function returns a record. Note the reference to the TYPE within the function requires the prefix of the package name, as in “CUSTPACK.CUST_INFO_T”.

This example requires that a sequence called S1 has been created, for example: CREATE SEQUENCE s1;

(46)

...Programmer Defined Records

¾ Define record based on packaged TYPE

¾ Function call returns record

SQL> declare 2 cust_rec custpack.cust_info_t; 3 begin 4 cust_rec := create_cust(); 5 dbms_output.put_line(cust_rec.cust_no); 6 end; 7 / 1

PL/SQL procedure successfully completed. SQL> declare 2 cust_rec custpack.cust_info_t; 3 begin 4 cust_rec := create_cust(); 5 dbms_output.put_line(cust_rec.cust_no); 6 end; 7 / 1

Define record Define record

Call function Call function

Here again we see the definition of a record based on a packaged TYPE. We also see the call to the function CREATE_CUST, where CREATE_CUST returns a record.

(47)

Language Fundamentals

Workshop A

¾ PL/SQL Records

Workshop – PL/SQL Records

1. Code a function that accepts a customer number and returns a customer record populated with information about the customer. Use the SQL statement “SELECT * INTO record FROM CUSTOMER WHERE cust_no = parameter” to retrieve the customer row.

2. Code an anonymous block that calls the function and uses the PUT_LINE procedure to display one or more customer attributes.

(48)

Variable Scope

¾ Variables persist in block they are declared

¾

And any embedded sub-blocks

¾ Cannot be referenced in outer block

¾

Can use packages to create “global” or “session”

variables if needed

¾ Easy to pass values

¾

Procedures can have IN and OUT parameters

The scope of a variable is where it can be referenced (i.e. used or seen). When a variable is declared, it is accessible throughout the PL/SQL block – and any embedded sub-blocks. A variable cannot be reference in:

¾ An outer (enclosing) block

¾ A called procedure or function (To accomplish this, use either use packaged variables or pass the procedure or function a value in the call. You will learn both techniques later in the course.)

¾ A calling procedure or function (To return a value from a procedure, use OUT type variables. Functions, by design, always return a value)

(Using packages, we can create session (global) variables. This will be discussed in the lesson on packages later in this course.)

(49)

Nested Blocks

SQL> declare 2 v_custno NUMBER := 100; 3 begin 4 dbms_output.put_line(v_custno) ; 5 declare

6 v_state CHAR(2):= 'NY' ; 7 begin 8 dbms_output.put_line 9 (v_custno || v_state) ; 10 end ; 11 end ; 12 / 100 100NY

PL/SQL procedure successfully completed. SQL> declare

2 v_custno NUMBER := 100;

3 begin

4 dbms_output.put_line(v_custno) ; 5 declare

6 v_state CHAR(2):= 'NY' ; 7 begin 8 dbms_output.put_line 9 (v_custno || v_state) ; 10 end ; 11 end ; 12 / 100 100NY

Both variables can be referenced in the inner block Both variables can be referenced in the inner block

A PL/SQL variable can be referenced in blocks nested within the block in which the variable is defined unless the nested block contains a variable of the same name; then the local version of the variable is referenced.

In the example, there are two blocks. In the first, we declare a variable, V_CUSTNO. In the second, we declare another variable, V_STATE. In the inner block, we can reference the V_CUSTNO variable. Take note that the V_STATE variable cannot be referenced outside the inner block. For instance, the following code will generate a compile error:

declare

v_custno NUMBER := 100;

begin

dbms_output.put_line(v_custno) ;

declare

v_state CHAR(2):= ‘NY’ ;

begin

dbms_output.put_line (v_custno || v_state) ;

end ;

dbms_output.put_line(v_state) ;

end ;

ERROR at line 10:

PLS-00201: identifier 'V_STATE' must be declared ORA-06550: line 10, column 4:

PL/SQL: Statement ignored generates error

(50)

Functions

¾ Most SQL functions can be used in PL/SQL

statements

¾ Specific PL/SQL error-reporting functions

¾ Aggregate and analytic functions only in SQL

¾ AVG, MIN, MAX, LEAD, LAG

7 if upper(v1) = 'ANDERSON' then 8 null;

9 end if;

7 if upper(v1) = 'ANDERSON' then 8 null;

9 end if;

7 v_msg varchar2(1020) default sqlerrm; 8 v_code number default sqlcode; 7 v_msg varchar2(1020) default sqlerrm; 8 v_code number default sqlcode;

More examples in the Error Handling

lesson More examples in the Error Handling

lesson

Scalar SQL functions such as UPPER, LOWER, INITCAP and SUBSTR can be used in PL/SQL statements. However, the aggregate functions (AVG, MIN, MAX) and analytic functions (LEAD, LAG) are not permitted in PL/SQL statements. Of course, the aggregate and analytic functions can be used in SQL statements embedded in PL/SQL.

Oracle also supplies error-related functions SQLERRM and SQLCODE for use in PL/SQL statements only. This slide shows an excerpt from a DECLARE block where we use these functions to initialize local variables. SQLERRM contains the text of the last error message; SQLCODE contains the Oracle error code. You will learn more about the use of these functions in the lesson on error handling later in this course.

(51)

2.21

Regular Expressions

¾ Like SQL, PL/SQL supports regular

expressions

LINUX> declare

2 v1 varchar2(20) := 'abcdefg'; 3 begin

4 if regexp_like(v1, '.*c*') then null; end if; 5 v1 := regexp_replace(v1, '.*c*', 'a');

6 dbms_output.put_line(v1); 7 end;

8 / aa

PL/SQL procedure successfully completed. LINUX> declare

2 v1 varchar2(20) := 'abcdefg'; 3 begin

4 if regexp_like(v1, '.*c*') then null; end if; 5 v1 := regexp_replace(v1, '.*c*', 'a');

6 dbms_output.put_line(v1); 7 end;

8 / aa

10g

Like the Oracle 10g SQL compiler, the 10g PL/SQL compiler supports regular expressions.

Oracle’s implementation is IEEE POSIX and Unicode Consortium compliant. Oracle10g provides many new operators such as:

¾ * - Matches zero or more occurrences ¾ + - Matches one or more occurrences ¾ ? - Matches zero or one occurrence ¾ ^ - Anchors to the beginning of the line ¾ $ - Anchors to the end of line

Oracle 10g Release 2 adds support for Perl expressions, including, but not limited to: ¾ \d – Match a digit character

¾ \D – Match a non-digit character ¾ \w – Match a word character ¾ \W – Match a non-word character

(52)

2 lastname varchar2(50); 3 begin

4 if regexp_like(lastname, '\W') then null; 5 end if;

6* end; SYSTEM@orcl> /

Oracle10g provides a new condition called REGEXP_LIKE.

Finally, 10g provides new functions REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR. These functions are similar to the 9i counterparts INSTR, REPLACE and SUBSTR – but they support regular expression operators.

Refer to the Oracle10g PL/SQL User's Guide and Reference for more information about using regular expressions in PL/SQL.

Refer to the supplied script plsql_regular_expressions.sql for an example of regular expressions in PL/SQL.

(53)

IF Statement

IF condition THEN action(s); END IF; IF condition THEN action(s); END IF; IF condition THEN action(s); ELSE action(s); END IF; IF condition THEN action(s); ELSE action(s); END IF; IF condition THEN action(s);

ELSIF condition THEN

action(s);

action(s); [ELSE action(s);] END IF; IF condition THEN action(s);

action(s);

[ELSE

action(s);]

END IF;

The IF statement (and CASE statement) can be used for conditional processing. The IF construct must be terminated with END IF and followed by a semi-colon. You can optionally use ELSE to provide what to do if the condition is not met.

You may nest IF statements. For example: IF V_sales_amt = 100 THEN … ELSE IF V_sales_amt < 200 THEN … ELSE … ENDIF; ENDIF:

The use of ELSIF allows you to check multiple conditions within one construct.

0 Caution: ELSIF is spelled “ELSIF”, not “ELSEIF”, and not “ELSE IF”. Believe it or not! There is no limit to the number of ELSIF conditions that can be included in a single construct.

(54)

CASE

¾ Simple CASE

¾ Searched CASE

CASE expression WHEN this1 THEN that1

WHEN this2 THEN that2 . . . [ ELSE that ]

CASE expression WHEN this1 THEN that1

WHEN this2 THEN that2 . . . [ ELSE that ]

CASE WHEN condition1 THEN return-value1 WHEN condition2 THEN return-value2 . . .

[ ELSE return-value ]

CASE WHEN condition1 THEN return-value1 WHEN condition2 THEN return-value2 . . .

[ ELSE return-value ]

Exception raised if no condition true and ELSE

not coded

Oracle9i introduced PL/SQL support for a CASE statement and CASE expression (8i introduced support for SQL-level CASE). Oracle supports two flavors of CASE, simple and searched.

¾ The simple case expression tests for an equal condition on the supplied value or expression. The first WHEN value that is equal causes Oracle to return the corresponding THEN value. If none of the WHEN values match the supplied expression, the ELSE value is returned. If the ELSE is not coded, NULL is returned.

¾ The searched case (as seen in the previous example) allows multiple comparison expressions (<, >, <=, >=, BETWEEN, LIKE, IN, IS NULL, etc.). The first TRUE expression causes Oracle to return the corresponding THEN value. If none of the WHEN values match the supplied expression, the ELSE value is returned. If the ELSE is not coded, a CASE not found exception is returned. The WHEN clauses are evaluated sequentially. The 1st _{TRUE WHEN causes the associated}

statement(s) to be executed; The CASE statement then ends (execution continues after the END CASE clause). If none of the WHEN expressions is true the ELSE statement (if any) will execute.

The CASE statement raises a CASE_NOT_FOUND exception if an ELSE clause is not provided and none of the WHEN’s are TRUE.

Only one THEN statement (or ELSE statement) is executed for each CASE statement. There is no “fall-through” as in the C language ‘switch’ statement.

CASE is limited to 128 WHEN/THEN pairs (255 total values). This limit can be overcome by nesting CASE within CASE.

(55)

Simple CASE

<<salary_test>> CASE v_sal WHEN 12 THEN dbms_output.put_line('Salary is '||v_sal); v_sal := v_sal * 1.2 ; dbms_output.put_line('Salary is '||v_sal); WHEN 14 THEN dbms_output.put_line('Salary is '||v_sal); v_sal := v_sal * 1.15 ; dbms_output.put_line('Salary is '||v_sal); ELSE v_sal := v_sal * 1.1 ; END CASE salary_test;

Here is an example of a simple CASE statement. Notes: ¾ The label is optional but provides good documentation.

¾ Each THEN can have any number of statements, each terminated with a semi-colon. ¾ Only the 1st TRUE THEN is executed. Control is transferred to the END CASE after the 1st

TRUE THEN is executed.

¾ If the ELSE is not provided and none of the THEN’s are TRUE, a CASE_NOT_FOUND exception is raised and control is automatically transferred to the EXCEPTION block, if coded.

Restriction: The case-operand and the when-operands can be any datatype except BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

(56)

Searched CASE

create or replace procedure searched_case (p1 in number) is

v_switch char(1); BEGIN

v_switch := CASE

WHEN p1 = 1 THEN 'A' WHEN p1 = 2 THEN 'B' WHEN p1 = 3 THEN 'C' END;

dbms_output.put_line(v_switch); END;

create or replace procedure searched_case (p1 in number) is

v_switch char(1); BEGIN

v_switch := CASE

WHEN p1 = 1 THEN 'A' WHEN p1 = 2 THEN 'B' WHEN p1 = 3 THEN 'C' END;

dbms_output.put_line(v_switch); END;

Here is an example of a Searched CASE expression. Note the absence of a CASE selector and the use of Boolean expressions in each WHEN clause.

(57)

2.27

Simple Loops

LOOP

EXIT [WHEN <condition>] ;

END LOOP;

LOOP

EXIT [WHEN <condition>] ; END LOOP; SQL> declare 2 counter number := 0; 3 begin 4 LOOP 5 counter := counter + 1; 6 dbms_output.put_line(counter); 7 EXIT WHEN counter = 5;

8 END LOOP; 9 end; 10 / 1 2 3 4 5

PL/SQL procedure successfully completed. SQL> declare 2 counter number := 0; 3 begin 4 LOOP 5 counter := counter + 1; 6 dbms_output.put_line(counter); 7 EXIT WHEN counter = 5;

8 END LOOP; 9 end; 10 / 1 2 3 4 5

Use the EXIT statement to exit

loop Use the EXIT statement to exit

loop

Initialize counter! Initialize counter!

A simple loop repeats the logic contained within the LOOP / END LOOP construct until an unconditional EXIT statement is executed or a condition specified by the EXIT WHEN clause is met. Be careful; an infinite loop will occur if the EXIT statement is not executed.

PL/SQL also offers a “WHILE” loop: WHILE condition LOOP

statement(s); END LOOP;

The WHILE loop differs from a simple loop in that it checks the condition first before entering the loop and then continues to execute until the condition is met. When the WHILE condition is met, the loop exits. In other words, the WHILE loop repeats a sequence of statements until the controlling condition is no longer TRUE.