Advantages of PL/SQL
The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown:
1) Support for SQL:
The SQL has become the standard database language as it is powerful, flexible, and easy to learn. A little English-like command like SELECT, INSERT, DELETE, and UPDATE make it easy to manipulate the data stored in a relational database.
The SQL is non-procedural; it means that you can state what you want complete without stating how to do it. The Oracle determines the best way to carry out your request. There is no essential connection between the consecutive statements as Oracle executes the SQL statements one at a time. The PL/SQL use all the SQL data manipulation, transaction control commands, and cursor control as well as all the SQL functions, operators, and pseudo columns. And hence, you can manipulate the Oracle data flexibly and safely. The PL/SQL also fully supports the SQL datatypes. That decreases the need to convert data passed between your applications and the database.
The PL/SQL also supports the dynamic SQL, an advanced programming method that makes your applications so more flexible and versatile. Your programs can build and process the SQL data definition, session control statements, and data control "on the fly" at run time.
2) Support for Object-Oriented Programming:
The Object types are an ideal object-oriented modeling tool that you can use to decrease the cost and time needed to build the complex applications. In addition allowing you to create software components that are maintainable, modular, and reusable, object types allow various teams of programmers to develop software components concurrently.
By encapsulating the operations with data, the object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into techniques. The object types also hide the implementation details, so that you can modify the details without affecting the client programs.
Moreover, object types allow for realistic data modeling. The Complex real-world entities and relationships map directly into object types. That helps your programs better replicate the world that they are trying to replicate.
3) Better Performance:
Without PL/SQL, the Oracle must process the SQL statements one at a time. Each of the SQL statement results in another call to Oracle and higher performance overhead. In the networked environment, the overhead can become important. Every time a SQL statement is issued, it should be sent over the network, creating more traffic.
Though, with PL/SQL, the whole block of statements can be sent to Oracle at one time. This can drastically decrease the communication between your application and Oracle. As the figure shows, if your application is database intensive, you can use the PL/SQL blocks and subprograms to the group SQL statements before sending them to the Oracle for execution.
The PL/SQL stored procedures are compiled once and stored in an executable form; therefore the procedure calls are quick and efficient. Also, the stored procedures, which execute into the server, and can be invoked over slow network connections with a single call. That reduces the network traffic and improves the round-trip response times. The Executable code is automatically cached and shared among the users. That lowers the memory requirements and invocation overhead.
Figure: PL/SQL Boosts Performance
4) Higher Productivity:
The PL/SQL adds functionality to the non-procedural tools like Oracle Forms and Oracle Reports. By PL/SQL in these tools, you can use common procedural constructs to build the applications. For e.g. , you can use whole PL/SQL block in an Oracle Forms trigger. You do not require using multiple trigger steps, macros, or user exits. And hence, PL/SQL increases the productivity by putting better tools in your hands.
Furthermore, the PL/SQL is similar in all environments. As soon as you master the PL/SQL with one Oracle tool, you can transfer your information to other tools, and so multiply the productivity gains. For example, the scripts written with one tool can be used by another tool.
5) Full Portability:
The Applications written in the PL/SQL are portable to any operating system and platform, on which the Oracle runs. In another word, PL/SQL programs can run anywhere that the Oracle can run; you do not require tailor them to each new environment. That means you can write the portable program libraries that can be reused in various environments.
6) Tight Integration with SQL:
The PL/SQL & SQL languages are tightly integrated. The PL/SQL supports all the SQL datatypes and the non-value NULL. That permits you to manipulate the Oracle data easily and efficiently. It also helps you to write the high-performance code.
The %TYPE and %ROWTYPE attributes further integrate the PL/SQL with SQL. For e.g., you can use the %TYPE attribute to declare variables, by using the declarations on the definitions of database columns. If a definition changes, the variable declaration changes consequently the next time you compile or run your program. The new definition takes the effect without any effort on your part. This gives the data independence, reduces maintenance costs, and allows the programs to adapt as the database changes to meet new business requirements.
7) Security:
The PL/SQL stored procedures enable you to partition the application logic between the client and server. In this way, you can prevent client applications from manipulating the sensitive Oracle data. The Database triggers written in the PL/SQL can disable the application updates selectively and do content-based auditing of user queries.
Moreover, you can limit the access to Oracle data by allowing users to manipulate it only through the stored procedures that execute with their definer's privileges. For example, you can grant the users access to a procedure that updates a table, but not allow them to access to the table itself.