Cams can be had for 175 and up. The cost is rather high to install one. but guessing a shop will charge no less than $750.00 and upwards to $1,800.
You really dont want to spend this unless you are going to get P&P heads and P&P intakes to go with it. Doing heads, cam and intakes all at the same time would be more wise and cost efficient.
I am not sure of your technical abilities but doing it yourself and with the help of a few knowledgeable friends this can be done in a day to a day and a half. But this is not for the timid. You have to have all the right tools, manuals and parts such as gaskets, assembly lube, new head bolts, RTV sealant to be able to complete this job it is a great learning experience.
As far as sound goes depends what cam grind you get. If it is rather mild cam and you already have dual exhaust it will have a nice mild lope but if you go with a more aggressive cam you will have a much more wild lope and can shake the ground and other cars around you at a traffic light.
I have a fairly agressive cam and at Idle my car lopes like a harley and sounds like a beast WIDE OPEN THROTTLE!!!!