Search results
Oracle CASE expression allows you to add if-else logic to SQL statements without having to call a procedure. The CASE expression evaluates a list of conditions and returns one of the multiple possible results. You can use a CASE expression in any statement or clause that accepts a valid expression.
In a simple CASE expression, Oracle Database searches for the first WHEN... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
We can use a CASE statement in WHERE clause as: SELECT employee_no, name, department_no FROM emps WHERE (CASE WHEN :p_dept_no = 50 THEN 0 WHEN :p_dept_no = 70 THEN 0 ELSE -1 END) = 0;
The simple CASE statement evaluates a single expression and compares it to several potential values. The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE.
7 gru 2023 · A case expression returns a single value. In PL/SQL you can write a case statement to run one or more actions. The differences between case expressions and statements are: You complete them with end case (instead of just end) Each then/else clause contains a statement, rather than returning a value
Statement 2 DECLARE A INT :=30; BEGIN IF ( A = 20) THEN dbms_output.put_line('A IS GRATER THEN 20'); ELSIF ( A = 21) THEN dbms_output.put_line('A IS LESS THEN 20'); ELSE dbms_output.put_line('INPUT IS NOT VALID'); END IF; dbms_output.put_line('YOUR NUMBER IS :' || A); END;
Let’s examine the syntax of the simple CASE statement in detail: 1) selector. The selector is an expression that is evaluated once. The result of the selector is used to select one of the several alternatives e.g., selector_value_1 and selector_value_2. 2) WHEN selector_value THEN statements.
