Oracle offers a variety of objects that can be used to complement the tables in your database. Views allow you to store a SQL query that defines a logical representation of data in one or more tables, allowing you to create a type of virtual table over your physical tables. Synonyms allow you to define an alternate name for almost any object in Oracle. Sequences help create unique integer values in a thread-safe manner. Triggers allow you to define a behavior when a DML statement is executed against one of your tables. Combined with the tables and indexes in your database, you will be well equipped to handle almost any situation that occurs in your database design.
David Berry is a software engineer with over 15 years of experience developing applications in languages such as Java and C#. Throughout his career, he has worked extensively with enterprise database systems including Oracle and SQL Server.
Views Hello. My name is David Berry. Welcome to this module on views in Oracle. In this module, we're going to talk about views. We'll start off by defining what a view is, and then we'll talk about situations when you might want to use a view. We'll then discuss how to create a view and what some of your options are. Next we'll talk about views and DML statements, because this is always a question that people have if they can execute insert, update, and delete statements against their views. Finally we'll wrap up with a discussion about how you might want to make use of views in your database, in your applications. Let's get started.
Synonyms Hello, my name is David Berry. Welcome to this module on Synonyms in Oracle. In this module we're going to start by talking about what a synonym is in Oracle and why you might want to use one. We'll then discuss how you go about defining a synonym in your Oracle database and finally we'll finish up with a couple of demonstrations of how you can define and use synonyms to tie everything together.
Sequences Hello, my name is David Berry. Welcome to this module on sequences in Oracle. In this module, we're going to cover Oracle sequences. We'll start with what a sequence is, and where you would use one. We'll then cover how you can create a sequence in Oracle, the options Oracle gives you, and what these options mean. We'll then talk about how you access the values in a sequence, both if you are in just plain old SQL, or if you're writing an application in a language like C#. Finally, we're going to discuss sequences and transactions, and how even if a transaction rolls back, sequence values are not reused, and the implications of this.
Triggers Hello, my name is David Berry. Welcome to this module on triggers in Oracle. A trigger is a construct in Oracle that allows you to execute an action when a statement is executed in your database. Typically this is a DML statement and that is the scenario we'll spend the most time on. But as we'll see there are some system events that you can also attach a trigger to. This gives you the ability to intercept certain events in your database and perform additional logic when those events occur. For example, you may want to manipulate the values of a DML statement that is running as a certain table or perform some other action, like updating another table and triggers give you the ability to do this. In this module, we'll start out by talking about some of the scenarios that triggers are commonly used in. We'll then continue with a discussion of the different types of triggers that you can create. Next we'll look at the syntax around how you define and manage triggers in your database. We'll then work through a number of examples of triggers to see how they work and put into practice what we've learned. And finally, we'll finish up by discussing compound triggers, which is a type of trigger that allows you to share variable scope across all of the different trigger firing points and can be quite useful in some scenarios.