冯刚.txt

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

1，定义一个函数，输入一个大于0的整数n， 返回1到n的整数和

create  or replace function sum1(i int)
return int
is
s_sum int;
k int;
begin
     s_sum :=0;
     if i >0
     then
     for k in 1..i
     loop
     s_sum:=s_sum+k;
     end loop;
     return s_sum;
     else
     return 0;
     end if;
     end  sum1;
验证
declare
     a int;
     begin
     a := sum1(&a);
     dbms_output.put_line(a);
     end;

 第二题   一小题 定义一个函数，输入emp的empno，返回该行的sal



   create or replace function  sal1(s_no number)
     return number
     is
     s_sal number;
     begin
     select sal into s_sal from emp where empno = s_no;
     return s_sal;
     end sal1;
     
验证  
  declare
     a int;
     begin
     a := sal1(&a);
     dbms_output.put_line(a);
     end;
     
   
    2小题  定义一个更新emp表sal的存储过程，输入参数为empno,sal,根据empno更新相应的sal



     create or replace procedure newemp1(n_empno emp.empno%type,n_sal emp.sal%type)
     is
     begin
     update  emp set emp.sal = n_sal where emp.sal = n_empno;
     commit;
     dbms_output.put_line('更新成功'||n_empno||n_sal);
     end newemp1;
    
  验证
   
  declare   
    begin
    newemp1(22,4000);
    end; 

3小题  定义一个table 里面存放的是%rowtype类型，用该类型定义一个变量datas，从emp表取五行记录放到datas里面进行循环，循环里完成以下操作：先通过刚才定义的函数取sal，如果sal<2500 ,调用刚才写的存储过程更新sal为3600

    
    declare
type table1 is table of emp%rowtype index by binary_integer;
datas table1;
i int;
p int;
begin

 select * into datas(1) from emp where empno=7844;
 select * into datas(2) from emp where empno=7934;
 select * into datas(3) from emp where empno=7369;
 select * into datas(4) from emp where empno=7521;
 select * into datas(5) from emp where empno=7788;
 for i in 1..5
 loop
 p:=sal1(datas(i).empno);
 if p>= 2500
 then
 dbms_output.put_line(p);
 else
 newemp1(datas(i).empno,3600);
 commit;
 dbms_output.put_line('修改成功');
 end if;
 end loop;
 end;