In PL/SQL, a procedure is a block of code that can take zero or more input parameters, perform a specific task, and optionally return zero or more output parameters. Procedures are typically used to encapsulate a specific piece of functionality that can be called from other parts of the program. For example, you might have a procedure that updates a customer’s address, or that calculates the total sales for a given period.
A function, on the other hand, is also a block of code that can take zero or more input parameters, perform a specific task, but must return a single output value. Functions are typically used to perform calculations or return a single value based on the input parameters. For example, you might have a function that calculates the average salary for a group of employees, or that returns the first name of an employee based on their employee ID.
Here is an example of a PL/SQL procedure that updates the salary of an employee:
And here is an example of a PL/SQL function that calculates the total salary for a department:
In the above example, the procedure is called update_employee_salary, and it takes two parameters: p_employee_id, and p_new_salary. It updates the salary of an employee with the id of p_employee_id and the new salary is p_new_salary.
The function is called get_department_total_salary, and it takes one parameter: p_department_id. It calculates and return the total salary of the department with the id of p_department_id.
To call these procedures and functions, you can use the EXECUTE or EXEC command. For example, to call the update_employee_salary procedure and update an employee’s salary, you would use the following command:
This would update the salary of the employee with ID 123 to 50000.
To call the get_department_total_salary function and retrieve the total salary for a department, you would use the following command:
This would retrieve the total salary for department 10 and assign it to the variable l_total_salary and then it will print the output.
In general, procedures are used to perform actions and functions are used to return a value. Procedures do not return values, whereas functions always return a value. You can use procedures to perform complex logic and calculations, and then use functions to retrieve the results.
It’s also worth mentioning that in PL/SQL, procedures and functions can be defined inside a package. A package is a container that can hold procedures, functions, variables and other constructs together. This way, you can organize your code and make it more readable and maintainable.
In summary, procedures and functions in PL/SQL are blocks of code that can be executed to perform specific tasks and return specific values. Procedures are used to perform actions, while functions are used to return values. They can be defined independently or inside a package and they can take zero or more input parameters and optionally return zero or more output parameters.
