Oracle PL/SQL/SQL Plus/Title

Материал из SQL эксперт
Перейти к: навигация, поиск

Add title to report

   <source lang="sql">
 

SQL> SQL> -- create demo table SQL> create table emp(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    fname         VARCHAR2(10 BYTE),
 4    lname          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> TTITLE LEFT "" - > RIGHT "Page: " FORMAT 99 SQL.PNO SKIP 2 SQL> BTITLE CENTER "Company Confidential" SQL> SQL> select * from emp;


emp emp Current Numb Name LNAME START_DAT END_DATE Salary CITY


---------- ---------- --------- --------- --------- ----------

DESCRIPTION


01 Jason Martin 25-JUL-96 25-JUL-06 $1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 $6661.78 Vancouver Tester

                                                   Page:   2

emp emp Current Numb Name LNAME START_DAT END_DATE Salary CITY


---------- ---------- --------- --------- --------- ----------

DESCRIPTION


03 James Smith 12-DEC-78 15-MAR-90 $6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 $2344.78 Vancouver

                                                   Page:   3

emp emp Current Numb Name LNAME START_DAT END_DATE Salary CITY


---------- ---------- --------- --------- --------- ----------

DESCRIPTION


Manager 05 Robert Black 15-JAN-84 08-AUG-98 $2334.78 Vancouver Tester

8 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL>


 </source>
   
  


bottom title

   <source lang="sql">
 

SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal        NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> set pagesize 22 SQL> set linesize 80 SQL> SQL> ttitle left "SQL*Plus report" - > right "Page: " format 99 SQL.PNO - > skip center "OVERVIEW" - > skip center "emp department 30" - > skip 2 SQL> SQL> btitle col 20 "Confidential" tab 8 - > "Created by: " SQL.USER SQL> SQL> select empno, ename, bdate

 2  ,      sal         as salary
 3  ,      comm         as commission
 4  from   emp
 5  where  deptno = 30;

Enter... SQL*Plus report

                                   OVERVIEW
                               emp department 30
                                          month
EMPNO        lname         BDATE         salary COMMISSION

-------------------- ---------- --------- ----------
    2 Jack                 20-02-1961  $1600.00    $300.00
    3 Wil                  22-02-1962  $1250.00    $500.00
    5 Mary                 28-09-1956  $1250.00   $1400.00
    6 Black                01-11-1963  $2850.00  [N/A]
   10 Take                 28-09-1968  $1500.00       $.00
   12 Jane                 03-12-1969   $800.00  [N/A]


                  Confidential        Created by: sqle

6 rows selected. SQL> btitle off SQL> btitle btitle OFF and is the following 66 characters: col 20 "Confidential" tab 8 "Created by: " SQL.USER SQL> ttitle off SQL> SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


Set report title

   <source lang="sql">
 

SQL> SQL> SQL> create table emp(

 2           emp_id                integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode                   varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");

1 row created. SQL> SQL> SQL> set echo off SQL> STORE SET settings REPLACE Wrote file settings SQL> -- set page variables SQL> set feedback on SQL> set pagesize 20 SQL> set linesize 100 SQL> -- format columns SQL> column name format a36 heading "emp Name" SQL> column street format a30 heading "Street" SQL> column city format a12 heading "City" truncated SQL> column state format a6 heading "State" SQL> -- add page header and footer SQL> ttitle center "emp REPORT" skip 2 SQL> btitle center "COMPANY CONFIDENTAL" SQL> -- write report to file SQL> spool custrpt SQL> SQL> select firstname||" "|| midinit||"."||" "|| lastname name,

 2         street, city, state
 3    from emp
 4   order by lastname ;
                                            emp REPORT

emp Name Street City State


------------------------------ ------------ ------

Joe J. Jones 1 Ave New York NY Sue J. Smith 1 Street New York NY Peggy J. X 1 Drive New York NY

                                        COMPANY CONFIDENTAL

3 rows selected. SQL> SQL> spool off SQL> clear columns columns cleared SQL> ttitle off SQL> btitle off SQL> @settings SQL> set echo on SQL> SQL> SQL> drop table emp; Table dropped.


 </source>