oracle数据库的随堂笔记（四）-定义并使用变量-白红宇

oracle数据库的随堂笔记（四）-定义并使用变量

阅读量：6233 次

发布时间：2019-06-22

本文共 3124 字，大约阅读时间需要 10 分钟。

定义并使用变量

介绍

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：

1）标量类型（scalar)

2）复合类型（composite)

3）参照类型（reference）

4）lob（large object）

1.标量（scalar)-常用类型

在编写pl/sql块时，如果要使用变量，需在定义部分定义变量

1） pl/sql中定义变量和常量的语法如下：

identifier [constant] datatype[not null] [:=| default expr]

identifier:名称

constant:指定常量。需要定义它的初始值，且其值是不能改变的

datatype:数据类型

not null:指定其变量值不能为null

:= 给变量或常量指定初始值

default:用于指定初始值

expr:指定初始值的pl/sql表达式（文本值、其它变量、函数等）

2）标量定义的案例

a.定义一个变长字符串

v_ename varchar2(10);

b.定义一个小数（范围-9999.99~9999.99）

v_sal1 number(6,2);

c.定义一个小数并赋一个初始值5.4（:=是pl/sql的赋值号）

v_sal2 number(6.2):=5.4;

d.定义一个日期类型的数据

v_hiredate date;

e.定义一个布尔变量，不能为空，初始值为false

v_valid boolean not null default false;

3）使用标量

在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号（:=）

下面以输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）为例。说明变量的使用，看看如何编写。

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;

4）标量（scalar）-使用%type类型

对于上面的pl/sql块有一个问题：

如果员工的姓名长度超过5时，就会出现错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，它会按照数据库列来确定你定义的变量的类型和长度。

使用方法：标识符名表名.列名%type;

例：v_ename emp.ename%type;

v_sal emp.sal%type;

2.复合变量（composite）

用于存放多个值的变量。主要包括这几种：

1）pl/sql记录

2）pl/sql表

3）嵌套表

4）varray（动态数组）

1） pl/sql记录

类似于高级语言中的结构体。需要注意的是，当引用pl/sql记录成员时，必须要加记录变量名作为前缀（记录变量.记录成员）。

实例：

declare

--定义了一个pl/sql记录类型emp_record_type，类型包括三个数据name，salary，title

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

title emp.job%type

);

--定义了一个xf_record变量，类型是emp_record_type

xf_record emp_record_type;

begin

select ename,sal,job into sp_record from emp where empno=7788;

dbms_output.put_line('员工名：'||xf_record.name||'工资：'||xf_record.salary);

end;

2）pl/sql表

相当于高级语言中的数组。但是需要注意的是，在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。

实例:

declare

--定义了一个pl/sql表类型xf_table_type，该类型是用于存放emp.ename%type类型数据

--index by binary_integer表示下标是整数

type xf_table_type is table of emp.ename%type index by binary_integer;

--定义了一个sp_table变量，类型为xf_table_type

xf_table xf_table_type;

begin

--表的下标是任意的，这里只存入一行数据

select ename into sp_table(0) from emp where empno=7788;

dbms_output.put_line('员工名：'||sp_table(0);

end;

？如果把where子句去除会怎样

3）嵌套表（nested table)

待完善。

4)变长数组（varray）

待完善。

3.参照变量（reference）

参照变量是指用于

存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用

游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型。

1）参照变量-ref cursor游标变量

当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。

实例：

1）请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资.

declare

--定义一个游标类型xf_emp_cursor

type xf_emp_cursor is ref cursor;

--定义一个游标变量

test_cursor xf_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;