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

I. the function of variables

Since PL/SQL is a programming language, variables are bound to be used. Similar to other programming languages, variables in PL/SQL mainly have the following functions: 1. Use incoming storage data; 2. Used to manipulate data; 3. Reuse data.

For example, in the example given in the previous section, variables are used:

DECLARE

V_fnameVARCHAR2 (20)

-- declare variables

BEGIN

SELECT first_name

INTO v_fname

-- store the data queried by the SELECT statement in the variable v_fname

FROM employees

WHERE employee_id = 100

DBMS_OUTPUT.PUT_LINE ('The result is' | | v_fname)

-- using data stored in the variable v_fname

END

II. Naming rules of variables

The main naming rules in PL/SQL are:

1. Must start with a letter

two。 Can contain letters and numbers

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

4. The length of a variable should not exceed 30 characters.

5. Keywords and reserved words cannot be used.

Third, the use of 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.

two。 In the execution part, that is, the BEGIN section assigns new values and uses the

3. It can be used as a parameter in a subroutine.

4. It can also be used to store the output of the program.

Let's demonstrate the use of variables in combination with the previously used examples:

SQL > edit

DECLARE v_fname VARCHAR2 (20);-- the variable v_fname is declared, but BEGIN SELECT first_name INTO is not initialized-the variable v_fnam is used to store the query results of the SELECT statement, FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE ('Theresult is' | | v_fname);-- the variable v_fnam is passed to the method END; / Theresult is StevenPL/SQL procedure successfullycompleted as a parameter.

Declare and initialize variables

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

The format of the variable declaration is:

Identifier [CONSTANT] datatype [NOT NULL] [: = | DEFAULT expression]

The contents in parentheses here are optional, and the specific meaning is as follows:

1. CONSTANT represents a constant. Once it is defined as a constant, you cannot assign another value to the variable.

two。 If [NOT NULL] is defined, the variable must be assigned a value

3. The assignment in PL/SQL uses: =. You can also use DEFAULT to define the default value during initialization.

Let's look at a few specific examples:

DECLARE v_hiredate DATA;-- it is only declared here that v_deptno NUMBER (2) NOT NULL: = 10;-- it is defined as NOT NULL when declared, so it must be initialized, assigned to 10, or written as-- v_deptno NUMBER (2) NOT NULL DEFUALT 10. V_location VARCHAR2 (13): = 'atlanta'-- initialize c_comm CONSTANT NUMBER: = 1400 at the same time as the declaration, which means that no other values can be assigned to it in the future.

Let's look at two examples:

Example 1.

SQL > DECLARE

V_myName VARCHAR (20);-- only declare that BEGIN DBMS_OUTPUT.PUT_LINE ('My name is:'| | v_myName) is not initialized;-- use the uninitialized variable v_myName v_myName: = 'John';-- assign DBMS_OUTPUT.PUT_LINE (' My name is:'| | v_myName) to the variable v_myName. -- using the assigned variable END

SQL > /

Myname is:-- because there is no initialization, there is no result Myname is: John-- use a variable after assignment to display the value of the variable PL/SQLprocedure successfully completed.

Example 2.

SQL >

DECLARE v_myName VARCHAR2 (20): = 'John';-initialize the BEGIN DBMS_OUTPUT.PUT_LINE (' Myname is:'| | v_myname) for the variable while declaring it;-- Note that the variable is deliberately written in lowercase to verify that the PL/SQL is case-insensitive END; / Myname is: JohnPL/SQLprocedure successfully completed.

5. String separator

Quotation marks are often used when printing output results, but single quotation marks are sometimes used as delimiters, such as Imam, in order to avoid confusion, PL/SQL uses Q to quote the separator: Q represents quotes, plus single quotation marks', followed by any pair of symbols, can play the role of single quotation marks. Look at the following example:

SQL > edit

DECLARE v_event VARCHAR2 (15); BEGIN v_event: = qexamples Fatherboys daylights;-- here we use qmarks, followed by paired!, to avoid treating the delimiter'as single quotation marks DBMS_OUTPUT.PUT_LINE ('3rd Sunday in June is:' | | v_event); v_event: = q' [Mother's day]' -- any pair of special symbols is fine. Here we use a pair of square brackets [] DBMS_OUTPUT.PUT_LINE ('2nd Sunday in May is:' | | v_event); END

SQL > /

3rdSunday in June is: Father's day2ndSunday in May is: Mother's dayPL/SQLprocedure successfully completed.

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.