Creating functions in Oracle

A function is a named PL/SQL block or a subprogram. A function must return a value back to the caller. A functions can be stored in the database as a schema object for a repeated execution. Function is called as part of a SQL expression or as part of a PL/SQL expression.

Syntax of stored procedure

CREATE OR REPLACE FUNCTION Function_Name

(parameter_name1 IN datatype1,

  parameter_name2 IN datatype2,

  .......................................................................)

RETURN datatype
IS/AS

          variable declarations;

          constant declarations;

BEGIN

         PL/SQL statements block;

EXCEPTION

        exception PL/SQL block;

END;

NOTE:

1. You can only use IN parameter.

2. Variable and constant declarations is optional.

3. Exception is also an optional statement in function.

Example#1
Step 1 : Creating a function to display "Hello World".

SQL> CREATE OR REPLACE FUNCTION func_DisplayMsg

             RETURN varchar2
             IS

                    v_name varchar2(15);

             BEGIN
                    v_name:='Hello World';

                    return v_name;
             END;
             /

Step 2 : How to execute or call a function.

SQL> SET SERVEROUTPUT ON

NOTE : On Set serveroutput to display message on sql prompt before executing function.

SQL> DECLARE

                    msg varchar2(15);

            BEGIN
                    msg:=func_DisplayMsg();

                    dbms_output.put_line(msg);
             END;
             /

Result : Hello World

Remove Oracle 10g manually from windows 8.1

Step - 1 : Open Registry Editor (regedit.exe).

Step - 2 : Remove Oracle key from Services folder.

Step - 3 : Remove Oracle key from Wow6432Node folder.

Step - 4 : Restart your PC and remove folder where Oracle is installed. Oracle 10g is generally installed with the name ORACLE. 

Step - 5 : Remove oracle folder from Program Files or Program Files(x86) folder. 

Note : Sometimes you need not to worry about step 4 & 5. once you removed oracle registry, Oracle folder is automatically removed but it is better practice to check at least once whether oracle folder is deleted automatically or not.

Oracle stored procedure returning a Cursor

#Example
Step 1 : create a sample table to execute procedure.
Step 2 : create procedure returning ref cursor in out parameter.
Step 3 : Execute stored procedure.

#Step 1 :Create Sample table and insert few values.

SQL> CREATE TABLE LoginInfo

           (

                LogID number,

                LogDateTime date,

                UserName varchar2(10)

            );

SQL> INSERT INTO LoginInfo VALUES(1,TO_DATE('01/01/2015 20:10:40','dd/mm/yyyy hh24:mi:ss'),'USER_A');

SQL> INSERT INTO LoginInfo VALUES(2,TO_DATE('02/01/2015 09:15:20','dd/mm/yyyy hh24:mi:ss'),'USER_A');

SQL> INSERT INTO LoginInfo VALUES(3,TO_DATE('02/01/2015 10:09:00','dd/mm/yyyy hh24:mi:ss'),'USER_B');

SQL> INSERT INTO LoginInfo VALUES(4,TO_DATE('02/01/2015 12:10:31','dd/mm/yyyy hh24:mi:ss'),'USER_A');

SQL> INSERT INTO LoginInfo VALUES(5,TO_DATE('03/01/2015 11:30:05','dd/mm/yyyy hh24:mi:ss'),'USER_A');

SQL> INSERT INTO LoginInfo VALUES(6,TO_DATE('03/01/2015 12:40:07','dd/mm/yyyy hh24:mi:ss'),'USER_B');
NOTE : TO_DATE function is used to insert time values with date.

SQL>SELECT * FROM loginInfo;

LOGID	LOGDATETIME	USERNAME
1	01-01-2015 20:10:40	USER_A
2	02-01-2015 09:15:20	USER_A
3	02-01-2015 10:09:00	USER_B
4	02-01-2015 12:10:31	USER_A
5	03-01-2015 11:30:05	USER_A
6	03-01-2015 12:40:07	USER_B

#Step 2 :Create Procedue returning Ref Cursor.

CREATE OR REPLACE PROCEDURE Proc_forCursor(

p_cursor OUT SYS_REFCURSOR, p_user IN LoginInfo.UserName%TYPE)

IS

BEGIN

  OPEN p_cursor FOR

  SELECT * FROM LoginInfo WHERE UserName = p_user;

END;

/

#Step 3 : Execute stored procedure.

DECLARE

  v_userCursor SYS_REFCURSOR;

  v_table LoginInfo%ROWTYPE;

BEGIN

  proc_forCursor(v_userCursor,'USER_A');

  LOOP

    FETCH v_userCursor INTO v_table;

    EXIT WHEN v_userCursor%NOTFOUND;

    dbms_output.put_line(v_table.LogID ||'  '|| v_table.USERNAME);

  END LOOP;

  CLOSE v_userCursor;

END;

/

Result : 
1 USER_A
2 USER_A
4 USER_A
5 USER_A

Oracle Stored Procedure for SELECT Statement.

Example#1 : Procedure for SELECT Statement using dual table.

SQL> CREATE OR REPLACE PROCEDURE proc_SelectDual

           IS
                vName varchar2(100);

           BEGIN

              SELECT 'Hello World' INTO vName  from dual;
              DBMS_OUTPUT.PUT_LINE(vName);

           END;

           /
SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_SelectDual;
result : Hello World

Example#2 : 
Step#1 : Create table & insert few values.

SQL> CREATE TABLE Stu_Info

            (

                 stuid number,

                 name varchar2(10),

                 city varchar2(10),

                 amount number

            );

SQL> Insert into Stu_Info values(1000,'RAM','NEW DELHI',1000);

SQL> Insert into Stu_Info values(1001,'SACHIN', 'MUMBAI',2000);

SQL> Insert into Stu_Info values(1002,'SMITH','NEW YORK',3000);

SQL> Insert into Stu_Info values(1003,'CHENG', 'BEIJING',4000);

SQL> SELECT * from Stu_Info;

STUID	NAME	CITY	AMOUNT
1000	RAM	NEW DELHI	1000
1001	SACHIN	MUMBAI	2000
1002	SMITH	NEW YORK	3000
1003	CHENG	BEIJING	4000

Step#2 : Create procedure for SELECT INTO using OUT Parameter.

SQL> CREATE OR REPLACE PROCEDURE proc_SelectStuInfo

          ( p_stuid IN Stu_info.stuid%TYPE,

             p_name OUT Stu_info.name%TYPE,

             p_city OUT Stu_info.city%TYPE,

             p_amount OUT Stu_info.amount%TYPE)

           IS

           BEGIN

                  SELECT     name, city, amount 
                       INTO     p_name, p_city, p_amount
                     FROM     Stu_info
                  WHERE    stuid = p_stuid;

           END;

           /

Step#3 : Execute Procedure 

SQL> DECLARE
                     p_name Stu_info%TYPE;
                     p_city Stu_info%TYPE;
                     p_amount Stu_info%TYPE;
            BEGIN
             proc_SelectStuInfo(1000,p_name,p_city,p_amount);
              DBMS_OUTPUT.PUT_LINE('Name : ' || p_name);
              DBMS_OUTPUT.PUT_LINE('City : ' || p_city);
              DBMS_OUTPUT.PUT_LINE('Amount : ' || p_amount);
            END;
                  /

Result : Name      : RAM
               City         : NEW DELHI
               Amount : 1000

SQL Statements in oracle

SQL Statements can be classified as follows.

SQL Statements
Data Definition Language (DDL) Database Structure can be created or alter using DDL Statements. Create, alter, drop, rename and truncate are few of them. DDL Statements are Auto Commit.	CREATE ALTER DROP RENAME TRUNCATE
Data Manipulation Language (DML) Once the database structured is created you can insert, update, delete or merge data. DML Statements manipulate table's data.	INSERT UPDATE DELETE MERGE
Data Retrieval Language (DRL) Data Retrieval language retrieves information from tables.	SELECT
Transaction Control Language (TCL) Transaction control language controls the transactions.	COMMIT ROLLBACK SAVEPOINT
Data Control Language (DCL) Data Control Language is used for permission.	GRANT REVOKE

How to use COMMIT in Oracle stored procedure

EXAMPLE  ( COMMIT Statement in stored procedure)

# Step 1 : Create a table & Insert rows by Stored Procedure

SQL> CREATE TABLE Stu_Info

            (

                 stuid number,

                 name varchar2(10),

                 city varchar2(10),

                 amount number

            );

#Step2 : Create Procedure p_insertStuInfo to insert record into table.

SQL> CREATE OR REPLACE PROCEDURE p_InsertStuInfo

          ( p_stuid IN number,

             p_name  IN VARCHAR2,

             p_city  IN VARCHAR2,

             p_amount IN NUMBER)

           IS

           BEGIN

              INSERT INTO Stu_info
                 values(p_stuid,p_name,p_city,p_amount);
              COMMIT;

           END;

           /

Step 3 : Execute Procedure multiple times with different values.

SQL> EXEC p_insertStuInfo(1000,'RAM','NEW DELHI',1000);

SQL> EXEC p_insertStuInfo(1001,'SACHIN','MUMBAI',2000);

SQL> EXEC p_insertStuInfo(1002,'SMITH','NEW YORK',3000);

SQL> EXEC p_insertStuInfo(1003,'CHENG','BEIJING',4000);

Setp 4 : Check table records.

SQL> SELECT * from Stu_Info;

STUID	NAME	CITY	AMOUNT
1000	RAM	NEW DELHI	1000
1001	SACHIN	MUMBAI	2000
1002	SMITH	NEW YORK	3000
1003	CHENG	BEIJING	4000

Setp 5 : Execute ROLLBACK & recheck table's data.

SQL> ROLLBACK;

SQL> SELECT * from Stu_Info;

STUID	NAME	CITY	AMOUNT
1000	RAM	NEW DELHI	1000
1001	SACHIN	MUMBAI	2000
1002	SMITH	NEW YORK	3000
1003	CHENG	BEIJING	4000

ROLLBACK doesn't affect the table's data because whenever we execute procedure it will call COMMIT and it will save subsequent data permanently. if you are using DML statements like INSERT,UPDATE or DELETE in procedure then must use COMMIT statements inside the procedure.