背景
我熟悉資料庫，但從未特別使用過 Oracle。在嘗試幫助某人完成家庭作業的程序中，我在嘗試理解BREAK命令的行為時遇到了障礙。特別是，使用該BREAK命令的結果似乎取決于我正在使用的查詢中列的順序，據我所知，這并沒有以任何方式反映在檔案中。
設定示例
我創建了一個表格，其中有幾種不同的顏色，每個都有一些該顏色的專案，如下所示：
CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
顯示預期行為的示例
當我運行以下命令時，
CLEAR COLUMNS;
CLEAR BREAKS;
CLEAR SCREEN;
SET LINESIZE 120;
COLUMN product_color NOPRINT NEW_VALUE the_color;
BREAK ON product_color SKIP PAGE;
TTITLE CENTER the_color;
SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
這似乎產生了合理的輸出，每種顏色有一個“頁面”顯示頂部的顏色并顯示下面的相關記錄：
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
表現出意外行為的看似相同的示例
However, if I simply change the order of the columns in the query, i.e. replacing the last line of the script above with something like:
SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
then the results are completely different, generating a page break after every single record for no apparent reason:
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
2 Green shirt
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
5 Traffic cone
Questions
- Why does changing the order of the columns in the query output result in different behavior from the
BREAKstatement? (Or is theBREAKcommand a red herring, with the page breaks in the second example being created for some unrelated reason?) - Is this actually documented somehow in the documentation, but in some way that I'm not understanding?
- Is there a term describing this behavior that I can search for to read more?
- Is there any better documentation for SQL*Plus than the official Oracle site? It seems to be fairly spotty and rarely to unambiguously specify what the commands do.
Possible leads
I have read through the documentation for the SQL*Plus BREAK command located at https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve009.htm#SQPUG030 but I'm not seeing anything relevant to the order that columns appear in.
I thought the issue might potentially have something to do with suppression of duplicates interfering with the detection of the field changing, but adding DUPLICATES to the BREAK command has no effect.
Of the six possible permutations, it looks like the ones that cause a problem are the two where product_color is the first column.
同樣，將查詢更改為類似
SELECT 1, product_color, product_id, product_name FROM products ORDER BY product_color;
產生預期的行為，這表明最左邊的列可能在這里扮演了一些特殊的角色。
此外，我發現NOPRINT從COLUMN命令中洗掉會使這種行為消失，原因我不明白。
環境資訊
oracle服務器報告它是
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
我使用 Oracle SQL Developer 版本 21.2.1.204，Build 204.1703 進行連接。
uj5u.com熱心網友回復：
奇怪的。我的環境和你的一樣，我沒有看到這種行為。我剛剛clear screen從您發布的代碼中洗掉。
這是為您（和我）回傳正確結果的查詢。
客戶端 11.2.0.2.0
C:\Oracle\oraclexe112_64bit\app\oracle\product\11.2.0\server\bin>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.2.0 Production on ╚et Stu 11 08:03:58 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL>
現在，在您的資料庫中產生意外中斷的查詢（但不在我的資料庫中），product_color是select列串列中的第一列：
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL>
我也嘗試了不同的客戶端，沒問題：
客戶端 11.2.0.1.0
SQL*Plus: Release 11.2.0.1.0 Production on ╚et Stu 11 08:10:56 2021
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL>
客戶端 18.5.0.0.0
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Nov 11 08:12:20 2021
Version 18.5.0.0.0
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL>
SQL> SET LINESIZE 120;
SQL>
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL>
SQL> TTITLE CENTER the_color;
SQL>
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL>
我認為這很關鍵：
我使用 Oracle SQL Developer 版本 21.2.1.204，Build 204.1703 進行連接。
SQL Developer. Me and Roberto used SQL Plus, Oracle's command-line tool.
When I tested your code that returns "invalid" result, so true!
columns cleared
breaks cleared
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
2 Green shirt
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
5 Traffic cone
It is SQL Developer that produces "wrong" result. It has nothing to do with the database version, but the tool.
So, what to do? Use SQL*Plus instead :) Or, download SQLcl, the newest command-line tool Oracle provides.
uj5u.com熱心網友回復：
BREAK在你的例子中的行為是不對的。感謝@Littlefoot，我更新了這篇文章，這是因為您使用的是 SqlDeveloper，這是一種不適合此類報告的 Java 工具。
我創建了您的表并將記錄插入到兩個資料庫中，然后運行您所做的報告。
演示 12cR2
SQL> select banner from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
Table created.
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
1 row created.
SQL> INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
1 row created.
現在讓我們運行報告
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR breaks;
breaks cleared
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL> TTITLE CENTER the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
正如您所看到的，在 12.2 中，BREAK命令中沒有意外行為，它可以正常作業。
演示 19c
SQL> select banner_full from v$version ;
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> CREATE TABLE products(product_id NUMBER, product_color VARCHAR(10), product_name VARCHAR(20));
Table created.
INSERT INTO products(product_id, product_color, product_name) VALUES(1, 'Green', 'Green baseball cap');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(2, 'Green', 'Green shirt');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(3, 'Green', 'Grapes');
1 row created.
INSERT INTO products(product_id, product_color, product_name) VALUES(4, 'Orange', 'Orange baseball cap');
1 row created.
SQL> INSERT INTO products(product_id, product_color, product_name) VALUES(5, 'Orange', 'Traffic cone');
1 row created.
現在，讓我們運行報告
SQL> CLEAR COLUMNS;
columns cleared
SQL> CLEAR BREAKS;
breaks cleared
SQL> COLUMN product_color NOPRINT NEW_VALUE the_color;
SQL> BREAK ON product_color SKIP PAGE;
SQL> TTITLE CENTER the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
SQL> SELECT product_color, product_id, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_NAME
---------- --------------------
4 Orange baseball cap
5 Traffic cone
因此，BREAK命令的預期行為相同。
回答您的問題
Why does changing the order of the columns in the query output result in different behaviour from the BREAK statement? (Or is the BREAK command a red herring, with the page breaks in the second example being created for some unrelated reason?)
It does not. As I said before, such behaviour can't be reproduced neither in 12c nor 19c ( not in 11g as @Littlefoot shown ). I assure you that the normal behaviour of BREAK is not affected by the order.
Is this actually documented somehow in the documentation, but in some way that I'm not understanding?
Not really, as you understands how the command works but not the behaviour you are getting, which clearly is not reproducible in 12c or higher versions. I have no option to test nothing in 11g as that's a very old and unsupported version, but in the other answer from @Littlefoot, you can see that the behaviour is the same.
Is there any better documentation for SQLPlus than the official Oracle site? It seems to be fairly spotty and rarely to unambiguously specify what the commands do.*
The reference is the best you can find. Obviously, Oracle, as every other major software provider, is not perfect documenting. I can't blame them for that, honestly, as it happens everywhere. Actually, I do think they have one of the best ones.
My advice: Use sqlplus and Upgrade your database. 11g was deprecated years ago. You can try with sqlcl the sql developer command line interface, but I never used against 11g, although I do think it should not be a problem
By the way, regarding I have discovered that removing NOPRINT from the COLUMN command makes this behaviour go away, for reasons I don't understand.
PRINT|NOPRINT Controls the printing of the column (the column heading and all the selected values). NOPRINT turns off the screen output and printing of the column. PRINT turns the printing of the column ON.
Not exactly. If you change NOPRINT by PRINT, it works as it should, so that you might print the column that is part of the BREAK. But, it only shows once in the results, because you break on it.
SQL> COLUMN product_color PRINT NEW_VALUE the_color;
SQL> SELECT product_id, product_color, product_name FROM products ORDER BY product_color;
Green
PRODUCT_ID PRODUCT_CO PRODUCT_NAME
---------- ---------- --------------------
1 Green Green baseball cap
2 Green shirt
3 Grapes
Orange
PRODUCT_ID PRODUCT_CO PRODUCT_NAME
---------- ---------- --------------------
4 Orange Orange baseball cap
5 Traffic cone
Hope this long answer help you somehow. Feel free to ask any question or doubt you might have. But I am quite sure that the reason is your version.
UPDATE
As @Littlefoot as stated in his answer below, the issue might be related with SQLDeveloper, which is a Java tool. I did not realize you were using SQL Developer, even though you can run scripts, it is not the same for those sqlplus specific reporting commands. I was relying in your documentation links to sqlplus, so naturally I assume you were using it.
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/356375.html