田粟.txt

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

1，从控制台打印下列信息(用签套的循环，外层循环控制行，内层循环控制*号的个数以及出现的样式)
         *
        ***
       *****
      *******
       *****
        ***
         *
    dbms_output.put('*');   ----输出一个字符不换行
    dbms_output.new_line();  ----换行

declare
   r char;
   e char;
   i int;
   j int;
   k int;
begin
   r := ' ';
   e := '*';
   i := 1;
   j := 1;
   k := 1;
   while i < 5 loop
       for j in 1..(4 - i) loop dbms_output.put(r);
       end loop;
       for k in 1..(2*i - 1) loop dbms_output.put(e);
       end loop;
       dbms_output.new_line(); 
   j := 1;
   k := 1;
   i := i + 1;
   end loop;
   i := 1;
   for i in 1..3 loop
       for j in reverse 1..i loop dbms_output.put(r);
       end loop;
       for k in reverse 1..(7 - 2*i) loop dbms_output.put(e);
       end loop;
       dbms_output.new_line();
       j := 1;
       k := 1;
   end loop;
   
end;

2，在程序块里写一个事务分成两个单元（一个存储点）
   存储点之前给emp表插入一行数据
   存储点之后给emp表的某一行修改下age ，然后查询该行的age ，如果age > 40 回滚到存储点提交事务，如果条件不满足，提交整个事务

declare
   kk myemp%rowtype;
begin
   insert into myemp values(7777,'kkkkk','MANAGER',7839,to_date('2007-06-05','YYYY-MM-DD'),1111,null,44);
   savepoint p1;
   update myemp set age = &b where empno = &a;
   select age into kk.sal from myemp where empno = &a;
   if kk.age > 40  
     then rollback to p1;
     commit;
   else commit;
   end if;
end;

3，给%TYPE   %ROWTYPE  RECORD  TABLE四种类型分别举出相应的例子
   

declare
      num myemp.empno%type;                
      kk myemp%rowtype;                 
      type hh_data is record
       (
         hh_name myemp.ename%type,
         hh_job myemp.job%type
        )
         ;pp hh_data;                      
        type my_gg is table of myemp.job%type index by binary_integer;gg my_gg;                                                                                          
begin
    
  select empno into wage from myemp where empname='kkkkk';
  dbms_output.put_line(num);
  select * into kk from myemp where empno = &aaa;
  dbms_output.put_line(kk.ename);
  dbms_output.put_line(kk.job);
  
  select ename,job into pp from emp where empno=&bbb;
  dbms_output.put_line(hh_data.ename);
  dbms_output.put_line(hh_data.job);
  
  select ename into gg(-1) from myemp where empno=&ccc;
  dbms_output.put_line(gg(-1));
   
  end;

4，定义一个table 里面存放的是%rowtype类型，用该类型定义一个变量datas，从emp表取五行记录放到datas里面，通过循环，把五行数据的值输出来