PL/SQL例子2

create or replace procedure c
(
v_deptno  in emp.deptno%type,
v_max out emp.sal%type
)
as
begin
select max(sal+nvl(comm,0)) into v_max from emp where deptno=v_deptno;
end;

 

 

 

create or replace procedure cc
(
v_empno  in emp.empno%type,
v_sal out emp.sal%type,
v_comm out emp.comm%type,
v_tol out emp.sal%type
)
as
begin
select sal,nvl(comm,0),(sal+nvl(comm,0)) into v_sal,v_comm,v_tol from emp where
empno=v_empno;
end;

 

 

create or replace procedure upsal
(
v_deptno in number,
v_sal in number
)
as
begin
update emp set sal=v_sal where deptno=v_deptno;
end;

 

 

 

create or replace procedure somsal
(
v_empno in number,
v_sal in number
)
as
begin
update emp set sal=v_sal where empno=v_empno;
end;

 

 

 

create or replace function maxsal
(
v_deptno in number
)
return number
is
v_maxsal number;
begin
select max(sal) into v_maxsal from emp where deptno=v_deptno;
return v_maxsal;
end;

 

 

 

create or replace procedure cursor
(v_cursor out mypack.mycursor
)
is
begin
open v_cursor for select * from emp ;
end ;

 

 

create or replace function selalldep
(a out number,
c in number
)
return number
as
b number;
begin
select count(empno),sum(sal+nvl(comm,0)) into a,b from emp
where deptno=c;
return b;
end;

 

 

 

create or replace package mypack is
type mycursor is ref cursor;
end ;

declare
i number(3):=1;
j number(3):=2;
begin

loop

insert into t_user values(i,'a'||i,'a'||i,1,to_date('2007-01-01','yyyy-mm-dd'),1,i,'sdd');

insert into t_user values(j,'a'||j,'a'||j,1,to_date('2007-01-01','yyyy-mm-dd'),2,j,'sdd');

i:=i+2;
j:=j+2;
exit when i>600 and j>600;

end loop;
end;