Oracle基础学习（二） 存储过程和函数

--创建函数语法
create [or replace] function [schema.]function_name
(函数参数列表) --参数有IN、OUT、IN OUT三种类型；IN代表需要输入的参数，OUT代表要返回的参数，IN OUT代表即是输入参数也是返回参数。
return datetype--返回类型               
-- out参数：查询某个员工姓名、月薪和职位 -- 原则： 如果只有一个返回值，用存储函数；否则，就用存储过程。
[is | as ]---任选一个，后面跟pLSQL代码块
[declare]--有参数时使用关键字
begin
  --执行的SQL
end;


--实例

1.

根据输入参数的部门内码获得部门名称

CREATE OR REPLACE FUNCTION f_get_name(i_empno IN VARCHAR2)
RETURN VARCHAR2 IS
v_emp_name emp.ename%TYPE;
BEGIN
SELECT t.ename INTO v_emp_name
FROM emp t WHERE t.empno = i_empno;
RETURN(v_emp_name);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('你需要的数据不存在');
WHEN OTHERS THEN
dbms_output.put_line('发生其他错误');
END f_get_name;

2.
create or replace function avg_pric(v_total in number(10.2),
v_num In OUT number(10,2)) return  number(10,2)
as
v_pric number(10,2);
begin
  v_pric:=v_total/v_num;
  
  return v_pric;
  end;

--查看函数
select * from  User_Procedures;--用户拥有的触发器、存储过程、函数查询表
select * from User_Source;--查询用户拥有触发器、存储过程、函数的源码。

select * from dba_Procedures;--系统表
select * from dba_Source;--系统表

3.--查询某个员工的年收入
create or replace function queryemp_income(eno in number)
return number
as 
    --定义变量接收薪水和奖金
    p_sal emp.sal%type;
    p_comm emp.comm%type;
begin
  select sal,comm into p_sal,p_comm from emp where empno=eno;
  --nvl为遇空函数，如果p_comm为空则返回0
  return nvl(p_comm,0)+p_sal*12;
end;

　存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

调用存储过程方式一，exec 存储过程名

调用存储过程方式二，PLSQL程序

1.无参数存储过程
create or replace procedure pro_no_par is  
begin  
update employees set salary=salary+100 where employee_id=100;  
commit;  
dbms_output.put_line('工资已经调整！！');  
end pro_no_par;  
--执行,使用execute  
execute pro_no_par; 

2.in传入参数存储过程

create or replace procedure pro_no_par 
is 

　　begin
　　update employees set salary=salary+100 where employee_id=100;
　　commit;
　　dbms_output.put_line('工资已经调整！！');
　　end pro_no_par;
　　--执行,使用execute
　　execute pro_no_par;

 3. out参数　

  declare
  ex_var_1 number;
  ex_var_2 employees.first_name%type;
  ex_var_3 employees.phone_number%type;
  begin
   ex_var_1:=102;
　　pro_out_par(ex_var_1,ex_var_2,ex_var_3);
　　dbms_output.put_line('员工号为'||ex_var_1||'的员工姓名是;'||ex_var_2||',联系电话是：'||ex_var_3);
  end;

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型

　　

-- out参数：查询某个员工姓名、月薪和职位
-- 原则： 如果只有一个返回值，用存储函数；否则，就用存储过程。
create or replace procedure queryempinfor(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;
/

--将人员表中经理职位并且年薪大于7万的人员信息插入到MANAGER表中，输出参数是插入的条数

create or replace procedure p_manager_update(o_ename_num out number) is
begin
select to_number(count(1)) into o_ename_num
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER';
insert into MANAGER(INCODE,ENAME,CREATE_DATE)
select deptno_seq.nextval,e.ename,sysdate
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000
and e.job='MANAGER'; 
commit;
end p_manager_update;

--在COMMONAD WINDOWS里执行

exec p_manager_update;

 

--显示cursor的处理

declare 

---声明cursor,创建和命名一个sql工作区

cursor cursor_name is 

     select real_name from account_hcz;

     v_realname varchar2(20);

begin

     open cursor_name; ---打开cursor,执行sql语句产生的结果集

     fetch cursor_name into v_realname; --提取cursor,提取结果集中的记录

     dbms_output.put_line(v_realname);

     close cursor_name; --关闭cursor

end ;