PL/SQL编程(三)

五、包

包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成的。

①使用create package命令来创建包

--实例

--创建包规范

create package example4 is

procedure update_sal(name varchar2,newSal number);

function annual_income(name varchar2) return number;

end;

包规范只包含了过程和函数的说明,但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

②建立包体使用create package body命令

--实例,创建包体

create or replace package body example4 is

--过程

procedure update_sal(name varchar2,newSal number)

is

begin

--过程执行部分

update emp set sal=newSal where ename=name;

end;

--函数

function annual_income(name varchar2)

return number is annual_salary number;

begin

--函数执行部分

select sal*12+nvl(comm,0)*12 into annual_salary from emp where ename=name;

return annual_salary;

end;

end;

如何调用包的过程或是函数?

当调用包的过程或是函数时,在过程和函数前面需要带上包名,如果要访问其他方案的包,还需要在包前加上方案名。

如:

sql>call example4.update_sal('SCOTT',1500);

 

六、定义及使用变量

在pl/sql程序中,变量的类型包括有:

①标量类型(scalar)

②符合类型(composite)

③参照类型(reference)

④lob(large object)

 

1.标量定义的案例

①定义一个变成的字符串

v_ename varchar2(10);

②定义一个小数,范围-9999.99—9999.99

v_sal number(6,2);

③定义一个小数并给定初始值为5.4

v_num number(6,2) :=5.4;

-- :=pl/sql中的赋值符号

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

v_hiredate date;

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

v_valid boolean not null default false;

 

2.使用标量变量

标量的赋值符号是[ := ]

--案例1:输入员工号,显示员工姓名、工资、个人所得税(0.03为例)

--说明变量的使用

declare

--税率,常量

c_tax_rate number(3,2):=0.03;

--员工姓名

v_name varchar2(5);

--工资

v_sal number(7,2);

--个人所得税

v_tax number(7,2);

begin

--执行部分

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

--计算个人所得税

v_tax := v_sal*c_tax_rate;

--输出

dbms_output.putline('姓名是'||v_name||' 工资是'||v_sal||' 交税:'||v_tax);

end;

 

3.使用%type类型

对于上一个案例,如果员工姓名超过了5个字符就会出错。为了降低pl/sql程序的维护工作量,推荐使用%type属性定义变量,这样它会按照数据库列的定义来确定自己定义变量的类型和长度。

使用:变量名 表名.列名%type;

 

4.使用复合变量

用于存放多个记录的变量(类似于数组),主要包括以下几种:

①pl/sql记录

类似于高级语言的结构体。

需要注意的是:当引用pl/sql记录成员时,必须要加记录变量作为前缀(记录变量.记录成员)

--案例

declare

--定义

type emp_record_type is record(

name emp.ename%type,

salary emp.sal%type,

title emp.job%type;

);

--声明

test_record emp_record_type;

begin

--执行部分

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

dbms_output.putline('姓名:'||test_record.ename);

end;

 

②pl/sql表

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

declare

--定义类型

type ex_table_type is table of emp.ename%type

index by binary_integer;

--定义变量

begin

select ename into ex_table(0) from emp where empo=7788;

dbms_output.putline('员工名:'||ex_table(0));

end;

说明:

ex_table_typepl/sql表类型;

emp.ename%type指定了表的元素类型和长度

ex_table pl/sql表变量

ex_table(0)是表示下标为0的元素

 

③嵌套表

④varry(动态数组)

 

5.参照变量

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

 

6.ref cursor游标变量的使用

使用游标时,

案例1

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

declare

--定义游标类型

type ex_emp_cursor is ref cursor;

--定义游标变量

test_cursor ex_emp_cursor;

--定义2个变量

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

--执行部分:把test_cursorselect结合,让cursor指向select结果集

open test_cursor for select ename,sal from emp where deptno=&no;

--循环取出结果,需要定义2个额外的变量

loop

--循环体

fetch test_cursor into v_ename,v_sal;

--判断test_cursor是否为空,循环退出条件

exit when test_cursor%notfound;

dbms_output.putline('姓名:'||v_ename' 薪资:'||v_sal);

end loop;

end;

 

案例2

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

declare

--定义游标类型

type ex_emp_cursor is ref cursor;

--定义游标变量

test_cursor ex_emp_cursor;

--定义2个变量

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

--执行部分:把test_cursorselect结合,让cursor指向select结果集

open test_cursor for select ename,sal from emp where deptno=&no;

--判断工资高低,决定是否更新100

if sal<200 then

update emp set sal = sal+100;

--循环取出结果,需要定义2个额外的变量

loop

--循环体

fetch test_cursor into v_ename,v_sal;

--判断test_cursor是否为空,循环退出条件

exit when test_cursor%notfound;

dbms_output.putline('姓名:'||v_ename' 薪资:'||v_sal);

end loop;

end;

 

posted on 2013-02-25 22:01  Frank.Fan  阅读(599)  评论(0编辑  收藏  举报