Oracle PL/SQL/Stored Procedure Function/Utility Function

Calculate average grade

  
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> CREATE OR REPLACE FUNCTION AverageGrade (p_Department IN VARCHAR2,p_Course IN NUMBER) RETURN VARCHAR2 AS
  2
  3    v_AverageGrade VARCHAR2(1);
  4    v_NumericGrade NUMBER;
  5    v_NumberStudents NUMBER;
  6
  7    CURSOR c_Grades IS
  8      SELECT grade
  9        FROM myStudent
 10        WHERE department = p_Department
 11        AND course = p_Course;
 12  BEGIN
 13    SELECT COUNT(*)
 14      INTO v_NumberStudents
 15      FROM myStudent
 16      WHERE department = p_Department
 17        AND course = p_Course;
 18
 19    IF v_NumberStudents = 0 THEN
 20      RAISE_APPLICATION_ERROR(-20001, "No students registered for " ||
 21        p_Department || " " || p_Course);
 22    END IF;
 23
 24    SELECT AVG(DECODE(grade, "A", 5,
 25                             "B", 4,
 26                             "C", 3,
 27                             "D", 2,
 28                             "E", 1))
 29      INTO v_NumericGrade
 30      FROM myStudent
 31      WHERE department = p_Department
 32      AND course = p_Course;
 33
 34    SELECT DECODE(ROUND(v_NumericGrade), 5, "A",
 35                                         4, "B",
 36                                         3, "C",
 37                                         2, "D",
 38                                         1, "E")
 39      INTO v_AverageGrade
 40      FROM dual;
 41
 42    RETURN v_AverageGrade;
 43  END AverageGrade;
 44  /
Function created.
SQL>
SQL> select AverageGrade("HIS", 101) from dual;
AVERAGEGRADE("HIS",101)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B
1 row selected.
SQL>
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
SQL>

Function to count tab

  
SQL> CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
  2     RETURN PLS_INTEGER AUTHID CURRENT_USER
  3  IS
  4     str      VARCHAR2 (32767) := "SELECT COUNT(*) FROM " || tab;
  5     retval   PLS_INTEGER;
  6  BEGIN
  7     IF whr IS NOT NULL
  8     THEN
  9        str := str || " WHERE " || whr;
 10     END IF;
 11
 12     EXECUTE IMMEDIATE str
 13                  INTO retval;
 14  EXCEPTION
 15     WHEN OTHERS
 16     THEN
 17        DBMS_OUTPUT.put_line ("TABCOUNT ERROR: " || DBMS_UTILITY.FORMAT_ERROR_STACK);
 18        DBMS_OUTPUT.put_line (str);
 19        RETURN NULL;
 20  END;
 21  /
Function created.
SQL>

Function to update null value

  
SQL>
SQL> CREATE OR REPLACE FUNCTION updNVal (
  2     tab IN VARCHAR2,
  3     col IN VARCHAR2,
  4     val IN NUMBER,
  5     whr IN VARCHAR2 := NULL,
  6     sch IN VARCHAR2 := NULL)
  7     RETURN INTEGER
  8  IS
  9  BEGIN
 10     EXECUTE IMMEDIATE
 11        "UPDATE " || NVL (sch, USER) || "." || tab ||
 12        "   SET " || col || " = :the_value
 13          WHERE " || NVL (whr, "1=1")
 14       USING val;
 15     RETURN SQL%ROWCOUNT;
 16  END;
 17  /
Function created.
SQL>

Raise pay level

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION RAISE(
  2            p_paylevel INTEGER,
  3            p_payrate NUMBER)
  4       RETURN NUMBER IS
  5       v_newrate NUMBER;
  6  BEGIN
  7       IF p_paylevel = 1 THEN
  8            v_newrate := p_payrate * 1.10;
  9       ELSIF p_paylevel = 2 THEN
 10            v_newrate := p_payrate * 1.05;
 11       ELSIF p_paylevel = 3 THEN
 12            v_newrate := p_payrate * 1.04;
 13       ELSIF p_paylevel = 4 THEN
 14            v_newrate := p_payrate * 1.03;
 15  ELSIF p_paylevel = 5 THEN
 16            v_newrate := p_payrate ;
 17       ELSE
 18            v_newrate := p_payrate * 1.02;
 19       END IF;
 20       RETURN v_newrate;
 21  END RAISE;
 22  /
Function created.
SQL>
SQL>
SQL> select raise(2,3) from dual;
RAISE(2,3)
----------
      3.15
1 row selected.
SQL>
SQL> --

Raise power function

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION RAISE(
  2            p1 INTEGER,
  3            p2 NUMBER)
  4       RETURN NUMBER IS
  5       p3 NUMBER;
  6  BEGIN
  7       IF p1 = 1 THEN
  8            p3 := p2 * 1.10;
  9       ELSIF p1 = 2 THEN
 10            p3 := p2 * 1.05;
 11       ELSIF p1 = 3 THEN
 12            p3 := p2 * 1.04;
 13       ELSIF p1 = 4 THEN
 14            p3 := p2 * 1.03;
 15       ELSIF p1 = 5 THEN
 16            p3 := p2 ;
 17       ELSE
 18            p3 := p2 * 1.02;
 19       END IF;
 20       RETURN p3; --
 21  END RAISE;
 22  /
Function created.
SQL>
SQL>
SQL> select raise(2,3) from dual;
RAISE(2,3)
----------
      3.15
1 row selected.
SQL>
SQL> --

Temperature package: convert Celsius to Fahrenheit back, forth

   
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE temperature IS
  2     PROCEDURE Temperature_Conversion ( degree number,cf varchar2);
  3     FUNCTION Celsius_to_Fahrenheit ( degree NUMBER) Return Number;
  4     FUNCTION Fahrenheit_to_Celsius ( degree NUMBER) Return Number;
  5  END temperature;
  6  /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY temperature AS
  2
  3  PROCEDURE temperature_conversion (degree number,cf varchar2) IS
  4      buffer NUMBER := 0;
  5  BEGIN
  6      IF cf = "C"
  7      THEN
  8          buffer := TEMPERATURE.Celsius_to_Fahrenheit(degree);
  9          DBMS_OUTPUT.PUT_LINE("Fahrenheit = " || buffer);
 10          RETURN;
 11      ELSIF cf = "F"
 12      THEN
 13          buffer := TEMPERATURE.Fahrenheit_to_Celsius(degree);
 14          DBMS_OUTPUT.PUT_LINE("Celsius = " || buffer);
 15          RETURN;
 16      END IF;
 17      DBMS_OUTPUT.PUT_LINE("Bad Temperature Conversion Code: " || cf);
 18  RETURN;
 19
 20  END temperature_conversion;
 21
 22      FUNCTION celsius_to_fahrenheit (degree NUMBER)
 23      RETURN NUMBER IS
 24      buffer NUMBER;
 25
 26      BEGIN
 27         buffer := (degree * 9/5) + 32;
 28         RETURN buffer;
 29      END celsius_to_fahrenheit;
 30
 31      FUNCTION fahrenheit_to_celsius (degree NUMBER)
 32      RETURN NUMBER IS
 33      buffer NUMBER;
 34
 35      BEGIN
 36         buffer := (5/9) * (degree - 32);
 37         RETURN buffer;
 38      END fahrenheit_to_celsius;
 39
 40  END temperature;
 41  /
Package body created.
SQL>
SQL>

Validate date value format

  
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION validate_date_format
  2     (p_input_date in VARCHAR2) RETURN BOOLEAN IS
  3     myResult DATE;
  4  BEGIN
  5     myResult := TO_DATE(p_input_date, "MM/DD/YYYY");
  6     IF LENGTH(SUBSTR(p_input_date,
  7        INSTR(p_input_date, "/", 1, 2) + 1)) = 4 THEN
  8        RETURN TRUE;
  9     ELSE
 10        RETURN FALSE;
 11     END IF;
 12  EXCEPTION
 13     WHEN OTHERS THEN
 14        RETURN FALSE;
 15  END validate_date_format;
 16  /
Function created.
SQL>
SQL> show error
No errors.
SQL>
SQL> BEGIN
  2     IF validate_date_format("&&valid_date") THEN
  3        DBMS_OUTPUT.PUT_LINE("Date: " || "&&valid_date" ||
  4           CHR(9) || " is a VALID Date.");
  5     ELSE
  6        DBMS_OUTPUT.PUT_LINE("Date: " || "&&valid_date" ||
  7           CHR(9) || " is an INVALID Date.");
  8     END IF;
  9  END;
 10  /
old   2:    IF validate_date_format("&&valid_date") THEN
new   2:    IF validate_date_format("") THEN
old   3:       DBMS_OUTPUT.PUT_LINE("Date: " || "&&valid_date" ||
new   3:       DBMS_OUTPUT.PUT_LINE("Date: " || "" ||
old   6:       DBMS_OUTPUT.PUT_LINE("Date: " || "&&valid_date" ||
new   6:       DBMS_OUTPUT.PUT_LINE("Date: " || "" ||
Date:    is an INVALID Date.
PL/SQL procedure successfully completed.
SQL>

Your own month add method

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION add_mon (date_in DATE,months_to_add NUMBER)
  2  RETURN DATE AS
  3    day_in VARCHAR2(3);
  4    day_work VARCHAR2(3);
  5    date_out DATE;
  6    date_work DATE;
  7  BEGIN
  8
  9    day_in := TO_NUMBER(TO_CHAR(date_in,"dd"));
 10    date_work := ADD_MONTHS(date_in, months_to_add);
 11    day_work := TO_NUMBER(TO_CHAR(date_work,"dd"));
 12    IF day_in = day_work THEN
 13      date_out := date_work;
 14    ELSIF day_work < day_in THEN
 15      date_out := date_work;
 16    ELSE
 17      date_out := date_work - (day_work - day_in);
 18    END IF;
 19
 20    RETURN date_out;
 21  END;
 22  /
Function created.
SQL> show error
No errors.
SQL>
SQL> select add_mon(sysdate, 6) from dual;
ADD_MON(SYSDATE,6)
--------------------
16-DEC-2008 18:11:35
1 row selected.
SQL>
SQL> --