Oracle's Custom functions

A function in the database contains four parts: declaration, return value, function body, and exception handling.

2. --Functions with no parameters
4. Create or Replace function Get_user return VARCHAR2 is
6. V_user VARCHAR2 (50);
8. Begin
10. Select username to V_user from User_users;
12. return v_user;
14. End Get_user;
18. --Test
20. Method One
22. Select Get_user from dual;
26. Method Two
28. sql> var v_name varchar2 (50)
30. Sql> Exec:v_name:=get_user;
34. The PL/SQL process has completed successfully.
38. sql> Print V_name
42. V_name
44. ------------------------------
46. TEST
50. Method Three
52. sql> exec dbms_output.put_line (' Current database user is: ' | | Get_user);
54. The current database user is: TEST
58. The PL/SQL process has completed successfully.

• --Functions with in parameters
• Create or Replace function Get_empname (v_id in number) return VARCHAR2 as
• V_name VARCHAR2 (50);
• Begin
• Select name into V_name from employee where id = v_id;
• return v_name;
• exception
• When No_data_found Then
• Raise_application_error (-20001, ' The ID you entered is invalid! ‘);
• End Get_empname;

Report:

Function call Restrictions
1, the SQL statement can only call the storage function (server side), and cannot call the client's function
2, SQL can only call with input parameters, not with output, input and output functions
3. SQL cannot use the unique data type (Boolean,table,record, etc.)
4. A function called in an SQL statement cannot contain insert,update and DELETE statements

View function Yard Source code
Oracle stores the function name and its source code information in a data dictionary User_source
Select text from User_source where name= ' get_empname ';

Delete a function
Drop function Get_empname;

Http://www.cnblogs.com/1848/articles/1828927.html

Oracle's Custom functions