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

I. types of variables

There are the following main types of variables in PL/SQL:

Scalar: scalars, single variables, such as Integer,Character, etc., are the most common and widely used

Composite: compound variables with multiple seed structures, such as arrays, are also widely used

Reference: reference type, similar to a pointer, pointing to a type

Large object (LOB): CLOB (CharacterLarge Object, storing documents), BLOB (Binary Large Object, storing binary files, such as pictures, sounds, etc.)

Non-PL/SQL variables: Bindvariables: bind variables, environment variables. The so-called environment refers to the environment allowed by PL/SQL, because PL/SQL can run in C language, JAVA language and other languages. C language and JAVA language is the environment of PL/SQL. If there is an environment, it is necessary to define and configure the environment. This is the role of this variable.

II. Standards for declaring and initializing variables

Variable declaration and initialization should follow the following standards

1. Follow the naming convention, which is good for the maintainability of the code

two。 See the name and know the meaning, use a meaningful name

3. Each line defines a variable for the maintenance of the code

4. Initialize variables defined as non-empty or constant

5. Use ": =" or "DEFAULT" to assign initial values

6. Do not use column names as variable names to avoid confusion: if a variable is the same as a column name, PL/SQL first interprets it as a column name. Such as the following example:

SQL > edit

DECLARE employee_id NUMBER (6); BEGIN SELECT employee_id INTO employee_id-the variable name and column name are duplicated, so it is easy to get confused FROM employees WHERE last_name = 'Kochhar'; DBMS_OUTPUT.PUT_LINE (' The resultis'| | employee_id); END

SQL > /

Theresult is 101PL/SQLprocedure successfully completed.

Where the variable name and column name coincide, although sometimes it can be run, but there is no guarantee that the result is correct.

7. If constraint is defined as NOT NULL, an initial value must be assigned to it when declared.

Third, detailed explanation of various types of variables

1. Scalar Variable scalar

The Scalar variable is the most widely used variable. It contains only a single value and has no more complex internal structure, such as:

TRUE--Boolean

25-JAN-01--Date

256120.08--Number

"Atlanta"-String

"The Soul ofthe lazy man desires, and he has nothing; but the soul ofthe diligent shall bemade rich."-- String (if this value is not long, for example, CHAR or VARCHAR can usually store up to 2000 to 4000 characters, it can be used as a string, otherwise it is best to be a CLOB type, that is, an unstructured document)

The common Scalar variables are as follows:

CHAR [(maximum_length)]-the length is immutable

VARCHAR (maximum_length)-variable length

NUMBER [(precision,scale)]

BINARY_INTEGER

BINARY_FLOAT

BINARY_DOUBLE

PLS_INTEGER

BOOLEAN

DATE

-the following categories are actually stored as numbers internally-

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

The following example demonstrates how to define a Scalar variable:

DECLARE v_emp_job VARCHAR2 (9) v_count_loop BINARY_INTEGER: = 0; v_dept_total_sal NUMBER (9): = 0; v_orderdate DATE: = SYSDATE + 7; c_tax_rate CONSTANT NUMBER (3): = 8.25; v_valid BOOLEAN NOTNULL: = TRUE;.

Boolean variables in Scalar variables have the following characteristics:

1. The Boolean variable in Oracle is different from other programming languages in that it has null values in addition to TRUE and FALSE.

two。 When determining conditions, you can use logical operators: AND, OR, and NOT

3. Numbers, characters, and expressions can all return Boolean values.

2. Bind Variable binding variable

Binding variables are also known as environment variables, and environment variables in PL/SQL have the following characteristics:

1. Created in a specific environment: as in C, or in Java

two。 Because it is created in the environment, it is also called a host variable

3. If it is used in sql*plus, define it with the keyword VARIABLE

4. It can be used in SQL statements or in PL/SQL blocks

5. Even if the PL/SQL block execution ends, the environment variable can still be accessed, because even if the program ends, the environment will not end.

6. When referencing an environment variable, precede it with a colon ":"

Here is an example of how to use binding variables:

SQL > VARIABLE b_salary NUMBER

Define environment variables in sql*plus, using the VARIABLE keyword

SQL > edit

BEGIN SELECT salary INTO: b_salary-- when referencing the binding variable b_salary in the PL/SQL block, you need to precede the variable with the colon FROM employees WHERE employee_id = 178

SQL > PRINT b_salary

-- after the PL/SQL block has been run, you can still access the variable bounded salary. PRINT-PRINT is a command of sql*plus and is not in the PL/SQL block, so there is no need to use a colon to refer to this variable.

SQL > SELECT first_name, last_name FROMemployees WHERE salary =: b_salary

-- because b_salary is an environment variable, it can also be accessed; here the SQL statement is executed, so you need to use colons to refer to FIRST_NAME LAST_NAME-Oliver TuvaultSarath SewallKimberely Grant

If autoprint is set to open, the results can be automatically printed to the screen

SQL > VARIABLE b_emp_salary NUMBER

SQL > SET AUTOPRINT ON

# set automatic printing function

SQL > edit

1 DECLARE 2 v_empno NUMBER (6): = & empno; 3 BEGIN 4 SELECT salary 5 INTO: b_emp_salary 6 FROM employees 7 WHERE employee_id = END; empno; 8 END; 9 / Entervalue for empno: 178old 2: v_empno NUMBER (6): = & empno;new 2: v_empno NUMBER (6): = 178pr 178old SQL procedure successfully completed. Bond EMPRES SALARY-7000

3. LOB Large Object data type

The main types of data of LOB type are as follows:

A. CLOB-- is used to store strings

B. BLOB-- is used to store binary files, such as pictures, etc.

C. BFILE-- is stored on disk because of its large size, and only pointers are stored in the database, not the file itself, so its access speed may be faster than that of BLOB.

D. NCLOB-- is used to store words that are not English letters

4. Composite compound variable

Compound variables can store a variety of other types of variables, such as RECORD, AREA,TABLE and other variables, including Boolean, date, character, lob and other variables.

4. The function of% TYPE

% TYPR is a property unique to PL/SQL. For example, the following statement:

SELECT first_name

INTO v_fname

Where the values of fist_name and v_fname are the same column, in order to avoid hardcode, you can use% TYPE to write the code in the program, and use% TYPE when declaring a variable to indicate that the data type of the variable is the same as that of a column of a table in the database.

% TYPE can be used in the following two situations:

1. The same type as a column in the database

two。 The same as the other one that has already declared the variable type.

Prefixes are required when using% TYPE, which can be:

1. Table and column names in the database

two。 The name of the variable that has been defined.

The format of using% TYPE to define variables is as follows:

Identifier table.colum_name%TYPE

Here is an example of the use of% TYPE:

Example 1.

... Emp_lname employees.last_name%TYPE;-- defines a variable emp_lname whose data type is the same as the last_name column in the employees table.

Example 2.

... Balance NUMBER (7 min_balance 2);-- defines the first variable balance, whose data type is NUMBER min_balance balance%TYPE:=1000;-- defines the second variable, min_balance, whose data type refers to the data type of the previous variable balance.

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.