Oracle PL/SQL/Stored Procedure Function/Function Return

Append result from generator function to a table

    
SQL>
SQL>
SQL> create table gift(
  2   gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12  )
 13  storage(initial 50m);
Table created.
SQL>
SQL> alter table gift
  2  add constraint gift_pk primary key(gift_id);
Table altered.
SQL>
SQL> analyze table gift compute statistics;
Table analyzed.
SQL>
SQL>
SQL> create or replace type gift_type as object (
  2   gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12   );
 13  /
SQL>
SQL> create or replace type gift_table as table of gift_type;
  2  /
Type created.
SQL>
SQL> create or replace function gift_generator(p_num_rows in number)
  2     RETURN gift_table
  3  AS
  4      v_gift_table     gift_TABLE := gift_table();
  5  BEGIN
  6
  7      for i in 1..p_num_rows loop
  8          v_gift_table.EXTEND;
  9          v_gift_table(i) := ( gift_type(i,i,sysdate,0,sysdate,null,"CA",1,null,null ));
 10
 11      end loop;
 12
 13      return v_gift_table;
 14
 15  END;
 16  /
Function created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
  2      v_start  number;
  3      v_run1    number;
  4  begin
  5      v_start := dbms_utility.get_time;
  6      insert /*+APPEND */ into gift (select * from table(gift_generator(100) ) );
  7      v_run1 := dbms_utility.get_time - v_start;
  8      dbms_output.put_line("no pipe "||v_run1);
  9  end;
 10  /
no pipe 5
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table gift;
Table dropped.
SQL>

Concatenates two strings into one:

   
SQL>
SQL> 
SQL> CREATE OR REPLACE FUNCTION join_strings(string1 VARCHAR2, string2 VARCHAR2 ) RETURN VARCHAR2 IS
  2  BEGIN
  3    RETURN string1 ||" "|| string2||".";
  4  END;
  5  /
Function created.
SQL>

Function return Integer

   
SQL>
SQL> -- The WHILE loop as part of a function.
SQL>
SQL> CREATE OR REPLACE FUNCTION noSpaceF(p_pass_string VARCHAR2)
  2        RETURN NUMBER IS
  3            counter INTEGER := 1;
  4            v_return NUMBER := 0;
  5  BEGIN
  6        WHILE counter <= LENGTH(p_PASS_STRING) LOOP
  7             IF SUBSTR(p_PASS_STRING,counter,1) != " " THEN
  8                  v_return := v_return + 1;
  9             ELSE
 10                  NULL;
 11             END IF;
 12             counter := counter + 1;
 13        END LOOP;
 14        RETURN v_return;
 15  END noSpaceF;
 16  /
Function created.
SQL>
SQL> select noSpaceF("www.sqle.ru www.sqle.ru") from dual;
NOSPACEF("WWW.sqle.ruWWW.sqle.ru")
----------------------------------------
                                      28

Multiple RETURN Statements

    
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION ClassInfo (
  2    p_Department session.department%TYPE,
  3    p_Course     session.course%TYPE)
  4    RETURN VARCHAR2 IS
  5
  6    studentCount NUMBER;
  7    studentMax     NUMBER;
  8    v_PercentFull     NUMBER;
  9  BEGIN
 10    SELECT current_lecturer, max_lecturer
 11      INTO studentCount, studentMax
 12      FROM session
 13      WHERE department = p_Department
 14      AND course = p_Course;
 15
 16    v_PercentFull := studentCount / studentMax * 100;
 17
 18    IF v_PercentFull = 100 THEN
 19      RETURN "Full";
 20    ELSIF v_PercentFull > 80 THEN
 21      RETURN "Some Room";
 22    ELSIF v_PercentFull > 60 THEN
 23      RETURN "More Room";
 24    ELSIF v_PercentFull > 0 THEN
 25      RETURN "Lots of Room";
 26    ELSE
 27      RETURN "Empty";
 28    END IF;
 29  END ClassInfo;
 30  /
Function created.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>

Return a type

    
SQL>
SQL> CREATE TABLE a_table(COL1 NUMBER);
Table created.
SQL>
SQL> CREATE or replace TYPE debug_o AS OBJECT ( string VARCHAR2(100) );
  2  /

SQL> CREATE or replace TYPE debug_t AS TABLE OF debug_o;
  2  /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION tracer RETURN debug_t AS
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3    v_trace debug_t := debug_t();
  4  BEGIN
  5    v_trace.EXTEND;
  6    v_trace(v_trace.LAST) := debug_o("Started Insert At " || TO_CHAR(SYSDATE,"HH24:MI:SS"));
  7    INSERT INTO a_table VALUES(1);
  8    COMMIT;
  9    v_trace.EXTEND;
 10    v_trace(v_trace.LAST) := debug_o("Completed Insert At " || TO_CHAR(SYSDATE,"HH24:MI:SS"));
 11    RETURN(v_trace);
 12  END;
 13  /
Function created.
SQL>
SQL> SELECT * FROM TABLE(tracer)
  2  /
STRING
----------------------------------------------------------------------------------------------------
Started Insert At 19:30:39
Completed Insert At 19:30:39
SQL>
SQL> drop table a_table;
Table dropped.
SQL>

Return a varray from a function

   

SQL>
SQL> create table gift(
  2   gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12   )
 13  storage(initial 50m);
Table created.
SQL>
SQL> alter table gift
  2  add constraint gift_pk primary key(gift_id);
Table altered.
SQL>
SQL> analyze table gift compute statistics;
Table analyzed.
SQL>
SQL> create or replace type gift_type as object
  2   ( gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12   );
 13  /

SQL>
SQL> create or replace type gift_table as table of gift_type;
  2  /
Type created.
SQL>
SQL> create or replace function gift_generator_piped
  2     (p_num_rows in number)
  3     RETURN gift_table
  4     PIPELINED
  5  AS
  6  BEGIN
  7
  8      for i in 1..p_num_rows loop
  9          PIPE ROW ( gift_type(i,i,sysdate,0,sysdate,null,"CA",1,null,null ));
 10      end loop;
 11
 12      return;
 13
 14  END;
 15  /
Function created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
  2      v_start  number;
  3      v_run1    number;
  4  begin
  5      v_start := dbms_utility.get_time;
  6      insert /*+APPEND */ into gift (select * from table(gift_generator_piped(1000) ) );
  7      v_run1 := dbms_utility.get_time - v_start;
  8      dbms_output.put_line("piped "||v_run1);
  9
 10  end;
 11  /
piped 2
PL/SQL procedure successfully completed.
SQL> drop table gift;
Table dropped.
SQL>

RETURN statement.

    
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION ClassInfo(
  2    p_Department session.department%TYPE,
  3    p_Course     session.course%TYPE)
  4    RETURN VARCHAR2 IS
  5
  6    studentCount NUMBER;
  7    studentMax     NUMBER;
  8    v_PercentFull     NUMBER;
  9  BEGIN
 10    SELECT current_lecturer, max_lecturer
 11      INTO studentCount, studentMax
 12      FROM session
 13      WHERE department = p_Department
 14      AND course = p_Course;
 15
 16    v_PercentFull := studentCount / studentMax * 100;
 17
 18    IF v_PercentFull = 100 THEN
 19      RETURN "Full";
 20    ELSIF v_PercentFull > 80 THEN
 21      RETURN "Some Room";
 22    ELSIF v_PercentFull > 60 THEN
 23      RETURN "More Room";
 24    ELSIF v_PercentFull > 0 THEN
 25      RETURN "Lots of Room";
 26    ELSE
 27      RETURN "Empty";
 28    END IF;
 29  END ClassInfo;
 30  /
Function created.
SQL>
SQL>
SQL> drop table session;
Table dropped.

Return value from a function

   
SQL> -- The mypi function.
SQL> CREATE OR REPLACE FUNCTION mypi
  2      RETURN NUMBER IS
  3  BEGIN
  4       NULL;
  5       RETURN 3.14;
  6  END mypi;
  7  /
Function created.
SQL>
SQL> -- Verifying the mypi function.
SQL> BEGIN
  2       DBMS_OUTPUT.PUT_LINE("value of pi is " || mypi());
  3  END;
  4  /
value of pi is 3.14
PL/SQL procedure successfully completed.
SQL>

Return varchar2 value from function

    
SQL>
SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
  5    RETURN p1;
  6  END CallFunc;
  7  /
Function created.
SQL> VARIABLE v_Output VARCHAR2(50);
SQL> CALL CallFunc("Hello!") INTO :v_Output;
CallFunc called with Hello!
Call completed.
SQL> PRINT v_Output
V_OUTPUT
--------------------------------------------------------------------------------
Hello!

Save the returning value from a procedure to a variable

    
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
  5    RETURN p1;
  6  END CallFunc;
  7  /
Function created.
SQL>
SQL> VARIABLE v_Output VARCHAR2(50);
SQL> CALL CallFunc("Hello!") INTO :v_Output;
CallFunc called with Hello!
Call completed.
SQL> PRINT v_Output
V_OUTPUT
--------------------------------------------------------------------------------
Hello!
SQL>
SQL>

This script demonstrates using a record type as a function return value

    
SQL>
SQL>
SQL> CREATE TABLE emp
  2   (emp_id              INTEGER             NOT NULL
  3   ,fname               VARCHAR2(30 CHAR)   NOT NULL
  4   ,mid_name           VARCHAR2(1 CHAR)
  5   ,lname                VARCHAR2(30 CHAR)   NOT NULL
  6   ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));
Table created.
SQL>
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL>
SQL> 
SQL> DECLARE
  2     TYPE emp_record IS RECORD (emp_id INTEGER,fname VARCHAR2(30 CHAR),mid_name  VARCHAR2(1 CHAR),lname VARCHAR2(30 CHAR));
  3
  4     emp emp_RECORD;
  5
  6     FUNCTION get_row (emp_id_in INTEGER)
  7     RETURN emp_RECORD IS
  8       CURSOR c (emp_id_cursor INTEGER) IS SELECT * FROM emp WHERE emp_id = emp_id_cursor;
  9
 10     BEGIN
 11       FOR i IN c(emp_id_in) LOOP
 12         RETURN i;
 13       END LOOP;
 14
 15     END get_row;
 16
 17   BEGIN
 18     emp := get_row(1);
 19     dbms_output.put_line(CHR(10));
 20     dbms_output.put_line("emp_ID  : "||emp.emp_id);
 21     dbms_output.put_line("fname     : "||emp.fname);
 22      dbms_output.put_line("mid_name : "||emp.mid_name);
 23     dbms_output.put_line("lname      : "||emp.lname);
 24
 25   END;
 26   /
DECLARE
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at line 15
ORA-06512: at line 18

SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>

Use function return value in select statement

   

SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   Employee"s Fi LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason         Martin     25-JUL-96 25-JUL-06  $1,234.56 Toronto    Programmer
02   Alison        Mathews    21-MAR-76 21-FEB-86  $6,661.78 Vancouver  Tester
03   James         Smith      12-DEC-78 15-MAR-90  $6,544.78 Vancouver  Tester
04   Celia         Rice       24-OCT-82 21-APR-99  $2,344.78 Vancouver  Manager
05   Robert        Black      15-JAN-84 08-AUG-98  $2,334.78 Vancouver  Tester
06   Linda         Green      30-JUL-87 04-JAN-96  $4,322.78 New York   Tester
07   David         Larry      31-DEC-90 12-FEB-98  $7,897.78 New York   Manager
ID   Employee"s Fi LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
08   James         Cat        17-SEP-96 15-APR-02  $1,232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> -- Cursor without parameters (simplest)
SQL>     CREATE OR REPLACE Function FindEmployeeSalary ( name_in IN varchar2 ) RETURN number
  2      IS
  3          mySalary number;
  4
  5          CURSOR c1
  6          IS
  7             SELECT salary
  8              from employee
  9              where first_name = name_in;
 10
 11      BEGIN
 12
 13          open c1;
 14          fetch c1 into mySalary;
 15
 16          if c1%notfound then
 17               mySalary := 0;
 18          end if;
 19
 20          close c1;
 21
 22      RETURN mySalary;
 23
 24      END;
 25      /
Function created.
SQL>
SQL>     select FindEmployeeSalary(first_name) from employee;
FINDEMPLOYEESALARY(FIRST_NAME)
------------------------------
                       1234.56
                       6661.78
                       6544.78
                       2344.78
                       2334.78
                       4322.78
                       7897.78
FINDEMPLOYEESALARY(FIRST_NAME)
------------------------------
                       6544.78
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>

Use function to check passwords

    
SQL>
SQL> CREATE OR REPLACE FUNCTION myFunction(in_username IN VARCHAR,in_new_password IN VARCHAR,in_old_password IN VARCHAR)
  2  RETURN BOOLEAN
  3  AS
  4      pwd_okay BOOLEAN;
  5  BEGIN
  6      IF in_new_password = in_username THEN
  7         raise_application_error(-20001, "Password may not be username");
  8      END IF;
  9      RETURN TRUE;
 10  END myFunction;
 11  /
Function created.
SQL>

Use user-defined function to combine and format columns

    
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.


SQL>
SQL> create or replace function FORMAT_EMP (DeptNo IN number,EmpName IN varchar2,Title IN varchar2) return varchar2
  2  is
  3    concat_rslt   varchar2(100);
  4  begin
  5    concat_rslt :="Department: " || to_char(DeptNo) ||" emp: " || initcap(EmpName) ||" Title: " || initcap(Title);
  6    return (concat_rslt);
  7  end;
  8  /
Function created.
SQL>
SQL> select format_emp(deptno,ename,job) from emp;
FORMAT_EMP(DEPTNO,ENAME,JOB)
--------------------------------------------------------------------------------
Department: 20 emp: Smith Title: Clerk
Department: 30 emp: Allen Title: Salesman
Department: 30 emp: Ward Title: Salesman
Department: 20 emp: Jones Title: Manager
Department: 30 emp: Martin Title: Salesman
Department: 30 emp: Blake Title: Manager
Department: 10 emp: Clark Title: Manager
Department: 20 emp: Scott Title: Analyst
Department: 10 emp: King Title: President
Department: 30 emp: Turner Title: Salesman
Department: 20 emp: Adams Title: Clerk
FORMAT_EMP(DEPTNO,ENAME,JOB)
--------------------------------------------------------------------------------
Department: 30 emp: James Title: Clerk
Department: 20 emp: Ford Title: Analyst
Department: 10 emp: Miller Title: Clerk
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.

We use user function in DML statements

    

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL> CREATE TABLE myTable
  2    (num_col    NUMBER
  3    ,char_col   VARCHAR2(60));
Table created.
SQL>
SQL> CREATE OR REPLACE FUNCTION FullName (p_empID  emp.ID%TYPE) RETURN VARCHAR2 IS
  2
  3    v_Result  VARCHAR2(100);
  4  BEGIN
  5    SELECT fname || " " || lname INTO v_Result FROM emp WHERE ID = p_empID;
  6
  7    RETURN v_Result;
  8  END FullName;
  9  /
Function created.
SQL>
SQL> DECLARE
  2    CURSOR c_IDs IS SELECT ID FROM emp WHERE ID BETWEEN 10 AND 20;
  3  BEGIN
  4    FOR v_Rec IN c_IDs LOOP
  5      INSERT INTO myTable (num_col, char_col)
  6        VALUES (v_Rec.ID, FullName(v_Rec.ID));
  7    END LOOP;
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table myTable;
Table dropped.