oracle pl / sql程序 [英] oracle pl/sql procedure

问题描述

create or replace 
PROCEDURE LOGV_SP_INSERTUSER
              (
                i_firstname in LOGV_USERS.firstname%type,
                i_lastname in LOGV_USERS.lastname%type,
                i_username in LOGV_USERS.username%type,
                i_password in LOGV_USERS.password%type,
                i_roleid in LOGV_USERS.roleid%type,
                p_result out number
                )
          AS 
           BEGIN
               INSERT INTO LOGV_USERS
                    (
                     userid,
                     firstname,
                     lastname,
                     username,
                     password,
                     roleid
                     ) 
               VALUES
                    (
                    user_seq.nextval,
                    i_firstname,
                    i_lastname,
                    i_username,
                    i_password,
                    i_roleid
                    ); 
         p_result:=sql%rowcount;
    END  LOGV_SP_INSERTUSER;

i想要从这个程序中返回用户ID我怎么能这样做呢？

i want return userid from this procedure how can i do that thanxs a lot

推荐答案

您好，



也许您可以修改存储过程，如下所示。

Hello,

Perhaps you can modify your stored procedure as shown below.

CREATE OR REPLACE PROCEDURE LOGV_SP_INSERTUSER (
    i_firstname IN LOGV_USERS.firstname%type,
    i_lastname  IN LOGV_USERS.lastname%type,
    i_username  IN LOGV_USERS.username%type,
    i_password  IN LOGV_USERS.password%type,
    i_roleid    IN LOGV_USERS.roleid%type,
    p_result    OUT NUMBER,
    p_uid       OUT LOGV_USERS.userid%type
)
AS 
    BEGIN
        p_uid := user_seq.nextval;
        INSERT INTO LOGV_USERS (
            userid,
            firstname,
            lastname,
            username,
            password,
            roleid
        ) 
        VALUES (
            p_uid,
            i_firstname,
            i_lastname,
            i_username,
            i_password,
            i_roleid
        ); 
        p_result := sql%rowcount;

END  LOGV_SP_INSERTUSER;

注意：我假设userid的数据类型是NUMBER。



问候，

Note: I am assuming that the data type for userid is NUMBER.

Regards,

这篇关于oracle pl / sql程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！