What You Ought To Know About Case In Oracle Plsql
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View What You Ought To Know About Case In Oracle Plsql as PDF for free.
More details
- Words: 1,381
- Pages: 7
HOME
ARCHIVES
SITEMAP
ORACLE STUFF
ORANA PLUGINS USED EMAIL VIEW RANDOM POST
ABOUT ME
CONTACT ME
SUBSCRIBE BY
News, views, tips and tricks on Oracle, ColdFusion and other fun stuff Ads by Google
Oracle Books
Oracle Certification
PL SQL
ORACLE9I PLSQL
« GET ALL THE COOL NEW FEATURES OF ORACLE APPLICATION EXPRESS 3.0 IN ORACLE DATABASE XE
What you Ought to Know About CASE in Oracle PL/SQL
Oracle PL/SQL evangelist Steven Feuerstein’s latest PL/SQL puzzler The Mysteries of CASE in PL/SQL is interesting. It touc one “less-than-obvious aspect of PL/SQL’s CASE”. So, what is this “mysterious” thing about CASE? Let’s first do a quick review of CASE in PL/SQL. In general, you have two types, CASE statements and CASE expressions. further categorize each as simple or searched: Simple CASE statements:
CASE expression WHEN result THEN statement; statement; ... WHEN result THEN statement; statement; ... ... ELSE statement; statement; ... END CASE; For example:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
DECLARE grade CHAR (1); BEGIN grade := 'A'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.put_line DBMS_OUTPUT.put_line WHEN 'B' THEN DBMS_OUTPUT.put_line WHEN 'C' THEN DBMS_OUTPUT.put_line ELSE DBMS_OUTPUT.put_line END CASE; END;
('Excellent'); ('You are smart'); ('Very Good'); ('Good'); ('Go home');
21 / Excellent You are smart Searched CASE statements:
CASE WHEN boolean_expression THEN statement; statement; ... WHEN boolean_expression THEN statement; statement; ... ... ELSE statement; statement; ... END CASE; For example:
SQL> DECLARE 2 grade CHAR (1); 3 BEGIN 4 grade := 'X'; 5 6 CASE 7 WHEN grade = 'A' 8 THEN 9 DBMS_OUTPUT.put_line ('Excellent'); 10 WHEN grade = 'B' 11 THEN 12 DBMS_OUTPUT.put_line ('Very Good'); 13 END CASE; 14 EXCEPTION 15 WHEN case_not_found 16 THEN 17 DBMS_OUTPUT.put_line ('No such grade'); 18 END; 19 / No such grade Top 3 things to notice about CASE statements: 1. The CASE statement executes one or many statements based on a certain condition. 2. If no ELSE is specified, and no expressions evaluate to TRUE, then the exception CASE_NOT_FOUND is raised. 3. WHEN clauses are evaluated in order, from top to bottom.
Simple CASE expressions:
case_result := CASE expression WHEN result THEN result_expression; WHEN result THEN result_expression; ...
ELSE result_expression; END; For example:
SQL> DECLARE 2 grade CHAR (1) := 'B'; 3 appraisal VARCHAR2 (20); 4 BEGIN 5 appraisal := 6 CASE grade 7 WHEN 'A' 8 THEN 'Excellent' 9 WHEN 'B' 10 THEN 'Very Good' 11 ELSE 'No such grade' 12 END; 13 DBMS_OUTPUT.put_line ('Grade ' || grade || ' is ' || appraisal); 14 END; 15 / Grade B is Very Good Searched CASE expressions:
case_result := CASE WHEN boolean_expression THEN result_expression; WHEN boolean_expression THEN result_expression; ... ELSE result_expression; END; For example:
SQL> DECLARE 2 grade CHAR (1) := 'Z'; 3 appraisal VARCHAR2 (20); 4 BEGIN 5 appraisal := 6 CASE 7 WHEN grade = 'A' 8 THEN 'Excellent' 9 WHEN grade = 'B' 10 THEN 'Very Good' 11 END; 12 DBMS_OUTPUT.put_line 13 ('Grade ' || grade || ' is ' || NVL(appraisal, 'not graded')); 14 END; 15 / Grade Z is Not graded Top 3 things to notice about CASE expressions: 1. The CASE expression returns a value based on a certain condition. 2. The CASE expression is terminated by END (not END CASE). 3. When no WHEN conditions are met, no error is raised, the CASE expression will simply return NULL.
Now back to Steven’s puzzler:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN -- Searched CASE expression RETURN CASE WHEN letter_in = 'A' THEN 'Apple' WHEN letter_in = 'B' THEN 'Banana' END; END fruit_translator; /
Function created.
When you execute the above function, passing a value that is not ‘A’ and not ‘B’, the CASE expression should return NULL
SQL> BEGIN 2 DBMS_OUTPUT.put_line ( 'Expression good for you = ' 3 || NVL (fruit_translator ('C'), 'Unknown Fruit') 4 ); 5 END; 6 / Expression good for you = Unknown Fruit As expected. The following function contains a CASE statement:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN -- Searched CASE statement CASE WHEN letter_in = 'A' THEN retval := 'Apple'; WHEN letter_in = 'B' THEN retval := 'Banana'; END CASE; RETURN retval; END fruit_translator; /
Function created.
When you execute the above function passing a value that is not ‘A’ and not ‘B’, and because there is no ELSE specified, t statement should raise an (unhandled) exception:
SQL> BEGIN 2 DBMS_OUTPUT.put_line ( 'Statement good for you = ' 3 || NVL (fruit_translator ('C'), 'Unknown Fruit') 4 ); 5 END;
6 / BEGIN * ERROR at line 1: ORA-06592: CASE not found while executing CASE statement ORA-06512: at "EDDIE.FRUIT_TRANSLATOR", line 7 ORA-06512: at line 2 As expected.
One more thing to note about CASE. In Oracle 8i, CASE statements and expressions are supported only in SQL and not in In 8i you cannot use CASE in PL/SQL. Starting with Oracle database release 9.0.1, the SQL and PL/SQL parsers were integ and, as a result, in version 9.0.1 and above CASE works in both SQL and PL/SQL. Thank you Steven for this nice puzzler. It’s easy to solve (without executing the code between CASE statements and CASE expressions.
) as long as you know the differe
Sources and resources:
CASE gotcha in Oracle 8i
Improving SQL efficiency using CASE
CASE Statements
CASE Expressions
Simple CASE expression
Searched CASE Expression
Using CASE Statements
Share This Subscribe to this feed • Save to del.icio.us Post a Comment | Trackbacks Closed | 124 Views
Home > About This Post
Post a Comment
This entry was posted by Eddie Awad on Wednesday, July 25th, 2007, at 5:00 am, and was filed in pl/sql, Oracle.
Your email is never published nor shared. Required fie marked *
Subscribe to the
RSS 2.0 feed for all comments to this post.
Name:
Email: Search This Blog Website/Blog:
Find Spam protection: Sum of four + seven ? *
Recent Posts What you Ought to Know About CASE in Oracle PL/SQL
Comment:
Get All the Cool New Features of Oracle Application Express 3.0 in Oracle Database XE New Oracle ACE and Website Rousing Entertainment With the Guru of Oracle PL/SQL Oracle Database 11g Old Feature: Internet Addressing 8 Links from Around the Web (2007-07-13)
Post c Notify me of followup comments via e-mail d e f g
Oracle Blogs Ranked by Technorati Authority - July 07 Edition 5 Links from Around the Web (2007-07-07) Running on a Virtual Private Server and a Couple of Pictures Here is A Different And Handy Way to Search in Your Browser 16 Links to Help You Learn And Use Regular Expressions Oracle Database 11g Overview Presentation Related Posts CASE gotcha in Oracle 8i CASE used in CREATE INDEX Oracle PLSQL in CFQUERY “Between” CASE and DECODE links for 2006-12-08 Most Viewed Posts HTTP POST from inside Oracle - 10,587 Views 10 Things About Computer Programming You May Not Agree With - 7,333 Views Format your SQL the easy way - 6,735 Views Ubuntu on Microsoft Virtual PC Works Like a Charm - 6,309 Views Two Oracle PL/SQL Features You Probably Don’t Know About - 4,204 Views Oracle SQLPlus - 4,132 Views Back to basics: outer joins - 3,391 Views Saving/Downloading files to/from Oracle using ColdFusion 3,379 Views Comments Do Make a Difference - 3,201 Views Back to basics: inner joins - 3,119 Views Eddie Awad Randomized What you Ought to Know About CASE in Oracle PL/SQL Get All the Cool New Features of Oracle Application Express 3.0 in Oracle Database XE ©
Eddie Awad: You know that Facebook is the next big thing when your aunt in Canada, your brother in...
joel garry (6)
2007 Eddie Awad
Simplr theme by Scott
Powered by WordPress
Slideshare Adds Audio Synchronization Here’s how to mess with people talkin’ too much about Facebook Oracle refutes 'SSH hacking' slur - Register Bugtraq: Oracle bad Views - Exploit released 70+ Tools For Job Hunting 2.0
Sponsor: Chicago Flower D
Valid XHTML & CSS
RSS: Posts & Comm
ARCHIVES
SITEMAP
ORACLE STUFF
ORANA PLUGINS USED EMAIL VIEW RANDOM POST
ABOUT ME
CONTACT ME
SUBSCRIBE BY
News, views, tips and tricks on Oracle, ColdFusion and other fun stuff Ads by Google
Oracle Books
Oracle Certification
PL SQL
ORACLE9I PLSQL
« GET ALL THE COOL NEW FEATURES OF ORACLE APPLICATION EXPRESS 3.0 IN ORACLE DATABASE XE
What you Ought to Know About CASE in Oracle PL/SQL
Oracle PL/SQL evangelist Steven Feuerstein’s latest PL/SQL puzzler The Mysteries of CASE in PL/SQL is interesting. It touc one “less-than-obvious aspect of PL/SQL’s CASE”. So, what is this “mysterious” thing about CASE? Let’s first do a quick review of CASE in PL/SQL. In general, you have two types, CASE statements and CASE expressions. further categorize each as simple or searched: Simple CASE statements:
CASE expression WHEN result THEN statement; statement; ... WHEN result THEN statement; statement; ... ... ELSE statement; statement; ... END CASE; For example:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
DECLARE grade CHAR (1); BEGIN grade := 'A'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.put_line DBMS_OUTPUT.put_line WHEN 'B' THEN DBMS_OUTPUT.put_line WHEN 'C' THEN DBMS_OUTPUT.put_line ELSE DBMS_OUTPUT.put_line END CASE; END;
('Excellent'); ('You are smart'); ('Very Good'); ('Good'); ('Go home');
21 / Excellent You are smart Searched CASE statements:
CASE WHEN boolean_expression THEN statement; statement; ... WHEN boolean_expression THEN statement; statement; ... ... ELSE statement; statement; ... END CASE; For example:
SQL> DECLARE 2 grade CHAR (1); 3 BEGIN 4 grade := 'X'; 5 6 CASE 7 WHEN grade = 'A' 8 THEN 9 DBMS_OUTPUT.put_line ('Excellent'); 10 WHEN grade = 'B' 11 THEN 12 DBMS_OUTPUT.put_line ('Very Good'); 13 END CASE; 14 EXCEPTION 15 WHEN case_not_found 16 THEN 17 DBMS_OUTPUT.put_line ('No such grade'); 18 END; 19 / No such grade Top 3 things to notice about CASE statements: 1. The CASE statement executes one or many statements based on a certain condition. 2. If no ELSE is specified, and no expressions evaluate to TRUE, then the exception CASE_NOT_FOUND is raised. 3. WHEN clauses are evaluated in order, from top to bottom.
Simple CASE expressions:
case_result := CASE expression WHEN result THEN result_expression; WHEN result THEN result_expression; ...
ELSE result_expression; END; For example:
SQL> DECLARE 2 grade CHAR (1) := 'B'; 3 appraisal VARCHAR2 (20); 4 BEGIN 5 appraisal := 6 CASE grade 7 WHEN 'A' 8 THEN 'Excellent' 9 WHEN 'B' 10 THEN 'Very Good' 11 ELSE 'No such grade' 12 END; 13 DBMS_OUTPUT.put_line ('Grade ' || grade || ' is ' || appraisal); 14 END; 15 / Grade B is Very Good Searched CASE expressions:
case_result := CASE WHEN boolean_expression THEN result_expression; WHEN boolean_expression THEN result_expression; ... ELSE result_expression; END; For example:
SQL> DECLARE 2 grade CHAR (1) := 'Z'; 3 appraisal VARCHAR2 (20); 4 BEGIN 5 appraisal := 6 CASE 7 WHEN grade = 'A' 8 THEN 'Excellent' 9 WHEN grade = 'B' 10 THEN 'Very Good' 11 END; 12 DBMS_OUTPUT.put_line 13 ('Grade ' || grade || ' is ' || NVL(appraisal, 'not graded')); 14 END; 15 / Grade Z is Not graded Top 3 things to notice about CASE expressions: 1. The CASE expression returns a value based on a certain condition. 2. The CASE expression is terminated by END (not END CASE). 3. When no WHEN conditions are met, no error is raised, the CASE expression will simply return NULL.
Now back to Steven’s puzzler:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN -- Searched CASE expression RETURN CASE WHEN letter_in = 'A' THEN 'Apple' WHEN letter_in = 'B' THEN 'Banana' END; END fruit_translator; /
Function created.
When you execute the above function, passing a value that is not ‘A’ and not ‘B’, the CASE expression should return NULL
SQL> BEGIN 2 DBMS_OUTPUT.put_line ( 'Expression good for you = ' 3 || NVL (fruit_translator ('C'), 'Unknown Fruit') 4 ); 5 END; 6 / Expression good for you = Unknown Fruit As expected. The following function contains a CASE statement:
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (100); BEGIN -- Searched CASE statement CASE WHEN letter_in = 'A' THEN retval := 'Apple'; WHEN letter_in = 'B' THEN retval := 'Banana'; END CASE; RETURN retval; END fruit_translator; /
Function created.
When you execute the above function passing a value that is not ‘A’ and not ‘B’, and because there is no ELSE specified, t statement should raise an (unhandled) exception:
SQL> BEGIN 2 DBMS_OUTPUT.put_line ( 'Statement good for you = ' 3 || NVL (fruit_translator ('C'), 'Unknown Fruit') 4 ); 5 END;
6 / BEGIN * ERROR at line 1: ORA-06592: CASE not found while executing CASE statement ORA-06512: at "EDDIE.FRUIT_TRANSLATOR", line 7 ORA-06512: at line 2 As expected.
One more thing to note about CASE. In Oracle 8i, CASE statements and expressions are supported only in SQL and not in In 8i you cannot use CASE in PL/SQL. Starting with Oracle database release 9.0.1, the SQL and PL/SQL parsers were integ and, as a result, in version 9.0.1 and above CASE works in both SQL and PL/SQL. Thank you Steven for this nice puzzler. It’s easy to solve (without executing the code between CASE statements and CASE expressions.
) as long as you know the differe
Sources and resources:
CASE gotcha in Oracle 8i
Improving SQL efficiency using CASE
CASE Statements
CASE Expressions
Simple CASE expression
Searched CASE Expression
Using CASE Statements
Share This Subscribe to this feed • Save to del.icio.us Post a Comment | Trackbacks Closed | 124 Views
Home > About This Post
Post a Comment
This entry was posted by Eddie Awad on Wednesday, July 25th, 2007, at 5:00 am, and was filed in pl/sql, Oracle.
Your email is never published nor shared. Required fie marked *
Subscribe to the
RSS 2.0 feed for all comments to this post.
Name:
Email: Search This Blog Website/Blog:
Find Spam protection: Sum of four + seven ? *
Recent Posts What you Ought to Know About CASE in Oracle PL/SQL
Comment:
Get All the Cool New Features of Oracle Application Express 3.0 in Oracle Database XE New Oracle ACE and Website Rousing Entertainment With the Guru of Oracle PL/SQL Oracle Database 11g Old Feature: Internet Addressing 8 Links from Around the Web (2007-07-13)
Post c Notify me of followup comments via e-mail d e f g
Oracle Blogs Ranked by Technorati Authority - July 07 Edition 5 Links from Around the Web (2007-07-07) Running on a Virtual Private Server and a Couple of Pictures Here is A Different And Handy Way to Search in Your Browser 16 Links to Help You Learn And Use Regular Expressions Oracle Database 11g Overview Presentation Related Posts CASE gotcha in Oracle 8i CASE used in CREATE INDEX Oracle PLSQL in CFQUERY “Between” CASE and DECODE links for 2006-12-08 Most Viewed Posts HTTP POST from inside Oracle - 10,587 Views 10 Things About Computer Programming You May Not Agree With - 7,333 Views Format your SQL the easy way - 6,735 Views Ubuntu on Microsoft Virtual PC Works Like a Charm - 6,309 Views Two Oracle PL/SQL Features You Probably Don’t Know About - 4,204 Views Oracle SQLPlus - 4,132 Views Back to basics: outer joins - 3,391 Views Saving/Downloading files to/from Oracle using ColdFusion 3,379 Views Comments Do Make a Difference - 3,201 Views Back to basics: inner joins - 3,119 Views Eddie Awad Randomized What you Ought to Know About CASE in Oracle PL/SQL Get All the Cool New Features of Oracle Application Express 3.0 in Oracle Database XE ©
Eddie Awad: You know that Facebook is the next big thing when your aunt in Canada, your brother in...
joel garry (6)
2007 Eddie Awad
Simplr theme by Scott
Powered by WordPress
Slideshare Adds Audio Synchronization Here’s how to mess with people talkin’ too much about Facebook Oracle refutes 'SSH hacking' slur - Register Bugtraq: Oracle bad Views - Exploit released 70+ Tools For Job Hunting 2.0
Sponsor: Chicago Flower D
Valid XHTML & CSS
RSS: Posts & Comm
Related Documents
What You Ought To Know About Case In Oracle Plsql
November 2019 9
What Missionaries Ought To Know
November 2019 8
What Every Hindu Ought To Know
June 2020 9
What You Need To Know About Your Chapter 13 Case
October 2019 24
What You Need To Know
July 2019 39