Formatting SQLPlus Reports.docx
- 文档编号:11777890
- 上传时间:2023-06-02
- 格式:DOCX
- 页数:42
- 大小:31.13KB
Formatting SQLPlus Reports.docx
《Formatting SQLPlus Reports.docx》由会员分享,可在线阅读,更多相关《Formatting SQLPlus Reports.docx(42页珍藏版)》请在冰点文库上搜索。
FormattingSQLPlusReports
FormattingSQL*PlusReports
Thischapterexplainshowtoformatyourqueryresultstoproduceafinishedreport.ThischapterdoesnotdiscussHTMLoutput,butcoversthefollowingtopics:
∙FormattingColumns
∙ClarifyingYourReportwithSpacingandSummaryLines
∙DefiningPageandReportTitlesandDimensions
∙StoringandPrintingQueryResults
Readthischapterwhilesittingatyourcomputerandtryouttheexamplesshown.Beforebeginning,makesureyouhaveaccesstotheHRsampleschemadescribedinChapter1,"SQL*PlusOverview".
FormattingColumns
ThroughtheSQL*PlusCOLUMNcommand,youcanchangethecolumnheadingsandreformatthecolumndatainyourqueryresults.
ChangingColumnHeadings
Whendisplayingcolumnheadings,youcaneitherusethedefaultheadingoryoucanchangeitusingtheCOLUMNcommand.ThefollowingsectionsdescribehowdefaultheadingsarederivedandhowtoalterthemusingtheCOLUMNcommand.SeetheCOLUMNcommandformoredetails.
DefaultHeadings
SQL*Plususescolumnorexpressionnamesasdefaultcolumnheadingswhendisplayingqueryresults.Columnnamesareoftenshortandcryptic,however,andexpressionscanbehardtounderstand.
ChangingDefaultHeadings
YoucandefineamoreusefulcolumnheadingwiththeHEADINGclauseoftheCOLUMNcommand,inthefollowingformat:
COLUMNcolumn_nameHEADINGcolumn_heading
Example7-1ChangingaColumnHeading
ToproduceareportfromEMP_DETAILS_VIEWwithnewheadingsspecifiedforLAST_NAME,SALARY,andCOMMISSION_PCT,enterthefollowingcommands:
COLUMNLAST_NAMEHEADING'LASTNAME'
COLUMNSALARYHEADING'MONTHLYSALARY'
COLUMNCOMMISSION_PCTHEADINGCOMMISSION
SELECTLAST_NAME,SALARY,COMMISSION_PCT
FROMEMP_DETAILS_VIEW
WHEREJOB_ID='SA_MAN';
LASTNAMEMONTHLYSALARYCOMMISSION
-------------------------------------------------
Russell14000.4
Partners13500.3
Errazuriz12000.3
Cambrault11000.3
Zlotkey10500.2
Note:
Thenewheadingswillremainineffectuntilyouenterdifferentheadings,reseteachcolumn'sformat,orexitfromSQL*Plus.
Tochangeacolumnheadingtotwoormorewords,enclosethenewheadinginsingleordoublequotationmarkswhenyouentertheCOLUMNcommand.Todisplayacolumnheadingonmorethanoneline,useaverticalbar(|)whereyouwanttobeginanewline.(YoucanuseacharacterotherthanaverticalbarbychangingthesettingoftheHEADSEPvariableoftheSETcommand.SeetheSETcommandformoreinformation.)
Example7-2SplittingaColumnHeading
TogivethecolumnsSALARYandLAST_NAMEtheheadingsMONTHLYSALARYandLASTNAMErespectively,andtosplitthenewheadingsontotwolines,enter
COLUMNSALARYHEADING'MONTHLY|SALARY'
COLUMNLAST_NAMEHEADING'LAST|NAME'
Nowrerunthequerywiththeslash(/)command:
/
LASTMONTHLY
NAMESALARYCOMMISSION
---------------------------------------------
Russell14000.4
Partners13500.3
Errazuriz12000.3
Cambrault11000.3
Zlotkey10500.2
Example7-3SettingtheUnderlineCharacter
Tochangethecharacterusedtounderlineheadingstoanequalsignandrerunthequery,enterthefollowingcommands:
SETUNDERLINE=
/
LASTMONTHLY
NAMESALARYCOMMISSION
=============================================
Russell14000.4
Partners13500.3
Errazuriz12000.3
Cambrault11000.3
Zlotkey10500.2
Nowchangetheunderlinecharacterbacktoadash:
SETUNDERLINE'-'
Note:
Youmustenclosethedashinquotationmarks;otherwise,SQL*Plusinterpretsthedashasahyphenindicatingthatyouwishtocontinuethecommandonanotherline.
FormattingNUMBERColumns
WhendisplayingNUMBERcolumns,youcaneitheraccepttheSQL*PlusdefaultdisplaywidthoryoucanchangeitusingtheCOLUMNcommand.LatersectionsdescribethedefaultdisplayandhowyoucanalteritwiththeCOLUMNcommand.Theformatmodelwillstayineffectuntilyouenteranewone,resetthecolumn'sformatwith
COLUMNcolumn_nameCLEAR
orexitfromSQL*Plus.
DefaultDisplay
ANUMBERcolumn'swidthequalsthewidthoftheheadingorthewidthoftheFORMATplusonespaceforthesign,whicheverisgreater.IfyoudonotexplicitlyuseFORMAT,thenthecolumn'swidthwillalwaysbeatleastthevalueofSETNUMWIDTH.
SQL*Plusnormallydisplaysnumberswithasmanydigitsasarerequiredforaccuracy,uptoastandarddisplaywidthdeterminedbythevalueoftheNUMWIDTHvariableoftheSETcommand(normally10).IfanumberislargerthanthevalueofSETNUMWIDTH,SQL*Plusroundsthenumberupordowntothemaximumnumberofcharactersallowedifpossible,ordisplayshashesifthenumberistoolarge.
YoucanchooseadifferentformatforanyNUMBERcolumnbyusingaformatmodelinaCOLUMNcommand.Aformatmodelisarepresentationofthewayyouwantthenumbersinthecolumntoappear,using9storepresentdigits.
ChangingtheDefaultDisplay
TheCOLUMNcommandidentifiesthecolumnyouwanttoformatandthemodelyouwanttouse,asshown:
COLUMNcolumn_nameFORMATmodel
Useformatmodelstoaddcommas,dollarsigns,anglebrackets(aroundnegativevalues),andleadingzerostonumbersinagivencolumn.Youcanalsoroundthevaluestoagivennumberofdecimalplaces,displayminussignstotherightofnegativevalues(insteadoftotheleft),anddisplayvaluesinexponentialnotation.
Tousemorethanoneformatmodelforasinglecolumn,combinethedesiredmodelsinoneCOLUMNcommand(seeExample7-4).SeeCOLUMNforacompletelistofformatmodelsandfurtherdetails.
Example7-4FormattingaNUMBERColumn
TodisplaySALARYwithadollarsign,acomma,andthenumeralzeroinsteadofablankforanyzerovalues,enterthefollowingcommand:
COLUMNSALARYFORMAT$99,990
Nowrerunthecurrentquery:
/
LASTMONTHLY
NAMESALARYCOMMISSION
-------------------------------------------
Russell$14,000.4
Partners$13,500.3
Errazuriz$12,000.3
Cambrault$11,000.3
Zlotkey$10,500.2
Useazeroinyourformatmodel,asshown,whenyouuseotherformatssuchasadollarsignandwishtodisplayazeroinplaceofablankforzerovalues.
FormattingDatatypes
Whendisplayingdatatypes,youcaneitheraccepttheSQL*PlusdefaultdisplaywidthoryoucanchangeitusingtheCOLUMNcommand.Theformatmodelwillstayineffectuntilyouenteranewone,resetthecolumn'sformatwith
COLUMNcolumn_nameCLEAR
orexitfromSQL*Plus.Datatypes,inthismanual,includethefollowingtypes:
∙CHAR
∙NCHAR
∙VARCHAR2(VARCHAR)
∙NVARCHAR2(NCHARVARYING)
∙DATE
∙LONG
∙CLOB
∙NCLOB
∙XMLType
DefaultDisplay
Thedefaultwidthofdatatypecolumnsisthewidthofthecolumninthedatabase.ThecolumnwidthofaLONG,CLOB,NCLOBorXMLTypedefaultstothevalueofSETLONGCHUNKSIZEorSETLONG,whicheveristhesmaller.
ThedefaultwidthandformatofunformattedDATEcolumnsinSQL*PlusisdeterminedbythedatabaseNLS_DATE_FORMATparameter.Otherwise,thedefaultformatwidthisA9.SeetheFORMATclauseoftheCOLUMNcommandformoreinformationonformattingDATEcolumns.
Leftjustificationisthedefaultfordatatypes.
ChangingtheDefaultDisplay
YoucanchangethedisplayedwidthofadatatypeorDATE,byusingtheCOLUMNcommandwithaformatmodelconsistingoftheletterA(foralphanumeric)followedbyanumberrepresentingthewidthofthecolumnincharacters.
WithintheCOLUMNcommand,identifythecolumnyouwanttoformatandthemodelyouwanttouse:
COLUMNcolumn_nameFORMATmodel
Ifyouspecifyawidthshorterthanthecolumnheading,SQL*Plustruncatestheheading.SeetheCOLUMNcommandformoredetails.
Example7-5FormattingaCharacterColumn
TosetthewidthofthecolumnLAST_NAMEtofourcharactersandrerunthecurrentquery,enter
COLUMNLAST_NAMEFORMATA4
/
LASTMONTHLY
NAMESALARYCOMMISSION
----------------------
Russ$14,000.4
ell
Part$13,500.3
ners
Erra$12,000.3
zuri
z
LASTMONTHLY
NAMESALARYCOMMISSION
----------------------
Camb$11,000.3
raul
t
Zlot$10,500.2
key
IftheWRAPvariableoftheSETcommandissettoON(itsdefaultvalue),theemployeenameswraptothenextlineafterthefourthcharacter,asshowninExample7-5.IfWRAPissettoOFF,thenamesaretruncated(cutoff)afterthefourthcharacter.
ThesystemvariableWRAPcontrolsallcolumns;youcanoverridethesettingofWRAPforagivencolumnthroughtheWRAPPED,WORD_WRAPPED,andTRUNCATEDclausesoftheCOLUMNcommand.SeetheCOLUMNcommandformoreinformationontheseclauses.YouwillusetheWORD_WRAPPEDclauseofCOLUMNlaterinthischapter.
Note:
ThecolumnheadingistruncatedregardlessofthesettingofWRAPoranyCOLUMNcommandclauses.
Nowreturnthecolumntoitspreviousformat:
COLUMNLAST_NAMEFORMATA10
Example7-6FormattinganXMLTypeColumn
BeforeillustratinghowtoformatanXMLTypecolumn,youmustcreateatablewithanXMLTypecolumndefinition,andinsertsomedataintothetable.YoucancreateanXMLTypecolumnlikeanyotheruser-definedcolumn.TocreateatablecontaininganXMLTypecolumn,enter
CREATETABLEwarehouses(
warehouse_idNUMBER(3),
warehouse_specSYS.XMLTYPE,
warehouse_nameVARCHAR2(35),
location_idNUMBER(4));
Toinsertanewrecordcontainingwarehouse_idandwarehouse_specvaluesintothenewwarehousestable,enter
INSERTintowarehouses(warehouse_id,warehouse_spec)
VALUES(100,sys.XMLTYPE.createXML(
'
'));
TosettheXMLTypecolumnwidthto20charactersandthenselecttheXMLTypecolumn,enter
COLUMNBuildingFORMATA20
SELECT
w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal()
"Building"
FROMwarehousesw;
Building
--------------------
Owned
FormoreinformationaboutthecreateXML,extract,t
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Formatting SQLPlus Reports