Anything you can do from embedded SQL, you can do from a stored procedure. Instead of preparing and executing the SQL, you invoke the SP that does it for and returns the same results.

No extra programming logic needs to be written in the SP language. It can be, often saving the need to ship large volumes of data between the DBM and the application program, and then back again, but it doesn't need to be.

The difference is that that when the schema changes, the application program(s) don't. When a new schema is being developed, the SPs get modified in step and tested in isolation of any applications. When the switchover comes to pass, the existing DB is renamed and the new DB renamed to the old name. Next connection, all the applications using SPs are automatically using the new schema without a single line of their code needing to change.

It is exactly the same process of encapsulation that using objects gives you: OO-101: Tenent 1: Isolate the data format from the caller.

It also provides for an isolation layer between the application code and the DBM vendor. Provided the new DBM vendor also supports SPs and the DBI interface allows access to them, then you simply write the equivalent SP with the same name in the new vendor SP language and again, the applications don't need any modification.

It also allows the SPs to make use of vendor specific optimisations and extensions without fear of lock-in.