数据库实现拼音缩写

DELIMITER $$

USE `mypos`$$

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

CREATE

    TRIGGER `updatepysx` BEFORE INSERT ON `spb`

    FOR EACH ROW BEGIN

                 SET new.spmcsx=qjp(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=qjp(new.spmc);

        END IF;

    END;

$$

DELIMITER ;

CREATE DEFINER=`root`@`localhost` FUNCTION `qjp`(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 `pjsjk`$$

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 TRIGGER /*!50032 IF EXISTS */ `updatekcsl`$$

CREATE

    TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb`

    FOR EACH ROW BEGIN

                 Udpate kcb set kcsl = kcsl + new.jhsl where spid=new.spid;

    END;

$$

三、缩写存储过程插入进货记录总表和明细表

/*先插入进货总表，取总表的ID，@@IDENTITY; 再依次插入明细表，做成事务，（start transaction;）commit;rollback;*/

DELIMITER $$

/*插入进货表和进货明细表，供应商ID号(gysid)、进货日期(jhrq)、进货金额(jhje)、操作员ID号(czyid)、备注(jhbz)*/

/*进货ID号(jhid)、商品ID号(spid)、进货单价(jhdj)、进货数量(jhsl)*/

CREATE PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))

BEGIN

/* 插入总表，并且将明细表解析*/

        DECLARE @strls varchar(8000);

        DECLARE @jhid INT;

        DECLARE @spid INT;

        DECLARE @jhdj NUMERIC(18,2);

        DECLARE @jhsl NUMERIC(18,2);

SET autocommit = 0;

/** 标记是否出错 */

DECLARE t_error int default 0;

/** 如果出现sql异常，则将t_error设置为1后继续执行后面的操作 */

DECLARE continue handler for sqlexception set t_error=1;

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上传至实验报告二。