Midterm Short Answers Flashcards
Complete the PL/SQL block to declare a cursor named employee_cursor to select employee details from the employees table, then loop
through the cursor and print each employee’s name and salary.
DECLARE
CURSOR employee_cursor IS
SELECT employee_name, salary FROM employees;
BEGIN
/// Code here ///
END;
DECLARE
CURSOR employee_cursor IS
SELECT employee_name, salary FROM employees;
BEGIN
FOR employee_rec IN employee_cursor LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ‘ || employee_rec.employee_name || ‘, Salary: ‘ || employee_rec.salary);
END LOOP;
END
Complete the trigger named update_inventory_stock that updates the inventory stock level whenever a new order is placed.
CREATE OR REPLACE TRIGGER update_inventory_stock
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
/// Code Here///
END;
UPDATE inventory
SET stock_level = stock_level - :new.quantity
WHERE product_id = :new.product_id;
What is the purpose of using static SQL in PL/SQL?
a) To execute dynamic SQL statements that are constructed at runtime.
b) To enhance security, performance, and simplicity by using SQL statements that are known and fixed at compile time.
c) To allow the use of placeholders and bind variables in SQL statements.
d) To execute SQL statements that can change based on user input or other conditions.
b) To enhance security, performance, and simplicity by using SQL statements that are known and fixed at compile time.
Differentiate between implicit and explicit cursors in Oracle. Which of the following statements is TRUE?
a) Implicit cursors are user-defined cursors for multi-row queries, while explicit cursors are automatically created by Oracle for single-row queries.
b) Implicit cursors are automatically created by Oracle for single-row queries, while explicit cursors are user-defined for multi-row queries.
c) Both implicit and explicit cursors are automatically managed by Oracle without any user intervention.
d) Explicit cursors can only be used for SELECT statements, while implicit cursors can be used for any SQL statement.
b) Implicit cursors are automatically created by Oracle for single-row queries, while explicit cursors are user-defined for multi-row queries.
What is the significance of bulk operations in PL/SQL? Give an example.
a) Bulk operations allow the execution of multiple SQL statements in a single PL/SQL block, which enhances readability.
b) Bulk operations enable the execution of DDL statements within PL/SQL, which improves database schema management.
c) Bulk operations allow processing of multiple rows of data with a single context switch between PL/SQL and SQL, which significantly improves performance.
d) Bulk operations are used to handle exceptions in PL/SQL, which increases error management capabilities.
c) Bulk operations allow processing of multiple rows of data with a single context switch between PL/SQL and SQL, which significantly improves performance.
Explain the code
CREATE OR REPLACE PROCEDURE ExampleUpdate IS
dynamic_sql_statement VARCHAR2(1000);
result_values Sales%ROWTYPE;
CURSOR cur IS
SELECT * FROM “SALES” “A1”;
BEGIN
dynamic_sql_statement := ‘UPDATE Sales SET quantity_sold = quantity_sold + 10’;
EXECUTE IMMEDIATE dynamic_sql_statement;
COMMIT;
OPEN cur;
LOOP
FETCH cur INTO result_values;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Result: ‘ || result_values.product_name || ‘, ‘ || result_values.sale_date || ‘, ‘ ||
result_values.quantity_sold);
END LOOP;
CLOSE cur;
END ExampleUpdate;
Explanation:
CREATE OR REPLACE PROCEDURE ExampleUpdate IS: This line begins the definition of the PL/SQL procedure named
ExampleUpdate. The CREATE OR REPLACE allows you to modify the procedure if it already exists.
dynamic_sql_statement VARCHAR2(1000);
result_values Sales%ROWTYPE;
* dynamic_sql_statement: This is a variable of type VARCHAR2(1000) used to store a dynamic SQL statement. Dynamic SQL
allows you to build and execute SQL statements at runtime.
* result_values: This is a variable of the %ROWTYPE of the Sales table. %ROWTYPE allows you to declare a variable with the same
structure as a table row. When using %ROWTYPE in PL/SQL, you can declare a variable that has the same structure as a
specific table row. This means that the variable will have the same data types and order of fields as the corresponding table.
CURSOR cur IS
SELECT
“A1”.”TRANSACTION_ID” “TRANSACTION_ID”,
“A1”.”PRODUCT_NAME” “PRODUCT_NAME”,
“A1”.”SALE_DATE” “SALE_DATE”,
“A1”.”QUANTITY_SOLD” “QUANTITY_SOLD”
FROM
“SALES” “A1”;
* The above section declares a cursor named cur.
* The cursor is defined to select specific columns from the SALES table (TRANSACTION_ID, PRODUCT_NAME, SALE_DATE, and
QUANTITY_SOLD) using the alias “A1”.
OPEN cur; This line opens the cursor cur that was declared earlier. Opening a cursor prepares it for fetching rows from the result set of
the associated query.
LOOP
FETCH cur INTO result_values;
* This begins a loop that fetches rows from the cursor (cur) and stores the values into the result_values variable, which was
declared as the %ROWTYPE of the Sales table.
* The FETCH statement retrieves the next row from the result set into the specified variable (result_values).
EXIT WHEN cur%NOTFOUND; The EXIT WHEN statement checks if there are no more rows to fetch (cur%NOTFOUND). If there are no
more rows, it exits the loop.
DBMS_OUTPUT.PUT_LINE(‘Result: ‘ || result_values.product_name || ‘, ‘ || result_values.sale_date || ‘, ‘ ||
result_values.quantity_sold);
* This line prints the values of product_name, sale_date, and quantity_sold from the current row to the console using
DBMS_OUTPUT.PUT_LINE.
Explain the code
CREATE OR REPLACE PROCEDURE SortProcedure (p_sort_column VARCHAR2) IS
dynamic_sql_statement VARCHAR2(1000);
BEGIN
dynamic_sql_statement :=
‘SELECT * FROM Sales’ ||
CASE WHEN p_sort_column IS NOT NULL THEN ‘ ORDER BY ‘ || p_sort_column ELSE NULL END;
EXECUTE IMMEDIATE dynamic_sql_statement;
END SortProcedure;
/
This procedure SortProcedure takes a parameter p_sort_column of type VARCHAR2.
* It declares a variable dynamic_sql_statement of type VARCHAR2(1000) to store the dynamic SQL statement.
* The dynamic SQL statement is constructed using concatenation (||). It selects all columns from the Sales table and includes
an ORDER BY clause based on the value of the p_sort_column parameter. If p_sort_column is not NULL, it is added to the
statement; otherwise, the ORDER BY clause is omitted.
* The EXECUTE IMMEDIATE statement is used to execute the dynamically constructed SQL statement.
BEGIN
DynamicSortProcedure(NULL);
END;
/
* This block executes the SortProcedure with a NULL parameter, which means no specific sorting order is applied (no ORDER BY
clause in the dynamic SQL).
Explain the code
DECLARE
sql_stmt VARCHAR2(200);
salary_threshold NUMBER := 6000;
emp_count NUMBER;
BEGIN
sql_stmt := ‘SELECT COUNT(*) FROM employees WHERE salary > :threshold’;
EXECUTE IMMEDIATE sql_stmt INTO emp_count USING salary_threshold;
IF emp_count > 2 THEN
DBMS_OUTPUT.PUT_LINE(‘High number of employees with salary > ‘ || salary_threshold);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Manageable number of employees with salary > ‘ || salary_threshold);
END IF;
END;
/
The sql_stmt variable is assigned a dynamic SQL statement using a string literal.
* The SQL statement is designed to count the number of rows (COUNT(*)) in the employees table where the salary is greater than a
specified threshold.
* The :threshold is a placeholder for a bind variable, which allows for parameterization of the threshold value. It’s a placeholder
that will be replaced with an actual value when the dynamic SQL statement is executed.
:threshold:
* The :threshold placeholder is a bind variable in the SQL statement.
* This placeholder allows the dynamic SQL to be more flexible by allowing the substitution of a specific value at runtime.
* In this case, the salary_threshold variable is used to set the value for the :threshold placeholder.
* The EXECUTE IMMEDIATE statement is used to execute the dynamic SQL statement stored in the sql_stmt variable.
* The INTO emp_count clause captures the result of the dynamic SQL statement (the count of employees) into the emp_count
variable.
* The USING salary_threshold clause binds the value of the salary_threshold variable to the :threshold placeholder in the
dynamic SQL statement.
Explain the code
DECLARE
TYPE Rec_Cust IS RECORD
(l_id NUMBER,
l_fname VARCHAR2(20),
l_lname VARCHAR2(40));
Cust Rec_Cust;
CURSOR cr IS SELECT Cust_ID, Cust_First_Name, CUst_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;
BEGIN
OPEN cr;
LOOP
FETCH cr INTO Cust;
DBMS_OUTPUT.PUT_LINE(Cust.l_id ||’ ‘||
Cust.l_fname || ‘ ‘||
Cust.l_lname);
EXIT WHEN cr%NOTFOUND;
END LOOP;
CLOSE cr;
END;
– insert
CREATE TABLE MyCustomer AS
SELECT Cust_ID, Cust_First_name, Cust_Last_Name
FROM sh.Customers WHERE ROWNUM< 0;
DECLARE
TYPE rec_cust IS RECORD
(l_id NUMBER,
l_fname VARCHAR2(20),
l_lname VARCHAR2(40));
l_cust Rec_Cust;
CURSOR cr IS SELECT Cust_ID, Cust_First_Name, CUst_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;
BEGIN
OPEN cr;
LOOP
FETCH cr INTO l_cust;
INSERT INTO MyCustomer VALUES(l_cust.l_id,
l_cust.l_fname,
l_cust.l_lname);
EXIT WHEN cr%NOTFOUND;
END LOOP;
CLOSE cr;
END;
/
COMMIT;
SELECT * FROM MyCustomer;
DECLARE
TYPE Rec_Cust IS RECORD
(
l_id NUMBER,
l_fname VARCHAR2(20),
l_lname VARCHAR2(40)
);
Cust Rec_Cust;
Rec_Cust is a user-defined record type that encapsulates a structured set of fields or attributes.
* It is defined with three fields:
l_id of type NUMBER
l_fname of type VARCHAR2(20)
l_lname of type VARCHAR2(40)
* This record type is essentially a blueprint for a structure that can hold related pieces of data.
* Cust is declared as a variable of the record type Rec_Cust.
* This variable will be used to store an instance of the Rec_Cust record type, essentially creating a container that can hold
values for l_id, l_fname, and l_lname.
* The variable can be used to manipulate or store data structured according to the specified record type.
CURSOR cr IS SELECT Cust_ID, Cust_First_Name, CUst_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;
BEGIN
OPEN cr;
LOOP
FETCH cr INTO Cust;
DBMS_OUTPUT.PUT_LINE(Cust.l_id ||’ ‘||
Cust.l_fname || ‘ ‘||
Cust.l_lname);
EXIT WHEN cr%NOTFOUND;
END LOOP;
CLOSE cr;
END;
FETCH cr INTO Cust
* The FETCH statement is used within a loop to retrieve rows of data from a cursor.
* The INTO clause is followed by the variable or record into which the data from the fetched row will be stored.
* The data from the current row of the cursor result set is assigned to the fields of the record variable Cust.
* The fields of Cust correspond to the columns selected in the cursor declaration (Cust_ID, Cust_First_Name, Cust_Last_Name).
* After the FETCH statement, the values retrieved from the cursor are available in the Cust variable.
Same code could be written using a cursor instead of a record:
DECLARE
CURSOR cr IS SELECT Cust_ID, Cust_First_Name, Cust_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;
l_id NUMBER;
l_fname VARCHAR2(20);
l_lname VARCHAR2(40);
BEGIN
OPEN cr;
LOOP
FETCH cr INTO l_id, l_fname, l_lname; – Using individual variables
EXIT WHEN cr%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_id || ‘ ‘ || l_fname || ‘ ‘ || l_lname);
END LOOP;
CLOSE cr;
END;
Explain the code
CREATE OR REPLACE PROCEDURE Bulk_Insert
IS
TYPE tbltp IS TABLE OF MyCustomer%ROWTYPE;
l_tbl tbltp;
BEGIN
SELECT * BULK COLLECT INTO l_tbl
FROM sh.Customers;
FORALL i IN 1..l_tbl.LAST
INSERT INTO MyCustomer VALUES l_tbl(i);
COMMIT;
END;
/
