PLSQL Anonymous Block:-PLSQL block, those have no name is 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;
/
0 comments:
Post a Comment