Function
Sysdate is an example of a function triggered by the sysdate system date.
dbms_output.put_line
A function retrieves a value. (SELECT) generally carries out select operations within functions. It requests a set of ids and returns the corresponding names.
Example, return the Turkish date:
Create or Replace Function: Represented as a c or an r in the view. Privileges are removed if this is dropped.
This is why create or replace is preferred, as the code can change but the privileges remain.
1 2 3 4 5 6 7 8 |
create or replace function get_turkish_date return varchar2 is date varchar2 (15); begin select to_char(sysdate, 'DD/MM/YYYY') into date from dual; return date; end; |
Can be run in toad’s editor with “run as script”
1 |
select get_turkish_date from dual; |
This function works on every table, it returns as many results as there are rows in a table
1 |
select get_turkish_date from emp; |
Returns a single result from dual. Alternatively, create a table called single_row so that there would only be a single line entry.
Example: Change to upper case
1 2 3 4 5 6 7 8 9 10 11 12 |
create or replace function change_upper_case (p_word varchar2) return varchar2 is upper_case_word varchar2(100); begin upper_case_word := upper(p_word); return upper(p_word); end; select change_upper_case('mehmet') from dual; select first_name, change_upper_case(first_name) from emp; -- creates a column next to the functions and changes them to upper case. select change_upper_case(first_name) from emp ; -- function changes to upper case. applying a function to first_name. create public synonym bhc for change_upper_case; a hr user may not have the necessary privileges. grant create public synonym to hr; create synonym bhc for change_upper_case; creating a synonym specifically for or granting privileges to hr users |
Where the functions are used: The set name, so that a user can give an id to get its name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
create or replace function get_dept_name(p_deptno number) return varchar2 is dept_name varchar2(30); begin select department_name into dept_name from departments where department_id = p_deptno; return dept_name; exception when others then return SQLERRM; end; select get_dept_name (100) from dual; select get_dept_name (108) from dual; -- as it doesn't contain any exception handling, it will return an error when there isnt any data create or replace function get_dept_name(p_deptno number) return varchar2 is dept_name varchar2(30); begin select department_name into dept_name from departments where department_id = p_deptno; return dept_name; exception when no_data_found then return p_deptno || ' no set with entered number ' ; end; Outputs an error such as "There is no set number 108"a |
Automatic Storage Management...
12 March 2019