Updating salary for different departments using cursor
Until a SQL data manipulation statement is executed, CREATE TABLE dept_temp AS SELECT * FROM departments; DECLARE dept_no NUMBER(4) := 270; BEGIN DELETE FROM dept_temp WHERE department_id = dept_no; IF SQL%FOUND THEN -- delete succeeded INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700); END IF; END; / CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE mgr_no NUMBER(6) := 122; BEGIN DELETE FROM employees_temp WHERE manager_id = mgr_no; DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT)); END; / When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
Just given above example for illustration purpose only. Multiple record and single column declare v_ename varchar2(20); Cursor Cur_ename is select empname from emp; begin Open Cur_ename; loop Fetch Cur_ename into v_ename; exit when Cur_ename%notfound; dbms_output.put_line(‘Employee Name –Paul This is a classic example.CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE; emp_first_name employees_temp.first_name%TYPE; emp_last_name employees_temp.last_name%TYPE; BEGIN INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry'); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299; DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.PUT_LINE( emp_first_name || ' ' || emp_last_name); END; / CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees; DECLARE x VARCHAR2(20) := 'my_first_name'; y VARCHAR2(25) := 'my_last_name'; BEGIN INSERT INTO employees_temp VALUES(x, y); UPDATE employees_temp SET last_name = x WHERE first_name = y; DELETE FROM employees_temp WHERE first_name = x; COMMIT; END; / statement that is explained in "Using the EXECUTE IMMEDIATE Statement in PL/SQL".In addition to static SQL discussed in this chapter, PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.See Chapter 7, "Performing SQL Operations with Native Dynamic SQL".You might use sequence numbers to give each row a unique identifier, and refer to those identifiers from other rows to set up parent-child relationships. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row.
If you want precise control over query processing, you can declare an explicit cursor in the declarative part of any PL/SQL block, subprogram, or package.
However, you can track information about the execution of an implicit cursor through its cursor attributes. For more information, see "Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute".
%FOUND Attribute: Has a DML Statement Changed Rows?
For example, two statements might credit one bank account and debit another.
It is important not to allow one operation to succeed while the other fails.
At the end of a transaction that makes database changes, Oracle makes all the changes permanent or undoes them all.