PL/SQL is an abbreviation for Procedure Language & structured Query Language
PL/SQL is an extension of the language stored procedure
The PL/SQL program consists of three blocks, the declaration part , the execution part , and the exception handling part .
In the declaration section of a statement block, a variable declaration is declared, and the syntax for declaring a variable is:
variable_name [constant] type [NOT NULL] [: =value]
which
Variable_name is the variable name
Type is types
Value is the initial value of the variable
PL/SQL structure
DECLARE
/* Declaration section: The variables, types and cursors used in this declaration, and the local stored procedures and functions */
BEGIN
/* Execute part: Process and SQL statement, which is the main part of the program */
EXCEPTION
/* Execute exception section: Error handling */
END;
The Process Control Statements for PL/SQL include the following three classes:
Conditional statement: IF Statement Case statement
Loop statement: Loop statement, exit statement
Sequential statement: Goto statement, NULL statement
The basic form of the if statement is:
IF < Boolean expressions > Then
PL/SQL and statements
END IF;
Or
IF < Boolean expressions > Then
PL/SQL and statements
ELSE
Other statements
END IF;
The complex form of the
if statement is:
if < Boolean expression > Then
pl/sql and SQL statements
elsif < other Boolean expressions > Then
other statements
elsif < other Boolean expressions > Then
other statements
...
else
other statements
end IF;
the basic form of the case statement is:
case selector
when expression1 then result1
when Expression2 then result2
when expressionn then resultn
[ELSE resultn+1]
end case;
Looping statements
The general form of a simple loop statement :
LOOP
The statement to execute;
Exit when < conditional statement >/* condition satisfies, exits loop statement */
END LOOP;
Where: The EXIT when clause is required, otherwise the loop cannot be stopped.
The general form of a while Loop statement :
While < Boolean expressions > LOOP
The statement to execute;
END LOOP;
General form of a for Loop statement :
For loop counter in [REVERSE] lower limit: Upper LOOP
The statement to execute;
END LOOP;
which
The loop variable is automatically added 1 per loop, and the loop variable is automatically minus 1 using the keyword reverse.
The numbers that follow the in REVERSE must be in order from small to large, but not necessarily integers, which can be variables or expressions that convert to integers.
You can use the exit when clause to exit a loop
Comprehensive Example:
1 Declare2V_no Number(4) :=7369; --declaration and initialization of variables3 begin4 SelectSal intoV_no fromEmpwhereEmpno=V_no;5 6 ifV_no> - ThenV_no:= -;--If--then7 End if;8 9 whileV_no<= $ -- whileTen Loop OneV_no:=V_no+1; A Dbms_output.put_line (v_no); - EndLoop; - the forV_noinch Reverse 0.. $-- for -Loop--Loop - - Dbms_output.put_line (v_no); + - EndLoop; + End;
PL/SQL program design, Process Control