1. Character Set
The character set of PL/SQL includes:
- All uppercase and lowercase letters: include a~z and a~z.
- Number: Includes 0~9.
- Whitespace characters: include tabs, spaces, and carriage returns
- Other common English symbols
2. IdentifiersIdentifiers are used to define PL/SQL variables, constants, exceptions, cursor names, cursor variables, parameters, subroutine names, and other program unit names. In a PL/SQL program, identifiers start with characters, followed by characters, numbers, dollar signs ($), #, _, with a maximum length of 30 characters, and all characters are valid.
3. SeparatorsA delimiter is a single symbol or combination of symbols with special meanings.
| Symbol |
Description |
Symbol |
Description |
| + |
Arithmetic plus or represented as an integer |
- |
Arithmetic minus or expressed as negative |
| * |
Arithmetic multiplication |
/ |
Arithmetic except |
| = |
Relations, etc. |
:= |
Assignment operators |
| < |
Relationship is less than |
> |
Relationship is greater than |
| <= |
Relationship is less than or equal |
>= |
Relationship greater than or equal to |
| != |
Relationship is not equal to (<>, ^=, ~=) |
; |
Statement Terminator |
| ( |
Parenthesis operator start |
) |
End of parenthesis operator |
| /* |
Multi-line Comment start |
*/ |
Multi-line Comment end |
| << |
actually tag |
>> |
End tag |
| % |
A cursor property designator or a wildcard character that represents any of the characters |
_ |
Wildcards that represent a single character |
| : |
Host variable Indicator |
. |
Represents a subordinate relationship symbol |
| ‘ |
string identifiers |
" |
Citation identifiers |
| -- |
Single-line comment character |
|| |
String Join symbol |
| = |
Positional positioning symbols |
** |
Power operator |
4. Constant ValueThe so-called constant value refers to the character, numeric, date, and Boolean values that cannot be used as identifiers. (1) Character text: A string that is enclosed in single quotation marks, and the characters in the string are case-sensitive. If the string itself contains a single quotation mark, it is escaped with two consecutive single quotes. For example: ' Student ' book ', (2) Digital text: divided into integers and real numbers two categories. You can use scientific and technical law to represent digital text. (3) Boolean text: Indicates the value of a predefined variable, including True,false,null three values (4) Date type: Represents the date value, and its format differs from the date type format by two.
5. Data typePL/SQL data types include basic data types, primitive type subtypes, user-defined types, and three categories
- Number type: Binary_integer,pls_interger,number
- Character Type: Char,nchar,varchar2,nvarchar2,varchar
- Date/interval Type: Date,timestamp,interval
- Row identifier: Rowid,urowid
- Boolean Type: Boolean
- Original type: Raw,long raw
- LOB Type: Clob,blob,nclob,bfile
- Recording type: Record
- Collection type: Table
Attention:
- Varchar2,char is primarily used to store characters from the database character set, while Ncahar,nvarchar2 is used to store strings from the national character set.
- ROWID represents the physical address of a row, while Urowid can be either the physical address of a row or a logical address that represents a row.
- BLOBs store binary data, Clob,nclob hold text data, and bfile stores pointers to the operating system. The LOB type variable can store 4GB of data volume.
- Record type:
The data types described earlier are scalar data types, which are predefined by the system. Complex types, such as record types, collection types, and so on, need to be defined by the user. In PL/SQL, the record type is similar to a struct in the C language. Example: DeclareType t_emp is Record ( empn0 Number (4),ename varchar2 (+),sal Number (6,2));v_temp t_emp;BeginSelect empno,ename,sal to v_temp from EMP where empno=100; DBMS. OUTPUT. Put_Line (v_temp. ename| | v_temp. Sal); End;variables of the same record type can be assigned to each other, and variables of different record types cannot be assigned to each other even if the members are identical;A record type can only be applied to a PL/SQL block that defines the record type, that is, the record type is local. 5. Collection type: The collection type is also a conforming type, including index table type, nested type, and variable array type. The difference between a collection type and a record type is that member components in a record type can be of different types, similar to structs, and all members of a collection type must have the same data type, similar to an array. (1) Index Table type: syntax: Type index_tableis TABLE of Element_typeINDEX by Binary_integer | Pls_integer | VARCHAR2 (N) Example: DECLARETYPE emp_table is Table of char (TEN) INDEX by Binary_integer;v_enames emp_table;BEGINSelect ename to V_enames (0) from EMP where empno=100;Select Ename to V_enames (1) from EMP where empno=200;END;-----------------------The index table type can only be applied to PL/SQL blocks that define that type. (2) Nested table type nested table type the element index value starts at 1 and has no fixed upper bound. Definition syntax: Type nested_table is table of Element_type[not NULL]; (3) Variable group type mutable array type the element index value starts at 1 and has a fixed upper bound.TYPE Varrary_name is Varray | VARYING ARRAY (maxinum_size)of Element_type [not NULL]If you define a variable group type with a schema for the database, this type is global. 6.%type and%rowtype If you want to define a variable of a type that is consistent with the data type of a variable or a column in a database table (without knowing the data type of that variable or column), you can use%type. If you want to define a variable of a record type that is consistent with a table structure in the database, you can use%rowtype to implement it.
- The type of the variable varies depending on the type of variable referenced, the type of the database list, and the table structure.
- If the database table column has a not NULL constraint,%type, the data type returned with%rowtype does not have this restriction.
6. Variables and Constants (1) The definition of variables and constants:variable_name [CONSTANT] datatype [not NULL] [DEFAULT | : =expression];Description
- Only one variable can be defined per line;
- If you add a keyword constant, it means that the definition is a constant and must be the initial value;
- If the variable is defined with a not-null keyword, the variable must be assigned an initial value
- If the variable does not have an initial value assigned, the default is null
- Class with Default or ": =" for the variable.
(2) The scope of a variable's scope is the effective scope of the variable, starting from the life of the variable until the end of the block. If the PL/SQL blocks are nested with each other, the variables declared in the inner block are local and can only be referenced in the inner block, while the variables declared in the outer block are global, which can be referenced in the outer block or in the inner block. If a variable with the same name is defined in the inner block and the outer block, the external block name is used to label the global variable that references the outer block in the inner block. For example: <<outer>>declare v_enname varchar2 (100); BEGINv_ename: = ' Zhangsan ';DECLAREv_name varchar2 (+);BEGINv_name= ' This is INNER v_name ';outer.v_name= ' This is OUTER v_name ';END;END; 7. Compiling instructionsThe compilation instructions are special instructions issued to the compiler, also known as pseudo-directives, and do not alter the definition of the program. It simply passes information to the compiler, similar to a comment embedded in SQL. use the pragma keyword in PL/SQL to notify the compiler that the remainder of the PL/SQL statement is a compilation instruction or command. The compilation instructions are processed at compile time and are not executed at run time, similar to # define in C, with four compilation instructions:
- Exception_init: Tells the compiler that a specific error number is associated with the exception identifier declared in the program.
- Restrict_references: Tells the compiler about the purity of the wrapper, and limits the SQL statements and variables that can be used in the function.
- Serially_reuseable: Tells PL/SQL to run the engine without maintaining package-level data between data references.
- Autonomous_transction: Tell the compiler that the program block is self-made and that the commit and rollback of the transaction is run independently.
For example:DECLAREno_such_sequece EXCEPTION;PRAGMA exception_init (no_such_sequece,-2289)BEGINEND;
PL/SQL Syntax unit
