oracel[过程+函数+包]

过程

eg:-----------------------------------------传参数

create or replace procedure test_pro(p_name varchar2,p_sal varchar2) is

begin

update test set sal=p_sal where name=p_name;

end;

/

exec test_pro('tom','120');

eg2:-----------------------------------------无参数

create or replace procedure test_p is

begin

insert into mytest values('tom','jerry');

end;

/

-----show  error;

                                                   函数

create function first_fun(pName varchar2)return number is yearSal number(7,2)

begin

select sal*12_nvl(reward,0)*12 into yearSal from mytest where name=pName;

return yearSal;

end;

/

----调用函数

var income number;

call furst_fun('TOM') into:income;

print income;

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

declare

p_name varchar2(20);

p_pass varchar2(20);

begin

select name,passwd into p_name,p_pass from mytest where name=&no/name;

dbms_output.put_line('用户名:'||p_name||',密码:'||p_pass);

exception

when no_data_found then

dbms_output.put_line('对不起，请您输入正确的姓名！');

end;

/

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

包头+包体

---------包头

create package first_pak is

procedure update_sal(name varchar2,newSal varchar2);

function annual_income(name varchar2)return number;

end;

/

---------包体

  create or replace package body first_pak is

  procedure update_sal(name varchar2,newSal number)

  is

  begin

  update emp set sal=newSal where ename=name;

  end;

  function annual_income(name varchar2)

  return number is

  annual_salary number;

  begin

  select sal*12_nvl(comm,0) into annual_salary from emp where ename=name;

  return annual_salary;

  end;

  end;