Stored procedure insert not updating database
If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction, restoring the database to its former state. However, there are limitations on the use of pseudocolumns, including the restriction on the use of some pseudocolumns in assignments or conditional tests.For additional information, including restrictions, on the use of SQL pseudocolumns, see value of a sequence, the sequence is incremented immediately and permanently, whether you commit or roll back the transaction.See Chapter 7, "Performing SQL Operations with Native Dynamic SQL".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.As the following example shows, you can initialize cursor parameters to default values.
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.Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors.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?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.