ORACLE中的PL/SQL过程和函数

--prcedure过程----〉存储过程 没有返回值的函数

--function函数----〉有返回值


--PL/SQL BLOCK块

--声明部分（可选的）

declare

i number;

--可执行部分（必需的）

begin

select sal into i from emp where empno=&empno;

--异常处理部分（可选的）

exception

when no_data_found then

dbms_output.put_line('没有这个员工！');

end;

/


/*把以上内容永久保存为一个数据库对象，

起一个名字，通过该名字以后就可以调用*/


--什么是存储过程（存储过程的声明+pl/sql块）

--最简单的存储过程

create procedure myproc

as

begin

null;

end;

/


--or replace 的含义如果对象已经存在就替换

create or replace procedure myproc

as

begin

dbms_output.put_line('is my proc!');

end;

/


--如何把pl/sql块改造为存储过程

--利用as替换declare

--过程可以有参数，用参数替代 块中让用户在运行时输入的值

create or replace procedure myproc1(eno number)

as

--declare

i number;

--可执行部分（必需的）

begin

select sal into i from emp where empno=eno;

dbms_output.put_line(i);

--异常处理部分（可选的）

exception

when no_data_found then

dbms_output.put_line('没有这个员工！');

end;

/


--exec myproc1(7369);


--如何能在java应用程序中能接到 数据库 存储过程的返回值

--过程的参数分三种  in   out   in out (按地址传递，可进可出)

--out 特殊的按地址传递，空进 带值出

create or replace procedure myproc2(a number,b out number,c in out number)

as

begin

b:=a*2;

c:=c*2;

end;

/

--b 传入一个空变量  过程执行完毕后 改变量将会有值


declare

bb number;

cc number;

begin

cc:=100;

myproc2(10,bb,cc);

dbms_output.put_line(bb);

dbms_output.put_line(cc);

end;

/


create table myuser

(id number,

username varchar2(20),

password varchar2(20),

role number);


insert into myuser values(1,'tom','123',1);

insert into myuser values(2,'guo','123',5);

insert into myuser values(3,'zzq','123',1);



--过程和函数的参数和返回值只用写类型，不用写长短

create or replace procedure 

checkuser(uname varchar2,pword varchar2,isok out varchar2)

as

i number;

begin

select count(*) into i from myuser where username=uname and password=pword;

if i=1 then

isok:='y';

else

isok:='n';

end if;

end;

/


--函数 比过程多一个返回值 利用异常处理的函数 访问数据库一次

create or replace function myfunc(eno number) return varchar2

as

i varchar2(20);

begin

select ename into i from emp where empno=eno;

return i;

exception

when no_data_found then 

i:='none';

return i;

end;

/


--如何调用函数

declare

p varchar2(20);

begin

p:=myfunc(7777);

dbms_output.put_line(p);

end;

/




--不用异常处理的方式,缺点：两次访问数据库

create or replace function myfunc(eno number) return varchar2 as

  i varchar2(20);

  p number;

begin

  select count(*) into p from emp where empno = eno;

  if p >= 1 then

    select ename into i from emp where empno = eno;

    return i;

  else

    i := 'none';

    return i;

  end if;

end;

/


--登录的函数版本

create or replace function 

mycheckuser(uname varchar2,pword varchar2,myrole out number) 

return varchar2

as

i varchar2(5);

begin

select role into myrole from myuser 

where username=trim(uname) and password=trim(pword);

i:='y';

return i;

exception 

when no_data_found then

i:='n';

return i;

end;

/


--调用该函数

declare

p varchar2(5);

i number;

begin

p:=mycheckuser('zzq','123',i);

if p='y' then

dbms_output.put_line('login ok the user''s role is '||i);

else

dbms_output.put_line('username or password is wrong!!');

end if;

end;

/


实现登录的过程

插入纪录的过程

更新记录的过程

删除纪录的过程


--书写函数接受员工的员工号码返回它的所在部门

create or replace function mydno(eno number) return number

as

d number;

begin

select deptno into d from emp where empno=eno;

return d;

exception

when no_data_found then

d:=0;

return d; 

end;

/ 


--书写过程接受员工的员工号码返回它的工资

create or replace procedure mysal(eno number,esal out number)

as

begin

select sal into esal from emp where empno=eno;

exception

when no_data_found then

esal:=-1;

end;

/


--接受员工号码按规则给员工增长工资

create or replace procedure upsal(eno number) as

  dd number;

  ss emp.sal%type;

  eee exception;

begin

  dd := mydno(eno);

  mysal(eno,ss);

  --dd=0说明该员工并不存在

  if dd = 0 then

    raise eee;

  else

    if dd = 10 then

      if ss * 1.1 > 5000 then

        update emp set sal = 5000 where empno = eno;

      else

        update emp set sal = sal * 1.1 where empno = eno;

      end if;

    elsif dd = 20 then

      if ss * 1.2 > 5000 then

        update emp set sal = 5000 where empno = eno;

      else

        update emp set sal = sal * 1.2 where empno = eno;

      end if;

    elsif dd = 30 then

      if ss * 1.3 > 5000 then

        update emp set sal = 5000 where empno = eno;

      else

        update emp set sal = sal * 1.3 where empno = eno;

      end if;

    else

      null;

    end if;

  end if;

exception

  when eee then

    raise_application_error(-20005, '该员工并不存在！');

end;

/

--调用上边的过程

create or replace procedure upallsal 

as

cursor mycur is select * from emp;

begin

for ee in mycur

loop

upsal(ee.empno);

end loop;

end;

/