MySQL date and time functions

MySQL date and time functions
ADDDATE()
ADDTIME()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_ TIMESTAMP()
CURTIME()
DATE_ADD()
DATE_FORMAT()
DATE_SUB()
DATE()
DATEDIFF()
DAY()
DAYNAME()
DAY OF MONTH()
DAY OF WEEK()
DAY OF YEAR()
EXTRACT()
FROM_DAYS()
FROM_UNIXTIME()
GET_FORMAT()
HOUR()
LAST_DAY()
LOCALTIME()
LOCALTIMESTAMP()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
NOW()
PERIOD_ADD()
PERIOD_DIFF()
QUARTER()
SEC_TO_TIME()
SECOND()
STR_TO_DATE()
SUBDATE()
SUBTIME()
SYSDATE()
TIME_FORMAT()
TIME_TO_SEC()
TIME()
TIMEDIFF()
TIMESTAMP()
TIMESTAMPADD()
TIMESTAMPDIFF()
TO_DAYS()
UNIX_TIMESTAMP()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
WEEK()
WEEKDAY()
WEEK OF YEAR()
YEAR()
YEARWEEK()

MySQL Functions

1)  The MySQL LAST_INSERT_ID function to get the last insert id that MySQL has been generated.
2)  STR_TO_DATE() function to convert strings into a date and/or time values.

   SELECT STR_TO_DATE('20130101 113012','%Y%m%d %h%i%s') ;

      2013-01-01 11:30:12

How to create dynamic query stored procedure in mysql

Ref Cursors Oracle

Ref Cursors Oracle

Example :-

Declare
TYPE honni IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  honni;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

REF CURSOR can be categorized into three

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE honni IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here honni is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE honni IS REF CURSOR;
…..
End;

Here honni is a Weak Ref Cursor

3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
honni SYS_REFCURSOR;
…..
End;

Advantages
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )

Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values

In general Ref Cursors are only be used when static cursor cannot do the work

How to change the table name during import from impdp oracle

The following is an example of using the REMAP_TABLE parameter to rename the emp table to a new name of emp1:


impdp honnikery/honnikery directory=dpump_dir1 dumpfile=honni.dmp
tables=honnikery.emp remap_table=honnikery.emp:emp1  table_exists_action =append

full-text search mysql 5.6

Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
Full-Text index is an index type of FULLTEXT


Basic syntax of  full-text search function is:
MATCH(col1, col2, col3, ….) AGAINST (search_expr [search_modifier])


There are three different types of full-text searches available in MySQL

Boolean Search
Natural Language Search
Query Expansion Search


mysql> CREATE TABLE honnikery (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      name VARCHAR(200),
      description TEXT,
      FULLTEXT (name,description)
    ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)






INSERT INTO honnikery (name,description) VALUES
    ('prabhakar ','bidar to bangalore'),
    ('Naveen','used only with InnoDB or MyISAM tables '),
    (' MySQL','searches available in MySQL'),
    (' Tricks','tables AUTO_INCREMENT NOT NULL PRIMARY KEY');


mysql> SELECT * FROM honnikery
    WHERE MATCH (name,description)
    AGAINST ('tables' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | name             | description                                     |
+----+-------------------+------------------------------------------+
|  2 | Naveen            | used only with InnoDB or MyISAM tables            |
|  4 | Tricks            | tables AUTO_INCREMENT NOT NULL PRIMARY KEY |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
   

Procedures, Functions and Packages example

CREATE OR REPLACE PROCEDURE

CREATE OR REPLACE PROCEDURE display_numbers (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER)
AS
BEGIN
  FOR i IN p_lower .. p_upper LOOP
    DBMS_OUTPUT.put_line(i);
  END LOOP;
END;
/

SET SERVEROUTPUT ON
EXECUTE display_numbers(2, 6);
2
3
4
5
6

PL/SQL procedure successfully completed.


CREATE OR REPLACE FUNCTION

CREATE OR REPLACE FUNCTION difference (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER)
  RETURN NUMBER
AS
BEGIN
  RETURN p_upper - p_lower;
END;
/

VARIABLE l_result NUMBER
BEGIN
  :l_result := difference(2, 6);
END;
/

PL/SQL procedure successfully completed.

PRINT l_result

  L_RESULT
----------
         4

CREATE OR REPLACE PACKAGE

CREATE OR REPLACE PACKAGE my_package AS

PROCEDURE display_numbers (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER);
 
FUNCTION difference (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER)
  RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY my_package AS

PROCEDURE display_numbers (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER)
AS
BEGIN
  FOR i IN p_lower .. p_upper LOOP
    DBMS_OUTPUT.put_line(i);
  END LOOP;
END;
 
FUNCTION difference (
  p_lower  IN   NUMBER,
  p_upper  IN   NUMBER)
  RETURN NUMBER
AS
BEGIN
  RETURN p_upper - p_lower;
END;

END;
/


OUT PUT

SET SERVEROUTPUT ON

EXECUTE my_package.display_numbers(2, 6);
2
3
4
5
6

PL/SQL procedure successfully completed.

VARIABLE l_result NUMBER
BEGIN
  :l_result := my_package.difference(2, 6);
END;
/

PL/SQL procedure successfully completed.

PRINT l_result

  L_RESULT
----------
         4

Cursors using the %TYPE and %ROWTYPE in oracle

DECLARE
  -- Specific column from table.
  l_username  all_users.username%TYPE;
 
  -- Whole record from table.
  l_all_users_row  all_users%ROWTYPE;
 
  CURSOR c_user_data IS
    SELECT username,
           created
    FROM   all_users
    WHERE  username = 'SYS';
   
  -- Record that matches cursor definition.
  l_all_users_cursor_row  c_user_data%ROWTYPE;
BEGIN
  -- Specific column from table.
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  username = 'SYS';
 
  DBMS_OUTPUT.put_line('l_username=' || l_username);
 
  -- Whole record from table.
  SELECT *
  INTO   l_all_users_row
  FROM   all_users
  WHERE  username = 'SYS';

  DBMS_OUTPUT.put_line('l_all_users_row.username=' ||
                        l_all_users_row.username);
  DBMS_OUTPUT.put_line('l_all_users_row.user_id=' ||
                        l_all_users_row.user_id);
  DBMS_OUTPUT.put_line('l_all_users_row.created=' ||
                        l_all_users_row.created);
 
  -- Record that matches cursor definition.
  OPEN  c_user_data;
  FETCH c_user_data
  INTO  l_all_users_cursor_row;
  CLOSE c_user_data;

  DBMS_OUTPUT.put_line('l_all_users_cursor_row.username=' ||
                        l_all_users_cursor_row.username);
  DBMS_OUTPUT.put_line('l_all_users_cursor_row.created=' ||
                        l_all_users_cursor_row.created);
END;
/
l_username=SYS
l_all_users_row.username=SYS
l_all_users_row.user_id=0
l_all_users_row.created=18-JAN-2016 08:02:17
l_all_users_cursor_row.username=SYS
l_all_users_cursor_row.created=18-JAN-2016 08:02:17

PL/SQL procedure successfully completed.

What's the difference between MyISAM and InnoDB?

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB



MySQL and Storage Engines
MyISAM and InnoDB are two popular storage engines. A storage engine (or "database engine") is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

There are other engines available, namely CSV, MRGMYISAM, BLACKHOLE, MEMORY, ARCHIVE and PERFORMANCESCHEMA, each are more suited in certain situations more than others - there is no one engine that is best at everything.

Using multiple storage engines
But you do not have to choose one engine over another for the entire schema, it is perfectly valid to choose one engine for one table, and another engine for a different table, all within the same schema. Be careful in writing your application, however, as different engines will accept different operations.

Also, if you are hosting tables using different storage engines on the same server, the server’s memory resources will now have to be split between MyISAM and InnoDB

MyISAM vs InnoDB
Today we will focus on MyISAM vs InnoDB. The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.

Referential Integrity
Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.

InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.

Transactions & Atomicity
Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.

MyISAM do not support transactions whereas InnoDB does.

If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.

If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.

Table-locking vs Row-locking
When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.

Transactions & Rollbacks
When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT.
1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made
2. ROLLBACK - you can discard any operations that have not yet been committed yet
3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to

Reliability
MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.

InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the reply of those logs.

FULLTEXT Indexing
InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.

However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.

Conclusion
In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.

UTL_RAW Functions in Oracle with Syntax

UTL_RAW.TO_RAW


Syntax
result := PGM_UTL.TO_RAW(input);


UTL_RAW.BIT_AND

Syntax
result := UTL_RAW.BIT_AND(input1, input2);


UTL_RAW.BIT_COMPLEMENT

Syntax
result := UTL_RAW.BIT_COMPLEMENT(input);


UTL_RAW.BIT_OR


Syntax
result := UTL_RAW.BIT_OR(input1, input2);

UTL_RAW.BIT_XOR


Syntax
result := UTL_RAW.BIT_XOR(input1, input2);


UTL_RAW.CAST_TO_RAW


Syntax
result := UTL_RAW.CAST_TO_RAW(input);


UTL_RAW.CAST_TO_VARCHAR2

Syntax
result := UTL_RAW.CAST_TO_VARCHAR2(input);

UTL_RAW.COMPARE

Syntax
result := UTL_RAW.COMPARE(input1, input2[, pad]);


UTL_RAW.CONCAT


Syntax
result := UTL_RAW.CONCAT(input1, ...  input12);


UTL_RAW.CONVERT


Syntax
result := UTL_RAW.CONVERT(input, new_charset, old_charset);

UTL_RAW.COPIES

Syntax
result := UTL_RAW.COPIES(input, number);

UTL_RAW.LENGTH 

Syntax
result := UTL_RAW.LENGTH(input);


UTL_RAW.OVERLAY

Syntax
result := UTL_RAW.OVERLAY(new_bytes, input, position, length, pad);


UTL_RAW.REVERSE

Syntax
result := UTL_RAW.REVERSE(input);

UTL_RAW.SUBSTR


Syntax
result := UTL_RAW.SUBSTR(input, position[,length]);


UTL_RAW.TRANSLATE

Syntax
result := UTL_RAW.TRANSLATE(input, match, replace_bytes);


UTL_RAW.TRANSLITERATE


Syntax
result := UTL_RAW.TRANSLITERATE (input, replace_bytes, match, pad);


UTL_RAW.XRANGE

Syntax
result := UTL_RAW.XRANGE(start, end);

Sails Oracle Adapter Installation