Shulou(Shulou.com)06/01 Report--

1. Type

In the article ".net programmer Learning Oracle Series (5): three big data types", the basic data types of Oracle are described in detail. It is also mentioned that in addition to the basic data types, Oracle also supports some non-inherent numerical types in syntax.

In fact, Oracle syntactically supports much more than that, and Oracle also supports some complex and powerful data types. Such as attribute types% TYPE and% ROWTYPE, record types RECORD, collection types VARRAY and TABLE, etc. This section describes practical attribute types and flexible record types.

1.1, attribute type

An attribute type is a type that can directly refer to the data type of a column in the database to describe the variable type. Oracle provides two attribute types,% TYPE and% ROWTYPE, each of which is explained below.

% TYPE: this property allows you to refer to the data type of a column in the database or a previously declared variable in the declaration instead of hard-coding the type name. The% TYPE attribute can be used as the data type specifier when declaring constants, variables, and parameters. If the type of the reference is changed, the declaration will also be automatically updated, which is good for later code maintenance.

Example:

DECLARE v_staff_name tweets. END name% TYPEX begin SELECT t.staff_name INTO v_staff_name FROM demo.t_staff t WHERE TYPEX 5; DBMS_OUTPUT.PUT_LINE (v_staff_name); begin

% ROWTYPE: this property can represent the record type of the row of a table or cursor in the database. Variables declared with% ROWTYPE can store entire rows of data selected from a table or obtained from cursors or cursor variables, and the fields in the variable record have the same name and data type as the corresponding columns in each row.

Example:

DECLARE v_staff tweets% ROWTYPEX begin SELECT t.* INTO v_staff FROM demo.t_staff t WHERE t.promotional idles 5; DBMS_OUTPUT.PUT_LINE (v_staff.staff_name); END

To learn more about attribute types, refer to "Database PL/SQL User's Guide and Reference:% TYPE Attribute" and "Database PL/SQL User's Guide and Reference:% ROWTYPE Attribute".

1.2, record type

The record type is a composite structure composed of single-row and multi-column scalars. It can be seen as a user-defined data type that provides the ability to encapsulate one or more scalars into an object for operation. When using a variable of a record data type, you need to define the member variable of the record in the declaration section, and then reference the record variable itself or its members in the execution section. However, it is not possible to compare records as a whole, such as judging whether the variable of record type is NULL or not.

Example:

DECLARE TYPE staff_type IS RECORD (staff_name VARCHAR2 (50), gender VARCHAR2 (2)); v_staff staff_type;BEGIN SELECT t.playnamescoreDECODE (t.genderwag1 'male', 0 'female', 'bisexual') INTO vandalism. Recording namegramm.gender FROM demo.t_staff t WHERE t.encoding girls 5; DBMS_OUTPUT.PUT_LINE (v_staff.staff_name |'| v_staff.gender); END

The record type and the% ROWTYPE attribute are similar in purpose, except that the former is a custom structure, while the latter is a table structure, the former is more flexible and the latter is more convenient.

2. Variables

There is the concept of variables in general computer programming languages, and PL/SQL is no exception. Variables are used to store calculation results and represent variable states. This section will focus on how to define and assign variables in PL/SQL. In addition, there are constants in PL/SQL, but very few people use them, so interested readers can refer to "Oracle Database PL/SQL Language Reference: Constant".

2.1, variable type

There are many optional types for defining variables in PL/SQL, including the three basic data types common in Oracle and many non-inherent data types that Oracle supports syntactically. Data types that are not supported by Oracle itself, such as × × (INT/INTEGER) and Boolean type (BOOLEAN), but are available in PL/SQL.

2.2, variable definition

Defining variables in PL/SQL is essentially the same as defining variables in C#, except that variables in PL/SQL have to be defined centrally, the variable definition area must begin with the DECLARE keyword, and each line can only define one variable. If it is a SQL*Plus environment, it must start with VAR [IABLE].

Syntax:

Variable_name datatype [[NOT NULL] {: = | DEFAULT} expression]

If you use NOT NULL, you must assign an initial value to the variable. In addition, you need to follow the following naming rules when naming variables:

1. The variable name must begin with a letter.

2. The length of the variable name cannot exceed 30 characters.

3. The variable name cannot contain spaces.

4. The variable name in the same sentence block cannot be repeated.

5. The variable name cannot be the same as the column name in the query.

2.3. Variable assignment

Assigning a value to a PL/SQL variable is basically the same as assigning a value to a C# field. You can assign an initial value to a variable when you define it, or you can assign a value to it before use, and there will be a default value if it is not assigned. The only difference is that the default values are different for different types in C #, while the default values for all types in PL/SQL are the same, which is NULL.

Another strange thing in PL/SQL is that the assignment operator in all programming languages may be =, while the assignment operator in PL/SQL is: =. And the parameter writing method of Oracle-related API is also different from that of most other databases.

Example 1 (normal PL/SQL environment):

DECLARE v1 NUMBER; v2 NUMBER (5Power2); v3 NUMBER: = 50.20; v4 NUMBER (4): = 1998; v5 VARCHAR2 (4) DEFAULT 'Aids; V6 DATE NOT NULL: = fn_now;BEGIN v1: = 100; v2: = 99.99; v5: =' A5charm; V6: = SYSDATE; DBMS_OUTPUT.PUT_LINE (v1 |'| | v2 |'|'| v3 |'| | v4 |'| | v4 |'| | v5 | |'| v6); END |

Example 2 (SQL*Plus environment):

VARIABLE v1 NUMBERBEGIN: V1: = 12; DBMS_OUTPUT.PUT_LINE (: V1); END;/3, structure

Like ordinary programming languages, there are three common control structures and sequential control statements-GOTO in PL/SQL. This section will focus on the three widely accepted control structures. As for the unpopular GOTO statements, interested readers can refer to "Oracle Database PL/SQL User's Guide and Reference: Using the GOTO Statement".

3.1. Sequential structure

Sequential structure is the most basic, simple and commonly used program control structure in process-oriented programming. The sequential structure is used to represent several processing steps executed sequentially in the form of a linear structure, which goes down in one direction without turning. When in use, as long as the corresponding statements are written in the order in which the problem is solved, the execution order is from top to bottom and in turn.

3.2. Select structure

Two alternative structures are provided in PL/SQL, namely the IF structure and the CASE structure. There are three varieties of IF structure and two variants of CASE structure. Each selection statement is described one by one below:

Structural variation 1 of IF:

Syntax:

IF condition THEN {... statements to execute when condition is TRUE...} END IF

Example:

BEGIN IF 1 > 0 THEN DBMS_OUTPUT.PUT_LINE ('executed'); END IF;END

IF structural variant 2:

Syntax:

IF condition THEN {... statements to execute when condition is TRUE...} ELSE {... statements to execute when condition is FALSE...} END IF

Example:

BEGIN IF 1 > 2 THEN DBMS_OUTPUT.PUT_LINE ('The result is true'); ELSE DBMS_OUTPUT.PUT_LINE (' The result is false'); END IF;END

The third variant of IF structure:

Syntax:

IF condition1 THEN {... statements to execute when condition1 is TRUE...} ELSIF condition2 THEN {... statements to execute when condition2 is TRUE...} [ELSE {... statements to execute when both condition1 and condition2 are FALSE...}] END IF

Example:

BEGIN IF 1 > 2 THEN DBMS_OUTPUT.PUT_LINE ('1 > 2 branch'); ELSIF 1'80 THEN DBMS_OUTPUT.PUT_LINE ('excellent'); WHEN v_score > = 70 THEN DBMS_OUTPUT.PUT_LINE ('good'); WHEN v_score > = 60 THEN DBMS_OUTPUT.PUT_LINE ('medium'); ELSE DBMS_OUTPUT.PUT_LINE ('poor'); END CASE;END

Compared with multi-branch IF statements, CASE statements are more readable and efficient, so when there are more branches of the program, you should use CASE instead of IF as much as possible. The ELSE clause of the CASE statement is optional. However, if you omit the ELSE sentence, PL/SQL adds the following implicit ELSE clause to the CASE statement:

ELSE RAISE CASE_NOT_FOUND

In other words, if you omit the ELSE clause and the CASE statement does not match the WHEN clause, PL/SQL throws a predefined exception CASE_NOT_FOUND.

3.3. Cycle structure

Three loop structures are provided in PL/SQL, namely LOOP, WHILE LOOP, and FOR LOOP. In addition, PL/SQL provides an EXIT statement to exit the current loop. Each loop statement is described one by one below:

LOOP cycle

Syntax:

LOOP {... statements...} EXIT [WHEN boolean_condition]; END LOOP

Example 1:

DECLARE v_counter BINARY_INTEGER: = 0 EXIT WHEN v_counter begin LOOP v_counter: = v_counter + 1; DBMS_OUTPUT.PUT_LINE (v_counter);-- output result: 1, 2, 3, 4, 5, 6, 7, 8, 9 IF v_counter > = 9 THEN EXIT; END IF;-the above IF statement block can also be replaced by "EXIT WHEN v_counter > = 9;"

Example 2 (nested loop):

DECLARE I BINARY_INTEGER: = 0; j BINARY_INTEGER: = 0; begin LOOP I: = I + 1; j: = 0; LOOP j: = j + 1; DBMS_OUTPUT.PUT_LINE ('iposit j = (' |'* | | j |') ='| iAgj); EXIT WHEN j > = 3; END LOOP; EXIT WHEN i > = 4; END LOOP;END

Example 3 (tag loop):

DECLARE I BINARY_INTEGER: = 0; j BINARY_INTEGER: = 0; begin LOOP I: = I + 1; j: = 0; LOOP j: = j + 1; DBMS_OUTPUT.PUT_LINE ('iposit j = (' |'*'| | j |') ='| EXIT inner_loop WHEN j > = 3; EXIT outer_loop WHEN I > = 4; END LOOP inner_loop; END LOOP outer_loop;END

WHILE LOOP cycle

Syntax:

WHILE conditionLOOP {... statements...} END LOOP

Example:

DECLARE v_score NUMBER (3): = 0 begin WHILE v_score < 60 LOOP v_score: = v_score + 10; DBMS_OUTPUT.PUT_LINE (v_score);-- output result: 10, 20, 30, 40, 50, 60 END LOOP; DBMS_OUTPUT.PUT_LINE ('over'); END

FOR LOOP cycle

Syntax:

FOR loop_counter IN [REVERSE] lowest_number..highest_numberLOOP {... statements...} END LOOP

Example 1 (forward loop):

BEGIN FOR i IN 3... 7 LOOP DBMS_OUTPUT.PUT_LINE (I);-output result: 3, 4, 5, 6, 7 END LOOP;END

Example 2 (reverse loop):

BEGIN FOR i IN REVERSE 3... 7 LOOP DBMS_OUTPUT.PUT_LINE (I);-output result: 7, 6, 5, 4, 3 END LOOP;END

Note: counters (variables) in the FOR LOOP loop can be read, but cannot be modified. In addition, the EXIT and loop tags used in the example of the LOOP loop can also be used in the WHILE LOOP loop and FOR LOOP loop

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.