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
CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

IF Statement The IF statement executes a series of statement conditionally. Whether the series is executed or not depends on the value of the Boolean expression. Syntax:

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca