Description
Oracle database allows to create tables, objects, and execute all the familiar SQL statements like INSERT,UPDATE and DELETE. In addition, it also offers PL/SQL a very powerful and flexible procedural language, which gives us the ability to tie these SQL statements together. It gives us the ability to write procedural language, to create loops, perform conditional executions and provides a huge built-in setup, which gives fine control on 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 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 named blocks are called as sub-programs. And a subprogram can be termed as procedure or function. The key contrast among the procedure and functions are procedure is summoned as a stand alone proclamation and passes qualities to the calling program only by the means of parameter and a function can be utilized as a part of an expressions and it gives back a value to that expressions. And a sub-program can be nested inside one another and termed as bundles. These block structure is called as the language of PL/SQL block 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.
And these executable block must compulsory be defined in the program. An executable block contains the actual code that get executed.These block is enclosed between BETWEEN and END keywords. It contains at least one executable line of code in the PL/SQL executable statements.
The exceptional handling begins with the key word EXCEPTION. These block will be optional and comprising of error handling inside the program.
- Declarations
- Executable commands
- Exception Handling
PLSQL Syntax
DECLARE
<declaration section>
BEGIN
<executable commands>
EXCEPTION
<exception handling>
END;
Declaration section => It is optional block and describe some attributes.
Executable command => Contains the executable code.
Exception handling => Handle the errors inside the program.
Examples
The below example describes a sample PLSQL 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 describe the PL/SQL program execution. i.e.,Hello world program.
Description
The delimiter is a symbol with a specific definition. Successive is the series of delimiters in PL/SQL.
| 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 |
| -- | Single line comment indicator |
| ,= | Relational operators |
| ,'=,~=,^= | Different versions of NOT EQUAL |
Description
The PL/SQL identifiers are variables, procedures, constants, reserved words and exceptions. An identifier contains discretionary letter took after by numerals, dollar signs, letters, number signs and underscores and that doesn't go more than 30 characters. Also, these identifiers are not case sensitive by default, so one can utilize lower case or upper case to represent a numeric values and shouldn't utilize catch phrase that are held for an identifier.
The following are the valid identifier names, such as,
The following are the series of identifiers that are illegal in PL/SQL:
- Company_id# => Address_line1
- Primary_acct_responsibility => Address_line2
- First_name => S123456
- Lots_of_$MONEY$ => First_name
- Procedure-name => -- Contain invalid character "-"
- Company ID => -- Cannot have embedded space in name
- Maximum_value_exploded_for_detail => -- Name is too long
- Minimum_%due => -- Contain invalid character ""
Key Points
PLSQL Syntax draws out following main points.
- PLSQL Syntax - Syntax is a structure of a 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 - Is a symbol with specific definition.
- PL/SQL Identifiers - Identifiers are variables,procedures and the constants.