What Are Procedures in PL/SQL?

Procedure

---

• Are a type of subprogram that perform an action
• Can be stored in the database as a schema object
• Promote re-usability and maintainability

Creating Procedures with the SQL CREATE OR REPLACE

---

• Use the CREATE clause to create a stand-alone procedure that is stored in the Oracle database.
• Use the OR REPLACE option to overwrite an existing procedure.

Syntax:

  CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [mode] datatype1,
      parameter2 [mode] datatype2, ...)]
      IS|AS
     [local_variable_declarations; ...]
     BEGIN--actions;
     END [procedure_name];

Example:

  CREATE OR REPLACE PROCEDURE test
  IS
  BEGIN
    dbms_output.put_line('Hi This is Bhavesh Lakhani');
  END test;

Script output:

Calling Procedure

  EXECUTE test;

Difference between procedure and function

 

What Are Parameters in PL/SQL procedure?

---

• Are declared after the subprogram name in the PL/SQL header
• Pass or communicate data between the calling environment and the subprogram
• Are used like local variables but are dependent on their parameter-passing mode

Different types of Parameter mode:

---

1. IN parameter mode
2. OUT parameter mode
3. IN OUT parameter mode

• An IN parameter mode (the default) provides values for a subprogram to process
• An OUT parameter mode returns a value to the caller
• An IN OUT parameter mode supplies an input value, which may be returned (output) as a modified value

Procedural Parameter Modes

---

• Parameter modes are specified in the formal parameter declaration, after the parameter name and before its data type.
• The IN mode is the default if no mode is specified.

IN Parameter mode

---

Example: 

CREATE OR REPLACE PROCEDURE get_emp (emp_id number)
IS
v_fname varchar(15);
BEGIN
select first_name into v_fname from employees where employee_id=emp_id;
dbms_output.put_line('first_name : '||v_fname );
END get_emp;
/

script output:

Calling procedure:

EXECUTE get_emp(100);

DBMS output:

Out parameter mode:

---

Example:

CREATE OR REPLACE PROCEDURE emp_test( p_id IN employees.employee_id%TYPE,
                                      p_name OUT employees.last_name%TYPE,
                                      p_salary OUT employees.salary%TYPE) 
IS
BEGIN
SELECT last_name, salary INTO p_name, p_salary
FROM employees
WHERE employee_id = p_id;
END emp_test;

Script output:

Calling Procedure:

SET SERVEROUTPUT ON
DECLARE
v_emp_name employees.last_name%TYPE;
v_emp_sal employees.salary%TYPE;
BEGIN
emp_test(100, v_emp_name, v_emp_sal);
DBMS_OUTPUT.PUT_LINE(v_emp_name||' earns '||v_emp_sal);
END;
/

Output:

IN OUT parameter

---

Example:

CREATE OR REPLACE PROCEDURE say_hello(msg IN OUT varchar2) is
BEGIN
msg:='Hello '||msg;
END say_hello;

script output:

Calling procedure:

SET SERVEROUTPUT ON
DECLARE
v_msg varchar(15) :='Bhavesh';
BEGIN
say_hello(v_msg);
DBMS_OUTPUT.PUT_LINE(v_msg);
END;

Script output

Formal and Actual Parameters

---

• Formal parameters: Local variables declared in the parameter list of a subprogram specification
• Actual parameters (or arguments): Literal values, variables, and expressions used in the parameter list of the calling subprogram

Example:

CREATE OR REPLACE PROCEDURE get_emp (emp_id number)
IS
v_fname varchar(15);
BEGIN
select first_name into v_fname from employees where employee_id=emp_id;
dbms_output.put_line('first_name : '||v_fname );
END get_emp;

Passing actual parameter.

Execute get_emp(100);

Passing formal parameter

v_emp_id := 100;
EXECUTE get_emp(v_emp_id)

Different between IN,OUT and IN OUT parameter

---