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;