oracle11g教程从入门到精通

而在函数体内必须包含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)

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4