oracle存储过程和存储函数学习笔记

oracle存储过程和存储函数

过程和函数区别在于：
存储过程没有返回值
存储函数有返回值

CREATE [or replace] PROCEDURE 过程名（参数列表）
AS
PLSQL了程序体;

参数列表( test in number)
test参数名
in 是输入参数
number 参数为数字类型

存储过程调用方式：
1、exec 过程名;
2、在PLSQL程序体中调用
begin
过程名
end;

示例带参数存储过程：
create or replace procedure raisesalary(eno in number)
as
–定义一个变量保存涨前的薪水
psal emp.sal%type;

begin
–得到员工涨前的薪水
select sal into psal from emp where empno=eno;
–给该员工涨100
update emp set sal=sal+100 where empno=eno;

–打印
dbms_output.put_line( ‘涨前: ‘ || psal || ‘ 涨后: ‘ || (psal+100));

–可以在过程中加提交和回滚事物;

end;

存储函数（Function)：
必须有RETURN子句

CREATE [OR REPLACE] FUNCTION 函数名（函数列表）
RETURN 函数值类型
AS
PLSQL 子程序体;

查询员工年收入示例：
create or replace function queryempincome(eno in number)
return number
as
–定义变量保存员工的薪水和资金
psal emp.sal%type;
pcomm emp.comm%tyype;

begin

–得到员工的薪水的奖金
select sal ,comm into psal,pcomm from emp where empno = eno;

–直接返回年收入
–nvl处理空值问题，遇到NULL为0
return psal*12+nvl(pcomm,0);
end;

输出参数，存储过程、存储函数都可以有，可以实现多个返回值
out参数：查询某个员工姓名 月薪 和职位
create or repleace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin

select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;

补充问题：

问题查询员工全部信息（out多个）：

在out参数中使用光标：

包头：

create or replace package mypackage as

type empcursor is ref cursor;//定义引用光标类型

procedure queryEmpList(dno in number ,empList out empcursor);

end mypackage;

empcursor 输出所有员工所有信息

包体：

create or replace package body mypackage as

procedure queryEmpList(dno in nimber,empList out empcursor) as

begin

–打开光标  deptno部门编号

open empList for select * from emp where deptno=dno;

end queryEmpList;

end mypackage;

创建程序包;

创建主体在程序包上。

CTRL + S 保持程序包

常用命令：
sqlplus scott/tiger@ip:1521/orcl
set serveroutput on //打开字符串，打开输出开关

desc 查看表、视图、包的结构。