PLSQL练习

declare
  tabName varchar(128);
  a       number;
begin
  select table_name into tabName from user_tables where rownum = 1;
  dbms_output.put_line(tabName);
  if ('aa' = 'Aa') then
    a := 5;
  else
    a := 6;
  end if;
  dbms_output.put_line(a);
end;
/*
ICOL$
6
*/

-----------------------------------------------------------------

begin
  dbms_output.put_line(0.95F); --float
  dbms_output.put_line(0.95D); --double
  dbms_output.put_line(0.95);
  dbms_output.put_line(trunc(25.176, 1));
  dbms_output.put_line(trunc(25.176, -1));
  dbms_output.put_line(round(25.176, 1));
  dbms_output.put_line(round(25.176, -1));
  dbms_output.put_line(ceil(25.176));
  dbms_output.put_line(floor(25.176));
end;
/*
9.49999988E-001
9.4999999999999996E-001
.95
25.1
20
25.2
30
26
25
*/

-------------------------------------------------------------------

declare
  -- tabName varchar(128); -- no need
  num number;
begin
  for tabName in (select table_name from user_tables) loop
    dbms_output.put_line(tabName.table_name);
  end loop;
  for num in 1 .. 5 loop
    dbms_output.put_line(num);
  end loop;
end;

----------------------------------------------------------------------

select ascii(' '), initcap('xu weigui'), ltrim('aaaadfdfd', 'a') from dual;

select interval '40' year from dual;

select interval '40-3' year to month from dual;
----------------------------------------------------------------------

declare
  a interval year to month;
begin
  a := numtoyminterval(10.5, 'year');
  dbms_output.put_line(a);
end;

/*
+10-06
*/
----------------------------------------------------
declare
  type mytype is table of varchar2(100) index by binary_integer;
  mytable mytype;

begin
  mytable(1) := 'xuweigui';
  mytable(123) := 'Andy';
  dbms_output.put_line(mytable(1));
  dbms_output.put_line(mytable(123));
end;

--------------------------------------------------------------
declare
  type mytype is table of varchar2(100);
  mytable mytype := mytype('a');
  mytable2 mytype :=mytype(5,4,3,2,1); --Until you initialize it, a nested table or varray is atomically null;

begin
  if mytable is null then
   null;
  elsif mytable.count =0 then
   null;
  end if;
 
  dbms_output.put_line('mytable init');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  mytable.extend(5);
  dbms_output.put_line('mytable.extend(5)');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  mytable(1) := 'xuweigui';
  mytable(6) := 'Andy';
  dbms_output.put_line('mytable assign value');
  for i in mytable.first .. mytable.last loop
   dbms_output.put_line(mytable(i));
  end loop;
 
  dbms_output.put_line('mytable2 init');
  for i in mytable2.first .. mytable2.last loop
&nb