Oracle PL/SQL Tutorial/Function Procedure Packages/Utility Function
Содержание
- 1 Add day to month
- 2 Compare date offset in a function
- 3 Convert Comma-separated values to table collection
- 4 Create a procedure to count employees
- 5 Date calculation: business days between
- 6 Date time calculation function
- 7 Define your own varchar to date function
- 8 Format money
- 9 Function to convert celsius to fahrenheit
- 10 Function to convert fahrenheit to celsius
- 11 Generic function utilizing dynamic SQL to return the number of rows in the specified table.
- 12 Get circle area
- 13 Get nearest day
- 14 Get the Max date
- 15 Get the next business day
- 16 Removes all numeric digits from the string passed in.
- 17 String between function
- 18 Table Count function
- 19 To number or null
- 20 Word count function
Add day to month
SQL>
SQL> DECLARE
2 FUNCTION my_add_months (date_in IN DATE, months_shift IN NUMBER)
3 RETURN DATE IS date_out DATE;
4 day_in NUMBER;
5 day_out NUMBER;
6 BEGIN
7 date_out := ADD_MONTHS(date_in, months_shift);
8 day_in := TO_NUMBER(TO_CHAR(date_in,"DD"));
9 day_out := TO_NUMBER(TO_CHAR(date_out,"DD"));
10 IF day_out > day_in
11 THEN
12 date_out := date_out - (day_out - day_in);
13 END IF;
14
15 RETURN date_out;
16 END;
17 BEGIN
18 DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_add_months(
19 TO_DATE("31-Jan-2002 13:14:15","dd-mon-yyyy hh24:mi:ss"),1),
20 "dd-Mon-yyyy hh24:mi:ss"));
21
22 DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_add_months(
23 TO_DATE("28-Feb-2002 13:14:15","dd-mon-yyyy hh24:mi:ss"),1),
24 "dd-Mon-yyyy hh24:mi:ss"));
25 END;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Compare date offset in a function
SQL>
SQL> CREATE OR REPLACE FUNCTION is_overdue (due_date IN DATE,paid_date IN DATE)
2 RETURN BOOLEAN
3 IS
4 days_between NUMBER (2) := due_date - paid_date;
5 BEGIN
6 RETURN days_between > 30;
7 EXCEPTION
8 WHEN OTHERS
9 THEN
10 DBMS_OUTPUT.put_line ("Error in is_overdue; Check input data.");
11 END;
12 /
Function created.
SQL>
Convert Comma-separated values to table collection
SQL>
SQL>
SQL> create or replace type myTableType
2 as table of number;
3 /
Type created.
SQL>
SQL> create or replace
2 function str2tbl( p_str in varchar2 ) return myTableType
3 as
4 l_str long default p_str || ",";
5 l_n number;
6 l_data myTableType := myTabletype();
7 begin
8 loop
9 l_n := instr( l_str, "," );
10 exit when (nvl(l_n,0) = 0);
11 l_data.extend;
12 l_data( l_data.count ) :=
13 ltrim(rtrim(substr(l_str, 1, l_n - 1)));
14 l_str := substr( l_str, l_n+1 );
15 end loop;
16 return l_data;
17 end;
18 /
Function created.
SQL>
SQL> variable bind_variable varchar2(30)
SQL> exec :bind_variable := "1,3,5,7,99"
PL/SQL procedure successfully completed.
BIND_VARIABLE
--------------------------------
1,3,5,7,99
SQL> select * from TABLE ( cast ( str2tbl(:bind_variable) as myTableType ) )
2 /
COLUMN_VALUE
------------
1
3
5
7
99
SQL>
SQL> drop type myTableType;
Type dropped.
Create a procedure to count employees
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL>
SQL> create or replace function emp_count(p_deptno in number)
2 return number is
3 cnt number(2) := 0;
4 begin
5 select count(*) into cnt
6 from employees e
7 where e.deptno = p_deptno;
8 return (cnt);
9 end;
10 /
Function created.
SQL>
SQL>
SQL> select empno, ename
2 , emp_count(empno)
3 from employees;
EMPNO ENAME EMP_COUNT(EMPNO)
---------- -------- ----------------
1 Jason 0
2 Jerry 0
3 Jord 0
4 Mary 0
5 Joe 0
6 Black 0
7 Red 0
8 White 0
9 Yellow 0
10 Pink 2
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>
Date calculation: business days between
SQL>
SQL> CREATE OR REPLACE FUNCTION bizdays_between (
2 start_date IN DATE, end_date IN DATE)
3 RETURN INTEGER
4 IS
5 v_sundays INTEGER :=
6 NEXT_DAY (end_date - 7, "SUNDAY") -
7 NEXT_DAY (start_date - 1, "SUNDAY");
8
9 v_saturdays INTEGER :=
10 NEXT_DAY (end_date - 7, "SATURDAY") -
11 NEXT_DAY (start_date - 1, "SATURDAY");
12 BEGIN
13 RETURN (
14 end_date -
15 start_date -
16 (v_sundays + v_saturdays)/7 -
17 1
18 );
19 END;
20 /
Function created.
SQL>
Date time calculation function
SQL>
SQL> CREATE OR REPLACE FUNCTION expiration_date (
2 good_for_period_in IN INTERVAL YEAR TO MONTH
3 )
4 RETURN TIMESTAMP
5 IS
6 bad_month EXCEPTION;
7 PRAGMA EXCEPTION_INIT (bad_month, -1839);
8
9 todays_date TIMESTAMP;
10 result_date TIMESTAMP;
11 BEGIN
12 todays_date := TRUNC(SYSTIMESTAMP);
13 LOOP
14 BEGIN
15 result_date := todays_date + good_for_period_in;
16 EXCEPTION
17 WHEN bad_month THEN
18 todays_date := todays_date - INTERVAL "1" DAY;
19 WHEN OTHERS THEN
20 RAISE;
21 END;
22
23 EXIT WHEN result_date IS NOT NULL;
24 END LOOP;
25 RETURN result_date;
26 END;
27 /
Function created.
Define your own varchar to date function
SQL>
SQL> CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
2 RETURN DATE
3 IS
4 TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
5
6 fmts mask_t;
7 retval DATE := NULL;
8
9 mask_index INTEGER := 1;
10
11 date_converted BOOLEAN := FALSE;
12
13 PROCEDURE init_fmts
14 IS
15 BEGIN
16 fmts (1) := "DD-MON-RR";
17 fmts (2) := "DD-MON-YYYY";
18 fmts (3) := "DD-MON";
19 fmts (4) := "MM/DD";
20 fmts (5) := "MM/RR";
21 fmts (6) := "MMDDRR";
22 END;
23 BEGIN
24 init_fmts;
25
26 WHILE mask_index IS NOT NULL AND NOT date_converted
27 LOOP
28 BEGIN
29 retval := TO_DATE (value_in, fmts (mask_index));
30 date_converted := TRUE;
31 EXCEPTION
32 WHEN OTHERS
33 THEN
34 mask_index := fmts.NEXT (mask_index);
35
36 IF mask_index IS NULL
37 THEN
38 RAISE;
39 END IF;
40 END;
41 END LOOP;
42
43 RETURN retval;
44 END my_to_date;
45 /
Function created.
SQL>
SQL>
SQL>
Format money
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION format_money (p_value_num NUMBER)
2 RETURN VARCHAR2 IS
3 returnString VARCHAR2(100);
4 BEGIN
5 IF (p_value_num >= 1000000) THEN
6 returnString := LTRIM(TO_CHAR(p_value_num, "$999,999,999.00"));
7 ELSIF (p_value_num < 1000000) AND (p_value_num >= 1000) THEN
8 returnString := LTRIM( TO_CHAR(p_value_num, "$999,999.00"));
9 ELSE
10 returnString := LTRIM(TO_CHAR(p_value_num, "$999.00"));
11 END IF;
12 RETURN returnString;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RAISE_APPLICATION_ERROR( -20123, "Error occurred in MONEY " ||
16 "function for incoming value:" || TO_CHAR(p_value_num) ||
17 " and outgoing value:" || returnString);
18 END format_money;
19 /
Function created.
SQL>
SQL>
Function to convert celsius to fahrenheit
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>
Function to convert fahrenheit to celsius
SQL> CREATE OR REPLACE FUNCTION fahrenheit_to_celsius (degree NUMBER) RETURN NUMBER IS
2 buffer NUMBER;
3
4 BEGIN
5 buffer := (5/9) * (degree - 32);
6 RETURN buffer;
7 END fahrenheit_to_celsius;
8
9 /
Function created.
SQL>
SQL>
Generic function utilizing dynamic SQL to return the number of rows in the specified table.
SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (
2 sch IN VARCHAR2,
3 tab IN VARCHAR2)
4 RETURN INTEGER
5 IS
6 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
7 ignore INTEGER;
8 retval INTEGER;
9 BEGIN
10 DBMS_SQL.PARSE (cur, "SELECT COUNT(*) FROM " || sch || "." || tab, DBMS_SQL.NATIVE);
11 DBMS_SQL.DEFINE_COLUMN (cur, 1, retval);
12 ignore := DBMS_SQL.EXECUTE_AND_FETCH (cur);
13 DBMS_SQL.COLUMN_VALUE (cur, 1, retval);
14 DBMS_SQL.CLOSE_CURSOR (cur);
15 RETURN retval;
16 END;
17 /
Function created.
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>
Get nearest day
SQL>
SQL> CREATE OR REPLACE FUNCTION nearestday (yourdate IN DATE, dayname IN VARCHAR2)
2 RETURN DATE
3 IS
4 before_date DATE := NEXT_DAY (yourdate-7, dayname);
5 after_date DATE := NEXT_DAY (yourdate, dayname);
6
7 before_diff NUMBER;
8 after_diff NUMBER;
9 BEGIN
10 before_diff := yourdate - before_date;
11 after_diff := yourdate - after_date;
12 IF before_diff < after_diff
13 THEN
14 RETURN before_date;
15 ELSE
16 RETURN after_date;
17 END IF;
18 END;
19 /
Function created.
SQL>
Get the Max date
SQL>
SQL> DECLARE
2 curr_date DATE := SYSDATE;
3 last_date DATE;
4 step PLS_INTEGER := 1000;
5 BEGIN
6 LOOP
7 BEGIN
8 last_date := curr_date;
9 curr_date := curr_date + step;
10 EXCEPTION
11 WHEN OTHERS
12 THEN
13 IF step = 1
14 THEN
15 DBMS_OUTPUT.PUT_LINE ("Last date: " || TO_CHAR (last_date, "Month DD, YYYY"));
16 EXIT;
17 ELSE
18 step := step / 2;
19 curr_date := last_date;
20 END IF;
21 END;
22 END LOOP;
23 END;
24 /
Last date: December 31, 9999
PL/SQL procedure successfully completed.
Get the next business day
SQL> create or replace function next_business_day (p_days in number)
2 return varchar
3 is
4 v_new_day varchar2(10);
5 v_new_date date;
6 begin
7 v_new_day := to_char((sysdate + p_days), "fmDay");
8 v_new_date := sysdate + p_days ;
9
10 if v_new_day = "Saturday" then
11 v_new_day := "Monday" ;
12 v_new_date := v_new_date + 2 ;
13 elsif v_new_day = "Sunday" then
14 v_new_day := "Monday";
15 v_new_date := v_new_date + 1 ;
16 end if;
17
18 return(v_new_day || " " || to_char(v_new_date,"mm/dd/yyyy"));
19 end;
20 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> -- Test the next_business_day function:
SQL> select next_business_day(2) from dual;
NEXT_BUSINESS_DAY(2)
--------------------------------------------------------------------------------
Monday 07/28/2008
SQL> select next_business_day(200) from dual;
NEXT_BUSINESS_DAY(200)
--------------------------------------------------------------------------------
Monday 02/09/2009
SQL>
SQL>
SQL>
SQL>
////////////////////////////////////////////////
Unfortunately, your formula calculating number of business days between 2 dates has a defect. If you take the same business date for start_date and end_date it will give the difference of 1 business day, which is wrong.
I would propose the following correction:
SQL>
SQL> CREATE OR REPLACE FUNCTION bizdays_between (
2 start_date IN DATE, end_date IN DATE)
3 RETURN INTEGER
4 IS
5 v_sundays INTEGER :=
6 NEXT_DAY (end_date - 7, "SUNDAY") -
7 NEXT_DAY (start_date - 1, "SUNDAY");
8
9 v_saturdays INTEGER :=
10 NEXT_DAY (end_date - 7, "SATURDAY") -
11 NEXT_DAY (start_date - 1, "SATURDAY");
12 BEGIN
13 RETURN (
14 end_date -
15 start_date -
16 abs(sign(end_date - start_date))*((v_sundays + v_saturdays)/7 -
17 1)
18 );
19 END;
20 /
Sincerely,
Paul Filstein
paul.filstein at thehartford.ru
Removes all numeric digits from the string passed in.
SQL> CREATE OR REPLACE FUNCTION number_stripper(p_string VARCHAR2) RETURN VARCHAR2
2 IS
3 v_current_element NUMBER;
4 v_char_length NUMBER;
5 v_ret_val VARCHAR2 (2000);
6 v_current_char VARCHAR2 (1);
7 BEGIN
8 v_char_length := LENGTH (p_string);
9 v_current_element := 1;
10
11 LOOP
12 EXIT WHEN v_current_element > v_char_length;
13 v_current_char := SUBSTR (p_string, v_current_element, 1);
14
15 IF TRANSLATE (v_current_char, "1234567890", "**********") != "*"
16 OR v_current_char = "~"
17 THEN
18 v_ret_val := v_ret_val || v_current_char;
19 END IF;
20
21 v_current_element := v_current_element + 1;
22 END LOOP;
23
24 RETURN (v_ret_val);
25 END;
26 /
Function created.
SQL> select number_stripper("asdf123") from dual;
NUMBER_STRIPPER("ASDF123")
--------------------------------------------------------------------------------
asdf
SQL>
String between function
SQL>
SQL> CREATE OR REPLACE FUNCTION betweenString (
2 string_in IN VARCHAR2,
3 start_in IN INTEGER,
4 end_in IN INTEGER,
5 inclusive IN BOOLEAN := TRUE
6 )
7 RETURN VARCHAR2
8 IS
9 v_start INTEGER := start_in;
10 v_numchars INTEGER := ABS (end_in) - ABS (start_in) + 1;
11 BEGIN
12 IF string_in IS NULL
13 OR NVL (start_in, 0) = 0
14 OR (start_in < 0 AND end_in > 0)
15 OR (start_in > 0 AND end_in < 0)
16 OR (start_in < 0 AND end_in > start_in)
17 OR (start_in > 0 AND end_in < start_in)
18 THEN
19 RETURN NULL;
20 ELSE
21 IF v_start < 0
22 THEN
23 v_start := end_in;
24 ELSE
25 v_start := start_in;
26 END IF;
27
28 IF NOT NVL (inclusive, FALSE )
29 THEN
30 v_start := v_start + 1;
31 v_numchars := v_numchars - 2;
32 END IF;
33
34 IF v_start > end_in OR v_numchars < 1
35 THEN
36 RETURN NULL;
37 ELSE
38 RETURN (SUBSTR (string_in, v_start, v_numchars));
39 END IF;
40 END IF;
41 END;
42 /
Function created.
SQL>
Table Count function
SQL>
SQL> CREATE OR REPLACE FUNCTION tabCount (
2 tab IN VARCHAR2,
3 whr IN VARCHAR2 := NULL,
4 sch IN VARCHAR2 := NULL)
5 RETURN INTEGER
6 IS
7 retval INTEGER;
8 BEGIN
9 EXECUTE IMMEDIATE
10 "SELECT COUNT(*)
11 FROM " || NVL (sch, USER) || "." || tab ||
12 " WHERE " || NVL (whr, "1=1")
13 INTO retval;
14 RETURN retval;
15 END;
16 /
Function created.
SQL>
SQL>
SQL>
To number or null
SQL>
SQL> create or replace FUNCTION to_number_or_null(aiv_number in varchar2)return number is
2 begin
3 return to_number(aiv_number);
4 exception
5 when INVALID_NUMBER then
6 return NULL;
7 end to_number_or_null;
8 /
Function created.
SQL>
Word count function
SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
2 RETURN PLS_INTEGER
3 AS
4 words PLS_INTEGER := 0;
5 len PLS_INTEGER := NVL(LENGTH(str),0);
6 inside_a_word BOOLEAN;
7 BEGIN
8 FOR i IN 1..len + 1
9 LOOP
10 IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
11 THEN
12 IF inside_a_word
13 THEN
14 words := words + 1;
15 inside_a_word := FALSE;
16 END IF;
17 ELSE
18 inside_a_word := TRUE;
19 END IF;
20 END LOOP;
21 RETURN words;
22 END;
23 /
Function created.
SQL>
SQL>
SQL>