PLSQL中的变量使用

来源：互联网发布：音乐变成伴奏软件编辑：程序博客网时间：2024/04/28 08:59

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
(1). 标量类型(scalar)
(2). 复合类型(composite)
(3). 参照类型(reference)
(4). lob(large object)
1. 标量(scalar)——常用类型
     在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下：
     identifier [constant] datatype [not null] [:=| default expr]
     identifier : 名称
     constant ：指定常量。需要指定它的初始值，且其值是不能改变的
     datatype ：数据类型
     not null ：指定变量值不能为null
     := 给变量或是常量指定初始值
     default 用于指定初始值
     expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。
标量定义的案例
(1). 定义一个变长字符串
      v_ename varchar2(10);
(2). 定义一个小数,范围 -9999.99~9999.99
      v_sal number(6,2);
(3). 定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号
      v_sal2 number(6,2):=5.4;
(4). 定义一个日期类型的数据
      v_hiredate date;
(5). 定义一个布尔变量，不能为空，初始值为false
      v_valid boolean not null default false;
2. 标量(scalar)——使用标量
     在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。

[c-sharp] view plaincopy
declare    
  c_tax_rate number(3,2):=0.03;     
  --用户名     
  v_ename varchar2(5);     
  v_sal number(7,2);     
  v_tax_sal number(7,2);     
begin    
--执行     
    select ename,sal into v_ename,v_sal from emp where empno=&no;     
--计算所得税     
    v_tax_sal := v_sal*c_tax_rate;     
--输出     
    dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||' 交税：'||v_tax_sal);     
end;     

标量(scalar)——使用%type类型
对于上面的pl/sql块有一个问题：
就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：
标识符名表名.列名%type;
比如上例的v_ename，这样定义：v_ename emp.ename%type;
3. 复合变量(composite)——介绍
     用于存放多个值的变量。主要包括这几种：
     pl/sql记录
     pl/sql表
     嵌套表
     varray
(1). 复合类型——pl/sql记录
      类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

[c-sharp] view plaincopy
declare    
  --定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理     
  type emp_record_type is record(     
    name   emp.ename%type,     
    salary emp.sal%type,     
    title  emp.job%type);     
--定义了一个sp_record变量，这个变量的类型是emp_record_type     
  sp_record emp_record_type;     
begin    
  select ename, sal, job into sp_record from emp where empno = 7788;     
  dbms_output.put_line ('员工名:' || sp_record.name);     
end;    

(2). 复合类型-pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：

[c-sharp] view plaincopy
declare    
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type     
--index by binary_integer 表示下标是整数     
  type sp_table_type is table of emp.ename%type      
  index by binary_integer;     
--定义了一个sp_table变量，这个变量的类型是sp_table_type     
  sp_table sp_table_type;     
begin    
  select ename into sp_table(-1) from emp where empno = 7788;     
  dbms_output.put_line('员工名:' || sp_table(-1));     
end;    

    说明：
    sp_table_type 是pl/sql表类型
    emp.ename%type 指定了表的元素的类型和长度
    sp_table 为pl/sql表变量
    sp_table(0) 则表示下标为0的元素
    注意：如果把select ename into sp_table(-1) from emp where empno = 7788; 变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：
ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量（这里不讲）
(3). 复合变量——嵌套表(nested table)
(4). 复合变量——变长数组(varray)
(5). 参照变量——介绍
      参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
(6). 参照变量——ref cursor游标变量
      使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。实例如下：
1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.在1的基础上，如果某个员工的工资低于200元，就添加100元。

[c-sharp] view plaincopy
declare    
--定义游标sp_emp_cursor      
    type sp_emp_cursor is ref cursor;     
--定义一个游标变量     
    test_cursor sp_emp_cursor;        
--定义变量     
v_ename emp.ename%type;      
v_sal emp.sal%type;     
begin    
--执行     
--把test_cursor和一个select结合     
open test_cursor for select ename,sal from emp where deptno=&no;     
--循环取出     
loop     
    fetch test_cursor into v_ename,v_sal;     
    --判断是否test_cursor为空     
    exit when test_cursor%notfound;     
    dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);     
end loop;     
end;     