oracle函数、包、变量的定义和使用、重点”结构体和数组”

函数

实例1：输入雇员的姓名，返回该雇员的年薪

create function fun1(spName varchar2) return number is yearSal number(7,2);
begin 
select sal*12+nvl(comm,0) into yearSal from emp where ename=spName;
return yearSal;
end;
/

在sqlplus中调用函数
var income number
call annual_incomec('SCOTT') into: income;
print income

同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;
可以通过rs.getInt(1)得到返回的结果

 

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

实例1 用create package命令来创建包
create package mypackage is 
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现包规范中的过程和函数

实例2 建立包体使用命令create package body的命令
create package body mypackage 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) into annual_salary from emp where ename=name;
return annual_salary;
end;

end;

实例3 如何调用包的过程和函数
当调用包的过程和函数时，在过程和函数前需要带有包名，如果要访问其他方案的包，还需要在包名前加方案名
call mypackage.update_sal('SCOTT',1500);

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

变量的定义和使用
在编写pl/sql程序时，可以定义变量和常量，在pl/sql程序中包括有：
1标量类型(scalar)
定义一个变长字符串: v_ename varchar2(10);
定义一个小数并给初始值: v_sal number(6,2):=5.4;
定义一个布尔便令不能为空初始值为false:v_valid boolean not null default false
定义一个日期类型的数据: v_date date;

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

案例1：以输入员工号，显示雇员姓名，工资，个人所得税(0.03)为例。
declare 
c_tax_rate number(3,2):=0.03;
v_name varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where emp=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('姓名是：'||v_ename||' 工资：'||v_sal||' 交税'：v_tax_sal)
end;

2 增强版标量%type
在正常定义标量时字符需要指定大小，太大了浪费空间，可以采用：
v_ename emp.ename%type;
这是v_ename的类型和大小和表emp中字段ename的类型和大小保持一致性。

3 复合变量(composite)
用于存放多个值的变量，主要包括这几种：pl/sql记录、pl/sql表、嵌套表、varry

(1)pl/sql记录;类似高级语言中的结构体

declare
type emp_record_type is record(
v_name varchar2(5),
v_sal number(7,2));

sp_record emp_record_type;

begin 
select ename,sal into sp_record from emp where empno=22334;
dbm_output.put_line('员工名：'||emp_record.name);
end;

 

(2) pl/sql表：相当于数组

declare 
--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type
--index by binary_integer表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个sp_table变量
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_table(0));
end;

如果上述中select语句将where语句去掉，也就是返回了很多数据。

3参照变量 : 高级数组
参照变量是指用于存放数组值的指针变量，通过使用参照变量
(1)参照变量-ref cursor游标变量
使用游标时，当定义游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时需要指定select语句，这样一个游标就与一个select语句结合了。

实例1：编写一个块，可以输入部门号，显示该部门所有员工的信息
declare 
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;

begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断退出条件
exit when test_cursor%notfound;
dbms_output.put_line(v_ename||' '||v_sal);
end loop;

end;