[20231103]sqlplus column new_value old_value.txt

--//連結:

OLD_VALUE vs. NEW_VALUE

OLD_VALUE acts very similarly to NEW_VALUE:

The difference between the two only really comes up when you're using them for their intended purpose, to help you put
page headers and footers on sql*plus reports. The current value of NEW_VALUE and OLD_VALUE can be displayed in report
headers(TTITLE, REPHEADER) and footers (BTITLE, REPFOOTER). NEW_VALUE variables hold data from the new row about to be
printed on the page; OLD_VALUE variables hold data from the old row that was most recently printed on the page. Thus,
NEW_VALUE is usefulfor the report header, OLD_VALUE for the report footer. When you're just using NEW_VALUE and
OLD_VALUE to get values from thedatabase into script variables, either NEW_VALUE or OLD_VALUE will do.

只有當你為其預期的目的使用它們時，這兩者之間的區別才會真正顯現出來，以幫助你將頁首和頁尾放在sql*+報告上。NEW_VALUE和
OLD_VALUE的當前值可以顯示在報表頁頭(TTITLE、重新頁頭)和頁尾(BTITLE、重新頁尾)中。NEW_VALUE變數儲存即將列印在頁面上的新
行中的資料；OLD_VALUE變數儲存來自最近列印在頁面上的舊行中的資料。因此，NEW_VALUE對於報表頁頭有用，OLD_VALUE對於報表頁尾
有用。當您只是使用NEW_VALUE和OLD_VALUE從資料庫中獲取值到指令碼變數時，NEW_VALUE或OLD_VALUE都可以使用。
--//我看了半天沒看明白。似乎兩者在指令碼中是一樣的。
--//上面我一直不理解怎麼意思。實際上OLD_VALUE 和. NEW_VALUE僅僅在TTITLE和BTITLE有用。

--//並不像我以前理解的如下：
column xxxx new_value x2 old_value x1
--//new_value 儲存新值在X2中。儲存原來的舊值在X1中，實際上並不是這個意思，我理解錯誤。

--//自己做一個例子：
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> set pagesize 10
SCOTT@test01p> column dname new_value v_name1 old_value v_name2
SCOTT@test01p> ttitle left 'First department: ' v_name1
SCOTT@test01p> btitle left 'Last department : ' v_name2
SCOTT@test01p> select * from dept ;

First department: ACCOUNTING
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON
Last department : OPERATIONS

SCOTT@test01p> define v_name1
DEFINE V_NAME1         = "OPERATIONS" (CHAR)
SCOTT@test01p> define v_name2
DEFINE V_NAME2         = "OPERATIONS" (CHAR)
--//最後都是"OPERATIONS".
--//語法都很奇怪，沒有前面引用的&.標題頭使用new_value,標題尾使用old_value.
--//如果這樣寫錯誤:
SCOTT@test01p> column dname new_value v_name2 old_value v_name1
SCOTT@test01p> select * from dept ;

First department: OPERATIONS
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

Last department : OPERATIONS

--//沒有怎麼用處.使用完最好清除設定.自己打入select * from emp;就明白為什麼.

SCOTT@test01p> set pagesize 20
SCOTT@test01p> column dname new_value v_name1 old_value v_name2
SCOTT@test01p> select * from dept ;
....
SCOTT@test01p> select * from emp ;
First department: OPERATIONS
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
Last department : OPERATIONS
14 rows selected.

SCOTT@test01p> ttitle off
SCOTT@test01p> btitle off
SCOTT@test01p> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

[20231103]sqlplus column new_value old_value.txt

相關文章