One, stored procedures
A: Stored procedure: A stored procedure is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function.
You can use stored procedure names and parameters to invoke stored procedures, which avoids duplication of code and is convenient to use.
Example: The following is a stored procedure named Buyfruit, the parameter is the name of the purchaser, the name of the fruit, the purchase quantity three, the role of this stored procedure is, after entering these three parameters, determine whether the account balance and inventory is sufficient, enough to reduce the balance of the account costs, The inventory is reduced by the number of purchases shown, printing an order, and a detail.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Create PROCEDURE Buyfruit @username varchar (20), @fruitname varchar (20), @buycount int = 0 As BEGIN declare @kc int, @price float, @fruitid varchar (20) --Find out the stock of the fruit first Select @fruitid =ids, @kc = numbers, @price =price from fruit where [email protected] --Purchase according to the relationship between purchase quantity and inventory If @buycount < @kc Begin DECLARE @money decimal (18,2) Select @money = accounts from login where [email protected]--Find account balances based on user name if (@money > @price * @buycount) Begin Update login Set [email protected]* @buycount where [email protected] Update fruit Set numbers = [email protected] where Name[email protected] DECLARE @ordercode varchar (50) Set @ordercode = ' O ' +cast (getdate () as varchar (50)) INSERT into orders values (@ordercode, @username, GETDATE ()) INSERT into OrderDetails values (@ordercode, @fruitid, @buycount) End Else Begin print ' Insufficient balance ' End End Else Begin print ' Insufficient inventory ' End END |
Before purchasing the content in the database:
What is stored in the database after the purchase is successful:
Add to order and purchase details:
Two: Trigger
A trigger is a special kind of stored procedure
Triggers are executed automatically by triggering an event, and stored procedures can be called directly by the stored procedure name
The main function of the trigger is that it can realize the complex referential integrity and data consistency which cannot be guaranteed by the primary key and foreign key, plus the function of enforcing the constraint and cascade running.
About inserted and deleted temporary tables
These two tables are managed by the system, stored in memory, not stored in the database, so the user is not allowed to modify directly, is read-only, the system inserts the data into the inserted temporary table, and then inserted into the table of the database, when the next bar is inserted, this is deleted When performing a delete operation, the data is passed to the deleted table before the data is deleted, which is used to save the temporary data for recovery or record.
The following is a trigger that is triggered when a delete is deleted, and the deleted row is inserted into the Biandong table when you delete the data from the student table.
--Used to delete triggered triggers:
Create Trigger Tr_student_delete
On student
For delete--for trigger after trigger, delete after trigger
As
DECLARE @no varchar (3), @name varchar (4)
Select @no =sno, @name =sname from deleted--uses a temporary table
INSERT into Biandong values (@no, @name, ' 100 ')
Go
--Trigger the above program when performing the deletion below.
Delete from student where sname= ' monkey '
Another is the instead of trigger, when triggered by the program inside the trigger instead of executing the action, that is, executing the trigger inside the thing
In the following example, the original three tables, the other two tables are constrained by the code in the Info table, so it is not possible to delete a row in info separately, and the trigger can delete the line of code P001 in the three tables
Create Trigger Tr_info_delete
On info
Instead of delete--instead of triggers, when deleted replaces execution triggers
As
DECLARE @code varchar (20)
Select @code =code from deleted
Delete from family where [email protected]
Delete from work where [email protected]
Delete from info where [email protected]
Go
Instead of trigger creation complete below start trigger:
Delete from INFO where name = ' Hu June '
P001 rows in three tables are deleted at this time
D. Delete Trigger:
Drop Trigger Tr_info_delete
sql--stored procedure + trigger comparison!