PL/SQL (ii) PL/SQL blocks

PL/SQL is a block-structured language in which a PL/SQL program contains one or more logical blocks in which variables can be declared and variables must be declared before they are used. In addition to the normal execution procedures, PL/SQL also provides specialized exception handling parts for exception handling. Each logical block is divided into three parts, and the syntax is:

Syntax structure: The syntax of PL/SQL blocks

[DECLARE--declaration statements]  BEGIN--[EXCEPTION--exception statements]   END; 　　

Syntax parsing:

① Declaration section: The declaration section contains definitions of variables and constants. This section starts with the keyword declare, which can be omitted if the variable or constant is not declared.
② execution part: The execution part is the instruction portion of the PL/SQL block, starting with the keyword begin and ending with the keyword end. All of the executable PL/SQL statements are placed in this section, which executes the command and operates on the variable. Other PL/SQL blocks can be nested within that section as child blocks. The execution portion of the PL/SQL block is required. Notice that the end keyword is followed by a semicolon.

③ Exception Handling section: This section is optional, the section uses the exception keyword to divide the executable part into two small parts, the previous program is the normal operation of the program, in the event of an exception to jump to the exception part of the execution.

PL/SQL is a programming language that, like Java and C #, has its own language features in addition to its own unique data types, variable declarations and assignments, and process Control statements:

★pl/sql is not sensitive to capitalization, and for a good program style, the development team will choose a suitable coding standard. For example, some team rules: The key words are all larger, the rest of the section lowercase.

Each statement in a ★pl/sql block must end with a semicolon, and the SQL statement can be multiple lines, but a semicolon indicates that the statement ends. There can be multiple SQL statements in a row, separated by semicolons, but it is not recommended to write multiple statements in one line.

Special symbol descriptions in PL/sql:

Type	Symbol	Description
Assignment operators	:=	Both Java and C # are equal equals, and PL/SQL is assigned the following values: =
Special characters	||	String connection operator.
	--	A single-line comment in PL/SQL.
	/*,*/	Multi-line comments in PL/SQL, multi-line annotations cannot be nested.
	<<,>>	The label separator character. Only to identify the program's special location.
	..	Range operators, such as: 1. 5 identification from 1 to 5
Arithmetic operators	+,-,*,/	The basic arithmetic operator.
	**	Exponentiation operations, such as: 3**2=9
Relational operators	>,<,>=,<=,=	The basic relational operator, = represents an equality relationship, not an assignment.
	<>,!=	Unequal relations.
logical operators	And,or,not	The logical operator.

※ Variable Declaration

PL/SQL supports the types of data that are available in number,varchar2,date, and SQL data types such as Oracle are normally supported in PL/SQL. Declaring a variable must indicate the data type of the variable, or it can be initialized when the variable is declared, and the variable declaration must be in the declaration section. The syntax for declaring variables is:

Syntax format: declaring variables

Variable name data type [: = initial value]

Syntax parsing:

Data type if length is required, the length can be indicated by parentheses, for example: VARCHAR2 (20).

Code Demo: declaring variables

Sql> DECLARE 2SnameVARCHAR2( -) :='Jerry'; ①3 BEGIN 4Sname=Sname||'and Tom'; ②5Dbms_output.put_line (sname); ③6 END; 

Code parsing:

① declares a variable sname, and the initialization value is "Jerry". Strings are quoted in single quotes, and if a single quotation mark appears in a string, it can be represented by two single quotation marks (' '), that is, single quotes also have the effect of escaping.
② assigns a value to the variable sname, the assignment operator is ": =".
③dbms_output.put_line is the output statement, you can put the value of a variable output, in the Sql*plus output data, may not show the results, you can use the command: Set serveroutput on Settings output to the Sql*plus console.

Assigning values to variables can also be used with select ... The INTO statement assigns variables to the query data from the database. However, the result of a query can only be one row of records, not 0 or more rows.

Code Demo: Variable Assignment

Sql> DECLARE 2SnameVARCHAR2( -)DEFAULT 'Jerry'; ①3 BEGIN4 SELECTEname intoSname fromEmpWHEREEmpno=7934; ②5Dbms_output.put_line (sname);6 END;

Code parsing:

When the ① variable is initialized, the variable can be initialized with the default keyword.

② uses the Select...into statement to assign a value to the variable sname, which requires that the result of the query must be a row, not multiple lines, or no records.

※ Declaration Constants

Constants are given an initial value when declared, and are not allowed to be re-assigned at run time. Declare constants using the constant keyword.

Code Demo: Declaring constants

Sql> DECLARE 2 PiCONSTANT Number:=3.14;--Pi Long value ①3R Number DEFAULT 3;--radius of Circle Default value 3 ②4Area Number;--area. 5 BEGIN 6Area=Pi*R*R--Calculate Area7Dbms_output.put_line (area);--the area of the output circle8 END;

Code parsing:

① Use the keyword constant when declaring a constant, a constant value can be assigned using the assignment operator (: =), or you can use the default keyword to assign a value.

In Sql*plus, you can also declare a session (the session, which is a client from the connection to the exit process called the current user. Global-level variables, which work throughout the session, are called host variables . The host variable is referenced with a ": variable name" in PL/SQL Reference.

Code Demo: Host Constants

SQL>varvarchar($); ①sql>BEGIN2 SELECT  into  from WHERE empno=7499; ②3END;

Code parsing:
① can declare host variables using VAR.
When accessing a host variable in ②pl/sql, add ":" before the variable.

PL/SQL (ii) PL/SQL blocks