PLSQL Introduction

                                     Introduction

PLSQL Anonymous Block:-PLSQL block, those have no name are called PLSQL Anonymous Block. These block execute once. you can execute again by using SQL*Plus feature (/) but never called by name as they don't have any name.

Note:- Oracle never store P-Code for these blocks.


Block structure of Anonymous Block:-


Declare

    Declaration Section;  --Optional
begin
    Executable Section;  --Mandatory
Exception
    Exception Section;    --Optional
end;
/

DECLARATION SECTION:-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. PL/SQL identifiers are covered in detail throughout this book.

Example:-
DECLARE
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
v_salary NUMBER;
V_DOB DATE;
c_counter CONSTANT NUMBER := 0;
v_rate NUMBER NOT NULL := 7;


EXECUTABLE SECTION:-This section contains executable statements that allow you to manipulate the variables that have been declared in the declaration section.

Example:-                   
BEGIN
SELECT first_name, last_name INTO v_first_name, v_last_name FROM student
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||v_last_name);
END;
/
       
EXCEPTION SECTION:-This section contains statements that are executed when a run-time error occurs within the block. Run-time errors occur while the program is running and cannot be detected by the PL/SQL compiler.When a run-time error occurs, control is passed to the exception section of the block. The error is then evaluated, and a specific exception is raised or executed.


Example:-
BEGIN
SELECT first_name, last_name INTO v_first_name, v_last_name FROM student
WHERE student_id = 112;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no student with '||'student id 112');
END;
/

No comments:

Post a Comment