PLSQL - Oracle Server Flashcards

Question

There are at least 2 different Special Data Types used in PL/SQL. What are they?

Answer 1

Used to declare a variable with the reference to the table %type – used to specify the particular column from a table. - SYNTAX= Name details.name%type; - SYNTAX= City details.city%type; %rowtype – used to specify a complete row from a table. - Is declared within the declaration section of a PL/SQL. - SYNTAX= Record details%rowtype;

Answer 2

LOB is known as large object. LOB type variables are used to store data in large size. Used to store blocks of Unstructured Data and these data types are used to store text, graphic images, video and sounds. We can store maximum of 4gb of data in this variables data types. BFILE – stores large binary objects as a file in the file system. -Uses a pointer or file locator to store a file outside the database. -Locator includes a directory alias which specifies a full path name. -Read-only, cannot modify -cannot exceed 4gb. -cannot participate in transactions, are not recoverable, cannot be replicated. BLOB – can convert to RAW data type. -Used to store large binary objects in the database -Every BLOB variable stores a locator in the database which points to a large binary object. -cannot exceed 4gb. -can participate in transactions, are recoverable, can be replicated. CLOB – can convert to CHAR and VARCHAR types. -Used to store large blocks of character data in the database -Supports both fixed-width and variable-width character sets. -Every CLOB variable stores a locator in the db which points to a large block of character data. -cannot exceed 4gb. -can participate in transactions, are recoverable, can be replicated. NCLOB --Used to store large blocks of NCHAR data in the database -Supports both fixed-width and variable-width character sets. -Every NCLOB variable stores a locator which points to a large block of NCHAR data. -cannot exceed 4gb. -can participate fully in transactions, are recoverable, can be replicated.

Answer 3

1. Implicit Conversion. Is an Automatic data type conversion. 2. Explicit Conversion. Built-in function is used to make the conversion. TO_DATE or TO_NUMBER is used to convert the char data type to date data type. TO_CHAR function is used to convert the date data type to the character data type.

Answer 4

DECLARE Eg. ``` Set serveroutput on DECLARE num integer; val integer; total number(5); ```

Answer 5

BEGIN Eg. ``` BEGIN Num := 5; Val := 10; Total := 5 +10; Dbms_output.put_line(total); END; / ``` -statement Returns 15.

Answer 6

``` create table t ( email varchar2(320) check ( regexp_like(email, '[[:alnum:]]+@[[:alnum:]]+\.[[:alnum:]]') ) ); ``` insert into t values ('abc@xyz.com'); 1 row inserted. insert into t values ('abc@xyz.co.uk'); 1 row inserted. insert into t values ('ab.com'); SQL Error: ORA-02290: check constraint (CHRIS.SYS_C0014586) violated insert into t values ('abc@xyz'); SQL Error: ORA-02290: check constraint (CHRIS.SYS_C0014586) violated

Answer 7

Anchored data types are those data type which you assign to a variable based on a database object. They are called anchored data type because unlike the variable data type it is not dependent on that of any underlying object. SYNTAX= variable_name typed-attribute%type Example 1: How to Declare a variable with Anchored Datatype Write an anonymous block where I will declare a variable with anchored data type and then initialize that variable by fetching value from this table. SET SERVEROUTPUT ON; DECLARE v_fname students.first_name%TYPE; Here In the declaration section I have declared a variable by the name of v_fname with identical data type as the column First Name of table Students. This means that the data type of variable v_fname will be the varchar2 with data width 8. This is the data type and data width of the column first name of our table students. Adding the execution section to this anonymous PL/SQL block and initializing this variable v_fname by fetching data from the table students. BEGIN SELECT first_name INTO v_fname FROM students WHERE stu_id =1; DBMS_OUTPUT.PUT_LINE (v_fname); END; In this execution block I have a Select… Into statement which I am fetching first name of the student whose stu_id is 1 and storing it into our variable v_fname. That’s how we declare a variable with anchored data type.

Answer 8

The lower limit and the higher limit will be specified and as long as the loop variable is in between this range, the loop will be executed. The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The loop variable need not to be declared, as it is declared implicitly. FOR in .. LOOP . . . END LOOP; - In the above syntax, keyword ‘FOR’ marks beginning of the loop and ‘END LOOP’ marks the end of the loop. - Loop variable is evaluated every time before executing the execution part. - The execution block contains all the code that needs to be executed. The execution part can contain any execution statement. - The loop_variable is declared implicitly during the execution of the entire loop, and the scope of this loop_variable will be only inside this loop. - If the loop variable came out of the range, then control will exit from the loop. - The loop can be made to work in the reverse order by adding the keyword ‘REVERSE’ before lower_limit. ``` BEGIN dbms Qutput.put linef.Prp.gram started.' ); FOR a IN 1 .. 5 LOOP dbms_output.put_line(a); END LOOP: dbms_output.put_iine('Program completed.'); END; / ``` ``` OUTPUT: Program started. 1 2 3 4 5 Program completed. ```

Answer 9

The loop statements can also be nested. The outer and inner loop can be of different types. In the nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully. LOOP -outer LOOP — inner END LOOP; END LOOP; In this example, we are going to print number from 1 to 3 using FOR loop statement. Each number will be printed as many times as its value. For that, we will execute the following code. ``` DECLARE b NUMBER; BEGIN dbms output put line(‘Program started' ); FOR a IN 1..3 LOOP b:=1; WHILE (a>=b) LOOP dbms output put line(a); b:=b+1; END LOOP;‭ END LOOP;‭ ‬ dbms_output.put_line('Program completed' ); END; / ``` ``` OUTPUT: Program started. 1 2 2 3 3 3 Program completed ```

Answer 10

While Loop in PL/SQL works similar to the basic loop statement, except the EXIT condition is at the very beginning of the loop. It works like an entry-checking loop where the execution block will only execute if the condition is satisfied, as the exit condition is checked before execution. It does not explicitly require the ‘EXIT’ keyword to exit from the loop since it validates the condition implicitly each time. WHILE LOOP . . . END LOOP; In the above syntax, keyword ‘WHILE’ marks beginning of the loop and ‘END LOOP’ marks the end of the loop. EXIT condition is evaluated each time before the execution part is starting executing. The execution block contains all the code that needs to be executed. The execution part can contain any execution statement. ``` DECLARE a NUMBER :=1; BEGIN dbms_output.put_line('Program started'); WHILE (a <= 5) LOOP dbms_output.put_line(a); a:=a+1; END LOOP; dbms_output.put_line(‘Program completed' ); END: / ``` ``` OUTPUT: Program started. 1 2 3 4 5 Program completed ```

Answer 11

Select Insert Update Delete

Answer 12

There are two types of INSERT statements: BEGIN INSERT INTO (,,...) VALUES(,...:); END; The above syntax shows the INSERT INTO command. The table name and values are a mandatory fields, whereas column names are not mandatory if the insert statements have values for all the column of the table. The keyword ‘VALUES’ is mandatory if the values are given separately as shown above. BEGIN INSERT INTO (,,...,) SELECT ,,.. FROM ; END; The above syntax shows the INSERT INTO command that takes the values directly from the using the SELECT command. The keyword ‘VALUES’ should not be present in this case as the values are not given separately.

Answer 13

Data deletion means to delete one full record from the database table. The ‘DELETE’ command is used for this purpose. ``` BEGIN DELETE FROM WHERE ; END; ``` The above syntax shows the DELETE command. The keyword ‘FROM’ is optional and with or without ‘FROM’ clause the command behaves in the same way. ‘WHERE’ clause is optional. If this clause is not given, then the entire table will be deleted.

Answer 14

Data update simply means an update of the value of any column in the table. This can be done using ‘UPDATE’ statement. This statement takes the table name, column name and value as the input and updates the data. ``` BEGIN UPDATE SET =,=,= WHERE ; END; ``` The above syntax shows the UPDATE. The keyword ‘SET’ instruct that PL/SQL engine to update the value of the column with the value given. ‘WHERE’ clause is optional. If this clause is not given, then the value of the mentioned column in the entire table will be updated.

Answer 15

Data projection/fetching means to retrieve the required data from the database table. This can be achieved by using the command ‘SELECT’ with ‘INTO’ clause. The ‘SELECT’ command will fetch the values from the database, and ‘INTO’ clause will assign these values to the local variable of the PL/SQL block. ‘SELECT’ statement should return only one record while using ‘INTO’ clause as one variable can hold only one value. If the ‘SELECT’ statement returns more than one value than ‘TOO_MANY_ROWS’ exception will be raised. ‘SELECT’ statement will assign the value to the variable in the ‘INTO’ clause, so it needs to get at least one record from the table to populate the value. If it didn’t get any record, then the exception ‘NO_DATA_FOUND’ is raised. The number of columns and their datatype in ‘SELECT’ clause should match with the number of variables and their datatypes in the ‘INTO’ clause. The values are fetched and populated in the same order as mentioned in the statement. ‘WHERE’ clause is optional that allows to having more restriction on the records that are going to be fetched. ‘SELECT’ statement can be used in the ‘WHERE’ condition of other DML statements to define the values of the conditions. The ‘SELECT’ statement when using ‘INSERT’, ‘UPDATE’, ‘DELETE’ statements should not have ‘INTO’ clause as it will not populate any variable in these cases. ``` BEGIN SELECT ,.. INTO ,. . FROM WHERE ; END; ``` The above syntax shows the SELECT-INTO command. The keyword ‘FROM’ is mandatory that identifies the table name from which the data needs to be fetched. ‘WHERE’ clause is optional. If this clause is not given, then the data from the entire table will be fetched

Answer 16

To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause: ``` WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; ```