一、什么是存储过程和函数
1. 是被命名的pl/sql块
2. 被称之为pl/sql子程序
3. 与匿名块类似，有块结构:
声明部分是可选的（没有declare关键字）
必须有执行部分
可选的异常处理部分
二、匿名块和子程序之间的区别
三、存储过程：语法
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
IS|AS
procedure_body;
create or replace procedure add_dept is
v_deptment_id dept.deptno%type;
v_deptment_name dept.dname%type;
begin
v_deptment_id :=;
v_deptment_name := 'YWB';
insert into dept(deptno,dname) values(v_deptment_id,v_deptment_name);
commit;
dbms_output.put_line('插入了:'||sql%rowcount||'行');
end;
使用匿名块调用存储过程：
begin
add_dept;
end;
四、函数：
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
RETURN datatype
IS|AS
function_body;
函数与存储过程的区别：函数必须返回数据，存储过程可以返回数据，也可以不返回数据
create or replace function check_sal return boolean is
dept_id emp.deptno%type :=;
emp_no emp.empno%type :=;
salary emp.sal%type;
avg_sal emp.sal%type;
begin
select sal into salary from emp where empno=emp_no;
select avg(sal) into avg_sal from emp where deptno=dept_id;
if salary>avg_sal then
return true;
else
return false;
end if;
exception
when no_data_found then
return null;
end;
在匿名块中调用函数：
begin
if( check_sal is null) then
dbms_output.put_line('由于程序异常，输出null');
elsif (check_sal) then
dbms_output.put_line('工资高于平均工资');
else
dbms_output.put_line('工资低于平均工资');
end if;
end;
给函数传递参数：
create or replace function check_sal(empno number) return boolean is
dept_id employees.department_id%type;
sal employees.salary%type;
avg_sal employees.salary%type;
begin
select salary, department_id
into sal, dept_id
from employees
where employee_id = empno;
select avg(salary)
into avg_sal
from employees
where department_id = dept_id;
if sal > avg_sal then
return true;
else
return false;
end if;
exception
when no_data_found then
return null;
end; create or replace function check_sal(empno number) return number is
dept_id employees.department_id%type;
sal employees.salary%type;
avg_sal employees.salary%type;
begin
select salary, department_id
into sal, dept_id
from employees
where employee_id = empno;
select avg(salary)
into avg_sal
from employees
where department_id = dept_id;
if sal > avg_sal then
return ;
elsif (sal = avg_sal) then
return ;
else
return ;
end if;
exception
when no_data_found then
return null;
end; begin
if (check_sal() is null) then
dbms_output.put_line('由于程序异常，输出NULL');
elsif (check_sal() = ) then
dbms_output.put_line('工资高于平均工资');
elsif (check_sal() = ) then
dbms_output.put_line('工资等于平均工资');
else
dbms_output.put_line('工资低于平均工资');
end if;
end;