Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE CREATE_ACCOUNT_SP (
- p_account_id OUT INTEGER,
- p_email IN VARCHAR,
- p_password IN VARCHAR,
- p_location_name IN VARCHAR,
- p_account_type IN VARCHAR,
- p_first_name IN VARCHAR,
- p_last_name IN VARCHAR
- )
- IS
- p_count NUMBER (10);
- ex_error EXCEPTION;
- err_msg_txt VARCHAR(100) :=NULL;
- BEGIN
- SELECT COUNT (*)
- INTO p_count
- FROM I_ACCOUNT
- WHERE p_email = account_email;
- IF p_count > 0
- THEN
- err_msg_txt := 'The email already exists, try another one ';
- RAISE ex_error;
- ELSIF p_email IS NULL THEN
- err_msg_txt := 'the email must not be null.';
- RAISE ex_error;
- ELSIF p_password IS NULL THEN
- err_msg_txt := 'The password must not be null.';
- RAISE ex_error;
- ELSIF p_account_type IS NULL THEN
- err_msg_txt := 'The account type cannot be null.';
- RAISE ex_error;
- ELSIF p_account_type NOT IN ('Individual', 'Organization or Group') THEN
- err_msg_txt := ' The account type must be one of these "group or organization" or "individual." ';
- RAISE ex_error;
- END IF;
- INSERT INTO I_ACCOUNT ( ACCOUNT_ID,ACCOUNT_EMAIL,ACCOUNT_PASSWORD,ACCOUNT_LOCATION_NAME,ACCOUNT_TYPE,ACCOUNT_FIRST_NAME,ACCOUNT_LAST_NAME ) VALUES (
- Account_sequences.NEXTVAL,
- p_email,
- p_password,
- p_location_name,
- p_account_type,
- p_first_name,
- p_last_name
- );
- COMMIT;
- EXCEPTION
- WHEN ex_error THEN
- DBMS_OUTPUT.put_line(err_msg_txt);
- ROLLBACK;
- WHEN OTHERS THEN
- DBMS_OUTPUT.put_line(' the error code is: ' || SQLCODE);
- DBMS_OUTPUT.put_line(' the error msg is: ' || SQLERRM);
- ROLLBACK;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement