Description
In the late 1980's, the Oracle corporation has built the PL/SQL Programming Language. The PL/SQL is a procedural expansion language for oracle relational database and structure query language. PLSQL Overview chapter gives the clear description of PL/SQL Syntax, Delimiters and Identifiers.
The following are some key facts of the PL/SQL language.
- PL/SQL is a completely versatile and highly transaction processing language.
- It contains a working framework independent of programming environment and build-in translated.
- From SQL*plus command line interface, PL/SQL can be called directly.
- From outer programming language, it can be called directly to the database.
- The syntax structure of PL/SQL is similar to that of pascal programming dialect and ADA.
- PL/SQL is accessible in IBM DB2 and Times Ten in_memory database.
Description
Oracle database allows users to create tables, objects, and execute all the SQL statements like INSERT, UPDATE and DELETE. In addition to these, it also provides PL/SQL-a very powerful and flexible procedural language, which gives us the ability to combine these SQL statements together. It allows us to write procedural language to create loops, perform conditional executions and provides a built-in setup that gives fine control over execution of statements. And, PL/SQL helps in defining business logic more efficiently and allows the clients to create reusable units of work.
The programs of PL/SQL, in PLSQL Overview chapter, are divided and composed into a code of logical blocks. These blocks can either be anonymous or named. The anonymous blocks are called as mysterious blocks and the named blocks are called as sub-programs. And, a subprogram can be termed as a procedure or function. The key differences among the procedure and functions is- procedure is summoned as a stand alone statement and passes qualities to the calling program only by the means of parameter and is used to perform an action that does not returns a value, whereas a function can be utilized as a part of an expression and gives back a value to that expressions. And, sub-programs can be nested inside one another and termed as bundles. These block structures are called as the language of PL/SQL blocks and these blocks are characterized into 3-sections:
DECLARE is the keyword with which the PL/SQL block begins. It is an discretionary block and describes cursors, sub-programs, constants, nested programs, exceptions and variables.
An executable block contains the actual code that gets executed and must be defined in the program. This block is enclosed between BETWEEN and END keywords. It contains at least one executable line of code in the PL/SQL executable statements.
The exception handling begins with the key word EXCEPTION. This block will be optional and comprises of error handling methods inside the program.
- Declarations
- Executable commands
- Exception Handling
Syntax
DECLARE
<declaration section>
BEGIN
<executable commands>
EXCEPTION
<exception handling>
END;
Declaration section => It is the optional block and describes some attributes.
Executable command => Contains the executable code.
Exception handling => Handles the errors inside the program.
Examples
The below example explains a sample PL/SQL syntax program:
[c]
SQL> DECLARE
2 message varchar2(20):='Hello,world!';
3 BEGIN
4 dbms_output.put_line(message);
5 END;
6 /
Hello world
PL/SQL procedure successfully completed.[/c]
The above example describes the execution process of PL/SQL program. i.e., the Hello world program.
Description
Delimiters are symbols that contains a specific definition. Successive is the series in PL/SQL containing the delimiters.
| Delimiter | Description |
|---|---|
| % | Attribute indicator |
| . | Component selector |
| ' | Character string delimiter |
| +,-,*,/ | Addition,subtraction,multiplication,division |
| " | Quoted identifier delimiter |
| , | Item separator |
| : | Host variable indicator |
| (,) | List or expression delimiter |
| @ | Remote access indicator |
| (,) | List or expression delimiter |
| ; | Statement terminator |
| (:=) | Assignment operator |
| || | Concatenation operator |
| => | Association operator |
| , | Relational operators |
| --= | Single line comment indicator |
| ,'=,~=,^= | Distinct NOT EQUAL versions |
Description
The identifiers in PLSQL Overview are procedures, variables, constants, reserved words and exceptions. An identifier contains discretionary letter after numerals, dollar signs, letters, number signs and underscores that doesn't go more than 30 characters. Also, these identifiers are not case sensitive by default, so one can use either lower case or upper case to represent numeric values and shouldn't utilize catch phrase that is held for an identifier.
The following are the valid identifier names, such as:
The following are a series of identifiers that are not accepted in PL/SQL:
- First_name => S123456
- Lots_of_$MONEY$ => First_name
- Company_id# => Address_line1
- Primary_acct_responsibility => Address_line2
- Maximum_value_exploded_for_detail => -- Name is too long
- Minimum_%due => -- Contain invalid character
- Procedure-name => -- Contain invalid character "-"
- Company ID => -- Cannot have embedded space in name
Key Points
- PLSQL Overview - It is a procedural expansion language developed by the oracle corporation.
- PL/SQL Syntax - Syntax is a structure of the program code.
- Declaration - It is an optional block in PL/SQL syntax that begins with the keyword DECLARE.
- Executable commands - It is a mandatory block in PL/SQL syntax.
- Exception handling - It is a mandatory block in PL/SQL syntax that begins with the keyword EXCEPTION.
- PL/SQL Delimiters - It is a symbol with specific definition.
- PL/SQL Identifiers - Procedures, variables and constants.