首页 > 代码库 > PL/SQL 02 声明变量 declare
PL/SQL 02 声明变量 declare
语法：
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]
identifier：用于指定变量或常量的名称。
CONSTANT：用于指定常量。当定义常量时，必须指定它的初始值，并且其数值不能变。
datatype：用于指定变量或常量的数据类型。
NOT NULL：用于强制初始化变量（不能为NULL）。当指定 NOT NULL 选项时，必须要为变量提供数值。
:= 用于为变量和常量指定初始值。
DEFAULT：用于为常量和变量指定初始值。
expr：用于指定初始值的 PL/SQL 表达式，可以是文本值、其他变量、函数等。
例：
v_ename VARCHAR2(10);
v_sal NUMBER(6,2);
v_balance BINARY_FLOAT; --Oracle 10g 新数据类型
c_tax_rate CONSTANT NUMBER(3,2):=5.5;
v_hiredate DATE;
v_valid BOOLEAN NOT NULL DEFAULT FALSE;
v_sal emp.sal%TYPE; --%TYPE
v_tax_sal v_sal%type; --可以用变量的%TYPE
--复合变量
1、记录变量（类似高级语言的结构体）
DECLARE
TYPE emp_record_type IS RECORD(
name emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
select ename,sal,job into emp_record
from emp where empno=7788;
dbms_output.put_line(‘雇员名：‘||emp_record.name); --记录变量.记录成员
END;
2、表变量（类似高级语言的数组）
需要注意，PL/SQL 表变量与高级语言的数组有所区别，高级语言数组的下标不能为负，但 PL/SQL 表变量的下标可以为负；高级语言数字的元素个数有限制，而PL/SQL 表变量的元素个数没有限制，并且其下标没有上下限。
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
select ename into ename_table(-1) from emp
where empno=7788;
dbms_output.put_line(‘雇员名：‘||ename_table(-1));
END;
3、嵌套表（Nested Table）
嵌套表类似于高级语言的数组。需要注意，高级语言数组和嵌套表的下标都不能为负值；高级语言的元素个数是有限制的，而嵌套表的元素个数是没有限制的。嵌套表和 PL/SQL 表变量非常类似，但嵌套表可以作为表列的数据类型，而 PL/SQL 表变量不能作为表列的数据类型。当在表列中使用嵌套表时，必须首先 CREATE TYPE 语句建立嵌套表类型。
CREATE OR REPLACE TYPE emp_type AS OBJECT(
name varchar2(10),
salary number(6,1),
hiredate date);
/
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
/
对象类型 emp_type 用于存储雇员信息，而 emp_array 是基于 emp_type 的嵌套表类型，它可以用于存储多个雇员的信息。当建立了嵌套表类型之后，就可以在表列或对象属性中将其作为用户自定义数据类型来引用。但需要注意，当使用嵌套表类型作为表列时，必须要为其指定专门的存储表。
CREATE TABLE department(
deptno number(2),
dname varchar2(10),
employee emp_arry
) NESTED TABLE employee STORE AS employee; --employee为存储表
4、VARRAY（变长数组）
类似于嵌套表，它可以作为表列和对象类型属性的数据类型。但需要注意，嵌套表的元素个数没有限制，而 VARRAY 的元素个数是有限制的。当使用 VARRAY 时，必须首先建立 VARRAY 类型。
CREATE TYPE article_type AS OBJECT(
title VARCHAR2(20),
pub DATE
);
/
CREATE TYPE article_arry IS VARCHAR2(20) OF article_type;
/
对象类型 article_type 用于存储文章信息，而 article_arry 则用于存储多篇文章的信息，并且最多可以存储 20 篇文章。当建立了 VARRAY 类型之后，可以在表或对象属性将其作为用户自定义数据类型来引用。
如：
CREATE TABLE author(
id number(6),
name varchar2(10),
article article_arry
);
注意，嵌套表列数据需要存储在专门的存储表中，而 VARRAY 数据则与其他列数据一起存放在表段中。
--参照变量
参照变量是用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用空间。
1、REF CURSOR
当使用显式游标时，需要在定义显式游标时指定相应的 SELECT 语句，这种显式游标称为静态游标。当使用游标变量时，在定义游标变量时不需要指定 SELECT 语句，而是在打开游标时指定 SELECT 语句，从而实现动态的游标操作。
DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor FOR
select ename,sal from emp where deptno=10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
/
c1 为 REF CURSOR 类型，而 emp_cursor 为游标变量，并且在打开游标变量时指定了其所对应的 SELECT 语句。
2、REF obj_type
当编写对象类型应用时，为了共享相同对象，可以使用 REF 引用对象类型，REF 实际是指向对象实例的指针。
下面通过示例说明如何使用 REF。首先建立对象类型 home 和对象表 homes，然后插入数据。
CREATE OR REPLACE TYPE home_type AS OBJECT(
street varchar2(50),
city varchar2(20),
state varchar2(20),
zipcode varchar2(6),
owner varchar2(10)
);
/
CREATE TABLE homes OF home_type;
insert into homes values(‘呼伦北路12号‘,‘呼和浩特‘,‘内蒙‘,‘010010‘,‘马明‘);
insert into homes values(‘呼伦北路13号‘,‘呼和浩特‘,‘内蒙‘,‘010010‘,‘秦斌‘);
commit;
对象表 homes 存放着家庭所在地以及户主姓名。假定每个家庭有四口人，当进行人口统计时，为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用 REF 应用 home_type 对象类型，从而降低占用空间。示例如下：
create table person(
id number(6) primary key,
name varchar2(10),
addr REF home_type
);
insert into person select 1, ‘马文‘,ref(p)
from homes p where p.owner=‘马明‘;
insert into person select 2, ‘马武‘,ref(p)
from homes p where p.owner=‘马明‘;
insert into person select 3, ‘马全‘,ref(p)
from homes p where p.owner=‘马明‘;
insert into person select 4, ‘马才‘,ref(p)
from homes p where p.owner=‘马明‘;
commit;
当为 person 表插入数据时， addr 列将会存放指向 homes 表相应数据的地址指针。
--非 PL/SQL 变量
当在 SQL*Plus 或应用程序（例如 Pro*C/C++）中与 PL/SQL 块之间进行数据交互时，需要使用 SQL*Plus 变量或应用程序变量完成。当在 PL/SQL 块中引用非 PL/SQL 变量时，必须要在非 PL/SQL 变量前加冒号（“:”）。
1、使用 SQL*Plus 变量
在 PL/SQL 块中引用 SQL*Plus 变量时，必须首先使用 VARIABLE 命令定义变量；而如果要在 SQL*Plus 中输出变量内容，则需要使用 PRINT 命令。
var name varchar2(10)
begin
select ename into :name from emp
where empno=7788;
end;
SQL> print name;
NAME
--------------------------------
SCOTT
2、使用 Procedure Builder 变量
当在 PL/SQL 块中引用 Procedure Builder 变量时，必须首先使用 CREATE 命令定义变量，而如果在 Procedure Builder 中输出变量内容，则可以使用包 TEXT_IO。
PL/SQL> CREATE CHAR name LENGTH 10
PL/SQL> BEGIN
+> SELECT ename INTO :name FROM emp
+> WHERE empno=7788;
+> END;
PL/SQL> TEXT_IO.PUT_LINE(:name);
SCOTT
3、使用 Pro*C/C++变量
当在 PL/SQL 块中引用 Pro*C/C++ 程序的宿主变量时，必须首先定义宿主变量，而如果要输出变量内容，则可以使用 printf()。
char name[10];
EXEC SQL EXECUTE
BEGIN
SELECT ename INTO :name FROM emp
WHERE empno=7788l
END;
END-EXEC;
printf("雇员名：%s\n",name);
--三个 SQL*Plus 小例子
1、输出文本
SQL> DECLARE
2 txt VARCHAR2(20);
3 BEGIN
4 txt:=‘HuangHeShiZGDeMuQin‘;
5 dbms_output.put_line(txt);
6 END;
7 /
HuangHeShiZGDeMuQin
PL/SQL procedure successfully completed.
2、替代变量
SQL> DECLARE
2 a BINARY_INTEGER;
3 b BINARY_INTEGER;
4 BEGIN
5 a:=&x;
6 b:=&y;
7 dbms_output.put_line(a/b);
8 END;
9 /
Enter value for x: 9
old 5: a:=&x;
new 5: a:=9;
Enter value for y: 3
old 6: b:=&y;
new 6: b:=3;
3
3、SQL*Plus 变量赋值，并输出
SQL> var zg VARCHAR2(10)
SQL> BEGIN
2 :zg:=‘zhongguo‘;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> print zg;
ZG
--------------------------------
zhongguo
--实验举例
declare
stuname varchar2(10):=‘张三‘; --声明中赋值
stubir date;
begin
stubir:=to_date(‘2012-1-1‘,‘yyyy-mm-dd‘); --程序体中赋值
dbms_output.put_line(‘stuname的值是：‘||stuname);
dbms_output.put_line(‘stubir的值是：‘||stubir);
end;
declare
name emp.ename%type; --变量类型参考表中某列类型
begin
name:=‘john‘;
dbms_output.put_line(‘name is :‘||name);
end;
declare
no001 emp.empno%type;
name001 emp.ename%type;
begin
select empno,ename --将SQL查询结果存入变量（必须返回一行，不能多行）
into no001,name001
from emp
where empno=7369;
dbms_output.put_line(‘no001 is :‘||no001);
dbms_output.put_line(‘name001 is :‘||name001);
end;
declare
type emptype is record( --记录变量（包含多个变量）
no emp.empno%type,
name emp.ename%type,
date emp.hiredate%type
);
e emptype;
begin
select empno,ename,hiredate
into e
from emp
where empno=7369;
dbms_output.put_line(‘no is :‘||e.no);
dbms_output.put_line(‘name is :‘||e.name);
dbms_output.put_line(‘date is :‘||e.date);
end;
declare
e emp%rowtype; --记录变量另一种写法
begin
select *
into e
from emp
where empno=7369;
dbms_output.put_line(‘no is :‘||e.empno);
dbms_output.put_line(‘name is :‘||e.ename);
dbms_output.put_line(‘date is :‘||e.hiredate);
end;
declare
type etype is table of emp.ename%type --表变量（多个类型相同的变量，相当于数组）
index by binary_integer;
enames etype;
begin
enames(0):=‘SMITH‘;
enames(1):=‘ALLEN‘;
enames(2):=‘WARD‘;
dbms_output.put_line(enames(0));
dbms_output.put_line(enames(1));
dbms_output.put_line(enames(2));
end;
--表变量举例1
declare
type cc is table of sys_dmp01%rowtype;----表类型
v_tab cc;
i number default 1;
begin
select * bulk collect into v_tab -----------查询结果放到表类型中bulk collect (*换成字段会报错？)
from sys_dmp01; -----------四条记录
for i in 1..v_tab.count loop
insert into aa(ml_id,
mlmc,
mlyt,
mllb,
ztmc,
ztdx,
qjs,
bjs,
xt_id)
values(v_tab(i).ml_id,
v_tab(i).mlmc,
v_tab(i).mlyt,
v_tab(i).mllb,
v_tab(i).ztmc,
v_tab(i).ztdx,
v_tab(i).qjs,
v_tab(i).bjs,
v_tab(i).xt_id);
end loop;
end;
--表变量举例2
declare
type t is table of abc_t.s%type;
bl t;
i number default 1;
begin
select s bulk collect into bl
from abc_t;
for i in 1..bl.count
loop
dbms_output.put_line(bl(i));
end loop;
end;
--表变量举例3
declare
type t is table of abc_t.s%type;
bl t;
i number default 1;
sm number default 0;
begin
select s bulk collect into bl
from abc_t;
dbms_output.put_line(1);
while i<=bl.count
loop
loop
sm:=sm+bl(i);
i:=i+1;
exit when sm>=60;
end loop;
sm:=0;
dbms_output.put_line(i-1);
end loop;
end;
--表变量举例4 （判断字段中3个连续的数字）tt2是排序后的tt1表，不然判断会混乱
declare
type t is table of tt2%rowtype;
v_tab t;
begin
select * bulk collect into v_tab --*换成字段会报错
from tt2 ;
for i in 1 .. v_tab.count-2 loop --这里减2是因为最后2个数字不用判断，不然循环会报错
if v_tab(i+2).id - v_tab(i).id = 2 and v_tab(i+2).name = v_tab(i).name then
dbms_output.put_line(v_tab(i).name||‘-‘||v_tab(i).id);
dbms_output.put_line(v_tab(i+1).name||‘-‘||v_tab(i+1).id);
dbms_output.put_line(v_tab(i+2).name||‘-‘||v_tab(i+2).id);
dbms_output.put_line(‘----------‘);
end if;
end loop;
end;
PL/SQL 02 声明变量 declare