Summary of Oracle PL/SQL Variables

PLSQL variables can be of the following four types:
-Scalar)
-Composite)
-Reference)
-Lob type (large object)

1. scalar type:

-Only variables with a single value can be stored.
-When defining a scalar, you must specify the data type.

1.1 common scalar types

(1) varchar2 (N)
Defines a variable-length string
N specifies the maximum length of a string.
Maximum N value is 32767 bytes
Length must be specified during use
When this data type is used to manipulate the varchar2 table column in PLSQL block clocks, the starting value length should not exceed 4000 bytes.

(2) Char (N)
Define a fixed-length string
N specifies the maximum length of a string.
The maximum N value is 32767.
Length is specified during use. If no length is specified, the default value is 1.
When this data type is used to manipulate the char2 table column in PLSQL block clocks, the starting value length should not exceed 2000 bytes.

(3) Number (P, S)
Define fixed-length integers and floating-point numbers
P indicates the precision, used to specify the total number of digits
S indicates the scale, used to specify the number of digits after the decimal point

(4) Date
Define date and Data

(5) Boolean
Define Boolean variables
Values: True, false, and null
It is a unique data type of PLSQL. This type cannot be used for table columns.
Null indicates missing, inapplicable, or unknown.

(6) long and long row
Long: the data type is used to define a variable-length string. Similar to the varchar2 data type, the maximum length of the string is 32760 bytes.
Long row: the data type is used to define variable-length binary data. The maximum data length is 32760 bytes.

(7) binary_integer
Define integer
Value Range:-between 2147483647 and 2174483647

(8) binary_float and binary_double
Binary_float defines a single-precision floating point number (10 Gb in Oracle)
Binary_double defines double-precision floating-point numbers (10 Gb in Oracle)

(9) Timestamp
Define time and date data (New Oracle 9i)
The assignment method is the same as the Assignment Method for the date variable.
When the timestamp variable data is displayed, not only the date is displayed, but also the time and the last afternoon Mark are displayed.

1.2 define the scalar type
Syntax:
Identifier [constant] datatype [not null] [: = | default expr]
Identifier: Specifies the variable or constant name.
Constant: a constant.
Datatype: used to specify the type of a variable or constant
Not NULL: non-null. It must be initialized.
:=: The value assignment matches
Default: Default Value
Expr: Initial Value

1.3. scalar definition rules
Declare a variable per line
Variable Value assignment: identifier: = value;
Variable names can contain up to 30 characters

1.4 Use the % Type attribute
Install database columns or other variables to determine the type and length of new variables (dynamic and binding)
Syntax: identifier table. column_name % type;
Or identifier other_variable_name % type;

2. Compound Variables
Variable used to store multiple values
When you define a composite variable, you must use the PL/SQL Composite data type.
PL/SQL Composite data types include:
PL/SQL records
PL/SQL table (index table)
PL/SQL nested table
Varray
The composite data types are described in the relevant sections.

3. Reference Variables
Variable used to store the value pointer
You can share the same object by referencing variables to reduce the occupied space.
Two types of reference variables:
Cursor variable (ref cursor)
Object Type Variable (ref obj_type)
The reference variables are provided in the relevant sections.

4. lob variable
Variables used to store large volumes of data
There are two types:
Internal lob: including clob, blob, and nclob. Their data is stored in the database and transaction operations are supported.
External lob: bfile. This type of data is stored in the OS file and does not support transaction operations.
Clob: stores large volumes of character data
Nclob: stores large volumes of character data, Unicode encoding
BLOB: stores large volumes of binary data.
Bfile: stores the pointer to the OS file

5. Non-PL/SQL Variables

5.1,Replace variable(Used only for SQL * Plus or development tools with the same principle as SQL * Plus ):
Temporary storage value
It can be used to create a general script.
It can be used to interact with users, so it is also called interactive commands in SQL * Plus.
Replace the variable format by adding an & before the variable name to prompt the user to enter the replacement data when running the SQL command, and then run the SQL command according to the input data
Syntax:
(1) &: "& variable name" eg: & name;
Life cycle: it is in a single reference and does not need to be declared. If you replace the character or date type, it is best to extend it with single quotes.
Usage scope: Where, order by, column expression, table name, entire SELECT statement
(2) &: "& variable name" eg: & name;
Life cycle: entire session (Session connection), no need to declare
(3) define: "define variable name = variable value" eg: Define a = Clark;
Life cycle: indicates the entire session. It is pre-declared and used to reference declared variables.
Define variable = user-created char type value: Define variable name = value;
Define column_name (variable name): View variable commands.
Undefine variable name: Clear variable
Define: view all substitution variables and their values in the current session
(4) accept
Lifecycle: entire session
Pre-declaration. You can customize the prompt information and use & reference declared variables for use.
Definition:
Accept variable name number/Char/date prompt & apos; message content & apos; that is: ACC [EPT] variable [num [ber] | char | date] [for [Mat] format] [DEF [ault] default] [prompt text | nopr [OMPT] [hide]
Explanation:
Prompt command: this command is used to output user information, so that you can understand the functions and running status of the script file.
Pause command: Used to pause the running of script files
Hide options: used to hide user input so that others are invisible and secure
This command means that when the PLSQL program segment executes the variable name, user interaction is required to continue the execution, the PLSQL program section displays "prompt information content" for users to enter relevant information (if the hide option is specified, the next step is to display the information entered by the user with an asterisk to increase security, A bit like a password). The user-entered content is received and paid to the name. For the type of user-entered content under "prompt information content, the developer of the PLSQL program segment can specify it through number/Char/date. After the variable name gets the correct value, continue to execute the following programs!
For example, accept a char prompt & apos; enter the employee's employment time (yyyy-mm-dd): & apos; hide
Example: accept a char prompt & apos; input a: & apos; hide

The replacement variable is very dependent on the SQL * Plus environment. When the environment variable verify is disabled, it is not available. It can be used only by enabling it:
Set verify (environment variable) off; disable the debugging command (disable the replacement process)
Set verify (environment variable) on; open the debugging command (you can see the replacement process)

5.2,SQL * Plus Environment Variables(Used only for SQL * Plus or development tools with the same principle as SQL * Plus ):
Echo display echo
Whether heading {off/on} displays the column title
Arraysize {20/n} the size of the number of returned results from each query
Feedback {off/on} feedback
Long {80/n} on/text} Long TYPE
Linesize row width
Set linesize N (preferably within 200)
Pagesize: Set the page size. Set pagesize n
Wrap {off/on} lines
Set Modification
Show display
Command for formatting and displaying SQL * Plus:
Column [column option] can be used to set the format of a field or field alias.
Column last_name heading employee | Name & apos; | & apos; indicates line feed.
Col field name viewing command
CLE [ar]: Format of clearing Columns
Hea [Ding] Text: Set the column title
For [Mat] format: format the value of the displayed column, which is valid for characters and numbers and invalid for dates. For example: column salary justify left format $999,999.00, where justify left: Left alignment; col manager_id format 999999999
Limit the length of a string to a + number
The length of a number is limited to 9, and a number is limited to 9.
Noprint/print noprint: Block A field from the output (returned but not displayed ).
Col field name noprint/print.
If null has a null value, what is displayed.
Col name null & apos; on employee & apos;
Ttitle [text/Off/On] sets the report Header
Btitle [text/Off/On] sets the end of the report table
When creating a report, you must first consider the size of pagesize.
Break on [report_element]
To suppress the display of duplicate values, only one field name can be used. For example:
Select department_id, last_name
From employees
Where rownum <30
Order by 1, 2;
Break on department_id

5.3,The process of creating PL/SQL script files:Variable definition accept
Set Environment Variables
Format control command
Spool
SQL statement using variables
Spool off
Clear format Control
Reset Environment Variables
Release variable

5.4,Reference non-PL/SQL VariablesWhen you want to reference non-PL/SQL variables in PL/SQL blocks, that is, to reference the host variable (or "environment variable") of the PL/SQL block location, you must add a colon (":"), eg: ": Name" before a non-PL/SQL variable. The name is a non-PL/SQL variable.