存储过程包实例分享
View Code
2、STR包：
网址：http://www.joegarrepy.com/str_package.htm
http://plnet.org/（包含很多使用的包）
API参考手册:http://www.joegarrepy.com/pldoc_str.html
View Code
1 CREATE OR REPLACE PACKAGE str 2 IS 3 /** 4 * <HR> 5 * STR Package 12/21/2004 JG<BR> 6 * Package contains different procedures and functions to deal with strings 7 * <HR>AUDIT TRAIL 8 * <HR>VERSION 1.0 3/8/2005<BR> 9 * Package created to help processing strings. 10 * <HR> 11 * @headcom 12 */ 13 -- 14 /** 15 * TYPE is a record of string for string procesing 16 */ 17 TYPE rec_str IS RECORD (string_value VARCHAR2(4000)); 18 /** 19 * TYPE is a table of rec_str 20 */ 21 TYPE tab_str IS TABLE OF rec_str; 22 /** 23 * Function will split a string. The maximum size returnable 24 * is 4000 bytes and up to 15 fields. <br> 25 * For Exammple:<br> 26 * <PRE> 27 * SQL> select str.split('a,b,c,d',3,',') from dual; 28 * 29 * STR.SPLIT('A,B,C,D',3,',') 30 * -------------------------------------------------------------------------------- 31 * c 32 *</PRE> 33 * select str.split('a,b,c,d',3,',') from dual; 34 * @param in_del_field string to be split 35 * @param in_position position of split character(s) to return 36 * @param in_del delimter to split by 37 * @return Returns a single value from a string, up to 4K long 38 */ 39 FUNCTION split 40 (in_del_field IN VARCHAR2, 41 in_pos IN NUMBER, 42 in_del IN VARCHAR2) 43 RETURN VARCHAR2; 44 /** 45 * Type is a collection/array of values returned from the split_array function 46 */ 47 TYPE tab_split IS TABLE OF VARCHAR2(4000); 48 /** 49 * Funciton takes a string passed to it and returns it as a collection of the 50 * type tab_split. For Example 51 * <PRE> 52 * DECLARE 53 * t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',','); 54 * i INTEGER; 55 * BEGIN 56 * FOR i IN t_split.FIRST .. t_split.LAST LOOP 57 * DBMS_OUTPUT.PUT_LINE(t_split(i)); 58 * END LOOP; 59 * END; 60 * / 61 * </PRE> 62 * @param in_del_field string to be split 63 * @param in_del delimter to split by 64 * @return Returns an Array of type str.tab_split with a row for each delimited value in in_del_field. 65 */ 66 FUNCTION split_array 67 (in_del_field IN VARCHAR2, 68 in_del IN VARCHAR2) 69 RETURN tab_split; 70 /** 71 * Pipelined Function returns a table of one column with the deleminted string 72 * passed to it. For example, you could do the following to return a table of 73 * the alphabet... 74 * <PRE> 75 * SELECT * 76 * FROM TABLE(str.split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',')) 77 * </PRE> 78 * @param in_del_field string to be split 79 * @param in_del delimter to split by 80 * @return Returns a Piplined Table of Table Type tab_str with a row for each delimited value in in_del_field. 81 */ 82 FUNCTION split_pipe 83 (in_del_field IN VARCHAR2, 84 in_del IN VARCHAR2) 85 RETURN tab_str PIPELINED; 86 /** 87 * Function returns a TRUE if all characters in string are upper Case 88 * @param in_string String that could be upper case 89 * @return Returns a boolean TRUE if string is a upper case string, else a FALSE is returned 90 */ 91 FUNCTION is_upper_case 92 (in_string IN VARCHAR2) 93 RETURN BOOLEAN; 94 /** 95 * Function returns a TRUE if all characters in string are Lower Case 96 * @param in_string String that could be Lower case 97 * @return Returns a boolean TRUE if string is a Lower case string, else a FALSE is returned 98 */ 99 FUNCTION is_lower_case 100 (in_string IN VARCHAR2) 101 RETURN BOOLEAN; 102 /** 103 * Function will return a BOOLEAN TRUE if string passed to it has mixed case. 104 * @param in_string String that could contain mixed case 105 * @return Returns a boolean TRUE if string is a mixed string, else a FALSE is returned 106 */ 107 FUNCTION is_mixed_case 108 (in_string IN VARCHAR2) 109 RETURN BOOLEAN; 110 /** 111 * Function will return a boolean TRUE if a string passed to the function 112 * is a numeric value for example 113 * <PRE> 114 * BEGIN 115 * If str.is_number('3') Then 116 * DBMS_OUTPUT.PUT_LINE('True'); 117 * Else 118 * DBMS_OUTPUT.PUT_LINE('False'); 119 * End If; 120 * END; 121 * </PRE> 122 * @param in_value Value that is evaluated to see if it is a number 123 * @return Returns a BOOLEAN True if Chacter value passed is considered a number, else return a BOOLEAN False. 124 */ 125 FUNCTION is_number 126 (in_value IN VARCHAR2) 127 RETURN BOOLEAN; 128 /** 129 * Function will return a VARCHAR2 value of 'TRUE' if a string passed to the function 130 * is a numeric value, else it will return a value of 'FALSE'. This function was 131 * designed to be used in SQL statements since booleans values are only easliy handeled 132 * from PL/SQL. For example.. 133 * <PRE> 134 * SQL> SELECT str.is_number_sql('10') FROM dual; 135 * 136 * str.IS_NUMBER_SQL('10') 137 * -------------------------------------------------------------------------------- 138 * TRUE 139 * </PRE> 140 * @param in_value Value that is evaluated to see if it is a number 141 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a number, else 'FALSE' is returned. 142 */ 143 FUNCTION is_number_sql 144 (in_value IN VARCHAR2) 145 RETURN VARCHAR2; 146 /** 147 * Function will return a boolean TRUE if the string passed is an ordinal (1st, 2cd, 3rd etc.). 148 * @param in_value String to be evaluated 149 * @return Returns a boolean TRUE if string passed is an ordinal, else it returns a FALSE 150 */ 151 FUNCTION is_ordinal 152 (in_value IN VARCHAR2) 153 RETURN BOOLEAN; 154 /** 155 * Function returns like is_ordinal but instead returns a VARCHAR2 value of 'TRUE' if the 156 * string passed is an ordinal or returns a value of 'FALSE' if it is not. 157 * @param in_value Value that is evaluated to see if it is an ordinal 158 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned. 159 */ 160 FUNCTION is_ordinal_sql 161 (in_value IN VARCHAR2) 162 RETURN VARCHAR2; 163 /** 164 * Function returns a boolean TRUE if the string passes is a roman numeral. 165 * <PRE> 166 * For Example... 167 * <PRE> 168 * BEGIN 169 * IF str.is_roman_numeral('XXsII') THEN 170 * Dbms_Output.Put_Line('true'); 171 * ELSE 172 * Dbms_Output.Put_Line('false'); 173 * END IF; 174 * END; 175 * </PRE> 176 * @param in_value String to be evaluated 177 * @return Returns a boolean TRUE if string passed is a roman numeral, else it returns a FALSE 178 */ 179 FUNCTION is_roman_numeral 180 (in_value IN VARCHAR2) 181 RETURN BOOLEAN; 182 /** 183 * Function returns like is_roman_numeral but instead returns a VARCHAR2 value of 'TRUE' if the 184 * string passed is a roman numeral or returns a value of 'FALSE' if it is not. 185 * For Example... 186 * <PRE> 187 * SQL> SELECT str.is_roman_numeral_sql('XXII') FROM DUAL; 188 * 189 * STR.IS_ROMAN_NUMERAL_SQL('XXII') 190 * -------------------------------------------------------------------------------- 191 * TRUE 192 * 193 * SQL> 194 * </PRE> 195 * @param in_value Value that is evaluated to see if it is an ordinal 196 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned. 197 */ 198 FUNCTION is_roman_numeral_sql 199 (in_value IN VARCHAR2) 200 RETURN VARCHAR2; 201 /** 202 * Function will return a boolean TRUE if a string passed to the function is 203 * an exceptable date. Here is an example... 204 * <PRE> 205 * BEGIN 206 * If str.is_date('5-dec-2004') Then 207 * DBMS_OUTPUT.PUT_LINE('True'); 208 * Else 209 * DBMS_OUTPUT.PUT_LINE('False'); 210 * End If; 211 * END; 212 * </PRE> 213 * @param in_value Value that is evaluated to if it is a date 214 * @return Returns a BOOLEAN True if Chacter value passed is considered a date, else return a BOOLEAN False. 215 */ 216 FUNCTION is_date 217 (in_value IN VARCHAR2) 218 RETURN BOOLEAN; 219 /** 220 * Function evaluates a date in the same way as is_date but returns a VARCHAR2 221 * value of 'TRUE' if the string is a date or a value of 'FALSE' if it is not. 222 * For Example.... 223 * <PRE> 224 * SQL> select str.is_date_sql('10-dec-2004') from dual; 225 * 226 * STR.IS_DATE_SQL('10-DEC-2004') 227 * -------------------------------------------------------------------------------- 228 * TRUE 229 * </PRE> 230 * @param in_value Value that is evaluated to if it is a date 231 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a date, else 'FALSE' is returned. 232 */ 233 FUNCTION is_date_sql 234 (in_value IN VARCHAR2) 235 RETURN VARCHAR2; 236 /** 237 * Function will return the last date format used to evaluate a date with the 238 * is_date function and the is_date_sql function. For example... 239 * <PRE> 240 * SQL> select str.is_date_sql('10-dec-2004') from dual; 241 * 242 * STR.IS_DATE_SQL('10-DEC-2004') 243 * -------------------------------------------------------------------------------- 244 * TRUE 245 * 246 * SQL> select str.is_date_format from dual; 247 * 248 * IS_DATE_FORMAT 249 * -------------------------------------------------------------------------------- 250 * DDMMYYYY 251 * 252 * SQL> 253 * </PRE> 254 * @return Returns the date format that evalueted the IS_DATE call. 255 */ 256 FUNCTION is_date_format 257 RETURN VARCHAR2; 258 /** 259 * Function works similiar to UPPER and LOWER builtins except that it upper cases 260 * Only the first letter of each word and lower cases the rest of the word. 261 * Similiar to what MS Word would do if you changed the case of a sentance to Title. 262 * For Example... 263 * <PRE> 264 * SQL> SELECT str.title('joe garrepy') FROM dual; 265 * 266 * str.TITLE('JOEGARREPY') 267 * -------------------------------------------------------------------------------- 268 * Joe Garrepy 269 * 270 * SQL> 271 * </PRE> 272 * @param in_value in_value is the string to be formated 273 * @return Returns a VARCHAR2 value with all character string delimeted by spaces 274 * displaying an upper case character as the first character of each filed 275 * and the rest of the characters in each field displaying lower case. 276 */ 277 FUNCTION title 278 (in_value IN VARCHAR2) 279 RETURN VARCHAR2; 280 /** 281 * Function will Toggle the case of string, so what was upper is no lower and 282 * vice versa. For Example... 283 * <PRE> 284 * SQL> SELECT str.toggle('Toggle Case') FROM dual; 285 * 286 * str.TOGGLE('TOGGLECASE') 287 * -------------------------------------------------------------------------------- 288 * tOGGLE cASE 289 * 290 * SQL> 291 * 292 * </PRE> 293 * @param in_value in_value is the string to be formated 294 * @return Returns the passed string with the Case reversed. 295 */ 296 FUNCTION toggle 297 (in_value IN VARCHAR2) 298 RETURN VARCHAR2; 299 /** 300 * Function will remove double space from a string and replace them with a single space. 301 * The default remove is a space, but by changing the in_char parameter this will also remove 302 * other types of double characters 303 * For Example... 304 * <PRE> 305 * SQL> select str.remove_dbl_space('Test of double space .') from dual; 306 * 307 * str.REMOVE_DBL_SPACE('TESTOFDOUBLESPACE.') 308 * -------------------------------------------------------------------------------- 309 * Test of double space . 310 * 311 * SQL> 312 * </PRE> 313 * @param in_value Value that will have all double space removed from it. 314 * @param in_char Character that you want to replace double occurances out 315 * @return Returns in_value with sigle spaces replacing all double spaces. 316 */ 317 FUNCTION remove_dbl_space 318 (in_value IN VARCHAR2, 319 in_char IN VARCHAR2 DEFAULT ' ') 320 RETURN VARCHAR2; 321 -- 322 END str; 323 / 324 CREATE OR REPLACE PACKAGE BODY str 325 IS 326 -- ******************************************************************************** -- 327 -- Private Package Functions, Procedures, Cursors and Variables 328 -- ******************************************************************************** -- 329 globDateFormat VARCHAR2(100) := NULL; 330 globLastDateValue VARCHAR2(32767) := NULL; 331 TYPE tab_str_strip IS TABLE OF VARCHAR2(1); 332 t_str_strip tab_str_strip := tab_str_strip('-','/','\',' '); 333 TYPE tab_century IS TABLE OF VARCHAR2(3); 334 t_centruy tab_century := tab_century('CC', --> Century 335 'SCC'); --> Century BC prefixed with - 336 TYPE tab_year IS TABLE OF VARCHAR2(5); 337 t_year tab_year := tab_year('YYYY', --> Year 2001 338 'SYYY', --> Year BC prefixed with - 339 'IYYY', --> ISO Year 2001 340 'YY', --> Year 01 341 'RR', --> Year 01 rollover for Y2K compatibility * 342 'YEAR', --> Year spelled out 343 'SYEAR', --> Year spelled out BC prefixed with - 344 'BC'); --> BC/AD Indicator * 345 TYPE tab_quarter IS TABLE OF VARCHAR2(1); 346 t_quarter tab_quarter := tab_quarter('Q'); --> Quarter : Jan-Mar=1, Apr-Jun=2 347 TYPE tab_month IS TABLE OF VARCHAR2(7); 348 t_month tab_month := tab_month('MM', --> Month of year 01, 02...12 349 'RM', --> Roman Month I, II...XII * 350 'MONTH', --> In full [January ]...[December ] 351 'FMMONTH', --> In full [January]...[December] 352 'MON'); --> JAN, FEB 353 TYPE tab_week IS TABLE OF VARCHAR2(2); 354 t_week tab_week := tab_week('WW', --> Week of year 1-52 355 'W', --> Week of month 1-5 356 'IW'); --> ISO std week of year 357 358 TYPE tab_day IS TABLE OF VARCHAR2(6); 359 t_day tab_day := tab_day('DDD', --> Day of year 1-366 * 360 'DD', --> Day of month 1-31 361 'D', --> Day of week 1-7 362 'DAY', --> In full [Monday ]...[Sunday ] 363 'FMDAY', --> In full [Monday]...[Sunday] 364 'DY', --> MON...SUN 365 'DDTH', --> Ordinal Day 7TH 366 'DDSPTH', --> Spell out ordinal SEVENTH 367 'J'); --> Julian Day (days since 31/12/4713) 368 TYPE tab_title_lower IS TABLE OF VARCHAR2(5); 369 t_title_lower tab_title_lower := tab_title_lower('OF','DE','DES','LA','LAS', 370 'LE','LES','DA','AND','IN','ON', 371 'BY','THE','FOR'); 372 /** 373 * used for function is_date 374 */ 375 FUNCTION is_date_local 376 (in_value IN VARCHAR2, 377 in_format IN VARCHAR2) 378 RETURN BOOLEAN IS 379 retDate DATE; 380 BEGIN 381 retDate := TO_DATE(in_value,in_format); 382 RETURN TRUE; 383 EXCEPTION 384 WHEN OTHERS THEN 385 RETURN FALSE; 386 END is_date_local; 387 /** 388 * Used for processing internal casing in funciton TITLE 389 */ 390 FUNCTION f_internal_title 391 (in_value IN VARCHAR2, 392 in_split IN VARCHAR2 DEFAULT '-') 393 RETURN VARCHAR2 IS 394 k INTEGER; 395 retSplit VARCHAR2(32767); 396 varEval VARCHAR2(32767); 397 t_col_split str.tab_split; 398 boolRtrim BOOLEAN; 399 BEGIN 400 --> Check to see if split item needs to be added back to the end of the string 401 If SUBSTR(in_value,LENGTH(in_value),1) = in_split Then 402 boolRtrim := TRUE; 403 Else 404 boolRtrim := FALSE; 405 End If; 406 --> set up array to be split 407 t_col_split := str.split_array('**' || in_value || '**',in_split); 408 -- 409 FOR k IN t_col_split.FIRST .. t_col_split.LAST LOOP 410 -- 411 varEval := UPPER(SUBSTR(t_col_split(k),1,1)) || SUBSTR(t_col_split(k),2,Length(t_col_split(k))); -- 412 If retSplit IS NULL Then 413 retSplit := varEval; 414 Else 415 retSplit := retSplit || in_split || varEval; 416 End If; 417 -- 418 END LOOP; 419 -- 420 If boolRtrim Then 421 retSplit := retSplit || in_split; 422 End If; 423 -- 424 If boolRtrim Then 425 RETURN SUBSTR(retSplit,3,(LENGTH(retSplit)-5)); 426 Else 427 RETURN SUBSTR(retSplit,3,(LENGTH(retSplit)-4)); 428 End If; 429 -- 430 END f_internal_title; 431 -- ******************************************************************************** -- 432 -- Public Package Functions, Procedures, Cursors and Variabels 433 -- ******************************************************************************** -- 434 FUNCTION split 435 (in_del_field IN VARCHAR2, 436 in_pos IN NUMBER, 437 in_del IN VARCHAR2) 438 RETURN VARCHAR2 IS 439 retVal VARCHAR2(4000) := NULL; 440 varFldDel VARCHAR2(2000) := LTRIM(RTRIM(in_del_field)) || in_del; 441 BEGIN 442 -- 443 If in_pos = 1 Then 444 -- 445 retVal := SUBSTR(varFldDel,1,(INSTR(varFldDel,in_del,1,in_pos)-1)); 446 -- 447 ElsIf in_pos > 1 Then 448 -- 449 retVal := SUBSTR(varFldDel,(INSTR(varFldDel,in_del,1,(in_pos-1))+1), 450 (INSTR(varFldDel,in_del,1,in_pos)- 451 (INSTR(varFldDel,in_del,1,(in_pos-1))+1))); 452 -- 453 End If; 454 -- 455 RETURN retVal; 456 -- 457 END split; 458 -- ******************************************************************************** -- 459 FUNCTION split_array 460 (in_del_field IN VARCHAR2, 461 in_del IN VARCHAR2) 462 RETURN tab_split IS 463 t_return tab_split := tab_split(NULL); 464 i INTEGER := 1; 465 BEGIN 466 -- 467 LOOP 468 -- 469 EXIT WHEN str.split(in_del_field,i,in_del) IS NULL; 470 -- 471 If i > 1 Then 472 t_return.EXTEND; 473 End If; 474 -- 475 t_return(i) := str.split(in_del_field,i,in_del); 476 i := i + 1; 477 -- 478 END LOOP; 479 -- 480 RETURN t_return; 481 -- 482 END split_array; 483 -- ******************************************************************************** -- 484 FUNCTION split_pipe 485 (in_del_field IN VARCHAR2, 486 in_del IN VARCHAR2) 487 RETURN tab_str PIPELINED IS 488 t_split str.tab_split := str.split_array(in_del_field,in_del); 489 i INTEGER; 490 out_row rec_str; 491 BEGIN 492 -- 493 FOR i IN t_split.FIRST .. t_split.LAST LOOP 494 out_row.STRING_VALUE := t_split(i); 495 PIPE ROW (out_row); 496 END LOOP; 497 -- 498 RETURN; 499 -- 500 END split_pipe; 501 -- ******************************************************************************** -- 502 FUNCTION is_upper_case 503 (in_string IN VARCHAR2) 504 RETURN BOOLEAN IS 505 i INTEGER; 506 j INTEGER := 0; 507 varSearch VARCHAR2(32767) := REPLACE(in_string,' ',''); 508 retBool BOOLEAN := FALSE; 509 intAscii INTEGER; 510 BEGIN 511 If varSearch IS NOT NULL Then 512 FOR i IN 1 .. LENGTH(varSearch) LOOP 513 intAscii := ASCII(SUBSTR(in_string,i,1)); 514 If intAscii > 64 AND 515 intAscii < 123 AND 516 intAscii NOT IN (91,92,93,94,95,96) 517 Then 518 If intAscii <= 90 AND 519 intAscii >= 65 520 Then 521 retBool := TRUE; 522 Else 523 j := j + 1; 524 End If; 525 End If; 526 END LOOP; 527 If j > 0 Then 528 retBool := FALSE; 529 End If; 530 End If; 531 RETURN retBool; 532 END is_upper_case; 533 -- ******************************************************************************** -- 534 FUNCTION is_lower_case 535 (in_string IN VARCHAR2) 536 RETURN BOOLEAN IS 537 i INTEGER; 538 j INTEGER := 0; 539 varSearch VARCHAR2(32767) := REPLACE(in_string,' ',''); 540 retBool BOOLEAN := FALSE; 541 intAscii INTEGER; 542 BEGIN 543 If varSearch IS NOT NULL Then 544 FOR i IN 1 .. LENGTH(varSearch) LOOP 545 intAscii := ASCII(SUBSTR(in_string,i,1)); 546 If intAscii > 64 AND 547 intAscii < 123 AND 548 intAscii NOT IN (91,92,93,94,95,96) 549 Then 550 If intAscii <= 122 AND 551 intAscii >= 97 552 Then 553 retBool := TRUE; 554 Else 555 j := j + 1; 556 End If; 557 End If; 558 END LOOP; 559 If j > 0 Then 560 retBool := FALSE; 561 End If; 562 End If; 563 RETURN retBool; 564 END is_lower_case; 565 -- ******************************************************************************** -- 566 FUNCTION is_mixed_case 567 (in_string IN VARCHAR2) 568 RETURN BOOLEAN 569 IS 570 BEGIN 571 If NOT is_upper_case(in_string) AND 572 NOT is_lower_case(in_string) 573 Then 574 RETURN TRUE; 575 Else 576 RETURN FALSE; 577 End If; 578 END is_mixed_case; 579 -- ******************************************************************************** -- 580 FUNCTION is_number 581 (in_value IN VARCHAR2) 582 RETURN BOOLEAN IS 583 retNum NUMBER(10); 584 BEGIN 585 retNum := TO_NUMBER(in_value); 586 RETURN TRUE; 587 EXCEPTION 588 WHEN OTHERS THEN 589 RETURN FALSE; 590 END is_number; 591 -- ******************************************************************************** -- 592 FUNCTION is_number_sql 593 (in_value IN VARCHAR2) 594 RETURN VARCHAR2 IS 595 retVal VARCHAR2(5) := 'FALSE'; 596 BEGIN 597 If is_number(in_value) Then 598 retVal := 'TRUE'; 599 End If; 600 RETURN retVal; 601 END is_number_sql; 602 -- ******************************************************************************** -- 603 FUNCTION is_ordinal 604 (in_value IN VARCHAR2) 605 RETURN BOOLEAN IS 606 i INTEGER; 607 boolNumber BOOLEAN := FALSE; 608 boolOrdinal BOOLEAN := FALSE; 609 BEGIN 610 -- 611 boolOrdinal := FALSE; 612 --> determine if ordinal 613 FOR i IN 1 .. LENGTH(in_value) LOOP 614 -- 615 If str.is_number(SUBSTR(in_value,i,1)) Then 616 boolOrdinal := FALSE; 617 boolNumber := TRUE; 618 Else 619 If boolNumber Then 620 If UPPER(SUBSTR(in_value,i,LENGTH(in_value))) IN ('ST','ND','RD','TH') Then 621 boolOrdinal := TRUE; 622 End If; 623 End If; 624 boolNumber := FALSE; 625 End If; 626 -- 627 END LOOP; 628 -- 629 RETURN boolOrdinal; 630 -- 631 END is_ordinal; 632 -- ******************************************************************************** -- 633 FUNCTION is_ordinal_sql 634 (in_value IN VARCHAR2) 635 RETURN VARCHAR2 IS 636 BEGIN 637 If is_ordinal(in_value) Then 638 RETURN 'TRUE'; 639 Else 640 RETURN 'FALSE'; 641 End If; 642 END is_ordinal_sql; 643 -- ******************************************************************************** -- 644 FUNCTION is_roman_numeral 645 (in_value IN VARCHAR2) 646 RETURN BOOLEAN IS 647 intV INTEGER := 0; 648 intX INTEGER := 0; 649 intI INTEGER := 0; 650 i INTEGER; 651 BEGIN 652 -- 653 FOR i IN 1 .. LENGTH(in_value) LOOP 654 -- 655 CASE SUBSTR(UPPER(in_value),i,1) 656 WHEN 'X' Then 657 intX := intX + 1; 658 WHEN 'I' Then 659 intI := intI + 1; 660 WHEN 'V' Then 661 intV := intV + 1; 662 ELSE NULL; 663 END CASE; 664 -- 665 END LOOP; 666 -- 667 If LENGTH(in_value) = intX + intI + intV Then 668 RETURN TRUE; 669 Else 670 RETURN FALSE; 671 End If; 672 -- 673 END is_roman_numeral; 674 -- ******************************************************************************** -- 675 FUNCTION is_roman_numeral_sql 676 (in_value IN VARCHAR2) 677 RETURN VARCHAR2 IS 678 BEGIN 679 If is_roman_numeral(in_value) Then 680 RETURN 'TRUE'; 681 Else 682 RETURN 'FALSE'; 683 End If; 684 END is_roman_numeral_sql; 685 -- ******************************************************************************** -- 686 FUNCTION is_date 687 (in_value IN VARCHAR2) 688 RETURN BOOLEAN IS 689 d INTEGER; 690 m INTEGER; 691 y INTEGER; 692 varDate VARCHAR2(32767) := UPPER(REPLACE(REPLACE(REPLACE(REPLACE(in_value,'-',''),'/',''),'\',''),' ','')); 693 BEGIN 694 --> RESET DATE FORMAT 695 globDateFormat := NULL; 696 globLastDateValue := in_value; 697 -- 698 FOR d IN t_day.FIRST .. t_day.LAST LOOP 699 --> Check Day 700 If is_date_local(varDate,t_day(d)) Then 701 globDateFormat := t_day(d); 702 RETURN TRUE; 703 Else --> Check Day Month/Month Day 704 FOR m IN t_month.FIRST .. t_month.LAST LOOP 705 -- 706 If is_date_local(varDate,t_day(d) || t_month(m)) Then 707 globDateFormat := t_day(d) || t_month(m); 708 RETURN TRUE; 709 End If; 710 -- 711 If is_date_local(varDate,t_month(m) || t_day(d)) Then 712 globDateFormat := t_month(m) || t_day(d); 713 RETURN TRUE; 714 End If; 715 --> Check Year 716 FOR y IN t_year.FIRST .. t_year.LAST LOOP 717 --> check DAY MONTH YEAR 718 If is_date_local(varDate,t_day(d) || t_month(m) || t_year(y)) Then 719 globDateFormat := t_day(d) || t_month(m) || t_year(y); 720 RETURN TRUE; 721 End If; 722 --> check DAY YEAR MONTH 723 If is_date_local(varDate,t_day(d) || t_year(y) || t_month(m)) Then 724 globDateFormat := t_day(d) || t_year(y) || t_month(m); 725 RETURN TRUE; 726 End If; 727 --> check MONTH YEAR DAY 728 If is_date_local(varDate,t_month(m) || t_year(y) || t_day(d)) Then 729 globDateFormat := t_month(m) || t_year(y) || t_day(d); 730 RETURN TRUE; 731 End If; 732 --> check MONTH DAY YEAR 733 If is_date_local(varDate,t_month(m) || t_day(d) || t_year(y)) Then 734 globDateFormat := t_month(m) || t_day(d) || t_year(y); 735 RETURN TRUE; 736 End If; 737 --> check YEAR DAY MONTH 738 If is_date_local(varDate,t_year(y) || t_day(d) || t_month(m)) Then 739 globDateFormat := t_year(y) || t_day(d) || t_month(m); 740 RETURN TRUE; 741 End If; 742 --> check YEAR MONTH DAY 743 If is_date_local(varDate,t_year(y) || t_month(m) || t_day(d)) Then 744 globDateFormat := t_year(y) || t_month(m) || t_day(d); 745 RETURN TRUE; 746 End If; 747 -- 748 END LOOP; 749 y := NULL; 750 -- 751 END LOOP; 752 m := NULL; 753 End If; 754 -- 755 END LOOP; 756 -- 757 RETURN FALSE; 758 -- 759 END is_date; 760 -- ******************************************************************************** -- 761 FUNCTION is_date_sql 762 (in_value IN VARCHAR2) 763 RETURN VARCHAR2 IS 764 retVal VARCHAR2(5) := 'FALSE'; 765 BEGIN 766 If is_date(in_value) Then 767 retVal := 'TRUE'; 768 End If; 769 RETURN retVal; 770 END is_date_sql; 771 -- ******************************************************************************** -- 772 FUNCTION is_date_format 773 RETURN VARCHAR2 IS 774 retVal VARCHAR2(32767) := globDateFormat; 775 s INTEGER; 776 i INTEGER; 777 intInstr INTEGER; 778 BEGIN 779 --> find where dashes and special characters should be and put them back... 780 FOR s IN t_str_strip.FIRST .. t_str_strip.LAST LOOP 781 i := 1; 782 LOOP 783 intInstr := INSTR(globLastDateValue,t_str_strip(s),i); 784 EXIT WHEN intInstr < 1; 785 retVal := SUBSTR(retVal,1,intInstr) || t_str_strip(s) || SUBSTR(retVal,intInstr,LENGTH(retVal)); 786 i := i + 1; 787 END LOOP; 788 END LOOP; 789 -- 790 RETURN globDateFormat; 791 -- 792 END is_date_format; 793 -- ******************************************************************************** -- 794 FUNCTION title 795 (in_value IN VARCHAR2) 796 RETURN VARCHAR2 IS 797 i INTEGER; 798 j INTEGER; 799 s VARCHAR2(1) := ' '; 800 t_split str.tab_split := str.split_array(in_value,s); 801 varEval VARCHAR2(32767); 802 retVal VARCHAR2(32767); 803 boolLower BOOLEAN := FALSE; 804 BEGIN 805 -- 806 If in_value IS NOT NULL Then 807 FOR i IN t_split.FIRST .. t_split.LAST LOOP 808 --> check if word should always be lower case 809 If i > 1 Then 810 j := NULL; 811 FOR j IN t_title_lower.FIRST .. t_title_lower.LAST LOOP 812 -- 813 If UPPER(t_split(i)) = UPPER(t_title_lower(j)) Then 814 boolLower := TRUE; 815 varEval := LOWER(t_split(i)); 816 End If; 817 -- 818 END LOOP; 819 End If; 820 -- 821 If NOT boolLower Then 822 varEval := UPPER(SUBSTR(t_split(i),1,1)) 823 || LOWER(SUBSTR(t_split(i),2,LENGTH(t_split(i)))); 824 End If; 825 --> look MAC AND MC in names and Ucase correct characters 826 If UPPER(SUBSTR(t_split(i),1,3)) = 'MAC' Then 827 varEval := UPPER(SUBSTR(t_split(i),1,1)) 828 || LOWER (SUBSTR(t_split(i),2,2)) 829 || UPPER(SUBSTR(t_split(i),4,1)) 830 || LOWER(SUBSTR(t_split(i),5,LENGTH(t_split(i)))); 831 ElsIf UPPER(SUBSTR(t_split(i),1,2)) = 'MC' Then 832 varEval := UPPER(SUBSTR(t_split(i),1,1)) 833 || LOWER(SUBSTR(t_split(i),2,1)) 834 || UPPER(SUBSTR(t_split(i),3,1)) 835 || LOWER(SUBSTR(t_split(i),4,LENGTH(t_split(i)))); 836 End If; 837 --> for each value in array, split with a - and upper anything after the - 838 varEval := f_internal_title(varEval,'-'); 839 --> for each value in array, split with a / and upper anything after the / 840 varEval := f_internal_title(varEval,'/'); 841 --> for each value in array, split with a \ and upper anything after the \ 842 varEval := f_internal_title(varEval,'\'); 843 --> for each value in array, split with a . and upper anything after the . 844 varEval := f_internal_title(varEval,'.'); 845 --> for each value in array, split with a , and upper anything after the , 846 varEval := f_internal_title(varEval,','); 847 --> for each value in array, split with a ( and upper anything after the ( 848 varEval := f_internal_title(varEval,'('); 849 --> for each value in array, split with a " and upper anything after the " 850 varEval := f_internal_title(varEval,'"'); 851 --> for each value in array, split with a , and upper anything after the ' 852 If SUBSTR(varEval,LENGTH(varEval)-1,1) <> '''' Then 853 varEval := f_internal_title(varEval,''''); 854 End If; 855 -- 856 If i > 1 Then 857 retVal := retVal || s || varEval; 858 Else 859 retVal := varEval; 860 End If; 861 boolLower := FALSE; 862 END LOOP; 863 End If; 864 -- 865 RETURN retVal; 866 -- 867 END title; 868 -- ******************************************************************************** -- 869 FUNCTION toggle 870 (in_value IN VARCHAR2) 871 RETURN VARCHAR2 IS 872 i INTEGER; 873 varEval VARCHAR2(1); 874 retVal VARCHAR2(32767); 875 BEGIN 876 -- 877 FOR i IN 1 .. LENGTH(in_value) LOOP 878 --> get current character to evaluate 879 varEval := SUBSTR(in_value,i,1); 880 --> Evaluation character 881 If is_upper_case(varEval) Then 882 retVal := retVal || LOWER(varEval); 883 ElsIf is_lower_case(varEval) Then 884 retVal := retVal || UPPER(varEval); 885 Else 886 retVal := retVal || varEval; 887 End If; 888 -- 889 END LOOP; 890 -- 891 RETURN retVal; 892 -- 893 END toggle; 894 -- ******************************************************************************** -- 895 FUNCTION remove_dbl_space 896 (in_value IN VARCHAR2, 897 in_char IN VARCHAR2 DEFAULT ' ') 898 RETURN VARCHAR2 IS 899 s VARCHAR2(32767) := in_char; 900 ss VARCHAR2(32767) := in_char || in_char; 901 i INTEGER := 1; 902 retVal VARCHAR2(32767) := in_value; 903 BEGIN 904 -- 905 If in_value IS NOT NULL Then 906 -- 907 LOOP 908 -- 909 EXIT WHEN INSTR(retVal,ss) < 1; 910 -- 911 retVal := REPLACE(retVal,ss,s); 912 -- 913 i := i + 1; 914 -- 915 END LOOP; 916 -- 917 End If; 918 -- 919 RETURN retVal; 920 -- 921 END remove_dbl_space; 922 -- ******************************************************************************** -- 923 END str; 924 /
I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself.
我相信我们成为怎样的人是我们自己的选择。没有人会来拯救你，你必须要自己拯救自己。
