Advantages of pl/sql, PL-SQL Programming

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.

2231_boost performance.png

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.

Posted Date: 10/2/2012 2:00:52 AM | Location : United States







Related Discussions:- Advantages of pl/sql, Assignment Help, Ask Question on Advantages of pl/sql, Get Answer, Expert's Help, Advantages of pl/sql Discussions

Write discussion on Advantages of pl/sql
Your posts are moderated
Related Questions
Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

I want to implement heap sort algorithm in pl sql please share the source code for guidance

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

query to Find the account numbers of all customers whose balance is more than 10,000 $