PL/SQL program design, Process Control

Source: Internet
Author: User
Tags case statement

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.