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
Using COUNT The COUNT returns the number of elements that a collection presently contains. For instance, when a varray projects contains 15 elements, then the following IF con

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

EXCEPTION_INIT Pragma The pragma EXCEPTION_INIT relates an exception name with an Oracle error number. Which allow you to refer to any internal exception by the name and to wri

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur