Bolte IT Consulting

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.

PL SQL

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.

create or replace procedure say_hello_world is
  msg VARCHAR(255);
begin
  select 'Hello World' into msg from dual;
  dbms_output.put_line(msg);
end; 

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.

create or replace procedure <name_of_procedure> is
  <variables>
begin
  <code execution>
end;

References

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#i1006378