Other Database Objects - SQL - The Shortest Route For Beginners (2015)

SQL - The Shortest Route For Beginners (2015)

Chapter 9. Other Database Objects

Database Objects

Besides tables and constraints, each schema in a relational database management system can have several other objects. The following list is a subset of those objects that serve some specific functions, and are supported by all major DBMSs.

· Views

· Indexes

· Stored Procedures

· Triggers

What are views?

A view can be defined as a logical table which represents one or more tables or views. A view is nothing more than a stored query. It contains no data of its own. It derives its data from the tables (called base tables) on which it is based. Base tables can be tables or other views. All operations performed on a view actually affect the base tables.

What are Views good for?

· Views prevent direct access to the tables.

· Views restrict full access to the tables by providing selective columns from selective tables.

· Views simplify queries by presenting complex join results using a simple SELECT statement. For example, you can create a single view which fetches data from multiple tables using joins. This view will hide the fact that the information it is delivering actually resides in multiple tables. You can also add extensive calculations in your view. This way the end users can get their desired information without knowing how to create joins or calculations.

· Views present different data to different user groups.

· Views are extremely beneficial for end users who do not know how to write complicated queries.

· Views can format and present data differently from that stored in the base tables. For example, the columns of a view can be renamed without affecting the tables on which the view is based.

Syntax :

CREATE VIEW {view name} [(column alias, …)]

AS subquery;

NOTE: Some DBMSs do not allow the ORDER BY clause in views.

Oracle and some other DBMSs use CREATE OR REPLACE VIEW command to modify an existing view.

Use DROP VIEW {view name} to remove a view. Dropping views do not affect the tables on which they were based.

Create a View

In the following example a view is created to display department names along with minimum, maximum, and average salaries being paid in each department. After successful execution of the statement, you’ll see a message saying “view VW_DEPT_SALARY created” in the Script Output pane. You can verify this message by expending the Views node under the Connections section.

SQL Statement to create a view :

CREATE VIEW vw_dept_salary (department, minimum, maximum, average)

AS SELECT d.department_name, min(e.salary), max(e.salary), round(avg(e.salary))

FROM departments d, employees e

WHERE d.department_id=e.department_id

GROUP BY d.department_name;

Now that the view is created, you can use it by entering a simple SELECT statement, that follows, in the Worksheet area.

SQL Statement to use a view :

SELECT * FROM vw_dept_salary;

Output :

What are Indexes?

An index is an optional database object which is created on one or more columns of a table. The basic objective of an index is to increase the performance of data retrieval. In the presence of indexes, a database can efficiently locate the requested rows. Indexes are useful when applications often query a specific row or range of rows. Indexes are independent objects which means that you can drop and create indexes with no effect on the tables. In the absence of indexes a full table scan is performed to find a value. For example, without an index, a query of employee number 200 in the Employees table requires the DBMS to search every row in the table for this value. This approach can be feasible for small tables, but is not certainly suitable for large volumes of data.

To elaborate further, let's see an example. You have this book in your hand and want to search all occurrences of the word schema in it. What will you do? Definitely, you will go to the index provided at the back of this book to get all the page numbers where this word is used. Now suppose that the book doesn't have the index, then what? In this situation you will search every line of the book to find this word, starting from the first page. Of course, it is a nightmare, and no one would ever like to perform this exercise. This is why books have indexes to make your search easier. Database indexes are created to serve the same purpose. Once you define an index on one or more columns, your DBMS uses this sorted index to find the location of the required information.

An index can be imagined as a fast access path to reach the desired information. It is created to increase the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. Your DBMS automatically looks after and uses the indexes after their creation, and maintains them automatically by reflecting all data changes (such as insert, update, and delete) without user intervention.

You can create more than one index on a table. However, more indexes on a table also come with a side effect. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes you have associated with a table, the more effort the DBMS must make to update all the indexes after every DML action.

Guidelines :

· Create an index on a column which is used frequently in the WHERE clause or in a join condition. Don’t create indexes on columns that are not often used as a condition in queries.

· Columns that contain wide range of values (for example, first and last names of employees) are good candidate for it.

· Create indexes for large table.

· Provide a unique name to each index.

Create an Index

The following statement creates an index on the first name column in the Employees table to improve the speed of query access. The successful execution of this statement will show index EMP_FIRST_NAME_IX created in the Script Output pane.

NOTE: Use DROP INDEX {index name} to remove an index.

Syntax :

CREATE INDEX {index name} ON {table name} ({column}, [column, …]);

SQL Statement :

CREATE INDEX emp_first_name_ix ON employees(first_name);

Output :

What are Stored Procedures?

In chapter 7 you learned how to enforce data integrity rules using integrity constraints. However, you cannot enforce business rules with these integrity constraints. To enforce complex business rules all DBMSs (except SQLite) offer an object called stored procedure. A stored procedure is a compiled collection of SQL statements, variable declaration, flow control statements, and so on, that you create and store in a database. With the help of stored procedures you can also enforce complex data integrity because they contain predefined instructions that inform the DBMS what action a user can perform. In other words, a stored procedure is a method of data access that users cannot dynamically change. They add additional level of security to a database, because in the presence of a stored procedure, users never require object privileges (for example, SELECT, INSERT, UPDATE, and DELETE) to access database objects directly; they require only the EXECUTE privilege on the stored procedure to manipulate data.

NOTE: Stored procedures are not supported by SQLite and its syntax also varies among DBMSs. The one presented here is from Oracle to help you evaluate it in the current environment.

Syntax :

CREATE PROCEDURE {procedure name} [(parameter1 IN|OUT|IN OUT data type, parameter2 …)] IS

[variable declaration]

BEGIN

execution block

[EXCEPTION

exception block]

END [procedure name];

Where:

· Procedure Name: It is the name of the procedure to be created. Specify OR REPLACE (after the CREATE keyword) to re-create the procedure if it already exists.

· Parameters: Specifies the name of an argument to the procedure. If the procedure does not accept parameters, you can omit the parentheses following the procedure name.

· IN: Specify IN to indicate that you must supply a value for the parameter when calling the procedure.

· OUT: Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.

· IN OUT: Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution. If you omit IN, OUT, and IN OUT, then the argument defaults to IN.

· Datatype: Specify the datatype of the parameter. A parameter can have any datatype defined earlier.

· IS: It is a mandatory clause to declare a procedure.

· Variable Declaration: If the execution block uses some variables, declare them here.

· BEGIN: It’s a keyword which signifies the start of the execution block.

· EXCEPTION: The code in this block is executed when the main execution block fails. It is usually added for error handling.

· END: Marks the end of a procedure.

To understand how stored procedures work, let’s create one to enforce a business rule, which says: When a user deletes an employee’s record from the Employees table, the DBMS should log into an audit trail table the ID and the name of the deleted employee along with the current date and the computer name from where the delete action initiated. Create this stored procedure using the following steps:

Step # 1 Create Audit Trail Table :

Execute the following statement to create a table named Audit Trail, which will be used to store the credentials of the deleted employees.

CREATE TABLE audit_trail (empid number(6), first varchar2(25), last varchar2(25),

deletedon date, deletedby varchar2(25));

Step # 2 Create DeleteEmployee Stored Procedure :

Type and execute the following stored procedure in the Worksheet pane. A message saying PROCEDURE DELETEEMPLOYEE compiled will indicate that the procedure now resides in your database.

CREATE OR REPLACE PROCEDURE DeleteEmployee (empid IN Integer) IS

first varchar2(25);

last varchar2(25);

terminal varchar2(20);

BEGIN

terminal := SYS_CONTEXT('USERENV','HOST');

SELECT first_name, last_name INTO first, last FROM employees WHERE employee_id=empid;

INSERT INTO audit_trail VALUES (empid, first, last, sysdate, terminal);

DELETE FROM employees WHERE employee_id=empid;

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN raise_application_error(-20123,'Invalid Employee ID');

END DeleteEmployee;

The procedure receives one parameter (empid) as an integer value from the calling environment – in our case it will be the EXECUTE command that you’ll see in step # 3. Next, you declared three variables (first, last, and terminal) to hold the name of an employee, and the client machine from where the delete action was executed. We used Oracle’s SYS_CONTEXT function to store the name of the client machine in the variable using an assignment operator (:=). The SELECT statement in the execution block stores the name of the employee in the specified two variables. This is another use of the SELECT statement which is utilized in procedures to store table values in variables for further processing. Then comes the INSERT statement, which adds a record to the audit trail table to maintain deletion history. Note that we used the SYSDATE function to record when the delete action was performed. The DELETE statement on the next line removes the record of the specified employee from the EMPLOYEES table. We also used the COMMIT command to make the changes permanent. The code provided in the EXCEPTION block comes into action when you provide an employee id which doesn’t exist in the table. To see what this block does, pass 9999 as an argument in the EXECUTE command – which is explained next.

NOTE: When you create a stored procedure, it is saved in the schema you are currently connected to (for example, HR in the current scenario). Like other database objects, the user HR owns it, and other users cannot execute it unless they are explicitly granted the EXECUTE privilege on the procedure. Here is how it is granted:

GRANT EXECUTE ON DeleteEmployee to {username};

NOTE: Use the following command to remove a procedure from the database.

DROP PROCEDURE {procedure name};

Step # 3 Execute the Stored Procedure :

To run a stored procedure you type the EXECUTE command, then list the procedure name, followed by the parameter value(s), which are delimited by commas and enclosed in parentheses. Here is its syntax:

Syntax :

EXECUTE {procedure name} (parameter value, parameter value, …);

Run the following command in the Worksheet pane to execute the DeleteEmployee procedure. The EXECUTE command calls the stored procedure by passing a value (employee id) to it. This values goes into the empid parameter (defined as integer in the procedure), which then is used in the SELECT and DELETE statements. After executing this command, you’ll see a message anonymous block completed in the Script Output pane. Inspect the two affected tables (Employees and Audit Trail). You’ll find that the record of employee number 207 is vanished from the Employees table, and a corresponding history record is inserted into the audit table for this employee as shown in the following illustration.

SQL Statement to use a view :

EXECUTE DeleteEmployee(207);

Output :

Stored Procedures Advantages

Here are some of the main advantages to using stored procedures:

· Rather than executing single SQL statements, you can put them in a stored procedure to perform complex operations.

· All users can share the same procedure to execute the same set of instructions for recurring tasks, which not only prevents errors, but also ensures data consistency.

· Stored procedures also simplify the process of change management. You need to only change the stored procedure in case a change occurs in your backend database without other users even knowing about the change.

Database Users, schemas, and Privileges

Files that make up the database are stored on a machine called database server. These files are shared by many users. To keep each user’s data separate and secure, an account is created for each user that is identified by a unique username and password. Each user account owns tables and other data objects within its area of the database, which is called that user’s schema.

Usually a DBMS implements two security levels to protect schema objects. The first level controls access to the database itself. To cross this level, you must enter a username and a password. You did not provide these credentials every time when you connected to the database in SQL Developer, because you instructed the software to save this information in chapter 2. The second security level controls what privileges a user possesses once s/he connects to the database. These privileges are split into two categories: system privileges and object privileges.

System privileges are granted to an individual user to control his operations on the database. For example, connecting to the database or creating a new user or a table fall under this category. New user accounts are created in a database using the following SQL command, where riaz is the username and ahmed is his password.

CREATE USER riaz IDENTIFIED BY ahmed

Object privileges, on the other side, are granted to a user on an individual database object, such as tables. These privileges control how a user can access and manipulate that object. Examples of object privileges on a table include data insertion, deletion, updating, and view.

You can share your schema objects with others by granting object privileges on your schema objects to specified users. For this, you Use the GRANT command (as follows) to grant either system or object privileges:

Grant Syntax: GRANT privilege, privilege, … TO username;

Examples: GRANT CREATE SESSION, CREATE TABLE to riaz;

GRANT SELECT,INSERT,UPDATE,DELETE ON Employees to riaz;

Conversely, you can also withdraw the granted privileges any time using the REVOKE command.

Examples: REVOKE CREATE TABLE FROM riaz;

REVOKE UPDATE,DELETE ON Employees FROM riaz;

Since GRANT and REVOKE commands control access to the database objects, therefore, these two commands fall under the Data Control Language (DCL) category.

NOTE: To remove a trigger from the database use:

DROP TRIGGER {trigger name};

Just like constraints, you can also enable/disable a trigger like this:

ALTER TRIGGER {trigger name ENABLE | DISABLE}

What are Triggers?

The DeleteEmployee stored procedure demonstrated how to use multiple SQL statements to enforce a business rule. If you want to allow users to perform data manipulation actions without a stored procedure, but at the same time also want to automatically enforce the same business rule, then you can use triggers instead.

Database triggers are similar to stored procedures in the sense that they both have the same syntax sections, and both reside in the database. Besides similarities, there are a couple of differences between these two objects. The first difference is that triggers do not accept parameters. They also differ in the way they execute. You already went through a practical example for the stored procedures where you executed it using the EXECUTE command. In contrast, a trigger executes only when a specific event occurs. When a trigger executes, it is said to have fired. DBMS commands (INSERT, UPDATE, and DELETE) can cause triggers to fire. A trigger can fire either before or after its associated SQL command. A BEFORE trigger is often used to grab old values before data manipulation. These values are stored in a table along with new values for auditing purpose. The AFTER trigger is fired after a data manipulation action is performed. For example, you can create an AFTER trigger to update an inventory table after each sale to get current stock position.

Syntax :

CREATE OR REPLACE TRIGGER {trigger name}

[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table name

[FOR EACH ROW]

[DECLARE variable declaration]

BEGIN

trigger action

[EXCEPTION

exception block]

END [trigger name];

Step # 1 Create Audit Trail Table :

The sample trigger that you are going to create in this section will maintain a log of DML operations performed on the Employees table. Execute the following statement to create a table that will keep track of such user actions.

CREATE TABLE audit_employees (empid number(6), actiondate date,

performedby varchar2(25), action char(6));

Step # 2 Create Audit_Employees Trigger :

Type the following code in the Worksheet pane to create the trigger. After clicking the execute button, you’ll see “Enter Binds” dialog box. This box appears to receive values for the provided bind variable (:new and :old). Keep the NULL checkbox provided on this dialog box checked, and click the Apply button to dismiss it. The trigger will be saved in your database with a confirmation message: TRIGGER AUDIT_EMPLOYEES compiled.

The trigger is named AUDIT_EMPLOYEE and it will fire for all three DML operations (INSERT, UPDATE, and DELETE) performed on the Employees table. FOR EACH ROW is specified to designate the trigger as a row trigger. DBMS fires a row trigger once for each row that is affected by the triggering statement. We declared two variables (vDateNow and vTerminal) that are used in the beginning of the trigger action area to store current system date and client computer name. Then we used an IF condition block to evaluate the DML operation. The first one checks whether the user inserted a new record. If so, then the insert statement defined on the next line is fired to record the insert operation in the audit table; the other two statements are ignored. The trigger uses three conditions that target the Employees table. The DBMS fires the trigger when someone issues a statement to perform any of the three DML operations for the Employees table. The trigger is executed immediately after performing the DML action. In case of multiple row manipulations with a single statement the trigger fires once for each record. To reference existing and new column values in a trigger body, you use the syntaxes :OLD.column_name and :NEW.column_name, respectively.

CREATE OR REPLACE TRIGGER Audit_Employees AFTER INSERT OR UPDATE OR DELETE ON Employees FOR EACH ROW

DECLARE

vDateNow DATE;

vTerminal CHAR(20);

BEGIN

-- get current time, and the terminal of the user:

vDateNow := SYSDATE;

vTerminal := SYS_CONTEXT('USERENV','HOST');

IF INSERTING THEN

INSERT INTO Audit_Employees VALUES (:new.employee_id, vDatenow, vTerminal, 'INSERT');

ELSIF DELETING THEN

INSERT INTO Audit_Employees VALUES (:old.employee_id, vDatenow, vTerminal, 'DELETE');

ELSE

INSERT INTO Audit_Employees VALUES (:old.employee_id, vDatenow, vTerminal, 'UPDATE');

END IF;

COMMIT;

END;

Step # 3 Fire Trigger :

Now that the trigger is created, let’s fire it to get the audit trail on the Employees table. Execute the following statements one after the other in the Worksheet area, and watch the two tables – Employees and Audit_Employees.

SQL Statement :

Output :

Text Box: INSERT  INTO  employees  VALUES ( 207, 'Riaz','Ahmed', 'RT', '123.456.7890', '08-DEC-2014', 'IT_PROG', 50000, null, null, 50 );

SQL Statement :

Output :

Text Box: UPDATE  employees  set  salary=30000  WHERE   employee_id=100;

SQL Statement :

Output :

Text Box: DELETE  FROM  employees   WHERE  employee_id=207;

When you execute the first statement above, the DBMS performs two insert operations. First, it adds a complete record to the Employees table with all the values provided in the statement. Then, it logs an entry (as shown in the corresponding output) in the audit table, revealing the action and computer name. Similar log entries are recorded for the update and delete statements.

The example presented in this section is a very simple one to give you a taste of triggers. However, you can create comprehensive triggers that can even keep track of individual updated column values, and can perform various other tasks automatically behind the scenes.

Conclusion

Although the book ends here, your journey to explore the huge world of SQL certainly does not. Besides learning the basics of SQL, you went through some frequently used advanced topics like Joins, Subqueries, Stored Procedures, Indexes, and Triggers in this book.

SQL can be used not only to answer simple questions that you saw throughout this book. Using advanced features of this language you can even perform multidimensional data analysis as well, to answer complex analytical questions. These include ROLLUP, CUBING, RANKING, and WINDOWING to name a few.

You've got the whole world in front of you. Keep your spirits high and move on gradually to become a guru.

Good Luck!

Test Your Skill

1. Which database object is used to prevent direct access to table data?

1. Indexes

2. Views

3. Triggers

4. Tables

2. Which of the following database object is used to increase data retrieval performance?

1. Stored Procedures

2. Triggers

3. Views

4. Indexes

3. By passing parameters, which object do you use to enforce complex business rules?

1. Triggers

2. Views

3. Stored Procedures

4. Constraints

4. What command do you use to invoke a stored procedure?

5. What is the command to fire a trigger?