Useful SQL statements in ORACLE ( 67 )

 

 

Useful commands in ORACLE
Between MySQL and Oracle are many similarities but also differences. Here is a short list containing some simple Oracle statements in which you can find these differences. One interesting particularly is that for getting last id you can use an ORACLE SEQUENCE or just a SELECT MAX statement. And a very important thing, always before table alteration, don't forget to check if SEQUENCES and TRIGGERS exist!
Examples:



// Create a trigger CREATE SEQUENCE SEQ_SOMETABLE;
// Empty table DELETE FROM SOMETABLE WHERE FIELD > 1
// Truncate TRUNCATE TABLE SOMETABLE;
// Update table values UPDATE SOMETABLE SET FIELD = 'VALUE' WHERE ID > 1
// Update data type of db field ALTER TABLE SOMETABLE MODIFY (FIELD1 char(200), FIELD2 nummer(20));
// Add new column someid ALTER TABLE SOMETABLE ADD (SOMEID char(200));
// Get next value with sequence trigger SELECT seq_SOMETABLE.NEXTVAL FROM dual;
// Get last id - alternative for next value with sequence trigger SELECT * FROM SOMETABLE WHERE SOMEID = (SELECT MAX(SOMEID) FROM SOMETABLE)
// Drop sequence trigger DROP SEQUENCE oe.SOMETABLE_seq;
// Drop trigger DROP TRIGGER hr.TABLE;
// Find duplicates SELECT FIELD, COUNT(FIELD) from SOMETABLE GROUP BY FIELD HAVING COUNT (FIELD) > 1 ORDER BY COUNT(FIELD) DESC;
// Make a join between tables SELECT * FROM TABLE1 TB1 LEFT JOIN TABLE2 TB2 ON TB1.FIELD = TB2.FIELD
// Using REGEX SELECT * FROM TABLE1 WHERE REGEXP_LIKE(FIELD,'^[0-9]{1}[a-zA-Z](.*)$')";

// GET LIMIT 20 SELECT col FROM tbl WHERE rownum<=20;
More Oracle answers can be found on these websites:
Oracle GROUP BY & HAVING Clauses http://psoug.org/reference/group_by.html
Regular expressions in Oracle http://www.adp-gmbh.ch/ora/sql/re/index.html
Oracle's explain plan http://www.adp-gmbh.ch/ora/explainplan.html
Oracle GROUP BY HAVING tips http://www.dba-oracle.com/t_oracle_group_by_having.htm
Oracle Truncate Table http://psoug.org/reference/truncate.html
Difference between TRUNCATE, DELETE and DROP commands http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands