Basic grammatical elements of pl/sql

Constant

1. Define the syntax format for constants
Constant name constant type identifier [not null]:= value;
constants, including the following variable names must begin with a letter, cannot have spaces, cannot exceed 30 characters in length, and cannot have the same name as reserved words, and the constant (variable) quantity name is case-insensitive and can be followed by a number or special character. Not NULL in parentheses is an optional parameter, and if selected, indicates that the constant (variable) quantity cannot be a null value.
2. Examples
Execute the following pl/sql program in "Sqlplus Worksheet", which defines a numeric constant named Pi with a length of 9.
The results of the execution are shown in Figure 9.6.
―――――――――――――――――――――――――――――――――――――
Declare
PI constant Number (9): = 3.1415926;
Begin
Commit
End
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \constantdefine.sql.

Basic Data type variables

1. Basic data types
The basic data types commonly used in Pl/sql are shown in table 9.2.
Table 9.2 Common Data Base types

Type identifier	Description
Number	Digital type
Int	Integer type
Pls_integer	Integer, error occurs when overflow occurs
Binary_integer	An integral type that represents an integer with a symbol
Char	Fixed-length character, maximum 255 characters
Varchar2	Variable length character type, maximum 2000 characters
Long	Variable length character type, maximum 2GB
Date	Date type
Boolean	Boolean (TRUE, FALSE, null three to take one)

The data types used in Pl/sql and the data types used in the Oracle database are identical and have different meanings.
2. Definition method of basic data type variable
Variable name type identifier [not null]:= value;
3. Examples
Execute the following pl/sql program in "Sqlplus Worksheet", which defines a numeric variable named age, with a length of 3 and an initial value of 26. The results of the execution are shown in Figure 9.7.

―――――――――――――――――――――――――――――――――――――
Declare
Age Number (3): = 26;
Begin
Commit
End
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \basicdatatypedefine.sql.

Composite data type variable

The following are some common definitions of several composite data type variables.
1. Define variables using%type
To make the type of the variable in Pl/sql consistent with the data type of the field in the datasheet, Oracle 9i provides a%type definition method. This way, when the field type of the datasheet is modified, the type of the corresponding variable in the PL/SQL program is automatically modified.
Execute the following pl/sql program in "Sqlplus Worksheet", which defines a variable named MyDate that is the same type as the Tempuser.testtable field type in the CurrentDate datasheet.
The results of the execution are shown in Figure 9.8.
―――――――――――――――――――――――――――――――――――――
Declare
MyDate Tempuser.testtable.currentdate%type;
Begin
Commit
End
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \typedefine.sql.

2. Define record type variables
Many structured programming languages provide data types for record types, and in Pl/sql, record data types that are bundled with multiple base data types are also supported.
The following program code defines the record type named Myrecord, which consists of an integer myrecordnumber and a mycurrentdate base type variable of the date type, and Srecord is a variable of that type, and the method of referencing a record variable is " Record variable name. base type variable name.
The execution part of the program extracts the contents of the record of recordnumber Field 68 from the Tempuser.testtable datasheet, is stored in the Srecord compound variable, and then outputs the Srecord.mycurrentdate value, which is actually the current of the corresponding record in the datasheet. The value of date.
Perform the following pl/sql programs in "Sqlplus Worksheet", as shown in Figure 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
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Recordtypedefine.sql.
In the Pl/sql program, the SELECT statement is always used with into, and the INTO clause is followed by the variable to be assigned a value.

3. Define variables using%rowtype
Use%type to make a variable get the data type of the field, and use%rowtype to get the variable to take the data type of the entire record. Compare the difference between the definitions: variable name data table, column name%type, variable name data table%rowtype.
Execute the following pl/sql program in "Sqlplus Worksheet", which defines a compound type variable named MyTable, which is the same as the TestTable data table structure, as shown in Figure 9.10.
―――――――――――――――――――――――――――――――――――――
Declare
MyTable Testtable%rowtype;
Begin
SELECT * INTO MyTable
From tempuser.testtable
where recordnumber=88;
Dbms_output.put_line (mytable.currentdate);
End
―――――――――――――――――――――――――――――――――――――

"Matching program Location": 9th Chapter \ Rowtypedefine.sql.
4. Define a one-dimensional table type variable
There are differences between table type variables and data tables, and the syntax for defining table type variables is as follows:
―――――――――――――――――――――――――――――――――――――
Type is table type is table of type index by Binary_integer;
Table variable name table type;
―――――――――――――――――――――――――――――――――――――
The type can be the previous type definition, and the index by Binary_integer clause is indexed with a signed integer so that the data method in the table type variable is accessed by the table variable name (index symbol integer).
Execute the following pl/sql program in "Sqlplus Worksheet", which defines two one-dimensional table types named Tabletype1 and Tabletype2, equivalent to one-dimensional arrays. Table1 and table2 are two types of table-type variables respectively.
The results of the execution are shown in Figure 9.11.
―――――――――――――――――――――――――――――――――――――
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): = ' university ';
Table1 (2): = ' College ';
Table2 (1): = 88;
Table2 (2): = 55;
Dbms_output.put_line (table1 (1) | | Table2 (1));
Dbms_output.put_line (table1 (2) | | Table2 (2));
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Tabletypedefine1.sql.

"| |" Is the operator of the connection string.
5. Define a multidimensional table type variable
Execute the following Pl/sql program in Sqlplus worksheet, which defines a multidimensional table type named Tabletype1, which is the equivalent of a multidimensional array, and a multidimensional table type variable. Extracts recordnumber 60 in datasheet tempuser.testtable to be stored in Table1 and displayed. The results of the execution are shown in Figure 9.12.
―――――――――――――――――――――――――――――――――――――
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) recordnumber| | Table1. currentdate);
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Tabletypedefine2.sql.

In a well-defined table-type variable, you can use the attributes such as Count, delete, the top, last, next, exists, and prior to manipulate, using the method "table variable name. Property", which returns a number.
Execute the following pl/sql program in "Sqlplus Worksheet", which defines a one-dimensional table type named Tabletype1, Table1 is a one-dimensional table type variable with 3 data inserted in the variable, and the table variable properties are used synthetically.
The results of the execution are shown in Figure 9.13.
―――――――――――――――――――――――――――――――――――――
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 record number: ' | | To_char (Table1.count));
Dbms_output.put_line (' first record: ' | | Table1.first);
Dbms_output.put_line (' last note: ' | | Table1.last);
Dbms_output.put_line (' The first record of the second article: ' | | Table1.prior (2));
Dbms_output.put_line (' The latter record of the second article: ' | | Table1.next (2));
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Tabletypedefine3.sql.


An expression

variables, constants often need to compose various expressions to perform operations, the following describes the rules of operation for common expressions in Pl/sql.
1. Numeric expression
numeric expressions in a pl/sql program are composed of numeric constants, variables, functions, and arithmetic operators, and the arithmetic operators that can be used include + (addition),-(subtraction), * (multiplication),/(division), and * * (exponentiation).
Execute the following pl/sql program in "Sqlplus Worksheet", which defines an integer variable named result, calculated as the 10+3*4-20+5**2 value, and the theoretical result should be 27. The results of the execution are shown in Figure 9.14.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
result integer;
Begin
result:=10+3*4-20+5**2;
Dbms_output.put_line (' The result of the operation is: ' | | To_char (result));
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Datacompute.sql.

The Dbms_output.put_line function output can only be a string, so the numeric result is converted to a character type using the To_char function.
2. Character expression
Character expressions consist of character constants, variables, functions, and character operators, and the only character operators that can be used are the join operators "| |".
3. Expression of relationship
A relational expression consists of a character expression or a numeric expression and a relational operator, and the relational operators you can use include the following 9 kinds.
< less than
> Greater than
= Equals (not assignment operator: =)
Like similar to
In the ... Among
<= less than or equal
>= is greater than or equal to
!= is not equal to
Between in the ... Between
The expressions on both sides of the relational expression operator must be of the same data type.
4. Logical expression
Logical expressions consist of logical constants, variables, functions, and logical operators, and common logical operators include the following 3 kinds.
Not: Logical non-
Or: Logical OR
And: Logic and
Operations are in the order of not, and and OR.

Function

The PL/SQL program provides a number of functions for extended functions, except that the standard SQL language functions can be used, the most common data type conversion functions have the following 3.
To_char: Converts other types of data to character type.
To_date: Converts other types of data to date type.
To_number: Converts other types of data to numeric type.
The above introduces the most basic grammatical elements in Pl/sql, and the following introduces the process Control statements embodying pl/sql process programming ideas.