pl/sql笔记

if nvl(salary,0) <= 4000
then
   give_bonus(employee_id, 0);
else
    give_bonus(employee_id, 500);
end if;

----

order_exceeds_balance := :customer.order_total> max_allowable_order;

if order_exceeds_balance  then ...

----

if condition1 and condition2
then
...
else
...
end if;


if condition1
then
   if condition2
   then
     ...
   end if;
end if;

----

case expression
when  result1 then
  statements1
when  result2 then
  statements2
...
else
  statements_else
end case;

----

case
when expression1 then
  statements1
when expression2 then
  statements2
...
else
  statements_else
end case;

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

BEGIN
    INSERT INTO occupancy_history (pet_id,NAME,checkout_date)
       SELECT pet_id,NAME,checkout_date
           FROM occupancy WHERE checkout_date IS NOT NULL;
    DELETE FROM occupancy WHERE checkout_date IS NOT NULL;
END;  

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

MOD(n,m)
返回n除以m后的余数。如果n和m都是正数或者负数，计算余数所用的公式是n-(m*FLOOR(n/m)),如果n和m的正负号不同，公式是n-(m*CEIL(n/m))。如果m等于0，则返回n。
   FUNCTION is_odd (num_in IN NUMBER) RETURN BOOLEAN
IS
BEGIN
RETURN MOD(num_in,2) = 1;
END;

  FUNCTION is_even (num_in, IN NUMBER)  RETURN BOOLEAN
IS
BEGIN
RETURN MOD(num_in,2) = 0;
END;

------------
declare
service_interval  interval year to month ;
years_of_service number;
months_of_service number;
begin
service_interval := (end_date - start_date) year to month;
years_of_service := extract(year from service_interval);
months_of_service := extract(month from service_interval);
end;