This function can be called from a SQL statement. : Function Call « Stored Procedure Function « Oracle PL / SQL

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

This function can be called from a SQL statement.

 
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION FullName (
  2    p_ID  lecturer.ID%TYPE)
  3    RETURN VARCHAR2 IS
  4
  5    myResult  VARCHAR2(100);
  6  BEGIN
  7    SELECT first_name || ' ' || last_name
  8      INTO myResult
  9      FROM lecturer
 10      WHERE ID = p_ID;
 11
 12    RETURN myResult;
 13  END FullName;
 14  /

Function created.

SQL>
SQL> SELECT ID, FullName(ID) "Full Name"
  2    FROM lecturer;

        ID
----------
Full Name
--------------------------------------------------------------------------------
     10001
Scott Lawson

     10002
Mar Wells

     10003
Jone Bliss


        ID
----------
Full Name
--------------------------------------------------------------------------------
     10004
Man Kyte

     10005
Pat Poll

     10006
Tim Viper


        ID
----------
Full Name
--------------------------------------------------------------------------------
     10007
Barbara Blues

     10008
David Large

     10009
Chris Elegant


        ID
----------
Full Name
--------------------------------------------------------------------------------
     10010
Rose Bond

     10011
Rita Johnson

     10012
Sharon Clear


12 rows selected.

SQL>
SQL> INSERT INTO MyTable (char_col)
  2    VALUES (FullName(10010));

1 row created.

SQL>
SQL> drop table MyTable;

Table dropped.

SQL> drop table lecturer;

Table dropped.

 

Related examples in the same category