oracle中的过程和函数,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;