Basic pl SQL syntax

Constant

1. Define the syntax format of constants
Constant name constant type identifier [not null]: = value;
Constants, including variable names that follow them, must start with a letter, cannot contain spaces, cannot exceed 30 characters, and cannot have the same name as the reserved words. Constant (variable) names are case-insensitive, A letter can contain numbers or special characters. Not null in parentheses is an optional parameter. If this parameter is selected, the constant (variable) Quantity cannot be null.
2. Instance
Run the following PL/SQL program in SQLPlus Worksheet. This program defines a digital constant named pi with a length of 9.
The result 9.6 is displayed.
―――――――――――――――――――――――――――――――――――――
Declare
Piconstant number (9): = 3.1415926;
Begin
Commit;
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ constantdefine. SQL.

Basic Data Type Variable

1. Basic data type
Common basic data types in PL/SQL are described in table 9.2.
Table 9.2 common data basic types

The data types used in PL/SQL are the same as those used in Oracle databases. Some have different meanings.
2. Definition of basic data type variables
Variable name type identifier [not null]: = value;
3. Instance
Run the following PL/SQL program in [SQLPlus Worksheet]. This program defines the number variable named age. The length is 3 and the initial value is 26. The result 9.7 is displayed.

―――――――――――――――――――――――――――――――――――――
Declare
Age number (3): = 26;
Begin
Commit;
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ basicdatatypedefine. SQL.

Composite data type variable

The following describes the definitions of several common Composite data type variables.
1. Use % type to define variables
To make the variable types in PL/SQL consistent with the data types of fields in the data table, Oracle 9i provides the % type definition method. In this way, when the field type of the data table is modified, the corresponding variable type in the PL/SQL program is also automatically modified.
Run the following PL/SQL program in [SQLPlus Worksheet]. This program defines the variable named mydate. Its type is consistent with the currentdate field type in the tempuser. testtable data table.
The result 9.8 is displayed.
―――――――――――――――――――――――――――――――――――――
Declare
Mydate tempuser. testtable. currentdate % type;
Begin
Commit;
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ typedefine. SQL.

2. Define record type variables
Many Structured Programming Languages provide data types of record types. In PL/SQL, multiple basic data types can also be bundled together.
The following program code defines the record type named myrecord. The record type consists of an integer myrecordnumber and a date mycurrentdate basic type variable. srecord is a variable of this type, the method for referencing a record variable is to "record the variable name. basic type variable name ".
The execution part of the program is from tempuser. in the testtable table, extract the record content with the recordnumber field of 68, store it in the srecord compound variable, and then output the srecord. the value of mycurrentdate is actually the value of currentdate recorded in the data table.
Run the following PL/SQL program in SQLPlus Worksheet. The execution result is 9.9.
―――――――――――――――――――――――――――――――――――――
Set serveroutput on
Declare
Type myrecord is record (
Myrecordnumber int,
Mycurrentdate date );
Srecord myrecord;
Begin
Select * into srecord from tempuser. testtable where recordnumber = 68;
Dbms_output.put_line (srecord. mycurrentdate );
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ recordtypedefine. SQL.
In PL/SQL programs, the select statement is always used with into. The into clause is followed by the variable to be assigned a value.

3. Use % rowtype to define the variable
Use % type to get the data type of the field for the variable, and use % rowtype to get the data type of the entire record for the variable. Compare the definitions of the two: variable name data table. Column name % type, variable name data table % rowtype.
Run the following PL/SQL program in SQLPlus Worksheet. This program defines a composite variable named mytable, which has the same structure as the testtable data table. The execution result is 9.10.
―――――――――――――――――――――――――――――――――――――
Declare
Mytable testtable % rowtype;
Begin
Select * into mytable
From tempuser. testtable
Where recordnumber = 88;
Dbms_output.put_line (mytable. currentdate );
End;
―――――――――――――――――――――――――――――――――――――

[Supporting program location]: Chapter 1 \ rowtypedefine. SQL.
4. Define one-dimensional table type variables
Table type variables are different from data tables. The syntax for defining table type variables is as follows:
―――――――――――――――――――――――――――――――――――――
Type table type is table of type index by binary_integer;
Table variable name table type;
―――――――――――――――――――――――――――――――――――――
The type can be the previous type definition. The index by binary_integer clause indicates that the index is a signed integer. In this way, the data method used to access table-type variables is "Table variable name (index symbol integer )".
Run the following PL/SQL program in [SQLPlus Worksheet]. This program defines two one-dimensional table types, tabletype1 and tabletype2, which are equivalent to one-dimensional arrays. Table 1 and Table 2 are two types of table variables.
The result 9.11 is displayed.
―――――――――――――――――――――――――――――――――――――
Declare
Type tabletype1 is table of varchar2 (4) index by binary_integer;
Type tabletype2 is table of tempuser. testtable. recordnumber % type index by binary_integer;
Table1 tabletype1;
Table2 tabletype2;
Begin
Table1 (1): = '';
Table1 (2): = 'Junior College ';
Table2 (1): = 88;
Table2 (2): = 55;
Dbms_output.put_line (table1 (1) | table2 (1 ));
Dbms_output.put_line (table1 (2) | table2 (2 ));
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ tabletypedefine1. SQL.

"|" Is the operator used to connect strings.
5. Define multi-dimensional table type variables
Run the following PL/SQL program in [SQLPlus Worksheet]. This program defines a multi-dimensional table Type named tabletype1, which is equivalent to a multi-dimensional array. table1 is a variable of the multi-dimensional table type and converts the data table tempuser. in testtable, records with a recordnumber of 60 are extracted and stored in table1. The result 9.12 is displayed.
―――――――――――――――――――――――――――――――――――――
Declare
Type tabletype1 is table of testtable % rowtype index by binary_integer;
Table1 tabletype1;
Begin
Select * into table1 (60)
From tempuser. testtable
Where recordnumber = 60;
Dbms_output.put_line (table1 (60). recordnumber | table1 (60). currentdate );
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ tabletypedefine2. SQL.

In the defined table type variables, you can use attributes such as count, delete, first, last, next, exists, and prior to perform operations. property ", returns a number.
Run the following PL/SQL program in [SQLPlus Worksheet]. This program defines the one-dimensional table Type named tabletype1, table1 is a one-dimensional table type variable, and 3 data are inserted into the variable, table variable attributes are used in combination.
The result 9.13 is displayed.
―――――――――――――――――――――――――――――――――――――
Set serveroutput on
Declare
Type tabletype1 is table of varchar2 (9) index by binary_integer;
Table1 tabletype1;
Begin
Table1 (1): = 'chengdu City ';
Table1 (2): = 'beijing ';
Table1 (3): = 'qingdao ';
Dbms_output.put_line ('Total number of records: '| to_char (table1.count ));
Dbms_output.put_line ('first record: '| table1.first );
Dbms_output.put_line ('Last record: '| table1.last );
Dbms_output.put_line ('first Record of the second record: '| table1.prior (2 ));
Dbms_output.put_line ('Next Record of the second record: '| table1.next (2 ));
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ tabletypedefine3. SQL.

Expression

Variables and constants often need to form various expressions for calculation. The following describes the calculation rules of common expressions in PL/SQL.
1. numeric expression
The numeric expressions in PL/SQL programs are composed of numeric constants, variables, functions, and arithmetic operators. The Arithmetic Operators that can be used include + (addition) and-(subtraction), * (multiplication),/(Division), and ** (multiplication.
Run the following PL/SQL program in SQLPlus Worksheet. This program defines an integer variable named result, the calculated value is 10 + 3*4-20 + 5 ** 2, and the theoretical result is 27. The result 9.14 is displayed.
―――――――――――――――――――――――――――――――――――――
Set serveroutput on
Declare
Result integer;
Begin
Result: = 10 + 3*4-20 + 5 ** 2;
Dbms_output.put_line ('Operation result: '| to_char (result ));
End;
―――――――――――――――――――――――――――――――――――――
[Supporting program location]: Chapter 1 \ datacompute. SQL.

The output of the dbms_output.put_line function can only be a string. Therefore, the to_char function is used to convert the numeric result to the numeric type.
2. Character Expression
A character expression is composed of constant, variable, function, and character operator. The only character operator that can be used is the concatenation operator "| ".
3. relational expressions
Relational expressions consist of character expressions, numeric expressions, and Relational operators. The following types of Relational operators are available.
<Less
> Greater
= Equals (not the value assignment operator: =)
Like is similar
In ...... Medium
<= Less than or equal
> = Greater than or equal
! = Not equal
Between is ...... Between
The Data Types of expressions on both sides of relational expression operators must be consistent.
4. logical expressions
Logical expressions are composed of logical constants, variables, functions, and logical operators. common logical operators include the following three types.
NOT: Non-logical
OR: logical OR
AND: logic AND
The priority of Operations is NOT, AND, OR.

Function

PL/SQL programs provide many functions for extension. Apart from functions in the standard SQL language, the most common data type conversion functions include the following.
To_char: convert data of other types to character type.
To_date: convert other types of data to the date type.
To_number: convert other types of data to numeric type.
The above describes the most basic syntax elements in PL/SQL. The following describes the process control statements that reflect the procedural programming concepts of PL/SQL.

Type identifier indicates the Number numeric Int Integer type Pls_integer Integer type. If an overflow occurs, the Binary_integer Integer type is incorrect. It indicates the signed integer Char fixed-length character type and a maximum of 255 characters Varchar2 variable-length character type, A maximum of 2000 characters Long variable-length string type, and a maximum of 2 GB Date Boolean type (TRUE, FALSE, NULL)