在数据库的操作中,在编写sql语句的时候,我们可能会用到函数以及触发器等的操作来简化我们的工作。
在这片文章中,也和我以前的博文一样,不是纯粹的理论讲解,而是采用的是案例的方式进行笔记的记录说明。还是用到的以前博文中的员工表emp
1.认识plsql的函数
问题1 输入雇员姓名返回年薪
create or replace function sp_func(spName varchar2) return number is
yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
总结:
语法:
create 【or replace】 function 函数名(参数1 类型1,参数2 类型2) return 返回值类型
is 变量、常量和游标的定义;
begin
执行部分
exception
异常处理部分(可选)
end;
调用函数的第一种方式
var income number; --定义全局变量var 变量名 变量类型
call sp_func('SCOTT') into:income; --全局变量赋值:call 函数into:全部变量名
print income; --打印全局变量 print 全局变量名
调用函数的第二种方式
select sp_func('SCOTT') from dual;
2,包
包:
主要是一种用来组织和管理过程和函数的一种机制,主要由两部分组成,包规范和包体
包里面主要是声明一下过程和函数,过程和函数的实现放在包体里面
–创建包:包里面只是声明过程和函数
问题:创建一个包,包含一个过程,包含一个函数,
过程:输入员工姓名,新的工资,更新员工工资。
函数:输入员工的姓名,计算该员工的收入
createor replace package sp_package is
procedure update_sal(name emp.ename%type,newSal emp.sal%type);
function annual_income(name emp.ename%type) return number;
end;
总结
语法:创建包
create 【or replace】 package 包名 is
过程1声明
过程2声明
.......
函数1声明
函数2声明
....
end;
过程的实现放在包体里面,包里面只是声明过程和函数
–创建包体:
create or replace package body sp_package is
procedure update_sal(name emp.ename%type,newSal emp.sal%type) is
begin
update emp set sal=newSal where ename=name;
end;
function annual_income(name emp.ename%type) return number is
yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=name;
return yearSal;
end;
end;
创建包体的语法
create 【or replace】 package body 包名 is
过程1实现体
过程2实现体
....
函数1实现体
函数2实现体
end;
包中的过程和函数的调用 通过 包名.过程名或者 包名.函数名 来调用的
exec sp_package.update_sal('SCOTT',4500);
var income number;
call sp_package.annual_income('SCOTT') into:income;
print income;
select sp_package.annual_income('SCOTT') from dual;
PL/SQL变量:标量类型,复合类型,参照类型lob(large object)类型
–标量类型: 变量名【constan】 数据类型【not null】【:=值】|【default 值】;
案例:
定义一个变长的字符串:v_ename varchar2(20);
定义一个小数:v_vearsal number(7,2);
定义一个小数,给初始值为3.14:v_sal number(3,2):=3.14;
定义一个常量PI给默认值为3.14:PI constant number(3,2):=3.14;
定义一个日期类型;v_hiredate date
定义一个布尔类型 v_invid boolean not null default false;
编写一个PL/sql块,输入员工编号,显示雇员的姓名和工资,以及个人所得税(税率为0.05)
declare