CREATE tigger tigger_a AFTER UPDATE ON account FOR EACH ROW
SHOW CREATE TRIGGER trigger_a_insert \G;
CREATE TRIGGER tigger_a AFTER UPDATE ON student FOR EACH ROW INSERT INTO stu_history (ACTION,OLD,NEW) VALUES(‘update’,(SELECT COUNT(1) FROM student),(SELECT COUNT(1) FROM student)); CREATE TRIGGER tigger_b AFTER INSERT ON student FOR EACH ROW INSERT INTO stu_history(ACTION,OLD,NEW) VALUES(‘insert’,(SELECT COUNT(1) FROM student)-1,(SELECT COUNT(1) FROM student)); CREATE TRIGGER tigger_c AFTER DELETE ON student FOR EACH ROW INSERT INTO stu_history(ACTION,OLD,NEW) VALUES(‘delete’,(SELECT COUNT(1) FROM student)+1,(SELECT COUNT(1) FROM student)); INSERT INTO student (studentNo,Phone)VALUES(34,234); DELETE FROM student WHERE Phone=234; SELECT * FROM stu_history; CREATE VIEW v_stu_sub_g AS SELECT s.stu_id
DELIMITER @@ CREATE PROCEDURE test() BEGIN IF 1>0 THEN SELECT 1+1; END IF; END @@ DELIMITER ; CALL test();
DELIMITER @@ CREATE PROCEDURE testcase1(IN VALUE INT(11)) BEGIN CASE VALUE WHEN 1 THEN SELECT 1+1; WHEN 2 THEN SELECT 1+2; WHEN 3 THEN SELECT 1+3; ELSE SELECT 1+VALUE; END CASE; END @@ DELIMITER ; CALL testcase1();
DELIMITER @@ CREATE PROCEDURE testwhile1() BEGIN SET @i:=0; SET @sum:=0; WHILE @i<=10 DO SET @sum:@sum+@i; SET @i:=@i+1; END WHILE; END @@ DELIMITER ;
create view v_1 as select moumou from Biao
在account 表中实现购物，可能实现执行报错 逻辑错误情况 DELIMITER @@ CREATE PROCEDURE testBuy(in cust varchar(32), in shop varchar(32),in payedMoner decimal(9,2),out status varchar(2)) declare buyRes int (11); declare soldRes int (11); declare custMoney decimal(9,2); BEGIN set autocommint=0; start transaction; /购买/ update account set cash=cash-payedMoner where name=cust; select row_count() into buyRes; select cash from account where name=cust into custMoney; /售出/ update account set cash=cash+payedMoner wehre name=shop; select row_count() into soldRes; if buyRes>0 && soldRes>0 && custMoney>=0; then commit; set status=0; else rollback; set status=-1; set autocommint=1; end if; END @@ DELIMITER ;
create function testFail() returns varchar(10) begin try delete from v_1 where studentNo=1011; restrn’成功; end try; catch restrn’失败’; end catch
create function testFail() returns varchar(10) begin declare num,sum int (11) default 0; while num <=100 do set num=num+1; set sum =sum +sum ; end while; return sum; end //
