Developing Effective PL/SQL

Developing Effective PL/SQL Reference Guide for Undergraduate Students This books offers a short reference tutorial for beginner programmers that intends to develop effective PL/SQL within academic context. The book is organized in 13 chapters, from most basic PL/SQL syntax to the use of BULK Binding, Forall and Returning approaches that offers the possibility to create effective and simultaneously efficient PL/SQL code. Each chapter presents some PL/SQL code extracts with proper and argumentative discussion.

Fernando Almeida, PhD. INESC TEC and University of Porto 10/27/2015

Developing Effective PL/SQL 2015 Table of Contents Acronyms....................................................................................................................................... 4 Glossary ......................................................................................................................................... 5 1. Introduction............................................................................................................................... 6 1.1 Contextualization ................................................................................................................ 6 1.2 Objectives ............................................................................................................................ 6 1.3 Book Structure..................................................................................................................... 7 2. PL/SQL Structure ....................................................................................................................... 8 3. Variable Types ........................................................................................................................... 9 4. Conditional Instructions .......................................................................................................... 12 5. Loops ....................................................................................................................................... 14 6. Select in PL/SQL ....................................................................................................................... 17 6.1 Implicit cursors .................................................................................................................. 17 6.2 Explicit cursors................................................................................................................... 18 7. Insert and Update in PL/SQL ................................................................................................... 20 8. Records .................................................................................................................................... 21 8.1 Table-based Record ........................................................................................................... 21 8.2 Programmer-defined Record ............................................................................................ 21 9. Tables ...................................................................................................................................... 23 10. Bulk Binding, Forall and Returning ........................................................................................ 24 10.1 Bulk Collect ...................................................................................................................... 24 10.2 Forall................................................................................................................................ 25 10.3 Returning ......................................................................................................................... 26 11. Subprograms ......................................................................................................................... 28 11.1 Procedures ...................................................................................................................... 28 11.2 Functions ......................................................................................................................... 29 12. Packages ................................................................................................................................ 31 12.1 Package Specification ...................................................................................................... 31 12.2 Package Body .................................................................................................................. 31 Page 2

Developing Effective PL/SQL 2015 13. Exceptions ............................................................................................................................. 33 13.1 System-defined exceptions ............................................................................................. 33 13.2 User-defined exceptions ................................................................................................. 34 Bibliography ................................................................................................................................ 35

Page 3

Developing Effective PL/SQL 2015 Acronyms ANSI - American National Standards Institute DBMS - Database Management System DML -Data Manipulation Language I/O - Input/Output LOB - Large Object ORA - Oracle SQL - Structured Query Language

Page 4

Developing Effective PL/SQL 2015 Glossary Exception - the PL/SQL programming language uses exceptions to handle errors and other exceptional events.

Mismatch - an unsuitable match or a lack of correspondence.

Oracle - object-relational database management system produced and marketed by Oracle Corporation.

PL/SQL - results as a combination of SQL along with the procedural features of programming languages. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

Page 5

Developing Effective PL/SQL 2015 1. Introduction 1.1 Contextualization According to Oracle (2015) PL/SQL (Procedural Language extensions to SQL) is designed specifically for the seamless processing of SQL statements. PL/SQL program units are stored and compiled in the database, run within the Oracle executable and inherit the robustness, security, and portability of the Oracle Database.

PL/SQL brings the following advantages: 

Modular development of programs;



It can be used to group multiple SQL queries in just one block and send it at once to the server;



Errors can be processed with exceptions handlers;



We can take advantage of the procedural capabilities of PL / SQL that are not present in SQL;



Programs and libraries can be written and reused in different environments;



PL/SQL can cooperate with Oracle application development tools such as Oracle Forms and Oracle Reports, adding procedural processing power to these tools.

In order to guide students through the elementary syntax of PL/SQL guide, a relational model is presented below.

Products (cod_product, description, unit_price, available_stock, minimal_stock) Orders (cod_order, date_order, date_delivery, cod_customer -> Customers) OrdersProducts (cod_product -> Products, cod_order -> Orders, quantity) Customers (cod_customer, name, address, zip code, country, telephone) This proposed relational model is normalized in 3NF and it is composed by four tables, which one identified by their primary keys (underlined) and the foreign keys are identified by "->" symbol.

1.2 Objectives This mini books intends to provide a brief reference guide for undergraduate students that want to learn PL/SQL in the context of their curricular units at university. The book presents the main reference PL/SQL syntax items and also presents the most important syntax instructions that should be adopted in order to develop effective PL/SQL applications. This mini book doesn't intend to be a full PL/SQL guide, but it only focus on most critical aspects of the PL/SQL language.

Page 6

Developing Effective PL/SQL 2015 1.3 Book Structure The book is organized in 13 chapters as follow:  Chapter 1 "Introduction" - gives a brief overview about PL/SQL language and organization of the book;  Chapter 2 "PL/SQL structure" - gives a brief overview about how structuring code in PL/SQL;  Chapter 3 "Variable types" - details about the main different types that a PL/SQL variable can assume;  Chapter 4 "Conditional instructions" - presents the slight difference of the "If" statement and the "Case" statement;  Chapter 5 "Loops" - presents the different loops approaches that can be adopted in PL/SQL;  Chapter 6 "Select in PL/SQL" - shows the use of "Select" SQL statement in PL/SQL and introduces the use of implicit and explicit cursors;  Chapter 7 "Insert and update in PL/SQL" - shows the use of "Insert" and "Update" statements in PL/SQL;  Chapter 8 "Records" - details the use of records and its association with fields in a Oracle table;  Chapter 9 "Tables" - introduces the use of tables defined by the programmer;  Chapter 10 "Bulk Binding, Forall and Returning" - describes the adoption of special instructions in PL/SQL that let programmer to optimize code, particularly when dealing with big tables sizes and associated structures;  Chapter 11 "Subprograms" - introduces the use of procedures and functions in PL/SQL;  Chapter 12 "Packages" - introduces the adoptions of packages in order to proper organize the code in packages;  Chapter 13 "Exceptions" - gives an overview about the use of exceptions automatically created by PL/SQL environment or declared by the programmer.

Page 7

Developing Effective PL/SQL 2015 2. PL/SQL Structure PL/SQL is a block-structured language divided and written in three logical blocks as seen in Figure 1. "BEGIN" block and "END" are compulsory, and other two blocks "DECLARE" and "EXCEPTION" are optional block. "END" is not considered a block, but only a keyword to end of PL/SQL program.

Figure 1 - PL/SQL block structure ( (PL/SQL Tutorial - Online Web Development Tutorials, 2015)

Each of these blocks have the following purposes:  Declare - variables and constants are declared and initialized within this section. It is mandatory the declaration of variables and constants before referencing them in procedural statement;  Begin - procedural statement block responsible for the implementation of the actual programming logic;  Exception - defined or predefined error conditions can be declared there. PL/SQL is recognized for handling errors in a very effective way. Errors can be rise due to wrong syntax, bad logical or not passing validation rules.

Page 8

Developing Effective PL/SQL 2015 3. Variable Types Variables in PL/SQL can be generally one of the following four types:  Scalar data types o BINARY_INTEGER - Signed integer 2,147,483,647, represented in 32 bits;

in

range

-2,147,483,648

through

o NUMBER(prec, scale) - Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0; o FLOAT - ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits); o REAL - Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits); o INTEGER - ANSI and IBM specific integer type with maximum precision of 38 decimal digits; o CHAR - Fixed-length character string with maximum size of 32,767 bytes; o VARCHAR2 - Variable-length character string with maximum size of 32,767 bytes; o BOOLEAN - stores logical values that are used in logical operations. The logical values are the Boolean values TRUE and FALSE and the value NULL; o DATE - store fixed-length datetimes, which include the time of day in seconds since midnight. The default date format is set by the Oracle initialization parameter NLS_DATE_FORMAT. For example, the default might be 'DD-MONYY', which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year, for example, 01-OCT-12.  Large Object (LOB) o BFILE - Used to store large binary objects in operating system files outside the database; o BLOB - Used to store large binary objects in the database.  Composite - Used to store values that have internal components. PL/SQL offers two kind of composite data types, collection and record. Composite variable will be seen in detail in "Chapter 8. Records";  Reference - In addition to standard variable declarations seen above, PL/SQL allows variable datatypes to match the datatypes of existing columns, rows or cursors using the %TYPE and %ROWTYPE qualifiers. This makes code maintenance much easier.

Page 9

Developing Effective PL/SQL 2015 A small example of declaring variables is given below.

DECLARE age INTEGER; status BOOLEAN; date_of_birth DATE; address VARCHAR2(150); BEGIN null; END; The program doesn't produce any output and only performs the internal declaration of four variables. Each variable has a different type, which is declared after its name.

It is also possible to declare reference variables as cited previously. For that, and considering our relational model presented in the Introduction section, we could have the declarations below.

DECLARE -- Specific columns from tables v_unit_price Products.unit_price%TYPE; customer_name Customers.name%TYPE; -- Whole record from table v_all_products_info Products%ROWTYPE; BEGIN null; END; The type of the variables is the same as declared in the Oracle database. For that we used the special syntax "%TYPE". The last declaration defines a complex variable (record) that is composed by all the attributes to the Products. For that we used the special syntax "%ROWTYPE".

It is also important to highlight that internal variables and external variables linked to the database cannot have the same name, in order to let PL/SQL compiler distinguish both situations.

Typically a PL/SQL wants to output something in the console. To write in the console output we can use the "DBMS_OUTPUT". A small example demonstrating this situation is given below.

DECLARE a integer := 50; b integer := 20; c integer; d real; BEGIN c := a + b; dbms_output.put_line ('Value of c: ' || c); d := a / b; dbms_output.put_line ('Value of d: ' || d); END; We perform two operation: one sum and one division. The result of sum is placed in the variable "c"; the division is placed in variable "d". Both variables are written in the console.

Page 10

Developing Effective PL/SQL 2015 A more complete PL/SQL program will typically make use simultaneously of scalar and reference data types. In fact, this doesn't bring any additional difficulty as it is proven in example below. However, it is important to highlight that this piece of code only works if the database is composed by just one record; if not a table type must be used (see Chapter 9.

Tables).

DECLARE v_description Products.description%TYPE; v_unit_price Products.unit_price%TYPE; VAT CONSTANT number(3,2) := 1.23; BEGIN null; END; Here we have declared three variables. The first two variables use the same type as declared in Oracle; the last one is a constant type. The number type is composed by three places, two of them reserved for digital part of the number.

Page 11

Developing Effective PL/SQL 2015 4. Conditional Instructions PL/SQL offers two conditional instructions:  IF-THEN-ELSE statement - the IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms: "IF THEN", "IF THEN ELSE" and "IF THEN ELSIF";  Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions.

The example below demonstrate the use of the three variants of "IF" statement.

DECLARE a integer := 15 b boolean; BEGIN IF (a > 10) THEN b := TRUE; END IF; END;

DECLARE a integer := 15 b boolean; BEGIN IF (a > 10) THEN b := TRUE; ELSE b := FALSE; END IF; END;

DECLARE a integer := 15; b boolean; c boolean := FALSE; BEGIN IF (a > 30) THEN c := TRUE; ELSIF (a > 20) THEN b := TRUE; ELSE b := FALSE; END IF; END; The three examples above demonstrate the slight difference of "if" statement. In the first example we present a simple if without any else statement; the second example uses the traditional "if-else" approach; the third example include the "elsif" statement that let the programmer test the value of a condition using simultaneously the else and if approach inline.

In this last example variable "C" is true only when "A" greater than 30. However if "A" is greater than 20 and lesser or equal than 30, the value of "B" is true; on the other situations the value of "B" is false.

Page 12

Developing Effective PL/SQL 2015 For the demonstration of "CASE" statement we can attend the example below.

DECLARE grade char(1) := 'A'; BEGIN CASE grade WHEN 'A' THEN DBMD_OUTPUT.PUT_LINE ('Excellent'); WHEN 'B' THEN DBMD_OUTPUT.PUT_LINE ('Very Good'); WHEN 'C' THEN DBMD_OUTPUT.PUT_LINE ('Good'); WHEN 'D' THEN DBMD_OUTPUT.PUT_LINE ('Satisfactory'); WHEN 'E' THEN DBMD_OUTPUT.PUT_LINE ('Reproved'); WHEN 'F' THEN DBMD_OUTPUT.PUT_LINE ('Without minimal grade'); ELSE DBMD_OUTPUT.PUT_LINE ('No valid grade'); END CASE; END; Here we test the value of variable "Grade". The "Case" statement let the programmer to test the content of "Grade". We test the value of "Grade" against six scenarios and we write in console the message corresponding to each situation.

PL/SQL provides also a special CASE statement called Searched Case Statement. The syntax is slightly different as shown in example below.

DECLARE salary real; v_msg varchar2(50); BEGIN salary := 2500; CASE WHEN salary < 1000 THEN v_msg := 'Low'; WHEN salary >= 1000 and salary < 2000 THEN v_msg := 'Fair'; WHEN salary >= 2000 THEN v_msg := 'High'; END CASE; DBMS_OUTPUT.PUT_LINE(v_msg); END; Here we use the "Case" approach but with numeric and operators. The variable to be tested is the "Salary". Attending to the value of "Salary", it is written in the console the appropriate message to each situation. Look that the process of written in the console is only done after the "Case" statement. We adopt this approach to make the code cleaner. Inside the "Case" statement the content of "v_msg" is updated.

Page 13

Developing Effective PL/SQL 2015 5. Loops A loop statement allows user to execute a statement or group of statements multiple times and following is the general form of a loop statement in most of the programming languages.

There are three kind of loops in PL/SQL:  Basic Loop - in this loop structure, sequence of statements is enclosed between the LOOP and END LOOP statements. At each interaction, the sequence of statements is executed and then control resumes at the top of the loop;  While Loop - repeats a statement or group of statements until a given condition is true.

It tests the condition before executing the loop body;  For Loop - execute a sequence of statements multiple times and abbreviates the control that manages the loop variable.

Typically most situations which require a loop could be written with any of the three loop constructs. However, if we don't pick the construct best-suited for that particular requirement, we could end up having to write many additional lines of code. Therefore, the resulting module would also be harder to understand and maintain.

The syntax of a basic loop in PL/SQL programming and a small example is given below.

DECLARE my_num integer := 10; BEGIN LOOP my_num := my_num + 10; EXIT WHEN my_num > 100; END LOOP; DBMS_OUTPUT.PUT_LINE(my_num); END; Here we have declared three variables. The first two variables use the same type as declared in Oracle; the last one is a constant type. The number type is composed by three places, two of them reserved for digital part of the number.

Page 14

Developing Effective PL/SQL 2015 Let's look at a WHILE LOOP example in Oracle.

DECLARE my_num integer := 10; BEGIN WHILE my_num <= 100 LOOP my_num := my_num + 10; END LOOP; DBMS_OUTPUT.PUT_LINE(my_num); END; The "While" statement test the condition in the beginning. In this situation the "While" loop is true when "my_num" is equal or lower than 100. Inside the "While" loop we update the value of "my_num" by adding 10. When the loops end, the value of "my_num" is 110. This value is written in the console.

Then an example of the FOR LOOP is given below.

DECLARE my_num integer := 10; i integer; BEGIN For i IN 1..10 LOOP my_num := my_num + i; DBMS_OUTPUT.PUT_LINE(my_num); END LOOP; END; The traditional "For" loop also tests the condition in the beginning. In this situation the variable "i" will have values between 1 and 10. Therefore the "For" loop will be executed 10 times. Inside the "For" loop the value of "my_num" is updated and written in the console.

Page 15

Developing Effective PL/SQL 2015 Finally a slight different of the FOR LOOP can be used adopting the REVERSE modifier to run the FOR LOOP in reverse order. An example of such situation is given below.

DECLARE my_num integer := 10; i integer; BEGIN For i IN REVERSE 1..10 LOOP my_num := my_num + i; DBMS_OUTPUT.PUT_LINE(my_num); END LOOP; END; Here we still have a "For" loop but it is executed in the reverse order. It starts by 10 and decreases gradually until reaches 1. Inside the "For" loop the value of "my_num" is updated and written in the console.

Page 16

Developing Effective PL/SQL 2015 6. Select in PL/SQL The Select SQL operation can be done using PL/SQL by the adoption of cursors. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. There are two types of cursors:  Implicit cursors - these are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed;  Explicit cursors - they must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

6.1 Implicit cursors When we execute a DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are:  %FOUND - returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE;  %NOTFOUND - the logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.

Otherwise, it returns FALSE;  %ISOPEN - always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement;  %ROWCOUNT - returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

The usage of implicit cursors can be seen in example below.

DECLARE num_customers integer; BEGIN Select count(cod_customer) into num_customers From Customers; DBMS_OUTPUT.PUT_LINE('Total number of customers:' || num_customers); END; Here we want to get the total number of customers that exists in the table "Customers". For that we use an implicit cursor. The number of customers is placed in the variable

Page 17

Developing Effective PL/SQL 2015 "num_customers". After that, and inside PL/SQL, we can use this variable. In this situation the "num_customers" is written in the console.

Instead of the "count" clause we could other SQL aggregator clauses like "max", "min" or "sum". However, the following situation presented in example below would only work if in the database we have only one customer. Typically, and considering this situation as an exception, the following example code is not correct.

DECLARE name_customers Customers.name%TYPE; BEGIN Select name into name_customers From Customers; DBMS_OUTPUT.PUT_LINE('Name of customers:' || name_customers); END; Here we want to extract from the "Customers" table the name of each customer. Look that the variable "name_customers" is a simple variable type and, therefore, it only must contain one customer name. Therefore, this example doesn't work if there is more than one customer in the database.

In order to resolve this issue we can adopt two approaches. The first one is to use a loop cursor; the second one is to use the BULK COLLECT INTO syntax that will be presented in Chapter 15. Bulk Binding. The second one approach is better because it increases the performance of the system. Therefore, and in the context of this book, we will only look the second approach.

6.2 Explicit cursors Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. The explicit cursor uses the following workflow process:  Declare a cursor - defines the cursor with a name and the associated SELECT statement;  Open statement - allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it;  Fetch statement - involves accessing one row at a time;  Close statement - releases the allocated memory associated to the cursor.

The example below shows the same piece of code used as previously with an implicit cursor.

DECLARE num_customers integer; Cursor c1 is Select count(cod_customer) From Customers; BEGIN Open c1; Fetch c1 into num_customers;

Page 18

Developing Effective PL/SQL 2015 Close c1; DBMS_OUTPUT.PUT_LINE('Total number of customers:' || num_customers); END; The cursor "C1" must be previously declared. This is mandatory for an explicit cursor. Explicit cursors makes code more structured and easier to maintain. The exercise counts the number of customers and puts this value in variable "num_customers" using the "Fetch" statement. It is relevant to refer that the use of explicit cursors needs to use the "open" and "close" operation. After that we can write the total number of customers in the console.

Another example is given using the same table "Customers".

DECLARE v_name Customers.name%TYPE; v_country Customers.country%TYPE Cursor c1 is Select name, country From Customers Where cod_customer = "101" BEGIN Open c1; Fetch c1 into v_name, v_country; Close c1; DBMS_OUTPUT.PUT_LINE('Name of the customer:' || v_name); DBMS_OUTPUT.PUT_LINE('Country of the customer:' || v_country); END; This example starts by declaring two variables "v_name" and "v_country". Then, and within the same block, we declare a new cursor that searches for the name and country of a given customer. After that, and inside the "Begin" block, we open, fetch and close the cursor. The name and country of the given customer is placed in "v_name" and "v_country", respectively.

Finally both fields are written in console.

Page 19

Developing Effective PL/SQL 2015 7. Insert and Update in PL/SQL Like the Select statement, the insert and update statements can also be used in PL/SQL. Its usage is very simple like it is shown in these two examples: first one is an "insert" statement and the last one is an "update" statement.

DECLARE v_name Customers.name%TYPE := 'Peter Kruft'; v_country Customers.country%TYPE := 'UK'; BEGIN Insert Into Customers (cod_customer, name, country) Values (cod_customer_sequence.nextval, v_name, v_country); END; Here we insert a new customer in the "Customers" table. For that we declare previously the name and country of the customer. Then, in the "begin" statement, we use the standard SQL "Insert" syntax. It is also relevant to highlight that the "cod_customer" is an automatic sequence number.

DECLARE v_cod_customer Customers.cod_customer%TYPE:= 101; BEGIN Update Customers Set country = 'USA' Where cod_customer = v_cod_customer; END; Here we update the country of the customer with "cod_customer" equal to "101". Then we use the standard SQL "Update" syntax to update the country of the customer to "USA". Notice that the code of customer to be updated can be easily changed in the declare section. The code keeps working on without changing anything in the "Update" statement.

Page 20

Developing Effective PL/SQL 2015 8. Records Records in PL/SQL programs are very similar in concept and structure to the rows of a database table. A record is a composite data structure, which means that it is composed of more than one element or component, each with its own value. The record as a whole does not have value of its own; instead, each individual component or field has a value. The record gives you a way to store and access these values as a group.

PL/SQL supports three different kinds of records: table-based, cursor-based, and programmerdefined. These different types of records are used in different ways and for different purposes, but all three share the same internal structure: every record is composed of one or more fields. A short description of them is provided here:  Table-based - a record based on a table's column structure;  Cursor-based - a record based on the cursor's SELECT statement. In the context of this book we won't look to this kind of cursor because typically they offer bad performance;  Programmer-defined - a record whose structure you, the programmer, get to define with a declaration statement.

8.1 Table-based Record The %ROWTYPE attribute enables a programmer to create table-based and cursor-based records. The following example illustrates the concept of table-based records.

DECLARE v_customer Customers%ROWTYPE; v_customerCode Customers.cod_customer%TYPE := 101; BEGIN Select * Into v_customer From Customers Where cod_customer = v_customerCode; DBMS_OUTPUT.PUT_LINE(v_customer.name); DBMS_OUTPUT.PUT_LINE(v_customer.country); END; Here we want to extract all information regarding a given customer. For that we declare a variable named "v_customer" that is in fact a record of fields exactly equal like declared in the "Customer" table. This is a very common and useful approach when using PL/SQL. Then the "Select" statement extract all attributes of the customer with "cod_customer" equal to "101" and all these attributes are placed in variable "v_customer" that was previously declared.

After that we can write, for example, the name and country of the customer. For that, we can access these two fields using "v_customer.name" and "v_customer.country" fields.

8.2 Programmer-defined Record PL/SQL provides a programmer-defined record type that allows programmer to define different record structures. Records consist of different fields. An example using programmerdefined records is given below.

Page 21

Developing Effective PL/SQL 2015 DECLARE Type v_virtualCustomer IS Record (login varchar(25), name varchar(50), email varchar(35)); customer1 v_virtualCustomer; BEGIN customer1.login := 'ispg111'; customer1.name := 'Carl Smith'; customer1.email := 'csmith@outbox.net'; DBMS_OUTPUT.PUT_LINE(customer1.login); DBMS_OUTPUT.PUT_LINE(customer1.name); DBMS_OUTPUT.PUT_LINE(customer1.email); END; Here we use a traditional record type that is common to see in C, C++ or Java languages. First we declare a new "Record" type that is composed by three fields: login, name and email. After that we declare a new variable called "customer1" with the "v_virtualCustomer" type that was previously declared. Then we can use the "Customer1" variable like any other traditional variable. For that, we instantiate the value of those three fields and, after that, we simply write the content of each field in the console.

Page 22

Developing Effective PL/SQL 2015 9. Tables A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogeneous elements, indexed by integers. In technical terms, it is like an array; it is like a SQL table; yet it is not precisely the same as either of those data structures. This type of table is indexed by a binary integer counter (it cannot be indexed by another type of number) whose value can be referenced using the number of the index. Notice that PL/SQL tables exist in memory only, and therefore donâ&#x20AC;&#x2122;t exist in any persistent way, disappearing after the session ends.

There are two steps in the declaration of a PL/SQL table. First, programmer must define the table structure using the TYPE statement. Second, once a table type is created, programmer then declare the actual table. A full example regarding the adoption of tables in PL/SQL is given below.

DECLARE Type NamesList IS Table of Varchar2(25) Indexed By Binary_Integer; company_name_table NamesList; i integer; BEGIN company_name_table (1) := 'Anne'; company_name_table (2) := 'Paul'; company_name_table (3) := 'Peter'; For i IN 1..company_name_table.count Loop DBMS_OUTPUT.PUT_LINE(company_name_table(i)); End Loop; END; A very useful syntax in PL/SQL is the adoption of tables, particularly when connected to a table database. However, in this chapter, we only use the easiest approach that is the creation of a simple table type for internal use. For that, we start by declaring a new type that is composed by a table of varchar2. As consequence, we will have a simple table with characters in each position of the table. In the "Begin" section we start by adding three entries to the table in the first three positions. After that, we use a "For" loop that will be executed until the total size of table (in this scenario until 3). Then we write the content of each line of the table.

Page 23

Developing Effective PL/SQL 2015 10. Bulk Binding, Forall and Returning Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine. There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk binding the whole collection to the DML statement in one operation. In order to improve the performance three statements can be used in PL/SQL:  BULK Collect - enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk binds also allow many similar DML statements to be executed with one call instead of requiring a separate call for each;  Forall - indicates to PL/SQL compiler to load all input collection before send it to SQL Engine;  Returning - increases the performance by the prompt return in column the "Insert", "Update" and "Delete" instructions. It also eliminates the additional use of a "Select" instruction.

10.1 Bulk Collect Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. A PL/SQL program that reads a dozen rows from a cursor will probably see no noticeable benefit from bulk binds.

The same goes for a program that issues five or six UPDATE statements. However, a program that reads 1000 rows from a cursor or performs that many similar UPDATE statements will most likely benefit from bulk binds. The Bulk Collect Into can be used both in implicit and explicit cursors. An example of the use of Bulk Collect Into in an implicit cursor is given below.

DECLARE Type customerType IS Table of Customers%ROWTYPE; v_customer customerType; BEGIN Select * BULK COLLECT INTO v_customer From Customers; For i in 1..v_customer.Count Loop DBMS_OUTPUT.PUT_LINE(v_customer(i).name); DBMS_OUTPUT.PUT_LINE(v_customer(i).country); End Loop; END; The "BULK COLLECT INTO" syntax is very important in PL/SQL and it will be used to extract more than just one row from the database. In the "Declare" section we start by declaring a new type and variable. Then we use the traditional SQL "Select" command, but we alter it a little by placing the "BULK COLLECT INTO" syntax. In fact, we extract all information from customers and we place its content in the "v_customer" variable. This is valid because

Page 24

Developing Effective PL/SQL 2015 "v_customer" is a table of all fields of the "Customers" table. Then, and after the "Select" statement all information is already in the "v_customer" variable. After this point we don't need to connect again to the database and all the process is made in the "offline" mode. The impact in performance can be huge in situations that we value thousands or millions of rows in the "Customers" table. Then we use a "For" loop that will execute until reach the number total of customers and we write for each customer its name and country.

Furthermore an example of the use of Bulk Collect Into in an explicit cursor is given below.

DECLARE Cursor customer_cursor IS Select * From Customers; Type customerType IS Table of Customers%ROWTYPE; v_customer customerType; BEGIN Open customer_cursor; Fetch customer_cursor BULK COLLECT INTO v_customer; Close customer_cursor; For i in 1..v_customer.Count Loop DBMS_OUTPUT.PUT_LINE(v_customer(i).name); DBMS_OUTPUT.PUT_LINE(v_customer(i).country); End Loop; END; In this example we have the same situation as before but using an explicit cursor. The explicit cursor is declared in the "Declare" section. After that we open, fetch and finally close the cursor. In the "fetch" statement we used the "BULK COLLECT INTO" syntax and all customers are immediately placed in the "v_customer". After that, and like done in the example before, we use a "For" loop to write the name and country of each customer.

10.2 Forall The FORALL statement is used to issue a series of static or dynamic DML statements. The FORALL is usually much faster than an equivalent FOR loop. In some cases processing may be up to 30 times as fast for operations such as INSERT operations. When using FORALL each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses. This requires some setup in advance of using the FORALL statement.

DECLARE Cursor customer_cursor IS Select * From Customers; Type customerType IS Table of Customers%ROWTYPE; v_customer customerType; BEGIN Open customer_cursor; Fetch customer_cursor BULK COLLECT INTO v_customer; Close customer_cursor; Forall i in 1..v_customer.Count Insert Into CustomersTemp Values v_customer(i);

Page 25

Developing Effective PL/SQL 2015 END; The "Forall" syntax is used here to place all customers information in a new "CustomerTemp" table. For that, we previously get all information from "Customers" table into the "v_customer". Notice that we are using an explicit cursor. In the last part of the code we use the "Forall" statement to insert all "v_customer" fields into the "CustomersTemp" table.

Notice that the process of insert each row in the "CustomerTemp" is done in an offline mode and the PL/SQL compiler submits only one time all the "v_customer" content.

It is also possible to user FORALL statement with Update and Delete operations. An example of such situation is given below.

DECLARE Cursor customer_cursor IS Select * From Customers; Type customerType IS Table of Customers%ROWTYPE; v_customer customerType; BEGIN Open customer_cursor; Fetch customer_cursor BULK COLLECT INTO v_customer; Close customer_cursor; Forall i in 1..v_customer.Count Update Customers Set telephone = 'N/A' Where country = 'Portugal'; END; Here we use the "Forall" statement to update the telephone number of all customers that live in Portugal. The adoption of "Forall" lets the programmer to make this process in a very efficient way. The code is very similar to previously. The "Update" statement follows the traditional SQL standard syntax.

10.3 Returning The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into a PL/SQL record variable. This eliminates the need to SELECT the row after an insert or update, or before a delete. By default, programmers can use this clause only when operating on exactly one row. However, when using bulk SQL, programmers can use the form RETURNING BULK COLLECT INTO to store the results in one or more collections.

DECLARE Type ProductsType IS Record (description Products.description%TYPE, unit_price Products.unit_price%TYPE); v_product ProductsType; v_codProduct Number := 10; BEGIN Update Products Set unit_price = unit_price * 1.1 Where cod_product = v_codProduct

Page 26

Developing Effective PL/SQL 2015 Returning description, unit_price Into v_product; DBMS_OUTPUT.PUT_LINE(v_product.description); DBMS_OUTPUT.PUT_LINE(v_product.unit_price); END; The "Returning" statement is another way to increase PL/SQL performance. In this example it is used to immediately return the description and price of the updated product. The "Update" syntax sets the new price plus 10% for the product with "cod_product" equal to "10". Then the last line of "Update" statement is responsible to immediately return the "description" and "unit_price" of the product into the "v_product" variable. Finally the description and unit price of the product is written in the console.

Page 27

Developing Effective PL/SQL 2015 11. Subprograms A subprogram is a program unit/module that performs a particular task. A subprogram can be invoked by another subprogram or program which is called the calling program. When creating a procedure or function, programmers may define parameters. There are three types of parameters that can be declared:  IN - the parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function;  OUT - the parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function;  IN OUT - the parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

11.1 Procedures A procedure is created with the CREATE OR REPLACE PROCEDURE statement. An example of a procedure using only IN parameters is given below.

CREATE OR REPLACE PROCEDURE update_stock (v_cod IN products.cod_product%TYPE, v_stock IN products.available_stock%TYPE) IS BEGIN Update Products Set available_stock = v_stock Where cod_product = v_cod; END update_stock; The "update_stock" procedure receives two parameters: "v_cod" and "v_stock". The available stock of a given product ("v_cod") is updated by the new stock also received as parameter ("v_stock").

An example of a procedure using OUT parameters is given below.

CREATE OR REPLACE PROCEDURE get_stock (v_cod IN products.cod_product%TYPE, v_stock OUT products.available_stock%TYPE) IS BEGIN Select available_stock Into v_stock From Products Where cod_product = v_cod; END get_stock; The "get_stock" procedure receives one parameter "v_cod" and returns, by using the "OUT" syntax" the available stock of a given product. Notice that the "out" syntax is used to return a value from the execution of a procedure. The value of "v_stock" is received in the procedure by calling the "Select" statement.

An example of a procedure using IN OUT parameters is given below.

Page 28

Developing Effective PL/SQL 2015 CREATE OR REPLACE PROCEDURE get_stock_UPpriceVAT (v_cod IN products.cod_product%TYPE, VAT IN Number, v_stock OUT products.available_stock%TYPE, v_price IN OUT products.available_stock%TYPE) IS BEGIN v_price := v_price + v_price * VAT; Select available_stock Into v_stock From Products Where cod_product = v_cod; Update Products Set unit_price = v_price Where cod_product = v_cod; END get_stock_UPpriceVAT; The "update_stock" procedure receives two parameters: "v_cod" and "v_stock". The available stock of a given product ("v_cod") is updated by the new stock also received as parameter ("v_stock").

11.2 Functions A PL/SQL function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous section are true for functions too. A standalone function is created using the CREATE FUNCTION statement. An example of a function is given below.

CREATE OR REPLACE FUNCTION totalProducts RETURN number IS total number(4) := 0; BEGIN Select count(*) Into total From Products; Return total; END totalProducts; The "totalProducts" function returns the total number of products in the database. The number of products is calculated by using the "Select" statement with the aggregation operator "Count(*)". The total number of products is saved in "total" variable. Finally "total" is returned by the function.

It is also possible to use recursive functions. To illustrate the concept, we use the example below.

DECLARE num integer; v_factorial integer; CREATE OR REPLACE FUNCTION factorial (x Integer) RETURN Integer IS y integer; BEGIN If x=0 Then y := 1;

Page 29

Developing Effective PL/SQL 2015 Else Y := x * factorial(x-1); End If; Return y; END ; Begin num := 5; v_factorial := factorial(num); DBMS_OUTPUT.PUT_LINE('Factorial is: ' || v_factorial); End; The idea here is to use recursive calls to function factorial in order to calculate the factorial of a given number. The code is composed by two elementary PL/SQL blocks: anonymous block and function. In the first block we define the given number, we invoke the "factorial" function and we finally write the factorial of the number in the console. The "factorial" function is responsible for the calculation of factorial process. Notice that the factorial of 5! is 5*4*3*2*1

Page 30

Developing Effective PL/SQL 2015 12. Packages A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

The packages offer the following advantages:  It can be used to store all related functions and procedures. As a consequence all these procedures or/and functions are grouped together into single unit called packages;  Packages are reliable to granting privileges like other PL/SQL object;  All functions and procedures within a package can share variables among them;  Packages support overloading to overload functions and procedures;  Packages can improve the performance by loading multiple objects into memory at once. Therefore, subsequent calls to related program doesn't require to calling physically I/O;  Packages reduce the traffic because all block execute all at once.

12.1 Package Specification The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.

CREATE PACKAGE products_info AS Procedure find_price (v_cod Products.cod_product%TYPE); END products_info; The package specification only informs the system about the procedure and/or methods that are available under the package. In this situation it was declared the "find_price" procedure that receives a product code.

12.2 Package Body The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package. The CREATE PACKAGE BODY Statement is used for creating the package body.

CREATE OR REPLACE PACKAGE BODY products_info AS Procedure find_price (v_cod Products.cod_product%TYPE) IS v_price Products.unit_price%TYPE;

Page 31

Developing Effective PL/SQL 2015 Begin Select unit_price Into v_price From Products Where cod_product = v_cod; DBMS_OUTPUT.PUT_LINE('Price is: ' || v_price); END products_info; The package body presents the content of the "find_price" procedure. The procedure receives the code of a product and finds its price in the "Products" table. Finally it shows in console the price of the product.

Page 32

Developing Effective PL/SQL 2015 13. Exceptions An error condition during a program execution is called an exception in PL/SQL. It supports programmers to catch such conditions using "Exception" block in the program and an appropriate action is taken against the error condition. There are two types of exceptions:  System-defined exceptions;  User-defined exceptions.

13.1 System-defined exceptions System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule.

There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions. The most relevant system-defined exceptions are the following:  CURSOR_ALREADY_OPEN (ORA-06511) - when programmer tries to open a cursor that is already opened;  INVALID_CURSOR (ORA-01001) - when programmer performs an invalid operation on a cursor like closing a cursor, fetch data from a cursors that is not opened;  NO_DATA_FOUND (ORA-01403) - when a Select...Into clause does not return any row from a table;  TOO_MANY_ROWS (ORA-01422) - when a programmer selects or fetch more than one row into a record or variable;  ZERO_DIVIDE (ORA-01476) - when a programmer attempts to divide a number by zero.  INVALID_NUMBER (ORA-01722) - it is raised when the conversion of a character string into a number fails because the string does not represent a valid number;  ROWTYPE_MISMATCH (ORA-06504) - it is raised when a cursor fetches value in a variable having incompatible data type.

An example using system-defined exceptions is given below.

Declare v_cod_customer Customers.cod_customer%TYPE := 101 v_name Customers.name%TYPE; Begin Select name into v_name From Customers Where cod_customer = v_cod_customer DBMS_OUTPUT.PUT_LINE('Name is: ' || v_name); Exception When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE('No such customer');

Page 33

Developing Effective PL/SQL 2015 When Others Then DBMS_OUTPUT.PUT_LINE('Error unknown'); End; This anonymous block is responsible for the presentation of the name of a given customer.

However, it can display two additional messages is the "Select" statements returns an error. If the "Select" statement doesn't return any data, it displays "No such customer"; else if exists other kind of error it writes in console "Error unknown".

13.2 User-defined exceptions PL/SQL allows programmer to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly using a RAISE statement.

Declare v_cod_customer Customers.cod_customer%TYPE := 91 v_name Customers.name%TYPE; ex_invalid_cod_customer Exception; Begin If v_cod_customer < 100 Then Raise ex_invalid_cod_customer; Else Select name into v_name From Customers Where cod_customer = v_cod_customer DBMS_OUTPUT.PUT_LINE('Name is: ' || v_name); End if; Exception When ex_invalid_cod_customer Then DBMS_OUTPUT.PUT_LINE('Invalid code of customer'); When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE('No such customer'); When Others Then DBMS_OUTPUT.PUT_LINE('Error unknown'); End; This example starts by declaring an user-defined exception called "ex_invalid_cod_customer".

Then associates the exception declared before to a specific situation, respectively when customer code is below 100. In the exception section we define our own error message according to the exception triggered. For example, the message "Invalid code of customer" will only be displayed in console if the customer code is below 100.

Page 34

Developing Effective PL/SQL 2015 Bibliography DML RETURNING INTO Clause. (n.d.). Retrieved 10 22, 2015, from https://oraclebase.com/articles/misc/dml-returning-into-clause Oracle Database 12c .

(2015).

Retrieved October 20, http://www.oracle.com/technetwork/database/features/plsql/index.html Oracle Tutorial. (n.d.).

Retrieved http://www.techonthenet.com/oracle/

10

2015,

from

21,

2015,

from

TeachOnNet:

PL/SQL Packages. (n.d.).

Retrieved 10 22, http://www.way2tutorial.com/plsql/plsql_packages.php

2015,

from

Way2Tutorial:

PL/SQL Tutorial - Online Web Development Tutorials. (2015). Retrieved October 20, 2015, from http://www.way2tutorial.com/plsql/plsql_block_structure.php Schrag, R. (n.d.). How Bulk Binds in PL/SQL Boost Performance. Retrieved 10 21, 2015, from http://www.dbspecialists.com/files/presentations/bulk_binds.html Vasilaky, W. (n.d.).

PL/SQL Tables. http://comsci.liu.edu/~vasilaky/db2/tables.htm

Retrieved

10

22,

2015,

from

Page 35