存储过程(Oracle)学习20170917

一、知识点
1.使用procedure和function的原则是：若只需要返回1个值，则使用function，其他使用procedure
2.一般不需要在存储过程或者存储函数中commit和rollback，但最好做提交
3.调试存储过程需要赋权限:(1)以管理员登录：sqlplus / as sysdba show user (2)赋权：grant DEBUG CONNECT SESSION , DEBUG ANY PROCEDURE to c##scott;

1.第一个存储过程：打印Hello World

/*
调用存储过程
1.exec sayhelloworld（sqlplus语句） 
2.
begin
    sayhelloworld()
    sayhelloworld()
end;
/
*/
create or replace procedure sayhellowold
as
--说明部分
begin
    dbms_output.put_line('Hello World')
end;
/

2.创建一个带参数的存储过程：
给指定的员工涨100元钱的工资，并且打印涨前和涨后的薪水

/*
如何调用
begin
    raisesalary(7839);
    raisesalary(7566);
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;

    --需不需要commit？
    --注意：一般不需要再存储过程或者存储函数中commit和rollback
commit;

--打印
dbms_output.put_line('涨前：'||psal||'涨后'||(psal+100));

end;
/

3.查询某个员工的姓名、月薪和职位

create or replace procedure queryempinform
    (eno in number,
    pename out varchar2,
    psal out number,
    pjob out varchar2)
as
begin
    --得到该员工的姓名月薪和职位
    SELECT ename,sal,job into pename,psal,pjob from emp where empno=eno;
end;
/

4.创建一个存储函数
存储函数：查询某个员工的年收入

create or replace function queryempincome(eno in number)
return number
as
    --定义变量保存员工的薪水和奖金
    psal emp.sal%type;
    pcomm emp.comm%type;
begin
    --得到该员工的月薪和奖金
    select sal,comm into psal,pcomm from emp where empno=eno;

    --直接返回年收入
    return psal*12+nvl(pcomm,0);
end;

5.创建一个存储过程，以员工号为参数，输出该员工的工资

create or replace procedure  p_dgy1
    (v_empno in emp.empno%type,
    v_sal out emp.sal%type)
as
--创建一个存储过程，以员工号为参数，输出该员工的工资
begin
    select sal into v_sal from emp where empno=v_empno;
    dbms_output.put_line('工号：'||v_empno||'的员工工资为：'||v_sal);
end;
/

6.创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回)

create or replace procedure p_dgy2
    (v_empno in emp.empno%type,
    v_year out number)
as
begin
    select round((sysdate - hiredate)/365,1) into v_year from emp where empno=v_empno;
    dbms_output.put_line('工号为：'||v_empno||'的员工工作年限为：'||v_year||'年');
end;
/

7.创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门则工资增加150，若属于20号部门则工资增加200，若属于30号部门则工资增加250，若属于其他部门则增加300

create or replace procedure p_dgy3
    (v_empno in emp.empno%type)
as
    v_deptno emp.deptno%type;
    v_sal emp.sal%type;
begin
    select deptno into v_deptno from emp where empno=v_empno;
    select sal into v_sal from emp where empno=v_empno;
    dbms_output.put_line('部门号：'||v_deptno||'员工号：'||v_empno||'修改前的工资为'||v_sal);

    case v_deptno
    when 10 then update emp set sal=sal+150 where empno=v_empno;
    when 20 then update emp set sal=sal+200 where empno=v_empno;
    when 30 then update emp set sal=sal+250 where empno=v_empno;
    else update emp set sal=sal+300 where empno=v_empno;
    end case;

    select sal into v_sal from emp where empno=v_empno;
    dbms_output.put_line('部门号：'||v_deptno||'员工号：'||v_empno||'修改后的工资为'||v_sal);

end;
/