Oracle数据库---存储过程、存储函数

--创建存储过程
create or replace procedure first_proc
is
begin
dbms_output.put_line('我是过程');
dbms_output.put_line('hello everyone!');
end;

--创建函数
create or replace function first_func
return varchar2
is
begin
dbms_output.put_line('我是函数');
return 'hello everyone!';
end;

--调用存储过程
begin
first_proc;
end;

--调用存储函数
begin
dbms_output.put_line(first_func);
end;

--1：创建输入参数的存储过程
--根据员工号删除指定的员工信息
create or replace procedure proc1
(v_empno in empnew.empno%type)
is
begin
--根据员工号删除指定的员工信息
delete from empnew where empno = v_empno;
--判断是否删除成功
if sql%notfound then
-- -20000~ -20999之间
raise_application_error(-20008,'指定删除的员工不存在！');
else
dbms_output.put_line('删除成功！');
end if;
end;

--2;创建带有输出参数的存储过程
--求指定部门的平均工资和总人数
create or replace procedure proc2
(v_deptno in number, v_avgsal out number, v_cnt out number)
is
begin
select avg(sal),count(*)
into v_avgsal, v_cnt
from emp
where deptno = v_deptno;
exception
when no_data_found then
dbms_output.put_line('没有此部门！');
when others then
dbms_output.put_line(sqlerrm);
end;

--3：创建带有输入输出参数的存储过程
--实现两个数的交换
create or replace procedure proc3
(v_num1 in out number, v_num2 in out number)
as
v_temp number := 0;
begin
v_temp:= v_num1;
v_num1:= v_num2;
v_num2:= v_temp;
end;

--1:创建带有输入参数的存储函数
--根据部门编号返回该部门的总工资
create or replace function func1
(v_deptno in number)
return number
is
v_sumsal number;
begin
select sum(sal) into v_sumsal from emp where deptno = v_deptno;
return v_sumsal;
exception
when no_data_found then
dbms_output.put_line('没有此部门！');
when others then
dbms_output.put_line(sqlerrm);
end;

--2:创建带有输出参数的存储函数
--根据员工号输出员工的姓名和员工的工资，并且返回员工的年收入
create or replace function func2
(v_empno in emp.empno%type, v_name out emp.ename%type, v_sal out emp.sal%type)
return number
is
v_salsum number;
begin
select ename,sal,(sal+nvl(comm,0))*12
into v_name,v_sal,v_salsum
from emp
where empno = v_empno;
return v_salsum;
exception
when no_data_found then
dbms_output.put_line('没有此员工！');
when others then
dbms_output.put_line(sqlerrm);
end;

--3：创建带有输入输出参数的存储函数
--求两个数的平方和，并输出两个数的平方

create or replace function func3
(n1 in out number, n2 in out number)
return number
as
begin
n1 := n1*n1;
n2 := n2*n2;
return n1+n2;
end;

--调用无参的存储过程
begin
first_proc;
end;

--调用带有输入参数的存储过程
begin
proc1(1234);
end;

--调用带有输出参数的存储过程
declare
v_avgsalary number;
v_count number;
begin
proc2(10,v_avgsalary,v_count);
dbms_output.put_line('平均工资:'||v_avgsalary);
dbms_output.put_line('总人数:'||v_count);
end;

--调用带有输入输出参数的存储过程
declare
v_n1 number := 5;
v_n2 number := 10;
begin
proc3(v_n1,v_n2);
dbms_output.put_line('n1:'||v_n1);
dbms_output.put_line('n2:'||v_n2);
end;

--删除存储过程
drop procedure proc1;

--调用无参的函数
begin
dbms_output.put_line(first_func);
end;

--调用带有输入参数的函数
begin
dbms_output.put_line('部门的工资总额'||func1(&no));
end;

--调用带有输出参数的函数
declare
v_name emp.ename%type;
v_sal emp.sal%type;
v_salsum number;
begin
v_salsum := func2(&no,v_name,v_sal);
dbms_output.put_line('姓名：'||v_name);
dbms_output.put_line('工资：'||v_sal);
dbms_output.put_line('年收入：'||v_salsum);
end;

--调用带有输入输出参数的函数
declare
v_n1 number :=5;
v_n2 number :=6;
v_sum number;
begin
v_sum := func3(v_n1,v_n2);
dbms_output.put_line('n1的平方：'||v_n1);
dbms_output.put_line('n2的平方：'||v_n2);
dbms_output.put_line('平方和：'||v_sum);
end;

--删除存储函数
drop function func1;

--求部门的年收入
create or replace function func4
(v_deptno in number default 10,v_t in number)
return number
is
v_sumsal number;
begin
select sum((sal+nvl(comm,0))*12)
into v_sumsal
from emp
where deptno = v_deptno;
dbms_output.put_line('测试：'||v_t);
return v_sumsal;
exception
when no_data_found then
dbms_output.put_line('没有此部门');
when others then
dbms_output.put_line(sqlerrm);
end;

--调用
declare
v_totalsal number;
begin
v_totalsal := func4(v_t=>1);
dbms_output.put_line(v_totalsal);
end;