Stored procedures are implementation details. Database functions, lambdas, or a shell script stored somewhere in the file system are all implementation details and irrelevant for the architecture.
most books on microservices recommend one database per microservice.
Ok, so we can code the stored procedures in these databases.
again most microservice architecture books state that they should be
autonomous and loosely coupled
Between business capabilities, development's life cycles, management,
deployments, team's locations, etc. Nothing to do with the implementation details. Microservices don't solve a technical problem (just the opposite). They come to solve problems with the management and the time-to-market. It's a strategy, not a tactic. A way to fail-fast with the least costs possible. If a certain business capability is proven to be worthless, we drop it without messing up other capabilities, deployments, projects' management, releases...
Note that the "split" already acts like a decoupling agent. Say we have two services, A is backed by Oracle and B by MongoDB. If we don’t break the golden rule of decoupling, it should be possible to drop A + Oracle with negligible side effects on B.
Using stored procedures written say specifically in Oracle, tightly
couples the microservice to that technology.
It might cause vendor lock-in. Many times, the vendor is imposed by the business due to historical or contractual reasons1. It is important to know how to not lock our code to the vendor. For example, some ORM and frameworks implement a new query language that hides the database built-in functions and features.
Although, if our services are micro enough, vendor lock-in is no longer a problem since it impacts a small part of the whole. A small part that should be possible to be replaced (or isolated) quickly.
most MSA books (that I have read) recommend that microservices should
be business oriented (designed using DDD).
It should be business-driven and here the thing. Not all business take advantage of DDD. DDD and microservices overlap in many points, but they are not cause-effect. We could end up with a microservices ecosystem composed of anaemic services. Or composed of a mix of both: services implementing a complex domain and dumb anaemic services providing POJOs directly from the DB. There's nothing wrong with that.
Regarding books, they only focus on the execution of the strategy. The tactics -how to take advantage of the distributed computing- how to make it work to success, but they are (usually) agnostic to the strategy. Strategies vary from company to company and rarely depends on developers. So, we still have to extrapolate and adapt what books say to our specific needs, requirements and constraints. The goal is to make the business strategy profitable and sustainable.
Always bear in mind that any architecture is a means to an end. The business rules. We don't build microservices ecosystems for fashion or for love to the art.