Oracle PL/SQL by Example- P16

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:9

0
61
lượt xem
17
download

Oracle PL/SQL by Example- P16

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle pl/sql by example- p16', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL by Example- P16

  1. 722 pragmas EXCEPTION_INIT, get_student_info, 477 CASE statement projects, 217-221 HTP procedures, 112, 622-626 restrictions, 504 581-582, 586 collection projects, RESTRICT_ image procedures, 589 348, 652-659 REFERENCES, 500-506 insert_zip, 674-675 compound trigger predefined exceptions. projects, 313, 648-651 INTERVAL, 564 See exceptions cursor projects, median_grade, 487-488 premature termination 252, 643-645 NEW_LINE, 561 of FOR loops, 136-137 error handling projects, NEXT_DATE, 564 178, 632-635 of WHILE loops, 126-128 overview, 439 e_non_null_value PREREQUISITE column parameters exception, 641-642 (COURSE table), 601 actual parameters, e_too_many_students PRIOR collection method, 323 444-445 exception, 635-639 private objects, 465-469 datatype invalid instructor IDs, procedures. See also constraints, 445 handling, 634-635 functions; methods formal parameters, invalid student IDs, anonymous blocks, 440 444-445 handling, 632-634 benefits of, 439 IN parameters, exception projects, 209, block structure, 440 445-446 227, 635-642 BROKEN, 564 modes, 444 function projects, 455, CHANGE, 564 OUT parameters, 679-681 creating, 441-442 445-446 instructor_status PUT, 561 function, 680-681 current_status, 673-674 PUTF 561 , new_student_id display_student_ function, 679 count, 466 PUT_LINE, 561 zip_does_not_exist dynamic_sql_pkg, 677 querying data dictionary function, 679-680 FCLOSE, 560 for information on, 443 IF statement projects, FCLOSE_ALL, 560 REMOVE, 564 80, 619-622 FFLUSH, 561 remove_student, 681-690 iterative control projects, final_grade, 481, 484-487 RUN, 564 142, 161, 627-632 find_sname, 446, 461 SUBMIT, 564-565 CONTINUE WHEN form procedures, profiling PL/SQL, 556-559 statement, 629-630 587-588 PROGRAM_ERROR FOR loops, 627-628 FRAMESET exception, 171 simple loops, 628-629 procedures, 587 projects (try-it-yourself ) WHILE loops, GET_LINE, 561 block projects, 626-627 get_name_address, 37, 614-616 object type projects, 554, 675-676 bulk SQL projects, 696-703 437, 665-672 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. REGISTRATION_DATE column (STUDENT table) 723 package projects, PUT procedure, 561 cursor-based records, 493, 681-690 PUTF procedure, 561 233-235 PL/SQL in client/ PUT_LINE procedure, 561 student_rec example, server architecture, 350-353 19, 613-614 zip_rec example, Q PL/SQL in SQL*Plus, 358-362 19, 613-614 QL*Plus, PL/SQL in definition, 233, 349 procedure projects, DBMS_OUTPUT.PUT_LI fetching with BULK 447, 673-678 NE statement, 16-18 COLLECT statement, current_status overview, 12-13 422-423 procedure, 673-674 substitution variables, nesting, 367-372 dynamic_sql_pkg 13-17 %ROWTYPE procedure, 677 try-it-yourself projects, attribute, 350 get_name_address 19, 613-614 table-based records, procedure, 675-676 querying 233-234 insert_zip procedure, data dictionary course_rec example, 674-675 for procedure 350-353 record projects, 378, information, 443 zip_rec example, 659-665 data dictionary for 358-362 stored code projects, 512 stored code information, try-it-yourself projects, get_course_descript 496-500 378, 659-665 function, 690 scalar subqueries, 611 user-defined records get_course_description creating, 353, 659-665 function, 691-696 R NOT NULL constraint, transaction projects, 51 354-355 RAISE_APPLICATION_ERROR CHAP4 table, statement, 212-216, 639-640 time_rec_type creating, 616 example, 353-355 RAISE statement, 191 CHAP4_SEQ zip_info_rec example, raising user-defined sequence, 362-366 exceptions, 189-191 creating, 616 reading REF CURSOR keywords, 472 PL/SQL block script, binary files, 559 referencing package 617-619 elements, 460 trigger projects, 290 operating system files, 559-562 REGEXP_COUNT function, compound triggers, xvii-xviii 648-651 READ_ERROR exception, 561 REGEXP_INSTR function, xviii creating triggers, records REGEXP_SUBSTR function, xviii 645-648 collections of records, 373-377 REGISTRATION_DATE column propagating exceptions, (STUDENT table), 602 197-206 compatibility, 355-357 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. 724 regular expressions regular expressions. rows, fetching in cursors, SECTION table, 601-602 See expressions 236-237 SECTION_ID column regular joins, 608-609 %ROWTYPE attribute, 350 ENROLLMENT REMOVE procedure, 564 RTRIM function, 60 table, 602 remove_student procedure, rules for package bodies, 460 GRADE table, 605 681-690 RUN procedure, 564 GRADE_TYPE table, 604 reraising exceptions, 201-202, runtime errors, 11, 164-167 SECTION table, 601 206-208 SECTION_NO column (SECTION reserved words. See table), 601 also statements S SELECT INTO statement, 40-42 BEGIN, 6 SALUTATION column SELECT list cursors, 245 CONSTANT, 5 INSTRUCTOR table, 603 sequences DECLARE, 5 STUDENT table, 602 accessing, 43 definition, 23, 26 SAVE EXCEPTIONS option (FORALL statement), 408-409 CHAP4_SEQ sequence, END, 6 creating, 616 SAVEPOINT statement, 47-49 EXCEPTION, 6, 188 definition, 43 scalar subqueries, 611 IN, 132 drawing numbers scheduling jobs, 563-567 invalid use of, 26-27 from, 43-44 school_api package, LOOP, 114 in expressions, xx-xxi 461-463, 467 REF CURSOR, 472 in PL/SQL blocks, 44 scode_at_line function, 497 REVERSE, 132, 135-136 incrementing values, 43 scope RESTRICT_REFERENCES student_id_seq, 44 of cursors, 245 pragma, 500-506 server-side HTML image of exceptions RETURN statement, 115, 450 maps, 589-592 examples, 180-183 RETURNING option (BULK SET statement, 15 COLLECT statement), 426-427 sample script, 183-187 show error command, 443 REVERSE option (FOR loops), PL/Scope, xxvii show_description 135-136, 139-141 of user-defined function, 451-453 Rischert , Alice, 568 exceptions, 191-192 simple loops RNDS pragma restriction, 504 of variables, 34 EXIT condition RNPS pragma restriction, 504 searched CASE statements sample script, 118-120 ROLLBACK statement, 47 differences between CASE syntax, 114-115 and searched CASE, rolling back transactions, 47-49 EXIT WHEN condition 86-89 row triggers, 277, 283-285 sample script, 120-123 displaying letter grade %ROWCOUNT attribute for student, 91-95 syntax, 116-117 (cursors), 240 example, 86 structure, 114 ROWID datatype, 31 syntax, 84-85 try-it-yourself projects, 628-629 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. statements 725 SIMPLE_DOUBLE datatype, BULK COLLECT CONTINUE WHEN xviii, xx fetching data into overview, 145 SIMPLE_FLOAT datatype, collections, 425-426 sample script, 152-153 xviii, xx fetching records try-it-yourself projects, SIMPLE_INTEGER datatype, with, 422-423 629-630 xviii, xx with FORALL CREATE FUNCTION, single-line comments, 599 statement, 427-428 450-451 specifications LIMIT option, CREATE OR REPLACE object type 423-425 TRIGGER, xxiv specifications, 516 RETURNING option, CREATE OR REPLACE package specifications, 426-427 TYPE, 514 459-461 sample scripts, CREATE TRIGGER, 265 SQL standards 428-436 DBMS_OUTPUT.PUT_LI joins structure, 422 NE, 16-18 CROSS JOIN CASE ELSEIF syntax, 607-608 differences between conditions, 67 EQUI JOIN CASE statement displaying letter grade syntax, 608-609 and CASE for student, 69-73 expression, 97-99 NATURAL JOIN example, 66 syntax, 609-610 displaying name of day, 89-91 examples, 65-68 OUTER JOIN syntax, examples, 83-84 syntax, 65 610-611 overview, 82 END IF 54 , overview, 607 searched CASE END LOOP, 114 scalar subqueries, 611 statements, 84-95 EXECUTE IMMEDIATE SQL statements. See statements syntax, 82 common errors, try-it-yourself 383-386 SQLCODE function, 222-226 projects, 112 overview, 380 SQLERRM function, 222-226 CLOSE passing NULL values, START_TIME_DATE column sample script, 395-400 386-387 (SECTION table), 601 syntax, 394-395 sample script, 387-391 STATE column (ZIPCODE table), 603 COMMIT, 46-47 structure, 381-382 statement triggers, 277-278, CONTINUE, xx EXIT 283-285 compared to EXIT compared to statements statement, 148 CONTINUE statement, 148 ALTER SYSTEM, 564 overview, 144-145 sample script, 118-120 ALTER TRIGGER, sample script, 146-151 265-266 syntax, 114-115 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 726 statements EXIT WHEN invoking functions stored function sample script, 120-123 in, 453-454 requirements in nested IF statements SQL, 503 syntax, 116-117 Celsius/Farenheit try-it-yourself FETCH conversion projects, 512 sample script, 395-400 script, 76-79 get_course_descript syntax, 393-394 function, 690 example, 74-75 FOR UPDATE, 258-260 get_course_description logical operators, FORALL 75-76 function, 691-696 BULK COLLECT OPEN-FOR stored packages, clause, 427-428 calling, 464-465 sample script, 395-400 INDICES OF STREET_ADDRESS column syntax, 392-393 option, 410 INSTRUCTOR table, 603 RAISE, 191 sample script, 413-421 STUDENT table, 602 RAISE_APPLICATION_ SAVE EXCEPTIONS strong cursor variables, 472 ERROR, 639-640 option, 408-409 RETURN, 115, 450 student database simple examples, ROLLBACK, 47 COURSE table, 601 405-408 SAVEPOINT, 47-49 ENROLLMENT structure, 404-405 table, 602 VALUES OF option, SELECT INTO, 40-42 GRADE table, 605 411-412 SET, 15 GRADE_CONVERSION IF-THEN show error, 443 table, 605 example, 54-56 TYPE, 335 GRADE_TYPE table, 604 overview, 54 WHERE CURRENT, 261 GRADE_TYPE_WEIGHT syntax, 54 static methods, 536-538 table, 604 testing whether stored code INSTRUCTOR table, 603 date falls on creating, 451-452 SECTION table, 601-602 weekend, 58-61 enforcing purity level STUDENT table, 602 IF-THEN-ELSE with RESTRICT_ ZIPCODE table, 603 checking number of REFERENCES pragma, students enrolled in 500-506 student IDs course, 62-64 overloaded modules, instructor student IDs, NULL condition, 58 506-511 handling, 634-635 overview, 54 overview, 495 invalid student IDs, handling, 632-634 syntax, 56 querying data directory about, 496-500 STUDENT table, 602 when to use, 56 running, 452-453 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. transactions 727 students submitting jobs to queue, STUDENT, 602 checking number of 564-567 table-based records students enrolled in substitution variables, 13-17 course_rec example, course, 62-64 syntax errors, 10-11 350-353 displaying letter grades SYSDATE function, 619-620 zip_rec example, for, 69-73 358-362 CASE expression, ZIPCODE, 603 100-102 T terminating loops CASE statement, 91-95 table-based records, 233-234 with Boolean NULLIF function, course_rec example, expressions, 126 107-109 350-353 with EXIT condition, displaying number of zip_rec example, 358-362 114-115, 118-120 students for given zip tables with EXIT WHEN code, 183-187 CHAP4 table, condition, 116-117, student_api package creating, 616 120-123 get_course_descript COURSE, 601 TIMESTAMP datatype, 30 function, 690 ENROLLMENT, 602 premature termination, get_course_description GRADE, 605 126-128, 136-137 function, 691-696 GRADE_ time_rec_type record, 353-355 remove_student CONVERSION, 605 TO_CHAR function, 60 procedure, 681-690 GRADE_TYPE, 604 TOO_MANY_ROWS student_count_priv GRADE_TYPE_ exception, 170, 189 function, 466 WEIGHT, 604 transactions STUDENT_ID column INSTRUCTOR, 603 autonomous transactions, ENROLLMENT mutating tables, 292-299 270-272 table, 602 MY_SECTION, 665-672 committing, 46-47 GRADE table, 605 name_tab, 375-377 definition, 39 STUDENT table, 602 PL/SQL tables multiple transactions in student_id_seq sequence, 44 associative arrays, blocks, 50 student_info_pkg package, 477 317-319, 326-330 overview, 45-46 student_obj_type, 696-703 definition, 316-317 rolling back, 47-49 student_rec record, 351 nested tables, 319- try-it-yourself projects, 51 SUBEXPR parameter 321, 330-333 CHAP4 table, (REGEXP_INSTR/REGEXP_ PLAN_TABLE, 568-577 creating, 616 SUBSTR functions), xviii SECTION, 601-602 SUBMIT procedure, 564-565 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. 728 transactions CHAP4_SEQ try-it-yourself IF statement projects, sequence, projects, 290 80, 619-622 creating, 616 compound triggers, iterative control projects, PL/SQL block script, 648-651 142, 161, 627-632 617-619 creating triggers, CONTINUE WHEN triggering events, 264 645-648 statement, 629-630 triggers TRIM collection method, 323 FOR loops, 627-628 AFTER triggers, 269-270, try-it-yourself projects simple loops, 628-629 274-276 block projects, 37, WHILE loops, autonomous transactions, 614-616 626-627 270-272 bulk SQL projects, object type projects, BEFORE triggers, 437, 665-672 554, 696-703 267-269, 274-276 CASE statement projects, package projects, compound triggers, xxiv 112, 622-626 493, 681-690 capabilities, 300 collection projects, PL/SQL in client/server examples, 302-306 348, 652-659 architecture, 19, compound trigger 613-614 modifying, 306-312 projects, 313, 648-651 PL/SQL in SQL*Plus, 19, restrictions, 301 cursor projects, 252, 613-614 structure, 300-301 643-645 procedure projects, 447, try-it-yourself projects, 673-678 error handling projects, 313, 648-651 178, 632-635 current_status controlling, xxiv procedure, 673-674 e_non_null_value creating, 264-265, exception, 641-642 dynamic_sql_pkg 272-273, 645-648 procedure, 677 e_too_many_students definition, 264 exception, 635-639 get_name_address disabling, 265-266 invalid instructor IDs, procedure, 675-676 INSTEAD OF triggers, handling, 634-635 insert_zip procedure, 278-289 invalid student IDs, 674-675 mutating table issues, handling, 632-634 record projects, 292-299 exception projects, 209, 378, 659-665 restrictions, 266-267 227, 635-642 stored code projects, 512 row triggers, 277, function projects, 455, get_course_descript 283-285 679-681 function, 690 statement triggers, instructor_status get_course_description 277-278, 283-285 function, 680-681 function, 691-696 triggering events, 264 new_student_id function, 679 zip_does_not_exist function, 679-680 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. variables 729 transaction projects, 51 time_rec_type v_instructor_name variable, CHAP4 table, example, 353-355 294-295 creating, 616 zip_info_rec v_letter_grade variable, 100 CHAP4_SEQ example, 362-366 v_lname variable, 33 sequence, USER_DEPENDENCIES view, v_new_cost variable, 33 creating, 616 499-500 v_num1 variable, 164 PL/SQL block script, USER_ERRORS view, 498 v_num2 variable, 164 617-619 USER_OBJECTS view, 496 v_num_flag variable, 86-88 trigger projects, 290 UTL_FILE package v_pctincr variable, 33 compound triggers, example, 561-563 648-651 v_radius variable, 613 exceptions, 561 creating triggers, v_regdate variable, 33 functions, procedures, 645-648 v_result variable, 164 and datatypes, 560-561 TYPE statement, 335 v_student_id variable, 36 overview, 559 types. See object types v_student_name variable, 177 v_sum variable, 130 V U v_zip variable, 175-176 v_area variable, 613 uninitialized objects, 517-518 VALUES OF option (FORALL v_average_cost variable, 41 statement), 411-412 uppercase, 597 v_calories_per_cookie VALUE_ERROR exception, USER function, 121 variable, 32 167-168, 171, 185 user-defined exceptions v_cookies_amt variable, 32 VARCHAR2 datatype, 29 declaring, 188 v_counter variable, 33, variable-size arrays. See varrays e_exception1, 201 118-119, 129-130, 138-141, variables, 31. See also specific e_exception2, 201 147-149, 152-153 variables e_invalid_id, 188-191 v_counter1 variable, 154 anchored datatypes, e_my_exception, 192 v_counter2 variable, 154 28-29 e_no_sections, 206-208 v_current_date variable, 469 cursor variables e_too_many_sections, v_date variable, 89, 619-620 capabilities, 471-472 193-196 v_day variable, 90-91 compared to raising, 189-191 v_err_code variable, 224 cursors, 472 sample script, 193-196 v_err_msg variable, 224 creating, 472 scope, 191-192 v_exists variable, 175 handling, 473 user-defined records v_factorial variable, 138 rules for use, 479 creating, 353, 659-665 v_final_grade variable, 92 sample cursor variable NOT NULL constraint, v_instructor_id variable, in package, 473-475 354-355 294-295 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. 730 variables sample script, 475-479 W WRITE_ERROR exception, 561 strong cursor writing weak cursor variables, 472 variables, 472 blocks, 37, 614-616 Web pages, generating with weak cursor complex functions, 454 Oracle Web Toolkit, 582-596 variables, 472 Web Toolkit to operating system files, declaring and initializing, 559-562 31-34 client-side HTML image maps, 592-593 definition, 23-24 form procedures, X-Y-Z examples, 27-28 587-588 ZERO_DIVIDE exception, 170 illegal identifiers, 24-26 FRAMESET ZIP column initializing with SELECT procedures, 587 INTO, 40-42 INSTRUCTOR table, 603 generating Web pages, naming conventions, 24 STUDENT table, 602 582-596 package variables, ZIPCODE table, 603 HTF functions, 586 469-470 zip_cur cursor, 371 HTML forms, 588-589 scope, 34 zip_does_not_exist function, HTP procedures, 581-582 substitution variables, 455, 679-680 image procedures, 589 13-17 zip_info_rec record, 362-366 server-side HTML image syntax, 24 zip_rec record, 358-362 maps, 589-592 varrays zipcode_obj_type, 517 table of packages, 581 city_varray, 338-341 ZIPCODE table, 603 WHERE CURRENT clause, 261 collection methods, WHILE loops 336-338 calculating sum of course_varray, 653-657 integers between 1 creating, 334-335 and 10, 128-131 definition, 334 example, 124-125 name_varray, 652-653 flow of logic, 124-125 NULL varrays, 335-336 infinite WHILE loops, runtime errors, 656-659 125-126 sample script, 338-341 premature termination views of, 126-128 USER_DEPENDENCIES, syntax, 124 499-500 try-it-yourself USER_ERRORS, 498 projects, 627 USER_OBJECTS, 496 white space, formatting, 597 vr_student variable, 234 WNDS pragma restriction, 504 vr_zip variable, 234-235 WNPS pragma restriction, 504 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản