条件时的PLSQL情况 [英] PLSQL CASE WHEN CONDITION

问题描述

我有两个查询.

下面的PL/SQL无法正常工作.我想将输出存储到变量test1和test2中.这是说ORA-00923:找不到FROM关键字.不确定出什么事了

Below PL/SQL is not working. I want to store the output into varable test1 and test2. It's saying ORA-00923: FROM keyword not found. Not sure what is wrong

DECLARE    
  file_id NUMBER(10) NOT NULL :=5;
  test1   varchar(100);
  test2   varchar(100);

BEGIN

  DBMS_OUTPUT.PUT_LINE('File Id: ' || file_id);

  SELECT table_name 
    INTO test1,
         (CASE owner
            WHEN 'SYS' THEN  'The owner is SYS'
            WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
          END) 
    INTO test2 
    FROM all_tables 
   WHERE rownum <= 1;

END;

查询2.

在PL/SQL中，如果我仅使用select语句而不使用into子句，则它不起作用.这是我需要使用into子句的规则.以下一项不起作用.如果我要假脱机PL/SQL程序的输出，是否需要将column的输出存储到变量中并执行dbms_output?

Query 2.

In PL/SQL if i just use the select statement without into clause it's not working. Is it a rule that i need to use into clause. The below one does not work. If i want to spool a output from PL/SQL program do i need to store the output of column into the variable and do a dbms_output?

DECLARE

  file_id  NUMBER(10) NOT NULL :=5;
  test1 varchar(100);
  test2 varchar(100);

BEGIN

  DBMS_OUTPUT.PUT_LINE('File Id: ' || file_id);

  SELECT table_name,
         CASE owner
           WHEN 'SYS' THEN 'The owner is SYS'
           WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
         END
    FROM all_tables;

END;

推荐答案

在PL/SQL查询中只需要一个INTO子句，例如:

You only need one INTO clause in a PL/SQL query, for example:

SELECT table_name, CASE owner WHEN bla bla ... END
INTO test1, test2
FROM all_tables;

这篇关于条件时的PLSQL情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！