PL/SQL Language Fundamentals

Fundamentals of PL/SQL for novices?This article summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

 


Table of contents[Show]


 

PL/SQL Character Set

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:

Type

Characters

Letters

A–Z, a–z

Digits

0—9

Symbols

~!@#$%*( )_−+=|:;"'< >,^.?/

Whitespace

Space, tab, newline, carriage return

Characters are grouped together into four lexical units: identifiers, literals, delimiters, and comments.

 

Identifiers

Identifiers are names for PL/SQL objects, such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers have the following characteristics:

  • Can be up to 30 characters in length
  • Cannot include whitespace (space, tab, carriage return)
  • Must start with a letter
  • Can include a dollar sign ($), an underscore (_), and a pound sign (#)
  • Are not case-sensitive

Using PL/SQL’s reserved words as identifiers in your programs is not a good idea and can result in compilation or runtime errors that are difficult to troubleshoot.

 

Tip

If you enclose an identifier within double quotes, all but the first of these rules are ignored. For example, the following declaration is valid (although not exactly sensible):

DECLARE
   "1 ^abc"  VARCHAR2(100);
BEGIN
   IF "1 ^abc" IS NULL THEN ...
END;

 

Boolean, Numeric, and String Literals

Literals are specific values  not represented by identifiers. For example, TRUE, 3.14159, 6.63E-34, 'Moby Dick', and NULL are all literals of type Boolean, number, or string. There are no complex datatype literals because their values are internal representations; complex types receive values through direct assignment or via constructors. Unlike the rest of PL/SQL, literals are case-sensitive. To embed single quotes within a string literal, place two single quotes next to each other.

You can define your own quoting mechanism for string literals in both your SQL and PL/SQL statements. Use the characters q' (q followed by a straight single quote) to designate the programmer-defined delimiter for your string literal. Terminate the literal string with the programmer-defined delimiter followed by a trailing single quote—for example, q'!my string!'. NCHAR and NVARCHAR delimiters are preceded by the letters nq, as in nq'^nchar string^'. This technique can simplify your code when consecutive single quotes appear within a string, such as the literals in a SQL statement. If you define your delimiter with one of the four bracketing characters ( [ {<, you must use the righthand version of the bracketing character as the closing delimiter. For example, q'[ must be closed with ]'.

See the following table for examples:

Literal

Actual value

'That''s Entertainment!'

That’s Entertainment!

q'#That's Entertainment!#'

That’s Entertainment!

'"The Raven"'

“The Raven”

'TZ=''CDT6CST'''

TZ='CDT6CST’

q'$TZ='CDT6CST'$'

TZ='CDT6CST’

q'[TZ='CDT6CST']'

TZ='CDT6CST’

''''

'

'''hello world'''

‘hello world’

q'!'hello world'!'

‘hello world’

''''''

''

q'['']'

''

nq'<Price='£'>'

Price='£'

nq'-WHERE name LIKE 'ñ'-'

WHERE name LIKE 'ñ'

 

Numeric Literals

Oracle may improve runtime performance if you make explicit the datatype of numeric literals. You can do so by including or excluding a decimal point or by using a trailing f or d, as shown in the following table:

Literal

Datatype

3.14159

NUMBER

42

INTEGER

0.0

NUMBER

3.14159f

BINARY_FLOAT

3.14159d

BINARY_DOUBLE

You can also use the named constants:

  • BINARY_FLOAT_NAN (not a number)
  • BINARY_FLOAT_INFINITY
  • BINARY_FLOAT_MAX_NORMAL
  • BINARY_FLOAT_MIN_NORMAL
  • BINARY_FLOAT_MAX_SUBNORMAL
  • BINARY_FLOAT_MIN_SUBNORMAL

as well as the BINARY_DOUBLE versions of these constants.

 

Datetime Interval Literals

The datetime interval datatypes represent a chronological interval expressed in terms of either years and months or days, hours, minutes, seconds, and fractional seconds. Literals of these datatypes require the keyword INTERVAL followed by the literal and format string(s). The interval must go from a larger field to a smaller one, so YEAR TO MONTH is valid, but MONTH TO YEAR is not. See the following table for examples:

Literal

Actual value

INTERVAL '1–3' YEAR TO MONTH

1 year and 3 months later

INTERVAL '125–11' YEAR(3) TO MONTH

125 years and 11 months later

INTERVAL '-18' MONTH

18 months earlier

INTERVAL '-48' HOUR

48 hours earlier

INTERVAL '7 23:15' DAY TO MINUTE

7 days, 23 hours, 15 minutes later

INTERVAL '1 12:30:10.2' DAY TO SECOND

1 day, 12 hours, 30 minutes, 10.2 seconds later

INTERVAL '12:30:10.2' HOUR TO SECOND

12 hours, 30 minutes, 10.2 seconds later

 

Delimiters

Delimiters are symbols with special meaning, such as := (assignment operator), || (concatenation operator), and ; (statement delimiter). The following table lists the PL/SQL delimiters:

Delimiter

Description

;

Terminator (for statements and declarations)

+

Addition operator

Subtraction operator

*

Multiplication operator

/

Division operator

**

Exponentiation operator

||

Concatenation operator

:=

Assignment operator

=

Equality operator

<> and !=

Inequality operators

^= and ˜=

Inequality operators

< 

Less-than operator

<=

Less-than-or-equal-tooperator

> 

Greater-than operator

>=

Greater-than-or-equal-to operator

( and )

Expression  or list delimiters

<< and >>

Label delimiters

,

Item separator

'

Literal delimiter

q' and '

Programmer-defined string literal delimiter

nq' and '

Programmer-defined NCHAR string literal delimiter

"

Quoted literal delimiter

:

Host variable indicator

%

Attribute indicator

.

Component indicator (as in record.field or package.element)

@

Remote database indicator (database link)

=>

Association operator (named notation)

..

Range operator (used in the FOR loop)

--

Single-line comment indicator

/* and */

Multiline comment delimiters

 

Comments

Comments are sections of code that exist to aid readability. The compiler ignores them.

A single-line comment begins with a double hyphen (--) and terminates at the end-of-line (newline). The compiler ignores all characters between the -- and the newline.

A multiline comment begins with slash asterisk (/*) and ends with asterisk slash (*/). The /* */ comment delimiters also can be used for a single-line comment. The following block demonstrates both kinds of comments:

DECLARE

   -- Two dashes comment out remainder of line.

   /* Everything is a comment until the compiler
      encounters the following symbol */

You cannot embed multiline comments within a multiline comment, so be careful during development if you comment out portions of code that include comments. The following code demonstrates this issue:

DECLARE

   /* Everything is a comment until the compiler

      /* This comment inside another WON'T work!*/
      encounters the following symbol. */

 

   /* Everything is a comment until the compiler
      -- This comment inside another WILL work!
      encounters the following symbol. */

 

Pragmas

The PRAGMA keyword is used to give instructions to the compiler. There are six types of pragmas in PL/SQL:

AUTONOMOUS_TRANSACTION

Tells the compiler that the function, procedure, top-level anonymous PL/SQL block, object method, or database trigger executes in its own transaction space. See “Database Interaction” for more information on this pragma.

EXCEPTION_INIT

Tells the compiler to associate the specified error number with an identifier that has been declared an EXCEPTION in your current program or an accessible package. See “Exception Handling” for more information on this pragma.

INLINE

Tells the compiler whether calls to a subprogram should be replaced with a copy of the subprogram. See “Optimizing Compiler” for more information on inline optimization.

RESTRICT_REFERENCES

This pragma is deprecated; use DETERMINISTIC and PARALLEL_ENABLE instead. 

SERIALLY_REUSABLE

Tells the runtime engine that package data should not persist between references. This is used to reduce per-user memory requirements when the package data is needed only for the duration of the call and not for the duration of the session. See “Packages” for more information on this pragma.

UDF (Oracle Database 12c and higher)

Tells the compiler that you intend for your user-defined function to be invoked primarily from SQL rather than PL/SQL. This allows Oracle to optimize for performance inside SQL (at the possible expense of its performance in PL/SQL).

 

Statements

A PL/SQL program is composed of one or more logical statements. A statement is terminated by a semicolon delimiter. The physical end-of-line marker in a PL/SQL program is ignored by the compiler, except to terminate a single-line comment (initiated by the -- symbol).

 

Block Structure

Each PL/SQL program is a block consisting of a standard set of elements, identified by keywords (see Figure 1 ). The block determines the scope of declared elements and how exceptions are handled and propagated. A block can be anonymous or named. Named blocks include functions, procedures, packages, and triggers.

 PL/SQL programm block structure

Figure 1. The PL/SQL block structure

 

Here is an example of an anonymous block:

DECLARE
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date.
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END;

Here is a named block that performs the same action:

CREATE OR REPLACE PROCEDURE show_the_date

IS
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date.
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END show_the_date;

The following table summarizes the sections of a PL/SQL block:

Section

Description

Header

Required for named blocks. Specifies the way the program is called by other PL/SQL blocks. Anonymous blocks do not have a header. They start with the DECLARE keyword if there is a declaration section, or with the BEGIN keyword if there are no declarations.

Declaration

Optional; declares variables, cursors, types, and local programs that are used in the block’s execution and exception sections.

Execution

Contains statements that are executed when the block is run; optional in package specifications and type specifications.

Exception

Optional; describes error-handling behavior for exceptions raised in the executable section.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 385 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 382 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 154 views sepia Sat, 01 Dec 2018, 10:54:57
Introduction to PL/SQL
Introduction to PL/SQL 510 views Antoniy Wed, 12 Sep 2018, 15:18:13

Comments on PL/SQL Language Fundamentals

apv
apv Good for starrting article about PL/SQL language! 1 year ago
loader
loader
Attachment
Please login to comment