La expresión CASE se agregó por primera vez a SQL en Oracle 8i. Desde entonces Oracle ha extendió su soporte a PL/SQL para permitir que CASE se use como expresión o declaración.
- Expresión CASE de coincidencia de valor (simple)
- Expresión CASE buscada
- Declaración CASE de coincidencia de valor (simple)
- Declaración CASE buscada
Expresión CASE de coincidencia de valor (simple)
La expresión CASE es como una versión más flexible de la función DECODE. La expresión CASE de coincidencia de valor, o expresión CASE simple, compara el valor de la expresión (DEPTNO) con la lista de expresiones de comparación (10 – 40). Una vez que encuentra una coincidencia, se devuelve el valor asociado. La cláusula ELSE opcional le permite tratar situaciones en las que no se encuentra una coincidencia. Observe que la expresión CASE tiene un alias como «departamento». Esto aparecerá como el nombre de la columna.
SELECT ename, empno, deptno,
(CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END) department
FROM emp
ORDER BY ename;
La expresión CASE de coincidencia de valor también se admite en PL/SQL. El siguiente ejemplo lo usa en una tarea.
SET SERVEROUTPUT ON
DECLARE
deptno NUMBER := 20;
dept_desc VARCHAR2(20);
BEGIN
dept_desc := CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/
Todos los valores posibles devueltos por una expresión CASE deben ser del mismo tipo de datos.
Expresión CASE buscada
La expresión CASE buscada puede ser más complicada, ya que implica varias columnas en las comparaciones. Cada comparación se prueba a su vez y se devuelve el valor asociado si se encuentra una coincidencia. Una vez más, existe una cláusula ELSE opcional para tratar situaciones en las que no se encuentra una coincidencia.
SELECT ename, empno, sal,
(CASE
WHEN sal < 1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END) salary
FROM emp
ORDER BY ename;
La expresión CASE buscada también se admite en PL/SQL.
SET SERVEROUTPUT ON
DECLARE
sal NUMBER := 2000;
sal_desc VARCHAR2(20);
BEGIN
sal_desc := CASE
WHEN sal < 1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END;
DBMS_OUTPUT.PUT_LINE(sal_desc);
END;
/
Todos los valores posibles devueltos por una expresión CASE deben ser del mismo tipo de datos.
Declaración CASE de coincidencia de valor (simple)
Las declaraciones CASE soportadas por PL/SQL son muy similares a las expresiones CASE. Observe que la declaración termina con las palabras clave END CASE en lugar de solo la palabra clave END. Las sentencias PL/SQL CASE son esencialmente una alternativa a las sentencias IF .. THEN .. ELSIF. Son estructuras de control que llaman condicionalmente bloques de código.
La declaración CASE de coincidencia de valor a continuación ejecuta un bloque de código diferente según la coincidencia encontrada.
SET SERVEROUTPUT ON BEGIN FOR cur_rec IN (SELECT ename, empno, deptno FROM emp ORDER BY ename) LOOP DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ' || cur_rec.deptno || ' : '); CASE cur_rec.deptno WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('Accounting'); WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Research'); WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('Sales'); WHEN 40 THEN DBMS_OUTPUT.PUT_LINE('Operations'); ELSE DBMS_OUTPUT.PUT_LINE('Unknown'); END CASE; END LOOP; END; /
Declaración CASE buscada
Al igual que con su contraparte de expresión, la instrucción CASE buscada permite múltiples comparaciones utilizando múltiples variables.
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP
DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ' || cur_rec.sal || ' : ');
CASE
WHEN cur_rec.sal < 1000 THEN
DBMS_OUTPUT.PUT_LINE('Low');
WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN
DBMS_OUTPUT.PUT_LINE('Medium');
WHEN cur_rec.sal > 3000 THEN
DBMS_OUTPUT.PUT_LINE('High');
ELSE
DBMS_OUTPUT.PUT_LINE('Unknown');
END CASE;
END LOOP;
END;
/
• Oracle9iSQL Reference Release 2 (9.2) : SQL CASE Expression
• PL/SQL User’s Guide and Reference Release 2 (9.2) : PL/SQL CASE Expression
• PL/SQL User’s Guide and Reference Release 2 (9.2) : PL/SQL CASE Statement
DOMAIN SERVICES EXPIRATION NOTICE FOR dbandtech.com
Domain Notice Expiry ON: Dec 24, 2020
We have not gotten a settlement from you.
We have actually tried to contact you however were unable to contact you.
Browse Through: https://cutt.ly/Oh2pUcZ
For information and to post a discretionary settlement for your domain website solutions.
122420202124003753688578798dbandtech.com
Good day,
My name is Eric and unlike a lot of emails you might get, I wanted to instead provide you with a word of encouragement – Congratulations
What for?
Part of my job is to check out websites and the work you’ve done with dbandtech.com definitely stands out.
It’s clear you took building a website seriously and made a real investment of time and resources into making it top quality.
There is, however, a catch… more accurately, a question…
So when someone like me happens to find your site – maybe at the top of the search results (nice job BTW) or just through a random link, how do you know?
More importantly, how do you make a connection with that person?
Studies show that 7 out of 10 visitors don’t stick around – they’re there one second and then gone with the wind.
Here’s a way to create INSTANT engagement that you may not have known about…
Talk With Web Visitor is a software widget that’s works on your site, ready to capture any visitor’s Name, Email address and Phone Number. It lets you know INSTANTLY that they’re interested – so that you can talk to that lead while they’re literally checking out dbandtech.com.
CLICK HERE http://www.talkwithcustomer.com to try out a Live Demo with Talk With Web Visitor now to see exactly how it works.
It could be a game-changer for your business – and it gets even better… once you’ve captured their phone number, with our new SMS Text With Lead feature, you can automatically start a text (SMS) conversation – immediately (and there’s literally a 100X difference between contacting someone within 5 minutes versus 30 minutes.)
Plus then, even if you don’t close a deal right away, you can connect later on with text messages for new offers, content links, even just follow up notes to build a relationship.
Everything I’ve just described is simple, easy, and effective.
CLICK HERE http://www.talkwithcustomer.com to discover what Talk With Web Visitor can do for your business.
You could be converting up to 100X more leads today!
Eric
PS: Talk With Web Visitor offers a FREE 14 days trial – and it even includes International Long Distance Calling.
You have customers waiting to talk with you right now… don’t keep them waiting.
CLICK HERE http://www.talkwithcustomer.com to try Talk With Web Visitor now.
If you’d like to unsubscribe click here http://talkwithcustomer.com/unsubscribe.aspx?d=dbandtech.com
Ying Ying Chew is Managing Director, Human Capital at Apollo
and is responsible for leading Human Capital for the groups in Singapore, Hong Kong, Japan, Australia, and
China.
Akɑn khalayak pengguna, kami akan senantiasa merelakan produk yɑng tidak
cendala serta teratas Terlebih, kami memasгahҝan penyajian pemesanan secara online.
Maka begitu, di website ini tеrseliⲣ kontak kami bɑkal dapat Anda huƄungi.
Also visit my blog Harga Jayamix
This information is worth everyone’s attention. When can I find out more?
I’m extremely impressed with your writing skills as well as
with the layout on your blog. Is this a paid theme or
did you modify it yourself? Anyway keep up the nice quality writing, it’s rare to see a nice blog like this one today.
Hi, There’s no doubt that your web site may be having web browser
compatibility problems. Whenever I take a look at your website in Safari,
it looks fine but when opening in I.E., it has some overlapping issues.
I simply wanted to give you a quick heads up! Other than that, wonderful website!