mysql - Mysql 存储函数嵌套查询（在 INSERT 查询中选择）

Question

表

create table category(id int primary key auto_increment, name varchar(30));
insert into category(name) values('Snacks'),('Soft Drink'),('Raw');

create table material(id int primary key auto_increment, name text, catID int references category(id), quantity float, unit text, price float, pur_date date);

create table mStock(name text, catID int, quantity int, unit text);

添加材质的存储函数

CREATE DEFINER=`root`@`localhost` FUNCTION `addMaterial`( nm text, cat text, qty int, un text, pr float) RETURNS int(11)
    DETERMINISTIC
BEGIN

    declare cnt int;

    declare continue handler for 1062
    BEGIN
        return 0;
    END;

    insert into MATERIAL 
            ( name, catID, quantity, unit, price, pur_date) 
     values ( nm, ( select id from CATEGORY where lower(name) = lower(cat) ) , 
              qty, un, pr, curdate() );

    select count(*) into cnt from mSTOCK where lower(name) = lower(nm);

    if( cnt  > 0 )
    then
        update mSTOCK set quantity = quantity + qty where lower(name) = lower(nm);
    else
        insert into mSTOCK values( nm, ( select id from CATEGORY where lower(name) = lower(nm) ), qty, un );
    end if;

    RETURN 1;
END

检查表中是否添加了条目

select * from material;

select * from mStock;

类别 ID 已添加到物料表中，但未添加到 mStock 表中。我也尝试过使用select into查询，但它不起作用。

标签： mysqlstored-functionsmysql-function

Answer 1

在其他情况下注意您的 where 子句：

where lower(name) = lower(nm) 

将其替换为

where lower(name) = lower(cat)