In PL/SQL* Oracle, we can use conditional statement like other programming language, such as CASE and IF statement. How to do it in PL/SQL? Here’s the syntax for IF statement.
IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF;
Just remember, I write ELSIF, it’s on purpose and it’s not mistyping, in this case we write “else if” in PL/SQL as ELSIF. There is an example for IF statement usage in PL/SQL. This statement will ask your age and show output based on your age input.
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
age NUMBER := &age;
BEGIN
IF age < 17 THEN
DBMS_OUTPUT.PUT_LINE('You are teen');
ELSIF age < 56 THEN
DBMS_OUTPUT.PUT_LINE('You are adult');
ELSE
DBMS_OUTPUT.PUT_LINE('You are old');
END;
/
Just a little different than IF, CASE expression selects a result and returns it. The values returned by CASE is used to select one of several alternatives. The CASE syntax is shown below.
CASE selector WHEN axpression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1] END; /
For the example of CASE expression, we can use IF statement example above, just change it to CASE expression. We add text variable to store the return values from CASE expression since CASE return values.
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
age NUMBER := &age;
text VARCHAR2(20);
BEGIN
text:=
CASE
WHEN age < 17 THEN 'You are teen'
WHEN age < 56 THEN 'You are adult'
ELSE 'You are old'
END;
DBMS_OUTPUT.PUT_LINE(text);
END;
/
Example above didn’t use selector after CASE expression, you can use selector as alternatives way. I don’t really use CASE with selector anyway, but I will give you an example with selector below, I hope this can help.
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
grade CHAR(1) := UPPER('&grade');
text VARCHAR2(20);
BEGIN
text :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Bad'
WHEN 'E' THEN 'Poor'
ELSE 'Not valid grade'
END;
DBMS_OUTPUT.PUT_LINE(text);
END;
/
I think it’s enough for Conditional in PL/SQL Oracle tutorial, maybe this can help you little, feel free to give comment to improve my blog.
Popularity: 7% [?]
