PL/SQL Control structures

IF

IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF.

Example:

DECLARE 
	x NUMBER:=5; 
	y NUMBER:=3;
	z NUMBER:=0;
BEGIN 
	IF x > y THEN 
		z:=x; DBMS_OUTPUT.PUT_LINE(z);
	ELSIF x < y THEN 
		z:=y; DBMS_OUTPUT.PUT_LINE(z);
	ELSE 
		z:=-1; DBMS_OUTPUT.PUT_LINE(z);
	END IF; 
END;

Example:

DECLARE 
	v_dept_id NUMBER; 
	v_id EMPLOYEES.id%TYPE:=3; 
	v_sal_raise NUMBER:=0; 
BEGIN 
	SELECT dept_id INTO v_dept_id from employees WHERE id = v_id; 
	IF v_dept_id = 40 THEN 
		v_sal_raise:=2; DBMS_OUTPUT.PUT_LINE(v_sal_raise);
	ELSIF v_dept_id = 10 THEN 
		v_sal_raise:=3; DBMS_OUTPUT.PUT_LINE(v_sal_raise);
	ELSE 
		v_sal_raise:=0; DBMS_OUTPUT.PUT_LINE(v_sal_raise);
	END IF; 
EXCEPTION
WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(v_sal_raise);
END;

CASE

Example:

DECLARE
	test varchar2(10);
BEGIN
	test := 'B';
	CASE
		WHEN test = 'A' THEN DBMS_OUTPUT.PUT_LINE ('Test A');
		WHEN test = 'B' THEN DBMS_OUTPUT.PUT_LINE ('Test B');
		WHEN test = 'B' THEN DBMS_OUTPUT.PUT_LINE ('Test C');
	ELSE 
		DBMS_OUTPUT.PUT_LINE ('No such test!');
	END CASE;
END;

LOOP

There are 3 forms for LOOP: LOOP, WHILE-LOOP, FOR-LOOP

Example

DECLARE 
	x NUMBER:=0; 
BEGIN 
	LOOP 
		x:= x + 1; 
		IF  x = 3 THEN 
			EXIT; 
		END IF; 
	END LOOP; 
	IF x < 3 THEN 
		RETURN; 
	END IF; 
	DBMS_OUTPUT.PUT_LINE (' x= ' || TO_CHAR (x)); 
END;