Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.
The statement will generate an SQL script, but the SQL
that it generates will be incomplete. You need to add a comma between the
privileges CONNECT and DBA:
SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
4. Will the following SQL statement work? Will the generated output work?
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4 /
Yes. The syntax of the main statement is valid, and the SQL that will be
generated will grant CONNECT and DBA to all users selected.
5. True or False: It is best to set feedback on when generating SQL.
False. You do not care how many rows are being selected, as that will not be part
of the syntax of your generated statements.
6. True or False: When generating SQL from SQL, always spool to a list or log file for a record of what happened.
False. You should spool to an .sql file, or whatever your naming convention is
for an SQL file. However, you may choose to spool within your generated file.
7. True or False: Before generating SQL to truncate tables, you should always
make sure you have a good backup of the tables.
True. Just to be safe.
8. What is the ed command?
The ed command takes you into a full screen text editor. ed is very similar to vi on a UNIX system and appears like a Windows Notepad file.
9. What does the spool off command do?
The spool off command closes an open spool file.
Exercise Answers
1. Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statement
that will generate a series of GRANT statements to five new users: John, Kevin,
Ryan, Ron, and Chris. Use the column called USERNAME. Grant them Select access
to history_tbl.
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT SELECT ON HISTORY_TBL TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME IN ('JOHN','KEVIN','RYAN','RON','CHRIS')
4 /
grant select on history_tbl to JOHN;
grant select on history_tbl to KEVIN;
grant select on history_tbl to RYAN;
grant select on history_tbl to RON;
grant select on history_tbl to CHRIS;
2. Using the examples in this chapter as guidelines, create some SQL statements
that will generate SQL that you can use.
There are no wrong answers as long as the syntax is correct in your generated
statements.
WARNING: Until you completely understand the concepts presented in this
chapter, take caution when generating SQL statements that will modify
existing data or database structures.
Day 18, "PL/SQL: An Introduction"
Quiz Answers
1. How is a database trigger used?
A database trigger takes a specified action when data in a specified table is
manipulated. For instance, if you make a change to a table, a trigger could insert
a row of data into a history table to audit the change.
2. Can related procedures be stored together?
Related procedures may be stored together in a package.
3. True or False: Data Manipulation Language can be used in a PL/SQL statement.
True.
4. True or False: Data Definition Language can be used in a PL/SQL statement.
False. DDL cannot be used in a PL/SQL statement. It is not a good idea to automate
the process of making structural changes to a database.
5. Is text output directly a part of the PL/SQL syntax?
Text output is not directly a part of the language of PL/SQL; however, text
output is supported by the standard package DBMS_OUTPUT.
6. List the three major parts of a PL/SQL statement.
DECLARE section, PROCEDURE section, EXCEPTION section.
7. List the commands that are associated with cursor control.
DECLARE, OPEN, FETCH, CLOSE.
Exercise Answers
1. Declare a variable called HourlyPay in which the maximum accepted value is 99.99/hour.
DECLARE
HourlyPay number(4,2);
2. Define a cursor whose content is all the data in the CUSTOMER_TABLE where the CITY is INDIANAPOLIS.
DECLARE
cursor c1 is
select * from customer_table
where city = 'INDIANAPOLIS';
3. Define an exception called UnknownCode.
DECLARE
UnknownCode EXCEPTION;