如何实现SQL*Plus格式输出？及字段值为'Q'时输出'Query'的SQL代码？

Hey folks! Let's dive into SQL*Plus output formatting and walk through how to handle that specific field mapping scenario where you need to show 'Query' whenever the field value is 'Q'.

SQL*Plus gives you tons of control over how your query results and script output look. Here are the most useful tools to master:

• COLUMN Command: This is your go-to for customizing column display. You can set aliases, format widths, wrap text, and even add line breaks in headers. For example:

  -- Set a multi-line header and limit column width to 20 characters
  COLUMN employee_name HEADING 'Employee|Full Name' FORMAT A20
  -- Format a numeric column to show currency
  COLUMN salary HEADING 'Annual|Salary' FORMAT $999,999.99
  

• TTITLE/BTITLE: Use these to add headers and footers to your reports. Perfect for formal documentation:

  TTITLE CENTER 'Quarterly Sales Report' RIGHT 'Page: ' FORMAT 999 SQL.PNO
  BTITLE CENTER 'Generated on: ' SYSDATE
  

• SET Commands: Tweak global output settings to clean up results:
  • SET LINESIZE 180: Adjust the maximum line width to prevent wrapping
  • SET PAGESIZE 60: Set how many rows show per page
  • SET TRIMSPOOL ON: Remove trailing spaces from spooled output files
  • SET FEEDBACK OFF: Hide the "X rows selected" message at the end of queries
• PROMPT/ACCEPT: For interactive scripts, use PROMPT to print messages and ACCEPT to grab user input:

  PROMPT Please enter the department ID:
  ACCEPT dept_id NUMBER PROMPT 'Dept ID: '
  SELECT * FROM employees WHERE department_id = &dept_id;
  

There are two common scenarios for this requirement—let's cover both:

1. Replace Values Directly in Query Results #

If you want the mapped value to show up in your query output, use a CASE expression. This works directly in standard SQL and plays nicely with SQL*Plus formatting:

-- First, set up formatting for the mapped column (optional but recommended)
COLUMN field_label HEADING 'Field|Type' FORMAT A10

SELECT
    CASE
        -- Match exact 'Q' value; use UPPER() if you need case-insensitive matching
        WHEN UPPER(field) = 'Q' THEN 'Query'
        -- Show original value for all other cases, or replace with something else
        ELSE field
    END AS field_label,
    -- Include other columns you need
    id,
    record_date
FROM your_table;

Pro tip: Use UPPER(field) or LOWER(field) if your database is case-sensitive and you want to catch both 'q' and 'Q'.

2. Conditional Output in SQL*Plus Scripts #

If you need to print 'Query' as a standalone message (not in query results), use a PL/SQL block with DBMS_OUTPUT. First, enable server output in SQL*Plus:

SET SERVEROUTPUT ON

DECLARE
    -- Declare a variable matching your field's data type
    v_field_value your_table.field%TYPE;
BEGIN
    -- Fetch the field value from your table (adjust WHERE clause to target the right row)
    SELECT field INTO v_field_value
    FROM your_table
    WHERE id = 123; -- Replace with your actual row identifier

    -- Check the value and print the result
    IF v_field_value = 'Q' THEN
        DBMS_OUTPUT.PUT_LINE('Query');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Non-Query Value: ' || v_field_value);
    END IF;
END;
/

Hope these examples help you get exactly the output you need in SQL*Plus!

内容的提问来源于stack exchange，提问作者Paula