Experiment No 24: Create Procedures and stored procedures for modularity

 Experiment No 24: Create Procedures and stored procedures for modularity

Result:
The PL/SQL procedure was created and executed successfully. It performed the intended task, demonstrating modularity and reusability. Example output from the procedure greetings:

Hello World!

---

1. Write syntax for creating PL/SQL Procedure

-- General syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter1 [IN | OUT | IN OUT] datatype, parameter2 [IN | OUT | IN OUT] datatype, ...) ]
IS
   -- Declaration section (optional)
   declaration_section
BEGIN
   -- Executable section
   executable_statements;
EXCEPTION
   -- Exception handling section (optional)
   WHEN exception_name THEN
      exception_statements;
END procedure_name;
/

Example:

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

---

2. Write steps to call a procedure in PL/SQL

Steps:

1. Ensure the procedure is already created and compiled in the database.

2. Open a PL/SQL block or SQL*Plus / SQL Developer worksheet.

3. Call the procedure using its name:

BEGIN
   procedure_name;  -- Replace with your procedure
END;
/

Example:

BEGIN
   greetings;
END;
/

Output:

Hello World!

---

3. List types of parameters in Procedure and explain them

In PL/SQL, procedures can have three types of parameters:

Parameter Type	Description	Direction
IN	Accepts input value from caller. Cannot modify the caller’s variable.	Input only
OUT	Returns a value to the caller. Cannot be used as input inside the procedure.	Output only
IN OUT	Can accept input from caller and return a modified value.	Input and Output

Example with parameters:

CREATE OR REPLACE PROCEDURE add_numbers(
   num1 IN NUMBER,       -- Input parameter
   num2 IN NUMBER,       -- Input parameter
   sum OUT NUMBER        -- Output parameter
)
AS
BEGIN
   sum := num1 + num2;   -- Calculation
END;
/

-- Calling the procedure
DECLARE
   result NUMBER;
BEGIN
   add_numbers(10, 20, result);
   dbms_output.put_line('Sum = ' || result);
END;
/

Output:

Sum = 30

---

Exercise

Perfect! I will provide Oracle-ready PL/SQL code for both tasks, with input parameters and sample execution.

---

1. Procedure emp_count() to count number of employees in a department

Assumptions:

• Table: EMP

• Columns: EMPNO, ENAME, DEPTNO

Procedure:

CREATE OR REPLACE PROCEDURE emp_count(
   dept_no IN NUMBER,       -- Input parameter: department number
   total_emp OUT NUMBER     -- Output parameter: total employees
)
AS
BEGIN
   SELECT COUNT(*) INTO total_emp
   FROM emp
   WHERE deptno = dept_no;
   
   dbms_output.put_line('Total employees in Department ' || dept_no || ' = ' || total_emp);
END;
/

Calling the procedure:

DECLARE
   emp_total NUMBER;
BEGIN
   emp_count(10, emp_total);  -- Example: dept_no = 10
   -- The output is also displayed via dbms_output.put_line
END;
/

Sample Output (assuming dept_no 10 has 3 employees):

Total employees in Department 10 = 3

---

2. Stored procedure to accept name and greet user

Procedure:

CREATE OR REPLACE PROCEDURE greet_user(
   user_name IN VARCHAR2
)
AS
BEGIN
   dbms_output.put_line('Hello, ' || user_name || '! Welcome to the Oracle PL/SQL Lab.');
END;
/

Calling the procedure:

BEGIN
   greet_user('Vijay');  -- Example: user_name = 'Vijay'
END;
/

Sample Output:

Hello, Vijay! Welcome to the Oracle PL/SQL Lab.

---

3. EMP table columns:

• EMPNO (NUMBER, primary key)

• ENAME (VARCHAR2)

• JOB (VARCHAR2)

• MGR (NUMBER)

• HIREDATE (DATE)

• SAL (NUMBER)

• COMM (NUMBER, nullable)

• DEPTNO (NUMBER)

---

Procedure to insert three records

CREATE OR REPLACE PROCEDURE insert_emp_records
AS
BEGIN
   -- Insert first record
   INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   VALUES (9001, 'RAMESH', 'CLERK', 7698, SYSDATE, 15000, NULL, 10);

   -- Insert second record
   INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   VALUES (9002, 'SURESH', 'ANALYST', 7566, SYSDATE, 25000, NULL, 20);

   -- Insert third record
   INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   VALUES (9003, 'KIRAN', 'SALESMAN', 7698, SYSDATE, 18000, 2000, 30);

   -- Commit the changes
   COMMIT;

   dbms_output.put_line('Three records inserted successfully into EMP table.');
END;
/

---

Calling the procedure

BEGIN
   insert_emp_records;
END;
/

Expected Output:

Three records inserted successfully into EMP table.

---