Prerequisite : PL/SQL Introduction
What do you mean by PL/SQL?
In Oracle, PL/SQL (Procedural Language/SQL) is the procedural language extension to the non-procedural SQL. It combines the data manipulation power of SQL and the procedural power of standard programming languages. PL/SQL was developed by Oracle Corporation within the early '90s to reinforce the capabilities of SQL. It integrates well with SQL* PLUS and other application development products of Oracle. PL/SQL is the superset of SQL. It provides SQL data manipulation commands and SQL data types. In PL/SQL, a block without any name is called Anonymous Block. PL/ SQL block consists of various functions, library, procedures, trigger, packages etc.
The following points should be remembered while writing a PL/SQL program -
- In PL/SQL the semicolon (;) is placed at the end of an SQL statement or PL/SQL control statement.
- Section keyword DECLARE, BEGIN and EXCEPTION are not followed by semicolons.
- END keyword and all other PL/SQL statements require a semicolon to terminate the statements.
- Before you start creating code, define the goals and capabilities of your PL/SQL software.
- Select names for constants, variables, and other identifiers that are both meaningful and descriptive.
- To keep track of the modifications in your PL/SQL code, use version control tools (like Git).
- Keep the use of universal variables to a minimum.
- Observe the least privilege principle.
- Use the COMMIT and ROLLBACK instructions to provide proper transaction management.
Features of PL/SQL :
The various features of PL/SQL are given below -
- PL/SQL runs on various operating systems such as windows, Linux etc.
- PL/SQL have an error-checking facility and displays user-friendly messages when an error occurs in a program.
- It offers logging and debugging capabilities, including the capacity to use exception messages.
- SQL can be executed dynamically.
- When certain data events, such as INSERT, UPDATE, or DELETE actions on a table, occur, triggers are specialized forms of stored processes that are automatically invoked.
- Multi-row queries are handled using cursors.
- The declaration of variables and constants to store data values is supported.
What do you mean by PL/SQL Architecture?
The PL/SQL runtime system is a technology and not an independent product. This technology is actually like an engine that exhibits PL/SQL blocks, subprograms like functions and procedures. This engine can be installed in an Oracle Server or in application development tools such as Oracle Form Builder, Oracle Reports Builder etc.
PL/SQL Architecture
PL/SQL can reside in two environments -
- The Oracle Server
- The Oracle tools
These two environments are independent of each other. In either environment, the PL/SQL engine accepts any valid PL/SQL block as input. The PL/SQL engine executes the procedural part of the statements and sends the SQL statement executer in the Oracle Server. A single transfer is required to send the block from the application to the Oracle Server, thus improving performance, especially in a Client-Server network. PL/SQL code can also be stored in the Oracle server as subprograms that can be referenced by any number of applications connected to the database.
Advantages of PL/SQL :
- PL/SQL provides better performance.
- PL/SQL has high Productivity.
- It supports Object-Oriented Programming concepts.
- It has Scalability and Manageability.
- PL/SQL supports various Web Application Development tools.
Disadvantages of PL/SQL :
- PL/SQL requires high memory.
- Lacks of functionality debugging in stored procedures.
Similar Reads
SQL Server Architecture Microsoft SQL Server is a widely used relational database management system (RDBMS) that organizations around the world rely on for managing and processing their data. It provides a scalable and reliable platform for managing large volumes of data, supporting a wide range of applications from small-
5 min read
Architecture of MySQL MySQL is a Relational Database Management system which is free Open Source Software Under GNU License. It is also supported by Oracle Company . It is fast , scalable, easy to use database management System. MySQL support many operating system like Windows, Linux, MacOS etc. MySQL is a Structured Que
6 min read
RDBMS Architecture RDBMS stands for Relational Database Management System and it implements SQL. In the real-world scenario, people use the Relational Database Management System to collect information and process it, to provide service. E.g. In a ticket processing system, details about us (e.g. age, gender) and our jo
3 min read
Oracle Architecture Pre-requisites: Database Oracle Database is a relational database management system (RDBMS) that Oracle Corporation created and marketed. It is one of the most popular RDBMSs on the market and is used to store and retrieve data for a wide range of applications. Oracle Database is well-known for its
5 min read
PostgreSQL - System Architecture PostgreSQL, often simply called Postgres, is an advanced open-source Object-Relational Database Management System (ORDBMS). It stands out due to its robust feature set, extensibility, and compliance with SQL standards. Originating as the successor to the POSTGRES system, one of the earliest database
7 min read
PL/SQL Introduction PL/SQL (Procedural Language/Structured Query Language) is a block-structured language developed by Oracle that allows developers to combine the power of SQL with procedural programming constructs. The PL/SQL language enables efficient data manipulation and control-flow logic, all within the Oracle D
7 min read
SQL for Data Science Mastering SQL (Structured Query Language) has become a fundamental skill for anyone pursuing a career in data science. As data plays an increasingly central role in business and technology, SQL has emerged as the most essential tool for managing and analyzing large datasets. Data scientists rely on
7 min read
The Three-Level ANSI-SPARC Architecture In 1971, DBTG(DataBase Task Group) realized the requirement for a two-level approach having views and schema and afterward, in 1975, ANSI-SPARC realized the need for a three-level approach with the three levels of abstraction comprises of an external, a conceptual, and an internal level. The three-l
2 min read
Characteristics of SQL Structured Query Language (SQL) is a standard language to write queries. It was developed under R Project by IBM. SQL has a basic grammar and syntax. It was declared as a standard language to use by American Standard National Institute(ANSI) and International Standard Organization(ISO). The function
2 min read
DBMS Architecture 1-level, 2-Level, 3-Level A database stores important information that needs to be accessed quickly and securely. Choosing the right DBMS architecture is essential for organizing, managing, and maintaining the data efficiently. It defines how users interact with the database to read, write, or update information. The schema
7 min read