Oracle PL/SQL/Stored Procedure Function/Utility Function
Версия от 13:45, 26 мая 2010; (обсуждение)
Содержание
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> --