在T-SQL的存储过程中最后一句return var;这个是什么意思转换成MYySQL 中用什么来替换。谢谢
create PROCEDURE RandomGroup_Phone(...)/*差数不写了*/
.../*前面还有的定义*/
DECLARE @GroupState TINYINT.../*中间的逻辑部分*/RETURN @GroupState不知道在MySQL的存储过程中能有替代这句return语句没有。再次麻烦大家了!!!
create PROCEDURE RandomGroup_Phone(...)/*差数不写了*/
.../*前面还有的定义*/
DECLARE @GroupState TINYINT.../*中间的逻辑部分*/RETURN @GroupState不知道在MySQL的存储过程中能有替代这句return语句没有。再次麻烦大家了!!!
....
在调用后
SELECT DD
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
用UDF函数
CREATE FUNCTION
RETURNS....
RETURN ...
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)楼主是要这个么?
CREATE PROCEDURE SP_MCTMS_RandomGroup_Phone
(/*-1:出错
1:配对成功
0,2,3,4:项目不允许配对时的状态
5:项目不允许配对,配对已完成
9:查询到符合条件的病员的配对信息,@Name为所有符合条件病员姓名的字符连接
*/
@ProjNum VARCHAR(20),
@Location VARCHAR(20),
@Name CHAR(120) OUTPUT,
@Sex CHAR(1),
@Birthday SMALLDATETIME,
@GroupTime SMALLDATETIME,
@TestSequ VARCHAR(10) OUTPUT,
@DrugSequ VARCHAR(10) OUTPUT,
@GroupNum VARCHAR(1) OUTPUT,
@Re VARCHAR(8) OUTPUT
)
AS
DECLARE @GroupState TINYINT,@PatientID UNIQUEIDENTIFIER
/*获取项目配对状态*/
SET @GroupState=(SELECT GroupState FROM ProjectInformation WHERE ProjectNumber=@ProjNum)
IF @GroupState=1 /*项目允许配对*/
BEGIN
IF LEN(@Name)=4 /*强制插入新的病员记录*/
GOTO GroupLable
ELSE /*常规处理*/
BEGIN
/*检查病员是否已注册*/
IF EXISTS (SELECT ProjectNumber FROM GroupMatch WHERE ProjectNumber=@ProjNum AND PatientSex=@Sex AND PatientBirthday=@Birthday AND Location=@Location)
BEGIN
SELECT @Name=ISNULL(PatientName,' ')+','+@Name FROM GroupMatch WHERE ProjectNumber=@ProjNum AND PatientSex=@Sex AND PatientBirthday=@Birthday AND Location=@Location
RETURN 9
END
END
GroupLable:
DECLARE @GroupedCount INT,@CaseLoad INT
SELECT @CaseLoad=CaseLoad FROM ProjectInformation WHERE ProjectNumber=@ProjNum
SELECT @GroupedCount=SUM(GroupedCount) FROM GroupCondition WHERE ProjectNumber=@ProjNum
IF @GroupedCount=@CaseLoad /*配对数达到总病历数*/
BEGIN
SET @GroupState=5
RETURN @GroupState
END
ELSE /*为该病员分组配对*/
BEGIN
/*获取分组规则*/
DECLARE @SqlString VARCHAR(300)
DECLARE @ReLen TINYINT
SET @ReLen=LEN(@Re)
SET @SqlString='select RuleContent from GroupRule where Projectnumber='''+@ProjNum+''' and RelateGroup=1'
IF @ReLen>0 /*需要配对处理配对规则*/
BEGIN
SET @SqlString=@SqlString+' and ('
WHILE @ReLen>0
BEGIN
SET @SqlString=@SqlString+'(RuleSequence='+LTRIM(RTRIM(STR(@ReLen)))+' and Code='+SUBSTRING(@Re,@ReLen,1)+') or '
SET @ReLen=@ReLen-1
END
SET @SqlString=LEFT(@SqlString,LEN(@SqlString)-3)
SET @SqlString=@SqlString+')'
EXEC('DECLARE Cur_Rule CURSOR FOR '+ @SqlString)
/*计算分组规则交集*/
OPEN Cur_Rule
DECLARE @Rule VARCHAR(10)
FETCH Cur_Rule INTO @Rule
DECLARE @RuleCol VARCHAR(26)
SET @RuleCol=NULL
WHILE @@FETCH_STATUS=0
BEGIN
SET @RuleCol=dbo.MCTMS_RuleIntersection(@RuleCol,@Rule)
FETCH Cur_Rule INTO @Rule
END
close Cur_Rule
deallocate Cur_Rule
/*设置符合分组规则的分组条件查询语句*/
SET @SqlString='select GroupName,CaseLoad,GroupedCount into GroupCondTemp from GroupCondition where ProjectNumber='''+@ProjNum+''''
DECLARE @RuleLen TINYINT
SET @RuleLen=LEN(@RuleCol)
IF @RuleLen>0
BEGIN
SET @SqlString=@SqlString+' and GroupName in('
WHILE @RuleLen>0
BEGIN
SET @SqlString=@SqlString+''''+SUBSTRING(@RuleCol,@RuleLen,1)+''','
SET @RuleLen=@RuleLen-1
END
SET @SqlString=LEFT(@SqlString,LEN(@SqlString)-1)
SET @SqlString=@SqlString+')'
END
END
ELSE /*不需要处理分组规则*/
SET @SqlString='select GroupName,CaseLoad,GroupedCount into GroupCondTemp from GroupCondition where ProjectNumber='''+@ProjNum+''''
BEGIN TRAN
IF EXISTS (SELECT * FROM dbo.sysobjects where id=object_id(N'GroupCondTemp') and objectproperty(id,N'IsUserTable')=1)
DROP TABLE GroupCondTemp
EXEC(@SqlString) /*获取分组条件*/
IF NOT EXISTS (SELECT GroupName FROM GroupCondTemp) /*没有该项目的分组条件,返回配对状态为未开始*/
BEGIN
SET @GroupState=0
RETURN @GroupState
END
/*分配试验顺序号*/
DECLARE @ZeroCount TINYINT
SET @TestSequ=RTRIM(LTRIM(STR(@GroupedCount+1)))
SET @ZeroCount=LEN(RTRIM(LTRIM(STR(@CaseLoad))))-LEN(@TestSequ)
WHILE @ZeroCount>0
BEGIN
SET @TestSequ='0'+@TestSequ
SET @ZeroCount=@ZeroCount-1
END
/*分配入组号*/
DECLARE @GroupName VARCHAR(1),@GroupCaseLoad INT,@GroupGroupedCount INT,@Grouped bit,@Rand INT,@Up INT,@Down INT
DECLARE Cur_Cond CURSOR FOR SELECT * FROM GroupCondTemp ORDER BY GroupName
SELECT @CaseLoad=SUM(CaseLoad) FROM GroupCondTemp
SET @Grouped=0
WHILE @Grouped=0
BEGIN
SET @Rand=CONVERT(INT,Rand()*@CaseLoad)
SET @Up=0
SET @Down=0
OPEN Cur_Cond
FETCH Cur_Cond INTO @GroupName,@GroupCaseLoad,@GroupGroupedCount
WHILE @@FETCH_STATUS=0
BEGIN
SET @Down=@Up
SET @Up=@Up+@GroupCaseLoad
IF @Rand>=@Down AND @Rand<@Up
BEGIN
IF @GroupCaseLoad>@GroupGroupedCount
BEGIN
SET @GroupNum=@GroupName
SET @Grouped=1
END
BREAK
END
FETCH Cur_Cond INTO @GroupName,@GroupCaseLoad,@GroupGroupedCount
END
CLOSE Cur_Cond
END
DEALLOCATE Cur_Cond
DROP TABLE GroupCondTemp
/*分配药物包装号*/
IF EXISTS (SELECT DrugSequence FROM DrugSequence WHERE ProjectNumber=@ProjNum AND Location=@Location AND DrugStatus=0)
BEGIN
SELECT TOP 1 @DrugSequ=DrugSequence FROM DrugSequence WHERE ProjectNumber=@ProjNum AND Location=@Location AND DrugStatus=0 AND GroupNumber=@GroupNum
UPDATE DrugSequence SET DrugStatus=1 WHERE ProjectNumber=@ProjNum AND DrugSequence=@DrugSequ
END
ELSE
BEGIN
SELECT TOP 1 @DrugSequ=DrugSequence FROM DrugSequence WHERE ProjectNumber=@ProjNum AND DrugStatus=0 AND GroupNumber=@GroupNum
UPDATE DrugSequence SET Location=@Location,DrugStatus=1 WHERE ProjectNumber=@ProjNum AND DrugSequence=@DrugSequ
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN -1
END
END
/*保存分组配对结果*/
INSERT INTO dbo.GroupMatch
(PatientName,PatientSex,PatientBirthday,Location,TestSequence,ProjectNumber,GroupNumber,DrugSequence,Re,GroupTime)
VALUES(@Name,@Sex,@Birthday,@Location,@TestSequ,@ProjNum,@GroupNum,@DrugSequ,@Re,@GroupTime)
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN -1
END
/*更新分组条件表*/
UPDATE GroupCondition SET GroupedCount=GroupedCount+1 WHERE ProjectNumber=@ProjNum AND GroupName=@GroupNum
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN -1
END
COMMIT TRAN
RETURN 1
END
END
ELSE /*项目不允许配对*/
RETURN @GroupState
前面有的不会修改的地方都问过大家了,在此谢谢大家的帮助,最后一个RETURN语句我还不知道如何修改,希望大家能帮帮忙。谢谢!
你可以用OUT 变量OR 将结果保存在表中的方法
保存到表中应该会吧,INSERT(UPDATE)