Does a synonym have an object number Can a synonym be created on an object in a

Does a synonym have an object number can a synonym be

This preview shows page 10 - 14 out of 27 pages.

Does a synonym have an object number? Can a synonym be created on an object in a package? No(BTW what is a package?)1 WHERE ; 6
Image of page 10
Use the CREATE PACKAGEstatement to create the specification for a storedpackage, which is an encapsulated collection of related procedures,functions, and other program objects stored together in the database. ORACLE Statement Categories DML - Data Manipulation Language SELECT Displays data of a database table or view INSERT Adds data to a database table, either directly or, in some situations, through a view UPDATE Modifies existing data in a table, either directly or, in some situations, through a view. ( UPDATE ... SET ... WHERE...; ) ~> there is no FROM in UPDATE . DELETE Removes existing data from a table, either directly or, in some situations, through a view MERGE Performs a combination of INSERT, UPDATE, and DELETE statements in a single statement DDL - Data Definition Language < Ä ( THESE CAUSE AN IMPLICIT COMMIT ! ) CREATE ALTER DROP RENAME ÅÇ> RENAME <old name> TO <new name>; where the OBJECT is an existing table, view, sequence, or private synonym IN YOUR SCHEMA. TRUNCATE GRANT REVOKE FLASHBACK PURGE Note : PURGE is used with DROP and NOT with TRUNCATE when clearing the recycle bin after a table is dropped. COMMENT OCI - See OCI article Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use function calls to access an Oracle database and control all phases of SQL statement execution and data access. DBA - DBA_STATEMENTS describes SQL statements in stored PL/SQL objects accessible to SYS . TCL - Transaction Control Language COMMIT Saves a set of DML modifications performed in the current database session ROLLBACK Undoes a set of DML modifications performed during the current database session ( DOES NOT CAUSE IMPLICIT COMMIT ) SAVEPOINT Marks a position in a session to prepare for a future ROLLBACK to
Image of page 11
enable that ROLLBACK to restore data at a selected point in a session other than the most recent commit event FUNCTIONS INSTR(COLUMN,'a',5,3) -- Get this correct! Locate 'a' beginning at postion 5 and then return the position of the 3rd occurrence of character 'a' DECODE(COLUMN,val1,IFval1,val2,IFval2,AllElse) ~> This works like the CASE statement but CASE is better performing for the Query Optimizer. INSTR() and SUBSTR() Functions: Another INSTR() example: /* Display Values of CUST_NAME where the last name starts with Mc or MC: */ CUST_NAME --------------- Samuel McCain Allan MCEwen Good: WHERE SUBSTR(cust_name, INSTR(cust_name, '' )+ 1 ) LIKE 'Mc%' WHERE SUBSTR(cust_name, INSTR(cust_name, '' )+ 1 ) LIKE 'MC%' OR 'Mc%' Not good: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, '' )+ 1 )) IN ( 'MC%' , 'Mc%' ) WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, '' )+ 1 )) LIKE UPPER( 'MC%' ) ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SQL> select instr(substr(decode( 6 /3* 2 , 4 , 'abcabcabc' , 1 , 'bcabcabca' , 'cabcabcab' ), 9 ), 'c' ) from DUAL; -- The "answer" INSTR(SUBSTR(DECODE( 6 /3* 2 , 4 , 'ABCABCABC' , 1 , 'BCABCABCA' , 'CABCABCAB' ), 9 ), 'C' ) ---------------------------------------------------------------------- ---- 1 2 3 4 5
Image of page 12
MORE PHunction PHUN
Image of page 13
Image of page 14

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture