Transcript lecture7c

Chapter 14 (8i text): SQLPLUS
• Variables in SQL*Plus pp. 306
–
pp. 306-309
–
Simplest way is to precede the variable with &.
SQL*Plus then shows old value, prompts for new.
&& will define and preserve the value for &&.
–
Another way is to define the variable and assign its
value at the same time
define xCompany = ‘IDK’
–
You can prompt the user to enter a value with
ACCEPT/PROMPT:
accept xCompany prompt ’Company:’
CSCI N311: Oracle Database Programming
7-1
Chapter 19 (old text): SQL*PLUS
• Loading variables with a query.
•
You want to generate a query like this
for each table in USER_TABLES
select count(*) from LEDGER;
•
So the query is:
select 'select count (*) from '||
table_name
from USER_TABLES;
•
In a SQLPLUS script:
set feedback off
set heading off
spool slave.sql
select 'select count (*) from '||
table_name
from USER_TABLES;
spool off
start slave.sql
•
Script Output:
BREEDING
LEDGER
LODGING
SKILL
16
225
6
6
CSCI N311: Oracle Database Programming
7-2
Chapter 19 (old text): SQL*PLUS
• Loading/setting variables with a query.
spool slave.sql
select 'define average = ' , AVG(Amount)
from LEDGER;
spool off
• When above query is ran,
here is the output stored in slave.sql file:
define average = 9999.99
• Then after slave.sql has been ran, &average becomes usable as
if it were a constant… example:
select person, amount
from LEDGER
where amount > &average;
CSCI N311: Oracle Database Programming
7-3
Chapter 16 (8i text): SQLPLUS
• Additional Facts About Variables
–
pp. 346-347
–
&1, &2, &3… &n are start parameters.
Example:
start ledger.sql 01-JAN-01 01-JAN-02
Is like having this as the first 2 lines of ledger.sql:
define 1 = '01-JAN-01'
define 2 = '01-JAN-02‘
–
. (period)
ends a variable name so that it can be next to
constants or SQL.
–
set escape
sqlplus command that defines a character you can
place in front of & so they can be literals.
–
set scan on
set scan off
Toggles variable scanning in your script.
CSCI N311: Oracle Database Programming
7-4
Chapter 20: Changing the Oracle Surroundings
•
Know how to create an Index, what indexes do in
general, also unique and bitmap indexes.
•
Know what a Tablespace is, how it differs from a
table and a file.
•
Vocabulary: Extent, Segment.
•
Understand the elements of the CREATE TABLE’s
storage clause. Appendix p. 1194
•
Know what a Cluster is in Oracle and why you
would use them.
•
Know what a Sequence is in Oracle and how to use
them to populate values into a table.
CSCI N311: Oracle Database Programming
7-5