Searched Case Statement

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  v_user_date DATE := TO_DATE('&user_date','DD-MON-YYYY');
BEGIN
  CASE
    WHEN TO_CHAR(v_user_date,'D') = '1' THEN
      dbms_output.put_line('Today is Monday');
    WHEN TO_CHAR(v_user_date,'D') = '2' THEN
      dbms_output.put_line('Today is Tuesday');
    WHEN TO_CHAR(v_user_date,'D') = '3' THEN
      dbms_output.put_line('Today is Wednesday');
    WHEN TO_CHAR(v_user_date,'D') = '4' THEN
      dbms_output.put_line('Today is Thursday');
    WHEN TO_CHAR(v_user_date,'D') = '5' THEN
      dbms_output.put_line('Today is Friday');
    WHEN TO_CHAR(v_user_date,'D') = '6' THEN
      dbms_output.put_line('Today is Saturday');
    WHEN TO_CHAR(v_user_date,'D') = '7' THEN
      dbms_output.put_line('Today is Sunday');
  END CASE;
END;
/
Enter value for user_date: 01-JAN-2012
Today is Sunday
PL/SQL procedure successfully completed.

CASE Statement

--Case Statement
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  v_user_date DATE := TO_DATE('&user_date','DD-MON-YYYY');
BEGIN
  CASE TO_CHAR(v_user_date,'D')
    WHEN '1' THEN
      dbms_output.put_line('Today is Monday');
    WHEN '2' THEN
      dbms_output.put_line('Today is Tuesday');
    WHEN '3' THEN
      dbms_output.put_line('Today is Wednesday');
    WHEN '4' THEN
      dbms_output.put_line('Today is Thursday');
    WHEN '5' THEN
      dbms_output.put_line('Today is Friday');
    WHEN '6' THEN
      dbms_output.put_line('Today is Saturday');
    ELSE
      dbms_output.put_line('Today is Sunday');
  END CASE;
END;
/
Enter value for user_date: 01-JAN-2011
Today is Saturday
PL/SQL procedure successfully completed.

How would you display time of the day

SET SERVEROUTPUT ON
BEGIN
       DBMS_OUTPUT.PUT_LINE('Today is '|| TO_CHAR( sysdate, 'Day, HH24:MI'));
END;
/
Today is Monday   , 10:21
PL/SQL procedure successfully completed.

IF-THEN-ELSIF-ELSE-END IF statement with Logical Operators

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_char CHAR(1) := '&user_input';
BEGIN
    IF ( v_char >= 'A'  AND v_char <= 'Z') OR  ( v_char >= 'a'  AND v_char <= 'z')  THEN
        DBMS_OUTPUT.PUT_LINE('User input is a letter');
    ELSIF ( v_char >= '0' and v_char <= '9' ) THEN
        DBMS_OUTPUT.PUT_LINE('User input is a number');
    ELSE
         DBMS_OUTPUT.PUT_LINE('User input is a special Character');
   END IF;
END;
/
Enter value for user_input: A
User input is a letter
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input: 3
User input is a number
PL/SQL procedure successfully completed.
SQL> /
Enter value for user_input: $
User input is a special Character
PL/SQL procedure successfully completed.

NESTED IF Statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
 v_num1 NUMBER := &num1;
 v_num2 NUMBER := &num2;
v_total NUMBER;
BEGIN
 IF v_num1 > v_num2 THEN
  DBMS_OUTPUT.PUT_LINE ('outer IF ');
  v_total := v_num1 - v_num2;
 ELSE
  DBMS_OUTPUT.PUT_LINE ('outer ELSE');
  v_total := v_num1 + v_num2;
  IF v_total < 0 THEN
   DBMS_OUTPUT.PUT_LINE ('Inner IF');
   v_total := v_total * (-1);
  END IF; 
 END IF;
 DBMS_OUTPUT.PUT_LINE ('v_total =  '||v_total);
END;
/

IF-THEN-ELSIF-ELSE-END IF statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_empno NUMBER := &emp_number;
    v_sal        NUMBER;
BEGIN
    SELECT sal INTO  v_sal FROM emp
        WHERE empno = v_empno;
    IF v_sal BETWEEN 0 AND 1000 THEN
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting LOW salary' );
    ELSIF  v_sal BETWEEN 1001 AND 2000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting NORMAL salary' );
    ELSIF  v_sal BETWEEN 2001 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting HIGH salary' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  salary is more  than 3000 INR' );
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(' Emp No  '|| v_empno || ' does not exist' );
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error  '|| sqlcode ||' - '||sqlerrm);
END;
/
Enter value for emp_number: 7566
Emp No  7566  is getting HIGH salary
PL/SQL procedure successfully completed.

IF-THEN-ELSIF-ELSE-END IF statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
 v_user_input NUMBER := &user_input_number;
BEGIN
 IF v_user_input < 0 THEN
  DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is a negative number' );
 ELSIF  v_user_input = 0 THEN
  DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is equal to Zero' );
 ELSE
 DBMS_OUTPUT.PUT_LINE ( v_user_input || ' is a positive number' );
 END IF;
END;
/
Enter value for user_input_number: -24
-24 is a negative number
PL/SQL procedure successfully completed.

IF-THEN-ELSE-END IF Statement

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
 v_user_input_date  DATE  :=  TO_DATE('&user_input_date', 'DD-MON-YYYY');
 v_day VARCHAR2(10);
BEGIN
 v_day :=  TO_CHAR(v_user_input_date,'DAY');
 IF  v_day like 'S%'  THEN
  DBMS_OUTPUT.PUT_LINE( v_user_input_date || ' falls on weekend' );
 ELSE
  DBMS_OUTPUT.PUT_LINE( v_user_input_date || ' does not fall on weekend');
 END IF;
END;
/
Enter value for user_input_date: 18-DEC-2010
18-DEC-10 falls on weekend
PL/SQL procedure successfully completed.

IF-THEN-ELSE-END IF Statement

/*  Test user provided numbe is even or odd */
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
 v_user_input  NUMBER  := &user_input;
BEGIN
 -- Test user provided number is even
 IF  MOD(v_user_input, 2)   = 0 THEN
  DBMS_OUTPUT.PUT_LINE( v_user_input || ' is  a even number');
 ELSE
  DBMS_OUTPUT.PUT_LINE( v_user_input || ' is  a odd number');
 END IF;
END;
/
Enter value for user_input: 23
23 is  a odd number
PL/SQL procedure successfully completed.

IF-THEN-END IF Statement

SET SERVEROUTPUT ON
DECLARE
 v_num1  NUMBER  := 40;
 v_num2  NUMBER  := 30;
 v_temp   NUMBER;
BEGIN
 -- If num1 is greater than num2 rearrange their values
 IF  v_num1 > v_num2 THEN
  v_temp := v_num1;
  v_num1 := v_num2;
  v_num2 := v_temp;
 END IF;
 -- Display rearranged values of v_num1 and v_num2
 DBMS_OUTPUT.PUT_LINE( 'v_num1 = '|| v_num1);
 DBMS_OUTPUT.PUT_LINE( 'v_num2 = '|| v_num2);
END;
/
v_num1 = 30
v_num2 = 40
PL/SQL procedure successfully completed.

Accept User Input (substitute variable) in Anonymous PL/SQL Block

Oracle PL/SQL block accepts user input information with the help of substitution variable. Substitute variable can not be used to output values, because memory is not allocated to substitute variable. Substitute variable is prefixed with ampersand(&) or double ampersand (&&) character. Below example is using Oracle provided HR schema to demonstrate the use of substitute variable

SET SERVEROUTPUT ON
DECLARE
  v_employee_id  NUMBER := &s_employee_id;
  v_first_name  VARCHAR2(20);
  v_last_name  VARCHAR2(25);
BEGIN
  SELECT first_name, last_name
   INTO v_first_name, v_last_name
  FROM hr.employees
  WHERE employee_id = v_employee_id;
 
  DBMS_OUTPUT.PUT_LINE ( ' Employee Name is '|| v_first_name ||'  '|| v_last_name );

EXCEPTION
  WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Supplied employee id does not exists');
END;
/
Enter value for s_employee_id: 123
old   2:  v_employee_id  NUMBER := &s_employee_id;
new   2:  v_employee_id  NUMBER := 123;
Employee Name is Shanta  Vollman

PL/SQL procedure successfully completed