Clerical spells, or "prayers" as they might be called, along with the rest of the cleric's (and paladin's, as appropriate) stable of supernatural abilities, are a manifestation of the will of the gods in the D&D world. Beyond that, however, things get a little bit sketchy and can vary dramatically from game world to game world.
Are the Gods real? Do they have physical forms that can be destroyed, or are they little more (or little less) than incorporeal essences or energies? Do they inhabit the same plane as the PCs, or are they residents of different planes/spheres? Is their divine power the result of their otherworldly origin, or is their location distant from mortal realms a factor in establishing their divinity?
I have to admit it. Questions like these rarely, if ever, plague me when I'm creating a campaign world. Until recently, I never gave much thought to how the PCs might interact with their deities in ways other than those prescribed by the rules--divine spell casting abilities, all of which are pretty well codified. Direct interaction with demons, devils, elementals: hell yes! Those are exciting realms to explore, certainly. I have no idea why I didn't realize earlier that interaction with infernal/elemental outsiders was ultimately hollow without the possibility for interaction with the divine, at least in some way.
Recently I've started to incorporate elements of various religious traditions into the game--dwarves draw strength from their ancestors, so I set up networks of ancestor spirits who actually intercede in worldly matters and serve as liaisons with the greater dwarven deities, and so forth. I feel like this has expanded the sense of cosmic depth in the world and increased opportunity for the mysterious in a positive sense as much as a negative one, but I also feel like I'm a real latecomer to this aspect of the game, and I'm still getting my feet under me.
Do the gods play any kind of big role in your campaign worlds, past or present? In my current campaign they don't take center stage in any sense, but with two highly devout followers of the same god in my 3.x campaign it has to have some weight, or else it's purely a cosmetic choice.
I'm intrigued to find out how other DMs handle divinities in their campaigns. Here are some initial considerations that I think might affect how I incorporate them in the future:
-In Swords and Sorcery style campaigns, I can see divine intervention coming primarily from antagonistic forces and not from beneficent ones. Or even beneficial intervention coming with a price or danger (i.e. Elric's summoning of Arioch).
-In a more high-fantasy setting, the PCs could benefit from divine protections while undertaking quests on behalf of the patron deity.
-In a gonzo/high magic setting, the PCs could routinely interact with divinities, even battling them directly (happens on a couple of occasions in older D&D materials).
-A campaign world or region focused on religious conflict could be interesting, as involvement with political intrigues could hinge upon one's devotion or lack thereof, in addition to sectarian affiliation.