Oracle PL/SQL/Stored Procedure Function/Function Parameters

A stored function with no parameters.

    
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE NoParamsP AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE("No Paramaters!");
  4  END NoParamsP;
  5  /
Procedure created.
SQL>
SQL> CREATE OR REPLACE FUNCTION NoParamsF
  2    RETURN DATE AS
  3  BEGIN
  4    RETURN SYSDATE;
  5  END NoParamsF;
  6  /
Function created.
SQL>
SQL> BEGIN
  2    NoParamsP;
  3    DBMS_OUTPUT.PUT_LINE("Calling NoParamsF on " || TO_CHAR(NoParamsF, "DD-MON-YYYY"));
  4  END;
  5  /
No Paramaters!
Calling NoParamsF on 18-JUN-2008
PL/SQL procedure successfully completed.
SQL>

Boolean value function parameter

   
SQL>
SQL> create or replace
  2  function ite(
  3    p_expression boolean,
  4    p_true varchar2,
  5    p_false varchar2 ) return varchar2 as
  6  begin
  7    if p_expression then
  8        return p_true;
  9    end if;
 10    return p_false;
 11  end ite;
 12  /
Function created.
SQL>
SQL>  set serverout on
SQL>
SQL>
SQL>  exec dbms_output.put_line( ite( 1=2, "Equal", "Not Equal" ) );
Not Equal
PL/SQL procedure successfully completed.
SQL>
SQL>  exec dbms_output.put_line( ite( 2>3, "True", "False" ) );
False
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Clob type parameter

   
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL> -- create the PL/SQL procedures and methods
SQL> CREATE OR REPLACE PROCEDURE myProcedure(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
  4  END myProcedure;
  5  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Get circle area

    
SQL> CREATE OR REPLACE FUNCTION circle_area (p_radius IN NUMBER) RETURN NUMBER AS
  2    v_pi   NUMBER := 3.1415926;
  3    v_area NUMBER;
  4  BEGIN
  5    v_area := v_pi * POWER(p_radius, 2);
  6    RETURN v_area;
  7  END circle_area;
  8  /
Function created.
SQL>
SQL>

Mixed Name and Position Notation Calls

    

SQL> CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
  2  BEGIN
  3    RETURN a + b + c;
  4  END;
  5  /
Function created.

Named Notation

    
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION add_three_numbers(a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
  2  BEGIN
  3    RETURN a + b + c;
  4  END;
  5  /
Function created.
SQL>
SQL> BEGIN
  2    dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
  3  END;
  4  /
  dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
                       *
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00703: multiple instances of named argument in list
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Pass number value to function

    

SQL> CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (degree NUMBER) RETURN NUM
BER IS
  2      buffer NUMBER;
  3  BEGIN
  4     buffer := (degree * 9/5) + 32;
  5     RETURN buffer;
  6  END celsius_to_fahrenheit;
  7  /
Function created.
SQL>
SQL>

Pass value to function parameter

   
SQL> -- The stored function squareme.
SQL> CREATE OR REPLACE FUNCTION squareme(thenum number)
  2       RETURN NUMBER IS
  3  BEGIN
  4       RETURN thenum * thenum;
  5  END squareme;
  6  /
Function created.
SQL>
SQL>
SQL>
SQL> -- Passing parameters to squareme.
SQL> BEGIN
  2       DBMS_OUTPUT.PUT_LINE("9 squared is " || squareme(9) );
  3  END;
  4
  5  /
9 squared is 81
PL/SQL procedure successfully completed.

Positional Notation

    
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
  2  BEGIN
  3    RETURN a + b + c;
  4  END;
  5  /
Function created.
SQL>
SQL> BEGIN
  2    dbms_output.put_line(add_three_numbers(3,4,5));
  3  END;
  4  /
PL/SQL procedure successfully completed.

The FullName Function

    
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>
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> drop table lecturer;
Table dropped.
SQL>
SQL>

Use Column type as the function parameter type

    
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>
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 TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CountCredits (
  2    p_ID IN lecturer.ID%TYPE)
  3    RETURN NUMBER AS
  4
  5    v_TotalCredits NUMBER := 0;  -- Total number of credits
  6    v_CourseCredits NUMBER;      -- Credits for one course
  7    CURSOR c_RegisteredCourses IS
  8      SELECT department, course
  9        FROM myStudent
 10        WHERE student_id = p_ID;
 11  BEGIN
 12    FOR v_CourseRec IN c_RegisteredCourses LOOP
 13      SELECT num_credits
 14        INTO v_CourseCredits
 15        FROM session
 16        WHERE department = v_CourseRec.department AND course = v_CourseRec.course;
 17
 18      v_TotalCredits := v_TotalCredits + v_CourseCredits;
 19    END LOOP;
 20
 21    RETURN v_TotalCredits;
 22  END CountCredits;
 23  /
Function created.
SQL>
SQL> select CountCredits(10002) from dual;
COUNTCREDITS(10002)
-------------------
                  8
1 row selected.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
SQL>
--

Varray type parameter

   
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL>
SQL>
SQL> create table e
  2  as
  3  select empno, ename, init, mgr, deptno
  4  from   emp;
Table created.
SQL>
SQL>
SQL> create or replace type numberlist_t
  2  as varray(4) of varchar2(20);
  3  /
Type created.
SQL>
SQL> column numlist format a60
SQL>
SQL> alter table e add (numlist numberlist_t);
Table altered.
SQL>
SQL> describe e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(8)
 INIT                                               VARCHAR2(5)
 MGR                                                NUMBER(4)
 DEPTNO                                             NUMBER(2)
 NUMLIST                                            NUMBERLIST_T
SQL>
SQL> create or replace function ext
  2       (p_varray_in numberlist_t)
  3  return varchar2
  4  is
  5    v_ext varchar2(20);
  6  begin
  7    v_ext := p_varray_in(1);
  8    return v_ext;
  9  end;
 10  /
Function created.
SQL>
SQL>
SQL> drop table e;
Table dropped.
SQL> drop table emp;
Table dropped.