WHAT IS PL/SQL
Ashish Ranjan
IT Recruiter- Talent Acquisition || B.TECH(EEE) || Tech & Non-Tech Hiring || Leadership Hiring || Corporate Hiring
PL/SQL?(Procedural Language for SQL) is?Oracle Corporation's?procedural?extension?for?SQL?and the?Oracle relational database. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7),?Times Ten in-memory database?(since version 11.2.1), and?IBM Db2?(since version 9.7).[1]?Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
PL/SQL includes procedural language elements such as?conditions?and?loops, and can handle?exceptions?(run-time errors). It allows declaration of constants and?variables, procedures, functions, packages, types and variables of those types, and triggers.?Arrays?are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with?object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
Historically, the first public version of PL/SQL definition[2]?was in 1995, and the Oracle's inception year ~1992. It implements the ISO?SQL/PSM?standard.[3]
PL/SQL program unit[edit]
The main feature of SQL (non-procedural) is also its drawback: control statements (decision-making?or?iterative control) cannot be used if only SQL is to be used. PL/SQL provides the functionality of other procedural programming languages, such as decision making, iteration etc. A PL/SQL program unit is one of the following: PL/SQL anonymous block,?procedure,?function,?package?specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is developed, compiled, and ultimately executed on the database.[4]
领英推荐
PL/SQL anonymous block[edit]
The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to?NULL?value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label.[5]
For example:
<<label>> -- this is optional
DECLARE
-- this section is optional
number1 NUMBER(2);
number2 number1%TYPE := 17; -- value default
text1 VARCHAR2(12) := ' Hello world ';
text2 DATE := SYSDATE;