And your example isn't exactly a stellar one - if I know there's "a bunch of guns pointing" at the doorway I'm trying to get through, I'm not going to pick the lock on the door - if I have the choice I'll either blow the door down for that 'shock and awe' approach, or I find another entrance and use a stealthy approach on that one instead.
This kind of thinking is something that you'll frequently have to use in tactical shooters (R.I.P.) despite the fact that those games only present you with pure combat scenarios with no such thing as "loot". In the classic Rainbow Six or SWAT games the choice between destroying a door or picking the lock is one of the most common tactical decisions you need to make. If you know that there is an enemy right behind a door, it may indeed be the best idea to set up some explosive charges and blow the door open. On the other hand, if you know that on the other side of the room there's an enemy pointing a gun at a hostage, you might want to rethink your action — blowing up the door would take care of the first guy, sure, but it'd also alert the other guys in the room to your presence, causing them to instantly aim their guns at the door you're trying to get through and perhaps kill the hostages while they're at it. Even if you removed the hostages out of the equation, charging through that single door would be extremely risky and require you to pull off some John Wayne-esque heroics to take out all of the enemies unscathed. So instead, you'll have to think of something more clever. For example, there might be another entrance that would allow you to storm the room from another direction, or perhaps even from two different directions simultaneously if you made use of both doors, but in order to reach it you'd again have to get past a locked door or two, preferably as silently as possible to not alert the enemies. If you do it silently and pick the locks, you might be able to take the enemies by surprise and mow them down easily. If the enemies hear you coming, they will be expecting you, making the encounter a lot more dangerous. Hell, one of them might even come to investigate and put a bullet into the back of your head while you're carelessly shuffling through your pockets to find more of those damn sweet explosive charges.
Of course, it's a completely different genre, but aside from the lack of loot, is a squad-based tactical shooter with close-quarters combat
that far removed from a dungeon crawler, in principle? Deus Ex has of course been mentioned already, and it could be classified as an RPG based on some definition and has doors that can be either picked or blown up without making either approach redundant, because wanting to avoid attention is usually the reason you choose to pick a lock in the first place, aside from creating a more desirable route to your objective. Explosives solve many problems in Jagged Alliance 2 and several other tactical games, but in many instances it's better to use lockpicks or wire cutters to get past certain obstacles, because even though you're there to kill every enemy on the map, you don't want to start a fight until your guys are in the proper positions. Fallout, Arcanum and Underrail also have parts with similar open-ended area design, with the latter having an especially good stealth system (by RPG standards) which would keep the lockpicking skill important for certain characters even if you were able to blow up doors.
Too bad that RPGs are still mostly stuck in the "go to room, kill everything, move to next room" approach where you lose a lot of the tactical depth that is provided by good level design, which reduces some possibly very useful and interesting things like lockpicking to just serving one purpose, which at best creates an illusion of playing a particular type of character instead of actually letting you play one.
's back to bash/pick/detonate it open at more opportune time and place*
