The vast majority of people in Scotland back the continued production of oil and gas, a poll has found.