ORACLE PL/SQL

Identify lexical units in a PL/SQL block

Like every other programming language, PL/SQL has:

• a character set, reserved words, punctuation

• Datatypes, rigid syntax, fixed rules of usage and statement formation.

You use these basic elements of PL/SQL to represent real-world objects and operations.

A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as
follows:

• Delimiters (simple and compound symbols)

• Simple symbols consist of one character.

Identifiers, which include reserved words

• Identifiers should be descriptive.

• Avoid obscure names such as cpm. Instead, use meaningful names such as
cost_per_thousand.

Reserved Words

• Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL and so
should not be redefined.

• For example, the words BEGIN and END, which bracket the executable part of a block or
subprogram, are reserved.

DECLARE

end BOOLEAN; -- not allowed; causes compilation error

However, you can embed reserved words in an identifier, as the following example shows:

DECLARE

end_of_game BOOLEAN; -- allowed

• Reserved words are written in upper case to promote readability.

• Other PL/SQL identifiers, reserved words can be written in lower or mixed case. For a list of
reserved words, see Appendix F.

• literals

• comments

• To improve readability, you can separate lexical units by spaces.

• You must separate adjacent identifiers by a space or punctuation.

• The following line is not allowed because the reserved words END and IF are joined:
IF x > y THEN high := x; ENDIF; -- not allowed

• You cannot embed spaces in lexical units except for string literals and comments.

• For example, the following line is not allowed because the compound symbol for assignment
(:=) is split:

count : = count + 1; -- not allowed

• To show structure, you can divide lines using carriage returns and indent lines using spaces
or tabs. Compare these IF statements for readability:

IF x>y THEN max:=x;ELSE max:=y;END IF; | IF x > y THEN

| max := x;

| ELSE

| max := y;

| END IF;

Use built-in SQL functions in PL/SQL

User defined function

• It’s also known as stored function or user function.

• Are similar to procedures.

• Function always returns a value.

• Can be used as a part of an SQL expression.

Note

• Oracle SQL does not support calling of functions with Boolean parameters or returns.

• PL/SQL function is a named block that returns a value.

Functions is sorted into the type of function based on categories such as string/character, conversion,
advanced, numeric/mathematical, and date/time
Oracle / PLSQL: Functions - Listed by Category (techonthenet.com)

Example

DECLARE

Variable1 data_type := value;

variable2 data_type;

BEGIN

variable2 := ROUND(variable1, 2);

DBMS_OUTPUT.PUT_LINE(result: ' || variable2);

END;

Describe when implicit conversions take place and when explicit conversions must be dealt with

Explicit:
 • Data must be specified how the data type should be converted.

• Otherwise, SQL Server it will try to guess your intentions (implicitly).

• An explicit conversion occurs when you use the CONVERT or CAST keywords explicitly in your
query.

Implicit:

• The database engine will convert the data type automatically, a process invisible to the user.

Explicit

• You must describe the conversion in your hand.

• It uses the CAST or CONVERT functions.

• For example, a column date1 written in '21.01.2013'. it is in a varchar format according to the
provided data/table.

• A column date2 which is in '21/01/2013' format. It is a date but provided in a varchar format
as well.

• Example to compare date1 and date2 (whether equal) you must write:

select ....... where cast(date1 as date) =cast(date2 as date)

Use decision structures: IF-THEN and CASE

• Decision making statements are used

• They are in charge of executing a statement out of multiple given statements based on some
condition.

• The condition will return either true or false. Based on what the condition returns, the
associated statement is executed.

The decision making statements in PL/SQL are of two types:

• If Else statements

Case statement