Avoid client-side SQL - call stored procedures instead


Link to this posting

To access/manipulate data, client applications (in client/server architecture) and middle tier components (in distributed n-tier applications) should not issue SELECT, INSERT, UPDATE and DELETE statements in any form (embedded into a host language, used as data sources of data objects etc.). Instead, they should execute database stored procedures. Only the procedures have the permissions to access and manipulate the tables directly. The the client or middle layer application doesn't see the tables and only has the permissions to execute the procedures.

Benefit #1: Hiding the complexity.

The stored procedures act as API between the client or middle layer application (written in Java, .NET, PowerBuilder or whatever), and the tables. The stored procedures are the main "working horses" of the system. They implement the BLL (business logic layer), i.e. enforce data validation (except of basic validation like required fields or values ranges), perform automatic and manual business-actions etc. If the system has more than one client (for example, for web and for Windows), these clients are kept simpler (less intellectual) - that simplifies future changes and bug fixes since the business logic is implemented only in one place.

Benefit #2: Improved performance.

It's very common that a method, which implement business logic, contains a few (sometimes a lot of) SELECT and DML statements (and calls other client methods, which do the same). If business logic is implemented on the client side, each such SQL means two trips: to the database (with the input data) and back to the client (with the retrieved data or success/failure status). If business logic is implemented in stored procedures, then the whole process occurs on the database side, so only two client-database trips are involved: calling the proc and returning its result. That improves performance drastically! If you would implement the BLL on the the client or middle layer side, that could kill performance if you have heavy database processing. Always keep in mind, that each SQL statement requires two trips - to and from the database. So, if your business flow has 20 SQL statements, that means 40 DB trips (against 2 trips when using stored procedures). And what if some SQLs appear inside loops? It's a very common practice to scan a result set, executing SQLs for each record!

From the O'Reilly book "Oracle PL/SQL Programming":

Rather than have developers write their own SQL statements, you should provide an interface to those SQL statements. This is one of the most important motivations for building packages, yet is only rarely employed by developers.

With this approach, PL/SQL developers as a rule will not write SQL in their applications. Instead, they will call predefined, tested, and optimized code that does all the work for them; for example, an "add" procedure (overloaded to support records) that issues the INSERT statement and follows standard error-handling rules; a function to retrieve a single row for a primary key; and a variety of cursors that handle the common requests against the data structure (which could be a single table or a "business entity" consisting of multiple tables).

If you take this approach, developers will not necessarily need to understand how to join three or six different highly normalized tables to get the right set of data. They can just pick a cursor and leave the data analysis to someone else. They will not have to figure out what to do when they try to insert and the row already exists. The procedure has this logic inside it.

Perhaps the biggest advantage of this approach is that as your data structures change, the maintenance headaches of updating application code are both minimized and centralized. The person who is expert at working with that table or object type makes the necessary changes within that single package, and the changes are then "rolled out" more or less automatically to all programs relying on that package.
What are the advantages of using an SQL stored procedures?

Maintainability - If we have multiple numbers of applications and we want to do some changes in procedure, then we just need to change the procedure, not all the applications. So, the maintenance is easier for stored procedure

Security - Stored Procedure not just secure the data and access code but also it applies the security within the application code. Also, it limits the direct access to tables. Securing our data is what all we need and so Stored Procedure do.

Testing - We can test stored procedure without any dependency of the application.

Speed - It has a good speed because stored procedures are saved in the cache memory, so we don’t need to extract them from the base every time. We can easily use them through this cache on the server.

Replication - We can replicate the stored procedure from one database to another. Also, we can revise the policies on a central server rather than on individual servers.

https://www.quora.com/What-are-the-advantages-of-using-an-SQL-stored-procedures


Tips for different systems:

Oracle:
All the procs, serving one table, should be organized in one package. For example, it can be named PCK_ORDER for ORDER table. Stored procedures should use the following prefixes for CRUD operations: S, I, U and D. Example for ORDER table: S_ORDER, I_ORDER, U_ORDER and D_ORDER. Of course, the package can also contain other procs (not related to the basic CRUD), served by the package - for example, S_OPEN_ORDERS (to retrieve orders in status 'OPEN'), G_LAST_ORDER_DATE_FOR_CUST etc. The naming convention for procs which return data:
### S returns a recordset via a REF_CURSOR (which mimics returning data with a SELECT statement in other databases - that's why it's S).
### G returns data via RETURN statement or OUT parameter(s) (i.e. it's a classic getter).
If the procedure is not dedicated to one particular table but implements a business process which changes many tables, use the prefix P (just Procedure), or don't use any prefix at all (when called from other procs, it will be clear that it's a proc since it will follow the package name).

MS SQL Server, Sybase, MySQL and other databases which don't support packages:
All the procs, serving one table, should begin with the same entity name, so they will be listed together when sorted. That will make the life a bit easier in lack of packages. That means using naming convention postfixes instead of prefixes. Example for ORDER table's CRUD procs: ORDER_S, ORDER_I, ORDER_U and ORDER_D. For additional DB actions (not the CRUD), do the same (start the proc name with the entity name) for the same reason (like ORDERS_OPEN_S to retrieve orders in status 'OPEN'). Unfortunately, that is not always possible in Sybase due to the 30 characters limit in identifiers length, so names like LAST_ORDER_DATE_FOR_CUST_G are acceptable as well (first of all, proc names must be clear and absolutely self-explanatory!). The naming convention for procs which return data:
### S returns a recordset with a SELECT statement.
### G returns data via OUTPUT parameter(s) (i.e. it's a classic getter).
If the procedure is not dedicated to one particular table but implements a business process which changes many tables, use the postfix P (just Procedure).

Java:
In an entity framework (such as Hibernate and MyBatis), map Java bean fields to returned recordset (for SELECT) or parameters (for INSERT, UPDATE and DELETE) of stored procedures rather than to fields of database tables.

.NET:
If an object of ADO.NET's DbCommand class is used in the DAL (data access layer) then set its CommandType property to StoredProcedure and populate its SelectCommand, InsertCommand, UpdateCommand and DeleteCommand properties with the names of the stored procedure used for SELECT, INSERT, UPDATE and DELETE (but not with hardcoded SQL as it happens when the CommandType is set to Text).

PowerBuilder:
When you are creating a DataWindows, choose "Stored Procedure" as its data source. Then, in the DataWindow painter, choose procs for insert, update and delete (menu "Rows" > "Stored Procedure Update"). If your DB is Oracle, then read Oracle packaged procedure as DW data source.