章猛.txt

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

第一题
（为什么输入小数也可以得到值？）(pl/sql 中为什么没有int?)

create or replace function sum_n(n int)
  return int
as
   renn int :=1;
   n_sum int :=0;
   n_error exception;
begin
 if n<=0 then
    raise n_error;
 end if;
 while renn <= n loop
    n_sum:=renn+n_sum;
    renn:=renn+1;
 end loop;
 return n_sum;
exception
 when n_error then
     dbms_output.put_line('输入错误');
     return null;
end;

/* 
declare 
 zm int;
 mz int;
begin
 zm:=&aa;
 mz :=sum_n(zm);
 dbms_output.put_line(mz);
end;
*/

2.1
/*
create or replace function no_re_sal(f_empno number)
 return number
as
 f_sal number(8,2);
begin
 select sal into f_sal from zmemp where empno=f_empno;
 return f_sal;
exception
 when no_data_found then
   dbms_output.put_line('该员工不存在');
   return null;
end;
*/


2.2
/*
create or replace procedure up_emp(up_no number,up_sal number)
as
begin
  update zmemp set sal = up_sal where empno = up_no;
  commit;
end;
*/

2.3
declare
 type tabler is table of zmemp%rowtype index by binary_integer;--定义tabler类型
 datas tabler;
 p_sal number;--通过函数返回sal值
 i int :=1;
 --sum_row int;
begin
 --select count(1) into sum_row from zmemp;
 
 select * into datas(1) from zmemp where empno=7369;
 select * into datas(2) from zmemp where empno=7499;
 select * into datas(3) from zmemp where empno=7566;
 select * into datas(4) from zmemp where empno=7521;
 select * into datas(5) from zmemp where empno=7698;
 while i<=5 loop
    p_sal :=no_re_sal(datas(i).empno);--通过函数返回sal值
    if p_sal < 2500 then              --比较返回的sal值
      up_emp(datas(i).empno,3600);    --更新sal值到emp表中
    else
      null;
    end if;
    i:=i+1;
    commit;
 end loop;
exception
  when no_data_found then
  dbms_output.put_line('数据不存在');
end;