oracle包函数过程,oracle 函数，包，存储过程简单实例

/*

存储过程

参数类型：

in 表示入参

out 表示出参

in out 表示既是入参又是出参

默认情况下是入参

注意： 存储过程的参数数据类型不需要指定精度

*/

create or replace procedure pro_myjob(title in varchar2)

is

var_mytitle jobs.job_title%type;

begin

select j.job_title into var_mytitle from jobs j where j.job_id=title;

dbms_output.put_line('工种:'||var_mytitle);

exception

when no_data_found then

raise_application_error(-20000,'no data found');

end;

create or replace procedure sp_insertJobs(p_id varchar2,p_title varchar2,p_salarymin number,p_salarymax number)

is

begin

insert into jobs values(p_id,p_title,p_salarymin,p_salarymax);

commit;

end;

--出参---

create or replace procedure sp_myjobForOut(p_title in varchar2, p_errMsg out varchar2)

as

var_mySalary jobs.max_salary%type;

--var_mySalary number(10);

begin

select j.max_salary into var_mySalary from jobs j where j.job_id=p_title;

dbms_output.put_line('最高薪水:'||var_mytitle);

exception

when no_data_found then

p_errMsg:='没有'||p_title||'工号对应的数据';

when too_many_rows then

p_errMsg:=p_title||'工号对应的数据过多';

when others then

p_errMsg:='不确定的错误';

end;

--存储过程调用-----

declare

var_err varchar2(200);

begin

dbms_output.put_line('bef'||var_err);

sp_myjobForOut('AD_PREwS',var_err);

dbms_output.put_line('af'||var_err);

end;

--参数 in out-----

create or replace procedure sp_myjobintout(p_msg in out varchar2)

is

v_msg varchar2(200);

begin

select j.max_salary into v_msg from jobs j where j.job_id=p_msg;

p_msg:='最高薪水:'||v_msg;

exception

when no_data_found then

p_msg:='没有工号对应的数据';

when too_many_rows then

p_msg:='工号对应的数据过多';

when others then

p_msg:='不确定的错误';

end;

declare

var_err varchar2(200):='AD_PRESs';

begin

dbms_output.put_line('bef'||var_err);

sp_myjobintout(var_err);

dbms_output.put_line('af'||var_err);

end;

select * from jobs

declare

var_msg varchar2(20);

begin

pro_myjob('AD_PRES2',var_msg);

dbms_output.put_line(var_msg);

end;

--存储过程的查看-----

select * from user_source

where lower(name) = 'sp_myjobintout';

--删除------

drop procedure sp_myjobintout

select * from jobs

--------------创建函数--------------------

select * from demo where did=1;

select substr(dname,3,3) from demo where did=1;

create or replace function f_demo(aid number) return varchar2

is

name1 varchar2(20);

begin

select dname into name1 from demo where did=aid;

return name1;

end;

-------------调用-----

declare

name1 varchar2(20);

begin

--name1:=f_demo(aid=>3);

select f_demo(3) into name1  from dual;

dbms_output.put_line(name1);

end;

-----2 comandline

var name1 varchar2;--定义变量

exec :name1:=f_demo(1);---执行  :name1

--------------------------------------

create or replace function f_demo_row(aid number) return demo%rowtype

is

rowdata demo%rowtype;

begin

select * into rowdata from demo where did=aid;

return rowdata;

exception

when others then

dbms_output.put_line('error:'||sqlerrm);

end;

----------pl/sql调用------

declare

rowdata1 demo%rowtype;

begin

rowdata1:=f_demo_row(3);

dbms_output.put_line(rowdata1.did||'  '||rowdata1.dname);

end;

-------------------包------------

create or replace package pkg_t2

is

procedure p_demo(aid in number,aname in out varchar2);

function f_demo(aid number) return varchar2;

function f_demo(aname varchar2) return demo%rowtype;

end pkg_t2;

---包体--------

create or replace package body pkg_t2

is

procedure p_demo(aid in number,aname in out varchar2)

is

name1 varchar2(10);

begin

select dname into name1 from demo where did=aid;

aname:=name1;

end p_demo;

function f_demo(aid number) return varchar2

is

name1 varchar2(20);

begin

select dname into name1 from demo where did=aid;

return name1;

end f_demo;

function f_demo(aname varchar2) return demo%rowtype-- ref

is

rowdata demo%rowtype;

begin

select * into rowdata from demo where dname=aname;

return rowdata;

exception

when others then

dbms_output.put_line('error:'||sqlerrm);

end;

end pkg_t2;

------------调用-----------

pkg_t2.f_demo(aname=>'sss')

------------包 与 游标-----------------

create or replace package pkg_cur is

type my_cur_type is ref cursor;

cursor mycur return jobs%rowtype;

procedure sp_getdata(p_sql varchar2,p_cursor in out my_cur_type);

end pkg_cur;

create or replace package body pkg_cur is

cursor mycur return jobs%rowtype is select * from jobs;

procedure sp_getdata(p_sql varchar2,p_cursor in out my_cur_type)

as

begin

open p_cursor for p_sql;

end sp_getdata;

end pkg_cur;

declare

my_cur pkg_cur.my_cur_type;

rowdata jobs%rowtype;

begin

-- my_cur:=pkg_cur.mycur;

open pkg_cur.mycur;

loop

fetch pkg_cur.mycur into rowdata;

exit when pkg_cur.mycur%notfound;

dbms_output.put_line('did:'||rowdata.job_id||'   dname:'||rowdata.job_title);

end loop;

close pkg_cur.mycur;

end;

select * from jobs

create or replace procedure sp_getdata(p_sql varchar2,p_cursor in out pkg_cur.my_cur_type)

as

begin

open p_cursor for p_sql;

end;