ACS3 Data Model

     1: PROCEDURE init_email_types
     2: IS
     3:    CURSOR c1 IS
     4:       SELECT up.user_id as prefs_user_id, users.email, users.user_id from users, users_preferences up
     5: 	WHERE users.user_id = up.user_id(+);
     6:    prefs_user_id users_preferences.user_id%TYPE;
     7: 
     8: BEGIN
     9:    FOR c1_val IN c1 LOOP
    10: 	-- since we did an outer join, if the user_prefs user_id field is null, then
    11: 	-- no record exists, so do an insert. Else do an update
    12: 	IF c1_val.prefs_user_id IS NULL THEN
    13: 	 INSERT INTO users_preferences (user_id, email_type)
    14: 		values (c1_val.user_id, guess_user_email_type(c1_val.email));
    15: 	ELSE UPDATE users_preferences set email_type = guess_user_email_type(c1_val.email)
    16: 	 	WHERE user_id = c1_val.user_id;
    17: 	END IF;
    18:    END LOOP;
    19:    COMMIT;
    20: END init_email_types;