MacBook currently has quiet SSD, will adding extra HDD cause constant noise? My MacBook Pro has an SSD drive that runs silent, which is really great.

I am considering removing the SuperDrive, moving the SSD to the optical bay, and adding a 7200-RPM regular HDD in the main compartment. 

I would install OS X on the SSD, and keep large files I seldom need on the HDD.

Will there be a (new) constant spinning noise, or will the HDD be silent when not in use?
Certainly it depends on the drive.  From my experience using Apple laptops that contained HDD's, there was NO noise unless the drive was failing (an unfortunately common occurrence).  But some HDD's are definitely louder than others.

One thing to clarify though: you will not hear the drive "spinning".  You may hear the drive being accessed. The primary source of noise is the actuator arm, not the spindle/platters.  When idle the drive should be silent.