tailieunhanh - Oracle Database Administration for Microsoft SQL Server DBAs part 28

Oracle Database Administration for Microsoft SQL Server DBAs part 28 takes the administration topics with which the SQL Server DBA is familiar, translates them into Oracle terms, and then expands on Oracle functionality. Definitions and comparative terms run throughout the book so the SQL Server DBA can easily leverage existing knowledge. This Oracle Press guide also expands on some of the features in Oracle that do not match up directly with SQL Server, and looks at other processes often performed on an Oracle database that would not typically be a standard practice in SQL Server environments | 252 Oracle Database Administration for Microsoft SQL Server DBAs EMPNAME EMPDEPT Mandy Emily Gabrielle 10 20 50 SQLPLUS rollback Rollback complete. SQLPLUS select from emp no rows selected This example uses an anonymous block of code rather than a stored procedure. If you were to put this statement in a stored procedure after executing the stored procedure if you did not have the commits in the stored procedure you could still roll back after the execution of the procedure. SQLPLUS create procedure INS_EMP as begin insert into emp values Mandy 10 insert into emp values Emily 20 savepoint before_delete delete from emp where emp_dept 20 end Procedure created. SQLPLUS select from emp no rows selected SQLPLUS exec INS_EMP PL SQL procedure successfully completed. SQLPLUS select from emp EMPNAME EMPDEPT Mandy 10 SQLPLUS rollback to before_delete Rollback complete. SQLPLUS select from emp EMPNAME EMPDEPT Mandy 10 Emily 20 SQLPLUS commit Commit complete. SQLPLUS rollback Chapter 9 PL SQL 253 Rollback complete. Rollback ineffective because commit already done. SQLPLUS select from emp EMPNAME EMPDEPT Mandy Emily 10 20 Add commit to stored procedure SQLPLUS create or replace procedure INS_EMP as begin insert into emp values Mandy 10 insert into emp values Emily 20 savepoint before_delete delete from emp where emp_dept 20 commit end Procedure created SQLPLUS exec ins_emp PL SQL procedure successfully completed. SQLPLUS select from emp EMPNAME EMPDEPT Mandy 10 commit part of the stored procedure so rollback to a savepoint will error out SQLPLUS rollback to before_delete rollback to before_delete ERROR at line 1 ORA-01086 savepoint BEFORE_DELETE never established As you can see from the examples in the same session without a commit rollbacks are possible to the beginning of the statement or to the savepoints. Defining Commits With the transaction savepoints in place you now need to confirm the changes and commit them. The transaction size is important as noted earlier. You do .