I'm pretty sure non-ROM-based media (CD, floppies, etc.) can't directly execute code, so yes, the code from the "ROM" (or ISO, as it's usually called) must be loaded to RAM before it can be executed.
The little I tried in PS1 hacking, was using PCSX Tracer or something, and documents recommended hacking savestates (I guess recommending PCSX because the savestates were essentially renamed zip files, making them easy to uncompress for hacking), as until the game needed to load anything else from the CD it was sort of like hacking a ROM-based system.
(and then of course, once the hacking has been tested, finding the original data in the ISO and replacing it)