What is PL/SQL?
PL/SQL stands for Procedural Language/Structured Query Language. It is a programming language that uses detailed sequential instructions to process data. A PL/SQL program combines SQL commands (such as Select and Update) with procedural commands for tasks, such as manipulating variable values, evaluating IF/THEN logic structure, and creating loop structures that repeat instructions multiple times until the condition satisfies the defined criteria. PL/SQL was expressly designed for this purpose.
The structure of a PL/SQL program
block is:
Declare
Variable declaration
Begin
Program statements
Exception
Error-handling statements
End;
PL/SQL program variables are
declared in the program’s declaration section using the data declaration syntax
shown earlier. The beginning of the declaration section is marked with the
reserved word DECLARE. You can declare multiple variables in the declaration
section. The body of a PL/SQL block consists of program statements, which can
be assigned statements, conditional statements, looping statements, and so on,
that lie between the BEGIN and EXCEPTION statements. The exception section
contains program statements for error handling. Finally, PL/SQL programs end
with the END; statement.
In a PL/SQL program block, the
DECLARE and EXCEPTION sections are optional. If there are no variables to
declare, you can omit the DECLARE section and start the program with the BEGIN
command.
Using HTML and PL/SQL in Oracle Application Express
Oracle Application Express installs
with your Oracle database and is comprised of data in tables and PL/SQL code. Whether you are running the Oracle
Application Express development environment or an application you built using
Oracle Application Express, the process is the same. Your browser sends a URL
request that is translated into the appropriate Oracle Application Express
PL/SQL call. After the database processes the PL/SQL, the results are relayed
back to your browser as HTML. This cycle happens each time you either request
or submit a page.
Specific
HTML content not handled by Oracle Application Express forms, reports, and
charts are generated using the PL/SQL region type. You can use PL/SQL to have
more control over dynamically generated HTML within a region.
htp and htf Packages:
htp
(hypertext procedures) and htf (hypertext functions) packages generate
HTML tags. These packages translate PL/SQL into HTML that is understood by a
Web browser. For instance, the htp.anchor procedure generates the HTML anchor
tag, <A>. The following PL/SQL block generates a simple HTML document:
Create or replace procedure hello AS
BEGIN
htp.htmlopen; --
generates <HTML>
htp.headopen; --
generates <HEAD>
htp.title('Hello'); --
generates <TITLE>Hello</TITLE>
htp.headclose; --
generates </HEAD>
htp.bodyopen; --
generates <BODY>
htp.header(1, 'Hello'); -- generates <H1>Hello</H1>
htp.bodyclose; --
generates </BODY>
htp.htmlclose; --
generates </HTML>
END;
Oracle provided the htp.p tag to
allow you to override any PL/SQL-HTML procedure or even a tag that did not
exist. If a developer wishes to use a new HTML tag or simply is unaware of the
PL/SQL analog to the html tag, he/she can use the htp.p procedure.
For every htp procedure that
generates HTML tags, there is a corresponding htf function with identical
parameters. The function versions do not directly generate output on your web
page. Instead, they pass their output as return values to the statements that
invoked them. See the use of these packages on page 113 of the book.
htp.p / htp.print:
Generates the specified parameter as
a string.
htp.p(‘<p>’):
Indicates that the text that comes
after the tag is to be formatted as a paragraph.
<strong>Customer:</strong>:
Renders the text they surround in
bold.
htf.escape_sc:
Escape_sc is a function that
replaces characters that have special meaning in HTML with their escape
sequence.
converts occurrence of & to
&
converts occurrence of “ to
"
converts occurrence of < to
<
converts occurrence of > to
>
To prevent XSS (Cross Site Scripting)
attacks, you must call SYS.HTF.ESCAPE_SC to prevent embedded javascript code
from being executed when you inject the string into the HTML page.
sys.htf.escape_sc:
The SYS prefix is used to signify
Oracle’s SYS schema. The HTP and HTF packages normally exist in the SYS schema
and APEX relies on them.
Cursor FOR LOOP Statement
The cursor FOR LOOP statement lets
you run a SELECT statement and then immediately loop through the rows of the
result set. This statement can use either an implicit or explicit cursor.
If you use the SELECT statement only
in the cursor FOR LOOP statement, then specify the SELECT statement inside the
cursor FOR LOOP statement, as in Example A. This form of the cursor FOR LOOP
statement uses an implicit cursor and is called an implicit cursor FOR LOOP
statement. Because the implicit cursor is internal to the statement, you cannot
reference it with the name SQL.
Example A - Implicit Cursor FOR LOOP Statement
BEGIN
FOR item IN (
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' ||
item.job_id);
END LOOP;
END;
/
Result:
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK
If you use the SELECT statement
multiple times in the same PL/SQL unit, then define an explicit cursor for it
and specify that cursor in the cursor FOR LOOP statement, as in Example B. This
form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP
statement. You can use the same explicit cursor elsewhere in the same PL/SQL
unit.
Example B - Explicit Cursor FOR LOOP Statement
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' ||
item.job_id);
END LOOP;
END;
/
Result:
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK
From page number 211 to 214 of my book
“Create Rapid Low-Code Web Applications in Oracle APEX”, you can see
how to use HTML in PL/SQL code. In this blog post, I’ll try to explain that
code.
Display Customer Information
This procedure fetches information about the selected
customer and presents it in a desirable format (as shown under) using the style
named demoCustomerInfo.
Declare
This is the parent PL/SQL block. A nested block is
also used under Display Current Orders section. Read about PL/SQL block in
What is PL/SQL at the beginning of this post.
l_customer_id
varchar2(30) := :P11_CUSTOMER_ID;
Assigns the customer ID from Page 11 to the variable
l_customer_id. This variable is used ahead in a SQL statement to fetch details
of the selected customer. In PL/SQL, := is called the assignment operator. The value of the variable that is
being assigned the new value is placed on the left side of the assignment
operator, and the value is placed on the right side of the operator.
:P11_CUSTOMER_ID is called a bind variable. Bind
variables are substitution variables that are used in place of literals. You can
use bind variables syntax anywhere in Oracle Application Express where you are
using SQL or PL/SQL to reference the session state of a specified item. For
example:
SELECT * FROM employees WHERE last_name like '%' ||
:SEARCH_STRING || '%'
In this example, the search string is a page item.
If the region type is defined as SQL Query, you can reference the value using
standard SQL bind variable syntax. Using bind variables ensures that parsed
representations of SQL queries are reused by the database, optimizing memory
usage by the server.
The use of bind variables is encouraged in APEX.
Bind variables help you protect your Oracle APEX application from SQL injection
attacks. Bind variables work in much the same way as passing data to a stored
procedure. Bind variables automatically treat all input data as “flat” data and
never mistake it for SQL code. Besides the prevention of SQL injection attacks,
there are other performance-related benefits to its use.
You declare a field item as a bind variable by
prefixing a colon character (:), like this:
:P11_CUSTOMER_OPTIONS
When using bind variable syntax, remember the
following rules:
- Bind variable names must correspond to an item name.
- Bind variable names are not case-sensitive.
- Bind variable names cannot be longer than 30 characters (that is, they must be a valid Oracle identifier).
Although page item and application item names can be
up to 255 characters, if you intend to use an application item within SQL using
bind variable syntax, the item name must be 30 characters or less.
begin
Read What is PL/SQL at the beginning of this post.
if :P11_CUSTOMER_OPTIONS = 'NEW' then
l_customer_id :=
:P18_CUSTOMER_ID;
end if;
P11_CUSTOMER_OPTIONS is a radio group item that is
created in section 6.6.6. This item has two values “Existing Customer” and “New
Customer” as shown in the following figure. In the above conditional code, if
the user opts to create a new customer, then the new customer ID is moved from
Page 18 to the variable l_customer_id (a new customer record is generated through
Page 18).
for x in (select *
from demo_customers where customer_id = l_customer_id)
loop
Initiated the FOR loop to locate and fetch the record of
the selected customer in demo_customers table.
htp.p('<div
class="demoCustomerInfo">');
The <div> tag
defines a division or a section in an HTML document. This is the opening tag
that references demoCustomerInfo class in CSS to format the following
elements. Click to see how Cascading Style Sheet is used in Oracle Application Express.
htp.p('<strong>Customer:</strong>');
Displays the label “Customer:” in bold.
htp.p('<p>');
The paragraph opening tag.
htp.p(htf.escape_sc(x.cust_first_name)
|| ' ' || htf.escape_sc(x.cust_last_name) || '<br />');
Concatenates customers’ first and last names using
the || symbol. The <br /> is an XHTML tag and inserts a single line
break.
htp.p(sys.htf.escape_sc(x.cust_street_address1)
|| '<br />');
Show customer’s first address on a new line.
if
x.cust_street_address2 is not null then
sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
It’s a condition to check whether the customer’s
second address is not null. If it’s not, print it on a new line.
htp.p(sys.htf.escape_sc(x.cust_city)
|| ', ' || x.cust_state || ' ' ||
htf.escape_sc(x.cust_postal_code));
Displays city, state and postal code data on the same
row separating each other with a comma and a blank space.
htp.p('</p>');
The paragraph end tag.
end loop;
The loop terminates here after selecting the
customer with relevant information.
htp.p('</div>');
The div tag terminates here. Following is the output
you see for the above code.
Display Products
Here you create a section on your web page to
display all products along with their prices and include an option through
which users can add products to their cart.
htp.p('<div
class="demoProducts" >');
Creates a division based on the class demoProducts.
htp.p('<strong class="demoTitle">P
R O D U C T S</strong>');
Displays the section title in bold on top of column
headings using the sub-class demoTitle.
htp.p('<table
width="100%" cellspacing="0" cellpadding="0"
border="0">
Here you are initiating to draw a table. The
<table> tag defines an HTML table. An HTML table consists of the
<table> element and one or more <tr>, <th>, and <td>
elements. The <tr> element defines a table row, the <th> element
defines a table header, and the <td> element defines a table cell.
The Width attribute specifies the width of a
table. In this instance, we’re setting the width in percent of the surrounding
element. In the style sheet, we set the area for demoProducts to 58% and for
demoCurrentOrder to 38% div.demoProducts{clear:both;float:left;width:58%;margin:16px 0 0
0;padding:0 8px 0 0;}. This division meant that the elements in the
demoProducts class would consume 58% of the overall region whereas
demoCurrentOrder elements would share 38%. If you set the table’s width to 50%,
the product section would look like the following figure. Setting 50% width
instructs the browser to use half of the demoProducts area we specified in the CSS
rule.
<thead>
<tr><th
class="left">Product</th><th>Price</th><th>Add
to Cart</th></tr>
</thead>
The <thead> tag is used to group header
content in an HTML table. The <thead> element is used in conjunction with
the <tbody> and <tfoot> elements to specify each part of a table
(header, body, footer). The <tr> tag creates a row for the column heading.
The three <th> tags specify the headings i.e. Product, Price, and Add to
Cart. The class=”left” declaration is included that points towards the CSS rule
div.demoProducts .left{text-align:left;} to align the title “Product” to
the left.
<tbody>');
The <tbody> tag is used to group the body
content in an HTML table.
for c1 in (select
product_id, '<a href="javascript:productPopup(''' || product_id ||
''');">' || sys.htf.escape_sc(product_name) || '</a>'
product_name, list_price, 'Add to Cart'
add_to_order
from
demo_product_info
where product_avail
= 'Y'
order by
product_name) loop
This FOR loop fetches Product ID, Product Name, and
List Price columns from the products table. To display Add > link in the
table, we appended a column aliased add_to_order and populated all rows with a
constant value 'Add to Cart'. For information on FOR LOOP see Cursor FOR
LOOP Statement section earlier in this post.
The <a href="javascript:productPopup('''
|| product_id || ''');">' || sys.htf.escape_sc(product_name) ||
'</a> tag defines an anchor. An anchor can be used in two ways:
1.
To create a link to another document, by using the href attribute
2.
To create a bookmark inside a document, by using the name attribute
The <a> element is usually referred to as a
link or a hyperlink. The most important attribute of the <a> element is
the href attribute, which specifies the URL of the page the link goes to. In
the above scenario, the first portion <a href="javascript:productPopup('''
|| product_id || ''');"> calls a JavaScript function named
productPopup by passing the product_id value. The second portion, ||
sys.htf.escape_sc(product_name) || '</a> concatenates product name to
the former one to display product name as a link. The function, in turn, opens
a popup to display details based on the product ID for the clicked product.
sys.htp.p('<tr><td
class="left">'||c1.product_name||'</td><td>'||trim(to_char(c1.list_price,'999G999G990D00'))||
'</td><td><a
href="f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||
c1.product_id||'"
class="demoAddtoCart">Add
></a></td></tr>');
The c1 prefix in front of column names, points
to the FOR LOOP cursor. The TRIM function in the expression trim(to_char(c1.list_price,'999G999G990D00'))
takes a character expression and returns that expression with leading and/or
trailing pad characters removed. This expression initially formats the list
price column to add a thousand separator and decimal places. Next, it converts
the numeric price value to text expression using the TO_CHAR function and
finally applies the TRIM function. The TO_CHAR function converts a DATETIME,
number, or NTEXT expression to a TEXT expression in a specified format. The
tables that follow list the elements of a number format model that we used
above and provide some examples.
Element
|
Example
|
Description
|
0
|
0999
|
Returns
leading zeros.
|
9990
|
Returns trailing zeros.
|
|
9
|
9999
|
Returns
value with the specified number of digits with a leading space if positive or
with a leading minus if negative. Leading zeros are blank, except for a zero
value, which returns a zero for the integer part of the fixed-point number.
|
D
|
99D99
|
Returns in the specified position the decimal
character, which is the current value of the NLS_NUMERIC_CHARACTER parameter.
The default is a period (.).
|
G
|
9G999
|
Returns
in the specified position the group separator (the current value of the
NLS_NUMERIC_CHARACTER parameter. The default is comma [,]). You can specify
multiple group separators in a number format model.
Use
the following SQL statement to check the current value for decimal and group
separator characters:
select
value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';
|
The expression <a href="f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||c1.product_id||'"
class="demoAddtoCart">Add ></a> creates a link
with an ADD request. The value of REQUEST is the name of the button the
user clicks or the name of the tab the user selects. For example, suppose you
have a button with the name CHANGE, and the label Apply Change. When a user
clicks the button, the value of REQUEST is CHANGE. In section 6.10.7 on page
number 117 of the book, we created the following process named Add
product to the order collection.
for
x in (select * from demo_product_info where product_id = :P12_PRODUCT_ID)
loop
apex_collection.add_member(p_collection_name
=> 'ORDER',
p_c001 => x.product_id,
p_c002 => x.product_name,
p_c003 => x.list_price,
p_c004 => 1);
end
loop;
During the
process creation, we selected Request=Expression 1 in Condition Type and entered
ADD in Expression 1. The ADD request in the above <a> tag is referencing
the same expression. When a user clicks the ADD> link on the web page, the
URL sends the ADD request to the above process along with the product’s ID. In
return, the process adds the product to the selected item list. The > is
used to show the” >” symbol after the ADD label.
end loop;
End of FOR loop.
htp.p('</tbody></table>');
Table and table body closing tags.
htp.p('</div>');
The Closing div tag.
Display Current Order
htp.p('<div
class="demoCurrentOrder" >');
Defines the <div> tag and utilizes the
demoCurrentOrder class for this section.
htp.p('<strong class="demoTitle">Current Order</strong>');
Displays section heading as follows.
Declare
This is the nested or child block. To nest a block
means to embed one or more PL/SQL blocks inside another PL/SQL block to allow
you to have better control over the program’s execution.
c number := 0; t
number := 0;
Declared two numeric counter variables and
initialized them with zero. The variable c is used to evaluate whether any
product is selected in the current order while the variable t stores the total
value.
Begin
-- loop over cart
values
for c1 in (select
c001 pid, c002 i, to_number(c003) p, count(c002) q, sum(c003) ep, 'Remove' remove
from
apex_collections
where
collection_name = 'ORDER'
group by c001,
c002, c003
order by c001)
loop
The collection enables you to temporarily capture one or
more non-scalar values. You can use collections to store rows and columns
currently in session state so they can be accessed, manipulated, or processed
during a user’s specific session. You can think of a collection as a bucket in
which you temporarily store and name rows of information.
Every collection contains a named list of data
elements (or members) which can have up to 50 character attributes (varchar2
(4000)), 5 numbers, 5 dates, 1 XML type, 1 BLOB, and 1 CLOB attribute. You
insert, update, and delete collection information using the PL/SQL API
APEX_COLLECTION.
When you create a new collection, you must give it a
name that cannot exceed 255 characters. Note that collection names are not
case-sensitive and will be converted to uppercase. Once the collection is
named, you can access the values (members of a collection) in the collection by
running a SQL query against the database view APEX_COLLECTIONS.
The APEX_COLLECTIONS view has the following
definition:
COLLECTION_NAME
NOT NULL VARCHAR2(255)
SEQ_ID NOT
NULL NUMBER
C001
VARCHAR2(4000)
C002
VARCHAR2(4000)
C003
VARCHAR2(4000)
C004
VARCHAR2(4000)
C005
VARCHAR2(4000)
...
C050
VARCHAR2(4000)
N001
NUMBER
N002
NUMBER
N003
NUMBER
N004
NUMBER
N005
NUMBER
CLOB001
CLOB
BLOB001
BLOB
XMLTYPE001
XMLTYPE
MD5_ORIGINAL
VARCHAR2(4000)
Use the APEX_COLLECTIONS view in an application just
as you would use any other table or view in an application, for example:
Select c001, c002, c003, n001, clob001
From APEX_collections
Where
collection_name = 'DEPARTMENTS'
Note that you can't read apex_collection using
external tools. A collection is related to an APEX session and not available
outside of it. However, using the following statement you can query WWV_FLOW_COLLECTION_MEMBERS$.
It is in this table that APEX stores its collection data.
The CREATE_OR_TRUNCATE_COLLECTION method creates a
new collection if the named collection does not exist. If the named collection
already exists, this method truncates it. Truncating a collection empties it,
but leaves it in place.
On page number 102 - section 6.6.7, we created a
process named Create or Truncate
Order Collection under the page rendering
section and used the following statement to create a collection named ORDER:
apex_collection.create_or_truncate_collection
(p_collection_name => 'ORDER');
In the above loop, we’re selecting records from the
same ORDER collection. Columns from apex_collections in the select statement
correspond to:
Column
|
Corresponds To
|
C001 – pid
|
Product
ID
|
C002 – i
|
Product Name
|
C003 – p
|
List
Price
|
C004
|
Quantity
|
htp.p('<div
class="sideCartItem"><a href="'||
'f?p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'||c1.pid||
'"><img
src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart"
title="Remove from cart" /></a>
The above <a> tag creates a link with a REMOVE
request. This time it uses product ID from the collection. In section 6.10.7 on
page number 117, there is a process named Remove
product from the order collection (as
shown below) where the request expression is set to REMOVE.
for
x in (select seq_id, c001 from apex_collections
where
collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
loop
apex_collection.delete_member(p_collection_name
=> 'ORDER', p_seq => x.seq_id);
end
loop;
In HTML, images are defined with the <img>
tag. The <img> tag has no closing tag. To display an image on a page, you
need to use the src attribute. Src stands for "source". The value of
the src attribute is the URL of the image you want to display.
Syntax for defining an image:
<img src="url"
alt="some_text"/>
The URL points to the location where the image is
stored. The value of IMAGE_PREFIX determines the virtual path the Web server
uses to point to the images directory distributed with Oracle Application
Express. We used “delete.gif” which is displayed in front of the product name. The
required alt attribute specifies an alternate text for an image if the image
cannot be displayed.
When a user clicks the remove link [X] in
the Current Order section, the above URL sends the REMOVE request to the above
process along with the product ID. The DELETE_MEMBER procedure deletes a specified
member from a given named collection using the p_seq => x.seq_id parameter which is the sequence ID of the collection member to be
deleted.
<a
href="javascript:productPopup('''||c1.pid||''');">'||c1.i||'</a>
The above link allows you to see product details in
the Current Order section as well and calls the popup through the JavaScript
function. c1.pid is the product ID passed on to the function. c1.i is
concatenated in the URL to display the product name as a link.
<span>'||trim(to_char(c1.p,'$999G999G999D00'))||'</span>
<span>Quantity:
'||c1.q||'</span>
<span
class="subtotal">Subtotal: '||trim(to_char(c1.ep,'$999G999G999D00'))||'</span>
The above three lines of code display price,
quantity, and sub-total of the selected product in the Current Order section as
shown below:
</div>');
The ending div tag.
c := c + 1;
This counter increments the value of c with 1 at the
end of each loop. The variable c is used to calculate the number of items selected
in the current order.
t := t + c1.ep;
Similar to the variable c, t is also incremented
with the sum of the list price (c1.ep) to calculate the total order value.
end loop;
if c > 0 then
sys.htp.p('<div
class="sideCartTotal">
<p>Items:
<span>'||c||'</span></p>
<p
class="sideCartTotal">Total:
<span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
</div>');
else
sys.htp.p('<p
class="sideCartTotal">You have no items in your current
order.</p>');
end if;
The if
c > 0 condition evaluates whether a product is selected
in the current order. A value other than zero in this variable indicates the addition of product(s). If the current order has some items added, show the
label Total: along with the value, which is stored in the variable t. If no
items are selected, show the message defined in the else section.
end;
Mark the end of the child PL/SQL block.
htp.p('</div>');
Div tag closed.
end;
Parent
PL/SQL block ended.
