According Oracle.com website
Procedures and functions are named PL/SQL blocks. Having names bestows them with certain advantages: they can be stored within the database, which means they can be reused easily; they can call each other; and they can accept parameters that modify their behaviour.

A procedure is a named PL/SQL block that carries out one or more actions.
A function is a named PL/SQL block that returns a value.

Syntax

CREATE [OR REPLACE] PROCEDURE <procedure_name> [parameter(s)] AS
[Variable declaration SECTION]
BEGIN
<Statements>
END [<procedure_name>];

You’ll notice that the structure of a stored procedure is pretty much identical to that of an anonymous block; the only differences are that stored procedures must be given a name, can accept parameters, and they dispense with the necessity of the keyword DECLARE at the start of the declaration section.

Here’s the syntax for functions.

CREATE [OR REPLACE] FUNCTION <function_name> [parameter(s)] RETURN <datatype> AS
[variable declaration SECTION]
BEGIN
<Statements>
END [<function_name>];

Example of creating a PL/SQL procedure

CREATE OR REPLACE PROCEDURE print_email(
in_student_id NUMBER
)
IS
row_student students%ROWTYPE;
BEGIN
-- get contact based on student id
SELECT *
INTO row_student
FROM students
WHERE student_id = p_student_id;

-- print out contact's information
dbms_output.put_line( row_student.first_name || ' ' ||
row_student.last_name || '<' || row_student.email ||'>' );

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;

Executing a PL/SQL Procedure

The syntax for running a PL/SQL procedure is:

EXECUTE procedure_name( arguments);

EXEC procedure_name( arguments);

For example, in our case, we have to run this one:

EXECUTE print_email(3);

Functions vs procedures in Oracle

What is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions?

A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results.
You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.
A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.

Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE while procedures can't.

- A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.
- Functions can be used in select or update or delete statement while procedure can't.
- Functions are normally used for computations where as procedures are normally used for executing business logic.
- A Function returns 1 value only. Procedure can return multiple values.
- Stored procedure is precompiled execution plan where as functions are not.

Example procedure with SELECT

%ROWCOUNT Attribute: How Many Rows Fetched So Far?

When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.

DECLARE
CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---');
END IF;
END LOOP;
CLOSE c1;
END;
/

%NOTFOUND Attribute: Has a Fetch Failed?

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/

From: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm//

DBMS_SQL.DEFINE_COLUMN

In functions and procedures we can have the situation when we should parse the whole table or what we get back from a sql query:

CREATE OR REPLACE PROCEDURE DynamicQuery (
2 p_Major1 IN lecturer.major%TYPE DEFAULT NULL,
3 p_Major2 IN lecturer.major%TYPE DEFAULT NULL) AS
4
5 v_CursorID INTEGER;
6 v_SelectStmt VARCHAR2(500);
7 myFirstName lecturer.first_name%TYPE;
8 v_LastName lecturer.last_name%TYPE;
9 v_Major lecturer.major%TYPE;
10 v_Dummy INTEGER;
11
12 BEGIN
13 v_CursorID := DBMS_SQL.OPEN_CURSOR;
14
15 v_SelectStmt := 'SELECT first_name, last_name, major
16 FROM lecturer
17 WHERE major IN (:m1, :m2)
18 ORDER BY major, last_name';
19
20 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
21
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_Major1);
23 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m2', p_Major2);
24
25 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, myFirstName, 20);
26 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);
27 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30);
28
29 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
30
31 LOOP
32 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
33 EXIT;
34 END IF;
35
36 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, myFirstName);
37 DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);
38 DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major);
39
40 INSERT INTO MyTable (char_col)VALUES (myFirstName || ' ' || v_LastName || ' is a ' ||v_Major || ' major.');
41 END LOOP;
42
43 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
44
45 COMMIT;
46 EXCEPTION
47 WHEN OTHERS THEN
48 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
49 RAISE;
50 END DynamicQuery;
51 /

WikkaWiki : SQL OracleProceduresFunctions

Oracle PL/SQL

Procedures and functions

Definitions