张杨.txt

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

1.定义一个函数，输入一个大于0的整数n， 返回1到n的整数和
/*create or replace function sumzsh1(n int)
return int
as
  sum1 int:=0;i int ; n_error exception;
begin 
   if n>=0 then
        for i in 1..n
        loop
         sum1:=sum1+i;
         end loop;
          return sum1;
     else
        raise n_error; 
    end if;
  exception
    when n_error then
         dbms_output.put_line('chi shu bu xing');
    
end sumzsh1;*/
declare 
   n int;
   sss int;
 begin 
  
    sss:=sumzsh1(&n);
   dbms_output.put_line('sum='||sss);
 end;

2，定义一个函数，输入emp的empno，返回该行的sal
create or replace function salreturn (v_empno varchar2)
return number
is 
v_empsal number;
begin 
  select sal into v_empsal from emp where empno=v_empno;
  return v_empsal;
  exception
     when no_data_found then
       dbms_output.put_line('bu cun zai');
      return null;
end salreturn;


3   定义一个更新emp表sal的存储过程，输入参数为empno,sal,根据empno更新相应的sal
create or replace procedure updatesal(v_empno varchar2,v_sal emp.sal%type)
is
begin 
    update emp set sal=v_sal where empno=v_empno;
    
 end updatesal;
4   定义一个table 里面存放的是%rowtype类型，用该类型定义一个变量datas，从emp表取五行记录放到datas里面进行循环，循环里完成以下操作：先通过刚才定义的函数取sal，如果sal<2500 ,调用刚才写的存储过程更新sal为3600
create or replace procedure updatetable
is
 type emp_table_type is table of emp%rowtype index by binary_integer;
 type emp_empno_type is table of emp.empno%type index by binary_integer;
 datas emp_table_type;
 empnotable emp_empno_type;
 i number;
 v_sal number;
 begin 
   for i in 1..5 loop
     select empno into empnotable(i) from emp where i=i;
   end loop;
   from i in 1..5 loop
     select * into datas(i) from emp where empno=empnotable(i);
     v_sal:=salreturn(datas(i).empno);
     if v_sal<2500 then
       updatesal(datas(i).empno,3600);
     end if;
     end loop;
  end;