How do I add a new user to a PL/SQL table using a function?

Asked

Viewed 29 times

0

I recently started working with PL/SQL and one of the doubts I have is in creating and declaring functions. More specifically on this:

-Creates a function that creates a new user:

1: Usa uma sequence para dar ao User um novo ID
2: Passa o nome, morada, etc... como IN argumentos
3: Returna como OUT argumentos o ID criado e uma mensagem de erro O_ERROR_MESSAGE
4: A função retorna TRUE se o user é adicionado, se não retorna FALSE
5: Não te esqueças das excepções
6: Cria um bloco PL/SQL e testa a função criada

I’ve already written these lines of code:


    CREATE OR REPLACE FUNCTION DSB_ADD_NEW_USERS (I_NAME IN VARCHAR2,
                                                I_ADDRESS IN VARCHAR2,
                                                I_BIRTHDATE IN DATE,
                                                I_COUNTRY IN VARCHAR2)
    RETURN NUMBER IS
    O_ERROR_MESSAGE EXCEPTION;
    CURRENT_USER NUMBER;

BEGIN

DSB_NB_SEQ_USER_ID.NEXTVAL;
SELECT COUNT(USER_ID) INTO CURRENT_USER
FROM DSB_NB_USERS;

WHILE CURRENT_USER != 0
LOOP
DSB_NB_SEQ_USER_ID.NEXTVAL;
SELECT COUNT(USER_ID) INTO CURRENT_USER
FROM DSB_NB_USERS;
END LOOP;

INSERT INTO DSB_NB_USERS (USER_ID, NAME, ADDRESS, BIRTHDATE, COUNTRY_ID_FK) VALUES (CURRENT_USER, I_NAME, I_ADDRESS, TO_DATE('I_BIRTHDATE', 'DD/MM/YYYY'), I_COUNTRY);

RETURN CURRET_USER;

EXCEPTION
    WHEN O_ERROR_MESSAGE THEN 
    RETURN NULL;

    WHEN OTHERS THEN
    RETURN NULL;
END;

DECLARE
    I_NEW_USER NUMBER;
BEGIN
    I_NEW_USER := DSB_ADD_NEW_USERS(I_NAME => 'Arnaldo Amaral',
                                    I_ADDRESS => 'Rua da Agra',
                                    I_BIRTHDATE => '03/05/1959',
                                    I_COUNTRY => 'PT');                                 
END;

Am I far from the truth? I know I still have a few things to add. Thanks for the help!!!

oracle pl-sql

  • Welcome to Stackoverflow in Portuguese. As the name implies, the Official language used here is English. So, can you Please Translate your Question? If you prefer, you may also Ask this same Question in the English Stackoverflow site.

  • Done! Thank you Rafael.

1 answer

0

create or replace FUNCTION DSB_ADD_NEW_USERS (I_NAME IN VARCHAR2,
                                                I_ADDRESS IN VARCHAR2,
                                                I_BIRTHDATE IN DATE,
                                                I_COUNTRY IN VARCHAR2)
    RETURN NUMBER IS
    CURRENT_USER NUMBER;

BEGIN
CURRENT_USER := DSB_NB_SEQ_USER_ID.NEXTVAL;
DBMS_OUTPUT.PUT_LINE('Hello World');

INSERT INTO DSB_NB_USERS (USER_ID, NAME, ADDRESS, BIRTHDATE, COUNTRY_ID_FK) VALUES (CURRENT_USER, I_NAME, I_ADDRESS, TO_DATE(I_BIRTHDATE, 'DD/MM/YYYY'), I_COUNTRY);
RETURN CURRENT_USER;

EXCEPTION
    WHEN OTHERS THEN 
    RETURN -1;

END;

SET SERVEROUT ON
DECLARE
    I_NEW_USER NUMBER;
BEGIN
    I_NEW_USER := DSB_ADD_NEW_USERS(I_NAME => 'Arnaldo Amaral',
                                    I_ADDRESS => 'Rua da Agra',
                                    I_BIRTHDATE => '03/MAY/1959',
                                    I_COUNTRY => 'PT');       
                                    commit;


END;
/

After a few hours of asking various people, this was the solution found and tested.

Thank you!

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.