Learn SQL Database Programming

Creating and calling a stored procedure in Oracle

To create a stored procedure, you can execute the following query:

CREATE OR REPLACE PROCEDURE getplayergameinfo(data OUT varchar2)ISBEGIN SELECT p.playerid INTO data FROM appearances a JOIN people p ON p.playerid = a.playerid JOIN batting b ON a.playerid = b.playerid AND a.yearid = b.yearid  AND a.teamid = b.teamid WHERE b.yearid = 2017 AND H <> 0 FETCH FIRST 1 ROWS ONLY; END; 

To call a stored procedure, you can execute the following query:

DECLARE  results VARCHAR2(4000); BEGIN   getplayergameinfo(results);   DBMS_OUTPUT.PUT_LINE(results); END;

The preceding query will return the results of the getplayergameinfo stored procedure.