Teach Yourself PL/SQL in 21 Days- P14

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

Thêm vào BST

Báo xấu

86
lượt xem 12
download

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

Teach Yourself PL/SQL in 21 Days- P14: 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- P14

Answers 627 2. What function do I use to combine two strings together? You use the CONCAT function; however, you can still rely on || to concatenate A strings. 3. What function converts ‘11/28/99’ to an Oracle DATE? The TO_DATE function gives you this flexibility. 4. You can use the TRUNC and ROUND functions with what data types? Both NUMBER and DATE include the ROUND and TRUNC functions. Exercises 1. Create a PL/SQL block that reads in the month of a date and displays the month in a Roman numeral format. Use a date of 06/11/67. This allows you to practice the TO_CHAR function. When printing the Roman numeral equivalent, use LTRIM to remove any spaces padded to the left of the Roman numeral. If you are really ambitious, on your own you can create the same RM-type function by using IF...THEN...ELSE statements for practice from Day 4. Remember, practice helps to solidify your knowledge through repetition and understanding. Here is one solution: DECLARE v_Hold_Month Number; BEGIN v_Hold_Month := TO_NUMBER(TO_CHAR(TO_DATE(‘11-JUN-67’),’MM’)); DBMS_OUTPUT.PUT_LINE(v_Hold_Month); DBMS_OUTPUT.PUT_LINE(‘Converted to Roman Numeral ‘ || LTRIM(TO_CHAR(v_Hold_Month,’RM’),’ ‘)); END; / Your output is 6 Converted to Roman Numeral VI 2. Use the TRUNC function on the SYSDATE to round to the nearest century. The answer is SELECT TO_CHAR(TRUNC(SYSDATE,’CC’),’MM/DD/YYYY HH:MI:SS AM’) “Today’s Date and Time” from DUAL The output is similar to Today’s Date and Time ------------------------- 01/01/1900 12:00:00 AM
628 Appendix 3. Use CONCAT to link two strings together. Repeat the same line by using || instead of CONCAT. Here is one solution: DECLARE v_String1 VARCHAR2(60) := CONCAT(‘Connect String1 to’, ‘ String2’); v_String2 VARCHAR2(60) := ‘Connect String1 to’ || ‘ String2’; BEGIN DBMS_OUTPUT.PUT_LINE(v_String1); DBMS_OUTPUT.PUT_LINE(v_String2); END; / Your output looks similar to Connect String1 to String2 Connect String1 to String2 4. Calculate the number of days between 01/01/97 to 03/31/97. Remember to use the TRUNC function to eliminate the TIME dependency. The answer is SELECT TRUNC(TO_DATE(‘03/31/97’,’MM/DD/YY’)) - TRUNC(TO_DATE(‘01/01/97’,’MM/DD/YY’)) “Days_Subtracted” from DUAL; Your output is Days_Subtracted --------------- 89 5. Convert the CHARACTER string ‘06/11/67’ to a date, and subtract from 06/11/97 to see how old your author is (and holding). The answer is SELECT (TO_DATE(‘06/11/97’,’MM/DD/YY’) - TO_DATE(‘06/11/67’,’MM/DD/YY’))/365 “Years Old” from DUAL; Your output is Years Old --------- 30.021918 6. Calculate how many months are between 05/15/97 and 08/22/97. The answer is
Answers 629 SELECT MONTHS_BETWEEN(‘22-AUG-97’,’15-MAY-97’) “Fractional” from DUAL; A Your output is Fractional ---------- 3.2258065 7. Round the SYSDATE to the nearest century. The answer is SELECT TO_CHAR(ROUND(SYSDATE,’CC’),’MM/DD/YYYY HH:MI:SS AM’) “Today’s Date and Time” from DUAL; Your output is similar to Today’s Date and Time ------------------------- 01/01/2000 12:00:00 AM 8. Calculate the time in Newfoundland from Central Standard Time from 02-22-97, 05:00 AM. Here is one solution: SELECT TO_CHAR(NEW_TIME(TO_DATE(‘02-22-97 05:00:00 AM’, ‘MM-DD-YY HH:MI:SS AM’), ‘CST’,’NST’), ‘DD-MON-YY HH:MI:SS AM’) “Central to Newfoundland” from DUAL; Your output is Central to Newfoundland ------------------------ 22-FEB-97 07:30:00 AM 9. From Listing 6.22, subtract one month and explain the answer. Two possible answers are SELECT ADD_MONTHS(TO_DATE(‘31-MAR-97’),-1) from DUAL; SELECT ADD_MONTHS(TO_DATE(‘31-MAR-97’),-1.5) from DUAL; The output, of course, is the end of February because February has fewer than 30 days: ADD_MONTH --------- 28-FEB-97
630 Appendix 10. Calculate the number of days until Christmas from the last day of the month of today’s date. (We don’t get paid until the end of the month!) Here is one solution: SELECT LAST_DAY(SYSDATE) “Last_Day”, TO_DATE(‘25-DEC-97’) - LAST_DAY(SYSDATE) “Shopping Days” from DUAL; The output is similar to Last_Day Shopping Days --------- ------------- 30-JUN-97 177.67266 Day 7, “Procedures, Packages, Errors, and Exceptions” Quiz 1. What statement do you use to recompile a procedure? You use the CREATE OR REPLACE PROCEDURE command to recompile a procedure. 2. How do you invoke a procedure? You use the execute command if you want to explicitly and manually call a proce- dure. From within a package or another PL/SQL construct, you simply list the pro- cedure name in the code, and the call to it is made automatically. 3. Name at least four predefined Oracle exception errors. There are many Oracle predefined exceptions, including no_data_found, too_many_rows, invalid_cursor, value_error, invalid_number, zero_divide, cursor_already_open, and login_denied. 4. How do you call a module of a package? To call a specific procedure within a package, you use dot notation, as shown in the following example: package_name.procedure_name Exercises 1. Write a package specification for the functions written in previous lessons. Additionally, include in the specification one or two of the procedures used in this lesson.
Answers 631 Package specifications contain public declarations of the name of the package and its functions and procedures. The following is an example and might differ slightly from your answer: A CREATE PACKAGE day_8_package_spec as -- ➥package name declaration FUNCTION inv_count (qty number, part_nbr varchar2(25)) -- function declaration return number; PROCEDURE pay_salary (emp_id number); -- ➥procedure declaration PROCEDURE hire_employee (emp_name, pay_date number, pay_type char)); -- ➥procedure declaration END day_8_package_spec; 2. Write an exception-handling piece of code to trap the error of receiving more rows than you expected as well as an unknown error. One possible way to write this exception handler is exception WHEN too_many_rows THEN ... -- code to be executed when a SELECT returns -- too many rows END; WHEN others THEN ... -- code to be executed when an exception is -- encountered which is not the too_many_rows Day 8, “Using SQL to Manipulate Data and Control Transactions” Quiz 1. Name some of the database objects that you can base a variable declaration on. PL/SQL variables can be based on database table columns, other variables, con- stants, and cursors. 2. Name at least two of the exception types discussed in this chapter. There are many exceptions that a programmer can prepare for while coding. Some of the most common are no_data_found, too_many_rows, invalid_cursor, and when_others.
632 Appendix 3. Do you need to list the table column names while inserting data into that table? No. If you elect to omit the column names during an insert statement, Oracle will automatically align the input data with the columns of the data. The first piece of data is inserted into the first column, the second piece of data will be inserted into the second column, and so on. 4. What are the four SQL DML statements permitted in a PL/SQL block? The four DML statements that are supported within a PL/SQL block are INSERT, DELETE, UPDATE, and SELECT. Exercises Evaluate each of the following three declarations and determine which ones are legal or not legal. Explain your answer for those that are not legal. 1. Legal or not legal: DECLARE emp_rec emp_rec_type; This is an invalid declaration because emp_rec_type must be declared prior to this declaration. A proper declaration would be DECLARE TYPE emp_rec_type IS record (id INTEGER, name VARCHAR2(35)); emp_rec emp_rec_type; 2. Legal or not legal: DECLARE emp_last_name %type; This is an invalid declaration. The proper declaration would have to include a table and column reference such as emp_last_name emp.l_name%type; 3. Legal or not legal: DECLARE TYPE emp_table_type is table of VARCHAR2(55); emp_dept_table emp_table_type; This declaration is incorrect because the INDEX BY clause is missing. This declara- tion should look like DECLARE TYPE emp_table_type is table of VARCHAR2(55) INDEX BY BINARY_INTEGER; emp_dept_table emp_table_type;
Answers 633 Day 9, “Manipulating Data with Cursors” A Quiz 1. What are the cursor attributes and what is their purpose? The implicit and explicit cursors each have four attributes, which provide useful information about the cursor. These attributes are %isopen, %found, %notfound, and %rowcount. 2. How many cursors can you use at a time? There are no predefined limits on the number of cursors a session can have. The only constraint that limits the number of cursors is the availability of memory to manage them. 3. Where is the cursor pointer when the cursor is first opened? The cursor pointer is pointing to immediately prior to the first row when the cursor is first opened. 4. Name the different cursor variable parameter modes and their purpose. The cursor variable argument can have one of three different modes: IN—The program can have read-only abilities with the parameter. In other words, the cursor argument is passed only to the procedure or function. OUT—The program can return values to the calling PL/SQL block. IN OUT—The program can read and write to the variable. Exercise Create a PL/SQL block that determines the top five highest paid employees from your Employee table. Be sure to incorporate the usage of the appropriate cursor attributes. Print these five employees to the screen. This exercise can be solved in several different ways. Your solution can include exception handling as well as other methods of processing the data. I have chosen the following method as my solution: DECLARE c_emp_name VARCHAR2(32); c_sal NUMBER(9,2); CURSOR emp_cursor is -- cursor declaration SELECT emp_name, pay_type from employee ORDER BY pay_rate desc; -- key to getting top 5 highest paid employees
634 Appendix BEGIN OPEN emp_cursor; FETCH emp_cursor INTO c_emp_name, c_sal; --fetch into variables for later use WHILE emp_cursor%rowcount
Answers 635 Exercise Write the code necessary to generate a PL/SQL nested table with 10 new depart- A ment IDs and names. Use department ID numbers that are not currently being used in the database. Make up the department names. Next, write a FORALL statement that inserts all the records by using a bulk bind. Here is one possible solution to the exercise: 1: DECLARE INPUT 2: --Define a nested table type for department IDs and names. 3: TYPE dept_id IS TABLE OF department.dept_id%TYPE; 4: TYPE dept_name IS TABLE OF department.dept_name%TYPE; 5: 6: --Declare a nested table variable for each type. 7: dept_ids dept_id; 8: dept_names dept_name; 9: inx1 PLS_INTEGER; 10: BEGIN 11: --Initialize the collections 12: dept_ids := dept_id(); 13: dept_names := dept_name(); 14: 15: --Extend once, outside the loop for better performance. 16: dept_ids.extend(10); 17: dept_names.extend(10); 18: 19: --Generate 10 new departments, numbered from 20: --1101-1110. 21: FOR inx1 IN 1..10 LOOP 22: dept_ids(inx1) := inx1 + 1100; 23: dept_names(inx1) := ‘Dept #’ || TO_CHAR(inx1+1100); 24: END LOOP; 25: 26: FORALL x IN dept_ids.first..dept_ids.last 27: INSERT INTO department (dept_id, dept_name) 28: VALUES (dept_ids(x), dept_names(x)); 29: END; 30: / The nested tables types are declared in lines 3–4. The corresponding variables are ANALYSIS declared in lines 7–8. The tables are initialized by calling their constructor meth- ods in lines 12–13. Because we know that we are going to deal with only 10 elements, only one call to extend is made for each table. These calls occur in lines 16–17, and they extend each table by 10 entries. The loop in lines 21–24 generates 10 new departments, numbered from 1101 through 1110. The FORALL statement in lines 26–28 inserts these 10 rows into the department table.
636 Appendix Day 11, “Writing Database Triggers” Quiz 1. Which data manipulation statements can support triggers? INSERT, UPDATE, and DELETE. 2. What are the four basic parts of a trigger? The event that fires the trigger, the database table on which the trigger is defined, the optional WHEN clause, and the PL/SQL block containing the code to be executed. 3. In a trigger, what are the correlation names :OLD and :NEW used for? :OLD is used to refer to the values in a row before it is changed. :NEW is used to refer to the values after the row is changed. 4. What is the name of the system view that can be used to retrieve trigger defini- tions? The USER_TRIGGERS view shows all triggers you own. In addition, you might want to look at the ALL_TRIGGERS view and the DBA_TRIGGERS view. The ALL_TRIGGERS view adds triggers that others own but which are defined on your tables. If you have database administrator privileges, the DBA_TRIGGERS view lists all triggers defined in the database. 5. What is a mutating table? A mutating table is one that is in the process of being modified by the SQL state- ment which fired a trigger. Because the table is being changed it is not in a consis- tent state and Oracle does not allow queries against it. 6. Name some possible uses for triggers. Some possible uses for triggers are enforcing a business rule, enforcing security, logging changes, replicating data, and calculating column values. Exercises 1. Write a set of triggers to maintain the emp_name and dept_name fields redundantly in the emp_dept relation so that you do not have to join the employee and depart- ment tables just to get a simple department listing.
Answers 637 Here is one solution: CREATE OR REPLACE TRIGGER emp_dept_names INPUT/ OUTPUT BEFORE INSERT OR UPDATE OF emp_id, dept_id ON emp_dept A FOR EACH ROW DECLARE redundant_dept_name department.dept_name%TYPE; redundant_emp_name employee.emp_name%TYPE; BEGIN --Get the employee’s name BEGIN SELECT emp_name INTO redundant_emp_name FROM employee WHERE employee.emp_id = :NEW.emp_id; EXCEPTION --the employee record may not exist. WHEN OTHERS THEN redundant_emp_name := ‘’; END; --Get the department name BEGIN SELECT dept_name INTO redundant_dept_name FROM department WHERE department.dept_id = :NEW.dept_id; EXCEPTION --the department record may not exist. WHEN OTHERS THEN redundant_dept_name := ‘’; END; --Store the employee and department names in the emp_dept record. :NEW.dept_name := redundant_dept_name; :NEW.emp_name := redundant_emp_name; END; / Trigger created. CREATE OR REPLACE TRIGGER department_emp_dept AFTER UPDATE OF dept_name ON department FOR EACH ROW BEGIN UPDATE emp_dept SET emp_dept.dept_name = :NEW.dept_name WHERE emp_dept.dept_id = :NEW.dept_id; END;
638 Appendix / Trigger created. CREATE OR REPLACE TRIGGER employee_emp_dept AFTER UPDATE OF emp_name ON employee FOR EACH ROW BEGIN UPDATE emp_dept SET emp_dept.emp_name = :NEW.emp_name WHERE emp_dept.emp_id = :NEW.emp_id; END; / Trigger created. The first trigger, emp_dept_name, handles inserts and updates on the emp_dept ANALYSIS table itself. Whenever a new record is inserted or an existing record updated, the current employee and department names are retrieved from their respective tables and stored with the emp_dept record. The second trigger, department_emp_dept, ensures that any changes to a department’s name are propagated to all the related records in the emp_dept table. The third trigger does the same thing for changes to employee names. Writing these triggers almost leads to a mutation problem. Recall the emp_dept_upd trig- ger shown in Listing 11.3. It is defined to fire only when the dept_id field is updated. In other words, it is defined as AFTER UPDATE OF dept_id ON emp_dept. Removing the words OF dept_id would cause it to fire whenever an emp_dept record was changed. In that case, a change to a department name would fire department_emp_dept, which would issue an update against the emp_dept table. That would in turn fire the emp_dept_upd trigger, which would issue an update against the department table, which in turn would mutate because the SQL statement that started all this was an update against that table. 2. Write the SQL statements necessary to populate the emp_name and dept_name fields for any existing emp_dept records. This could be done as either one or two updates. Here is a solution done with one UPDATE statement: UPDATE emp_dept ed SET emp_name = (SELECT emp_name FROM employee e WHERE e.emp_id = ed.emp_id), dept_name = (SELECT dept_name FROM department d WHERE d.dept_id = ed.dept_id);
Answers 639 Day 12, “Using Oracle8i Objects for Object- A Oriented Programming” Quiz 1. What is the difference between a class and an object? A class, or an object type, as it is called by Oracle, serves as the blueprint for one or more objects. It is just a design, and you might compare it to a table definition. An object, on the other hand, represents an instance of a class. You can create many objects of a given type, just as you can create many records in a table. 2. What are the allowed return values for an ORDER function? The allowed return values for an ORDER function are 0, 1, and -1. A 0 value means that the two objects being compared are equal. A value of -1 means that the object whose method was called is less than the other object. A value of 1 means that the object whose method was called is greater than the other object. 3. An object table has one column for each attribute of an object, plus one additional column. What is this additional column used for? The extra column in an object table is used to store the object identifier, which uniquely identifies that object in the database. It is an Oracle-generated value, and is automatically assigned to each object when it is first stored in the table. 4. How is an object reference different from an object? An object reference functions similarly to a pointer in a language such as C. It is used to store a reference from one object to another. It is only a pointer, and in order to access the referenced object, you must use that pointer in a query to retrieve the specified object. 5. How many attributes must an object have? How many methods? An object must have at least one attribute. It does not, however, have to have any methods. 6. What datatypes are allowed for the return value of a MAP function? A MAP function can return values only of type NUMBER, VARCHAR2, or DATE. 7. What are accessor and mutator methods? Accessor methods are member functions that exist primarily to enable you to retrieve specific attribute values from an object. Mutator methods are member pro- cedures that enable you to set the value of a specific attribute or set of attributes. Using accessor and mutator methods helps insulate your code from changes to an object’s underlying implementation.
640 Appendix Exercises 1. Write a stored function that creates and returns an object of type building. This function should accept as parameters the building’s name, its address, and the man- ager’s employee number. Have the function check the database before creating the new building object, to be sure that another building with the same name does not already exist. If another building with the same name does exist, the function should return null. Here is one solution: 1: CREATE OR REPLACE FUNCTION CreateBuilding ( INPUT 2: --This is an example of how you can work around the 3: --fact that you can’t write your own “constructor” for 4: --the building object. This stored function serves 5: --as a psuedo-constructor. Note however, that Oracle can’t 6: --force you to call this. 7: inBldgName VARCHAR2, 8: inBldgStreet VARCHAR2, 9: inBldgCity VARCHAR2, 10: inBldgStateAbbr VARCHAR2, 11: inBldgZip VARCHAR2, 12: inBldgMgr employee.emp_id%TYPE 13: ) RETURN building AS 14: TheNewBldg building; 15: NoFlag integer; 16: BEGIN 17: --Check to see if this building already exists. 18: SELECT count(*) INTO NoFlag 19: FROM buildings 20: WHERE BldgName = inBldgName; 21: 22: IF NoFlag > 0 THEN 23: RETURN null; 24: END IF; 25: 26: --Check to see if the manager employee id is valid. 27: SELECT count(*) INTO NoFlag 28: FROM employee 29: WHERE emp_id = inBldgMgr; 30: 31: IF NoFlag = 0 THEN 32: RETURN null; 33: END IF; 34: 35: --All validation checks have been passed, create the new 36: --building object. 37: TheNewBldg := building (inBldgName 38: ,address (inBldgStreet 39: ,’’ --no second addr line
Answers 641 40: ,inBldgCity 41: ,inBldgStateAbbr 42: ,inBldgZip A 43: ,’’) --no phone number 44: ,inBldgMgr); 45: 46: RETURN TheNewBldg; 47: END; 48: / Function created. 49: --Create some building objects 50: DECLARE 51: a_building building; 52: BEGIN 53: --This will succeed 54: a_building := CreateBuilding(‘The Red Barn’, 55: ‘101 Pasture Lane’, 56: ‘Mio’,’MI’,’48826’,599); 57: dbms_output.put_line(‘Created: ‘ || a_building.BldgName); 58: 59: --This will fail because the building exists. 60: a_building := CreateBuilding(‘East Storage Shed’, 61: ‘101 Pasture Lane’, 62: ‘Mio’,’MI’,’48826’,599); 63: dbms_output.put_line(‘Created: ‘ || 64: nvl(a_building.BldgName,’Nothing’)); 65: 66: --This will fail because the manager does not exist. 67: a_building := CreateBuilding(‘The Blue Barn’, 68: ‘101 Pasture Lane’, 69: ‘Mio’,’MI’,’48826’,999); 70: dbms_output.put_line(‘Created: ‘ || 71: nvl(a_building.BldgName,’Nothing’)); 72: 73: END; 74: / Created: The Red Barn Created: Nothing Created: Nothing PL/SQL procedure successfully completed. The CreateBuilding function takes five arguments: a building name, street ANALYSIS address, city, state abbreviation, and manager ID. It returns an object of type building. The SELECT statement in lines 18–20 first checks to see if a building with the same name already exists. Then the SELECT statement in lines 27–29 checks to be sure
642 Appendix that the manager ID is a valid employee ID. If everything checks out, the building constructor is called in lines 37–44 to actually create the building object, which is then returned to the calling program (see line 46). The PL/SQL block at the end of the listing (lines 50–74) shows the results of three attempts to create building objects. The first succeeds. The second fails because a build- ing with the same name already exists. The third also fails, but this time because the building manager ID does not represent a valid employee ID. 2. Modify the building object type definition to use a MAP function, instead of an ORDER function, for comparisons. Here is one solution: 1: CREATE OR REPLACE TYPE building AS OBJECT ( INPUT 2: BldgName VARCHAR2(40), 3: BldgAddress address, 4: BldgMgr INTEGER, 5: MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER), 6: MAP MEMBER FUNCTION Compare 7: RETURN VARCHAR2 8: ); 9: 10: Type created. 11: 12: CREATE OR REPLACE TYPE BODY building AS 13: MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS 14: BEGIN 15: BldgMgr := NewMgr; 16: END; 17: 18: MAP MEMBER FUNCTION Compare 19: RETURN VARCHAR2 IS 20: BEGIN 21: RETURN BldgName; 22: END; 23: END; 24: / Type body created. This version of the building object is much the same as the one you first created ANALYSIS from Listing 12.7, except that it has a MAP function defined instead of an ORDER function. This MAP function, declared in lines 6–7 and defined in lines 18–22 of the sec- ond segment, simply returns the building name. When comparing objects of type building, Oracle will call this function and base the comparison on the values returned.
Answers 643 Day 13, “Debugging Your Code and A Preventing Errors” Quiz 1. True or False: Logic errors are easier to debug than syntax errors. False. Because the compiler doesn’t point them out, logic errors are almost always more difficult to debug than syntax errors. 2. Missing a semicolon is what type of an error? A syntax error. 3. Provide the answer to the calculation 6 + 4 / 2 = ?. The expression 6 + 4 / 2 evaluates to 8. The division takes precedence over the addition, so it is done first. 4. True or False: Commenting code is a waste of time. False. Comments improve the readability of code and clarify the intent of the application programmer. 5. True or False: Formatting code is not necessary. False. If you don’t format your code, it will be difficult to read, increasing the like- lihood of making a mistake. Exercise The DEBUG package in this lesson always writes debugging messages to the file named debug.txt. That will cause problems if multiple developers use the package at once. Modify the DEBUG package as follows: • Modify the ERASE procedure to accept a filename. This filename will be used by subsequent calls to the OUT procedure. • Modify both the OUT and ERASE procedures so that if no filename is passed, or if ERASE is never called, no file is created and no messages get written. For extra credit, add an ASSERT procedure to the DEBUG package, and build in a flag so that you can enable and disable assertions at will. In one possible solution to the exercise, the modified DEBUG package would look like this:
644 Appendix 1: CREATE OR REPLACE PACKAGE DEBUG AS INPUT 2: /* Procedure OUT is used to output a comment of your 3: choice, along with the contents of the variable. The 4: Procedure OUT statement defines the function.*/ 5: PROCEDURE OUT(p_Comments IN VARCHAR2, 6: p_Variable IN VARCHAR2); 7: 8: /* Procedure Erase begins a new file. 9: Used to start a new debugging process. Good idea to call 10: this function first. */ 11: PROCEDURE Erase (p_filename IN VARCHAR2); 12: 13: /*Procedure ASSERT tests a condition, and raises an error 14: if that condition is not true. */ 15: PROCEDURE assert ( 16: p_condition IN BOOLEAN, 17: p_message IN VARCHAR2); 18: END DEBUG; -- End Definition of package DEBUG 19: / 20: 21: CREATE OR REPLACE PACKAGE BODY DEBUG AS 22: log_filename VARCHAR2(30) := ‘’; 23: 24: PROCEDURE OUT(p_Comments IN VARCHAR2, 25: p_Variable IN VARCHAR2) IS 26: v_MyFHOUT UTL_FILE.FILE_TYPE; -- Declare File Handle 27: 28: BEGIN 29: /* Exit if no filename has been specified. */ 30: IF log_filename = ‘’ THEN 31: RETURN; 32: END IF; 33: 34: /* Use A to append all output being sent to the file */ 35: v_MyFHOUT := UTL_FILE.FOPEN(‘c:\a’,log_filename,’a’); 36: 37: /* This outputs the Time and Date as MM-DD-YY HH:MM:SS 38: followed by comments, and then by the contents of the 39: variables. Each element is surrounded by quotation marks, 40: and separated by a comma.*/ 41: 42: UTL_FILE.PUT_LINE(v_MyFHOUT,’”’|| 43: TO_CHAR(SYSDATE,’mm-dd-yy HH:MM:SS AM’) 44: || ‘“,”Comment: ‘ || p_Comments || 45: ‘“,”Variable Contents: ‘ || p_Variable || ‘“‘); 46: 47: /* Close the file handle which points to debug.txt */ 48: UTL_FILE.FCLOSE(v_MyFHOUT); 49: EXCEPTION 50: /* Create Exception to display error code and message */ 51: WHEN OTHERS THEN
Answers 645 52: DBMS_OUTPUT.PUT_LINE 53: (‘ERROR ‘ || to_char(SQLCODE) || SQLERRM); 54: NULL; -- Do Nothing A 55: END OUT; -- End Execution of Procedure OUT 56: 57: 58: PROCEDURE Erase (p_filename IN VARCHAR2) IS 59: v_MyFH UTL_FILE.FILE_TYPE; -- Create File Handle 60: BEGIN 61: /* Save the filename, then check to see if it is blank. 62: If the filename is blank, then do not do anything more. */ 63: log_filename := p_filename; 64: IF log_filename = ‘’ THEN 65: RETURN; 66: END IF; 67: 68: /* Open file to overwrite current file contents. 69: This erases the original file.*/ 70: 71: v_MyFH := UTL_FILE.FOPEN(‘c:\a’,log_filename,’w’); 72: 73: -- Close the file handle which points to debug.txt 74: UTL_FILE.FCLOSE(v_MyFH); 75: 76: EXCEPTION 77: -- Create Exception to display error code and message 78: WHEN OTHERS THEN 79: DBMS_OUTPUT.PUT_LINE 80: (‘ERROR ‘ || to_char(SQLCODE) || SQLERRM); 81: NULL; 82: END Erase; -- End Procedure Erase 83: 84: PROCEDURE ASSERT ( 85: p_condition IN BOOLEAN, 86: p_message IN VARCHAR2) AS 87: BEGIN 88: IF NOT p_condition THEN 89: RAISE_APPLICATION_ERROR (-20000,p_message); 90: END IF; 91: END ASSERT; 92: 93: BEGIN 94: Erase(‘debug.txt’); -- Erase contents of the file 95: END DEBUG; -- End procedure DEBUG 96: / As you can see, the ASSERT procedure has been added to this package. The proce- dure header is in lines 15–17, and the procedure body is in lines 84–91. Further, the ERASE procedure has been modified to accept a filename as an argument (lines 11 and 58). If the filename is blank, the ERASE procedure stores it and quits (lines
646 Appendix 63–66). The OUT procedure has also been modified. The first thing it does, in lines 29–31, is check the filename. If the filename is blank, OUT returns without logging any debugging information. So not only can you now write debugging information to any file you like, but you can also turn the feature off. Day 14, “Leveraging Large Object Types” Quiz 1. What are the two types of internal LOBs? The two types of internal LOBs are the persistent and the temporary LOBs. 2. What is the maximum size of a LOB? 4GB. 3. Can you write to external files? Currently, you can only read from external files, not write to them. 4. When copying LOBs from one row to another, is a new locator copied? Not only is a new locator created, but the entire LOB from the row is copied. If you have some 4GB objects, this table can eat up storage space fast! Exercise Create a temporary LOB that is of BLOB datatype, that will not be stored in the buffer, and that will be limited to the current call. Your solution should look similar to this: begin INPUT DBMS_LOB.CREATETEMPORARY ( blob_lob_loc, FALSE, DBMS_LOB.CALL); End; In this solution, you have created a temporary LOB of the BLOB datatype. You ANALYSIS have specified that it will not be stored in memory and will disappear after the current Oracle8i call. Day 15, “Managing Transactions and Locks” Quiz 1. How is a transaction ended? A transaction is ended when it is committed or rolled back.

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