[20230303]sqlplus column new_value old_value.txt
[20230303]sqlplus column new_value old_value.txt
--//前幾天在測試時遇到的問題,我以為定義列
column xxxx new_value x2 old_value x1
--//new_value 儲存新值在X2中。儲存原來的舊值在X1中,實際上並不是這個意思,我理解錯誤。
--//透過例子說明:
1.環境:
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
--//連結:
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都可以使用。
--//我看了半天沒看明白。似乎兩者在指令碼中是一樣的。
When you're just using NEW_VALUE and OLD_VALUE to get values from the database into script variables, either NEW_VALUE
or OLD_VALUE will do.
--//自己很少使用sqlplus repoer型別,總之沒有一個好的例子說明其用途。
2.測試:
SCOTT@test01p> column current_scn new_value v_scn2 old_value v_scn1
SCOTT@test01p> select current_scn from v$database;
CURRENT_SCN
-----------
14430020
SCOTT@test01p> select &v_scn1 n1 ,&v_scn2 n2 from dual ;
N1 N2
---------- ----------
14430020 14430020
SCOTT@test01p> select current_scn from v$database;
CURRENT_SCN
-----------
14430039
SCOTT@test01p> select &v_scn1 n1 ,&v_scn2 n2 from dual ;
N1 N2
---------- ----------
14430039 14430039
--//我開始的理解14430039儲存在v_scn2,14430020儲存在v_scn1.實際上情況根本不是這樣!!
select current_scn from v$database
union all
select current_scn+1 current_scn from v$database;
CURRENT_SCN
-----------
14430084
14430086
SCOTT@test01p> select &v_scn1 n1 ,&v_scn2 n2 from dual ;
N1 N2
---------- ----------
14430086 14430086
--//還有一個特點,clear columns不會清除v_scn1,v_scn2。
SCOTT@test01p> clear columns
columns cleared
SCOTT@test01p> define v_scn1
DEFINE V_SCN1 = 14430086 (NUMBER)
SCOTT@test01p> define v_scn2
DEFINE V_SCN2 = 14430086 (NUMBER)
--//總之,不像我想象的那樣使用。那位知道,給一個例子說明問題。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2937973/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20231103]sqlplus column new_value old_value.txtSQL
- sqlplus column命令用法SQL
- sqlplus選項之column print_noprintSQL
- new_value
- 在oracle sqlplus中通過new_value及define傳遞或定義變數variableOracleSQL變數
- 某Q友問題—為何sqlplus中new_value後續的..到底為何含義SQL
- oracle sqlplus中column格式化命令之heading用法OracleSQL
- oracle10g sqlplus之column格式化查詢結果OracleSQL
- [20120705]sqlplus 的column xxx noprint.txtSQL
- Error: no such columnError
- Column Monitoring
- Index column size too large. The maximum column size is 767 bytesIndex
- Hibernate 註解@Column(nullable = false) 和 @Column(unique=true)NullFalse
- CSS column-spanCSS
- CSS column-gapCSS
- CSS column-ruleCSS
- Oracle Column Group StatisticsOracle
- MAX or MIN of Indexed ColumnIndex
- [PT]Column Histogram StatisticsHistogram
- Index column size too large. The maximum column size is 767 bytes.Index
- CSS column-widthCSS
- Choosing Column Data Type
- 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- [Err] 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- ExtJs的Column佈局JS
- Flutter 之 Row、Column詳解Flutter
- flutter佈局-1-columnFlutter
- MySQL建立表失敗:Index column size too large. The maximum column size is 767 bytesMySqlIndex
- SAP UI5 Form 表單 Column Layout 下的 Column 個數分配問題UIORM
- v$lock之alter table drop column與alter table set unused column區別系列五
- SQLPlusSQL
- MySQL 5.7 到 5.6,出現 Index column size too large. The maximum column size is 767 bytesMySqlIndex
- Flutter之Row/Column用法詳解Flutter
- Flutter基礎元件Row&ColumnFlutter元件
- JPA的Column註解總結
- [LeetCode] Excel Sheet Column NumberLeetCodeExcel
- count(*) 和count(column)之區別
- Postgre 中列出Column情報的命令