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;