PROCEDURE

Example

DELIMITER //
CREATE PROCEDURE GetCount(
    IN table_name VARCHAR(50)
)
BEGIN
    SET @s = CONCAT('SELECT COUNT(*) FROM ',table_name);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

The above SQL code block creates a stored procedure named ‘GetCount’ that accepts a table name and returns the count of rows in the table. In this procedure, a SQL query is prepared as a string and then executed to get the desired output. In the output block shows the successful creation of the procedure.

Example

CREATE OR REPLACE PROCEDURE insert_data(p_name VARCHAR)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO test_table(name) VALUES (p_name);
END;
$$;

Output

(No output on screen)

Explanation

The procedure insert_data is defined here, which accepts a parameter p_name of type VARCHAR. The procedure is written in Pl/pgSQL (LANGUAGE plpgsql). Inside the procedure, an INSERT INTO statement is executed to insert the provided name into a table named test_table. This procedure does not return any output on execution. It simply performs an insert operation.

Example

CREATE PROCEDURE EmployeeSalary
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Salary FROM Employees WHERE ID = @EmployeeID
END;
GO
EXEC EmployeeSalary 1;

Output

FirstName   LastName    Salary
John        Doe         50000

Explanation

The above script creates a stored procedure named EmployeeSalary that accepts an integer parameter @EmployeeID. The stored procedure executes a select query against the Employees table and pulls out the FirstName, LastName, and Salary of the employee with the passed EmployeeID.

The stored procedure is then executed with the EXEC command, passing in 1 as the value for @EmployeeID. The employee with ID 1 has the values ‘John’, ‘Doe’, and ‘50000’ for fields FirstName, LastName and Salary respectively in the sample output.

Example

CREATE OR REPLACE PROCEDURE proc_name
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Hello, World!');
END;
/
BEGIN
  proc_name();
END;
/

Output

Hello, World!

Explanation

This is an example of a stored procedure in Oracle using PL/SQL. The CREATE OR REPLACE PROCEDURE statement is used to create the proc_name procedure. The DBMS_OUTPUT.PUT_LINE statement within the procedure is used to display the text ‘Hello, World!’. The procedure is executed with the BEGIN...END; block creating a call to proc_name().