(495) 925-0049, ITShop - 229-0436, 925-0049
 

 
 
 
 
 

: , Oracle ACE

,

: , , . . PL / SQL , ?

: , .

PL / SQL (, , PL / SQL ) , , . , , , . ? , .

: ? , , , .

, , , , . , , ORA -06502 VALUE _ ERROR , ( ) , , . :

DECLARE

l_name VARCHAR2(5) := 'STEVEN';

l_age NUMBER := '49 Years Old';

BEGIN

, ( ). , , . PL / SQL .

, PL / SQL :

1. valerr , . (. 1).

2. 1: .

3. PACKAGE valerr

4. IS

5. FUNCTION little_name RETURN VARCHAR2;

6. END valerr;

7.

8. PACKAGE BODY valerr

9. IS

10. g_name VARCHAR2 (1) := 'Liu';

11.

12. FUNCTION little_name RETURN VARCHAR2

13. IS

14. BEGIN

15. RETURN g_name;

16. END little_name;

17. BEGIN

18. DBMS_OUTPUT.put_line ('Before I show you the name... ');

19. EXCEPTION

20. WHEN OTHERS

21. THEN

22. DBMS_OUTPUT.put_line ( 'Trapped the error: ' // DBMS_UTILITY.format_error_stack ()

23. );

24. RAISE;

25. END valerr;

26. valerr . little _ name ,

[ : " the exception goes unhandled ", , . . ]:

SQL> BEGIN

2 DBMS_OUTPUT.put_line

('Name: ' // valerr.little_name);

3 END;

4 /

BEGIN

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value

error: character string buffer too small

ORA-06512: at "HR.VALERR", line 3

ORA-06512: at line 2

.

27. , :

28. SQL> BEGIN

29. 2 DBMS_OUTPUT.put_line

30. ('Name: ' // Valerr.little_name);

31. 3 END;

32. 4 /

33.

34. Name:

35. PL/SQL procedure successfully completed.

, PL / SQL , . , , .

- , . , Oracle .

, Oracle ( 5658561). , Oracle , Oracle MetaLink , .

? , . , , , , , .

: . . , . , , , :

PROCEDURE process_data

IS

l_name VARCHAR2 (10) :=

'Steven Feuerstein';

BEGIN

DBMS_OUTPUT.put_line (l_name);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line

( 'Trapped the error: '

// DBMS_UTILITY.format_

error_stack ()

);

RAISE;

END process_data;

:

PROCEDURE process_data

IS

l_name VARCHAR2 (10);

PROCEDURE initialize

IS

BEGIN

l_name := 'Steven Feuerstein';

END initialize;

BEGIN

initialize;

DBMS_OUTPUT.put_line (l_name);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line

( 'Trapped the error: '

// DBMS_UTILITY.format_

error_stack ()

);

RAISE;

END process_data;

, process_data, , :

SQL> BEGIN

2 process_data;

3 END;

4 /

Trapped the error: ORA-06502:

PL/SQL: numeric or value error:

character string buffer too small

BEGIN

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value

error: character string buffer too small

ORA-06512: at "HR.PROCESS_DATA",

line 19

ORA-06512: at line 2

. valerr 2, .

2:

1 PACKAGE BODY valerr

2 IS

3 g_name VARCHAR2 (1);

4

5 FUNCTION little_name

6 RETURN VARCHAR2

7 IS

8 BEGIN

9 RETURN g_name;

10 END little_name;

11

12 PROCEDURE initialize

13 IS

14 BEGIN

15 g_name := 'Lu';

16 END initialize;

17 BEGIN

18 initialize;

19 EXCEPTION

20 WHEN OTHERS

21 THEN

22 DBMS_OUTPUT.put_line ( 'Trapped the error: ' // DBMS_UTILITY.format_error_stack ()

23 );

24 RAISE;

25 END valerr;

, ( ). -, . . -, valerr ( 2) g _ name 3, 15. , , , , . .

, , . . , ? , .

: , , , .

, . , , , , . , , (. 3).

3: .

PROCEDURE process_data

IS

l_name VARCHAR2 (100) := 'Steven Feuerstein';

l_books_sold PLS_INTEGER;

PROCEDURE initialize

IS

BEGIN

l_books_sold := book_counter.in_circulation ('Oracle PL/SQL Programming');

END initialize;

BEGIN

initialize;

DBMS_OUTPUT.put_line (

l_name

// ' sold '

// l_books_sold

// ' books.');

EXCEPTION

WHEN OTHERS

THEN

q$error_manager.raise_unanticipated;

RAISE;

END process_data;

, , . - DECLARE - BEGIN - END , . , , , , .

, . " " , , .

, , process _ data 1_ books _ sold 245 . , l _ name , , , , . , . 4 process _ data , .

4: PROCESS_DATA

PROCEDURE process_data

IS

l_name VARCHAR2 (100) := 'Steven Feuerstein';

BEGIN

/*

l_name

*/

IF l_name IS NOT NULL

THEN

... ...

END IF;

/*

...

, l _ books _ sold

, .

*/

<>

DECLARE

l_books_sold PLS_INTEGER := book_counter.in_circulation ('Oracle PL/SQL Programming');

BEGIN

IF l_books_sold > 1000

THEN

... ...

END IF;

EXCEPTION

WHEN VALUE_ERROR

THEN

q$error_manager.raise_unanticipated

('Problem initializing l_books_sold!');

RAISE;

END check_books_sold;

... ...

END process_data;

: Oracle Database 10 g Release 1, PL / SQL . , , "" ( PLW -6002). , Oracle :

DECLARE

l_name VARCHAR2(5) := 'STEVEN';

l_age NUMBER := '49 Years Old';

BEGIN

, .

.

, - . , , , . , SELECT INTO ( ), , ( , ). Oracle NO _ DATA _ FOUND , . SELECT INTO ?!

: - , . WHEN , , . , .

, WHEN , ( ). , SELECT INTO , . , SELECT INTO ? .

SELECT INTO NO _ DATA _ FOUND , TOO _ MANY _ ROWS , . . :

,

.

, , . , , UTL _ FILE . GET _ LINE , NO _ DATA _ FOUND , .

, , , . , , , . NO _ DATA _ FOUND , SELECT INTO , .

" ", , . TOO _ MANY _ ROWS - ; , .

, . , , , . , :

A. . , . , UTL _ FILE . GET _ LINE process _ file 5, . 16 18: ( EXIT ), , UTL _ FILE NO _ DATA _ FOUND .

5: PROCESS_FILE UTL_FILE.GET_LINE .

1 PROCEDURE process_file (dir_in IN VARCHAR2, file_in IN VARCHAR2)

2 IS

3 TYPE line_t IS TABLE OF VARCHAR2 (32767)

4 INDEX BY PLS_INTEGER;

5

6 l_file UTL_FILE.file_type;

7 l_lines line_t;

8 BEGIN

9 l_file :=

10 UTL_FILE.fopen (LOCATION => dir_in

11 , filename => file_in

12 , open_mode => 'R'

13 , max_linesize => 32767

14 ) ;

15

16 LOOP

17 UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));

18 END LOOP;

19 EXCEPTION

20 WHEN NO_DATA_FOUND

21 THEN

22 /* */

23 FOR indx IN 1 .. l_lines.COUNT

24 LOOP

25 do_stuff_with_line (l_lines (indx));

26 END LOOP;

27

28 UTL_FILE.fclose (l_file);

29 END process_file;

, . , , , . ?

UTL _ FILE . GET _ LINE ! 6 , , . get _ next _ line , UTL _ FILE . GET _ LINE . NO _ DATA _ FOUND TRUE ( OUT ) , .

6: PROCESS_FILE

1 PROCEDURE process_file (dir_in IN VARCHAR2, file_in IN VARCHAR2)

2 IS

3 TYPE line_t IS TABLE OF VARCHAR2 (32767)

4 INDEX BY PLS_INTEGER;

5

6 l_file UTL_FILE.file_type;

7 l_lines line_t;

8 l_eof BOOLEAN := FALSE;

9

10 PROCEDURE get_next_line (line_out OUT VARCHAR2, eof_out OUT BOOLEAN)

11 IS

12 BEGIN

13 UTL_FILE.get_line (l_file, line_out);

14 eof_out := FALSE;

15 EXCEPTION

16 WHEN NO_DATA_FOUND

17 THEN

18 line_out := NULL;

19 eof_out := TRUE;

20 END get_next_line;

21 BEGIN

22 l_file :=

23 UTL_FILE.fopen (LOCATION => dir_in

24 , filename => file_in

25 , open_mode => 'R'

26 , max_linesize => 32767

27 );

28

29 WHILE (NOT l_eof)

30 LOOP

31 get_next_line (l_lines (l_lines.COUNT + 1), l_eof);

32 EXIT WHEN l_eof;

33 END LOOP;

34

35 /* */

36 FOR indx IN 1 .. l_lines.COUNT

37 LOOP

38 do_stuff_with_line (l_lines (indx));

39 END LOOP;

40

41 UTL_FILE.fclose (l_file);

42 END process_file;

WHILE , , . .

, , , , ( , ).

B. . . , , , , . , .

INSERT , , , .

C. . , , NO _ DATA _ FOUND . , - . , : .

NO _ DATA _ FOUND , , , ID :

FUNCTION id_for_name (

department_name_in IN departments

.department_name%TYPE

)

RETURN departments.department_id%TYPE

IS

l_return departments

.department_id%TYPE;

BEGIN

SELECT department_id

INTO l_return

FROM departments

WHERE department_name =

department_name_in;

RETURN l_return;

END id_for_name;

, , . , Oracle Advanced Queuing . , . , , :

PROCEDURE load_from_staging_table

IS

l_id departments.department_id%TYPE;

BEGIN

FOR dept_rec IN (SELECT *

FROM dept_staging_table)

LOOP

BEGIN

l_id := id_for_name

(dept_rec.department_name);

submit_update_request (dept_rec);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

submit_add_request (dept_rec);

END;

END LOOP;

END load_from_staging_table;

, id _ for _ name NO _ DATA _ FOUND . , , " " ( submit _ add _ request ) , .

, : . , ?

, id _ for _ name , , ( 7). . , propagate _ if _ ndf _ in , NO _ DATA _ FOUND ( SELECT INTO ) .

7: ID_FOR_NAME

1 FUNCTION id_for_name (

2 department_name_in IN departments.department_name%TYPE

3 , propagate_if_ndf_in IN BOOLEAN := FALSE

4 , ndf_value_in IN departments.department_id%TYPE := NULL

5 )

6 RETURN departments.department_id%TYPE

7 IS

8 l_return departments.department_id%TYPE;

9 BEGIN

10 SELECT department_id

11 INTO l_return

12 FROM departments

13 WHERE department_name = department_name_in;

14

15 RETURN l_return;

16 EXCEPTION

17 WHEN NO_DATA_FOUND

18 THEN

19 IF propagate_if_ndf_in

20 THEN

21 RAISE;

22 ELSE

23 RETURN ndf_value_in;

24 END IF;

25 WHEN TOO_MANY_ROWS

26 THEN

27 q$error_manager.raise_unanticipated

28 (text_in => 'Multiple rows found for department name'

29 , name1_in => 'DEPARTMENT_NAME'

30 , value1_in => department_name_in

31 );

32 END id_for_name;

, ndf _ value _ in , , , , . , NULL , " " (" no data found "), (, , ) . ?

, NO _ DATA _ FOUND , ( RAISE ; 21), . " " (" no data found ").

, TOO _ MANY _ ROWS , , ID , .

, id _ for _ name , ( 8). -1 , . "" -1 , . , .

8: ID_FOR_NAME

PROCEDURE load_from_staging_table

IS

c_no_such_dept CONSTANT PLS_INTEGER := -1;

l_id departments.department_id%TYPE;

BEGIN

FOR dept_rec IN (SELECT * FROM dept_staging_table)

LOOP

BEGIN

l_id :=

id_for_name (dept_rec.department_name

, raise_if_ndf_in => FALSE

, ndf_value_in => c_no_such_dept

);

IF l_id = c_no_such_dept

THEN

submit_update_request (dept_rec);

ELSE

submit_add_request (dept_rec);

END IF;

END;

END LOOP;

END load_from_staging_table;

, PL / SQL , , . .

. , , , : , , .

, - .



  »
   »
   
  » : 15.06.2009 
 

   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Standard Edition 2 Named User Plus License
IBM RATIONAL Clearcase Floating User From Rational Clearcase Lt Floating User Trade Up License + Sw Subscription & Support 12 Months
 
...
 
   WWW.ITSHOP.RU
 
...
 
   WWW.ITSHOP.RU
 
...
 
3D | 3D    WWW.ITSHOP.RU
 
...
 
 
 Subscribe.ru
: CASE, RAD, ERP, OLAP
ITShop.ru - , , ,
Microsoft Access
CASE-
Oracle " "
.
RP-.
 
 
Download
 
 
 
 



    
rambler's top100 Rambler's Top100