oracle过程和函数

--创建简单过程
create or replace procedure pro_hello
(u_name in varchar2) as
begin
  dbms_output.put_line(u_name);
end;
--调用过程
begin
  pro_hello('dddd');
end;

select * from emp;
--使用游标的过程
create or replace procedure pro_empbySal
(e_sal in number)
as
       cursor cur_emp is
              select * from emp
              where sal>e_sal;
begin
  for e in cur_emp loop
    dbms_output.put_line(e.ename);
  end loop;
end;

begin
   pro_empbySal(3000);
end;
--带输入输出参数的过程
create or replace procedure pro_emp_count
(e_sal in number,e_num out number)
as
begin
  select count(empNo) into e_num from emp
         where sal > e_sal;
end;

--调用过程
declare
         e_num number;
begin
         pro_emp_count(2000,e_num => e_num);
         dbms_output.put_line(e_num);
end;
--简单函数
create or replace function fun_emp_count
(e_sal in number)
return number as
       f_num number;
begin
  select  count(*) into f_num from emp where sal>e_sal;
  return f_num;
end;
--调用函数
declare
  f_num number;
begin
   f_num:=fun_emp_count(2000);
   dbms_output.put_line(f_num);
end;

--创建包头
create or replace package pack_emp as
       --过程
       procedure pro_emp(e_sal in number,e_num out number);
       --函数
       function fun_emp(e_sal in number) return number;
end pack_emp;

--创建包体
create or replace package body pack_emp as
       --实现过程
       procedure pro_emp(e_sal in number,e_num out number) is
       begin
         select count(*) into e_num from emp where sal > e_sal;
       end pro_emp;
      
      
       --实现函数
       function fun_emp(e_sal in number) return number
       is e_num number;
       begin
         select count(*) into e_num from emp where sal > e_sal;
         return e_num;
       end fun_emp;
end pack_emp;

--调用包中的过程

declare
    n number;
begin
  pack_emp.pro_emp(e_num=>n,e_sal => 2000);
  dbms_output.put_line(n);
end;

--调用包中的函数
declare
    n number;
begin
    n:=pack_emp.fun_emp(2000);
    dbms_output.put_line(n);
end;

--返回游标的存储过程
create or replace procedure proc_emp_cur
(cur out sys_refcursor,
e_sal in number default 2000)
as
begin
  open cur for select * from emp where sal>e_sal;
end;

--调用
declare
  c sys_refcursor;
  e emp%rowtype;
begin
  proc_emp_cur(c);
  loop
    fetch c into e;
    if c%notfound then
      exit;
    end if;
    dbms_output.put_line(e.ename);
  end loop;
end; 

create table user_emp as
       select empno,ename,job,sal from emp;

select * from user_emp;