Oracle PL/SQL/SQL Plus/Title — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Add title to report
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>
bottom title
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.
Set report title
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.