607.txt

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

declare
  user_name varchar2(30);
  user_salary emp.sal%type;
  user_data  emp%rowtype;
  type my_data is record(my_name emp.ename%type,
                         my_salary emp.sal%type);
  nsdata  my_data;
  
  ite int;
  oute int;
  forint int;
  
  type my_array is table of emp.ename%type index by binary_integer;
  
  arrs  my_array;
  
begin
     select sal into user_salary from emp where empno = &s;
     
     /*
     if user_salary > 4000 then
        dbms_output.put_line('gongzi da yu 1000');
     elsif user_salary = 3000 then
        dbms_output.put_line('deng yu 3000');
     elsif  (user_salary > 2000 and user_salary < 3000 )then
        dbms_output.put_line('deng yu 3000');
     else
        dbms_output.put_line('gongzi xiao yu deng yu 1000');
     end if;
     
     case
       when user_salary > 4000 then
            dbms_output.put_line('gongzi da yu 1000');
       when  user_salary = 3000 then
            dbms_output.put_line('deng yu 3000');
       when (user_salary > 2000 and user_salary < 3000 )then
            dbms_output.put_line('deng yu 3000');
       else
            dbms_output.put_line('gongzi xiao yu deng yu 1000');
     
     end case;*/
  
  
  user_name := 'helloworld';
  ite := -1;
  oute := 0;
  
  update emp set ename = 'abcde' where empno = 5678;
  savepoint p1;
  insert into emp(empno,ename) values(1239,'sss');
  commit;
  
  exception 
     when Dup_val_on_index then
       rollback to p1;
       commit;
       dbms_output.put_line('zhujian chongfu');

  /*
  <<mytag>>
     oute := oute + 1;
     ite := ite + oute;
     if oute < 10 then 
       goto mytag;
     else
       null;
     end if;    
   dbms_output.put_line(ite); */ 
  
  
  --select sal into user_salary from emp where empno = &s;
  --select * into user_data  from emp where empno = &aaa;
  /*
  <<outer1>>
  while oute < 5 loop
       select ename into arrs(-1)  from emp where empno = &empno;
       arrs(0) := 'aaa'||oute;
       arrs(1) := 'bbb'||oute;
       
       <<for1>>
       loop
          dbms_output.put_line(arrs(ite));
          ite := ite + 1;
             for forint in 1..3 loop
                dbms_output.put_line(forint);
             end loop;
          exit for1 when ite > 0;
       end loop for1;
       
       ite := -1;
    oute := oute + 1;
  end loop outer1;*/
  /*
  select ename into arrs(-1)  from emp where empno = &aaa;
  arrs(0) := 'aaa';
  arrs(1) := 'bbb';*/
  /*
  <<myloop>>
  loop
    dbms_output.put_line(arrs(ite));
    ite := ite + 1;
    
    exit myloop when ite > 1;
  end loop;*/
  
  /*
  <<whileloop>>
  while ite < 2 loop
        dbms_output.put_line(arrs(ite));
        ite := ite + 1;
  end loop whileloop;
  */
  
  /*
  <<forloop>>
  for ite in reverse -1..1 loop
    dbms_output.put_line(arrs(ite));
  end loop forloop;*/
        
  
  
  /*
     <<b_1>>
    declare
       user_age int := 23;
       user_name varchar2(10) := 'world1';
    begin
       dbms_output.put_line('user de age is'||user_age||' name is'||user_name);
         <<b_2>>
            declare
              -- user_age int := 23;
               user_name varchar2(10) := 'world2';
            begin
               dbms_output.put_line(' name is'||b_1.user_name);
            end ;
    end;*/
    
  --dbms_output.put_line(user_data.ename ||'de salary is '||user_data.sal);
  --dbms_output.put_line(nsdata.my_name ||'de salary is '||nsdata.my_salary);
  --dbms_output.put_line(arrs(-1));
end ;