intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Teach Yourself PL/SQL in 21 Days- P15

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

Thêm vào BST
Báo xấu
65
lượt xem 6
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Teach Yourself PL/SQL in 21 Days- P15: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Teach Yourself PL/SQL in 21 Days- P15

  1. locking 677 submitting jobs (simple LOBs (large object FILEISOPEN, 406, SUBMIT procedure), datatypes), 402 409-410 513 appending to one another, FILEOPEN, 410 SYSDATE function 417, 425-427 GETLENGTH, 406, default output, 176 assigning locator to, 419 410, 416, 420 table creation (MyTable), BFILE, 402 INSTR, 406, 410-411, 457 accessing external files, 416, 420, 427-428 tables (message queue), 404-405 READ, 406, 411, 416, 594 functions, 406 421 TO_DATE function, 164 limitations, 405 SUBSTR, 406, 412, TO_DATE function errors, see also external files 422, 427-428 164 BLOB, 402 TRIM, 416, 422, TO_DATE function with- assigning locator to, 428-429 ing PL/SQL code, 165 419 WRITE, 416, 422, trigger example, 295 CLOB, 402 425-427 truncating adding data to tables, locators, 416 SYSDATE to nearest 423-424 matching patterns in, 420, quarter, 178 comparing, 417-418 427-428 time from SYSDATE, copying, 418, 424-425 NCLOB, 402 177 erasing, 419, 429 reading into buffers, 421 user_errors view, 196 extracting from, 422, returning length, 420 varrays 427-428 trimming, 422, 428-429 adding data to, 283 functions writing to, 422, 425-427 declaring, 282 APPEND, 416-417, locators, 416 viewing executing jobs, 425-427 returning, 160 519 BFILENAME, 406-407 LOCK TABLE statement, viewing job information, COMPARE, 406-408, 233 525 416-418 syntax, 233 viewing your own jobs, COPY, 416, 418, locking, 443-444, 449 519 424-425 DBMS LOCK package, WHILE loop as part of a EMPTY_BLOB, 416, 446-448 function, 137 419 ALLOCATE_UNIQUE WHILE loop function exe- EMPTY_CLOB, 416, function, 446 cution, 138 419 CONVERT function, WHILE loop that does ERASE, 416, 419, 429 447-448 execute, 135 FILECLOSE, 406, 408 RELEASE function, WHILE loop that never FILECLOSEALL, 406, 448-449 executes, 135 408 REQUEST function, writing blocks, 15 FILEEXISTS, 406, 446-447 LN function, 158 408-409 SLEEP function, 449 loading Java classes, FILEGETNAME, 406, dictionaries, 449 563-565 409 distributed, 449 loadjava command, 563 explicit, 445 internal, 449
  2. 678 locking monitoring locks, 449 listing, 121 LOWER function, 157 rows, 445 nesting, 121 LPAD function, 157 tables, 443-444 guidelines, 147 LTRIM function, 157 exclusive mode, 444 incrementing through, LUB function, 160 row exclusive mode, 123-124 444 listing, 123 row share mode, 444 infinite, 144, 380 share lock mode, 444 jumping into (GOTO), 130 M share row exclusive labels, 142-143 mode, 444 changing execution MAP method, 366 LOG function, 158 order, 143 MAX function, 160 logarithms, returning, 158 listing, 142 messages logic errors, 378-379 syntax, 142 AQ (Advanced Queuing), nonterminating loops, 380 LOOP, 144, 146 587 order of operations, 379 example, 144 delay, 587 logical operators, 76-78 EXIT statements with, expiration, 587 AND, 77 144 repository, 588 nulls, 89 syntax, 143 queuing, 591 syntax, 77 nesting, 148 defining payload types, NOT, 76-77 order of execution, 125 591-592 nulls, 89 REPEAT...UNTIL, placing messages in, syntax, 76 144-146 598-604 OR, 77-78 example, 145 queue tables, creating, expressions using, 78 simulating, 145 592-595 nulls, 89 reversing, 122-123 queues, creating, syntax, 77 listing, 122 595-597 login_denied exception, 208 selecting, 146 retrieving messages logs, maintaining with trig- spacing, 148 from, 604-608 gers, 304-306 style tips, 148-149 starting/stopping, LONG datatype, 46 FOR loop confusing 597-598 syntax, 46 style, 148 methods, 336 LONG RAW datatype, 47 FOR loop suggested accessor, 342 syntax, 47 style, 148 constructor, 341 LOOP loops, 144, 146 terminating, 121, 138 referencing, 347 example, 144 WHILE, 134-135, index-by tables, 271-274 EXIT statements with, 144 137-138, 146 MAP, 366 syntax, 143 examples, 134-135, mutator, 342 loops, 121 137-138 ORDER, 364-366 CURSOR FOR, 254 EXIT statement, 139 publishing, calling Java debugging, 136 EXIT WHEN state- from PL/SQL, 571-573 FOR, 121-122, 146 ment, 140 SELF parameter, 361-362 common errors, 124 in functions, 137-138 listing, 362 EXIT statement, 140 stepping through, 136 specifying, 362 syntax, 134
  3. nulls 679 SSThresh variables, 35 NEXT_DAY function, 159, calling from PL/SQL, mixed case, 397 179-181 567-568 preceding with v_ or syntax, 179 calling from p_, 397 NEXT_ITEM_TYPE func- SQL*Plus, 566-567 NCLOB datatype (National tion, 545 publishing, 565-566 Character Large Object), NLS parameters (jobs), 515 MIN function, 160 402 NLSSORT function, 157 minus sign (-) nested blocks, 15 NLS_CHARSET_ID func- negation operator, 65, 67 nested tables, 274 tion, 160 subtraction operator, 65 declaring, 275 NLS_CHARSET_NAME MLSLABEL datatype entries function, 160 defining output variables, adding to, 275-278 NLS_INITCAP function, 462 removing, 279-281 157 VARIABLE_VALUE pro- extending, 277-278 NLS_LOWER function, 157 cedure, 469 initializing, 276-277 NLS_UPPER function, 157 MOD function, 158 nesting non-query statements, 454, MODE statement, 103 blocks, 54 456-460 IN, 103 listing, 54-55 binding variables, 455 IN OUT, 103 FOR loops, 121 closing cursor, 456 OUT, 103 listing, 122 DBMS_SQL pkg and, 454 monitoring locks, 449 functions, 55-56 executing statements, 456 MONTHS_BETWEEN IF statements, 115-117 opening cursor, 454 function, 159, 182 listing, 116 parsing, 454-455 syntax, 182 syntax, 115 NOT operator, 76-77 MSLABEL datatype vs IF...ELSIF state- nulls, 89 (syntax), 48 ment, 119 syntax, 76 mutating tables (triggers), loops, 148 Notepad, cutting and past- 320-323, 326-328 order of execution, 125 ing blocks from, 18 workaround example, objects, 350 not_logged_on exception, 323-324 dot notation, 356-357 208 mutator methods, 342 visibility, 358 no_data_found exception, mypi function listing, 107 procedures, 55-56 208, 232 networks, performance NULL statement (uses), 110 (SQL limitations), 8 nulls NEW_LINE procedure, 497 AND operator, 89 N NEW_TIME function, 159, checking for, 90 183-184 IF statement, 90 naming syntax, 183 in expressions, 86-89, 91 cursors, 247 NEXTVAL pseudocolumns, listing, 88 functions, 101 241 specifying alternate pipes, 545 NEXT_DATE procedure, values, 91 stored functions, 106 521 three-valued logic, 87 treating as specific val- ues, 91
  4. 680 nulls NOT operator, 89 format masks, 169-170 object types, 337 OR operator, 89 NLS parameters, 170 defining, 337, 339-343 NUMBER datatype, 38 converting to strings, 159 accessor methods, 342 defining output variables, cosines, returning, 158 constructor methods, 461 determining negative/ 341 syntax, 39-40 positive/zero, 158 mutator methods, 342 VARIABLE_VALUE dividing (returning syntax, 340-341 procedure, 468 remainders), 158 methods, 368 NUMBER datatype: exponentiation, 158 see also classes subtypes, 40-41 factorials, 150 object views, 337 number functions, 158 FACTORIAL function, object-oriented program- ABS, 158 150 ming, see OOP ACOS, 158 hyperbolic cosines, return- objects, 336 ASIN, 158 ing, 158 comparing, 363-366 ATAN, 158 hyperbolic sines, return- MAP method, 366 ATAN2, 158 ing, 158 ORDER method, CEIL, 158 hyperbolic tangents, 364-366 COS, 158 returning, 158 deleting from object COSH, 158 logarithms, 158 tables, 356 EXP, 158 raising to powers, 158 instantiating, 336, 343-345 FLOOR, 158 rounding, 158 methods (SELF parame- LN, 158 sines, returning, 158 ter), 361-362 LOG, 158 square roots, 158 nesting, 350 MOD, 158 tangents, returning, 158 dot notation, 356-357 POWER, 158 truncating, 158 visibility, 358 ROUND, 158 NVL function, 91, 160 Oracle limitations, 367 SIGN, 158 overloading functions, 363 SIN, 158 package (private versus SINH, 158 public), 205 SQRT, 158 O referencing, 358-359, 361 TAN, 158 DREF operator, 359, TANH, 158 object identifiers, 349, 358 361 TRUNC, 158 Object Navigator object identifiers, 358 numbers (Procedure Builder), 24 REF operator, 359, 361 absolute values, returning, object tables, 337, 349, retrieving 158 351-356 from columns, 347-349 arc cosines, returning, 158 creating, 350 from object tables, arc sines, returning, 158 deleting objects from, 352-353 arc tangents, returning, 356 storing 158 inserting objects, as columns, 345-347 converting to strings, 351-352 object tables, 349, 174-175 retrieving/updating 351-356 converting strings to, 159, data, 352-356 updating 168-173 versus column storage, from columns, 347-349 368 from object tables, 352-356
  5. overloading 681 OOP (object-oriented pro- less than/equal to (
  6. 682 overloading DBMS_LOB TEXT IO, 25 procedures, 195 external LOB func- UTL_FILE, 491, 496-497 parameter definitions, tions, 406 clearing buffer, 500 195 internal LOB func- SELF, 361-362 tions, 416 listing, 362 DBMS_OUTPUT, 491 specifying, 362 debugging with, 383, P triggers, 298-299, 385 312-313, 318-319 exceptions raised by, PACK_MESSAGE proce- condition, 299, 313, 491 dure, 545-547 318 DBMS_PIPE, 543-546 PACK_MESSAGE_RAW correlation names, functions, 545, 555 procedure, 553-554 299-300, 318 procedures, 545, PACK_MESSAGE_ROWID declarations, 299, 313, 553-555 procedure, 554 318 DBMS_SQL parameters instead-of, 318 anon blocks, 467, 469 assigning values, 105 pl/sql code, 299, 313, error handling func- CREATE PROCEDURE 318 tions, 471 command, 191 table name, 299, 318 non-query statements, cursors verb list, 298, 318 454, 456-460 declaring, 248 parentheses ( ), operator queries, 460-461, initializing, 248 precedence, 85 463-467 passing to, 247 PARSE procedure, 454 DEBUG, 385-387 scope, 256 parsing statements, 454-455 creating components, defining, 102-103 queries, 461 386-387 referencing tables, 103 payload, AQ (Advanced DEBUG.ERASE pro- syntax, 103 Queuing), 587 cedure, 388 dropping queue tables, payload types, defining DEBUG.OUT proce- 609 (message queuing), dure, 387 enqueuing messages, 591-592 declaring, 240-241 602-604 percent sign (%) (wildcard dependency, 206 event triggers, defining, character), 70 guidelines, 200 312 performance, network (SQL invoking, 203 granting revoking queue limitations), 8 objects, private versus access, 609 pipes public, 205 job definitions, 516 clearing message buffer, packages, 199, 201-206 message queues, creating, 554 procedures, 197 596-597 creating, 546, 550-551 recompiling, 204-205 modes, 103 moving messages to specification, 199-200 IN, 103 buffer, 548 variable declarations, IN OUT, 103 multiple sessions, 544 240 OUT, 103 naming, 545 state, 205 NLS (jobs), 515 operation of, 544 subprograms, 199 Passing to functions, 111 private, 544 calling, 203-204 public, 543
  7. procedures 683 RAW data calling methods not_logged_on, 208 PACK_MESSAGE_ (SSThresh), 567-568 no_data_found, 208 RAW procedure, output (dbms output.put others, 208 553-554 line() procedure), 17 program_error, 208 UNPACK_ processing benefits, 9 storage_error, 208 MESSAGE_RAW platform independence timeout_on_resource, 208 procedure, 554 (PL/SQL benefits), 9 too_many_rows, 208 readers, 543 Platinum Technology Inc. transaction_backed_out, reading data from, Web site, 11 208 551-553 PLS_INTEGER datatype, value_error, 208 removing, 549-550, 553 43 zero_divide, 208 removing all data from, syntax, 44 private objects (packages), 555 plus sign (+) 205 retrieving message from addition operator, 65 private pipes, 544 buffer, 548-549 identity operator, 65, 67 creating, 546, 550-551 rowids pointers (cursors), 246 privileges, database (exer- decoding, 554 pointers, see locators cise completion require- sending, 554 polling (alerts), setting time ments), 12 sending data to, 547 interval, 536 Procedural Language/ size, 545 polymorphism, 335-336 Structured Query versus alerts, 555-557 portability (PL/SQL bene- Language, see PL/SQL writers, 543 fits), 9 procedural languages, 10 PL/SQL, 10 POWER function, 158 procedure blocks, 51 block structure, 13 powers, raising numbers to, syntax, 52-53 compiling and execut- 158 Procedure Builder (Oracle ing blocks, 15-17 precedence, operators, Developer 2000), 24-28 syntax, 14-15 84-85 database connections, blocks, alternatives to parentheses, 85 27-28 retyping, 18-20 predefined exceptions PL/SQL interpreter, 25-26 calling from Java (SQLJ), invalid_cursor, 232 SS THRESH function, 578-579 no_data_found, 232 creating, 26 calling procedures and too_many_rows, 232 starting, 24 functions, 580, 582 value_error, 232 procedures, 190, 192-195, executing blocks, when_others, 232 197, 199 578-579 predefined Oracle errors, AQ (Advanced Queueing), calling Java from, 569 207-209 608 datatype compatibility, cursor_already_open, 208 dropping queue tables, 574-575 dup_val_on_index, 208 609 Employee class, invalid_cursor, 208 dropping queues, 608 569-570 invalid_number, 208 queue access, methods, publishing, login_denied, 208 granting/revoking, 571-573 609-610 OUT and IN OUT BROKEN, 522 arguments, 575-578
  8. 684 procedures CHANGE, 520 PUT_LINE, 498 versus functions, 190 CLOSE_CURSOR, 456 exceptions raised by, WAITANY, 535 COLUMN_VALUE, 463 498 WAITONE, 534 creating, 190, 192 re-creating, 194 WHAT, 521 debugging information, listing, 194 processing (PL/SQL bene- 193 recursive, 197, 199 fits), 9 declarative part, 190 terminating conditions, producers, 586 defining, 52-53 198 AQ (Advanced Queuing), listing, 53 REGISTER, 534 587 dependencies, discovering, REMOVE, 535 program code, see listings 192 REMOVEALL, 536 program_error exception, exception-handling part, replacing, 194 208 190 RESET_BUFFER, 545, propagate exceptions, 214 executable part, 190 554 propagation, AQ (Advanced FCLOSE, exceptions RETURN statement, 192 Queuing), 587-588 raised by, 494 RUN, 514-515 pseudocolumns, 241 FCLOSEALL, exceptions SET_DEFAULTS, 536 CURRVAL and raised by, 494 SIGNAL, 533 NEXTVAL, 241 FCLOSE_ALL, 494 stored, 190 ROWID, 242 FFLUSH, 500 creating, 191 ROWNUM, 242 exceptions raised by, dropping, 197 public objects (packages), 498 invoking, 194, 199 205 INTERVAL, 521 listing information, 195 public pipes, 543 nesting, 55-56 recompiling, 193-194 creating, 550-551 NEW_LINE, 497 SUBMIT, 512, 514 publishing NEXT_DATE, 521 parameters, 512 methods, calling Java from overloading, 197 swapn (listing), 57-58 PL/SQL, 571-573 PACK_MESSAGE, UNPACK_MESSAGE, SSThresh method, creat- 545-547 545, 548-549 ing Java procedures, PACK_MESSAGE_RAW, UNPACK_MESSAGE_ 565-566 553-554 RAW, 554 PURGE procedure, 545, 555 PACK_MESSAGE_ UNPACK_MESSAGE_ PUT command, 193 ROWID, 554 ROWID, 554 PUT procedure, 497 parameters (definitions), uses, 190 exceptions raised by, 498 195 VARIABLE_VALUE PUTF procedure, 498 PARSE, 454 CHAR datatype, 468 exceptions raised by, 498 PURGE, 545, 555 DATE datatype, 468 PUT_LINE command, 193 PUT, 497 MLSLABEL datatype, listing, 193 exceptions raised by, 469 PUT_LINE procedure, 498 498 NUMBER datatype, exceptions raised by, 498 PUTF, 498 468 exceptions raised by, RAW datatype, 468 498 ROWID datatype, 469 VARCHAR2 datatype, 468
  9. REF operator 685 Q expiration, 587 read-only transactions, 438 payload, 587 read-write transactions, 438 producers, 587 readers (pipes), 543 queries, 460-461, 463-467 propagation, 587-588 reading from DBMS OUT- binding variables, 461 sample scenario, 590 PUT package (syntax), closing cursor, 466 server configuration, 487-490 counting rows returned, 588-589 RECEIVE_MESSAGE 160 transaction support, function, 545, 548 defining output variables, 588 recompiling 461-463 transparency, 588 packages, 204-205 CHAR datatype, 462 message queuing, 591 stored procedures, DATE datatype, 462 defining payload types, 193-194 RAW datatype, 462 591-592 record variables ROWID datatype, 462 queue tables, creating, %rowtype attribute, 236 executing (SQL*Plus), 10 592-595 cursor-based, 239 executing statements, 463 queues, creating, declaring, 234-235 fetching rows, 463 595-597 table-based, 236 parsing statements, 461 placing messages in records reading results into vari- queues, 598, 602 attributes (%TYPE), ables, 463-465 parameters, 602-604 235-236 SELECT statement exam- record types, 599-602 dequeuing messages, 604 ple, 466-467 retrieving messages from DEQUEUE_OPTIONS Quest Software Web site, 11 queues, 604, 606 _T, 605-606 queuing dequeuing change mes- enqueing messages, AQ (Advanced Queuing), sages, 606-608 599-602 585-586, 608 record types, 604-606 AQ$_AGENT, 601 agents, 587 starting/stopping queues, AQ$_RECIPIENT_ consumers, 587 597-598 LIST_T, 602 control data, 587 ENQUEUE_OPTIONS deferred messaging0, _T, 600 587 referencing fields (dot delay, 587 dequeue, 587 R notation), 237 recursive functions, 149 disconnected messag- raising exceptions, 214 FACTORIAL, 150 ing, 587 RAW datatype, 46 testing, 151 dropping queue tables, defining output variables, uses, 152 609 462 recursive procedures, 197, dropping queues, 608 syntax, 47 199 enqueue, 587 VARIABLE_VALUE pro- terminating conditions, granting/revoking cedure, 468 198 access, 609-610 RAWTOHEX function, 159 REF cursor type (declaring message repository, READ function (LOBs), in packages), 260 588 416, 421 REF operator, 359, 361 messages, 587 BFILE, 406, 411 listing, 359
  10. 686 referencing referencing return data types, 105 inserting, 226-229 objects, 358-359, 361 RETURN statement, 101, employee table exam- DREF operator, 359, 192 ple, 227, 229 361 return values, function, dis- locking, 445 object identifiers, 358 playing, 23 RPAD function, 157 REF operator, 359, 361 retyping blocks (alterna- RTRIM function, 157 values (index-by tives), 18-20 RUN procedure, 514-515 tables), 268-269 REVERSE keyword (loops), REGISTER procedure, 534 122-123 relational operators (sample revoking access queues expressions), 69 (AQ), 609-610 S RELEASE function (DBMS ROLLBACK command, 441 LOCK package), 448-449 ROLLBACK statements, SAVEPOINT statements, remainders (division), 234 234 returning, 158 rolling back transactions, savepoints, 441-442 REMOVE procedure, 535 438, 441 saving address objects in REMOVEALL procedure, ROLLBACK command, tables, 346 536 441 scalar variables, 34 REMOVE_PIPE function, savepoints, 441-442 scientific notation, 174 545, 549-550 ROUND function, 158-159, scope, 58 removing 184-185 cursors, 262 classes (SSConstants), format masks, 177 exceptions, 214 568-569 syntax, 184-185 listing, 58-59 data (varrays), 283-284 rounding numbers, 158 SELECT FUNCTION com- jobs, 520 ROW ID, converting to mand, 161 pipes, 549-550, 553 string, 159 SELECT statement, 223, REPEAT...UNTIL loops, row-level triggers, 296 229-230 144-146 ROWID datatype examples, 229-230 example, 145 defining output variables, exceptions, 232 simulating, 145 462 syntax, 229, 233 REPLACE function, 157 syntax, 47-48 SELF parameter, 361-362 Repositories, message (AQ), VARIABLE_VALUE pro- listing, 362 588 cedure, 469 specifying, 362 REQUEST function (DBMS ROWID pseudocolumns, SEND_MESSAGE function, LOCK package), 446-447 242 545, 547 requirements (completing ROWIDTOCHAR function, Server Manager, 11 exercises), 12 159 servers RESET_BUFFER proce- ROWNUM pseudocolumns, configuring for AQ dure, 545, 554 242 (Advanced Queuing), retrieving objects rows 588-589 from columns, 347-349 counting number returned, PL/SQL processing bene- from object tables, 160 fits, 9 352-353 fetching into buffer, 463 SET SERVEROUTPUT ON statement, 108
  11. statements 687 SET TRANSACTION com- SQLJ, calling PL/SQL from statement-level triggers, 296 mand, 439 Java, 578-579 statements SET TRANSACTION state- calling procedures and BEGIN, 101 ments, 234 functions, 580, 582 bulk-binding, 284 SET_DEFAULTS proce- executing blocks, 578-579 BULK COLLECT key- dure, 536 SQLPlus Worksheet, 11, 28 word, 285-286 SHUTDOWN trigger (list- executing blocks, 28-29 FORALL keyword, ing), 315 SQRT function, 158 286-288 SIGN function, 158 square roots, 158 COMMIT, 440 SIGNAL procedure, 533 squareme function listing, CREATE FUNCTION, 26 SIN function, 158 110 CREATE TABLE, 224 sines, returning, 158 SS CONSTANTS class, cre- DELETE, 223 hyperbolic, 158 ating Java procedures, syntax, 231 inverse, 158 562-563 DML (LOCK TABLE), SINH function, 158 SS THRESH function 233 slash (/), 65 creating (Procedure END, 101 SLEEP function (DBMS Builder), 26 EXCEPTION, 101, 106 LOCK package), 449 listing, 20 executing, 456 SNP parameters, 511 with an error, 21 for queries, 463 software (exercise comple- SSConstants class, removing EXIT, 138-142 tion requirements), 12 from databases, 568-569 changing loop execution SOUNDEX function, 157 SSThresh function, 563 order, 143 SQL, 9 listing, 100 with FOR loop, 140 limitations, 8 SSThresh method with WHILE loop, 139 SQL*Plus calling from PL/SQL, within functions, 141 @ command, 18-19 567-568 EXIT WHEN, 138-142 calling methods calling from SQL*Plus, syntax, 138 (SSThresh), 566-567 566-567 with WHILE loop, 140 EDIT command, 19-20 publishing, 565-566 FETCH (INTO clause), interactivity, 10 starting 259 entering statements, 10 Procedure Builder (Oracle GOTO, 128-129, 131-132, executing queries, 10 Developer 2000), 24 134 Server Manager, 11 queues, 597-598 disadvantages, 133-134 SQL-Navigator (Quest starting transactions, example, 133 Software), 11 438-439 scoping rules, 129, SQL-Programmer (Sylvain SET TRANSACTION 131, 133 Faust Inc.), 11 command, 439 syntax, 128 SQL-Station (Platinum STARTUP triggers (listing), IF, 112-113 Technology Inc.), 11 315 common errors, 120 SQLCODE function, 160, statement labels, 128 formatting, 119-120 211-212 accessing, 128 jumping into (GOTO), SQLERRM function, 160, format, 128 131 211-212 jumping within (GOTO), 131-133
  12. 688 statements listing, 112 stopping queues, 597-598 raw to hex, 159 nesting, 115-117 storage_error exception, 208 single-byte to multi- syntax, 112 stored functions byte, 159 IF...ELSIF, 117-118 creating, 106-108 to dates, 159, 161-166 listing, 118 syntax, 106 to labels, 159 syntax, 117 naming, 106 to numbers, 159, versus nested IF, 119 squareme (listing), 110 168-173 IF...THEN...ELSE, stored procedures, 190 padding, 157 113-114 dropping, 197 phonetic representations, listing, 114 invoking, 194, 199 157 syntax, 113 listing information, 195 replacing, 157 INSERT, 223, 226-229 recompiling, 193-194 returning as lowercase, employee table exam- storing objects 157 ple, 227, 229 as columns, 345-347 returning length, 157 syntax, 226 retrieving/updating, returning location of MODE, 103 347-349 INSTR function, 156 IN, 103 object tables, 349, INSTRB function, 157 IN OUT, 103 351-356 returning portions of, 157 OUT, 103 creating, 350 sorting characters, 157 NULL, 110 inserting objects, trimming, 157 uses, 110 351-352 strong cursor variables, 258 OPEN...FOR, 258-259 string expressions, 86 Structured Query parsing, 454-455 string operators, 78-79 Language, see SQL queries, 461 concatenation (||), 78-79 SUBMIT procedure, 512, RETURN, 101, 192 listing, 78 514 SELECT, 223, 229-230 LIKE, 78 parameters, 512 examples, 229-230 strings subprograms (packages), exceptions, 232 capitalizing, 157 calling, 199, 203-204 syntax, 229, 233 initial letters, 156-157 SUBSTR function (LOBs), SET SERVEROUTPUT comparing, 79-81 157, 422, 427-428 ON, 108 case sensitivity, 81 BFILE, 406, 412 SQL, entering in character sets, 79 SUBSTRB function, 157 SQL*Plus, 10 datatypes, 79-81 subtypes, 35 executing queries, 10 listing, 81 datatypes terminators (block syntax), concatenating (CONCAT BINARY INTEGER, 14 function), 156 41-43 transaction control, 234 converting CHAR, 38 UPDATE, 223 dates to, 166-168 NUMBER, 40-41 object tables, 355 hex to raw, 159 VARCHAR2, 36 syntax, 231 multibyte to single- SUM function, 160 see also loops byte, 159 support STDDEV function, 160 numbers to, 159, Java, 559-561 stepping through loops, 174-175 calling from PL/SQL, incrementing, 123-124 569-578 listing, 123
  13. TOAD 689 calling PL/SQL program blocks, 14-15 locking, 443-444 (SQLJ), 578-580, 582 nesting, 15 exclusive mode, 444 procedures, creating, RAW datatype, 47 row exclusive mode, 561-569 ROWID datatype, 48 444 swapn function (listing), triggers, 298-300 row share mode, 444 57-58 instead-of, 318 share lock mode, 444 Sylvain Faust Inc. Web site, UROWID datatype, 48 share row exclusive 11 syntax errors, 372, 375-377 mode, 444 syntax correcting (example), message queuing, creating, blocks 375-376 592-595 anonymous, 49-51 SYSDATE function, 159, mutating (triggers), function, 51-52 175-176 320-323, 326-328 trigger, 54 date/time origin, 186 nested, 274 BOOLEAN datatype, 45 default output, 176 adding entries to, CHAR datatype, 36-37 formatting output, 176 275-278 Commands (ALTER declaring, 275 TRIGGER), 311 extending, 277-278 datatypes initializing, 276-277 BINARY INTEGER, T removing entries, 41 279-281 NUMBER, 39-40 tables object, 337 VARCHAR2, 35-36 adding data to (CLOB queue, dropping, 609 DATE datatype, 44-45 data), 423-424 TAN function, 158 DBMS OUTPUT package, adding records to, 458-460 tangents, returning, 158 485 creating, 223-224, 456, hyperbolic, 158 disabling, 485 458 inverse, 158 enabling, 485 CREATE TABLE com- TANH function, 158 reading from buffer, mand, 224 terminating conditions 487-490 DUAL, 161 (recursive procedures), writing to buffer, index-by, 266 198 486-487 declaring, 266-267 terminators, statement functions, declaring, deleting entries, 270- (block syntax), 14 101-102 271 TEXT IO package, 25 LOCK TABLE statement, inserting entries into, three-valued logic, 87 233 267-268 time zones LONG datatype, 46 methods, 271-274 NEW_TIME function, LONG RAW datatype, 47 referencing values, 159, 183-184 message queues 268-269 table of, 183 creating, 596-597 updating entries, 270 timeout_on_resource excep- starting/stopping, inserting rows, 226-229 tion, 208 597-598 employee table exam- TOAD (Tool for Oracle MSLABEL datatype, 48 ple, 227, 229 Application Developers), PLS_INTEGER datatype, 11 44
  14. 690 too_many_rows exception too_many_rows exception, rolling back, 438, 441 DDL statements, 320 208, 232 ROLLBACK com- mutating tables, TO_CHAR function, 94, mand, 441 320-323, 326-328 159 savepoints, 441-442 listing, 308 dates, 166-168 starting, 438-439 parameters, 298-299, syntax, 167 SET TRANSACTION 312-313, 318-319 numbers, 174-175 command, 439 condition, 299, 313, scientific notation, 174 support (AQ), 588 318 syntax, 174 transaction_backed_out correlation names, TO_DATE function, 94, 159, exception, 208 299-300, 318 161-166 TRANSLATE function, 157 declarations, 299, 313, default format masks, 165 Transparency (AQ), 588 318 errors, 164 triggers, 294-297, 299-300, pl/sql code, 299, 313, format masks, 162-163 313, 318-319 318 limitations, 162 after, 296 table name, 299, 318 listing, 164 before, 296 verb list, 298, 318 syntax, 161 blocks, 53 records, predating, 329 within PL/SQL code, 165 syntax for creating, 54 row-level, 296 TO_LABEL function, 159 creating (security exam- SHUTDOWN (listing), TO_MULTI_BYTE func- ple), 538-539 315 tion, 159 data integrity (maintain- single-row inserts, 329 TO_NUMBER function, 94, ing), 300-304 STARTUP (listing), 315 159, 168-173 listing, 302 statement-level, 296 format masks, 169-170 defining, 299-300, 313, syntax, 298-300 errors, 172-173 319 update, 296 listing, 170 delete, 296 viewing code, 309-310 NLS parameters, 170 enabling/disabling, creating command file, syntax, 169 310-311 310 TO_SINGLE_BYTE func- event, 311-312 TRIM function (LOBs), tion, 159 defining, 312-314 416, 422, 429 transaction control state- writing, 314-317 troubleshooting, see debug- ments, 234 example, 295-296 ging transactions, 438 execution order, 297 TRUNC function, 158-159, committing, 438, 440 firing (events), 294 176-178 COMMIT statement, guidelines, 298 format masks, 177 440 historical logs, 304-306 syntax, 176 explicit commits, 440 insert, 296 truncating implicit commits, 440 instead-of, 318 dates, 159, 176-178 two-phase commits, syntax, 318 format masks, 177 441-442 writing, 319 numbers, 158 ending, 439-440 limitations, 320-324, two-phase commits, 441-442 read-only, 438 326-328 read-write, 438
  15. VARIABLE_VALUE procedure 691 U user_objects view, 196 cursors, 257-261 user_object_size view, 196 as arguments, 260-261 user_source view, 196 assigning, 260 UID function, 160 USER_TRIGGERS view, declaring, 257-258 unary operators, 65 309 FETCH statement, 259 undefined Oracle errors, UTL_FILE package, 491, OPEN...FOR state- 209-210 496-497 ment, 258-259 exception_init directive, clearing buffer, 500 return clauses, 258 209 file input, 496 strong, 258 underscore (_) (wildcard opening files, 494 weak, 258 character), 70 testing for open files, 497 declaring, 49-50 UNIQUE_SESSION_NAME writing to files listing, 50-51 function, 545, 555 PUT procedure, 497 naming, 35 UNPACK_MESSAGE pro- PUTF procedure, 498 mixed case, 397 cedure, 545, 548-549 preceding with v_ or UNPACK_MESSAGE_RAW p_, 397 procedure, 554 output, defining, 461-463 UNPACK_MESSAGE_ ROWID procedure, 554 V package specifications, declaring, 240 UPDATE statement, 223 VALUE operator, 354 reading query results into, object tables, 355 Values, index-by tables, ref- 463-465 syntax, 231 erencing, 268-269 record update triggers, 296 value_error exception, 208, %ROWTYPE attribute, data integrity example, 232 236 301 VARCHAR2 datatype, 35 cursor-based, 239 updating entries (index-by defining output variables, declaring, 234-235 tables), 270 462 table-based, 236 updating objects subtypes, 36 retrieving values (anon from columns, 347-349 syntax, 35-36 blocks), 468-469 from object tables, VARIABLE_VALUE pro- scalar, 34 352-356 cedure, 468 scope, 58 UPPER function, 157 vs. CHAR (listing), 38 listing, 58-59 UROWID datatype (syntax), variable declarations (block VARIABLE_VALUE proce- 48 syntax), 14 dure USER function, 160 keywords, 14 CHAR datatype, 468 user-defined errors, 210-211 variable-sized arrays, 281 DATE datatype, 468 USERENV function, 160 adding\removing data, MLSLABEL datatype, users (returning names of), 283-284 469 160 declaring, 282-283 NUMBER datatype, 468 user_errors view (listing), variables RAW datatype, 468 196 binding, 455 ROWID datatype, 469 USER_JOBS view, 517 queries, 461 VARCHAR2 datatype, columns, 518 BLOB, initializing, 160 468 CLOB, initializing, 160
  16. 692 VARIANCE function VARIANCE function, 160 WHILE loops varrays, 281 EXIT statement, 139 adding\removing data, in functions, 137-138 283-284 stepping through, 136 declaring, 282-283 wildcard characters viewing percent sign (%), 70 jobs, 517-519 underscore (_), 70 DBA_JOBS view, 517 WRITE function (LOBs), DBA_JOBS_ 422, 425-427 RUNNING view, 517 writers (pipes), 543 USER_JOBS view, 517 writing trigger code, 309-310 blocks, 15-17 views event triggers, 314-316 object, 337 DDL events, 316-317 USER_TRIGGERS, 309 functions, 20-21 views (data dictionary), displaying return val- stored procedures, 195 ues, 23 VSIZE function, 160 finding compilation errors, 21-22 to DBMS OUTPUT pack- age, 486 W-Z syntax, 486-487 triggers, instead-of, 319 WAITANY procedure, 535 WAITONE procedure, 534 zero_divide exception, 208 weak cursor variables, 258 Web sites Oracle, 12 OTN (Oracle Technology Network), 12 Platinum Technology Inc., 11 Quest Software, 11 Sylvain Faust Inc., 11 WHAT procedure, 521 when_others exception, 232 WHILE loop, 134-135, 137-138, 146 examples, 134-135, 137-138 EXIT WHEN statement, 140 syntax, 134
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD


ERROR:connection to 10.20.1.98:9315 failed (errno=111, msg=Connection refused)
ERROR:connection to 10.20.1.98:9315 failed (errno=111, msg=Connection refused)
THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2