文章目录
Oracle中存储过程与存储函数的使用
参见 PLSQL程序设计
1.存储过程与存储函数说明
存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
- 什么时候用存储过程/存储函数
原则:如果只有一个返回值,用存储函数;否则,就用存储过程。
in
表示入参,out
表示出参。
2.存储过程
2.1创建存储过程
用CREATE PROCEDURE
命令建立存储过程。语法如下:
CREATE [OR REPLACE] PROCEDURE 过程名[(参数列表)]
AS
变量声明
PLSQL子程序体;
例,为指定的职工在原工资的基础上涨10%的工资,并打印涨工资前和涨工资后的工资
create or replace procedure raiseSalary(in_empno in number)
as
currSal emp.sal%type; -- 保存员工当前工资
begin
-- 查询该员工工资
select sal into currSal from emp where empno = in_empno;
-- 给该员工涨工资
update emp set sal = sal * 1.1 where empno = in_empno;
-- 打印涨工资前后工资
dbms_output.put_line('涨工资前:' || currSal || ',涨工资后:' || currSal * 1.1);
end;
入参和出参参数名不要与查询条件字段名相同,防止不必要的问题。
存储过程中DML语句可以不使用提交,由程序调用方进行提交或回滚。
如果创建存储过程后,Procedures下的过程名有红叉,可右键-编辑查看报错信息
可以明显看出是变量未声明的错误
2.2调用存储过程
①PL/SQL
begin
raisesalary(7369);
end;
②命令行exec[ute] 存储过程名
set serveroutput on;
exec raisesalary(7369);
③PL/SQL Developer工具
右键要测试的存储名称->测试
输入变量值,点击执行,在DBMS输出
窗口可看到结果
涨工资前:800,涨工资后:880
2.3输出参数
例,输入员工编号,输出员工名称和薪资
create or replace procedure queryEmpNameSal(in_empno in number,
out_ename out varchar2,
out_sal out number)
as
begin
select ename,sal into out_ename,out_sal from emp where empno = in_empno;
end;
执行
declare
outEname emp.ename%type;
outSal emp.sal%type;
begin
queryEmpNameSal(7369,outEname,outSal);
dbms_output.put_line(outEname || ':' || outSal);
end;
SMITH:800
PL/SQL Developer测试时输入入参值,然后执行,输出变量参数的值
2.4删除过程
drop procedure queryEmpNameSal;
3.存储函数
3.1创建存储函数
函数(function)为一命名的存储程序,可带参数,并返回一计算值。函数和过程的结构类似,但必须有一个RETURN子句,用于返回函数值。函数说明要指定函数名、结果值的类型,以及参数类型等。
语法如下:
CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
RETURN 函数值类型
AS
变量声明
BEGIN
PLSQL子程序体;
RETURN 函数值;
END;
例,查询某职工全年的总收入
create or replace function queryEmpSalary(in_empno in number)
return number
as
pSal number; -- 定义变量保存员工的工资
pComm number; -- 定义变量保存员工的奖金
begin
select sal,comm into pSal,pComm from emp where empno = in_empno;
return pSal * 12 + nvl(pComm,0);
end;
3.2调用存储函数
①
declare
v_sal number;
begin
v_sal := queryEmpSalary(7934);
dbms_output.put_line('salary is:' || v_sal);
end;
salary is:15600
②
3.3输出多参数
存储函数也可以输出多参数值,但只能返回一个值
create or replace function queryEmpNameSal2(in_empno in number,
out_ename out varchar2,
out_sal out number)
return varchar2
as
begin
select ename,sal into out_ename,out_sal from emp where empno = in_empno;
return out_ename;
end;
3.4删除函数
drop function queryEmpNameSal2;
4.查询存储过程及函数
描述 | 字典表 | 示例 |
---|---|---|
存储过程、存储函数、触发器 | user_source | SELECT * FROM user_source; |