Instructor's Notes
(If applicable)
- Lesson 1:
PL/SQL Programming for Oracle 10g: Introduction
A programmer’s introduction to the what, why, when and where of PL/SQL for Oracle 10g including:
- 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
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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.
- Note that Oracle client-side products such as Oracle Forms also support PL/SQL.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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”.
- Each declaration or statement must end in a semicolon.
Other Text:
(Examples or comments displayed on slide, if any).
DECLARE
variables; constants; cursors;
BEGIN
PL/SQL and SQL statements;
EXCEPTION
exception handlers;
END;
Terminate each statement or declaration with semi-colon
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
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.
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
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:
:= . ( @ % ;
ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "END"
“*” will be somewhere near clause that caused error
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line('Hello World') ;
3 end ;
4 /
Hello World
PL/SQL procedure successfully completed.
Instructor's Notes
(If applicable)
- 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 Oracle10g 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.
Other Text:
(Examples or comments displayed on slide, if any).
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.
Procedure is now compiled and stored in database
Call the procedure with the SQL*Plus EXECUTE command
Instructor's Notes
(If applicable)
- 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.
- Notes continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
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
Instructor's Notes
(If applicable)
- Supplemental Notes
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
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
Instructor's Notes
(If applicable)
- 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.
- Refer to the supplied script PACKAGE1.SQL for a working copy of the code shown here.
Other Text:
(Examples or comments displayed on slide, if any).
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 /
Instructor's Notes
(If applicable)
- A packaged object is referenced by its package name, as shown above.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> select p1.f1 from dual;
F1
----------------------------------
test
Instructor's Notes
(If applicable)
- The existence of PL/SQL objects (procedures, functions, packages, etc) is recorded in the data dictionary. USER_OBJECTS contains one row for each object.
Other Text:
(Examples or comments displayed on slide, if any).
SQL> select object_name, object_type from user_objects
2 where object_type in ('PROCEDURE', 'FUNCTION');
OBJECT_NAME OBJECT_TYPE
-------------------------------------------
F1 FUNCTION
P1 PROCEDURE
Instructor's Notes
(If applicable)
- 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).
Other Text:
(Examples or comments displayed on slide, if any).
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
Instructor's Notes
(If applicable)
- 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.
- Refer to the supplied script TRIGGER1.SQL for a copy of the code shown in this example.
Other Text:
(Examples or comments displayed on slide, if any).
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
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Workshop – Hello World
- 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.
- Convert the anonymous block into a stored procedure. Compile and test the procedure.
- Convert the stored procedure into a function. Compile and test the function.
- 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.
Other Text:
(Examples or comments displayed on slide, if any).
