What for to work with base ORACLE only through procedures?

Question

Oracle

I'm working with PHP + MySql, and I communicate with the database with queries like SELECT, UPDATE, INSERT.
They tell me that - but we work with PHP + ORACLE, and PHP programmers work with the database only through procedures.
I understand that there is an ORACLE developer who writes these procedures, and PHP programmers simply call them with the necessary parameters?
I understand correctly? If so, why such an approach? And is it right?

Answer 1

If the project is simple, then of course it is more convenient to call SELECT, UPDATE and INSERT from the client application.
But as soon as the project becomes large enough, or additional security restrictions are imposed, it is more convenient to move part of the business logic to the server. In this case - on stored procedures.
Pluses following:
1. Stored procedures and client applications can be written by different people (teams), with different preparation. Stored procedures are more experienced, client applications are more inexperienced.
2. Stored procedures can provide an extra layer of security. Part of the validation logic can be implemented on the server. And the client software, even by changing or forging calls, will not receive data that does not belong to them. If all application logic is implemented on the client, then an attacker can rewrite requests and get data that does not belong to him. In addition, the structure of the database is hidden from the client software.
3. Stored procedures, as non-interactive modules, are easier to debug and test in automatic mode.
4. Written storages are less subject to changes in the course of evolution than client software, and are actively reused in different client modules.

Answer 2

Normal approach. 20 years ago it was the de facto standard.
A database developer usually costs more than a PHP developer.
Now, usually, this approach is used in corporate development, where they can afford to keep such specialists.
And in mysql until quite recently there were no stored procedures. Since then, the fashion has gone to push all the logic into the application server.
This is neither bad nor good, you need to go from the task and know the strengths and weaknesses of placing business logic in an application server or in a database server.
https://habrahabr.ru/post/219445/

Answer 3

The simplest situation: update several tables based on selection results from others.
Individual requests "from outside" run the risk of not being fulfilled (for example, a connection break). Initiation of transactions in such a scheme is fraught with the same disconnection of their non-completion.
And the procedure - you can consider it an integral part of the database - is much less subject to all this. Well, as it were, "atomic" in the framework of the implementation of business logic.
Changes in the data structure in this case can only affect the database side (table structure and procedures).

Answer 4

I had the experience of participating in the writing of an OLTP system, in which there was a fairly high rate of updates, and the same records (from different transactions) were often updated. In order to avoid delays, it was necessary to ensure that transactions were completed quickly (explicit commit or rollback). If the client accesses the database not through stored procedures, then transaction management is performed on the client side, which means that if the client behaves incorrectly (does not quickly complete the transaction), or there are communication problems in the middle of the transaction, then records that update many transactions , will be blocked by this pending transaction. If the client interacts through stored procedures, then transaction management is carried out from such procedures (explicitly begins and explicitly ends in the same procedure). This minimizes transaction time. For us, this was one of the main reasons for using stored procedures. Although, of course, it was also essential that the traffic between the client and the database was minimized. In my opinion, these two reasons are of great importance for OLTP.
In another project (on MS SQL) there were huge amounts of initial data. To reduce the size of the database, a very specific representation of data in tables was developed, so that only certain predefined queries with required parameters could be effectively executed. If these queries did not specify the required parameters and / or add additional conditions, then the optimizer could easily choose an execution plan in which the server "went into itself" (it was simply impossible to wait for the result). However, the customer demanded to provide him with access to this data (for example, to provide some views) from his applications. If we provided views to him, then when using them, he could not set the required parameters, set additional conditions, bind these views with some other tables / views / subqueries. When executing such queries, performance problems would easily arise (moreover, the entire database server). We gave the customer a set of stored procedures with mandatory parameters (he could not have set them any more), returning resultsets, which he can read in the same way as when executing a regular query.

Answer 5

Met periodically in some frims. Basically, if the queries are very complex, with subqueries and some logic, then they are taken out into procedures, in other cases it is an overhead. Debating them is still a pleasure. Big problems with nested transactions from outside. So I see them more as a necessary evil.

Answer 6

the main reason is that php is not a typed language, heavy business logic should not be written in such a way. oracle pl/sql typed language, deeply integrated with data. in php you updated the code and you can only pray that the php code expects the same data that is in the database. if someone has deleted the table, the php code knows nothing about it. procedures in know to what tables address, know what types of columns. those. if you rolled on basis a curve a patch, at once it is visible, the affected procedures become invalid and will not allow to launch entirely curve procedure. php code, until it stumbles upon a jamb, will not suspect an ambush.
Plus, heavy business logic in pl/sql is easier to write, because you can bind to a table type by declaring table1.name%type, table1%rowtype. Plus, you can write much more efficient code using these constructs like bulk collect, bulk insert.

Answer 7

I myself know examples of banks where they do this, the interface is in PHP, and the business logic is Oracle procedures, and Oracle procedures are not always written by a separate programmer (well, it depends on qualifications), well, plus complex logic for working with a database is easier to do in procedures