陈平新.txt

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

1，从控制台打印下列信息(用签套的循环，外层循环控制行，内层循环控制*号的个数以及出现的样式)
         *
        ***
       *****
      *******
       *****
        ***
         *
  
  declare
  i int;
  --k int;
  --j int;
  
begin 
  i := 0;
  
  <<hang>>
  for i in 1..4 loop                   --上四行
       
      for k in reverse 0..(4-i) loop   --打印空格
        dbms_output.put(' ');        
      end loop;
      
      for j in 1..(2*i-1) loop         --打印*号
        dbms_output.put('*');  
      end loop;   
      dbms_output.new_line();
  end loop hang;
  
  for i in 1..3 loop                   --下三行
       
      for k in 0..i loop
        dbms_output.put(' ');        
      end loop;
      
      for j in reverse 1..(7-2*i) loop
        dbms_output.put('*');  
      end loop;   
      dbms_output.new_line();
  end loop;
end;  

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

declare
  name  emp.ename%type ;
  id    emp.empno%type ;
begin 
  insert into emp  (empno,name,sal)values(7758,'love',2300);
  savepoint pl;
 
end;

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

1  declare 
       name emp.ename%type;
       V_job  emp.job%type;
   begin 
    select ename, job into name,V_job from emp where empno=&num;
    dbms_output.put_line('yuan gong ming is'|| name);
    dbms_output.put_line('yuan gong work part Is'|| V_job);
   end;

2   declare 
     new_emp student%rowtype;
   begin 
        select * into new_emp from student where ID=&nn;
        dbms_output.put_line ('新名字: '||new_emp.name);
        dbms_output.put_line ('new age '|| new_emp.age);
        dbms_output.put_line ('new age '|| new_emp.lid);     
   end;

3   declare 
     type hh is record( r_name  student.name%type,
                        r_age   student.age%type,
                        r_lid   student.lid%type);
          dd  hh;
   begin 
        select name,age,lid into dd 
        from student where id=&NN;
        
        dbms_output.put_line ('新名字: '||dd.r_name);
        dbms_output.put_line ('new age '||dd.r_age);
        dbms_output.put_line ('new lid '||dd.r_lid); 
  end;

4  declare 
   type bb is table of emp.sal%type
   index by binary_integer;
   aa  bb;
begin
  select sal into aa(0) from emp where
     empno=&ll;
     aa(-10) :=7902;
     aa(2) :=2500;
     dbms_output.put_line('emp sal'||aa(0));
     dbms_output.put_line('emp sal'||aa(-10));
     dbms_output.put_line('emp sal'||aa(2));
end;

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


declare
  type yy  is table of emp%rowtype index by binary_integer;
  aa yy;
begin
  for i in 1..5 loop
    select * into aa(i) from emp where id=i;
   
   dbms_output.put(aa(i).empno); 
    dbms_output.put(aa(i).ename); 
    dbms_output.put(aa(i).job); 
    dbms_output.put(aa(i).HIREDATE); 
    dbms_output.put(aa(i).sal); 
    dbms_output.put(aa(i).age); 
    dbms_output.put(aa(i).MGR);
     dbms_output.put(aa(i).COMM);
     dbms_output.put(aa(i).DEPTNO);
    dbms_output.new_line();
  end loop;  
end;