Handcannon (Resident Evil: Umbrella Chronicles)- Very little ammo, but ridiculously powerful. Once I was fighting the Bat boss and I reduced his health to 3/4. Then I switched to the Handcannon. Boom. Headshot. DEAD. It's worth noting that bosses can easily withstand several close-range shotgun blasts.
Frost Splasher (Mega Man X5) - Whenever you do an air dash, you're protected by a shield that makes you damage enemies by touching them instead of the other way around. Mucho fun, especially with the Hyper Dash enhancement.
Storm Tornado (Mega Man X) - Creates a long wind tunnel that goes through enemies. Moves slowly at first, and hits multiple times depending on the enemy's size. So it basically kills anything it touches.
Z-Saber (Mega Man X4) The Z-Saber itself is a cool weapon, but what makes it awesome is the moves you get when you defeat bosses. When you've defeated all the bosses, you can destroy enemy shots, do a flaming uppercut slash, a longer ranged lightning stab, a downward ice thrust, and a somersaulting slash in mid air, as well as some moves that don't actually use the sword itself.
Magnet Mine (Mega Man X2) - A bomb that moves slowly across the screen. Its slow horizontal movement is a good thing because you can more easily contol it's up/down movements that way. Also, it falls into the "multiple hit, kills most enemies" category with Storm Tornado.
Inflator (Turok Rage Wars) - I think the name speaks for itself. A fully charged shot is pretty much the equivalent of shoving 500 Big Macs down your enemy's throat.
Pistol (Halo) - Halo was the first (and only, so far) game that I've played where the pistol wasn't an underpowered pile of crap. Sure, it was a bit on the overpowered side, but half of the weapons in the game can kill you in one hit, in certain circumstances (Sniper - Headshot, Rocket Launcher - direct or near direct impact, Shotgun - point blank, Plasma Grenade - sticking)
Semi Auto Rifle (Resident Evil 4) - A sniper rifle that has a fast firing rate, good damage and a good ammo capacity. Also pierces through multiple enemies. The best overall weapon in the game, good for bosses and regular enemies alike. Really useful in the minigame if there are no enemies nearby and you need to kill an enemy quickly before your bonus runs out.
Mine Thrower (Resident Evil 4) - Like the sniper, except instead of bullets, it shoots darts that explode after a short time.
Exploding Crossbow Bolts (Van Helsing) - Pretty self explanatory. Does good damage, and you could fire about 5-6 shots before the ammo ran out. But in that game, secondary ammo for your weapons would slowly recharge, so you got plenty of opportunity to use it.
Grapple Gun (Van Helsing) - Grapple walls, pull yourself towards them. Grapple enemies, pull themself towards you, followed by a rising uppercut slash. One moment, an enemy's throwing projectiles at you from a safe distance. The next, you're in his face, comboing his arse, and the both of you are in the air.
Scimitars (Van Helsing) - A nice alternative to the short ranged Tojo Blades. Too bad you don't get them until near the end of the game.
EDIT: As for the whole Resident Evil 4 pistol debate, I myself went with the Punisher. Why? I just used pistols to set up for Roundhouses and Suplexes, so damage from the pistol itself was kind of useless for me. Being able to shoot through those pesky shields was what made me choose the punisher over the other weapons.
