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;