pl/sql基础知识—定义并使用变量

n  介绍

在编写pl/sql程序是,可以定义变量和常量;在pl/sql程序中包括有:

①标量类型(scalar)

②复合类型(composite)

③参照类型(reference)

④lob(large object)

标量(scalar)一常用类型

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

n  pl/sql中定义变量和常量的语法如下:

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

identifier:名称

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

datatype:数据类型

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

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

default:用于指定初始值

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

n  标量定义的案例

①定义一个变长字符串

v_ename varchar2(10)

②定义一个小数 范围-9999.99~9999.99

v_sal number(6,2);

③定义一个小数并给一个初始值为5.4:=是pl/sql的赋值号

v_sal2number(6,2):=5.4

④定义一个日期类型的数据

v_hiredate date;

⑤定义一个布尔变量,不能为空,初始值为false

v_valid boolean:=false;

标量(scalar)一使用标量

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

案例:以输入工号,显示雇员姓名、工资、个人所得税(税率为0.03)为例,说明变量的使用,看看如何编写。

create or replace procedure pro1(v_in_empno in number) is

v_tax_rate number(3,2):=0.03;

v_sal number;

v_ename varchar2(32);

v_tax number;

begin

select ename,sal into v_ename,v_sal from emp where empno=v_in_empno;

v_tax:=v_sal*v_tax_rate;

dbms_output.put_line(v_ename||'工资是 '||v_sal||'  个人所得税是 '||v_tax);

end;

标量(scalar)一使用%type类型

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

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

为了让我们在pl/sql编程中,让变量的类型和大小与表的列的大小和类型一致可以使用%type。

我们看看这个怎么使用:

标识符名  表名.列名%type;

create or replace procedure pro1(v_in_empno in number) is

v_tax_rate number(3,2):=0.03;

v_sal emp.sal%type;    --为了让V_sal的类型更加灵活,使用%type自适应

v_ename emp.ename%type;

v_tax number;

begin

select ename,sal into v_ename,v_sal from emp where empno=v_in_empno;

v_tax:=v_sal*v_tax_rate;

dbms_output.put_line(v_ename||'工资是 '||v_sal||'  个人所得税是 '||v_tax);

end;

复合变量(composite

用于存放多个值的变量。常用的包括这:

①pl/sql记录     ②pl/sql表

复合类型一pl/sql记录

类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须要加记录变量作为前缀(记录变量.记录成员)如下:

 
 

type 自己定义的记录名称 is record(

变量 变量类型,

变量 变量类型,

);

//使用自定义的pl/sql记录

变量名 自己定义的pl/sql记录名;

请编写一个过程,该过程可以接收一个用户编号,并显示该用户的名字、薪水、工资岗位(注意:要求用pl/sql记录实现)

create or replace procedure
pro1(v_in_empno in number) is

type lsz_emp_record is
record(

v_ename emp.ename%type,

v_sal emp.sal%type,

v_job emp.job%type);

v_emp_record lsz_emp_record;

begin

select ename,sal,job into v_emp_record from
emp where empno=v_in_empno;

dbms_output.put_line('名字:'||v_emp_record.v_ename||'
工资:'||v_emp_record.v_sal);

end;

复合类型一pl/sql表(了解即可)

类似与高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制,实例如下:

declare

type sp_table_type is
table of emp.ename%type

index by binary_integer;

sp_table
sp_table_type;          --定义一个变量:sp_table:类型

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的元素

参照变量一介绍

参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写pl/sql程序是,可以使用游标变量(ref
cursor)和对象类型变量(ref
obj_type)两种参照变量类型。

参照变量-ref
cursor
使用

               
 

定义游标:

type自定义游标名 is ref cursor;

变量名 自定义游标名;

   

打开游标:

open 游标变量 for select 语句;

 
 
   

取出当前游标指向的行:

fetch 游标变量 into 其它变量;

     

判断游标是否指向记录最后:

游标变量%notfound

 

 

 

 

 

 

 

 

 

 

通过游标,我们可以取得返回结果集(这个结果集,往往是select语句的结果)的任何一行数据,从而提高共享的效率

 

使用游标:当定义游标时不需要指定相应的select语句,但是当使用游标时(open)需要指定select语句,这样一个游标就与一个select语句结合了。实例如下:

①请使用pl/sql编写一个过程,可以输入部门号,并显示该部门所有员工姓名和他的工资。

create
or
replace
procedure
pro1(v_in_deptno
number)
is

--先定义一个游标变量类型

type
lsz_emp_cursor is
ref
cursor;

--定义一个游标变量

v_emp_cursor
lsz_emp_cursor;

--定义两个变量

v_ename
emp.ename%type;

v_sal
emp.sal%type;

begin

--执行语句

open
v_emp_cursor for
select
ename,sal
from
emp where
deptno=v_in_deptno;

--取出游标指向的每行数据,用循环语句

loop

fetch
v_emp_cursor into
v_ename,v_sal;--这句话会引起v_emp_cursor向下走一格

--判断当前游标是否到达最后

exit
when
v_emp_cursor%notfound;

--输出

dbms_output.put_line('用户名:'||v_ename||'  薪水:'||v_sal);

end
loop;

--关闭游标(完成后一定要记住关闭游标)

close
v_emp_cursor;

end;

②在①的基础上,如果某个员工的工资低于200元,就增加100元。

create
or
replace
procedure
pro1(v_in_deptno
number)
is

--先定义一个游标变量类型

type
lsz_emp_cursor is
ref
cursor;

--定义一个游标变量

v_emp_cursor
lsz_emp_cursor;

--定义两个变量

v_ename
emp.ename%type;

v_sal
emp.sal%type;

v_empno
emp.empno%type;

begin

--执行语句

open
v_emp_cursor for
select
ename,sal,empno
from
emp where
deptno=v_in_deptno;

--取出游标指向的每行数据,用循环语句

loop

fetch
v_emp_cursor into
v_ename,v_sal,v_empno;--这句话会引起v_emp_cursor向下走一格

--判断当前游标是否到达最后

exit
when
v_emp_cursor%notfound;

--输出

--dbms_output.put_line('用户名:'||v_ename||'  薪水:'||v_sal);

if
v_sal<1500
then

update
emp set
sal=sal+100
where
empno=v_empno;

end
if;

end
loop;

--关闭游标(完成后一定要记住关闭游标)

close
v_emp_cursor;

end;

上一篇:JSP内置对象及常用方法


下一篇:PL/SQL基本语法