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

Oracle Database Administration for Microsoft SQL Server DBAs part 27 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 | 242 Oracle Database Administration for Microsoft SQL Server DBAs The SQL Developer tool provides a way to develop unit test and handle version control. In SQL Developer you can set up basic frameworks for the database objects. Statements generated by SQL Developer when creating new object Create procedure with two parameters passed in CREATE PROCEDURE EXAMPLE_PROC1 PARAM1 IN VARCHAR2 PARAM2 IN NUMBER AS BEGIN NULL END EXAMPLE_PROC1 Create trigger on insert CREATE TRIGGER EXAMPLE_TRIGGER1 BEFORE INSERT ON EMP REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW WHEN DEPTNO 10 BEGIN NULL END As noted in Table 9-1 a semicolon is the delimiter that marks the end of the block. The forward slash says to execute the code in SQL Plus. NOTE SQL Developer and some of the other tools have ways to execute statements with a run or run script statement which will run the code without the forward slash. However in SQL Plus the is required to have the code run like saying Go. In SQL Server you get an identity column but Oracle doesn t have an identity type. Insert triggers are useful for generating IDs for primary keys. Using a sequence you can retrieve the next value for a unique number to be used as an ID. You could also use a procedure for inserts to pull in the Chapter 9 PL SQL 243 next value from the sequence without a trigger. This could be used on every insert as long as the application does not rely on ad hoc queries for inserts. f Trigger for sequences and populating identity column create sequence order_id_seq start with 1 increment 1 create or replace trigger trg_i_orders before insert for each row begin select into from dual end You cannot use a role to grant permissions to the objects in a procedure that the procedure owner is using in that code. Permissions for the objects that are being accessed and used in the code must be explicitly granted to the procedure owner. Also worth mentioning is a difference in create and replace operations .