--3.1 Tb_gift_item建表,
--Tb_gift_item 表
--字段 说明 类型 长度 空
CREATE TABLE Tb_gift_item
(
[gift_no] int IDENTITY(1, 1),
[package_no] char(10),
[package_name] varchar(20) NOT NULL,
[package_detail] varchar(50) NULL,
[item_short_id] char(5) NOT NULL,
[item_amount] int NULL,
[item_bind] int NULL,
[item_name] varchar(20)NULL,
[item_long_id] varbinary(255) NULL,
[create_date] datetime NULL,
[package_status] int,--1,有效,0,无效 1
[note] varchar(50) NULL
)CREATE UNIQUE INDEX U_INDEX ON Tb_gift_item(package_no)
SET IDENTITY_INSERT Tb_gift_item ON
INSERT INTO Tb_gift_item
(gift_no,package_no,package_name,package_detail,item_short_id,item_amount,item_bind,item_name,item_long_id,create_date,package_status,note)
SELECT
gift_no=1
,package_no='1234567890'
,package_name='AAA'
,package_detail='AAA'
,item_short_id='12345'
,item_amount=1
,item_bind=1
,item_name='AAA'
,item_long_id=CAST('AAA' AS VARBINARY(255))
,create_date=GETDATE()
,package_status=1
,note='AAA'
SET IDENTITY_INSERT Tb_gift_item OFF--以及根据package_no获得长id的存储过程IF OBJECT_ID(N'PROC_1','P') IS NOT NULL
DROP PROCEDURE PROC_1
GO
CREATE PROCEDURE PROC_1
( @package_no CHAR(10)=''
,@result INT OUTPUT
,@item_long_id varbinary(255) OUTPUT
)
AS
IF REPLACE(@package_no,' ','')='' OR LEN(@package_no)!=10
BEGIN
PRINT('EROOR:package_no IS NULL OR package_no LENGTH IS ERROR')
SET @result=-1
RETURN
END
IF NOT EXISTS(SELECT package_no FROM Tb_gift_item WHERE package_no=@package_no AND package_status=1)
BEGIN
PRINT('EROOR:package_no IS NOT EXISTS')
SET @result=-1
RETURN
END
ELSE
BEGIN
SELECT @result=0,@item_long_id=Item_long_id FROM Tb_gift_item WHERE package_no=@package_no AND package_status=1
RETURN
END
GODECLARE @result2 INT,@item_long_id2 varbinary(255)
EXEC PROC_1 @package_no='1234567890 ',@result=@result2 OUTPUT,@item_long_id=@item_long_id2 OUTPUT
SELECT result=@result2,item_long_id=@item_long_id2感谢mysql 牛牛们 以前一直做mssql 郁闷啊救急下
上面是mssql的
转换成mysql
大意就是 @package_no in
@result INT out
@item_long_id outpackage_no 就是传递过来 如果在 Tb_gift_item 存在 在分别 把 result =0 item_long_id=这行记录中 item_long_id 的值 package_no 是UNIQUE INDEX 不会有重复记录如果不存在 就 result =1 item_long_id=null 或者 ‘’之类的DROP PROCEDURE IF EXISTS PROC_1;
DELIMITER //
CREATE PROCEDURE PROC_1
( IN package_no CHAR(10)
,OUT result INT
,OUT item_long_id varbinary(255)
)
BEGIN
SQL STATEMENT
END
//
DELIMITER ;
--Tb_gift_item 表
--字段 说明 类型 长度 空
CREATE TABLE Tb_gift_item
(
[gift_no] int IDENTITY(1, 1),
[package_no] char(10),
[package_name] varchar(20) NOT NULL,
[package_detail] varchar(50) NULL,
[item_short_id] char(5) NOT NULL,
[item_amount] int NULL,
[item_bind] int NULL,
[item_name] varchar(20)NULL,
[item_long_id] varbinary(255) NULL,
[create_date] datetime NULL,
[package_status] int,--1,有效,0,无效 1
[note] varchar(50) NULL
)CREATE UNIQUE INDEX U_INDEX ON Tb_gift_item(package_no)
SET IDENTITY_INSERT Tb_gift_item ON
INSERT INTO Tb_gift_item
(gift_no,package_no,package_name,package_detail,item_short_id,item_amount,item_bind,item_name,item_long_id,create_date,package_status,note)
SELECT
gift_no=1
,package_no='1234567890'
,package_name='AAA'
,package_detail='AAA'
,item_short_id='12345'
,item_amount=1
,item_bind=1
,item_name='AAA'
,item_long_id=CAST('AAA' AS VARBINARY(255))
,create_date=GETDATE()
,package_status=1
,note='AAA'
SET IDENTITY_INSERT Tb_gift_item OFF--以及根据package_no获得长id的存储过程IF OBJECT_ID(N'PROC_1','P') IS NOT NULL
DROP PROCEDURE PROC_1
GO
CREATE PROCEDURE PROC_1
( @package_no CHAR(10)=''
,@result INT OUTPUT
,@item_long_id varbinary(255) OUTPUT
)
AS
IF REPLACE(@package_no,' ','')='' OR LEN(@package_no)!=10
BEGIN
PRINT('EROOR:package_no IS NULL OR package_no LENGTH IS ERROR')
SET @result=-1
RETURN
END
IF NOT EXISTS(SELECT package_no FROM Tb_gift_item WHERE package_no=@package_no AND package_status=1)
BEGIN
PRINT('EROOR:package_no IS NOT EXISTS')
SET @result=-1
RETURN
END
ELSE
BEGIN
SELECT @result=0,@item_long_id=Item_long_id FROM Tb_gift_item WHERE package_no=@package_no AND package_status=1
RETURN
END
GODECLARE @result2 INT,@item_long_id2 varbinary(255)
EXEC PROC_1 @package_no='1234567890 ',@result=@result2 OUTPUT,@item_long_id=@item_long_id2 OUTPUT
SELECT result=@result2,item_long_id=@item_long_id2感谢mysql 牛牛们 以前一直做mssql 郁闷啊救急下
上面是mssql的
转换成mysql
大意就是 @package_no in
@result INT out
@item_long_id outpackage_no 就是传递过来 如果在 Tb_gift_item 存在 在分别 把 result =0 item_long_id=这行记录中 item_long_id 的值 package_no 是UNIQUE INDEX 不会有重复记录如果不存在 就 result =1 item_long_id=null 或者 ‘’之类的DROP PROCEDURE IF EXISTS PROC_1;
DELIMITER //
CREATE PROCEDURE PROC_1
( IN package_no CHAR(10)
,OUT result INT
,OUT item_long_id varbinary(255)
)
BEGIN
SQL STATEMENT
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS PROC_1;
DELIMITER //
CREATE PROCEDURE PROC_1
( IN package_no CHAR(10)
,OUT result INT
,OUT item_long_id varbinary(255)
)
BEGIN
DECLARE ROWCOUNT INT;
SELECT COUNT(*) INTO ROWCOUNT FROM Tb_gift_item WHERE package_no = package_no AND package_status=1;
-- select package_no into package_no2 FROM Tb_gift_item WHERE package_no = package_no AND package_status=1;
IF ROWCOUNT>0 THEN
set result=0;
SELECT result AS result_1;
ELSE
SET result=1;
SELECT result AS result_1;
END IF;
END
//
DELIMITER ;调用出错啊(0 row(s)affected)
(0 ms taken)Error Code : 1318
Incorrect number of arguments for PROCEDURE test2_200904171522.PROC_1; expected 3, got 1
(0 ms taken)
http://ark.hagongda.com/software.asp?id=1727
+------------+----------------+
| package_no | package_status |
+------------+----------------+
| 1234567890 | 1 |
+------------+----------------+
1 row in set (0.00 sec)mysql> DROP PROCEDURE if exists PROC_1;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE PROC_1 (
-> IN package_no1 CHAR(10),
-> OUT result INT ,
-> OUT item_long_id1 varbinary(255)
-> )
-> BEGIN
-> DECLARE ROWCOUNT INT;
-> select item_long_id into item_long_id1 from tb_gift_item where package_n
o = package_no1 AND package_status=1;
-> set result=FOUND_ROWS();
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call PROC_1(1234567890,@a,@b);
Query OK, 0 rows affected (0.00 sec)mysql> select @a,@b\G
*************************** 1. row ***************************
@a: 1
@b: AAA
1 row in set (0.00 sec)mysql> call PROC_1(1234567899,@a,@b);
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @a,@b\G
*************************** 1. row ***************************
@a: 0
@b: NULL
1 row in set (0.00 sec)mysql>
`gift_no` int primary key auto_increment,
`package_no` char(10),
`package_name` varchar(20) NOT NULL,
`package_detail` varchar(50) NULL,
`item_short_id` char(5) NOT NULL,
`item_amount` int NULL,
`item_bind` int NULL,
`item_name` varchar(20) NULL,
`item_long_id` varbinary(255) NULL,
`create_date` datetime NULL,
`package_status` int, -- 1,有效,0,无效1
`note` varchar(50)NULL
);create unique index U_INDEX on Tb_gift_item(package_no);INSERT INTO Tb_gift_item
(gift_no,package_no,package_name,package_detail,item_short_id,item_amount,item_bind,item_name,item_long_id,create_date,package_status,note)
values
(1,'1234567890','AAA','AAA','12345',1,1,'AAA',CAST('AAA' AS BINARY(255)),curdate(),1,'AAA');DROP PROCEDURE if exists PROC_1;delimiter //CREATE PROCEDURE PROC_1 (
IN package_no1 CHAR(10),
OUT result INT ,
OUT item_long_id1 varbinary(255)
)
BEGIN
DECLARE ROWCOUNT INT;
select item_long_id into item_long_id1 from tb_gift_item where package_no = package_no1 AND package_status=1;
set result=FOUND_ROWS();
END;
//
delimiter ;