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

I. the morphology of PL/SQL chunks

Letters, numbers, spaces and special symbols can be used in PL/SQL, including:

Identifiers-variables, keywords, reserved words

Separator-semicolon, comma, plus or minus sign, etc.

Constants-strings, numbers, Boolean variables

Comments-single-line comments, multi-line comments

When writing PL/SQL programs, you need to pay attention to the following two points:

1. Characters and dates must be referenced in single quotation marks, and double quotes can be quoted in the middle of single quotation marks.

two。 Numbers can be separate numbers, or they can be used in scientific counting.

In order to develop good programming, it is best to use the tab key to indent the code, and the key line returns to another line, so as to make the program easier to read and the logical structure clearer.

II. Notes

PL/SQL, like other programming languages, often requires annotations. Comments are automatically ignored when some programs are executed. There are two main types of comments:

1. Single-line comment: use-- to indicate that the scope of the comment is limited to the line on which the comment is located

two。 Multiline comments: use / * / to indicate that the scope of the comment is all lines contained in / * /.

As in the following example, both single-line comments and multiline comments are used:

SQL > edit

DECLARE v_annual_sal NUMBER (9 Compute the annual salary based on themonthly salary input from the user 2); monthly_sal NUMBER;BEGIN / * Compute the annual salary based on themonthly salary input from the user * /-- used multiline comments monthly_sal: = 3000; v_annual_sal: = monthly_sal * 12 -- The following line displays theannual salary-- single-line comment DBMS_OUTPUT.PUT_LINE (v_annual_sal) is used; END;/

SQL > /

36000PL/SQL procedure successfullycompleted.

3. The application of SQL function in PL/SQL.

PL/SQL and SQL are seamless, so many functions of SQL can be referenced directly in PL/SQL, such as one-line functions in SQL can be used directly, but some GROUP functions and DECODE functions cannot be used in PL/SQL. However, PL/SQL has its own conditional control statement, which allows you to do more flexible operations.

Here are a few examples of the use of the SQL function in PL/SQL:

SQL > edit

DECLARE v_desc_size INTEGER (5); v_prod_description VARCHAR2 (70): = 'Youcan use this product with your radios for higher frequency';BEGIN v_desc_size: = LENGTH (v_prod_description);-- the SQL function LENGTH () DBMS_OUTPUT.PUT_LINE (v_desc_size) is used here; END;/

SQL > /

62 PL/SQL procedure successfully completed.

It should be noted that before Oracle11g, the method of using incremental function (Sequences) in PL/SQL was:

DECLARE v_new_id NUMBER;BEGIN SELECT my_seq.NEXTVAL INTO v_new_idFROM Dual;-need to be written in full form END

But after Oracle11g, the increment function is simplified to the following form:

DECLARE v_new_id NUMBER;BEGIN v_new_id: = my_seq.NEXTVAL;-- can be written in a simplified form END

IV. Conversion of data types

The data to be converted in PL/SQL must be similar, for example, the number 123 and the string '123' can be converted to each other, but 'abc' cannot be converted into numbers.

There are two main types of data type conversions in PL/SQL:

1. Implicit conversion-implicit conversion means that data is converted automatically. It is important to note, however, that automatic conversion is subject to the NLS environment variable, which may not be possible if the environment variable is not configured accordingly, so this approach is not recommended.

Let's look at an example of data type conversion:

SQL > edit

DECLARE a_number NUMBER;BEGIN a_number: = '125';--' 125' is a string a_number: = a_number + 3;-- '125' is automatically converted to the number DBMS_OUTPUT.PUT_LINE (to_char (alphanumeric number 9999')) -- the function to_char is used for display conversion END;/

SQL > @ notes/s1.sql

128PL/SQLprocedure successfully completed.

two。 Explicit conversion-use a dedicated function to convert. Such as TO_CHAR,TO_DATE, TO_NUMBER, TO_TIMESTAMP and other functions.

To make a date conversion:

SQL > edit

DECLARE v_date_of_joining DATE;BEGIN v_date_of_joining: = TO_DATE ('February02,2000','Month DD, YYYY');-- use the function TO_DATE to explicitly convert DBMS_OUTPUT.PUT_LINE (v_date_of_joining); END;/

SQL > /

02-FEB-00PL/SQLprocedure successfully completed.

5. Nesting of blocks

a. Where nested blocks can be placed:

PL/SQL can nest other blocks, and nested blocks can be placed in the following two sections:

1. The executable part, that is, BEGIN

two。 The exception handling part, namely the EXCEPTION part.

It is important to note that nested blocks should not exceed 3 layers at most.

b. Range of variables (SCOPE):

Inner nested blocks can refer to variables declared in outer blocks

However, the outer block cannot refer to variables declared in the inner block.

Let's look at an example of a variable reference scope:

SQL > edit

DECLARE v_outer_variable VARCHAR2 (20): = 'GLOBAL _ VARIABLE';-- variable BEGIN DECLARE v_inner_variable VARCHAR2 (20): =' LOCAL VARIABLE' declared in the outer block -- declare the variable BEGIN DBMS_OUTPUT.PUT_LINE (v_inner_variable) in the inner block;-- functions in the inner block can reference the variable DBMS_OUTPUT.PUT_LINE (v_outer_variable) declared by the inner block -- the function of the inner block can also reference the variable END; DBMS_OUTPUT.PUT_LINE (v_outer_variable) declared by the outer block;-- the outer block can only reference the variable END that declares the outer block.

SQL > /

LOCALVARIABLEGLOBAL_VARIABLEGLOBAL_VARIABLEPL/SQLprocedure successfully completed.

If you modify it, refer to the variables of the inner block in the outer block:

SQL > edit

DECLARE v_outer_variable VARCHAR2 (20): = 'GLOBAL _ VARIABLE';BEGIN DECLARE v_inner_variable VARCHAR2 (20): =' LOCAL VARIABLE';BEGIN DBMS_OUTPUT.PUT_LINE (v_inner_variable); DBMS_OUTPUT.PUT_LINE (v_outer_variable); END; DBMS_OUTPUT.PUT_LINE (v_outer_variable) DBMS_OUTPUT.PUT_LINE (v_inner_variable); END;/

SQL > /

DBMS_OUTPUT.PUT_LINE (v_inner_variable); * ERRORat line 11:ORA-06550:line 11, column 23:PLS-00201:identifier 'Variable11, column 2:PL/SQL:Statement ignored-- reported an error, indicating that the variable declared by the inner block cannot be referenced

c. Visibility of variables (visibility)

If a duplicate name occurs when a variable is declared, the nearest principle is adopted, the local variable is preferred, and the variable in the outer layer block is overwritten (invisibility).

Let's take a look at an example of variable name renaming:

SQL > edit

-- Example about variable scope and visibility in nest blocks DECLARE v_father_name VARCHAR2 (20): = 'Patrick'; v_date_of_birth DATE: =' 20 BEGIN DECLARE v_child_name VARCHAR2 April 1972;-- defines the father's name and birthday BEGIN DECLARE v_child_name VARCHAR2 (20): = 'Mike'; v_date_of_birth DATE: =' 12 Mike'; v_date_of_birth DATE 2002' -defines the son's name and birthday BEGIN DBMS_OUTPUT.PUT_LINE ('Father''sName:' | | v_father_name); DBMS_OUTPUT.PUT_LINE ('Date ofBirth:' | | v_date_of_birth) -- the father's name is quoted here-- but according to the nearest principle, the son's birthday DBMS_OUTPUT.PUT_LINE ('Child''sName:' | | v_child_name) is quoted;-- the son's name END; DBMS_OUTPUT.PUT_LINE ('Date of Birth:' | | v_date_of_birth) is quoted. Memory is freed after the execution of the inner block, which refers to the father's birthday END;/.

SQL > /

Father'sName: PatrickDateof Birth: 12murDECMur02-father's name, son's birthday Child'sName: MikeDateof Birth: 20-APR-72-son's name, father's birthday PL/SQLprocedure successfully completed.

d. Use qualifier tags to reference variables

If you want the inner block to refer to the variable of the outer block, use the label when declaring, and specify the label as the modifier when referencing, then you can avoid variable overwriting.

Take a look at the following set of examples:

SQL > edit

BEGIN-- uses the label DECLARE v_father_name VARCHAR2 (20): = 'Patrick'; v_date_of_birth DATE: =' 20 Muhami 'Aprmai 1972' begin DECLARE v_child_name VARCHAR2 (20): = 'Mike'; v_date_of_birth DATE: =' 12 Murray Decmur2002' BEGIN DBMS_OUTPUT.PUT_LINE ('Father''sName:' | | v_father_name); DBMS_OUTPUT.PUT_LINE ('Date ofBirth:' | | outer.v_date_of_birth);-- when referencing a variable, it indicates that it is a variable in the outer block. DBMS_OUTPUT.PUT_LINE ('Child''sName:' | | v_child_name); DBMS_OUTPUT.PUT_LINE ('Date ofBirth:' | | v_date_of_birth);-- the variable referenced here is the variable END;END; END outer of the inner block

SQL > /

Father'sName: PatrickDateof Birth: 20-APR-72Child'sName: MikeDateof Birth: 12murDECMur02Mel-this time the father's name and birthday, the child's name and birthday can match PL/SQLprocedure successfully completed.

Of course, you can also use outer and inner tags at the same time

SQL > edit

BEGIN-- uses the outer tag DECLARE v_father_name VARCHAR2 (20): = 'Patrick'; v_date_of_birth DATE: =' 20 Patrick'; v_date_of_birth DATE-use the inner tag DECLARE v_child_name VARCHAR2 (20): = 'Mike'; v_date_of_birth DATE: =' 12murDeMel 2002' BEGIN DBMS_OUTPUT.PUT_LINE ('Father''sName:' | | v_father_name); DBMS_OUTPUT.PUT_LINE ('Date ofBirth:' | | outer.v_date_of_birth);-- variable DBMS_OUTPUT.PUT_LINE ('Child''sName:' | | v_child_name) that is specified as outer when referenced. DBMS_OUTPUT.PUT_LINE ('Date ofBirth:' | | inner.v_date_of_birth);-- variable END;END;ENDouter;/ that is specified as inner when referenced

SQL > /

Father'sName: PatrickDateof Birth: 20-APR-72Child'sName: MikeDateof Birth: 12-DEC-02-- PL/SQLprocedure successfully completed is consistent with the last execution result.

In addition to being explicitly specified, qualifiers can also use the package name and the name of the Procedure. Using qualifiers to refer to variables is a good programming habit to prevent confusion and conflicts.

6. Operators in PL/SQL

The operators in PL/SQL mainly include the following categories:

1. Logical operators: such as AND, OR, and NOT

two。 Arithmetic operators: such as +, -, *, /, and exponential operators * *

3. Connector to connect multiple strings, such as | |

The operation rules of PL/SQL are the same as those of SQL, for example, the priority of * and / is higher than + and -, etc. For security reasons, the order of operations can be forced to be controlled by parentheses.

General rules of PL/SQL programming

For PL/SQL programming, it is recommended to follow the following general rules:

1. Comment the code

two。 Uniform case rules (keyword uppercase, self-defined variable lowercase)

3. Determine naming rules, such as declaring variables starting with v _

4. Through reduction to enhance readability, many development tools integrate formatting tools that can be reduced automatically

(in vim, you can set automatic indentation through set autointent, and settabstop=# sets the amount of indentation, where # represents the number of characters to be indented)

5. Branches write code to make it easier to read.

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.