Plsql 简明教程

PL/SQL - Variables

在本章中，我们将讨论 Pl/SQL 中的变量。变量不过是赋予存储区域的一个名称，存储区域可以被我们的程序操作。PL/SQL 中的每个变量都有一个特定的数据类型，此数据类型确定变量内存的大小和布局；可存储在此内存中的值的范围；以及可应用于变量的操作集。

In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable’s memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable.

PL/SQL 变量的名称由一个字母组成（可以后跟更多的字母、数字、美元符号、下划线和数字符号），且不应超过 30 个字符。默认情况下，变量名称不区分大小写。你不能使用保留的 PL/SQL 关键字作为变量名称。

The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name.

PL/SQL 编程语言允许定义各种类型的变量，例如日期时间数据类型、记录、集合等，我们将在后续的章节中介绍这些内容。对于本章，我们只研究基本变量类型。

PL/SQL programming language allows to define various types of variables, such as date time data types, records, collections, etc. which we will cover in subsequent chapters. For this chapter, let us study only basic variable types.

Variable Declaration in PL/SQL

PL/SQL 变量必须在声明部分中声明，或在包中声明为全局变量。当你声明变量时，PL/SQL 将为变量的值分配内存，并且存储位置由变量名称识别。

PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable’s value and the storage location is identified by the variable name.

声明变量的语法为：

The syntax for declaring a variable is −

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

其中，variable_name 是 PL/SQL 中的有效标识符，datatype 必须是有效的 PL/SQL 数据类型或任何用户定义的数据类型，我们已经在上一章中讨论过了。下面显示了一些有效的变量声明及其定义：

Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid PL/SQL data type or any user defined data type which we already have discussed in the last chapter. Some valid variable declarations along with their definition are shown below −

sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

当你在数据类型中提供大小、范围或精度限制时，这被称为 constrained declaration 。受限声明需要的内存比不受限声明少。例如：

When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. For example −

sales number(10, 2);
name varchar2(25);
address varchar2(100);

Initializing Variables in PL/SQL

每当你声明一个变量时，PL/SQL 都会为其分配默认值 NULL。如果你想使用非 NULL 值初始化变量，你可以在声明期间使用以下任意一种方法来执行此操作：

Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following −

The DEFAULT keyword
The assignment operator

例如 -

For example −

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';

你还可以使用 NOT NULL 约束指定变量不应该具有 NULL 值。如果你使用 NOT NULL 约束，你必须明确针对该变量指定一个初始值。

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.

正确初始化变量是一种良好的编程实践，否则，程序有时会产生意外结果。试用以下示例，它使用了各种类型的变量：

It is a good programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the following example which makes use of various types of variables −

DECLARE
   a integer := 10;
   b integer := 20;
   c integer;
   f real;
BEGIN
   c := a + b;
   dbms_output.put_line('Value of c: ' || c);
   f := 70.0/3.0;
   dbms_output.put_line('Value of f: ' || f);
END;
/

执行上述代码后，将生成以下结果 −

When the above code is executed, it produces the following result −

Value of c: 30
Value of f: 23.333333333333333333

PL/SQL procedure successfully completed.

Variable Scope in PL/SQL

PL/SQL 允许块的嵌套，即每个程序块都可以包含另一个内部块。如果一个变量在内部块中声明，则外部块无法访问该变量。但是，如果一个变量在外部块中声明且可访问，则所有嵌套内部块也可以访问该变量。有两种类型的变量作用域 -

PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope −

Local variables − Variables declared in an inner block and not accessible to outer blocks.
Global variables − Variables declared in the outermost block or a package.

下面示例以简单形式说明了 Local 和 Global 变量的用法 −

Following example shows the usage of Local and Global variables in its simple form −

DECLARE
   -- Global variables
   num1 number := 95;
   num2 number := 85;
BEGIN
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE
      -- Local variables
      num1 number := 195;
      num2 number := 185;
   BEGIN
      dbms_output.put_line('Inner Variable num1: ' || num1);
      dbms_output.put_line('Inner Variable num2: ' || num2);
   END;
END;
/

执行上述代码后，将生成以下结果 −

When the above code is executed, it produces the following result −

Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

Assigning SQL Query Results to PL/SQL Variables

可以使用 SQL 的 SELECT INTO 语句为 PL/SQL 变量赋值。 SELECT list 中的每个项目都必须在 INTO list 中有一个相应的类型兼容变量。以下示例说明了这个概念。让我们创建一个名为 CUSTOMERS 的表 −

You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept. Let us create a table named CUSTOMERS −

( For SQL statements, please refer to the SQL tutorial )

(For SQL statements, please refer to the SQL tutorial)

CREATE TABLE CUSTOMERS(
   ID   INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Table Created

现在让我们在表中插入一些值 −

Let us now insert some values in the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

以下程序使用 SQL 的 SELECT INTO clause 将上述表中的值分配给 PL/SQL 变量 −

The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL −

DECLARE
   c_id customers.id%type := 1;
   c_name  customers.name%type;
   c_addr customers.address%type;
   c_sal  customers.salary%type;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;
   dbms_output.put_line
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

执行上述代码后，将生成以下结果 −

When the above code is executed, it produces the following result −

Customer Ramesh from Ahmedabad earns 2000

PL/SQL procedure completed successfully