在 MySql Workbench 中测试存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11525774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
test a stored procedure in MySql Workbench
提问by EB.
I have an Insert stored procedure where I am inserting into 2 tables. The second table using the Last_Insert_ID of the first table. Here is my sproc:
我有一个插入存储过程，我要插入到 2 个表中。第二个表使用第一个表的 Last_Insert_ID。这是我的 sproc：
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `new_user_create`(
IN oFarmName varchar(45),
IN oFirstName varchar(45),
IN oAddress1 varchar(45),
IN oCity varchar(45),
IN oState varchar(45),
IN oZip varchar(45),
IN oCountry varchar(45)
)
BEGIN
insert into intelliair.individual
( FarmName, FirstName)
values ( oFarmName, oFirstName);
insert into intelliair.address
(IndividualID, Address1, City, State, Zip, Country)
Values (Last_Insert_ID(), oAddress1, oCity, oState, oZip, oCountry);
END
Here is how I am testing the query in MySql workbench:
以下是我在 MySql 工作台中测试查询的方式：
call new_user_create(@myFarm, @MyName, @MyAddress, @MyCity, @MyState, @MyZip, @MyCountry)
There error I get is: "Column Address1 cannot be null"
我得到的错误是：“列地址 1 不能为空”
Where am I going wronng? Is it in the sproc? Or the way I am calling it?
我哪里出错了？它在 sproc 中吗？还是我调用它的方式？
回答by Ravinder Reddy
"Column Address1 cannot be null" indicates that the intelliair.address.Address1field must be defined not null.
“列地址 1 不能为空”表示intelliair.address.Address1必须定义该字段not null。
And, I don't think that you pre defined value for @MyAddressbefore passing it to the stored procedure.
Unless defined it is treated as NULLand hence is the error thrown.
而且，我不认为您在将值@MyAddress传递给存储过程之前预先定义了它的值。
除非定义它被视为NULL，因此是抛出的错误。
To cross check values before calling the stored procedure like :
在调用存储过程之前交叉检查值，例如：
select @MyAddress; -- ,@myFarm, @MyName, @MyCity, @MyState, @MyZip, @MyCountry;
Update 1:
更新 1：
You can call stored procedure by directly inputting values for each of the parameters.
Example:
您可以通过直接为每个参数输入值来调用存储过程。
示例：
call new_user_create(
'my Farm value', -- @myFarm
'E B', -- @MyName
'My Address is SO', -- @MyAddress1
'My City is Coders', -- @MyCity
'CA', -- @MyState
'12345', -- @MyZip
'US' -- @MyCountry
);
回答by spencer7593
The exception is being thrown by an INSERT (or UPDATE) statement, that is assigning a NULL value to a column named Address1that is declared to be NOT NULL.
异常由 INSERT（或 UPDATE）语句引发，即将 NULL 值分配给Address1声明为NOT NULL.
The most likely explanation, from what you show, is that the value passed in as the oAddress1 parameter is NULL, and the exception is being thrown by the second INSERT statement.
从您所展示的内容来看，最可能的解释是作为 oAddress1 参数传入的值为 NULL，并且第二个 INSERT 语句引发了异常。
The most likely explanation, therefore, is that when the call to the procedure is made, the @MyAddressuser variable is not assigned a value.
因此，最可能的解释是，当调用过程时，@MyAddress用户变量没有赋值。
(You can verify there is not an Address1column on the intelliair.individual table, or that if there is one, it's not defined as NOT NULL.)
（您可以验证Address1intelliair.individual 表中没有列，或者如果有，则未定义为NOT NULL。）
(There's also a possibility that it's not one of your statements that's throwing the exception, but rather a recursive SQL statement, like an INSERT statement in a BEFORE INSERT FOR EACH ROW trigger.)
（也有可能不是抛出异常的语句之一，而是递归 SQL 语句，例如 BEFORE INSERT FOR EACH ROW 触发器中的 INSERT 语句。）
回答by ASR
USE lportal;
DELIMITER $$
CREATE PROCEDURE `iemp`(IN eid INT(11),IN ename varchar(15),IN dname varchar(15),IN doj DATE)
BEGIN
INSERT INTO e_emp (eid,ename,dname,doj) VALUES (eid,ename,dname,doj);
END
