Oracle学习交流（4） -----开发子程序和包

 

--创建存储过程
create or replace procedure proc_first
as
begin
   dbms_output.put_line('hello world!');
end;

begin
proc_first;
end;

drop procedure proc_first

--带参数的存储过程 PS:括号内是参数,BEGIN前是变量
create or replace procedure proc_Second
(empid number) --is或as前是输入或输出参数
is
tempsalary number;--局部变量
begin
select sal into tempsalary from emp where empno = empid;
dbms_output.put_line(tempsalary);
end;

begin
  proc_Second(empid => 7499);--命名传参,可混乱
  proc_Second(7499);--匿名传参,顺序必须一直
  end;
drop procedure proc_Second;

--带输出参数的存储过程
create or replace procedure proc_third(
       empid number,
       v_salary out number  --表示输出参数
)as
begin
  select sal into v_salary from emp where empno = empid;
end;

--调用带OUT的参数.备注,参数需在括号内,不需要用到OUT
declare
vsalary number;
begin
  proc_third (7499,vsalary);
  dbms_output.put_line(vsalary);
end;

drop procedure proc_third;

--INOUT存储过程
create or replace procedure proc_four(
       paramOne in out number,--输入输出
       paramTwo in out number
)
as
v1 number;
v2 number;
begin
  v1:=paramOne*paramTwo;
  v2:=Mod(paramOne,paramTwo);
  paramOne:=v1;
  paramTwo:=v2;
end;

--调用
declare
v1 number :=10;
v2 number :=2;
begin
  proc_four(paramOne=>v1,paramTwo=>v2);
  dbms_output.put_line(v1||'       '||v2);
end;

drop procedure proc_four;

--函数
create or replace function firstfun return varchar2 as --必须先定义返回类型在as后才能声明局部变量
vuser varchar2(10);
begin
  select username into vuser from user_users;--查询当前登录用户
  return vuser;
end;

--调用
begin
dbms_output.put_line(firstfun);
end;

--有IN参数的函数
create or replace function SecondFun(empid emp.empno%type) return emp.ename%type as
empName emp.ename%type;
begin
  select ename into empName  from emp where emp.empno=empid;
  return empName;
end;

--调用
declare
empid emp.empno%type;
begin
  dbms_output.put_line(SecondFun(7499));
end;

 

--有OUT参数的函数
create or replace function ThirdFun(
       empId emp.empno%type,
       empName out emp.ename%type
) return emp.sal%type
as
Vsalary number;
begin
  select ename ,sal into empName ,Vsalary from emp where emp.empno=empId;
  return Vsalary;
end;
--调用
declare
ename emp.ename%type;
begin
  dbms_output.put_line(ThirdFun(&empid,ename));
  dbms_output.put_line(ename);
end;

 

--查询用户子程序和源代码
select * from user_source

select * from user_objects
--包
create or replace package my_page is   --is后边通常书写的是局部变量
g_empNo number;
procedure proc_updateSalary_employee(
          param_empNo number
  );
function fun_get_salary(param_empNo number) return number;
end my_page;

--包的身体
create or replace package body my_page as --as后必须是包头实现

procedure proc_updateSalary_employee(
  param_empNo number
  ) as
  begin
    update emp set sal=sal+1 where emp.empno=param_empNo;
  end;
 
 function fun_get_salary(param_empNo number) return number
   as
   salaryTemp number;
   begin
     select sal into salaryTemp from emp where emp.empno=param_empNo;
     return salaryTemp;
   end;
   end my_page;
  
  
 --调用
 begin
   dbms_output.put_line(my_page.fun_get_salary(7499));
   my_page.proc_updateSalary_employee(7499);
   dbms_output.put_line(my_page.fun_get_salary(7499));
 end;
 
 --授权
 grant execute  on my_page to hr;
begin
   dbms_output.put_line(scott.my_page.fun_get_salary(7499));
end;