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_type是pl/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_cursor和select结合，让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_cursor和select结合，让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;