操作实验四

-- 实验四：进销存管理系统（2）
-- 一、编写触发器实现：当插入或修改商品名称时，修改名称拼音缩写

DELIMITER $$

USE `mypos`$$

DROP FUNCTION IF EXISTS `qpysx`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `qpysx`(p_name VARCHAR(10)) RETURNS VARCHAR(10) CHARSET utf8
BEGIN
    DECLARE V_RETURN VARCHAR(255);
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10), 
    0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 
    0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
    0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1), 
    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
    IF v_return IS NOT NULL THEN 
        RETURN V_RETURN;
    ELSE
        RETURN p_name;
    END IF ;
    END$$
DELIMITER ;

DELIMITER $$

USE `mypos`$$

DROP FUNCTION IF EXISTS `qxmjp`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `qxmjp`(p_name VARCHAR(100) ) RETURNS VARCHAR(100) CHARSET utf8
BEGIN
    DECLARE V_COMPARE  VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    SET I = 1;
    SET V_RETURN = '';
    WHILE I < LENGTH(P_NAME) DO
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
            SET V_RETURN = CONCAT(V_RETURN, qpysx(V_COMPARE));
        END IF;
        SET I = I + 1;
    END WHILE;
    IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;
    RETURN V_RETURN;
    END$$

DELIMITER ;

DELIMITER $$

USE `mypos`$$

DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx`$$

CREATE
    TRIGGER `updatepysx` BEFORE INSERT ON `spb` 
    FOR EACH ROW BEGIN
        SET new.spmcsx=qxmjp(new.spmc);
    END;
$$

DELIMITER ;

DELIMITER $$

USE `mypos`$$

DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx1`$$

CREATE
    TRIGGER `updatepysx1` BEFORE UPDATE ON `spb` 
    FOR EACH ROW BEGIN
    IF (old.spmc != new.spmc) THEN
        SET new.spmcsx=qxmjp(new.spmc);
    END IF;
    END;
$$

DELIMITER ;

-- 二、编写触发器实现：当插入或删除进货明细表时修改库存表的库存数量。

DELIMITER $$

USE `mypos`$$

DROP TRIGGER /*!50032 IF EXISTS */ `updatekcsl`$$

CREATE
    TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb` 
    FOR EACH ROW BEGIN
        Update kcb set kcsl = kcsl + new.jhsl where spid=new.spid;
    END;
$$

-- 三、缩写存储过程插入进货记录总表和明细表
/*先插入进货总表，取总表的ID，@@IDENTITY; 再依次插入明细表，做成事务，（start transaction;）commit;rollback;*/
/*插入进货表和进货明细表，供应商ID号(gysid)、进货日期(jhrq)、进货金额(jhje)、操作员ID号(czyid)、备注(jhbz)*/
/*进货ID号(jhid)、商品ID号(spid)、进货单价(jhdj)、进货数量(jhsl)*/
/* 插入总表，并且将明细表解析*/
/* 标记是否出错 */ 
/*如果出现sql异常，则将t_error设置为1后继续执行后面的操作 */ 
DELIMITER $$
DROP PROCEDURE if EXISTS `jhjlcp` $$
CREATE PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))
BEGIN
DECLARE t_error int default 0; 
DECLARE continue handler for sqlexception set t_error=1;
SET autocommit = 0;
DECLARE @strls varchar(8000);
DECLARE @jhid INT;
DECLARE @spid INT;
DECLARE @jhdj NUMERIC(18,2);
DECLARE @jhsl NUMERIC(18,2);
START TRANSACTION;
    insert into jhzb(gysid,jhrq,jhje,czyid,jhbz)values(gysid,curdate(),jhje,czyid,jhbz)
    SET @jhid = @@identity;
    WHILE (jhmx>'') DO
        SET @strls = MID(jhmx,1,LOCATE(';',jhmx)-1);
        SET @spid=CAST(MID(@strls,1,LOCATE(',',@strls)-1)  AS SIGNED);
        SET @strls = MID(@strls,LOCATE(',',@strls)+1);
        SET @jhdj=CAST(MID(@strls,1,LOCATE(',',@strls)-1)  AS DECIMAL);
        SET @jhje=CAST(MID(@strls,LOCATE(',',@strls)+1)  AS DECIMAL);
        INSERT INTO jhmxb(jhid,spid,jhdj,jhsl)values(@jhid,@spid,@jhdj,@jhsl);
        SET jhmx = MID(jhmx,LOCATE(';',jhmx)+1);
    END WHILE;
IF t_error=1 THEN
ROLLBACK; -- 事务回滚 
ELSE
COMMIT; -- 事务提交 
END IF; 
END */$$
DELIMITER ;

-- 四、备份数据库的结构和数据，导出SQL文件名为：mypos2.sql，将mypos2.sql上传至实验报告四。