Cursor variables, using PL/SQL functions, record updates and shortcuts

Cursor variables, using PL/SQL functions, record updates and shortcuts

Cursor variables: In PL/SQL, a cursor variable is a variable that stores a pointer to a cursor, rather than storing the result set itself. This allows us to pass a cursor between different PL/SQL blocks or functions, and allows us to write more flexible and modular code. Here’s an example of declaring and using a cursor variable:

DECLARE
   TYPE emp_cursor IS REF CURSOR;
   emp_cur emp_cursor;
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cur FOR SELECT * FROM employees;
   LOOP
      FETCH emp_cur INTO emp_rec;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
   END LOOP;
   CLOSE emp_cur;
END;

In the example above, we declared a cursor variable named emp_cur, which points to a cursor that selects all the rows from the employees table. We then used a loop to fetch each row from the cursor and display the first name and last name of each employee.

Using PL/SQL functions

In PL/SQL, a function is a block of code that returns a value. Functions can be called from other PL/SQL blocks or from SQL statements. Here’s an example of defining and using a function:

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

In the example above, we defined a function named calc_salary that takes an employee ID as a parameter and returns the employee’s salary multiplied by 1.1. We can then call this function from other PL/SQL blocks or SQL statements.

Record updates: In PL/SQL, we can update a record in a table using the UPDATE statement. Here’s an example of updating a record in the employees table:

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;

In the example above, we updated the salary of the employee with ID 100 by multiplying it by 1.1.

Shortcuts in PL/SQL: PL/SQL provides several shortcuts to make coding more efficient. Here are a few examples:

• %ROWTYPE: This is a shortcut for defining a variable that has the same structure as a table. For example:

DECLARE
   emp_rec employees%ROWTYPE;
BEGIN
   -- Code to use the emp_rec variable
END;

In this example, the emp_rec variable is defined with the same structure as the employees table.

• %TYPE: This is a shortcut for defining a variable that has the same data type as a column. For example:

DECLARE
   emp_name employees.first_name%TYPE;
BEGIN
   -- Code to use the emp_name variable
END;

In this example, the emp_name variable is defined with the same data type as the first_name column in the employees table.

• :=: This is a shortcut for assigning a value to a variable. For example:

DECLARE
   emp_name VARCHAR2(50) := 'John';
BEGIN
   -- Code to use the emp_name variable
END;

In this example, we assigned the value ‘John’ to the emp_name variable using the := shortcut.