Creating Stored Procedures With SQL CREATE PROCEDURE

Previous

Next

The SQL CREATE PROCEDURE statement lets you create stored procedures that are stored in the database. These stored (schema level) subprograms can be accessed from SQL. You can use the optional OR REPLACE clause to modify an existing procedure without first dropping the procedure.

Example: Creating a Simple Stored Procedure is an example of a simple stored procedure that displays current date.

CREATE OR REPLACE PROCEDURE today_is AS
BEGIN
-- display the current system date in long format
  DBMS_OUTPUT.PUT_LINE( 'Today is ' || TO_CHAR(SYSDATE, 'DL') );
END today_is;
/
-- to call the procedure today_is, you can use the following block
BEGIN
  today_is(); -- the parentheses are optional here
END;
/