Lets start with the science side of things, and then swing around to the more historical side. Weight is going to be best translated as mass and power, or kinetic energy. In other words, the ‘heavier’ the gun, the more powerful it is. The reason is very sciencey, but think it through. If the cannon weighs five pounds (not realistic, but extremities aid in understanding a scientific concept), and they try to fire a five hundred pound shot from it, what will happen?

Assuming you could somehow, magically guarantee that the shot is able to leave the barrel, the gun would do most of the moving. In the real world, however, the gun would explode. The shot is too heavy to be launched from the barrel, the cannon wants to move, and you have a ready made bomb.

So a quick rule of thumb is that the cannon should be heavier than the shot it fires.

Now, onto the more historical side of things. The reason they had multiple weights of shot, for the same caliber is fairly simple. They both had different jobs. If you had a gun that could fire a five hundred pound shot, the gun would have to be massive. This would be on the order of several tons.

Napoleonic Era wars were about speed and ability to reposition the guns and fire. A light gun would best be pulled by a team of horses, and they could move at, or close to, a full gallop. This means that you would effectively have a powerful weapon moving at the speed of calvary. There are probably posts that can describe horse artillery tactics in the Napoleonic War better than myself. But the role of horse artillery was something that best be described as lightning strikes.

In contrast, a heavier gun, pulled by a similar team of horses. Could only move at a walking pace. The gun was more versatile and could be used for a wider range of roles, but it lacked any flexibility on the field. Calvary, Dragoons, and Horse Artillery could easily outmaneuver them and destroy them. The Heavier gun could use single powerful shot, and be more effective at dealing with fortified positions, or it could be filled with more pellets for a canister shot.

So, because of the physics, the heavy guns where more destructive in a single use, but highly susceptible to the lighter and faster guns. The fast guns where weak, but able to quickly out position the heavier guns.

That is about the best answer that I can give with the way the question is stated. A little more context could help a lot. I wouldn’t be surprised to find that you are referring to the differences between a ‘cannon’ and a ‘howitzer’ or ‘mortar’. Without knowing what you are reading, you could be getting an over simplified look at the differences between a mortar and a cannon, but the author may or may not distinguish between the different types of guns.