PL/SQL Basic syntax
PL/SQL is a block-structured language, which means that PL/SQL programs are partitioned and written into logical blocks of code. Each block consists of three sub-parts:
S.N. |
paragraph and Description |
1 |
Statement This section starts with the keyword declare. It is an optional part and restricts all variables, cursors, subroutines, and other elements used in the program. |
2 |
Executable commands This section is the closing keyword begin and end, which is a mandatory part. It consists of the PL/SQL statement of the program's executable file. It should have at least one executable line of code, which may simply be an empty command to indicate that nothing is being done. |
3 |
Exception handling This section starts with the keyword exception. This section is optional and contains an exception in the program handling error. |
Each PL/SQL statement ends with a semicolon (;). can be nested in PL/SQL blocks that use the begin and end, and so on. Here is the basic structure of a PL/SQL block:
DECLARE <declarations section>BEGIN<executable command(s ) ><exception handling>END;
' Hello World ' example:
DECLARE message varchar2: =' Hello, world! ' ; BEGIN dbms_output. Put_Line(message); END; /
End The line signal is the end of a PL/SQL block. To run the SQL command line code, you may need to enter the beginning of the first blank line before the last line of the code. When the above code is executed at the SQL prompt, it produces the following results:
Hello Worldpl/sql procedure successfully completed.
PL/SQL identifiers
PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers are followed by a letter that can be followed by more letters, numbers, dollar signs, underscores, and number symbols, and must not exceed 30 characters.
By default, identifiers are case insensitive. Therefore, you can use an integer or integer to represent a numeric value. You cannot use reserved keywords as identifiers.
PL/SQL Separators
A delimiter is a symbol with a special meaning. The following is a list of the PL/SQL separators:
Separators |
Description |
+, -, *, / |
addition, subtraction/negation, multiplication, division |
% |
Property Index |
‘ |
String delimiter |
. |
Component Selection |
(,) |
An expression or list separator |
: |
Host variable Indicator |
, |
Item Separator |
" |
Quoted identifier delimiter |
= |
Relational operators |
@ |
Remote Access Indicator |
; |
Declaring a terminating character |
:= |
Assignment operators |
= |
Operator Association |
|| |
Join operators |
** |
Exponentiation operator |
<<, >> |
Label delimiter (start and end) |
/*, */ |
Multiline comment delimiter (start and end) |
-- |
Single-line Comment indicator |
.. |
Range operations |
<, <=, >= |
Relational operators |
<>, ' =, ~=, ^= |
Different versions of unequal |
PL/SQL annotations
Program comments are explanatory notes that can include your own writing and help anyone read the PL/SQL code of the source code. All programming languages allow some form of annotation.
Support single-line and multiline annotations in PL/SQL. All characters in any comment will be ignored by the PL/SQL compiler. Use the delimiter-(double hyphen) and multiline comment in the PL/SQL single-line comment by the/* and/*.
DECLARE --Variable declaration message varchar2: =' Hello, world! ' ; BEGIN/* PL/SQL executable statement (s) */ dbms_output. Put_Line(message); END; /
When the above code is executed at the SQL prompt, it produces the following results:
Hello Worldpl/sql procedure successfully completed.
PL/SQL Program Unit
The PL/SQL unit is any one of the following:
PL/SQL block
Function
Package
Inclusion
Process
Trigger
Type
Type body
PL/SQL data type
PL/SQL variables, constants, and parameters must have a valid data type, specifying a valid range of storage formats, constraints, and values. This tutorial introduces scalar and LOB in PL/SQL, and the data types available for the other two data types will be covered in later chapters.
category |
Description |
Scalar |
Single value does not have an internal component, such as number, DATE, or BOOLEAN |
Large Object (LOB) |
Store large objects that point to other data items such as text, graphics images, video clips, and sounds |
Composite |
A data item with an internal component that can be accessed separately. For example, collections and records |
Reference |
Point to other data items |
PL/SQL Scalar data types and sub-types
PL/SQL Scalar data types and sub-types are subject to the following major categories:
Data Type |
Description |
Digital |
The numeric value of the arithmetic operation performed on it |
Character |
An alphanumeric string value that represents a single character or character |
Boolean |
Logical value of the logical operation on which to execute |
Date Time |
Date and time |
The subtype of the data type provided by PL/SQL. For example, the number of data types has a subtype called integer. You can use subtypes in a PL/SQL program to make data types compatible with data types in other programs, while embedding PL/SQL code in another program, such as a Java program.
PL/SQL numeric data type and subtype
The following is the detailed information for the PL/SQL predefined numeric data types and their subtypes:
Data Type |
Description |
Pls_integer |
Signed integers in the range 2,147,483,647 to 2147483648, denoted by 32 bits |
Binary_integer |
A signed integer in the range of 2,147,483,647 to 2147483648, represented by 32 bits |
Binary_float |
Floating-point number in single-precision IEEE 754 format |
Binary_double |
Double-precision floating-point number in IEEE 754 format |
Number (PREC, scale) |
Fixed-point or floating-point number in the range 1E-130 to (but not including) the absolute value 1.0E126. The number variable can also represent 0 |
DEC (PREC, scale) |
ANSI specific fixed-point type uses 38-bit decimal maximum precision |
DECIMAL (PREC, scale) |
IBM-specific fixed point type with 38 decimal places maximum precision |
NUMERIC (pre, Secale) |
Floating-point types have a maximum precision of 38 decimal places. |
DOUBLE PRECISION |
With 126 binary digits maximum precision ANSI-specific float (approx. 38 decimal digits) |
FLOAT |
With 126 binary digits (approximately 38 decimal digits) Maximum precision ANSI and IBM-specific floating-point type |
Int |
With 38 decimal places maximum precision ANSI specific integer type |
INTEGER |
38-bit decimal maximum precision specific integer type for ANSI and IBM |
SMALLINT |
38-bit decimal maximum precision specific integer type for ANSI and IBM |
REAL |
Maximum precision floating point with 63-bit binary digits (approximately 18 decimal places) |
The following is a valid declaration:
DECLARE num1 INTEGER; Num2 REAL; num3 DOUBLE PRECISION; BEGIN null; end;/
Let's compile and run the above program, which will produce the following results:
PL/SQL procedure successfully completed
PL/SQL character data type and subtype
The following is a detailed information about the predefined character data types and their subtypes of PL/sql:
Data Type |
Description |
CHAR |
A fixed-length string with a maximum size of 32,767 bytes |
VARCHAR2 |
Maximum size variable length string with 32,767 bytes |
RAW |
The maximum size of a variable-length binary or byte string of 32,767 bytes, rather than PL/SQL interpretation |
NCHAR |
A fixed-length country string with a maximum size of 32,767 bytes |
NVARCHAR2 |
Maximum size variable length country string with 32,767 bytes |
LONG |
Has a 32,760-byte maximum size variable length string |
LONG RAW |
The maximum size of a variable-length binary or byte string of 32,760 bytes, rather than PL/SQL interpretation |
ROWID |
Physical row identifier, one row of addresses in a normal table |
Urowid |
Physical Universal Row Identifier (physical, logical, or foreign row identifier) |
PL/SQL Boolean data type
A logical value stored using a Boolean data type in a logical operation. The logical values are Boolean values that are true and false, and NULL values.
However, SQL does not have a data type equal to Boolean. Therefore, the Boolean value cannot be used for:
PL/SQL Date time and interval type
The date data type stores a fixed-length datetime, which includes the time of day from midnight in a few seconds. Valid dates range from January 1, 4712 to 999912 months before 31st.
The default date format is set by the Oracle initialization parameter Nls_date_format. For example, the default can be "Dd-mon-yy", which includes a date in a two-digit month, an abbreviation for the month name, and the last two digits of the year, for example, 01-oct-12.
Each date, including century, year, month, day, time, minute, second. The following table shows the valid values for each field:
Field name |
valid datetime value |
valid interval value |
Year |
-4712 to 9999 (excluding year 0) |
Any non-0 integer |
MONTH |
12 |
0 to 11 |
Day |
31 (subject to the values of the year and month, as set by the locale of the calendar) |
Any non-0 integer |
HOUR |
XX to 23 |
0 to 23 |
MINUTE |
XX to 59 |
0 to 59 |
SECOND |
XX to 59.9 (n), where 9 (n) is the time fractional seconds precision |
0 to 59.9 (N), where 9 (n) is the interval fractional seconds precision |
Timezone_hour |
-12 to 14 (range accommodates daylight saving time changes) |
Not applicable |
Timezone_minute |
XX to 59 |
Not applicable |
Timezone_region |
Find V$timezone_names in Dynamic performance view |
Not applicable |
Timezone_abbr |
Find V$timezone_names in Dynamic performance view |
Not applicable |
PL/SQL Large object (LOB) data type
Large object (LOB) data types are large to data items such as text, graphic images, video clips, and sound waveforms. LOB data types allow for efficient, random, segmented access to these data. The following are the predefined PL/SQL LOB data types:
Data Type |
Description |
size |
BFILE |
System files for storing large binary objects outside the operational database |
dependent on system, cannot exceed 4,000 gigabytes (GB) |
Blob |
Used to store large binary objects in the database |
8 to 128 terabytes (TB) of |
Clob |
Used to store characters in large chunks of data in a database |
8-128 TB |
NCLOB |
Used to store large chunks of nchar data in a database |
8-128 TB |
PL/SQL user-defined sub-types
Subtypes are another type of data, which is a subset of the so-called basic types. Subtypes have the same operations as their basic types, but they are only a subset of valid values.
PL/SQL Pre-defines the encapsulation criteria for some subtypes. For example, PL/SQL pre-defined subtypes character and integers are as follows:
Subtype CHARACTER is CHAR; Subtype INTEGER is number (38,0);
You can define and use your own subtypes. The following program description defines and uses a user-defined subtype:
DECLARE subtype name is char (); Subtype message is VARCHAR2 (+); Salutation name; Greetings message; BEGIN Salutation: = ' Reader '; Greetings: = ' Welcome to the world of PL/SQL '; Dbms_output.put_line (' Hello ' | | salutation | | greetings); end;/
When the above code is executed at the SQL prompt, it produces the following results:
Hello Reader Welcome to the world of Pl/sqlpl/sql procedure successfully completed.
PL/SQL NULL
PL/SQL Null value indicates missing or unknown data, clam It is not an integer, character, or any other specific data type. It is important to note that NULL is not the same as an empty data string or a null character value ' \ s '. Null can be assigned, but it cannot be equated with anything, including itself.
SQL Records-plsql basic syntax and data types