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.