Anonymous Blocks: Quick and one-time use.
Named Blocks: Modular, reusable, and stored for repeated use. ( stored as an
object in DB)
Anonymous Blocks:
1. Does not have a name.
2. Cannot be stored in the database.
3. Executed immediately after being written.
Named Blocks:
Named and Stored: Saved in the database for reuse.
Reusable: Can be executed multiple times.
Supports Parameters: Accepts IN, OUT, and IN OUT parameters.
Modular: Helps break complex logic into smaller, manageable units.
Improved Maintainability: Centralized updates reflect everywhere.
Security: Access can be restricted using privileges.
Error Handling: Includes robust exception handling.
Encapsulation: Packages allow public and private declarations.
Types: Includes procedures, functions, packages, and triggers.
Performance: Reduces runtime overhead and network traffic
Example :
DECLARE
v_name VARCHAR2(50) := 'Vishal'; -- Declaring a variable
v_greeting VARCHAR2(100); -- Declaring another variable
BEGIN
v_greeting := 'Hello, ' || v_name || '! Welcome to PL/SQL programming.';
DBMS_OUTPUT.PUT_LINE(v_greeting); -- Printing the greeting message
END;
/
DBMS_OUTPUT.PUT_LINE – package and procedure
Package and method or function
SET SERVEROUTPUT ON; enable the display of output
Example
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, Vishal!');
END;
--- addition of two number
--- addition of two number – getting values from
DECLARE user
DECLAREs
num1 number;
num2 number;
num1 number;
num2 number;
total number (10);
BEGIN
num1 :=10;
num2:=10;
total := num1+num2;
dbms_output.put_line(total);
END;
Constant value
In PL/SQL, a constant is a variable whose value cannot be changed once it
is initialized
DECLARE
emp_id NUMBER NOT NULL;
BEGIN
emp_id := NULL; -- This will raise an error
END;
If we require more records that
time we need
1. Cursor
2. Bulk collect
DECLARE
lv_ename varchar2(10);
begin
select ename into lv_ename from emp where empno = 7654;
dbms_output.put_line(lv_ename);
end;
Multiple records :
DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE empno IN (7654, 7788, 7839); --
Example: multiple empno values
lv_ename VARCHAR2(10);
BEGIN
-- Open the cursor and loop through the records
FOR emp_record IN emp_cursor LOOP
lv_ename := emp_record.ename; -- Fetch each employee's name
DBMS_OUTPUT.PUT_LINE(lv_ename); -- Output the name
END LOOP;
END;
