Chapter 6
Understanding the Language of Databases: SQL
In This Chapter
Learning the basics of SQL
Using the data dictionary
Programming with PL/SQL
Communicating with most relational databases is done with Structured Query Language, or just SQL for short. SQL can be used against many types of relational databases, not just Oracle. However, most databases have their own little differences with SQL. As a budding Oracle database administrator (DBA), you should have a good grasp on the SQL language.
SQL can be very simple to learn. In fact, some people say that it’s one of the easiest programming languages. You will see in this chapter that to begin functioning as an Oracle DBA, the basic commands aren’t difficult. With that said, keep in mind that this chapter is meant only as a high-level introduction to the fundamentals of SQL. The SQL language is very large and powerful. And although most of your DBA tasks won’t require you to know the more advanced SQL features, you should continue your learning beyond what this chapter offers.
The second part of this chapter focuses on the data dictionary, which is the key component to understanding how your database is organized and how it runs. All good DBAs have a strong understanding of the data dictionary. Understanding the data dictionary will keep you secure, tune, troubleshoot, and configure your database.
Last, we take a look at the PL/SQL programming language. PL/SQL is SQL with more traditional programming constructs. Some say the PL stands for Procedural Language, and others might say Programming Language. Either way, PL/SQL extends the SQL language to be more powerful. It’s not always a focus of DBAs, but often more a focus for developers. However, as a DBA, understanding at least some PL/SQL will go a long way to making you a successful DBA.
Learning the Basics of SQL
The first step to learning SQL is to understand the different types of commands.
Data Manipulation Language (DML) statements manage data within schema objects.
Data Definition Language (DDL) statements create or alter structures (not data) in the database.
Data Control Language (DCL) statements manage security in the database.
Transaction Control Language (TCL) statements manage transactions in the database.
You can see that SQL can be broken down into different areas. This chapter focuses mostly on DML and TCL within the context of SQL. In Chapter 7, we touch on DDL and DCL.
SQL calling environments
There are many environments in which you can use to execute your SQL statements. The Oracle database is typically delivered with two:
SQL*Plus: Command line
SQL*Developer: A more graphically enhanced environment
So as to not confuse matters any more than we have to, we focus on the SQL*Plus environment. That way, we can direct our attention toward the commands themselves and not the other fancy features that SQL*Developer offers, which can be distracting. After you become familiar with the SQL language, you may decide that exploring a more powerful environment can lead to better productivity.
SQL*Plus comes installed on the database server itself or as part of the Oracle client installation. Again, to keep things simple, we show using the SQL*Plus environment directly on the server. As a DBA, you may find that this is also where a lot of your work will be done.
Follow these steps to connect to your database with SQL*Plus in a UNIX/Linux environment:
1. Open a terminal to your OS as the Oracle software owner.
2. Set your environment with oraenv, as shown in Chapter 4.
3. Type <sqlplus> and press Enter.
4. Type your username and press Enter.
5. Type your password and press Enter.
For the examples in this chapter, we use the provided demo schema, HR. This schema should be installed if you selected it as part of the database creation. Here is an example of what you see as a result of following the above login procedure:
[oracle@orasvr01 admin]$ . oraenv
ORACLE_SID = [dev] ? dev12c
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@orasvr01 admin]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 19:23:27 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: hr
Enter password:
Last Successful login time: Fri Jun 28 2013 18:32:41 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL statement clauses
Your SQL statements can be broken into sections called clauses. Not all statements contain all the clauses. It depends on what you’re trying to do. See Table 6-1 for the basic architecture of the SQL SELECT clauses.
Table 6-1 SELECT Statement Clauses
|
Clause |
Description |
|
SELECT |
Contains the columns and operators to display the data |
|
FROM |
Contains one or more tables from which the data originates |
|
WHERE |
Determines what data will be returned or restricted |
|
GROUP BY |
Groups the data according to certain values |
|
ORDER BY |
Orders the output of the data based on specified columns |
The only mandatory clauses in a statement are SELECT and FROM. The rest are optional, as you will see throughout the chapter as we discuss the other clauses.
Case sensitivity in the database
SQL commands themselves are called key words. For example, some key words are
SELECT
FROM
TABLE
WHERE
CREATE
DELETE
You may notice that we frequently use uppercase when writing key words. You don’t have to, though. As a matter of fact, all SQL commands are case-insensitive. The reason we use uppercase is because typing key words in uppercase is common in the SQL language and makes them a little easier to read.
Additionally, object names aren’t case-sensitive. However, we often use lowercase when typing object names in statements. Again, this is only to help make things easier to read. The fact is all object names and attributes are converted to uppercase when they’re stored in the database.
The only thing that is truly case-sensitive in your database is the data. Whatever you store in the database goes in and comes out with the specified case during the operations themselves. Of course, you can influence this one way or the other, which we will show later when we talk about functions.
Even though object names are case-insensitive, you can technically force them to be case-sensitive by putting the names in double quotes (“ ”). However, that practice is highly discouraged because you could end up with three different objects in the database with essentially the same name: for example, EMP, emp, Emp. That is confusion that no one needs.
Viewing your objects and data with the DESCRIBE and SELECT statements
Perhaps the most common statements you will run as a DBA are the DESCRIBE and SELECT commands. After all, a big part of your job will be researching what is in the database and analyzing the current conditions.
For the examples in this chapter, we start out using objects in the HR (demo) schema. In the next section, we start using the data dictionary.
Say you want to get some information about the jobs in your company.
1. Open a terminal to your OS as the Oracle software owner.
2. Set your environment with oraenv, as shown in Chapter 4.
3. Type <sqlplus> and press Enter.
4. Type <hr> and press Enter.
5. Type <your password> and press Enter.
6. Type <DESCRIBE jobs> and press Enter.
The following output appears:
SQL> DESCRIBE jobs
Name Null? Type
----------------------------- -------- --------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
7. To see the job_id and job_title, type
<SELECT job_id, job_title FROM jobs;>
and press Enter.
You should see the following output:
SQL> select job_id, job_title from jobs;
JOB_ID JOB_TITLE
---------- -----------------------------------
AD_PRES President
AD_VP Administration Vice President
AD_ASST Administration Assistant
FI_MGR Finance Manager
FI_ACCOUNT Accountant
AC_MGR Accounting Manager
AC_ACCOUNT Public Accountant
SA_MAN Sales Manager
SA_REP Sales Representative
PU_MAN Purchasing Manager
PU_CLERK Purchasing Clerk
ST_MAN Stock Manager
ST_CLERK Stock Clerk
SH_CLERK Shipping Clerk
IT_PROG Programmer
MK_MAN Marketing Manager
MK_REP Marketing Representative
HR_REP Human Resources Representative
PR_REP Public Relations Representative
19 rows selected.
The DESCRIBE command allows you to see the table structure. This includes the column names, the data types, and whether the columns are allowed to be empty (null). This information can be very important when constructing various SQL statements. For example, if you were inserting a row, you would need to supply values for job_id and job_title because they are NOT NULL.
The SELECT statement is very simple. Notice that it was typed all on one line. SQL doesn’t really care how you break up statements line by line, as long as you don’t break words in half.
Break up SQL statements by clause. For longer, more complex statements, you may use many line breaks. These breaks can help make statements easier to read.
Here are two SELECT statements and their output:
SQL> select *
2 from jobs
3 where job_title = 'President';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
SQL> select *
2 from jobs
3 where job_title like 'P%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AC_ACCOUNT Public Accountant 4200 9000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
IT_PROG Programmer 4000 10000
PR_REP Public Relations Representative 4500 10500
6 rows selected.
Note that instead of using a list of columns, we use an asterisk (*). That tells the SELECT clause is to return all the columns, as opposed to what we show earlier where we select just two columns.
Notice the use of the WHERE clause. The WHERE clause restricts what data is returned. In this example, we use the WHERE clause in two ways:
As an equality (=): You search for exactly what you want to find.
As a fuzzy search (LIKE): You can use wild cards to complete search terms. Oracle uses the percent sign as a wild card symbol.
The use of the % symbol specifies that we want to select all rows that begin with capital P and then have anything after them. Often, on operating systems, you see an asterisk used as a wild card. That’s not the case inside an SQL statement, though; instead, use a percent sign (%).
Adding to our SELECT statement, you see
SQL> select lower(job_id), upper(job_title) title, max_salary
2 from jobs
3 where job_title like 'P%'
4 and max_salary < 14000
5 order by max_salary ASC;
LOWER(JOB_ TITLE MAX_SALARY
---------- ----------------------------------- ----------
pu_clerk PURCHASING CLERK 5500
ac_account PUBLIC ACCOUNTANT 9000
it_prog PROGRAMMER 10000
pr_rep PUBLIC RELATIONS REPRESENTATIVE 10500
We added some functions to our columns in the SELECT clause. Functions take and input to produce an output: in this case, job_id and the job_title. We used the character functions UPPER and LOWER. Can you guess what they do? In this case, it’s pretty obvious. Oracle has dozens of functions for you to use to act on your data in all kinds of ways. In this case, we demonstrate how it is not necessarily important how your data is stored; you can display it however you want.
Notice the names of the columns for job_id and job_title in the output. job_id seems to be a mix of our function and the column_name. That’s because Oracle automatically uses whatever you type in the SELECT clause for your column heading. On the second column, job_title, we use an “alias” to make the output is a little prettier. An alias comes after the column construct but before the comma. In this example, title is the alias. The alias will always default to uppercase unless you put double quotes (“ ”) around it. You also need to use double quotes if your alias is more than one word. For example
SQL> select upper(job_title) "Job Title"
2 from jobs
3 where job_title like 'P%';
Job Title
-----------------------------------
PRESIDENT
PUBLIC ACCOUNTANT
PURCHASING MANAGER
PURCHASING CLERK
PROGRAMMER
PUBLIC RELATIONS REPRESENTATIVE
The use of the AND statement is a construct of the WHERE clause. The AND statement allows us to use multiple conditions to restrict our data.
Last, the ORDER BY clause sorts the output on the column specified, either numerically or alphabetically, depending on the data type. By default, it sorts in ascending order. We added the ASC (ascending) key word for clarification. We could have used DESC instead to order the results in descending numeric order of max_salary.
Add to your data with the INSERT statement
To add rows to your database, you use the INSERT statement. An INSERT statement acts on one table at a time. The INSERT statement has three clauses, of which one is optional:
INSERT clause
Column clause (optional)
VALUES clause
Here’s how you would insert a new row into the jobs table:
1. For the INSERT clause, type
<INSERT INTO jobs>
and press Enter.
2. For the columns clause, type
<(job_id, job_title)>
and press Enter.
3. For the VALUES clause, type
<VALUES ('TRN_MGR','TRAINING MANAGER'),>
and press Enter.
You see
SQL> INSERT INTO jobs
2 (job_id, job_title)
3 VALUES ('TRN_MGR','TRAINING MANAGER'),
1 row created.
After you add one row to your table, the results appear as follows:
SQL> SELECT *
2 FROM jobs
3 WHERE job_id = 'TRN_MGR';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
TRN_MGR TRAINING MANAGER
The salary columns are empty. Remember when we describe the table earlier? Those columns didn’t have a constraint on them specifying that they cannot be null. Therefore, we left them out as an example. In the next exercise, we show you how to fix that.
Single quotes (‘ ’) must be used around character fields. Anything that contains characters (such as a, b, or c) needs to have single quotes around it if you’re talking about data. Numeric fields can be left without quotes.
Changing data with the UPDATE statement
If you have data which you want to modify, use the UPDATE statement. The UPDATE statement acts on columns. Here are the clauses of the UDPATE statement:
UPDATE clause
SET clause
WHERE clause
The first two clauses are required. Technically, the last clause is optional albeit highly recommended.
Because we “forgot” the salary information in our INSERT statement, here’s how to fix it with an UPDATE statement:
1. Type
<UPDATE jobs>
and press Enter.
2. Type
<SET min_salary = 10000, max_salary = 20000>
and press Enter.
3. Type
<WHERE job_id = 'TRN_MGR';>
and press Enter.
You see
SQL> UPDATE jobs
2 SET min_salary = 10000, max_salary = 20000
3 WHERE job_id = 'TRN_MGR';
1 row updated.
And the results are
SQL> SELECT *
2 FROM jobs
3 WHERE job_id = 'TRN_MGR';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
TRN_MGR TRAINING MANAGER 10000 20000
Always consider using a WHERE clause with an UPDATE statement, or else you update all the rows.
Use your WHERE clause with your UDPATE statement to form a SELECT statement. That way, you can verify that your WHERE clause is acting on the correct data before you run your update. (This would also apply to a DELETE statement.)
If you like what you see, you have to make your changes permanent. Type <COMMIT;> and then press Enter.
You see:
SQL> commit;
Commit complete.
And, your changes cannot be easily undone.
Removing data with the DELETE statement
The last DML-type statement to talk about is the DELETE statement. The DELETE statement allows you to remove rows from tables. DELETE acts on one table at a time. You should also carefully consider using a WHERE clause with your DELETE statement, or else all your rows will be removed.
The DELETE statement has two clauses:
DELETE clause
WHERE clause
Here’s how to remove the last rows we just added to the database for the TRN_MGR job_id:
1. Type
<DELETE FROM jobs>
and press Enter.
2. Type
<WHERE job_id = 'TRN_MGR';>
and press Enter.
You see
SQL> DELETE FROM jobs
2 WHERE job_id = 'TRN_MGR';
1 row deleted.
And the results appear as follows:
SQL> SELECT *
2 FROM jobs
3 WHERE job_id = 'TRN_MGR';
no rows selected
There are no longer any rows in the table for the job_id TRN_MGR. Additionally, all the columns were removed. To remove just one of the values, use an UPDATE statement and set the column to empty (null). DELETE always acts on all columns; it removes rows.
Oops! We did not mean to DELETE the TRN_MGR row! Luckily, we did not COMMIT our change yet. We can easily undo this change with a ROLLBACK statement:
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT *
2 FROM jobs
3 WHERE job_id = 'TRN_MGR';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
TRN_MGR TRAINING MANAGER 10000 20000
As long as you have not issued a COMMIT in your session, you can rollback any changes to the last COMMIT within your session. Also, until you commit your data, no one else in the database can see it.
Leaving data un-committed for long periods of time can cause locking problems in your database. Data that has been changed and not committed holds a lock on the row(s) in question. You should commit your changes as soon as possible.
The last section about DML was very brief; we encourage you to seek out further training on SQL. (Try SQL For Dummies, 8th Edition, by Allen G. Taylor.)
Using the Data Dictionary
Learning the in and out of the data dictionary is perhaps one of the most important things you can do to become a top-notch Oracle DBA. The data dictionary is a collection of tables and views inside the database that hold all the information about the current and past state of the database. Data in the data dictionary is modified only by Oracle itself through the running processes of the database. Oracle records millions of bits of information for which you can use to tune, secure, and troubleshoot the database. It may take years for you to master. Understanding the Oracle data dictionary is one of the measuring sticks between a junior or senior DBA.
The data dictionary can be broken into the categories noted in Table 6-2.
Table 6-2 Categories of Data Dictionary Objects
|
Prefix |
Type |
Description |
|
USER_ |
View |
Objects owned by current user |
|
ALL_ |
View |
All objects to which current user has access |
|
DBA_ |
View |
All objects in the database |
|
V$ |
View |
Dynamic performance view. Populated from memory and control files |
|
GV$ |
View |
Like V$, but, for multiple instances in a cluster environment |
|
X$ |
Table |
Internal tables containing cryptic but often useful data |
Of the dictionary view types listed in Table 6-2, as a DBA, you will spend most of your time reading the DBA_ and V$ views. These views provide the most useful and most easily interpreted data. In fact, users who aren’t DBAs often will not have access to the DBA_ and V$ views. On the other hand, all users have ACCESS to the USER_ and ALL_ views. Keeping the DBA_, V$, and X$ views hidden is an important part of database security. Some information in those views could be used by people to gain access to data in which they are not allowed.
Oracle 12c has thousands of data dictionary views in the database. We can’t give you an exact number because it depends on what options you install and configure. In the database we’re using for demonstration purposes, there are approximately three thousand.
Most, but not all, DBA_ and V$ views have names that are somewhat intuitive. For example, Table 6-3 has a sampling of useful views in the database.
Table 6-3 Useful Data Dictionary Views
|
Name |
Contents |
|
DBA_TABLES |
Information about all tables |
|
DBA_USERS |
Information about all users |
|
DBA_AUDIT_TRAIL |
Information about captured audits |
|
V$DATABASE |
Information about the current database configuration |
|
V$CONTROLFILE |
Information about the current database control files |
Despite the vast amount of data dictionary objects available to you, getting information about them is relatively easy with a little bit of practice. The Oracle documentation is going to be the definitive source of all information, listing the different views and describing the contents of the various columns. You can get the documentation for the view at
http://docs.oracle.com/cd/E16655_01/server.121/e17615/toc.htm
With a little bit of know-how and common sense, you can also get a lot of the information yourself. As we mention earlier, many of the views have names that are self-explanatory. With that information, you can look inside the database to see what views are available. There is actually a view of the views:
SQL> describe dictionary
Name Null? Type
----------------------------- -------- --------------------
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)
You can sometimes find what you are looking for with a little bit of common sense and cleverness. Say you’re looking for information about indexes:
SQL> SELECT table_name
2 FROM dictionary
3 WHERE table_name like 'DBA%INDEX%';
TABLE_NAME
------------------------------------------------------------
DBA_INDEXES
DBA_INDEXTYPES
DBA_INDEXTYPE_ARRAYTYPES
DBA_INDEXTYPE_COMMENTS
DBA_INDEXTYPE_OPERATORS
DBA_PART_INDEXES
DBA_XML_INDEXES
7 rows selected.
As we mention earlier, getting familiar with the data dictionary is paramount. You might hear there’s no need to worry about the data dictionary because all the GUI tools give you the information that you need. The fact is those GUI tools do read from the data dictionary views. However, don’t let that give you a false sense of security. More than once, we’ve seen where the GUI tools have failed or gone offline. If you’re not comfortable navigating the data dictionary by SQL, it could be the end of your DBA job in an emergency.
We would also advise you that despite the availability of the GUI tools, a DBA who is efficient at querying the data dictionary with SQL can often get accurate answers more quickly than someone using a tool like Database Express. As senior DBAs, we both agree that we actually prefer the data dictionary over Enterprise Manager for many of the day-to-day tasks.
Last, if you want very specific reports generated on a schedule, there is no better way than to write your own reports and schedule to run as a script through a scheduler, such as Windows Task Scheduler or UNIX/Linux crontab. Then, after generating the report, the script can send the results out via e-mail. You may like to have reports that are not canned in Enterprise Manager, such as Users with Failed Login’s due to Wrong Password in the Last 24 Hours:
SQL> SELECT USERNAME, USERHOST, TIMESTAMP, ACTION_NAME, RETURNCODE
2 FROM dba_audit_trail
3 WHERE username = 'HR'
4 ORDER BY timestamp;
USERNAME USERHOST TIMESTAMP ACTION_NAME RETURNCODE
-------- --------------- --------- ------------ ----------
HR orasvr01 06-JUN-13 LOGON 0
HR orasvr01 08-JUN-13 LOGON 1017
HR orasvr01 09-JUN-13 LOGOFF 0
HR orasvr01 16-JUN-13 LOGON 0
HR orasvr01 17-JUN-13 LOGON 0
HR orasvr01 17-JUN-13 LOGOFF 0
HR orasvr01 18-JUN-13 LOGOFF 0
HR orasvr01 28-JUN-13 LOGON 0
HR orasvr01 28-JUN-13 LOGON 1017
HR orasvr01 28-JUN-13 LOGOFF 0
10 rows selected.
Or, Tables Created by User HR in the Last 100 Days:
SQL> SELECT object_name, created
2 FROM dba_objects
3 WHERE created > sysdate - 100
4 AND object_type = 'TABLE'
5 AND owner = 'HR';
OBJECT_NAME CREATED
--------------- ---------
REGIONS 09-MAY-13
LOCATIONS 09-MAY-13
JOB_HISTORY 09-MAY-13
JOBS 09-MAY-13
EMPLOYEES 09-MAY-13
DEPARTMENTS 09-MAY-13
COUNTRIES 09-MAY-13
7 rows selected.
Programming with PL/SQL
PL/SQL is an SQL with more powerful programmatic contructs built around your code. For example, PL/SQL offers
Looping control
Variables
If/then constructs
Error handling
Normal SQL really doesn’t have any of this. Normal SQL is good to use in code that acts on specific data in the “now.” It can’t make any data-driven decisions. You have to know what that data is — and how you want it to look.
PL/SQL is also more secure than regular SQL. As it stands, when users execute SQL, they have to have permissions on the underlying objects in which the data lives. However, with PL/SQL, named programs execute with the permissions of the owner. That way, the owner of the data could write a program to manage the data. The owner then gives access to the program to the user, not the underlying objects. For example, say you have a program that pulls a user’s salary history for them to view. You don’t want the user to be able to select on the employee salary table. And without a PL/SQL program, that’s what you’d have to do. You can code it so that when the program runs, the program pulls in the connected user as a variable and collects the salary history for that user only.
PL/SQL is often the primary domain of application developers. As a DBA, however, you should also be familiar with the basic premises of the code and be able to read how the code functions. Even though DBAs may not be application developers, you will be called upon to help troubleshoot code or tune code that may be in the form of PL/SQL programs.
Furthermore, Oracle has provided the DBAs with hundreds of built-in programs written in PL/SQL to facilitate actions in the database. In some cases, these programs will be required for you to do your job. Understanding how PL/SQL functions will help you better understand how to use these built-in features.
Types of PL/SQL programs
PL/SQL programs come in many forms. PL/SQL programs are also sometimes referred to as “program units.” See Table 6-4 for a listing of the common types of PL/SQL constructs you’ll come across.
Table 6-4 Type of PL/SQL Program Units
|
Name |
Description |
|
ANONYMOUS BLOCK |
Un-named program that runs from the command line |
|
PROCEDURE |
Stored, named program that performs a tasks |
|
FUNCTION |
Program that takes input, acts upon it, and produces output |
|
PACKAGE |
Group of named procedures and/or functions which are related by task |
|
TRIGGER |
Program acts upon outcome of some other action; fires automatically |
PL/SQL block structure
PL/SQL programs are built on the block structure. That is, they can be broken down into specific parts of the program based on function. The parts of the PL/SQL block differ slightly based on the type of program unit, but they all have similar characteristics. Here is a breakdown of the parts of a PL/SQL program unit:
Declarative: This section contains the name of the unit (if it’s named) and any variables. The variables are named, typed, and optionally initialized in the section. The program unit would not be named if it is coded as an anonymous block. An anonymous block is used when you are often writing a program for a one-time use.
Body: This is the section that holds the meat of the program. It contains the functionality and the business logic needed to process the variables and data. You will see things like loops and if/then statements in this section.
Exception: This section defines and handles any errors that come up during the processing of the body. If an error is properly handled, often the program can continue running. Or, at the very least, output a meaningful message to the end user. If an error is encountered and is not handled by the exception section, often the program aborts with a default error message.
End: The end section doesn’t contain anything. It just signifies that the program is at the end of its processing. And in a package of many procedures, the end section separates it from the next procedure in the list.
Calling PL/SQL programs
PL/SQL procedures, functions and packages are called in a couple of different ways. You can use the EXECUTE command, or you can call the program as part of another block. Triggers, as described in Table 6-4, on the other hand, are not called from the command line. They automatically execute only after some other process completes. For example, you might want a trigger to fire every time someone updates the salary column of the employees table. Then, perhaps that trigger shoots an e-mail to the HR manager to report the change.
The DESCRIBE command can also work against PL/SQL programs. This can prove helpful if you don’t know the arguments or variables that the procedure may take for input. For example, say you have a procedure that gets the salary for an employee based on first and last name input. The procedure is called get_sal.
SQL> DESCRIBE get_sal
PROCEDURE get_sal
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_LAST_NAME VARCHAR2 IN
P_FIRST_NAME VARCHAR2 IN
The procedure takes to IN arguments of VARCHAR2 type.
Here’s how you would execute the procedure with the EXECUTE command, using the employee Mike Whalen:
SQL> EXECUTE get_sal('Whalen','Mike')
Mike, Whalen - Makes: $8300
PL/SQL procedure successfully completed.
As we mention earlier, Oracle has a plethora of pre-supplied packages, procedures, and functions for managing the database. To get a complete list, go to the following section of the documentation that outlines all the Oracle-supplied program units:
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/toc.htm
Table 6-5 offers some example Oracle-supplied programs.
Table 6-5 Example Oracle-Supplied Programs
|
Name |
Description |
|
DBMS_SCHEDULER |
Manages the internal database scheduler |
|
DBMS_STATS |
Gathers statistics on users, objects, system, and whole database |
|
SYSDATE |
Outputs current time and date of system |
|
UTL_MAIL |
Utility for e-mail with features, such as attachments, Cc, and Bcc |
|
DBMS_METADATA |
Function for pulling object DDL out of database among other tasks |
|
DBMS_DATAPUMP API |
Manages Data Pump within a PL/SQL program |