-- if.sql

DECLARE
   ptc employee.pay_type_code%TYPE;
   st person.state%TYPE;
   pay_raise NUMBER(9,2);
BEGIN
	SELECT e.pay_type_code, p.state
	  INTO ptc, st
	  FROM person p JOIN employee e USING(id)
	 WHERE id = 7111
	   FOR UPDATE OF e.pay_amount;

	IF ptc IN ('H','S') AND st IN ('CA','WA') THEN
	   pay_raise := 0.15;
	ELSIF ptc IN ('H','S','T') THEN
	   pay_raise := 0.12;
	ELSE 
	   pay_raise := 0.10;
	END IF;

	/* Uncomment this section and comment out the above
	if statement to get the same results.

	CASE
	   WHEN ptc IN ('H','S') AND st IN ('CA','WA') THEN
	      pay_raise := 0.15;
	   WHEN ptc IN ('H','S','T') THEN
	      pay_raise := 0.12;
	   ELSE
	      pay_raise := 0.10;
	END CASE;
	*/

	UPDATE employee
	   SET pay_amount = pay_amount + pay_amount * pay_raise
	 WHERE id = 7111;
END;
/
