数据库函数，游标，存储过程等的使用（oracle）

1.定义并输出

declare

pname table.field%type;

begin

select field into pname from table t where t.field_b=x;

dbms_output.put_line(pname);

end;

 

2.ifelse循环的使用

declare

age number(3);

marry boolean := true; --boolean不能直接输出

pname varchar2(10) := '';

begin

age := ;

dbms_output.put_line(age);

if marry then

dbms_output.put_line('true');

else

dbms_output.put_line('false');

end if ;

dbms_output.put_line(pname);

end;

 

3.loop循环

declare

pnum number(4):=0;

begin

while pnum < 10 loop

dbms_output.put_line(pnum);

pnum := pnum + 1;

end loop;

end;

 

declare

pnum number(4):=0;

begin

loop

exit when pnum=10;

pnum:=pnum+1;

dbms_output.put_line(pnum);

end loop;

end;

 

4.游标的定义和使用

declare

cursor c1 is

select * from tab;

emprec tab%rowtype;

begin

open c1;

loop

fetch c1

into emprec;

exit when c1%notfound;

dbms_output.put_line(emprec.empno || ' ' || emprec.ename);

end loop;

close c1; --要记得关闭游标

end;

 

5.异常的定义和输出

declare

pnum number(4) := 10;

begin

pnum := pnum / 0;

exception

when zero_pide then

dbms_output.put_line('not is 0');

when value_error then

dbms_output.put_line('convert error');

when others then

dbms_output.put_line('other');

end;

 

6.存储过程

create or replace procedure addsal(eno in tab.empno%type) is

emprec tab%rowtype;

begin

select * into emprec from tab t where t.empno = eno;

 

update tab t set t.sal = t.sal + 100 where t.empno = eno;

dbms_output.put_line('oldSalary' || emprec.sal || ',newSalary' ||

(emprec.sal + 100));

end addsal;

 

7.存储函数

create or replace function accf_yearsal(eno in tab.empno%type)

return number is

Result number;

psal tab.sal%type;

pcomm tab.comm%type;

begin

select t.sal, t.comm into psal, pcomm from tab t where t.empno = eno;

Result := psal * 12 + nvl(pcomm, 0);

return(Result);

end accf_yearsal;