Database system Concept 7-functions, stored procedures, triggers

I. Functions and stored Procedures
A) through functions and stored procedures, the business logic can be saved in the database and called when needed. For example, the maximum number of courses students can repair in a semester, the minimum number of teachers, etc., these judgments have more complex logic, although in the database can also achieve such control, but with functions or stored procedures in the database entrance to check, can be independent from the application, easy to maintain. However, it is not necessarily easy to maintain the business logic by writing it in a stored procedure.
b) The functions defined by the SQL standard are as follows:
Create Function Dept count (dept_name varchar (20))
Returns integer
Begin
declare D_count integer;
Select COUNT (*) into D_count
From instructor
where Instructor.dept_name= Dept_name
return d count;
End
Once the function is well defined, it can be called in a query statement, just like a built-in function:
Select Dept Name, Budget
From instructor
where Dept count (dept name) > 12;
c) The function can also return a table, called a table functions, which is equivalent to a view with parameters
Create function Instructors of (dept_name varchar (20))
Returns table (ID varchar (5), name varchar (), dept_name varchar, salary numeric (8,2))
return table
(select ID, name, Dept_name, salary
From instructor
where instructor.dept_name = Instructor of.dept_name);
Similar functionality can also be used with stored procedures:
CREATE PROCEDURE Dept_count_proc (in Dept_name varchar (a), out D_count integer) begin SELECT COUNT (*) into D_count
From instructor
where Instructor.dept_name= Dept_count proc.dept_name
End
In and out represent the input and output of the data. Stored procedures can also be overloaded.
d) The difference between stored procedures and functions:
A function can only return a single value or a Table object through a return statement. The stored procedure does not allow return, but returns multiple values through the out parameter;
Functions can be embedded in SQL and can be called in a select, while stored procedures do not work;
There are many function limitations, such as the inability to use temporary tables and only table variables. There are also some functions that are not available, and so on. And the limit of the stored procedure is relatively few;
In general, the function of the stored procedure implementation is a little more complicated, and the function implementation is more specific.

II. syntax structure of SQL
A) SQL is also like Java, C and other languages support if, for and other syntax structure, with declare declaration variables, with set assignment, but a section of SQL to write between begin...end, using begin atomic...end words, the internal statement constitutes a transaction.
b) while and repeat
While the Boolean expression do
sequence of statements;
End While

Repeat
sequence of statements;
Until Boolean expression
End Repeat

c) for
Declare n integer default 0;
for R as
Select budget from department where dept name = ' Music '
Do
Set n = n? R.budget
End for

D) if
If Boolean expression
Then statement or compound statement
ElseIf Boolean expression
Then statement or compound statement Else statement or compound statement
End If

third, trigger trigger
A) The trigger contains two elements: the time of the trigger, the action executed after it is triggered.
When the consistency constraint mechanism of the database is not enough to meet the business requirements, the trigger can be used to restrict it, and the requirement of monitoring, alarming and automatization can be realized.
b) Creation of triggers
Create trigger Timeslot_check1 after insert on section
Referencing new row as Nrow
For each row
When (Nrow.time slot_id not in (
Select Time slot_id
From Time_slot))
Begin
Rollback
End
For a trigger that is created when the section table is inserted, referencing new row as Nrow saves the injected row to the Nrow temporary variable, and then uses the for each row to traverse.
In addition to the insert operation, the deleted trigger is written as:
Create trigger Timeslot_check2 after delete on timeslot
Referencing old row as Orow
For each row
When (Orow.time slot_id not in (
Select Time slot_id
From Time_slot)
and Orow.time slot_id in (
Select time slot_id from section) begin
Rollback
End
The temporary save is the old row before the delete, then the update new line, the old line will need:
Create trigger credits_earned After update of takes on (grade)
Referencing new row as Nrow
Referencing old row as Orow
For each row
When ...
Begin Atomic
...
End
Only Takes.grade is triggered when it is updated
c) In addition to using after to define the trigger after the action, you can also use before to trigger before the action occurs, in addition to the row for the trigger (for each row), there are triggers for the table, the corresponding syntax has; refenencing old/new table as, for Each statement
D) Although triggers can be used to solve many problems, it is not recommended to use a trigger if there is an alternative, because a trigger error can only be found at run time, and the association of multiple triggers can cause maintenance difficulties.


Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan?

Database system Concept 7-functions, stored procedures, triggers