ITPub博客

首页 > 数据库 > Oracle > PL/SQL 02 声明变量 declare

PL/SQL 02 声明变量 declare

原创 Oracle 作者:j04212 时间:2014-02-12 17:13:22 0 删除 编辑

语法:

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;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27633655/viewspace-1080383/,如需转载,请注明出处,否则将追究法律责任。

下一篇: PL/SQL 03 流程控制
请登录后发表评论 登录
全部评论

注册时间:2012-10-23

  • 博文量
    94
  • 访问量
    249213