Programmable Objects

In this chapter, you will learn how to create programmable objects, including how to create and use views, which includes selecting data from views and inserting, updating, and deleting data using views. You will learn how to create and use variables, which includes how to declare and assign values to variables. You will also learn how to create and use stored procedures, including how to use variables and parameters in stored procedures, as well as how to control flow and error handling. Finally, you will learn how to create and use functions, triggers, and temporary tables.

In this chapter, we will cover the following topics:

• Creating and using views
• Creating and using variables
• Creating and using stored procedures
• Creating and using functions
• Creating and using triggers
• Creating and using temporary tables

Let's get started!

...

Technical requirements

You can refer to the code files for this chapter by going to the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-12.

Creating and using views

A view is a stored query. You can select data from a view to return the results of the query. You can also think of a view as a virtual table. A view can be created from one or more tables and can contain all or just some of the rows from one or more tables.

Views can be important for allowing certain users to have access to only specific fields in a table. If you had sensitive data in some of the columns and you didn't want everyone to be able to view that data, then you could provide them with the view instead. Also, you can use a view to make column names more intuitive by using column aliases in the view definition. You could also summarize data in a view to generate reports.

Learning how to create and query a view

...

Creating and using variables

A variable lets you store a single data value that can be used during your session's queries. You can only store a limited set of data types in a variable. These include string, integer, decimal, float, or NULL. If you use a different type in your variable, it will be converted into one of the permitted types listed previously.

Learning how to create and assign values to variables

In order to create and assign a value to a variable, you use the SET statement. There are two variations of assigning a value to a variable:

• SET @varname = value;
• SET @varname := value;

You can also set a variable with a SELECT statement, as shown in the following code sample:

SELECT @varname := column1 FROM...

Creating and using stored procedures

A stored procedure is a set of SQL statements stored in a database. It could be just one SQL statement or many statements. With the help of this, you can reuse certain pieces of code. This can particularly be helpful when you are grouping business logic into a set of queries that will need to be run over and over again.

Creating a stored procedure

Let's learn how to create a stored procedure. First, we'll go through the following syntax, which is used to create a stored procedure:

DELIMITER $$
CREATE PROCEDURE storedprocname()
 BEGIN
 your sql statments go here; 
 END $$
DELIMITER ;

In the previous code sample, we have the following:

• DELIMITER lets MySQL know that there may be lines...

Creating and using functions

You can create user-defined functions in MySQL. These are used to extend the functionality of MySQL, and they work much the same as other built-in functions work. As you may recall, built-in functions are things such as SUM() or AVG().

Understanding the difference between a function and a stored procedure

A function differs from a stored procedure in many ways, as shown in the following table:

Function	Stored procedure
Returns only one mandatory value	Can return zero, one, or multiple values
Doesn't allow transactions	Allow transactions
Can be used in SELECT, WHERE, and HAVING clauses	Can't be used in SELECT, WHERE, and HAVING clauses
Only allows input parameters...

Creating and using triggers

A trigger is a set of actions that run after you insert, update, or delete data. Triggers are created on tables. You can use triggers to enforce business rules for data, audit data, or validate data.

There are a few different types of DML triggers:

• BEFORE INSERT: This causes the trigger to run some logic before you insert data into the table.
• AFTER INSERT: This causes the trigger to run some logic after you insert data into the table.
• BEFORE UPDATE: This causes the trigger to run some logic before you update data in the table.
• AFTER UPDATE: This causes the trigger to run some logic after you update data in the table.
• BEFORE DELETE: This causes the trigger to run some logic before you delete data from the table.
• AFTER DELETE: This causes the trigger to run some logic after you delete data from the table.

Additionally, you can create multiple triggers...

Creating and using temporary tables

Temporary tables allow you to store temporary query results that can be used during a query session. A temporary table can only be used in your current session. No one else can use them except you. These kinds of tables are useful when you have a complicated or long-running query whose results you want to use in a session. This way, you won't have to keep running the same query over and over, but instead, store the results and query that table.

Learning how to create and use a temporary table

To create a temporary table, you can use the same syntax that we used for creating a permanent table, except we will add the TEMPORARY keyword. You can either create a temporary table with a definition...

Summary

In this chapter, you learned how to create programmable objects, including how to create and use views, which included selecting data from views and inserting, updating, and deleting data using views. Additionally, you learned how to alter and drop views. You learned how to create and use variables, which included how to declare and assign values to variables. This also included how to use variables in a query.

Then, you learned how to create and use stored procedures, including how to alter and drop stored procedures. This included learning how to use variables and parameters in stored procedures, as well as how to control flow in stored procedures using IF, CASE, LOOP, REPEAT, and WHILE. Finally, you learned how to handle errors in stored procedures.

After that, you learned how to create and use functions, including how to alter and drop functions. You learned how to...

Questions

1. What is a view?
2. Can you update, insert, and delete via a view?
3. What is a variable?
4. How do you set the value of a variable?
5. What is a stored procedure?
6. What is the difference between a parameter and a variable?
7. What types of flow control statements are available?
8. What is a function?
9. What is a trigger?
10. What is a temporary table?

Further reading

• For more information on MySQL error codes, visit https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html.
• For more information on MySQL SQL states in error handling, visit https://dev.mysql.com/doc/refman/5.6/en/declare-handler.html.
• For more information on Oracle flow control statements, visit https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/controlstatements.htm.
• For more information on PostgreSQL flow control statements, visit https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS.
• For more information on Oracle error handling, visit https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm#TTPLS195.
• For more information on PostgreSQL error handling, visit https://www.postgresql.org/docs/11/plpgsql-errors-and-messages.html.
• For more information on SQL Server TRY CATCH statements,...