The power of SQL scripting
SQL scripts are sets of commands saved into a file. In the beginning of my SQL class, we had to run some scripts to create a guitar shop database. This is just one example of a script you can create in SQL. When working with views recently, I found out how to automatically generate a script to print the results of the view. If there’s a set of SQL commands you find yourself running several times, what does a programmer do? Automate it!
A stored procedure is pretty similar. It’s a set of commands stored with a certain name, that can then be shared with applications. This isn’t just a script that someone can run, but instead a script that is stored in a form that can be accessed by applications. The key thing to this is that is allows access to the data to be controlled. Data may be entered or changed, but the procedure itself cannot be altered. If there’s a database that might have to keep track of IPs visiting the site, it can call on a stored procedure to automatically do this. The IP will be all that it touches, and all that can be accessed through it.
SQL functions are kind of like methods I've used in programming. They already have the logic written out, you know what to expect of them, and you just pass the data in. If you need to add numbers together, you use SUM. Need to get the smallest value? Use MIN. I’ve heard some complaints about SQL functions at work. Some folks think that those operations should be handled on the application end. It’s less work on the database, and supposedly functions aren’t known for their efficiency.
Finally, triggers are sometimes referred to as a special kind of stored procedure. They are a set of statements that are attached to a table. The key difference is that they only execute when an INSERT, UPDATE, or DELETE statement occurs. You then have a set of commands that execute when those triggers happen. For example, let’s say you have a table of employees. When an employee leaves the company, they may be deleted from the table. You may want to still hang on to a log of employees at the company though. On a DELETE statement, you could tell the database to take that row, and insert it into another table that contains only employees that no longer work at the company.
A stored procedure is pretty similar. It’s a set of commands stored with a certain name, that can then be shared with applications. This isn’t just a script that someone can run, but instead a script that is stored in a form that can be accessed by applications. The key thing to this is that is allows access to the data to be controlled. Data may be entered or changed, but the procedure itself cannot be altered. If there’s a database that might have to keep track of IPs visiting the site, it can call on a stored procedure to automatically do this. The IP will be all that it touches, and all that can be accessed through it.
SQL functions are kind of like methods I've used in programming. They already have the logic written out, you know what to expect of them, and you just pass the data in. If you need to add numbers together, you use SUM. Need to get the smallest value? Use MIN. I’ve heard some complaints about SQL functions at work. Some folks think that those operations should be handled on the application end. It’s less work on the database, and supposedly functions aren’t known for their efficiency.
Finally, triggers are sometimes referred to as a special kind of stored procedure. They are a set of statements that are attached to a table. The key difference is that they only execute when an INSERT, UPDATE, or DELETE statement occurs. You then have a set of commands that execute when those triggers happen. For example, let’s say you have a table of employees. When an employee leaves the company, they may be deleted from the table. You may want to still hang on to a log of employees at the company though. On a DELETE statement, you could tell the database to take that row, and insert it into another table that contains only employees that no longer work at the company.
Comments
Post a Comment