内容发布更新时间 : 2024/12/23 2:18:15星期一 下面是文章的全部内容请认真阅读。

而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例： Sql代码

1. --输入雇员的姓名，返回该雇员的年薪

2. create function annual_incomec(name varchar2) 3. return number is

4. annual_salazy number(7,2); 5. begin

6. --执行部分

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

8. return annual_salazy; 9. end; 10./

如果函数创建过程有编译错误，可以使用show error;命令显示错误 在sqlplus中调用函数 Sql代码

1. SQL> var income number

2. SQL> call annual_incomec('scott') into: income; 3. SQL> print income

同样我们可以在java程序中调用该函数

select annual_income('SCOTT') from dual; 这样可以通过rs.getInt(l)得到返回的结果。 包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。 1.我们可以使用create package命令来创建包。 实例：

--创建一个包sp_package

--声明该包有一个过程update_sal --声明该包有一个函数annual_income Sql代码

1. create package sp_package is

2. procedure update_sal(name varchar2, newsal number);

3. function annual_income(name varchar2) return number;

4. end;

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

2.建立包体可以使用create package body命令 --给包sp_package实现包体 Sql代码

1. create or replace package body sp_package is

2. procedure update_sal(name varchar2, newsal number)

3. is

4. begin

5. update emp set sal = newsal where ename = name;

6. end;

7. function annual_income(name varchar2) return number is

8. annual_salary number; 9. begin

10. select sal * 12 + nvl(comm, 0) into annual_salary from emp

11. where ename = name; 12. return annual_salary; 13. end; 14.end; 15./

3.如何调用包的过程或是函数 当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。 如：

SQL> call sp_package.update_sal('SCOTT', 1500); 特别说明：

包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力呵呵。

触发器

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。 特别说明：

我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。

十八：定义并使用变量,复合类型

定义并使用变量 介绍

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有： 1.标量类型(scalar) 2.复合类型(composite) 3.参照类型(reference) 4.lob(large object)

标量(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;

标量(scalar)——使用标量

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

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

1. declare

2. c_tax_rate number(3,2):=0.03;

3. --用户名

4. v_ename varchar2(5); 5. v_sal number(7,2);

6. v_tax_sal number(7,2); 7. begin 8. --执行

9. select ename,sal into v_ename,v_sal from emp where empno=&no; 10.--计算所得税

11. v_tax_sal := v_sal*c_tax_rate; 12.--输出

13. dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||' 交税：'||v_tax_sal); 14.end; 15./

标量(scalar)——使用%type类型 对于上面的pl/sql块有一个问题：

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

比如上例的v_ename，这样定义： v_ename emp.ename%type;

复合变量(composite)——介绍

用于存放多个值的变量。主要包括这几种： 1.pl/sql记录 2.pl/sql表 3.嵌套表 4.varray

复合类型——pl/sql记录

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

1. declare

2. --定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理

3. type emp_record_type is record(

4. name emp.ename%type, 5. salary emp.sal%type, 6. title emp.job%type); 7. --定义了一个sp_record变量，这个变量的类型是emp_record_type 8. sp_record emp_record_type; 9. begin

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

11. dbms_output.put_line ('员工名:' || sp_record.name); 12.end;

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

1. declare

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

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

4. type sp_table_type is table of emp.ename%type 5. index by binary_integer;

6. --定义了一个sp_table变量，这个变量的类型是sp_table_type 7. sp_table sp_table_type; 8. begin

9. select ename into sp_table(-1) from emp where empno = 7788;

10. dbms_output.put_line('员工名:' || sp_table(-1)); 11.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:实际返回的行数超出请求的行数 解决方法是：使用参照变量（这里不讲）

复合变量——嵌套表(nested table)