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:
- IN parameter mode
- OUT parameter mode
- 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)