Monday, July 4, 2016

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()

Thursday, April 28, 2016

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

Monday, March 28, 2016

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

Thursday, March 17, 2016

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

Wednesday, February 3, 2016

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)
   


Monday, February 1, 2016

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