黄松平.txt

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

1
declare
v_i int :=1; --控制每行输出空格数
v_j int :=1; --控制每行输出*号数量
row int :=1; --控制行数
i int :=6;   --空格数量
j int :=7-i; --*号数量
 begin
 while row < 8 loop
    for v_i in 1 .. i/2 loop
     dbms_output.put(' ');
    end loop;
    for v_j in 1 .. j loop
     dbms_output.put('*');
    end loop;
  dbms_output.new_line();
  if row<4 then 
   i :=i-2;
   j :=7-i;
  else 
   i :=i+2;
   j:=7-i;
   end if;
  row :=row+1;
  end loop;
 end;
SQL> 
 26  /

   *
  ***
 *****
*******
 *****
  ***
   *
2
declare
v_age emp.age%type;
begin
insert into emp values(5604,'miacl',40,);
savepoint p1;
update emp set age=45 where empno=7788;
select age into v_age from emp where empno=7788;

if v_age>40 then
rollback to p1;
commit;
else
commit;
end if;
end;
3
declare
  v_name emp.ename%type;
  v_emp emp%rowtype;
  type record_type is record(v_empno emp.empno%type,v_name emp.ename%type);
  my_record record_type;
  type table_type is tabel of emp.ename%type index by binary_integer;
  my_table table_type;
begin
 select ename into v_name from emp where empno=7788;
 select * into v_emp from emp where empno=7788;
 select empno,ename into my_record from emp where empno=7788;
 select ename into my_table(-1) from emp where empno=7788;
end;

4
declare
  type table_type is table of emp%rowtype index by binary_integer;
  dates table_type;
  v_i int :=-1;
begin
  select * into dates(-1) from emp where empno=7788;
  ....
  for v_i in -1 .. 4 loop
    dbms_ouput.putline(dates(v_i).empno,dates(v_i).ename,dates(v_i).sex,dates(v_i).age);
  end loop;
end;