In some cases they could be used to get things that were in the game "legitimately" but which you might not have access to. E.g. getting Mew in Pokemon Blue/Red or Surfing Pikachu in Pokemon Yellow.
Sometimes the codes would break in interesting ways. For example, an invincibility code might cause problems if it was on during a battle that you are scripted to lose. Or the "invincibility" code might actually work by instantly healing you; so you might find out that you are vulnerable if an enemy is strong enough to take all of your health with a single hit.
Like the parent post said, there was a lot of experimentation even when everything was working "correctly".
Pokemon Red (Game Boy SP + Game Genie) unlocking the unobtainable Mew (which Nintendo only gave away at rare in-person tournament events) at game start:
Zelda Link's Awakening (Game Boy + Game Genie) changing the value of "1" rupee to "255" rupees for effectively infinite currency (the creator isn't aware of the significance of "255"):
Banjo-Kazooie (N64 + Gameshark) replacing the main characters with the Rareware logo and adding an "infinite jump by pressing A" hack, which adds displacement or velocity with a button press (yes, this is totally absurd):
Goldeneye on N64 had some really cool hidden modes that could only be turned on with Game Genie. I don't recall which ones exactly required the Game Genie, but I'm pretty sure there was one that made everyone invisible until their first death in multiplayer. That was a lot of fun.
Some of the hidden stuff in Goldeneye was available without memory hacking, it just required a long sequence of button presses that was unknown for a fair amount of time. I believe stuff like invisibility in multiplayer or the special developer models are only available via button sequences.
I memorized the invisibility sequence. Grabbing Oddjob and smashing in that invis cheat was a sure way to make everyone hate you at a party. Oddjob was a good pairing because you weren't completely invisible, so his short stature/hitbox made the effect more scummy.
You're right that N64 did not have Game Genie (Super Nintendo and Sega Genesis also did), but N64 did have Gameshark, which was the spiritual successor to Game Genie (along with Action Replay). There is a project (https://github.com/JCR64/GoldenEye-007-N64-Gun-Game) that adds games to Goldeneye via Gameshark
In some cases they could be used to get things that were in the game "legitimately" but which you might not have access to. E.g. getting Mew in Pokemon Blue/Red or Surfing Pikachu in Pokemon Yellow.
Sometimes the codes would break in interesting ways. For example, an invincibility code might cause problems if it was on during a battle that you are scripted to lose. Or the "invincibility" code might actually work by instantly healing you; so you might find out that you are vulnerable if an enemy is strong enough to take all of your health with a single hit.
Like the parent post said, there was a lot of experimentation even when everything was working "correctly".