sqlserver里的存储过程改成oracle的给举个例子，和说明需要注意的地方

[lorinzhang]

CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
    @ApplicationName                        nvarchar(256),
    @UserName                               nvarchar(256),
    @Password                               nvarchar(128),
    @PasswordSalt                           nvarchar(128),
    @Email                                  nvarchar(256),
    @PasswordQuestion                       nvarchar(256),
    @PasswordAnswer                         nvarchar(128),
    @IsApproved                             bit,
    @CurrentTimeUtc                         datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @NewUserId uniqueidentifier
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    SET @CreateDate = @CurrentTimeUtc

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId
        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
        SET @NewUserCreated = 1
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0
        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    END

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    IF ( EXISTS ( SELECT UserId
                  FROM   dbo.aspnet_Membership
                  WHERE  @NewUserId = UserId ) )
    BEGIN
        SET @ErrorCode = 6
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

    IF (@NewUserCreated = 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate = @CreateDate
        WHERE  @UserId = UserId
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    INSERT INTO dbo.aspnet_Membership
                ( ApplicationId,
                  UserId,
                  Password,
                  PasswordSalt,
                  Email,
                  LoweredEmail,
                  PasswordQuestion,
                  PasswordAnswer,
                  PasswordFormat,
                  IsApproved,
                  IsLockedOut,
                  CreateDate,
                  LastLoginDate,
                  LastPasswordChangedDate,
                  LastLockoutDate,
                  FailedPasswordAttemptCount,
                  FailedPasswordAttemptWindowStart,
                  FailedPasswordAnswerAttemptCount,
                  FailedPasswordAnswerAttemptWindowStart )
         VALUES ( @ApplicationId,
                  @UserId,
                  @Password,
                  @PasswordSalt,
                  @Email,
                  LOWER(@Email),
                  @PasswordQuestion,
                  @PasswordAnswer,
                  @PasswordFormat,
                  @IsApproved,
                  @IsLockedOut,
                  @CreateDate,
                  @CreateDate,
                  @CreateDate,
                  @LastLockoutDate,
                  @FailedPasswordAttemptCount,
                  @FailedPasswordAttemptWindowStart,
                  @FailedPasswordAnswerAttemptCount,
                  @FailedPasswordAnswerAttemptWindowStart )

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

数据类型的对照我已经知道了，其他还需要注意什么，就拿这个做例子吧，改的地方请帮忙标注一下，谢谢

[tangren]

--如果返回一个值，请使用函数，不需要每个语句用BEGIN..END
CREATE OR REPLACE FUNCTION ASPNET_GETUSERBYEMAIL(APPLICATIONNAME IN NVARCHAR2,
                                                 EMAIL           IN NVARCHAR2)
  RETURN NUMBER AS
  V_OUNNT NUMBER;
BEGIN
  IF EMAIL IS NULL THEN
    SELECT COUNT(*)
      INTO V_COUNT
      FROM ASPNET_APPLICATIONS A, ASPNET_USERS U, ASPNET_MEMBERSHIP M
     WHERE LOWER(APPLICATIONNAME) = A.LOWEREDAPPLICATIONNAME
       AND U.APPLICATIONID = A.APPLICATIONID
       AND U.USERID = M.USERID
       AND M.LOWEREDEMAIL IS NULL;
  ELSE
    SELECT COUNT(*)
      INTO V_COUNT
      FROM ASPNET_APPLICATIONS A, ASPNET_USERS U, ASPNET_MEMBERSHIP M
     WHERE LOWER(APPLICATIONNAME) = A.LOWEREDAPPLICATIONNAME
       AND U.APPLICATIONID = A.APPLICATIONID
       AND U.USERID = M.USERID
       AND LOWER(EMAIL) = M.LOWEREDEMAIL;
  END IF;
  IF (V_COUNT = 0) THEN
    RETURN(1);
  ELSE
    RETURN(0);
  END IF;
END ASPNET_GETUSERBYEMAIL;

[tangren]

oracle存储过程格式：
create or replace procedure 过程名(输入参数名 in 参数类型, 输出参数名 out 参数类型) is
--说明部分
v_number number;
begin
--执行部分
v_number := 0;

--判断表中是否存在记录
select count(*) into v_nubmer from t;
if v_number>0 then
--如果t表中有记录的处理语句
end if;
end;
/

1、变量说明必须放到说明部分。
如上：定义一个v_number 类型为number;
2、变量名不能以@开头。
3、ORACLE中赋值没有SET语句。
v_nubmer := 10;
4、oracle中不能使用IF EXSITS(select *...)语句
使用select count(*) into 变量 from ...，然后判断记录数

[lorinzhang]

你好tangren,我找了个短点的，这里的应该怎么改，返回结果集列怎么写，外加判断
用惯了sqlserver 再新学oracle，太头疼了，谢谢，做这个实例吧，返回结果集好像很复杂

CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END

[tangren]

CREATE FUNCTION dbo.aspnet_Membership_GetUserByEmail(ApplicationName VARCHAR2(256),
                                                     Email           VARCHAR2(256))
  RETURN NUMBER AS
  v_count NUMBER;
  --dbo假设为用户名
BEGIN
  IF Email IS NULL THEN
    SELECT COUNT(*)
      INTO v_count
      FROM dbo.aspnet_Applications a,
           dbo.aspnet_Users        u,
           dbo.aspnet_Membership   m
     WHERE LOWER(ApplicationName) = a.LoweredApplicationName
       AND u.ApplicationId = a.ApplicationId
       AND u.UserId = m.UserId
       AND m.LoweredEmail IS NULL;
  ELSE
    SELECT COUNT(*)
      INTO v_count
      FROM dbo.aspnet_Applications a,
           dbo.aspnet_Users        u,
           dbo.aspnet_Membership   m
     WHERE LOWER(ApplicationName) = a.LoweredApplicationName
       AND u.ApplicationId = a.ApplicationId
       AND u.UserId = m.UserId
       AND LOWER(Email) = m.LoweredEmail;
  END IF;
  IF (v_count = 0) THEN
    RETURN(1);
  ELSE
    RETURN(0);
  END IF;
END;

[lorinzhang]

我这个是什么错误，没看懂，有两个错误，说的是哪里需要分号？我没少些啊
错误(6,1): PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined The symbol "authid was inserted before "RETURN" to continue.
错误(7,1): PLS-00103: Encountered the symbol "AS" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor

CREATE OR REPLACE PROCEDURE ASPNET_GETUSERBYEMAIL
(
    APPLICATIONNAME IN  NVARCHAR2,
    EMAIL           IN NVARCHAR2
)
RETURN NUMBER AS V_OUNNT NUMBER;
AS
BEGIN
    IF EMAIL IS NULL THEN
       BEGIN
    	        SELECT COUNT(*)
             INTO V_COUNT
             FROM ASPNET_APPLICATIONS A,
             ASPNET_USERS        U,
             ASPNET_MEMBERSHIP   M
             WHERE LOWER(APPLICATIONNAME) = A.LOWEREDAPPLICATIONNAME
             AND U.APPLICATIONID = A.APPLICATIONID
             AND U.USERID = M.USERID
             AND M.LOWEREDEMAIL IS NULL;
        END;
    ELSE
        BEGIN
         SELECT COUNT(*)
       INTO V_COUNT
       FROM ASPNET_APPLICATIONS A,
           ASPNET_USERS        U,
           ASPNET_MEMBERSHIP   M
       WHERE LOWER(APPLICATIONNAME) = A.LOWEREDAPPLICATIONNAME
       AND U.APPLICATIONID = A.APPLICATIONID
       AND U.USERID = M.USERID
       AND LOWER(EMAIL) = M.LOWEREDEMAIL;
        END;
    END IF ;
    IF (V_COUNT = 0) THEN
       BEGIN
       RETURN(1);
       END;
    ELSE
       BEGIN
       RETURN(0);
       END;
    END IF;
END ASPNET_GETUSERBYEMAIL;