SQL functions, stored procedures, triggers, cascading deletion, and batch data import

Use xsgl go/* custom functions -- scalar functions -- table value functions -- inline Table value functions -- Multi-statement Table value functions during function calling, when defining a function using select print exec, note the following: 1. if a return value exists in the return value of a function, assign the return value to a variable of the same type as the return type, and then return the return value 2. during function execution, DBO must be added. functionname. Otherwise, the system will prompt that the function cannot be found. 3. the function cannot return data such as select print. 4. the Inline Table value function actually returns a table. In its call, it also needs to call select * From functionname5. when the table value function returns a value, insert @ info select ID, name from student where id = @ ID return */select * From deleted create a scalar function create function getname (@ I D INT) returns char (10) as begin declare @ name charselect @ name = Name from student where id = @ ID return @ nameend select DBO. getname (1) Create Function max2 (@ a real, @ B real) returns real as begin declare @ temp realif @ A> @ bset @ temp = @ aelseset @ temp = @ breturn @ tempend -- inline Table value function create function getinfo (@ id int) returns @ info table (ID int, name char (10) as begin insert @ info ------- this sentence is very important, but I don't know what it means to select I D, name from student where id = @ ID return end select * from student select * From DBO. getinfo (1) receive response )------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------/* Stored Procedure 1. there are two ways to run SQL statements. One is to save the source code, and then run the test shell. The other is to use the stored procedure, store compiled SQL statements in the database memory. Execute can be directly used for execution. storage Process classification: 1. system stored procedures: In the master database, start with SP 2. custom stored procedure 3. for regular operations, we can define a stored procedure to avoid data transmission between the client and the service, submission efficiency */exec sp_stored_procedures -- view all stored procedures in the current environment -- create stored procedures for querying Student Information create procedure queryid @ ID int as select * from student where id = @ ID ------- The sequence functions trigger is called only when the table or view is modified. If an event is called, a trigger may be triggered, so that the call trigger that dares to trigger cannot display the call, instead, define a trigger when the table is modified and the trigger is automatically called-DML trigger for insert Delete Update-DDL trigger for create alter drop-cascade Delete When deleting data in the student table, the corresponding data in the exam table is also deleted. For example, the ID of the student exam table is the primary key. In exam, stu_id cau_id is the primary key and stu_id is the foreign key that references the student table. If you want to delete the ID data in the student table, the exam table also has some ID information, the deletion will fail, so a trigger will be used at this time. It can also be called cascading deletion When deleting the ID information in the student table, the trigger that defines the delete event in the student table is triggered, and then the trigger is executed to delete the data in the exam table. The DML trigger is processed relative to an event in a table. (Note: because SRC is still referencing and the data in the student table, the deletion will fail. We need to declare cascading deletion when creating the table :) this event is only executed when the table is defined, the trigger will be executed, but not the execution trigger shown by parameters. Create trigger joindel on studentfor Delete as delete from exam where stu_id = (select stu_id from deleted) drop trigger joindelselect * from student Delete from student where id = 1 select * From studentselect * From examdelete from student where id = 2 select * from student select * from exam -- insert into exam values (100,3, 2) Delete from student where id = 10 ------ cascade Delete (modify) Create Table exam2 (stu_id int not null, cau_id int not null, grade int, constraint PK2 primary key (stu_id, cau_id), constraint fk21 foreign key (stu_id) References student (ID) on Delete cascade, -- declare cascading constraint fk22 foreign key (cau_id) References cause (ID )) -- import data to import data in Table 2 to table 1. select * into talbe2 from Table1 (table2 must be a new table) 2. insert into Table1 select * From Table2 when creating a table and creating a foreign key, add on Delete cascade delete on update cascade modify select * into exam3 -- exam2 must be a new table. If not, the table prompts that the table already exists from examdrop table exam3insert into exam2select * From examselect * From exam2delete from student where id = 3