Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Ttitle BTitle — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Remarks in Scripts with Ttitle and Btitle
The TTITLE (top title) and BTITLE (bottom title) commands have this syntax:
TTITLE option text OFF/ON
where option refers to the placement of the title:
- COLUMN n (start in some column, n)
- SKIP m (skip m blank lines)
- TAB x (tab x positions)
- LEFT/CENTER/RIGHT (default is LEFT)
The same holds for BTITLE.
In addition, page numbers may be added with the extension:
option text format 999 sql.pno
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 "employees department 30" -
> skip 2
SQL>
SQL> btitle col 20 "Confidential" tab 8 -
> "Created by: " SQL.USER
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL>
SQL> select empno, ename, bdate
2 , msal as salary
3 , comm as commission
4 from employees
5 where deptno = 30;
SQL*Plus report Page: 1
OVERVIEW
employees department 30
month
EMPNO last_name BDATE salary COMMISSION
------ -------------------- ---------- --------- ----------
5 Joe 23-08-1969 $1900.00 1400
6 Black 24-07-1970 $2000.00 null
10 Pink 28-03-1974 $2400.00 0
Confidential Created by: sqle
3 rows selected.
SQL>
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> drop table employees;
Table dropped.
SQL>
TTitle ON and OFF
SQL>
SQL> SET PAGESIZE 66
SQL> COLUMN name FORMAT A30
SQL> COLUMN type FORMAT A15
SQL> COLUMN source_size FORMAT 999999
SQL> COLUMN parsed_size FORMAT 999999
SQL> COLUMN code_size FORMAT 999999
SQL> TTITLE "Size of PL/SQL Objects > &&1 KBytes"
Enter value for 1:
SQL> SELECT name, type, source_size, parsed_size, code_size
2 FROM user_object_size
3 WHERE code_size > &&1 * 1000
4 ORDER BY code_size DESC
5 /
old 3: WHERE code_size > &&1 * 1000
new 3: WHERE code_size > * 1000
WHERE code_size > * 1000
*
ERROR at line 3:
ORA-00936: missing expression
SQL>
SQL> TTITLE OFF
SQL>
SQL>
SQL>
Use TTITLE and BTITLE
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name 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 Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME 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
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 Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> SET echo off
SQL> COLUMN salary FORMAT $9,999,999
SQL> COLUMN first_name FORMAT a10
SQL> TTITLE LEFT "Salary Report ##########################"
SQL> SKIP 1
SQL> BTITLE LEFT "End of report **********************" " Page #"
SQL> format 99 sql.pno
SQL> SET linesize 50
SQL> SET pagesize 25
SQL> COLUMN city FORMAT a7
SQL> BREAK ON city skip1 ON report
SQL> REM 2 breaks - one on city, one on report
SQL> COMPUTE sum max min of salary ON city
SQL> COMPUTE sum of salary ON report
SQL> REM a compute for each BREAK
SQL> SET feedback off
SQL> SET verify off
SQL> SELECT * FROM employee
2 ORDER BY city
3 /
Salary Report ##########################
ID FIRST_NAME LAST_NAME START_DAT END_DATE
---- ---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
----------- ------- ---------------
07 David Larry 31-DEC-90 12-FEB-98
$7,898 New Yor Manager
k
06 Linda Green 30-JUL-87 04-JAN-96
$4,323 Tester
----------- *******
$4,323 minimum
$7,898 maximum
$12,221 sum
End of report ********************** Page #
Salary Report ##########################
ID FIRST_NAME LAST_NAME START_DAT END_DATE
---- ---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
----------- ------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06
$1,235 Toronto Programmer
----------- *******
$1,235 minimum
$1,235 maximum
$1,235 sum
End of report ********************** Page #
Salary Report ##########################
ID FIRST_NAME LAST_NAME START_DAT END_DATE
---- ---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
----------- ------- ---------------
05 Robert Black 15-JAN-84 08-AUG-98
$2,335 Vancouv Tester
er
08 James Cat 17-SEP-96 15-APR-02
$1,233 Tester
03 James Smith 12-DEC-78 15-MAR-90
$6,545 Tester
02 Alison Mathews 21-MAR-76 21-FEB-86
$6,662 Tester
04 Celia Rice 24-OCT-82 21-APR-99
$2,345 Manager
----------- *******
End of report ********************** Page #
Salary Report ##########################
ID FIRST_NAME LAST_NAME START_DAT END_DATE
---- ---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
----------- ------- ---------------
$1,233 minimum
$6,662 maximum
$19,119 sum
-----------
sum
$32,574
End of report ********************** Page #
SQL> REM clean up parameters set before the SELECT
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR COLUMNS
columns cleared
SQL> BTITLE OFF
SQL> TTITLE OFF
SQL> SET verify on
SQL> SET feedback on
SQL> SET echo on
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>