你的位置:首页 > 数据库

[数据库]简单的sql的存储过程及存储函数


--存储过程
create or replace procedure raisesalary(eno in number)
as

psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
update emp set sal=sal+100 where empno=eno;
dbms_output.put_line('qian:'||psal||' hou:'||(psal+100));

end;
/

--调用存储过程函数
begin
-- Call the procedure
raisesalary(eno => :eno);
end;


--存储函数
create or replace function query(eno in number)
return number
as

psal emp.sal%type;
pcomm emp.comm%type;

begin

select sal,comm into psal,pcomm from emp where empno =eno;
return psal*12+pcomm;

end;
/

--调用存储函数函数
begin
-- Call the function
:result := query(eno => :eno);
end;

--使用存储过程out多个参数

create or replace procedure info(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin

select ename,sal,job into pename,psal,pjob from emp where empno=eno;

end;
/

--调用函数
begin
-- Call the procedure
infoqq(eno => 7369,
pename => :pename,
psal => :psal,
pjob => :pjob);
end;