Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
181
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.59 KB | None | 0 0
  1. CREATE OR REPLACE PROCEDURE CREATE_ACCOUNT_SP (
  2. p_account_id      OUT INTEGER,
  3. p_email           IN VARCHAR,
  4. p_password        IN VARCHAR,
  5. p_location_name   IN VARCHAR,
  6. p_account_type    IN VARCHAR,
  7. p_first_name      IN VARCHAR,
  8. p_last_name       IN  VARCHAR
  9. )
  10.  
  11. IS
  12. p_count NUMBER (10);
  13. ex_error EXCEPTION;
  14. err_msg_txt VARCHAR(100) :=NULL;
  15.  
  16. BEGIN
  17.  
  18. SELECT COUNT (*)
  19. INTO p_count
  20. FROM I_ACCOUNT
  21. WHERE p_email = account_email;
  22.  
  23. IF p_count > 0
  24. THEN
  25. err_msg_txt := 'The email already exists, try another one ';
  26. RAISE ex_error;
  27. ELSIF p_email IS NULL THEN
  28. err_msg_txt := 'the email  must not be null.';
  29. RAISE ex_error;
  30. ELSIF  p_password IS NULL THEN
  31. err_msg_txt := 'The password must not be null.';
  32. RAISE ex_error;
  33. ELSIF p_account_type IS NULL THEN
  34. err_msg_txt := 'The account type cannot be null.';
  35. RAISE ex_error;
  36. ELSIF p_account_type NOT IN ('Individual', 'Organization or Group') THEN
  37. err_msg_txt := ' The account type must be one of these "group or organization"  or "individual." ';
  38. RAISE ex_error;
  39. END IF;
  40.  
  41.  
  42. INSERT INTO I_ACCOUNT ( ACCOUNT_ID,ACCOUNT_EMAIL,ACCOUNT_PASSWORD,ACCOUNT_LOCATION_NAME,ACCOUNT_TYPE,ACCOUNT_FIRST_NAME,ACCOUNT_LAST_NAME ) VALUES (
  43.     Account_sequences.NEXTVAL,
  44.     p_email,
  45.     p_password,
  46.     p_location_name,
  47.     p_account_type,
  48.     p_first_name,
  49.     p_last_name
  50.  
  51.     );
  52.  
  53. COMMIT;
  54.  
  55.  
  56.     EXCEPTION
  57.     WHEN ex_error THEN
  58.     DBMS_OUTPUT.put_line(err_msg_txt);
  59.     ROLLBACK;
  60.     WHEN OTHERS THEN
  61.     DBMS_OUTPUT.put_line(' the error code is: ' || SQLCODE);
  62.     DBMS_OUTPUT.put_line(' the error msg is: ' || SQLERRM);
  63.     ROLLBACK;
  64.  
  65. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement