段湘南.txt

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

1，从控制台打印下列信息(用签套的循环，外层循环控制行，内层循环控制*号的个数以及出现的样式)
         *
        ***
       *****
      *******
       *****
        ***
         *
declare
   type my_array is table of varchar2(20) index by binary_integer;
   arr my_array;
   i int;
   j int;
   k int;
begin
  
   arr(0) := ' ';
   arr(1) := '*';
   i := 1;
   j := 1;
   k := 1;
   while i < 5 loop
       for j in 1..(4 - i) loop dbms_output.put(arr(0));
       end loop;
       for k in 1..(2*i - 1) loop dbms_output.put(arr(1));
       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(arr(0));
       end loop;
       for k in reverse 1..(7 - 2*i) loop dbms_output.put(arr(1));
       end loop;
       dbms_output.new_line();
       j := 1;
       k := 1;
   end loop;
   
end;

2，在程序块里写一个事务分成两个单元（一个存储点）
   存储点之前给emp表插入一行数据
   存储点之后给emp表的某一行修改下sal ，然后查询该行的sal ，如果sal > 3000 回滚到存储点提交事务，如果条件不满足，提交整个事务
declare
   my_data emp%rowtype;
begin
   insert into emp values(1003,'Jordan','MANAGER',7839,to_date('2007/06/05','YYYY/MM/DD'),4000,null,20);
   savepoint p1;
   update emp set sal = &b where empno = &a;
   select sal into my_data.sal from emp where empno = &a;
   if my_data.sal > 3000  
     then rollback to p1;
     commit;
   else commit;
   end if;
end;

3，给%TYPE   %ROWTYPE  RECORD  TABLE四种类型分别举出相应的例子
1)
declare
   ename_01 emp.ename%type;
   salary emp.sal%type;
begin
   select ename,sal into ename_01,salary from emp where empno = &a;
   dbms_output.put_line('员工姓名： '||ename_01||' '||'员工工资： '||salary);
end;
2)
declare
   my_data student_010%rowtype;
begin
   select * into my_data from student_010 where id = &a;
   dbms_output.put_line('学生姓名： '||my_data.name||' '||'年龄： '||my_data.age);
end;
3)
declare
   type stu_record_type is record(
              stu_name student_010.name%type,
              stu_age student_010.age%type,
              stu_score student_010.score%type);
   stu_record stu_record_type;
     
begin
   select name,age,score into stu_record from student_010 where id = &a;
   dbms_output.put_line('学生姓名： '||stu_record.stu_name||' '||'年   龄：'||stu_record.stu_age||' '||'分数：'||stu_record.stu_score);
end;
4)
declare
   type stu_name_type is table of student_010.name%type index by binary_integer;
   stu_name stu_name_type; 
begin
   select name into stu_name(-1) from student_010 where id = &a;
   stu_name(0) := 'Mouse';
   stu_name(1) := 'Cat';
   dbms_output.put_line(stu_name(-1));
   dbms_output.put_line(stu_name(0));
   dbms_output.put_line(stu_name(1));
end;

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

declare
    type datas_array is table of student_010%rowtype index by binary_integer;
    datas datas_array;
    j int;
begin
    j := 0;
    while j < 5 loop
      select * into datas(j0) from student_010 where id = &a;
      j := j + 1;
    end loop;
    for  i in 1..5  loop
      dbms_output.put(datas(i).id||' ');
      dbms_output.put(datas(i).name||' ');
      dbms_output.put(datas(i).sex||' ');
      dbms_output.put(datas(i).age||' ');
      dbms_output.put(datas(i).coid||' ');
      dbms_output.put(datas(i).score||' ');
      dbms_output.new_line();
    end loop;
end;