Experiment 5: trigger and stored procedure (function) - NPU

Experiment 5: triggers and stored procedures (functions)

1. For SPJ_MNG database, create and execute the following stored procedures. (40 points in total)

(1) Create a stored procedure without parameters - jsearch1. The function of this stored procedure is to return all information of Beijing suppliers in the S table when the stored procedure is executed. Call the stored procedure and verify the result. (5 points)

delimiter $$
create procedure jsearch1()
begin
    select * from s
    where city = 'Beijing';
end$$
delimiter ;

(2) Create a stored procedure with input parameters - jsearch2. This stored procedure is used to return all the information of a supplier when the name of the supplier's city (such as Beijing) is entered. Call the stored procedure and verify the result. (5 points)

delimiter $$
create procedure jsearch2(in in_city varchar(45))
begin
    select * from s
    where city = in_city;
end$$
delimiter ;

(3) Create a stored procedure (function) with input and output parameters - jsearch3. This stored procedure is used to return the name of a supplier (output parameter SNAME) when a supplier number (input parameter SNO) is entered. Call the stored procedure and verify the result. (5 points)

delimiter $$
create procedure jsearch3
    (in in_sno varchar(45), out out_sname varchar(45))
begin
    select sname from s
    where sno = in_sno;
end$$
delimiter ;

(4) create a stored procedure jsearch4 using cursors, call the stored procedure and verify the result after creation. Function of the stored procedure: when an engineering number JNO is entered, the names of all suppliers (SNAME) for engineering parts will be returned. These supplier names are spliced into a string and separated by commas', '.

For example: input: J2, output: 'lean, Shengxi, for the people'. (10 points)

delimiter $$
create procedure jsearch4(in in_jno varchar(45))
begin
    #Define variables
    declare result varchar(100) default '';
    declare tmp_name varchar(100);
    declare done int default 0;
    
    #Create cursor
    declare cur_sname cursor for
    select distinct sname from s,spj
    where in_jno = spj.jno
        and spj.sno = s.sno;
    
    #Generate results
    declare continue handler for sqlstate '02000' set done = 1;
    open cur_sname;
    fetch cur_sname into tmp_name;
    repeat
        set result = concat(result, tmp_name);
        set result = concat(result, ', ');
        fetch cur_sname into tmp_name;
    until done
    end repeat;
    close cur_sname;
    
    select left(result, char_length(result)-2);
    
end$$
delimiter ;

(5) View the text information of the stored procedures jsearch1 and jsearch2. (5 points)

Tip: show create procedure jsearch1. To facilitate viewing, add \ G at the end of the above command and display it in two columns of text.

jsearch1:

jsearch2:

(6) View the basic status information of stored procedure jsearch1. (5 points)

Prompt: show procedure status like 'jsearch%';

(7) Delete the jsearch1 stored procedure. (5 points)

drop procedure jsearch1;

2. Create and execute the following triggers for Student database: (40 points in total)

(1) Delete the foreign key constraint on the SC table and create an INSERT named for the SC table_ INSERT trigger for S.

Function of the trigger: when the user inserts a record into the SC table, if the inserted CNO value is not the existing value of CNO in the C table, the user will be prompted that "data not in the C table cannot be inserted" and the insertion of the data will be blocked; If the inserted sno value is not the existing value of sno in the S table, the user will be prompted that "data not in the S table cannot be inserted" and the insertion of the data will be blocked. After the trigger is created successfully, insert a record into the SC table to verify whether the trigger works normally. (5 points)

DELIMITER $$
create trigger insert_s before insert on sc
for each row
begin
    
    if new.sno not in (select sno from s) then
        begin
            #delete from sc where sc.sno = new.sno;
            signal sqlstate 'HY000' set message_text='Cannot insert S Data not in table';
        end;
    end if;
    
    if new.cno not in (select cno from c) then
        begin
            #delete from sc where sc.cno = new.cno;
            signal sqlstate 'HY000' set message_text='Cannot insert C Data not in table';
        end;
    end if;

end $$
DELIMITER ;

Test sno

insert into sc values(2009, 1, 999)

Test cno

insert into sc values(2001, 9, 999)

(2) Create a table named Dele for the s table_ DELETE trigger for S1

The trigger is used to prompt the user that "data in this table cannot be deleted" and prevent the user from deleting data in table S. After the trigger is created successfully, delete the records in table S to verify whether the trigger works normally. (5 points)

DELIMITER $$
create trigger dele_s1 before delete on s
for each row
begin
    signal sqlstate 'HY000'
    set message_text='Data in this table cannot be deleted';
end $$
DELIMITER ;

test

delete from s where sno = '2001';

(3) Create a table named Dele for the s table_ DELETE trigger for S2

This trigger is used to delete the student'S course selection record in SC table when deleting the record in S table. After the trigger is created successfully, delete the records in table S and verify whether the trigger works normally (confirm whether the relevant data of table S and table SC are deleted). (5 points)

DELIMITER $$
create trigger dele_s2 before delete on s
for each row
begin
    delete from sc where sc.sno = old.sno;
end $$
DELIMITER ;

test

delete from s where sno = '2001';

(4) Create a table named UPDATE for the s table_ UPDATE trigger for S

The function of this trigger is to prevent updating the contents of the "sdept" field in the S table (the update is unsuccessful and the prompt "sdept field cannot be updated"). After the trigger is created successfully, update the contents of the "sdept" field in the S table to verify whether the trigger works normally. (5 points)

DELIMITER $$
create trigger update_s before update on s
for each row
begin
    if new.sdept != old.sdept then
        signal sqlstate 'HY000'
        set message_text='Cannot update sdept field';
    end if;
end $$
DELIMITER ;

test

update s set sdept = '1' where sno = '2002';

(5) Delete update_s trigger. (5 points)

drop trigger if exists update_s;

(6) Design a before update trigger and after update trigger, and compare the differences between before and after triggers. (5 points)

before update trigger

drop trigger if exists b_update;

DELIMITER $$
create trigger b_update before update on s
for each row
begin
    if new.sdept != old.sdept then
        signal sqlstate 'HY000'
        set message_text='Cannot update sdept field';
    end if;
end $$
DELIMITER ;

after update trigger

drop trigger if exists b_update;

DELIMITER $$
create trigger b_update before update on s
for each row
begin
    if new.sdept != old.sdept then
        signal sqlstate 'HY000'
        set message_text='Cannot update sdept field';
    end if;
end $$
DELIMITER ;

The difference between before trigger and after trigger: before trigger is executed before update, so the expected function of updating data in cavggrade table cannot be realized; The after trigger is triggered only after the data in the sc table is updated, so it can meet the needs of updating the data in the cavggrade table.

(7) Create a new course grade table CAvgGrade(Cno, Snum, examSNum, avgGrade)

It respectively represents the course number, the number of students who choose the course, the number of people who take the examination, and the average score of the course. The trigger is used to realize the following functions: when someone's score is inserted, deleted or updated in the SC table, the table CAvgGrade is automatically updated. Note that when grade in the SC table is NULL, it indicates that the student has not participated in the exam, and the score does not need to be calculated when calculating the average score, but when grade is 0, that is, when the exam score is 0, the student's score needs to be calculated when calculating the average score. (10 points)

Tip: in MySQL, you need to create three triggers for insert, update and delete actions respectively. You can first design and implement a common stored procedure, and then call the stored procedure in the 3 trigger.

Stored procedure:

DELIMITER $$
CREATE PROCEDURE update_cavggrade(in in_cno int)
BEGIN
    declare tmp_snum int;
    declare tmp_examsnum int;
    declare tmp_avggrade int;
    select count(*) from sc where cno = in_cno
    into tmp_snum;
    select count(*) from sc where cno = in_cno and grade is not null
    into tmp_examsnum;
    select avg(grade) from sc where cno = in_cno and grade is not null
    into tmp_avggrade;
       
    #Add to avggrade
    if in_cno in (select distinct cno from cavggrade) then
        UPDATE cavggrade SET snum = tmp_snum, examsnum = tmp_examsnum, avggrade = tmp_avggrade
        WHERE cno = in_cno;
    else 
        insert into cavggrade values(in_cno,tmp_snum,tmp_examsnum,tmp_avggrade);
    end if;
END$$
DELIMITER ;

After insert trigger:

DELIMITER $$
CREATE TRIGGER sc_AFTER_INSERT AFTER INSERT ON sc FOR EACH ROW
BEGIN
  call update_cavggrade(new.cno);
END$$
DELIMITER ;

After update trigger:

DELIMITER $$
CREATE TRIGGER sc_AFTER_UPDATE AFTER UPDATE ON sc FOR EACH ROW
BEGIN
  call update_cavggrade(new.cno);
  call update_cavggrade(old.cno);
END$$
DELIMITER ;

After delete trigger:

DELIMITER $$
CREATE TRIGGER sc_AFTER_DELETE AFTER DELETE ON sc FOR EACH ROW
BEGIN
  call update_cavggrade(old.cno);
END$$
DELIMITER ;

Data test

INSERT

insert into sc values(2003,1,96);

sc table insert data

CAvgGrade table data update

DELETE

delete from sc where cno = 1;

sc table delete data

CAvgGrade table data update

UPDATE

UPDATE sc SET grade = 6 WHERE cno = 1 and sno = 2003;

sc table modify data

CAvgGrade table data update

3. Create an employee table employee(eID, eName, salary). Assuming that there are 1000 employee data in the table, complete the following requirements. (20 points in total, 10 points for each question)

(1) In order to help this topic automatically generate 1000 employee data, create a user-defined function generateEID that automatically generates employee ID.

The employee ID is required to be an 8-digit number. The first four digits represent the current year of inserting employee data, and the last four digits increase from 0001 to 9999. For example, the first data inserted in 2015 is 20150001, and all 1000 employee IDs are 20150001-20151000 respectively. Call this function to automatically insert 1000 pieces of data. (note that the employee name can be any value when inserting data, and the salary is a number between 2000 and 5000)

DELIMITER $$
CREATE PROCEDURE gnrt_eID(in n int)
BEGIN
    set @x = 0;
    repeat 
        set @x = @x + 1;
        insert into employee values(20150000 + @x,concat('ename',cast(@x as char(10))),floor(2000 + 3000*rand()));
    until @x >= n
    end repeat;
END$$

Execute functions to generate data

(2) The company plans to raise wages for employees according to certain rules. Please use the cursor to create a stored procedure and execute the stored procedure to complete this salary adjustment. The wage growth rules are as follows:

If the salary is less than 3000 yuan, it will increase by 300 yuan per month;

The salary is between 3000-4000 yuan, with a monthly increase of 200 yuan;

If the salary is greater than or equal to 4000 yuan, it will be increased by 50 yuan per month;

DELIMITER $$
CREATE PROCEDURE update_salary()
BEGIN

    declare tmp_eID int;
    declare tmp_salary int;
    
    declare done int default 0;
    declare cur_employee cursor for
        select eID, salary from employee;
    declare continue handler for sqlstate '02000' set done = 1;
    
    open cur_employee;
    fetch cur_employee into tmp_eID, tmp_salary;
    
    repeat
        case
            when tmp_salary < 3000
                then update employee 
                set salary = salary + 300 where eID = tmp_eID;
            when tmp_salary >= 3000 and tmp_salary < 4000
                then update employee 
                set salary = salary + 200 where eID = tmp_eID;
            else
                update employee 
                set salary = salary + 50 where eID = tmp_eID;
        end case;
        fetch cur_employee into tmp_eID, tmp_salary;
    until done
    end repeat;
    
    close cur_employee;
    
END$$

Before execution:

After execution:

Problems and solutions in the experiment

1

During the experiment, because I didn't read the title clearly, I mistakenly thought that I needed to get the s table information of the project in Beijing, resulting in a waste of a lot of time.

2

During the experiment, the comma at the end of the string needs to be deleted, but the function to obtain the length of the string given by the online search method is length (), but the result is wrong. After further query, I found that length () gives the length of bytes, and here I need the length of characters, so I found char_length() function, the result is correct this time.

3

Here I generated an error in the workbench:

After consulting, the third-party tool may cause this problem. It is successfully executed on the command line!

Keywords: Database MySQL

Added by jonaofarc on Mon, 22 Nov 2021 03:15:22 +0200

Programming VIP

Experiment 5: trigger and stored procedure (function) - NPU

Experiment 5: triggers and stored procedures (functions)

1. For SPJ_MNG database, create and execute the following stored procedures. (40 points in total)

(1) Create a stored procedure without parameters - jsearch1. The function of this stored procedure is to return all information of Beijing suppliers in the S table when the stored procedure is executed. Call the stored procedure and verify the result. (5 points)

(2) Create a stored procedure with input parameters - jsearch2. This stored procedure is used to return all the information of a supplier when the name of the supplier's city (such as Beijing) is entered. Call the stored procedure and verify the result. (5 points)

(3) Create a stored procedure (function) with input and output parameters - jsearch3. This stored procedure is used to return the name of a supplier (output parameter SNAME) when a supplier number (input parameter SNO) is entered. Call the stored procedure and verify the result. (5 points)

(5) View the text information of the stored procedures jsearch1 and jsearch2. (5 points)

(6) View the basic status information of stored procedure jsearch1. (5 points)

(7) Delete the jsearch1 stored procedure. (5 points)

2. Create and execute the following triggers for Student database: (40 points in total)

(1) Delete the foreign key constraint on the SC table and create an INSERT named for the SC table_ INSERT trigger for S.

(2) Create a table named Dele for the s table_ DELETE trigger for S1

(3) Create a table named Dele for the s table_ DELETE trigger for S2

(4) Create a table named UPDATE for the s table_ UPDATE trigger for S

(5) Delete update_s trigger. (5 points)

(6) Design a before update trigger and after update trigger, and compare the differences between before and after triggers. (5 points)

(7) Create a new course grade table CAvgGrade(Cno, Snum, examSNum, avgGrade)

Data test

INSERT

DELETE

UPDATE

3. Create an employee table employee(eID, eName, salary). Assuming that there are 1000 employee data in the table, complete the following requirements. (20 points in total, 10 points for each question)

(1) In order to help this topic automatically generate 1000 employee data, create a user-defined function generateEID that automatically generates employee ID.

Problems and solutions in the experiment

1

2

3

Popular Keywords