6月8号练习与答案.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

1.

create or replace function intsum(n int)
return int
as 
 amount int;
 overlarge exception;
begin
 if(n>0) then
  amount := 0;
   for i in 1..n loop
   amount := amount + i;
  end loop;
  return amount;
 else 
  raise overlarge;
 end if;    
 exception
  when overlarge then
  dbms_output.put_line('请输入大于0的整数');   
end intsum;   

 
2.1

create or replace function getsal(n int)
return number
as
 sall emp.sal%type;
begin
 select sal into sall from emp where empno =n;
 return sall;
end; 

2.2

create or replace procedure updateemp(n int,s number)
is
begin
 update emp set sal=s where empno=n;
 exception
  when NO_DATA_FOUND then
   dbms_output.put_line('未查到数据！');
end updateemp;  

2.3


首先加emp的列sql>alter table emp add (id int);
创建索引sql>create sequence sq_20
            increment by 1
            start with 1;



create or replace procedure rowtable
is
 tmpsal emp.sal%type;

 type empno_table is table of emp.empno%type
 index by binary_integer;
 e_table empno_table;
 
 type rowtable is table of emp%rowtype
 index by binary_integer;
 datas rowtable;
begin 
 --alter table emp add (id int);
 --insert into emp (id) values(seq_emp.nextval);
 
 update emp set id=sq.nextval; 
 for i in 1..5 loop    
   select empno into e_table(i) from emp where id=i; 
 end loop;
 
 for j in 1..5 loop
   select * into datas(j) from emp where empno=e_table(j);
   tmpsal := getsal(datas(j).empno); 
   if tmpsal<2500 then
    updateemp(datas(j).empno,3600);
   end if;
 end loop;  
end;    


完成后删除列和索引
sql>alter table emp drop (id);
sql>drop sequence sq;