Pl/sql concept
PL/SQL stands for Procedural Language Extension to SQL. PL/SQL extends SQL by adding L/SQL is a combination of SQL along with the procedural features of programming languages. programming structures and subroutines available in any high-level language. PL/SQL is used for both server-side and client-side development. For example, database triggers. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).Advantages of PL/SQL
These are the Advantages of PL/SQL
Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
supports error handling.
pl/sql supports subprograms.
supports variable (sql support bind variable).
PL/SQL BLOCK STRUCTURE
PL/SQL BLOCK STRUCTURE
PL/SQL programs are combined into blocks
PL/SQL blocks combine statements that represent a single logical task.
PL/SQL blocks can be divided into two groups: named and anonymous. Named PL/SQL blocks are used when creating subroutines. These subroutines are procedures, functions, and packages. The subroutines then can be stored in the database and referenced by their names later.
PL/SQL blocks contain three sections: the declaration section, the executable section, and the exception-handling section. The executable section is the only mandatory section of the block.
Others are optional As a result, a PL/SQL block has the following structure:
DECLARE
Declaration statements
BEGIN
Executable statements
EXCEPTION
Exception-handling statements
END;
DECLARATION SECTION
The declaration section is the first section of the PL/SQL block. It contains definitions of PL/SQL identifiers such as variables, constants, cursors, and so on.
DECLARE
One or more variable declarations
BEGIN
One or more PL/SQL statements
END;
Ex-
DECLARE
v_ename VARCHAR2(35);
V_job VARCHAR2(35);
c_comm CONSTANT NUMBER := 0;
EXECUTABLE SECTION
The executable section is the next section of the PL/SQL block. This section contains executable statements that allow you to manipulate the variables that have been declared in the declaration section
Write the keywords BEGIN and END. Place your lines of code between those two keywords. Here’s an example:
BEGIN
NULL;
END;
o/p- pl procedure executed successfully.
EXCEPTION-HANDLING SECTION
The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed when a runtime error occurs within the block. Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler. When a runtime error occurs,
control is passed to the exception-handling section of the block. The error is then evaluated, and a specific exception is raised or executed. Ex-
DECLARE
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Variables
Variables are an essential part of any programming language. A variable is simply a location in memory where you can store data of different types.
All variables must be declared before they can be used.
Variables can be read or set during execution of a PL/SQL block.
Variables - Scalar Types --A scalar type holds a single value of a specific type.The main atatypes supported by PL/SQL are those which correspond directly to the types held within the database, BOOLEAN types are also supported.ex-
Varchar,char,number
Several other subtypes are available, these are basically made up from the types above. Other types include, BINARY_INTEGER, DEC, DECIMAL, FLOAT, DOUBLE PRECISION, INT, INTEGER, NATURAL, NUMERIC, PLS_INTEGER and POSITIVE.
THE MOST COMMON DATATYPES
The following are the major data types in Oracle that you can use in your PL/SQL:
VARCHAR2
CHAR
NUMBER[(precision, scale)]
BINARY_INTEGER
DATE
TIMESTAMP
BOOLEAN
LONG
LONG RAW
LOB (large object)
ROWID
The PL/SQL Identifiers
PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters.
Assignment operators
INTO – this operator is used for assign variable for internal values ex-
Select ename,job,comm into v_ename,v_job,v_comm from emp
Where empno=7839;
:= this operator is used for external values
For example
V_sal := 5000;
V_job :=upper(‘moharana’);
V_total := v_num1 + v_num2;
DBMS_OUTPUT.PUT_LINE(‘MESSAGE TO PRINT’)
DBMS_OUTPUT : is a package that includes a number of procedure and functions that accumulate information in a buffer so that it can be retrieved later. These functions can also be used to display messages to the user.
PUT_LINE : Put a piece of information in the package buffer followed by an end-of-line marker. It can also be used to display message to the user. Put_line expects a single parameter of character data type. If used to display a message, it is the message 'string'.
EG:dbms_output.put_line(x);
Executing a Block of Code in SQL*PlusEG:dbms_output.put_line(x);
Execute the code block by simply placing a backslash (/) as the last line of code, and thenpress the Enter key.
The following is an example displaying a code block that has been typed into SQL*Plus:
SQL> BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO HCL TEX ACADEMY ');
END;
/
Note The line SET SERVEROUTPUT ON at the beginning of the script is an important detail.
That command instructs SQL*Plus to look for and display any output from DBMS_OUTPUT.PUT_LINE. A common mistake is to omit the SET SERVEROUTPUT ON command and then be left wondering why you don’t see any output.
DECLARE
V_Sample1 NUMBER(2);
V_Sample2 CONSTANT NUMBER(2) :=99;
V_Sample3 NUMBER(2) NOT NULL := 0;
V_Sample4 NUMBER(2) := 50;
V_Sample5 NUMBER(2) DEFAULT 25;
BEGIN
DBMS_OUTPUT.PUT_LINE('The Value in Sample1 : '||NVL(TO_CHAR(V_Sample1), 'Not Assigned'));
DBMS_OUTPUT.PUT_LINE('The Value in Sample2 : '||NVL(TO_CHAR(V_Sample2), 'Not Assigned'));
DBMS_OUTPUT.PUT_LINE('The Value in Sample3 : '||NVL(TO_CHAR(V_Sample3), 'Not Assigned'));
DBMS_OUTPUT.PUT_LINE('The Value in Sample4 : '||NVL(TO_CHAR(V_Sample4), 'Not Assigned'));
DBMS_OUTPUT.PUT_LINE('The Value in Sample5 : '||NVL(TO_CHAR(V_Sample5), 'Not Assigned'));
END;
PL/SQL Program Units
A PL/SQL unit is any one of the following:
PL/SQL block
Function
Package
Package body
Procedure
Trigger
Type
Type body
Commenting Your Code
Place two dashes before any text to create a one-line comment. For example, in the following code there is a comment placed before the query to describe its functionality: ex-
-- The following query obtains a count of rows from the employees table
Multiline comments can be created beginning with a slash and asterisk (/*) and ending with an asterisk and slash (*/). The following lines depict a multiple-line comment for a given code block: ex-
/* This comment describes the functionality
in the following code block. */
Some important questions to remind.
Why it is more efficient to combine SQL statements into PL/SQL blocks?
What are the differences between named and anonymous PL/SQL blocks?
What happens when the runtime error NO_DATA_FOUND occurs in the PL/SQL block just shown?
What happens when an anonymous PL/SQL block is executed?
What steps are included in the compilation process of a PL/SQL block?
What is a syntax error?
How does a syntax error differ from a runtime error?
No comments:
Post a Comment