【Oracle篇】存储过程

一、PL/SQL为我们提供了一种叫做存储子程序的机制，允许在应用程序之间共享PL/SQL代码。而且这些PL/SQL代码可以存储在数据库中。 二、存储子程序是存储在数据库中的一个命名的PL/SQL块。PL/SQL9i中支持三种类型的存储子程序： ---过程、函数、包 三、为什么

一、PL/SQL为我们提供了一种叫做存储子程序的机制，允许在应用程序之间共享PL/SQL代码。而且这些PL/SQL代码可以存储在数据库中。
二、存储子程序是存储在数据库中的一个命名的PL/SQL块。PL/SQL9i中支持三种类型的存储子程序：
---过程、函数、包
三、为什么在PL/SQL要使用存储子程序？我们来分析一下它的优势：
1、可扩展性
2、模块化
3、可重用性
4、可维护性
5、抽象和数据隐藏
6、安全性

三、函数与过程相似，也是数据库中存储的命名PL/SQL程序块。建立它们遵循了相同的规则。它们的安全方式和参数传递也相同。函数的主要独特特性 是它必须返回一个值。这个值可以是NUMBER或者VARCHAR2这样的单独数据类型，或者也可以是PL/SQL数组或者对象这样的复杂数据类型。

--1：定义一个 procedure ,完成，根据输入一个员工编号，输出其姓名以及工作。

create or replace procedure pro_emp(v_eno number)
as
cursor cur_emp is select e.ename,e.job from emp e where e.empno=v_eno;
begin
for v_row in cur_emp loop
dbms_output.put_line(v_row.ename||' '||v_row.job);
end loop;
end pro_emp;


--2：定义一个 procedure ,完成，对dept表的信息插入。（尝试按名称调用和混合调用）.并且提交。

create or replace procedure pro_add_dept(v_dno number,v_dna varchar2,v_dloc varchar2)
as
begin
insert into dept(deptno,dname,loc) values(v_dno,v_dna,v_dloc);
commit;
end pro_add_dept;


--3：-- 使用自定义函数完成：输出scott.emp每个人的信息，及其工资占部门总工资百分比。

create or replace function fun_scott_empsal return number
as
emp_salpers number;

begin
select round((select e.sal from emp e where e.ename='SCOTT')/(select sum(sal) from emp e where e.deptno=(select e.deptno from emp e
where e.ename='SCOTT'))*100,2) into emp_salpers from emp e where e.ename='SCOTT';

return emp_salpers;
end fun_scott_empsal;

-- 过程调用函数输出信息
create or replace procedure pro_empsal(v_salpers number)
as
cursor cur_emp is select e.* from emp e where e.ename='SCOTT';
begin
for v_row in cur_emp loop
dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.job||' '||v_row.mgr||' '||v_row.hiredate||' '||v_row.sal||' '||v_row.comm||' '||v_row.deptno||' '||v_salpers);
end loop;
end pro_emp;

-- 要求使用自定义函数完成： 根据部门编号，统计部门总工资的函数。

create or replace function fun_scott_emp(v_deptno number) return number
as
dept_sum number;
begin
select sum(sal) into dept_sum from emp e where e.deptno=v_deptno;
return dept_sum;
end fun_scott_emp;


/*
4：

定义一个包，用来管理emp表
共有属性：员工工资
共有方法：根据员工编号返回员工工资
根据员工工资返回工资等级 需要借助salgrade;

共有重载过程：根据部门编号输出员工姓名。
根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
再 调用“根据部门编号输出员工姓名。”来输出）

私有属性：部门编号
私有方法：根据部门名称得到部门编号

*/

create or replace package pak_emp is

-- 共有属性：员工工资
emp_sal number;

--共有方法：根据员工编号返回员工工资(1) ,根据员工工资返回工资等级 (2) 需要借助salgrade;
function getSalbyeno(eno number) return number;
function getSalgradeby(esal number) return number;

-- 共有重载过程：根据部门编号输出员工姓名。
procedure pro_printName(dno number);

--根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
--再 调用“根据部门编号输出员工姓名。”来输出）
procedure pro_printName(dname number);

end pak_emp;


create or replace package body pak_emp is

-- 私有属性：部门编号
dno varchar2(10);
-- 私有方法：根据部门名称得到部门编号
function getDnobydname(v_dname varchar2) return number
is
begin
select deptno into v_dno from dept where dname=v_dname;
end getDnobydname;

--共有方法：根据员工编号返回员工工资(1) ,根据员工工资返回工资等级 (2) 需要借助salgrade;
function getSalbyeno(eno number) return number
is
begin
select sal into v_sal from emp where empno=eno;
end getSalbyeno;

create or replace function getSalgradeby(esal number) return number
is
begin
select emp_rk.rk salgrade from
(
select e.*,rank() over(order by sal desc) rk from emp e where e.sal=esal
) emp_rk;
end getSalgradeby;

-- 共有重载过程：根据部门编号输出员工姓名。
procedure pro_printName(dno number)
is
begin
select ename into v_ename from emp where deptno=dno;
end;

--根据部门名称输出员工姓名。（要求 先 调用“根据部门名称得到部门编号”私有方法 得到部门编号
--再 调用“根据部门编号输出员工姓名。”来输出）
procedure pro_printName(v_dname number);
is
begin
select e.ename into v_ena from dept d,emp e where dname=v_dname and e.deptno=d.deptno;
end;

end pak_emp;