PLS-1: Introduction to Oracle PL/SQL ended@T8

The PL/SQL and sql share the same variable type;

Script could run across the different OS platform

APEX libarary provided by oracle

 

 

There is a constant keyword for pl/sql like c_tax=constant number(3,2)=6.78

The reference varible is like emp.sal%TYPE

The composite varible is TYPE rec is RECORD (a number, b varchar2(10)); v_rec rec; v_rec.a :=10;

The rowtype could be deinfed as emp_rec emp%RowType and emp_rec.sal:=90

The host variable is like: variable g_val number and it could be accessed by pl/sql block like :g_val

 

Operator precedentce:

** + - * % || not and or (is null) (btween and) (y like '%Del'') ( z in ('black','white'))

 

Most functions available for sql is also ok for PL/Sql statement but not all

greatest and least, upper, add_months, to-data,to_char  are ok but decode, max, group... are not ok

x := round(last-day(sysdate()-trunc(sysdate, 'mon'));

y:=  extract(year from sysdate)>2000

m:= to_date('12-jan-2090',DD-MON-YEAR')

z:=  to_number(to_cahr(age, 'yy'))

Conditional/Selection Control

--Whenever you compare null to anything, it will be false;

if .. then

else

end if;

 

if then

.....

else

if then 

else

end if

end if;

 

if then

'''

elseif

...

else

...

end if;

 

case when ...then; when... then;

else

end case;

 

Iteration Control Structure

loop

(<exit when x>3 is optional or directly exit or continue or <continue when x<3>)

end loop

 

we also could have nested loop:

<<outer_loop>>

LOOP

  <<inner_loop>>

  LOOP

  exit inner_loop when()

  exit outer_loop when()

end loop inner_loop;

end loop outer_loop

 

while ...  loop

...

end loop;

 

//you can use the i, e.g. to_char(i) but it is not allowed to change it, lie i:=2

for i in 1..10 loop

...

end loop;

 

// this is a reverse loop 3,2,1

select value into v from vt;

for i in reverse 1..6(or 0..trunc(high/low)*2 or varible of <v>) loop

...

(NULL which means doing nothing like NOP in the assemble language)

end loop;

 

 

declare

type datelist is table or data index by PLS_INTEGER;

dates DataList

begin

for j in 1..3 loop

dates(j*5) := SYSDATE;

end loop;

end;

/

 

 

 

 

dbms_output.enable()有什么用

设置 dbms_output 输出的缓冲。 不设置如果输出超过2000字节就不可以用了……
oracle 中 set serveroutput on 是什么意思
即打开oracle自带的输出方法dbms_output。在执行set serveroutput on以后，使用dbms_output方法可以输出信息，例如：dbms_output.put_line('This is');