Test Your PostgreSQL stored procedure knowledge with This Quiz

Progress

True / False

In postgresql, a stored procedure is a named PL/SQL block that performs one or more specific tasks.

True False

A stored procedure in postgresql is a named PL/SQL block that can perform tasks such as querying or modifying data.

True / False

In postgresql, stored procedures can accept input parameters.

True False

Stored procedures can accept input parameters, allowing them to perform operations based on the provided input values.

True / False

In postgresql, stored procedures can be called from within other stored procedures.

True False

Stored procedures can call other stored procedures, enabling modular and reusable code.

True / False

In postgresql, a stored procedure cannot return a value to the caller.

True False

While stored procedures themselves do not return values directly, they can return values through OUT parameters or by manipulating data in the database.

True / False

In postgresql, stored procedures must be compiled before they can be executed.

True False

Stored procedures must be compiled before execution, ensuring that the PL/SQL code is syntactically and semantically correct.

True / False

In postgresql, you can create a stored procedure that performs a DML operation without committing the transaction.

True False

Stored procedures can perform DML operations, and the transaction control (commit/rollback) can be managed outside the procedure or within it as needed.

True / False

In postgresql, stored procedures can handle exceptions using the EXCEPTION block.

True False

Stored procedures can include an EXCEPTION block to handle runtime errors and perform appropriate error handling.

True / False

In postgresql, stored procedures can be created within packages for better organization and encapsulation.

True False

Packages in postgresql allow grouping related procedures, functions, and other PL/SQL constructs for better organization and encapsulation.

True / False

In postgresql, you can create a stored procedure to execute dynamic SQL statements.

True False

Stored procedures can execute dynamic SQL using the EXECUTE IMMEDIATE statement, allowing for flexibility in SQL execution.

Q10

True / False

In postgresql, a stored procedure with an IN OUT parameter allows the caller to pass a value to the procedure and get an updated value back.

True False

An IN OUT parameter allows a stored procedure to accept a value, modify it, and return the updated value to the caller.

Q11

Multiple Choice

Identify the SQL statements that correctly create a basic stored procedure in postgresql.

SQL Code

CREATE OR REPLACE PROCEDURE increase_salary
(p_employee_id NUMBER, p_increment NUMBER)
IS BEGIN
 UPDATE employees
 SET salary = salary + p_increment
 WHERE employee_id = p_employee_id;
END;

CREATE OR REPLACE PROCEDURE increase_salary (p_employee_id NUMBER, p_increment NUMBER) IS BEGIN UPDATE employees SET salary = salary + p_increment WHERE employee_id = p_employee_id; END; CREATE OR REPLACE PROCEDURE increase_salary (p_employee_id NUMBER, p_increment NUMBER) AS BEGIN UPDATE employees SET salary = salary + p_increment WHERE employee_id = p_employee_id; END; CREATE OR REPLACE PROCEDURE increase_salary AS (p_employee_id NUMBER, p_increment NUMBER) BEGIN UPDATE employees SET salary = salary + p_increment WHERE employee_id = p_employee_id; END;CREATE OR REPLACE PROCEDURE increase_salary (p_employee_id NUMBER, p_increment NUMBER) IS BEGIN UPDATE employees SET salary = salary + p_increment END;

A basic stored procedure in postgresql involves defining input parameters and implementing the desired logic within the BEGIN and END blocks. The correct SQL statements will include the appropriate CREATE OR REPLACE PROCEDURE syntax.

Q12

Multiple Choice

Select the SQL statements that correctly create a stored procedure with output parameters in postgresql.

CREATE OR REPLACE PROCEDURE get_salary (p_employee_id NUMBER, p_salary OUT NUMBER) IS BEGIN SELECT salary INTO p_salary FROM employees WHERE employee_id = p_employee_id; END; CREATE OR REPLACE PROCEDURE get_salary (p_employee_id NUMBER, OUT p_salary NUMBER) IS BEGIN SELECT salary INTO p_salary FROM employees WHERE employee_id = p_employee_id; END; CREATE OR REPLACE PROCEDURE get_salary AS (p_employee_id NUMBER, p_salary OUT NUMBER) BEGIN SELECT salary INTO p_salary FROM employees WHERE employee_id = p_employee_id; END;CREATE OR REPLACE PROCEDURE get_salary (p_employee_id NUMBER, p_salary OUT NUMBER) AS BEGIN SELECT salary INTO p_salary FROM employees WHERE employee_id = p_employee_id; END;

Stored procedures with output parameters allow the procedure to return values to the calling environment. The correct SQL statements will define the output parameter with the OUT keyword and include logic to set its value.

Q13

Multiple Choice

Determine the SQL statements that demonstrate the creation of a stored procedure with an IN OUT parameter in postgresql.

CREATE OR REPLACE PROCEDURE adjust_salary (p_salary IN OUT NUMBER) IS BEGIN p_salary := p_salary * 1.10; END; CREATE OR REPLACE PROCEDURE adjust_salary (p_salary IN OUT NUMBER) AS BEGIN p_salary := p_salary * 1.10; END; CREATE OR REPLACE PROCEDURE adjust_salary (p_salary NUMBER IN OUT) IS BEGIN p_salary := p_salary * 1.10; END;CREATE OR REPLACE PROCEDURE adjust_salary (p_salary NUMBER IN OUT) AS BEGIN p_salary := p_salary * 1.10; END;

IN OUT parameters allow the procedure to accept a value, modify it, and return the modified value to the caller. The correct SQL statements will use the IN OUT keyword in the parameter definition and modify the parameter within the procedure.

Q14

Multiple Choice

Identify the SQL statements that correctly demonstrate exception handling within a stored procedure in postgresql.

CREATE OR REPLACE PROCEDURE update_department (p_employee_id NUMBER, p_department_id NUMBER) IS BEGIN UPDATE employees SET department_id = p_department_id WHERE employee_id = p_employee_id; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END; CREATE OR REPLACE PROCEDURE update_department (p_employee_id NUMBER, p_department_id NUMBER) AS BEGIN UPDATE employees SET department_id = p_department_id WHERE employee_id = p_employee_id; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END; CREATE OR REPLACE PROCEDURE update_department (p_employee_id NUMBER, p_department_id NUMBER) AS BEGIN UPDATE employees SET department_id = p_department_id WHERE employee_id = p_employee_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END;CREATE OR REPLACE PROCEDURE update_department (p_employee_id NUMBER, p_department_id NUMBER) IS BEGIN UPDATE employees SET department_id = p_department_id WHERE employee_id = p_employee_id; IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found'); END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'An unexpected error occurred'); END;

Exception handling in postgresql allows procedures to manage errors gracefully. The correct SQL statements will include an EXCEPTION block that captures specific exceptions and raises appropriate error messages.

Q15

Multiple Choice

Choose the SQL statements that correctly create a stored procedure with a cursor in postgresql.

CREATE OR REPLACE PROCEDURE increase_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_rec IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END; CREATE OR REPLACE PROCEDURE increase_all_salaries AS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_rec IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END; CREATE OR REPLACE PROCEDURE increase_all_salaries AS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; CLOSE emp_cursor; END;CREATE OR REPLACE PROCEDURE increase_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; CLOSE emp_cursor; END;

Cursors in stored procedures allow you to fetch and process multiple rows from a result set. The correct SQL statements will declare, open, and fetch from the cursor, and then close it after processing.

Q16

Multiple Choice

Which SQL statements demonstrate the creation of an autonomous transaction in a stored procedure in postgresql?

CREATE OR REPLACE PROCEDURE log_error IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_errors (error_message, log_time) VALUES ('Error occurred', SYSDATE); COMMIT; END; CREATE OR REPLACE PROCEDURE log_error PRAGMA AUTONOMOUS_TRANSACTION IS BEGIN INSERT INTO log_errors (error_message, log_time) VALUES ('Error occurred', SYSDATE); COMMIT; END; CREATE OR REPLACE PROCEDURE log_error IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_errors (error_message, log_time) VALUES ('Error occurred', SYSDATE); COMMIT; END log_error;CREATE OR REPLACE PROCEDURE log_error AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_errors (error_message, log_time) VALUES ('Error occurred', SYSDATE); COMMIT; END;

Autonomous transactions allow a transaction within a stored procedure to commit or rollback independently of the main transaction. The correct SQL statements will include the PRAGMA AUTONOMOUS_TRANSACTION directive.

Q17

Multiple Choice

Determine the SQL statements that correctly create a stored procedure with a dynamic SQL query in postgresql.

CREATE OR REPLACE PROCEDURE delete_rows (p_table_name VARCHAR2, p_condition VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || p_table_name || ' WHERE ' || p_condition; END; CREATE OR REPLACE PROCEDURE delete_rows (p_table_name VARCHAR2, p_condition VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || p_table_name || ' WHERE ' || p_condition; END; CREATE OR REPLACE PROCEDURE delete_rows (p_table_name VARCHAR2, p_condition VARCHAR2) AS BEGIN DELETE FROM p_table_name WHERE p_condition; END;CREATE OR REPLACE PROCEDURE delete_rows IS (p_table_name VARCHAR2, p_condition VARCHAR2) BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || p_table_name || ' WHERE ' || p_condition; END;

Dynamic SQL allows the construction and execution of SQL statements dynamically at runtime. The correct SQL statements will use the EXECUTE IMMEDIATE command to run the dynamic SQL query.

Q18

Multiple Choice

Identify the SQL statements that correctly create a stored procedure with a nested procedure in postgresql.

CREATE OR REPLACE PROCEDURE calculate_bonus IS PROCEDURE calc_bonus(p_salary NUMBER, p_bonus OUT NUMBER) IS BEGIN p_bonus := p_salary * 0.10; END; BEGIN DECLARE v_salary NUMBER := 5000; DECLARE v_bonus NUMBER; calc_bonus(v_salary, v_bonus); INSERT INTO bonuses (employee_id, bonus_amount) VALUES (1, v_bonus); END; CREATE OR REPLACE PROCEDURE calculate_bonus AS PROCEDURE calc_bonus(p_salary NUMBER, p_bonus OUT NUMBER) IS BEGIN p_bonus := p_salary * 0.10; END; BEGIN DECLARE v_salary NUMBER := 5000; DECLARE v_bonus NUMBER; calc_bonus(v_salary, v_bonus); INSERT INTO bonuses (employee_id, bonus_amount) VALUES (1, v_bonus); END; CREATE OR REPLACE PROCEDURE calculate_bonus IS PROCEDURE calc_bonus(p_salary NUMBER, p_bonus OUT NUMBER) IS BEGIN p_bonus := p_salary * 0.10; END; BEGIN DECLARE v_salary NUMBER := 5000; DECLARE v_bonus NUMBER; INSERT INTO bonuses (employee_id, bonus_amount) VALUES (1, calc_bonus(v_salary)); END;CREATE OR REPLACE PROCEDURE calculate_bonus AS PROCEDURE calc_bonus(p_salary NUMBER, p_bonus OUT NUMBER) IS BEGIN p_bonus := p_salary * 0.10; END; BEGIN DECLARE v_salary NUMBER := 5000; DECLARE v_bonus NUMBER; calc_bonus(v_salary, v_bonus); INSERT INTO bonuses (employee_id, bonus_amount) VALUES (1, v_bonus); END calculate_bonus;

Nested procedures allow for modular programming by defining procedures within other procedures. The correct SQL statements will include the nested procedure within the main procedure's BEGIN and END blocks.

Q19

Multiple Choice

Choose the SQL statements that correctly create a stored procedure with a loop in postgresql.

CREATE OR REPLACE PROCEDURE update_salaries IS BEGIN FOR emp_rec IN (SELECT employee_id, salary FROM employees) LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END; CREATE OR REPLACE PROCEDURE update_salaries AS BEGIN FOR emp_rec IN (SELECT employee_id, salary FROM employees) LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END; CREATE OR REPLACE PROCEDURE update_salaries IS BEGIN WHILE emp_rec IN (SELECT employee_id, salary FROM employees) LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END;CREATE OR REPLACE PROCEDURE update_salaries AS BEGIN FOR emp_rec IN (SELECT employee_id, salary FROM employees) LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_rec.employee_id; END LOOP; END update_salaries;

Loops in stored procedures allow for iterative processing of records. The correct SQL statements will use a LOOP, WHILE, or FOR loop to iterate through the records and perform the required updates.

Q20

Multiple Choice

Select the SQL statements that demonstrate the creation of a stored procedure with conditional logic in postgresql.

CREATE OR REPLACE PROCEDURE give_bonus IS v_rating NUMBER; BEGIN SELECT performance_rating INTO v_rating FROM employees WHERE employee_id = 1; IF v_rating = 5 THEN UPDATE employees SET salary = salary * 1.20 WHERE employee_id = 1; ELSIF v_rating = 4 THEN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = 1; ELSE UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 1; END IF; END; CREATE OR REPLACE PROCEDURE give_bonus AS v_rating NUMBER; BEGIN SELECT performance_rating INTO v_rating FROM employees WHERE employee_id = 1; IF v_rating = 5 THEN UPDATE employees SET salary = salary * 1.20 WHERE employee_id = 1; ELSIF v_rating = 4 THEN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = 1; ELSE UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 1; END IF; END; CREATE OR REPLACE PROCEDURE give_bonus AS v_rating NUMBER; BEGIN SELECT performance_rating INTO v_rating FROM employees WHERE employee_id = 1; IF v_rating = 5 THEN UPDATE employees SET salary = salary * 1.20 WHERE employee_id = 1; ELSIF v_rating = 4 THEN UPDATE employees SET salary = salary * 1.10 WHERE employee_id = 1; ELSE UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 1; END IF; END give_bonus;CREATE OR REPLACE PROCEDURE give_bonus IS v_rating NUMBER; BEGIN SELECT performance_rating INTO v_rating FROM employees WHERE employee_id = 1; IF v_rating = 5 THEN UPDATE employees SET salary = salary * 1.20 WHERE employee_id = 1; ELSE UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 1; END IF; END;

Conditional logic in stored procedures allows for the implementation of business rules based on specific conditions. The correct SQL statements will use IF-THEN-ELSE blocks to check the conditions and execute the appropriate logic.

Q21

Single Choice

How would you create a stored procedure in postgresql to update the salary of an employee?

SQL Code

CREATE OR REPLACE PROCEDURE update_salary (
 emp_id IN NUMBER,
 new_salary IN NUMBER
) IS
BEGIN
 UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END;

CREATE OR REPLACE PROCEDURE update_salary (emp_id IN NUMBER, new_salary IN NUMBER) IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; END; CREATE OR REPLACE FUNCTION update_salary (emp_id IN NUMBER, new_salary IN NUMBER) IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; END; CREATE OR REPLACE TRIGGER update_salary (emp_id IN NUMBER, new_salary IN NUMBER) IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; END;CREATE OR REPLACE PACKAGE update_salary IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; END;

A stored procedure in postgresql is created using the CREATE OR REPLACE PROCEDURE statement, and it includes parameters and a PL/SQL block that performs the desired operation.

Q22

Single Choice

Which keyword is used to pass a parameter by reference in an postgresql stored procedure?

SQL Code

CREATE OR REPLACE PROCEDURE update_bonus (
 emp_id IN NUMBER,
 bonus OUT NUMBER
) IS
BEGIN
 SELECT bonus INTO bonus FROM employees WHERE employee_id = emp_id;
END;

IN OUT IN OUTRETURN

The OUT keyword is used to pass a parameter by reference, allowing the stored procedure to return a value to the calling environment.

Q23

Single Choice

How would you handle exceptions in an postgresql stored procedure?

SQL Code

CREATE OR REPLACE PROCEDURE handle_error (
 emp_id IN NUMBER
) IS
BEGIN
 UPDATE employees SET salary = salary * 1.10 WHERE employee_id = emp_id;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

WHEN OTHERS WHEN NO_DATA_FOUND WHEN TOO_MANY_ROWSWHEN VALUE_ERROR

Exceptions in an postgresql stored procedure are handled using the EXCEPTION block, where specific errors can be caught and handled.

Q24

Single Choice

What is the purpose of the RETURN clause in a stored procedure?

SQL Code

CREATE OR REPLACE FUNCTION calculate_bonus (
 emp_id IN NUMBER
) RETURN NUMBER IS
 bonus NUMBER;
BEGIN
 SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id;
 RETURN bonus;
END;

Specify the datatype of the parameter Specify the datatype of the return value in a function Indicate the end of the procedureHandle exceptions in the procedure

The RETURN clause is used in functions, not procedures, to specify the datatype of the value returned by the function.

Q25

Single Choice

How would you create a stored procedure that includes a cursor to iterate over multiple rows?

SQL Code

CREATE OR REPLACE PROCEDURE update_all_salaries IS
 CURSOR emp_cursor IS SELECT employee_id, salary FROM employees;
BEGIN
 FOR emp_record IN emp_cursor LOOP
 UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_record.employee_id;
 END LOOP;
END;

CREATE OR REPLACE PROCEDURE update_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_record.employee_id; END LOOP; END; CREATE OR REPLACE FUNCTION update_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_record.employee_id; END LOOP; END; CREATE OR REPLACE PACKAGE update_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_record.employee_id; END LOOP; END;CREATE OR REPLACE TRIGGER update_all_salaries IS CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; BEGIN FOR emp_record IN emp_cursor LOOP UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_record.employee_id; END LOOP; END;

A cursor is used in stored procedures to iterate over multiple rows returned by a query, allowing row-by-row processing.

Q26

Single Choice

How can you modify an existing stored procedure to add an additional parameter?

SQL Code

CREATE OR REPLACE PROCEDURE update_employee_details (
 emp_id IN NUMBER,
 new_name IN VARCHAR2,
 new_salary IN NUMBER
) IS
BEGIN
 UPDATE employees SET employee_name = new_name, salary = new_salary WHERE employee_id = emp_id;
END;

Use ALTER PROCEDURE Use CREATE OR REPLACE PROCEDURE Use MODIFY PROCEDUREUse DROP PROCEDURE and CREATE PROCEDURE

To modify an existing stored procedure, you use the CREATE OR REPLACE PROCEDURE statement, which allows you to redefine the procedure with additional parameters.

Q27

Single Choice

What is the purpose of using the IN OUT parameter in a stored procedure?

SQL Code

CREATE OR REPLACE PROCEDURE adjust_salary (
 emp_id IN NUMBER,
 salary_adjustment IN OUT NUMBER
) IS
BEGIN
 SELECT salary INTO salary_adjustment FROM employees WHERE employee_id = emp_id;
 salary_adjustment := salary_adjustment * 1.10;
END;

To pass a value into the procedure only To return a value from the procedure only To pass a value into the procedure and return an updated valueTo store a value within the procedure without returning it

The IN OUT parameter allows the stored procedure to both receive an initial value from the calling environment and return an updated value after processing.

Q28

Single Choice

How would you create a stored procedure to delete a record from a table based on a condition?

SQL Code

CREATE OR REPLACE PROCEDURE delete_employee (
 emp_id IN NUMBER
) IS
BEGIN
 DELETE FROM employees WHERE employee_id = emp_id;
END;

CREATE OR REPLACE PROCEDURE delete_employee (emp_id IN NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END; CREATE OR REPLACE FUNCTION delete_employee (emp_id IN NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END; CREATE OR REPLACE TRIGGER delete_employee (emp_id IN NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END;CREATE OR REPLACE PACKAGE delete_employee IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END;

A stored procedure for deleting a record is created using the DELETE statement within the PL/SQL block of the procedure, specifying the condition in the WHERE clause.

Q29

Single Choice

What is the best way to handle transactions within a stored procedure?

SQL Code

CREATE OR REPLACE PROCEDURE process_order (
 order_id IN NUMBER
) IS
BEGIN
 UPDATE orders SET status = 'Processed' WHERE order_id = order_id;
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
END;

Use COMMIT after each DML statement Use ROLLBACK after each DML statement Use COMMIT and ROLLBACK to manage transactionsUse SAVEPOINT to manage transactions

Transactions in a stored procedure are handled using the COMMIT statement to save changes and ROLLBACK to undo changes in case of errors.

Q30

Single Choice

How would you create a stored procedure that accepts multiple parameters and returns a result set?

SQL Code

CREATE OR REPLACE PROCEDURE get_employee_details (
 emp_id IN NUMBER,
 emp_name OUT VARCHAR2,
 emp_salary OUT NUMBER
) IS
BEGIN
 SELECT employee_name, salary INTO emp_name, emp_salary FROM employees WHERE employee_id = emp_id;
END;

Use a FUNCTION instead of a PROCEDURE Use OUT parameters to return the result set Use IN parameters to return the result setUse a TRIGGER to return the result set

A stored procedure that accepts multiple parameters and returns a result set uses OUT parameters to return the values retrieved by the SELECT statement.

postgresql Database Quiz Questions