Stored Procedures are essentially programs that are stored in DBMS that carry out a series of steps. A procedure is introduced by the keywords, CREATE PROCEDURE, followed by the procedure name and its parameters. A parameter, for its part, is a value that is sent into the procedure. There can be any number of parameters, each followed by a mode and a data type.
 The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). That is, IN parameters cannot be changed while OUT parameters can only be set. In the type area, the size of the field should not be specified the way it is done in the CREATE TABLE statement. The actual length of a parameter depends on the value that is passed in when the procedure is called.

SQL> CREATE TABLE T2 (column1 INTEGER, column2 CHAR (10));
Table created.
SQL> CREATE PROCEDURE addrow (i IN NUMBER) AS
    BEGIN
   INSERT INTO T2 VALUES (i, 'sampledata');
   END addrow;
run;
    /
Procedure created.
Functions

A function is similar to an operator in operation.  A function is a name that performs a specific task.  A function may or may not take values (arguments) but it always returns a value as the result. If function takes values then these values are to be given within parentheses after the function name.
The following is the general format of a function.

function [(argument-1, argument-2…)]

Types of functions

Functions are classified based on the type of data on which they perform the operation. The following are the different types of functions available in Oracle.

1. Arithmetic Functions.
2. Date & Time functions.
3. String functions.
4. Conversion functions.
5. Miscellaneous functions.
6. Group functions.

Arithmetic Functions

Arithmetic functions take numbers and perform arithmetic operations.

SQL> select mod (10, 4) from dual;

 MOD (10, 4)
----------
         2
SQL> select round (1047.785, 2), trunc (1047.785, 2) from dual;

ROUND (1047.785, 2) TRUNC (1047.785, 2)
----------------- -----------------
          1047.79           1047.78

Date & Time functions
Date Functions

Date functions operate on values of DATE data type. Except  MONTHS_BETWEEN all date functions return DATE data type.

SQL> Select sysdate, add_months(sysdate,2) from dual;

SYSDATE   ADD_MONTH
--------- ---------
09-AUG-08 09-OCT-08

SQL> select add_months('31-aug-2001',1) from dual;

ADD_MONTH
---------
30-SEP-01

Time Functions

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss')  GMT ,
  2            to_char(  new_time(sysdate,'GMT','AST'),'dd-mm-yyyy hh24:mi:ss')  AST
  3  from dual

GMT                 AST
------------------- -------------------
09-08-2008 16:47:17 09-08-2008 12:47:17

SQL>

String functions
String functions are functions that manipulate a set of characters. A set of characters is a string. For example, the name of the company, the address of a person all these are examples of a string.  CHAR and VARCHAR data types contain strings.
SQL> select name, duration from courses where  name like '%programming%';

NAME                   DURATION
-------------------- ----------
C programming                20
XML programming              10
Java programming             30

INITCAP converts first letter of each word to capital and remaining letters to lowercase.

SQL> select initcap('this IS to Test INITCAP') Result from  dual;

RESULT
-----------------------
This Is To Test Initcap

Conversion functions
          Conversion functions are used to convert a value from one data type into another. These functions are not required if Oracle can automatically convert the value. But there are cases where these conversion functions are required to convert the value to the required data type.

In the example below, a DATE given in CHAR format is converted to DATE before LAST_DAY function is applied.

SQL> select  last_day('20-aug-2001') from dual;

LAST_DAY(
---------
31-AUG-01

SQL>
Similarly it is possible to use a CHAR value where a NUMBER is required, as shown below.

SQL> select 5 * '20' from dual;

    5*'20'
----------
       100

SQL>

Miscellaneous functions
This function works like a multiple IF statement or a CASE/SWITCH statement in a typical programming language. It takes a value and compares it with the given values one by one. Wherever the value is equivalent to the given value it returns the corresponding value.

GREATEST and LEAST functions

These functions take a collection of values and return a single value which is either the least or greatest of the given values as the case may be. GREATEST is used to return the largest of the given values and LEAST the smallest of the given values. The following example shows the discount to be given to each course. The scheme is to given discount of 10% on the course fee or 500 whichever is higher. The following query with GREATEST function will achieve the result.

SQL> select  ccode, name, greatest( Discount * 0.10,500) fee from courses;
 
     CCODE NAME                        FEE
---------- -------------------- ----------
       111 .net programming            500
       112 C++ programming             500

SQL>

SQL> select  ccode, name, least( Discount * 0.10,500) fee from courses;

    CCODE NAME                        FEE
--------- -------------------- ----------
      111 .net programming            100
      112 C++ programming             200
SQL>

           
Group functions
Group functions are the functions that are used to perform operations on groups. If DISTINCT option is used then only distinct (unique) values are taken into account.  The following query is used to count the number of students who have paid some amount.

SQL> select count(name) from courses;

COUNT(NAME)
-----------
          5
SQL>   

But the count includes the duplicates.

SQL> select count(distinct name) from courses
COUNT(DISTINCTNAME)
-------------------