Over the course of the past 12 days, you have analyzed every major subject used to write highly effective concerns to recover information from a database. You have also temporarily researched factors of database design and database security. Today’s purpose is to cover innovative SQL subjects, which include the following:
- Temporary tables
- Stored procedures
- Embedded SQL
The first innovative subject we talk about is the use of short-term platforms, which are simply platforms that exist momentarily within a knowledge base and are instantly decreased when the customer information out or their database relationship finishes. Transact-SQL makes these short-term platforms in the tempdb database. This database is made when you set up SQL Server. Two types of structure are used to make a brief table.
create table #table_name (
Syntax 1 makes a table in the tempdb database. This table is made with a unique name made up of a mixture available name used in the CREATE TABLE control and a date-time seal. A short-term table is available only to its designer. Fifty customers could at the same time problem the following commands:
1> make table #albums (
2> specialist char(30),
3> album_name char(50),
4> media_type int)
The lb sign (#) before the table’s name is the identifier that SQL Server uses to banner a brief table. Each of the 50 customers would basically receive a private table for his or her own use. Each customer could upgrade, place, and eliminate information from this table without concerning about other customers invalidating the table’s information. This table could be decreased as always by providing the following command:
1> fall table #albums
The table could also be decreased instantly when the customer who designed it information out of the SQL Server. If you designed this declaration using some type of highly effective SQL relationship (such as SQL Server’s DB-Library), the table will be removed when that highly effective SQL relationship is shut.
Syntax 2 shows another way to make a brief table on an SQL Server. This structure generates a different result than the structure used in structure 1, so pay consideration to the syntactical variations.
create table tempdb..tablename (
Creating a brief table using the structure of structure 2 still results in a table being designed the tempdb information source. This table’s name has the same structure as the name for the table made out of structure 1. The difference is that this table is not decreased when the customer’s relationship to the information source finishes. Instead, the customer must actually problem a DROP TABLE control to eliminate this table from the tempdb information source.
Examples 13.1 and 13.2 demonstrate the fact that short-term platforms are indeed short-term, using the two variations of structure. Following these two illustrations, Example 13.3 demonstrates a common utilization of short-term tables: to momentarily store information came back from a question. This information can then be used with other concerns.
You need to make a knowledge source to use these illustrations. The information source MUSIC is made with the following tables:
Use the following SQL claims to make these tables:
1> make table ARTISTS (
2> name char(30),
3> homebase char(40),
4> style char(20),
5> artist_id int)
1> make table MEDIA (
2> media_type int,
3> information char(30),
4> price float)
1> make table RECORDINGS (
2> artist_id int,
3> media_type int,
4> headline char(50),
5> year int)
Thus you can join the Oracle dba training to make your career in this field