Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

Structured Query Languge

Loading...

Published in: Computer Science
681 Views

Question answer series

Priyanka / Navi Mumbai

8 years of teaching experience

Qualification: master's pf computer engineering

Teaches: Computer Science

Contact this Tutor
  1. Que: explain control structure in pl/sql? Ans: According to the structure theorem, any computer program can be written using the basic control structures, which can be combined in any way necessary to deal with a given problem. The selection structure tests a condition, and then executes one sequence of statements instead of another, depending on whether the condition is true or false. The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur. CONDITIONAL CONTROL 1. Conditional control allows you to control the flow of the execution of the program based on a condition; it means that the statements in the program are not executed sequentially. The IF statement lets you execute a sequence of statements conditionally. There are three forms of IF statements - IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. IF-THEN This construct tests a simple condition. If the condition evaluates to TRUE, one or more lines of code are executed. If the condition evaluates to FALSE, program control is passed to the next statement after the test. The following code illustrates implementing this logic in PL/SQL. // The test, in this case ">", is a relational operator If varl > 10 then var2 := varl + 20; END IF; • You may code nested IF-THEN statements as shown in the following. IF varl > 10 THEN IF var2 < varl THEN var2 := varl + 20; END IF; END IF; there are two END IF in the above example - one for each IF. This leads us into two rules about implementing IF logic in PL/SQL 1. Each IF statement is followed by its own THEN. There is no semicolon (;) terminator on the line that starts with IF. 2. Each IF statement block is terminated by a matching END IF. NOTE: YOU CAN WRITE ONE EXAMPLE IN ABOVE IF-THEN STATEMENT.
  2. 2. IF-THEN-ELSE This construct is similar to IF, except that when the condition evaluates to FALSE, one or more statements following the ELSE are executed. The following code illustrates implementing this logic in PL/SQL. IF varl > 10 THEN var2 := varl + 20; ELSE var2 := varl * varl; END IF; This statement can be nested also, as shown below. IF varl > 10 THEN var2 := varl + 20; ELSE IF varl BETWEEN 7 AND 8 THEN var2 2 * varl; ELSE var2 := varl * varl; END IF; END IF; This leads us to two more rules about implementing if logic in PL/SQL: 1. There can be one and only one ELSE with every IF statement. 2. There is no semicolon (;) terminator after ELSE. 3. IF-THEN-ELSIE ' This format is an alternative to using the nested IF-THEN-ELSE construct. IF varl > 10 THEN var2 := varl + 20; ELSIF varl BETWEEN 7 AND 8 THEN var2 := var2 * varl; ELSE var2 := varl * varl; END IF; • This leads us to one final rule about implementing IF logic in PL/SQL. 1. There is no matching END IF with each ELSIF.
  3. ITERATIVE CONTROL 1. LOOP 2. WHILE-LOOP LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR- LOOP. The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows: LOOP statementl ; statement2; statement3 ; END LOOP; All the sequence of statements is executed for each iteration of the loop. Then, the control resumes at the top of the loop and the cycle starts again. A WHILE loop has the following structure: WHILE LOOP statement 1; statement 2; statement 3; statement N; END LOOP; The reserved word WHILE marks the beginning of a loop construct. The word "" is the test condition of the loop that evaluates to TRUE or FALSE. The result of this evaluation determines whether the loop is executed. The following is an example of using WHILE LOOP. DECLARE v counter BEGIN NUMBER 1; WHILEv 5 LOOP I I v_counter);
  4. 3. FOR-LOOP GOTO -- increment the value of v _ counter by one v counter :=v counter + 1; END LOOP; END; Whereas the number of iteration through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. FOR counter IN [REVERSE] LOOP statement 1; statement 2; statement 3; statement N; END LOOP; The lower bound may not be 1. However, the loop counter increment (or decrement) must be 1. Between the lower bound and the upper bound is a double dot (..), which serves as the range operator. PL/SQL lets you determine the loop range dynamically at run time, as the following example shows: SET SERVE-ROUTPUT ON DECLARE cnt_employee NUMBER; BEGIN SELECT INTO cnt_employee FROM employee; FOR v_counter IN l..cnt_employee LOOP I I v _ counter); END LOOP; END; ' PL/SQL also includes a GOTO statement to branch from one point to another. The syntax is: GOTO • where "" is a label defined in the PL/SQL block.
  5. Labels are enclosed in double angle brackets (). When a GOTO statement is evaluated, control immediately passes to the statement identified by the label. An example follows: SET SERVEROUTPUT ON DECLARE v_counter NUMBER(2) 1; BEGIN LOOP v counter :=v counter+l; IFv counter > 5 THEN GOTO 1 ENDOFLOOP; -- print v_counter 5 times END IF; I I v_counter); END LOOP; END; QUE: EXPLAIN EXCEPTION HANDLING AVAILABLE IN PL/SQL? ANS: :HOW DO HANDLE EXCEPTION RAISED INSIDE PROCEDURE In PL/SQL, a warning or error condition is called an exception. A block is always terminated when PL/SQL raises an exception, but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. There are two classes of exceptions, these are: 1. Predefined - Oracle predefined errors which are associated with specific error codes. 2. User-defined - Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.
  6. • The "exception section" usually appears at the end of the PL/SQL- block. The syntax is: EXCEPTION WHEN exceptionl-name> THEN WHEN exception2-name> THEN
  7. 'NO DATA FOUND" and "TOO MANY ROWS" are the two most common errors found when executing a SELECT statement. The example below takes care of these two conditions. NOTE:FOLLOWING IS SHORT EXAMPLE FOR EXCEPTION HANDLING WRITE EITHER ONE EXAMPLE FROM THOSE TWO BEST IF YOU WRITE SECOND ONE. EXAMPLE 1: SET SERVEROUTPUT ON DECLARE ssn name BEGIN employee.emp_ssn%TYPE; employee.emp_last_name%TYPE; SELECT emp_ssn,emp_last_name INTO ssn,name FROM employee WHERE emp_dpt_number=45; EXCEPTION WHEN NO DATA FOUND THEN is no employee in that Department'); WHEN TOO MANY ROWS THEN too many rows'); END;
  8. However, it happens sometimes when other errors other than "NO_DATA_FOUND" and "TOO_MANY_ROWS" occurs. To cover all possible errors you can specify a catch all action named OTHERS. EXAMPLE2: SET SERVE-ROUTPUT ON DECLARE ssn name employee.emp_ssn%TYPE; employee.emp_last_name%TYPE; VARCHAR2(512); BEGIN SELECT INTO ssn,name FROM employee WHERE emp_dpt_number=45; EXCEPTION WHEN NO DATA FOUND THEN is no employee in that Department'); WHEN TOO MANY ROWS THEN too many rows'); WHEN OTHERS THEN SQLERRM; DBMS_OUTPUT.PUT_LlNE('This program encountered the following error:'); END; PL/SQL provides two special functions for use within an EXCEPTION section, SQLCODE and SQLERRM. SQLCODE contains the Oracle error code of the exception. SQLERRM contains the Oracle error message of the exception. You can use these functions to detect what error has occurred (very useful in an OTHERS action). The above example illustrated how SQLERRM can be used. SQLCODE and SQLERRM should be assigned to some variables before you attempt to use them.
  9. NOTE: HERE FOLLOWING EXPLINATION IS NECESSARY AND EXAMPLE IF YOU WANT TO WRITE THEN WRITE ELSE NOT WRITE THEN ALSO BE OK. A User-defined exception should be declared and raised explicitly by a RAISE statement. It can be declared only in the declarative part of the PI/SQL block. The syntax is: In the declarative section, EXCEPTION; The syntax for the RAISE statement is: RAISE ; An example follows(OPTlONAL): DECLARE exp_low_value var_equip_cnt var_min_equip BEGIN EXCEPTION ; -- declared here. equipment.eqp_qty_on_hand%TYPE; equipment.eqp_qty_on_hand%TYPE := 2, SELECT eqp_qty_on_hand INTO var_equip_cnt FROM equipment WHERE eqp_no = 4321; IF var_equip_cnt < var_min_equip THEN RAISE exp_low_value; END IF; EXCEPTION WHEN THEN is less than reorder level - immediately'); END; replenish
  10. QUE: WRITE NOTE ON LOB(LARGE OBJECTS)? ANS: Oracle supports LOBs(Large Objects) which can hold large amount of raw binary data, such as graphics images, as well as large amount of character data. Oracle extended SQL DDL and DML to provide support for LOBs. You can also manipulate LOBS using DBMS_LOB package and OCI (Oracle Call Interface). Depending upon the way in which LOBS are stored they can be classified as follows: 1. 2. Internal LOBs: • These are stored in the database tablespace. • They support transaction processing like any other scalar data type. • CLOB, BLOB and NCLOB belong to this category. External LOBS These are not stored in the database. Instead they are stored in the Operating System files. Only a pointer pointing to the actual data is stored in the database. They do not support transaction processing and integrity checking. BFILE data type belongs to this category. figure 1, to understand how data is stored in internal lob and external lob. DATABASE CLOB TABLE CLOB COL Lob Locator Internal LOB BFILE COL External LOB CLOB Value a.bmp
  11. LOB Datatypes ' The following are the different LOB datatypes that are supported by Oracle8. Data Type CLOB NCLOB BLOB BALE LOB Locator: Description The data is consisting of single-byte character data. The data is consisting of multi-byte or single-byte fixed length character data that corresponds to the national character set. The data is consisting of RAW binary data, such as bitmap images. The data is stored in an operating system file. Only a reference to the file is stored in the database. This is the example for External LOB. The data of the LOB column is NOT stored in the row along with the other columns of the row, instead only a locator is stored in the database and the actual data is stored elsewhere. The locator is similar to a pointer and points to the location where the data is actually stored. In case of Internal LOB the data is stored within the database, AND in case of external LOB the data is stored outside the database as a file in the file system of the operating system. The value that is stored in the row to point to the actual location of the Internal LOB is called as LOB Locator. It is used to locate the LOB data that is stored elsewhere in the database. LONG vs. LOBs LONG LONG datatype and LOBS are similar in some respects and differ in other. LOB data types can be taken as an extension to LONG RAW data type. LONG RAW is the only data type that supported large binary data in Oracle7. The following are the differences between LONG RAW data type and LOB data types. LONG type Can contain up to 2 GB of data. A table can contain only one LONG column. A sub-query cannot select a LONG column. LOB type Can contain up to 4 GB of data. A table can contain more than one LOB column. A subquery can select LOB column
  12. Defining and Manipulating LOBs A LOB column is defined just like any other column. Data of the LOB column can be accessed either directly or through DBMS _ LOB package. Let us first see how to create a table with LOB columns. The following example creates a table with a CLOB type column and a BFILE type column. create table lob table id number(5), clob col clob, bfile col bfile QUE: ORACLE DATATYPE? ANS: CHAR (size [BYTE I CHAR] ) VARCHAR2 ( size [BYTE I CHAR] ) NCHAR(size) NVARCHAR2 ( size) CLOB Fixed-length character data of length size bytes or characters. Variable-length character data, with maximum length size bytes Fixed-length Unicode character data of length size characters. Variable-length Unicode character data of length size characters. A maximum size must be specified. Single-byte character data Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (single-byte or multibyte) before setting size. Variable for each row, up to 4000 bytes per row. Consider the character set (single-byte or multibyte) before setting size. A maximum size must be specified. Fixed for every row in the table (with trailing blanks). Column size is the number of characters. (The number of bytes is 2 times this number for the ALI 6UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 2000 bytes per row. Default is 1 character. Variable for each row. Column size is the number of characters. (The number of bytes may be up to 2 times this number for a the ALI 6UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 4000 bytes per row. Default is 1 character. Up to 232 - 1 bytes, or 4 gigabytes.
  13. NCLOB LONG NUMBER (p, s) DATE BLOB BFILE RAW (size) LONG RAW ROWID Unicode national character set Variable- length character Variable-length numeric data. Maximum precision p Fixed-length date and time data, ranging from Jan. 1, 4712 Unstructured binary data Binary data stored in an external file Variable-length raw binary data Variable-length raw binary data Binary data representing row addresses Up to 232 - 1 bytes, or 4 gigabytes. Variable for each row in the table, up to 232 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility. Variable for each row. The maximum space required for a given column is 21 bytes per row. Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS DATE Up to 232 - Up to 232 - FORMAT parameter. 1 bytes, or 4 gigabytes. 1 bytes, or 4 gigabytes. Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility. Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility. Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROW ID) for each row in the table.
  14. QUE: TRIGGER MUTATING TABLE? ANS: A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of changing. It says that if the trigger reads the table (such as using a SELECT query), that changes (even using :NEW) will fail. This can also happen when a trigger on a parent table causes an insert on a child table referencing a foreign key. Mutating Tables Each new release of the Oracle database reduces the impact of the mutating table error on triggers . If a trigger does result in a mutating table error, the only real option is to rewrite the trigger as a statement-level trigger. Mutating table errors only impact row level triggers. But to use a statement level trigger, some data may need to be preserved from each row, to be used by the statement level trigger. This data can be stored in a PL/SQL collection or in a temporary table. A simple row level trigger that causes a mutating table error can result in a very complicated statement level trigger to achieve the needed result. Here are some important items to remember about triggers. 1. 2. 3. 4. 5. 6. 7. 8. On insert triggers have no :OLD values. On delete triggers have no :NEW values. Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back. Commits, rollbacks and save points are not allowed in the trigger body. Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger. If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order. A trigger can cause other events to execute triggers. A trigger cannot change a table that it has read from. This is the mutating table error issue.
  15. The fact that a trigger can cause other triggers to fire is an important item to remember. A trigger that causes other database events to execute triggers can cause the database crash. For example, the database can capture server errors by defining a trigger on the database server error event. But if this trigger causes a server error, the database will spin in a loop, with each firing of the trigger causing the error, firing the trigger again, and again, and again. The only way to regain control of the database is to disable the trigger. Avoiding Mutating Triggers The insert to the child table caused the foreign key to validate the data on the parent (which fired the trigger) causing the insert of the child table to result in a mutating table error on the parent table. following are the way to avoid mutating triggers: 1. 2. 3. 4. Don't use triggers The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary. Use an "after" or "instead of" trigger If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating. Re-work the trigger syntax - avoid mutating tables with a combination of row-level and statement-level triggers. Use autonomous transactions You can avoid the mutating table error by marking your trigger as an autonomous transaction . Making it independent from the table that calls the procedure. the mutating table error is usually the result of a poor application design and mutating triggers should be avoided whenever possible.

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related Study Notes

Upload Study Notes

If you have your own Study Notes which you think can benefit others, please upload on LearnPick. For each approved Study Note you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > Study Notes > Structured Query Languge