Advantages of pl/sql, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Advantages of pl/sql

%type - cursors, %TYPE: This attribute gives the datatype of a formerly...

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

Naming conventions-pl/sql, Naming Conventions The similar naming conventi...

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Create GUIs in PL/SQL, i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

Cursor for loops, Cursor FOR Loops In most cases that need an explicit ...

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

Using cursor attributes - bulk bind performance improvement, Using Cursor A...

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %

Join and and in sql, JOIN and AND in SQL In this Section is all about ...

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Biochemical origin of life - modern concept, BIOCHE M ICA L ORIGIN OF LI...

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Constants and variables in pl sql, Constants and Variables:   You can...

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor

Assignment 4, I need a query for PL/SQL, selecting names with cursor, goes ...

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd