MSSQL, MYSQL 차이점.

MSSQL
/*Table test_tbl*/
CREATE TABLE [dbo].[test_tbl](
 [IDX] [int] IDENTITY(1,1) NOT NULL,
 [DATA1] [int] NOT NULL,
 [DATA2] [smalldatetime] NOT NULL,
 [DATA3] [nchar](10) NOT NULL,
 [DATA4] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_test_tbl] PRIMARY KEY CLUSTERED
(
 [IDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/*StoredProcedure load_test_data*/
CREATE PROCEDURE [dbo].[load_test_data]
 @InValue int,
 @OutRow int OUTPUT,
 @OutValue1 int OUTPUT,
 @OutValue2 nchar(10) OUTPUT,
 @OutValue3 nvarchar(50) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;

 select top(2) IDX, convert(int,DATA1) as DATA1, DATA2, DATA3, DATA4 from test_tbl
 set @OutRow = @@ROWCOUNT
 select top(1) @OutValue2 = DATA3, @OutValue3 = DATA4 from test_tbl where IDX > 1
 set @OutValue1 = @InValue
 return 33
END


/*StoredProcedure insert_test_data*/
CREATE PROCEDURE [dbo].[insert_test_data]
 @InData1 int,
 @InData2 smalldatetime,
 @InData3 nchar(10),
 @InData4 nvarchar(50)
AS
BEGIN
 SET NOCOUNT ON;

    insert into test_tbl (DATA1, DATA2, DATA3, DATA4) values (@InData1, @InData2, @InData3, @InData4)
    return 35
END


/*Query insert_test_data*/
declare @OutReturn1 int
exec @OutReturn1 = insert_test_data 10, '2014-05-11 13:55:21', 'NCHAR(10)', 'NVARCHAR(50)'
select @OutReturn1


/*Query insert_test_data*/
declare @OutReturn2 int
declare @OutRow int
declare @OutValue1 int
declare @OutValue2 nchar(10)
declare @OutValue3 nvarchar(50)
exec @OutReturn2 = load_test_data 14, @OutRow OUTPUT, @OutValue1 OUTPUT, @OutValue2 OUTPUT,
@OutValue3 OUTPUT
select @OutReturn2, @OutRow, @OutValue1, @OutValue2, @OutValue3




MYSQL
/*Table test_tbl*/
CREATE TABLE `test_tbl` (
 `IDX` INT(11) NOT NULL AUTO_INCREMENT,
 `DATA1` INT(11) NULL DEFAULT '0',
 `DATA2` TIMESTAMP NULL DEFAULT NULL,
 `DATA3` CHAR(10) NULL DEFAULT '0',
 `DATA4` VARCHAR(50) NULL DEFAULT '0',
 PRIMARY KEY (`IDX`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


/*StoredProcedure load_test_data*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `load_test_data`(OUT `OutReturn` INT,
IN `InValue` INT, OUT `OutRow` INT, OUT `OutValue1` INT,
OUT `OutValue2` CHAR(10), OUT `OutValue3` VARCHAR(50))
 LANGUAGE SQL
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT ''
BEGIN
 select IDX, DATA1, DATA2, DATA3, DATA4 from test_tbl limit 2;
 set OutRow = FOUND_ROWS();
 select DATA3, DATA4 into OutValue2, OutValue3 from test_tbl where IDX > 1 limit 1;
 set OutValue1 = InValue;
 set OutReturn = 33;
END


/*StoredProcedure insert_test_data*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test_data`(OUT `OutReturn` INT,
IN `InData1` INT, IN `InData2` TIMESTAMP, IN `InData3` CHAR(10), IN `InData4` VARCHAR(50))
 LANGUAGE SQL
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT ''
BEGIN
 insert into test_tbl (DATA1, DATA2, DATA3, DATA4) values (InData1, InData2, InData3, InData4);
 set OutReturn = 35;
END


/*Query insert_test_data*/
call insert_test_data (@outreturn1, 10, '2014-05-11 13:55:21', 'NCHAR(10)', 'NVARCHAR(50)');
select @outreturn1;


/*Query load_test_data*/
call load_test_data(@outreturn2, 14, @outrow, @outvalue1, @outvalue2, @outvalue3);
select @outreturn2, @outrow, @outvalue1, @outvalue2, @outvalue3;

댓글 없음: