Expresiones y declaraciones CASE en Oracle

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

10 comentarios en “Expresiones y declaraciones CASE en Oracle”

  1. 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

  2. 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

  3. 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

  4. 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.

  5. 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!

  6. (whatsapp:+15054560788)Buy Abortion pills in DUBAI #Sharjah, Abu Dhabi, @Dubai, Ajman In Doha ?] +15054560788__?***?]”Abortion Pills For Sale In Doha Qatar BUY NEMBUTAL IN SPAIN,US,BRITAIN,UK,FRANCE buy ketamine online buy xanax online buy adderall online buy temazepam online buy tramadol online buy cannabis online buy canabis oil online. buy human growth homone pills online,CRAZYBULK HGH-X2,PROVACYL,GENF20PLUS,SEROVITAL buy fertility pills online, buy cytotec,mifepristone,misoprostol pills online. BUY MARIJUANA,SATIVA,MOONROCK,MUSHROOMS,MEDICAL MARIJUANA OIL,BUY HUMAN GROWTH HOMONES PILLS,BUY WEED Abu Dhabi, Ajman, Al Ain, Dubai, Fujairah, Ras Al Khaimah (RAK), Sharjah, Umm Al Quwain (UAQ) United Arab Emirates Abortion Clinic (Tampa Abortgion Clinic) was established in 1998 and is located in the beautiful, Lakeshore District of Hyde Park in Tampa Bay. It has been serving the State of FLorida and the United States for over 20 years. Our Abu Dhabi, United Arab Emirates Abortion Clinic provides the safest and most advanced techniques for providing non-surgical, medical and surgical abortion methods for early through late second trimester, including the Abortion By Pill Procedure (RU 486, Mifeprex, Mifepristone, early options French Abortion Pill), Tamoxifen, Methotrexate and Cytotec (Misoprostol). The Abu Dhabi, United Arab Emirates Abortion Clinic performs Same Day Abortion Procedure using medications that are taken on the first day of the office visit and will cause the abortiaon to occur generally within 4 to 6 hours (as early as 30 minutes) for patients who are 3 to 12 weeks pregnant. When Mifepristone and Misoprostol are used, 50 of patients complete in 4 to 6 hours; 75 to 80 in 12 hours; and 90 in 24 hours. We use a regimen that allows for completion without the need for surgery 99 of the time. All advanced second trimester and late term pregnancies at our Tampa clinic (17 to 24 weeks or greater) can be completed within 24 hours or less 99 of the time without the need surgery. The procedure is completed with minimal to no complications. Our Women’s Health Center located in Abu Dhabi, United Arab Emirates, Abu Dhabi, United Arab Emirates, uses the latest medications for medical abortions (RU486, Mifeprex, Mifegyne, Mifepristone, early options French abortion pill), Methotrexate and Cytotec (Misoprostol). The safety standards of our Abu Dhabi, United Arab Emirates Abortion Doctors remain unparalleled. They consistently maintain the lowest complication rates throughout the nation. Our Physicians and staff are always available to answer questions and care for women in one of the most difficult times in their life. The decision to have an abortion at the Abortion Clinic in Abu Dhabi, United Arab Emirates, involves moral, ethical, religious, family, financial, health and age considerations. email// slimjay237@gmail.com whatsapp:+15054560788

  7. Hi there,

    We run an Instagram growth service, which increases your number of followers safely and practically.

    We aim to gain you 300-1000+ real human followers per month, with all actions safe as they are made manually (no bots).

    The price is just $60 (USD) per month, and we can start immediately.

    Let me know if you are interested and have any questions.

    Kind Regards,
    Libby

  8. Greetings,

    This is Greg Stewart from Wyoming..

    I would like to take a moment of your time to introduce you to a business funding option that has no upfront fees and can help your business get credit based on your provable personal income and great credit history.

    Unsecured Loans/Lines of Credit less than $300,000 USD– USA ONLY

    $25,000 to $300,000
    Interest ranges from 5 to 15% per year
    Term Varies from 3 to 7 years
    Minimum credit score is 680+
    Minimum income is $30k/year
    No Upfront Fees

    Business Credit Cards – USA ONLY

    $25,000 to $100,000
    O% interest for 6 to 13 months
    Interest rates from 8 to 18% per year after interest free period
    Minimum credit score is 680+
    No Upfront Fees

    To apply simply click here – https://cutt.ly/8wjcjnwFj

    All the best,

    Gregory Stewart
    Owner

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *