For example, you have to update 1000 record at same time. By sql script, there would be 1000 thousand round trip between database server and your application, but by PL/SQL, it will be done in one trip. Besides, PL/SQL can be pre-compiled.
Yes, stored procedure is not always a good choice. Because that means build the business logic in the database, which means it is poor extensibility and maintainability. This is a trade-off.