We use user function in DML statements : Function Return « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Function Return

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.

   
    
    
    
  

Related examples in the same category