Call Oracle Stored Procedures in PL/SQL

1 year ago
Isabella Edwards
1 minute

In PL/SQL, you can call an Oracle stored procedure using the following syntax:

DECLARE
  <variable_declaration>;
BEGIN
  <procedure_name>(<parameter_values>);
END;

In this, is the part where variables are declared, is the name of the stored procedure to be called, and are the parameters of the stored procedure.

For instance, suppose there is a stored procedure named get_employee_info, which takes an employee ID as a parameter and returns the employee’s information. This stored procedure can be called in the following way:

DECLARE
  emp_id NUMBER := 1001;
  emp_name VARCHAR2(100);
  emp_salary NUMBER;
BEGIN
  get_employee_info(emp_id, emp_name, emp_salary);
  
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
END;

In the example above, variables emp_id, emp_name, and emp_salary are first declared. The get_employee_info stored procedure is then used to retrieve employee information and assign the results to the respective variables. Finally, the DBMS_OUTPUT.PUT_LINE statement is used to output the employee’s name and salary information.

bannerAds