Function is pl/sql.

Syntax of Function :

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

here we have a customer table :

SQL> select *from customer;

     AC_NO       B_NO NAME       CITY          BALANCE      COUNT CHK_DATE
---------- ---------- ---------- ---------- ---------- ---------- ---------
       111        101 biku       surat            7990          1 02-SEP-19
       112        103 sunil      vapi            11000          1 02-SEP-19
       113        102 balram     valsad          19000          3 01-JAN-19
       114        104 jenna      surat           18000          2 01-JAN-19
       115        105 sagar      mumbai           9000          1 01-JAN-19
       116        101 chirag     surat             800          3 01-JAN-19
       117        102 aditya     vapi              500          5 01-JAN-19
       118        103 rahul      valsad          14000          2 01-JAN-19
       119        103 bhavesh    mumbai           5000          1 01-JAN-19
       120        104 jay        surat             400          4 01-JAN-19
       121        105 bhola      vapi            11000          4 01-JAN-19

     AC_NO       B_NO NAME       CITY          BALANCE      COUNT CHK_DATE
---------- ---------- ---------- ---------- ---------- ---------- ---------
       122        101 sima       valsad           8000          2 01-JAN-19
       123        102 montu      surat            9000          2 01-JAN-19

13 rows selected.

then program of function to Return the name of customer input by the user id.

create or replace function get_name(p_id int) return varchar2 is
 m_name varchar2(20);
begin
 select name into m_name from customer where ac_no=p_id;

 return m_name;
end;
/

Here remember that the function is calling by Select statement. result :

SQL> select get_name(111) from dual;

GET_NAME(111)
----------------
biku

this Article is written by CHIRAG PATIL if you like this Article then comment to him





If you like code with vibhu  and would like to contribute, you can also write an article using   This link :

https://docs.google.com/forms/d/e/1FAIpQLScAmvlPvNUz35R-G0nc_zpRVP3o8xlhtFgC3aKPyLetX_RyXg/viewform?pli=1


OR  mail your article to codewithvibhu@gmail.com.  See your article appearing on the code with vibhu main page and help other students. 

❮ Previous                                                      Next ❯