What is PL/SQL?

What is PL/SQL?

Rating:

PL/SQL is Oracle's procedural extension to SQL, which is the standard database access language. PL/SQL is an advanced fourth-generation programming language (4GL) and offers seamless SQL access, tight integration with the Oracle server and tools, portability, security, and modern software engineering features such as data encapsulation, overloading, exception handling, and information hiding.

By using PL/SQL, users can manipulate data with SQL statements and control program flow with procedural constructs such as IF-THEN and LOOP. Users can also declare constants and variables, define procedures and functions, use collections and object types, and trap run-time errors.

Applications written by using any of the Oracle programmatic interfaces can call PL/SQL stored procedures and send blocks of PL/SQL code to the Oracle server for execution. 3GL applications can access PL/SQL scalar and composite datatypes through host variables and implicit datatype conversion.

PL/SQL code is very efficient for data-intensive operations and it minimizes network traffic in client/server applications, as it runs inside the database. PL/SQL's tight integration with Oracle Developer lets users develop the client and server components of applications in the same language. It also lets them partition the components for optimal performance and scalability. Moreover, Oracle's Web Forms lets users deploy their applications in a multi-tier Internet or intranet environment without modifying a single line of code.

A PL/SQL Example:

The debit_account procedure updates a bank account after an amount of money has been withdrawn from the account. It accepts an account number and an amount of money as parameters. It uses the account number to retrieve the account balance from the database and then computes the remaining balance. If the remaining balance is less than zero, the procedure jumps to an error routine; otherwise, it updates the bank account.

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT balance INTO old_balance FROM accounts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET balance = new_balance
WHERE acct_no = acct_id;
END IF;
COMMIT;
EXCEPTION
WHEN overdrawn THEN
DBMS_OUTPUT.PUT_LINE ('Requested amount is more than the balance');
END debit_account;

Advantages of PL/SQL: PL/SQL is a completely portable and high-performance transaction processing language that offers the following advantages:

• Full Support for SQL: PL/SQL lets users use all the SQL data manipulation, cursor control, and transaction control commands as well as all the SQL functions, operators, and pseudocolumns. Hence, users can manipulate Oracle data flexibly and safely. PL/SQL fully supports SQL datatypes, thereby reducing conversions as data is passed between applications and the database.
  
  Dynamic SQL is a programming technique that lets users build and process SQL statements dynamically at run time. It gives PL/SQL flexibility comparable to scripting languages such as Perl, Korn shell, and Tcl.



• Tight Integration with Oracle: PL/SQL supports all the SQL datatypes. Combined with the direct access that SQL provides, these shared datatypes integrate PL/SQL with the Oracle data dictionary.
  
  The %TYPE and %ROWTYPE attributes let PL/SQL code adjust as table definitions change. For example, the %TYPE attribute declares a variable based on the type of a database column. If the column's type changes, the variable uses the correct type at run time. This provides data independence and reduces maintenance costs.



• Better Performance: If an application is database intensive, PL/SQL blocks can be used to group SQL statements before the statements are sent to Oracle server for execution. This can significantly reduce the communication overhead between the application and Oracle server.
  
  PL/SQL stored procedures are compiled once and stored in executable form. Therefore, procedure calls are quick and efficient. A single call can start a compute-intensive stored procedure, thereby reducing network traffic and improving round-trip response times. Executable code is automatically cached and shared among users, thereby lowering memory requirements and invocation overhead.



• Higher Productivity: PL/SQL adds procedural capabilities to Oracle Forms and Oracle Reports. For example, an entire PL/SQL block can be used in an Oracle Forms trigger instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. For example, scripts written with one tool can be used by other tools.



• Scalability: PL/SQL stored procedures increase scalability by centralizing application processing on the Oracle server. Automatic dependency tracking helps in developing scalable applications.
  
  The shared memory facilities of the shared server (formerly known as Multi-Threaded Server or MTS) enable Oracle to support many thousands of concurrent users on a single node. For more scalability, the Oracle Net Connection Manager can be used to multiplex network connections.



• Maintainability: Once validated, a PL/SQL stored procedure can be used in any number of applications. If its definition changes, only the procedure is affected. The applications that reference the procedure are not affected. This simplifies maintenance and enhancement. Also, maintaining a procedure on the Oracle server is easier than maintaining the procedure's copies on various client machines.



• PL/SQL Support for Object-Oriented Programming:
  
  
  • Object Types: An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods, which can be implemented in PL/SQL.
    
    Object types are an ideal object-oriented modeling tool, which can be used to reduce the cost and time required to build complex applications. Besides allowing users to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
  
  
  
  • Collections: A collection is an ordered group of elements of the same type (e.g., the grades for a class of students). Each element has a unique subscript that determines its position in the collection. PL/SQL offers two kinds of collections: nested tables and varrays (abbreviation for variable-size arrays).
    
    Collections work like the set, queue, stack, and hash table data structures found in most third-generation programming languages. Collections can store instances of an object type, and they can also be attributes of an object type. Collections can be passed as parameters. Hence, they can be used to move columns of data into and out of database tables or between client-side applications and stored subprograms. Collection types can be defined in a PL/SQL package and can be used across many applications.


• Portability: Applications written in PL/SQL can run on any operating system and hardware platform where Oracle runs. Portable program libraries can be written and then reused in different environments.



• Security: PL/SQL stored procedures let users divide application logic between the client and the server in order to prevent client applications from manipulating sensitive Oracle data. Database triggers written in PL/SQL can prevent applications from making certain updates and can audit user queries.
  
  A DBA (Database Administrator) can restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that have a restricted set of privileges. For example, the DBA can grant users access to a procedure that updates a table rather than granting them access to the table itself.

Rating: