Stored Procedures and Functions are sometimes referred to as Stored Procedures. The difference between Stored Procedures and Functions is simple. Functions have to return values, Stored Procedures do not return anything. They just do stuff.
Stored Procedures do not execute DDL Statements. Creating database objects like tables at runtime is a bad idea since this should be defined by a datamodel. If you try to create a Procedure with DDL Statements it will not compile.
The below will print the message 'Hello World' in a console window of sql developer.
Copy paste the below source code into a fresh SQL worksheet and hit save or Ctrl. + Return. This will compile and execute the script.
The part create or replace procedure creates a new stored procedure with the given name, which in this example is say_hello_world.
The table dual is an Oracle dummy table, which is available in every database.
After the script has been successfully executed, you can see a new Stored Procedure in your connection explorer.
Also the dbms_output is displayed in a console below the source code editor.
The minmum syntax for creating a stored procedure in Oracle PL SQL is as follows.