{"id":1565,"date":"2017-11-27T21:46:14","date_gmt":"2017-11-27T14:46:14","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1565"},"modified":"2025-05-26T06:59:10","modified_gmt":"2025-05-26T13:59:10","slug":"plsql-procedure","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-procedure\/","title":{"rendered":"PL\/SQL Procedure"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you’ll learn how to create, compile, and execute a PL\/SQL procedure from the Oracle SQL Developer.<\/p>\r\n\r\n\r\n\r\n

Introduction to PL\/SQL procedures #<\/a><\/h2>\r\n\r\n\r\n\r\n

In PL\/SQL, a procedure is a named block that performs a specific task. Unlike an anonymous block, Oracle stores the procedure in the database, and you can execute it repeatedly.<\/p>\r\n\r\n\r\n\r\n

Typically, you create a procedure to encapsulate a reusable code block.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax for creating a procedure<\/p>\r\n\r\n\r\n

CREATE<\/span> [OR<\/span> REPLACE<\/span> ] PROCEDURE<\/span> procedure_name (\r\n    parameter1 IN<\/span> datatype,\r\n    parameter2 OUT<\/span> datatype,\r\n    parameter3 IN<\/span> OUT<\/span> datatype\r\n)     \r\nIS<\/span>\r\n   -- declare variables<\/span>\r\nBEGIN<\/span>\r\n   -- execute statements<\/span>\r\nEXCEPTION<\/span>\r\n   -- handle exeception<\/span>\r\n\r\nEND<\/span> [procedure_name];<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
PL\/SQL procedure header #<\/a><\/h3>\r\n\r\n\r\n\r\n
A procedure begins with a header that specifies its name and an optional parameter list.<\/p>\r\n\r\n\r\n\r\n
Each parameter can be in either IN<\/code>, OUT<\/code>, or INOUT<\/code> mode.<\/p>\r\n\r\n\r\n\r\n
The parameter mode determines if the procedure can access its initial value or modify its value:<\/p>\r\n\r\n\r\n\r\n