4.pl_sql--variable declaration and initialization, string delimiter

I. The role of variables

Since PL/SQL is a programming language, variables are bound to be used. Similar to other programming languages, the variables in PL/SQL have the following effects:1. used for temporary storage of data ; 2. used to manipulate data;3. reusing data.

For example, in the previous section cited, the variable is used:

DECLARE

V_fname VARCHAR2 (20);

-- declaring Variables

BEGIN

SELECT first_name

Into v_fname

-- the SELECT the data to which the statement is queried is stored to the variable v_fname in

From Employees

WHERE employee_id = 100;

Dbms_output. Put_Line (' The result is ' | | v_fname);

-- use stored in variable v_fname the data in

END;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/A6/wKioL1PpJYHRyCJqAAEfGqlhLA4353.jpg "title=" 1.png " alt= "Wkiol1ppjyhrycjqaaefgqlhla4353.jpg"/>

Ii. naming rules for variables

the naming conventions in PL/ SQL include:

1. must start with a letter;

2. can contain letters and numbers;

3. can contain special characters, such as $, _ and #. Note that _ can be used, but $ and # are best to use as much as possible , because in other programming languages it is rarely allowed to use the $ and #, in order to develop good programming habits, it is best not to use these two symbols;

4. the length of the variable should not exceed five characters;

5. Keywords and reserved words cannot be used.

Iii. How to use variables

the use of variables in PL/SQL is as follows:

1. variables need to be defined in the DECLARE section, variables can be initialized in the DECLARE section, or uninitialized, depending on the needs of the program.

2. in the Executive section, the BEGIN section assigns new values and uses;

3. can be used as a parameter in the subroutine;

4. can also be used to store the program output results.

The following example illustrates the use of variables using the previous examples:

Sql> Edit

DECLARE v_fname VARCHAR2 (20);--Declares a variable v_fname, but does not initialize BEGIN SELECT first_name into v_fname--variable v_fnam used to store SE      Query results for Lect statements from employees WHERE employee_id = 100; Dbms_output.   Put_Line (' Theresult is ' | | v_fname);--variable V_fnam is passed as a parameter to the method END; /

The result is Stevenpl/sql procedure successfullycompleted.

Iv. declaration and initialization of variables

variables need to be declared, initialization can be done at the time of declaration, or not at this time, but if the declaration is defined as not null , it must be initialized to be used.

the format of the variable declaration is:

Identifier [Constant]datatype [Not NULL] [: = | DEFAULT expression]

The contents of the brackets here are optional, meaning the following:

1. CONSTANT represents a constant, and once defined as a constant, it is not possible to assign other values to the variable;

2. if [not NULL] is defined, the variable must be assigned a value;

3. The assignment in PL/SQL Uses: =, default can also be used to define defaults when initializing.

Take a look at some specific examples:

        DECLARE             v_hiredate  DATA;              --here only declares no initialization             v_deptno     number (2)  NOT NULL := 10;       The        --  declaration is defined as  not null, so it must be initialized, assigned a value of 10, or it can be written in               -- v_deptno   number (2)  NOT NULL DEFUALT 10;             v_location   varchar2  :=  ' Atlanta '        The        --  declaration is also initialized              c_comm     constant  number  := 1400;              --is defined as a constant when declared, meaning that no other value can be assigned to it in the future.

Let's take a look at two examples:

Example 1.

Sql> DECLARE

      v_myname varchar (; )                --  declaration only, no initialization    begin      dbms_output. Put_Line (' my name is:  '  | |  v_myname);                 --  is using an uninitialized variable v_myname      v_myname :=  ' John ';                 --  for variable v_myname  Assign a value of       dbms_output. Put_Line (' my name is:  '  | |  v_myname);                 --  uses the variable   end after the assignment; 

Sql>/

Myname is:--because there is no initialization, so there is no result Myname is:john------the variable is used after the assignment, can display the value of the variable pl/sqlprocedure successfully c ompleted.

Example 2.

Sql>

DECLARE v_myname VARCHAR2 (): = ' John '; --Declare the variable at the same time as it initializes the BEGIN dbms_output.                Put_Line (' My name is: ' | | v_myname);   -Note that the variable is deliberately written in lowercase to verify that the case-insensitive END is not in PL/SQL; /

Myname Is:johnpl/sqlprocedure successfully completed.

Five, string Delimiter

quotation marks are often used when printing output results, but the single quote ' is sometimes used as a delimiter, such as I ' m , in order to avoid confusion,the Q is used in PL/SQL To make a reference to the delimiter:q means quotes, plus single quotes ', followed by any pair of symbols, can be quoted as a single quote function. Look at the following example:

Sql> Edit

DECLARE v_event VARCHAR2 (15); BEGIN v_event: = Q '!       Father ' s day! '; --Use Q ', and then add the pair!, you can avoid the delimiter ' as a single quote dbms_output.      Put_Line (' 3rd Sunday in June are: ' | | v_event);       V_event: = Q ' [mother ' s Day] '; --pairs of special symbols can be used here is a pair of brackets [] dbms_output.   Put_Line (' 2nd Sunday in could be: ' | | v_event); END;

Sql>/

3rdSunday in June Is:father's day2ndsunday in May Is:mother ' s daypl/sqlprocedure successfully completed.

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1538823