Oracle SQL*Plus中TTITLE不显示及冗余空格问题求助

问题说明 #

编写的Oracle SQL*Plus脚本存在两个问题：

1. TTITLE CENTER 'Sales for ' &sDate ' to ' &ednDate'语句无法正常显示标题
2. 调整列间距测试后，即使删除相关代码，输出仍存在冗余空格

解决方案 #

1. 修复TTITLE标题显示问题 #

SQL*Plus中拼接变量和字符串需要遵循正确语法，原语句存在语法错误，可通过以下两种方式修正：

方式一：使用字符串连接符||

TTITLE CENTER 'Sales for ' || '&sDate' || ' to ' || '&ednDate'

方式二：用双引号直接嵌入变量（SQL*Plus会自动解析&开头的变量）

TTITLE CENTER "Sales for &sDate to &ednDate"

2. 消除冗余空格问题 #

冗余空格主要来自列标题定义、输入变量残留空格以及SQL*Plus环境设置缓存，可通过以下步骤解决：

• 清理列标题的多余空格：检查所有COLUMN语句的HEADING参数，移除标题字符串末尾的空格。例如：
  原代码：

  COLUMN customerID FORMAT A8 HEADING "Customer ID "; 
  

  修改为：

  COLUMN customerID FORMAT A8 HEADING "Customer ID"; 
  

• 避免输入变量带空格：在ACCEPT语句中添加TRIM选项，自动去除输入值前后的空格：

  ACCEPT sDate CHAR FORMAT 'A10' PROMPT 'Enter the start date: ' TRIM
  ACCEPT ednDate CHAR FORMAT 'A10' PROMPT 'Enter the end date: ' TRIM
  

• 清除环境设置缓存：在脚本开头添加CLEAR ALL，彻底清除之前的列、断点、计算等设置残留：

  CLEAR ALL
  

修改后的完整脚本 #

CLEAR ALL
SET linesize 140
SET pagesize 30
SET underline =
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

ACCEPT sDate CHAR FORMAT 'A10' PROMPT 'Enter the start date: ' TRIM
ACCEPT ednDate CHAR FORMAT 'A10' PROMPT 'Enter the end date: ' TRIM

COLUMN customerID FORMAT A8 HEADING "Customer ID"; 
COLUMN fname FORMAT A10 HEADING "First name"; 
COLUMN memberID FORMAT A11 HEADING "Member ID"; 
COLUMN salesdate FORMAT A10 HEADING "Sales Date"; 
COLUMN salesID FORMAT A9 HEADING "Sales ID"; 
COLUMN countriesName FORMAT A18 HEADING "Countries"; 
COLUMN productName FORMAT A35 HEADING "Product Name"; 
COLUMN totalAmount FORMAT $999999.99 HEADING "Price (yen)"; 
COLUMN subtotal FORMAT $999999.99 HEADING "Subprice(yen)"; 

TTITLE CENTER "Sales for &sDate to &ednDate"
BTITLE LEFT 'Page No: ' FORMAT 999 SQL.PNO SKIP 2
BREAK ON customerID SKIP 3 ON fname ON memberID 
COMPUTE SUM LABEL 'Total (Yen): ' OF subtotal ON memberID 

CREATE VIEW salesDateView AS
SELECT s.customerID, c.fname, salesID, co.countriesName, p.productName, 
       s.totalAmount, m.memberID, s.totalAmount*(1-discountAmount) AS subtotal,
       salesdate
FROM sales s, customers c, member m, list l, discount d, product p, shop sh, countries co
WHERE c.customerID = m.customerID 
  AND m.memberID = d.memberID 
  AND d.discountID = s.discountID 
  AND s.listID = l.listID 
  AND l.productID = p.productID 
  AND s.shopID = sh.shopID 
  AND sh.countriesID = co.countriesID 
  AND TO_DATE(salesdate, 'DD/MM/YYYY') BETWEEN TO_DATE('&sDate', 'DD/MM/YYYY') AND TO_DATE('&ednDate', 'DD/MM/YYYY')
ORDER BY customerID, salesdate;

SELECT * FROM salesDateView;
DROP VIEW salesDateView;

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
TTITLE OFF
BTITLE OFF

内容的提问来源于stack exchange，提问作者CHONG YAO LIANG