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
<source lang="sql">
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></source>
Compare date offset in a function
<source lang="sql">
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></source>
Convert Comma-separated values to table collection
<source lang="sql">
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.</source>
Create a procedure to count employees
<source lang="sql">
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></source>
Date calculation: business days between
<source lang="sql">
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></source>
Date time calculation function
<source lang="sql">
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.</source>
Define your own varchar to date function
<source lang="sql">
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></source>
Format money
<source lang="sql">
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></source>
Function to convert celsius to fahrenheit
<source lang="sql">
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></source>
Function to convert fahrenheit to celsius
<source lang="sql">
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></source>
Generic function utilizing dynamic SQL to return the number of rows in the specified table.
<source lang="sql">
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></source>
Get circle area
<source lang="sql">
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></source>
Get nearest day
<source lang="sql">
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></source>
Get the Max date
<source lang="sql">
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.</source>
Get the next business day
<source lang="sql">
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</source>
Removes all numeric digits from the string passed in.
<source lang="sql">
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></source>
String between function
<source lang="sql">
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></source>
Table Count function
<source lang="sql">
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></source>
To number or null
<source lang="sql">
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></source>
Word count function
<source lang="sql">
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></source>
