PL/SQL Basic syntax

Sometimes we need to do some slightly more complex operations on the data in the database, and these operations are all one-time and are no longer used after they are exhausted.

It is too cumbersome and wasteful to use stored procedures, and to be deleted when it is exhausted. and a single SQL cannot meet the requirements. This is the time to use the SQL statement block.

If you are using an Oracle database, you can use PL/SQL (Procedure language/sql), which is a procedural query language. This is the third generation language. The SQL we use is Structured Query language, which belongs to the fourth generation language.

PL/SQL enables more complex logical operations, as we do with high-level languages such as java,c. However, if it is in the Mysql/sqlserver database, then PL/SQL is not available (PL/SQL is a process query language belonging to Oracle).

If you want to implement complex logical queries in Mysql/sqlserver, you can only do this by writing stored procedures.

I. The structure of PL/SQL

[DECLARE]--variable declaration (can be omitted)-such as: My_var varchar; begin--SQL Statement
[Exception]  --Exception declaration (can be omitted) end;

For example:

Begin  for rec in (select Fgwwh, sid$ from T_yw_temp)  loop      update t_yw_gcjs_lxsq lxsq      Set lxsq.sid$ = Rec.s id$      where lxsq.fgwwh = REC.FGWWH;          commit;    End Loop;end;

II. Basic rules of PL/SQL

1. Identifiers are case-insensitive, and all names are automatically changed to uppercase when they are stored.

2, identifiers only allow letters, numbers, underscores, and start with a letter.

3. You cannot use reserved words, and must be enclosed in double quotation marks with the same name as reserved words.

4, end needs to use a semicolon.

5, character types and date types need to be enclosed in single quotes.

Suggested writing Specifications:

1, the name should be "_" connection, rather than mixed case, such as: p_id (the name is ID, "p" means that it is a passed in parameters).

2, the variable before the best prefix, to indicate the variable data type, scope of action, and so on.

3. Each variable should be annotated.

4, it is recommended to use 3 half-width space instead of tab to indent.

5, after the comma and before and after the operator should be preceded by a space.

Iii. comments on PL/SQL

--Single line comment

/* Multiline Comment */

Iv. Declaration of variables

The syntax structure is as follows:

variable_name datatype [[not NULL] {: = | DEFAULT} expression];

There are two of the most common combinations of syntax structures:

The first is to declare variables directly, without assigning values, such as:

V_ID number;

Second, declare variables and assign values, such as:

V_ID number: = 22;   

When defining types for variables, in addition to the types (number, Vchar, LONG, DATE, TIMESTAMP) that can be defined as a database, you can also directly type the type of a field in the database as a variable, such as:

V_productid Productinfo.productid%type;  --If you need to assign a value, add ": =" at the back.

The above variable declarations are the most commonly used variable declarations, and of course there are other more complex types of variables, but they are not commonly used and are not described here.

V.if condition control statement

There are three ways to use the IF statement: If ..., if .... ELSE ...., IF ... ELSEIF .... Three different ways.

1. If structure

IF condition then statments; END IF;

2. IF ... ELSE ... Structure

IF condition then statments; ELSE statments; END IF;

3. IF ... ELSEIF ... Structure

IF condition then statements; ELSEIF statements; [ELSE statements] END IF;

Six, case control statements

1. Simple Case Statement

Syntax format:

Case Case_operandwhen When_operand then .... When the When_operand then ... END case;

Such as:

DECLARE    V_categoryid VARCHAR2 (12); BEGIN   SELECT category into V_categoryid from productinfo WHERE productid = ' 001 ';   Case V_categoryid "   001" then       dbms_output. Put_Line ("CATEGORY 001");   When the ' 002 ' then      dbms_output. Put_Line ("CATEGORY 002");   END case; END;

2. Search-Type Case statement

Syntax format:

Casewhen Boolean_expression then statement; When Boolean_expression then statement; END case;

Examples are as follows:

Casewhen v_productprice <= V_productproce then--sql statement when V_productprice > <=--sql statement: .. ELSE--sql statement end case;

Seven, Loop loop control statement

The Loop statement has the following four types:

· LOOP

· While ... LOOP;

' For ... LOOP;

' CURSOR for Loop;

1, the basic loop

<<basic_loop>>loop--sql statement EXIT basic_loop when ...; END LOOP;

Where <<basic_loop>> is the label of the Loop statement.

2, while ... Loop statement

While Boolean_expressionloop statement ... END LOOP;

3. For ... Loop statement

For index_name in [REVERSE] lower_bound. Upper_boundloopstatement ... END LOOP;

Indicates that index_name is incremented from Lower_bound to Upper_bound, similar to a for loop.

Where reverse means that the loop is reduced from upper_bound to Lower_bound.

For example:

DECLARE   v_num Number (8): = 0; BEGIN for   inx in 1..20 LOOP v_num: = V_num + inx;   END LOOP; END;

where Lower_bound and upper_bound to use ".." Connection.

Eight, exception handling

Defines how to handle a DML statement when an exception occurs. For example:

DECLARE   V_catgid VARCHAR2 (Ten): = 0;   V_bol BOOLEAN: = TRUE; BEGIN   SELECT CATEGORYID to V_catgid from   categoryinfo      EXCEPTION when       no_data_found       then ....      When ...  Then
      ...
      When OTHERS Then
      ... END;

Exceptions in Oracle can be categorized into three categories:

① pre-defined anomalies;

② non-pre-defined anomalies;

③ a custom exception.

Where predefined exceptions refer to Oracle's well-defined exceptions, which we can call directly, the commonly used pre-defined exceptions are:

As for non-pre-defined exceptions and custom exceptions do not introduce here.　　　　　　

PL/SQL Basic syntax