存储过程

--存储过程
--pl/sql

过程参数有IN/OUT/IN OUT三种类型，其中IN类型可以被过程体引用，但不能改写值；OUT类型不可被过程体引用，但可改写值；IN OUT 既可以被过程体引用，又可以改写值.
在调用in out类型时，调用过程插入参数时，不能直接用常量。必须用变量。这样才能接收out返回来的值。同理：用out的也不能直接用常量。

create procedure div(a int,b int)
as
c float;
begin
c:=a*1.0/b;
dbms_output.put_line(a||'/'||b||'='||c);
Exception
when others then dbms_output.put_line('除数为0');
end;

begin
div(10,0);
end;

begin
div(b=>20,a=>10);
end;

create procedure pc1
(a int:=20,
b int:=30,
c int:=40,
d int:=50)
as
e int;
begin
e:=a+b+c+d;
dbms_output.put_line(e);
end;

begin
pc1(1,2,3,4);
end;
begin
pc1(b=>1,d=>2,c=>3);
end;
begin
pc1(10,d=>2);
end;

create or replace procedure pc2
(a int,b in out int ,c out int )
as
begin
c:=a+b;
b:=a*b;
end;

declare
x int:=20;
y int:=30;
begin
pc2(10, x , y );
dbms_output.put_line(x||','||y);
end;

select * from users

create or replace procedure procAddUser
(
name varchar,
phone varchar,
mail varchar,
loginid varchar,
loginpwd varchar,
gender int:=1,
roleid int:=1,
stateid int:=1,
address varchar:='未知'
)
as
begin
insert into users values (
USERS_ID_ASQ.nextval,name,address,
phone,mail,stateid,0,loginid,loginpwd,
roleid,gender);
end;

begin
procadduser('haha','110','aa@aa.com','haha','123');
end;
select * from users

grant execute on div to abc
grant select on books to abc

create function fndiv
(a int,b int)
return float
as
c float;
begin
c:=a*1.0/b;
return c;
end;

select fndiv(10,20) from dual
select id,title,unitprice, fndiv(unitprice,2) 半价
from books

declare
x float;
begin
x:=fndiv(10,20);
dbms_output.put_line(x);
end;

create or replace package haha1
is
function fndiv(a int,b int) return float;
procedure add(a int,b int);
end;

create or replace package body haha1
is
function fndiv(a int,b int) return float
as
begin
return a*1.0/b;
end;
procedure add(a int,b int)
as
c int;
begin
c:=a+b;
dbms_output.put_line(c);
end;
end;

select haha1.fndiv(10,20) from dual
begin
haha1.add(10,haha1.fndiv(10,20));
end;