Apache Hive : HPL/SQL - %ROWTYPE Attribute

%ROWTYPE attribute lets you declare a record variable that has the same columns and data types as the specified database table.

Syntax:

var_name [schema.]table_name%ROWTYPE

Examples:

DECLARE 
  v orders%ROWTYPE;
BEGIN
  SELECT * INTO v FROM orders LIMIT 1;
  DBMS_OUTPUT.PUT_LINE('Item: ' || v.name || ' - ' || v.description);
END;

DECLARE 
  v orders%ROWTYPE;
  CURSOR c IS SELECT * FROM orders;
BEGIN
  OPEN c1;
  FETCH c1 INTO v1;
  DBMS_OUTPUT.PUT_LINE('Item: ' || v.name || ' - ' || v.description);
  CLOSE c1;
END;

BEGIN
  FOR v IN (SELECT * FROM orders)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Item: ' || v.name || ' - ' || v.description);
  END LOOP;
END;

DECLARE
  v orders%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM orders LIMIT 1' INTO v;
  DBMS_OUTPUT.PUT_LINE('Item: ' || v.name || ' - ' || v.description);
END;

Compatibility: Oracle

Apache Hive : HPL/SQL - %TYPE Attribute

%TYPE attribute lets you declare a variable that has the same data type as the specified referenced column.

Syntax:

var_name [schema.]table.column_name%TYPE

• If table.column_name cannot be found, the data type is derived from the first assignment expression.

Example:

DECLARE 
  i orders.item%TYPE;
BEGIN
  SELECT item INTO i FROM orders LIMIT 1;
  DBMS_OUTPUT.PUT_LINE('Item: ' || i);
END;

Compatibility: Oracle

Apache Hive : HPL/SQL - Assignment

You can use the assignment operator or statement to set a new value to variable in HPL/SQL.

If the variable was not explicitly declared before the assignment, a new variable is created and its data type is derived from the assignment expression.

Assignment Operator

Values can be set using the assignment operator := or =

Syntax:

var [:= | = ] expression;

Example:

code := 'A';
status := 1;
count = 0;

Compatibility: Oracle PL/SQL, PostgreSQL PL/pgSQL and Netezza NZPLSQL.

Apache Hive : HPL/SQL - CASE Expression

CASE expression allows you to implement IF-THEN-ELSE logic in expressions.

Syntax:

Simple CASE expression:

CASE expr
  WHEN expr THEN expr
  ...
  [ELSE expr] 
END

Searched CASE expression:

CASE 
  WHEN boolean_expr THEN expr
  ...
  [ELSE expr] 
END

Notes:

• NULL is returned if none of the WHEN expressions is matched and ELSE clause is not specified

Examples:

Simple CASE expression:

 CASE state
   WHEN 'AZ' THEN 'Arizona'
   WHEN 'CA' THEN 'California'
   ELSE 'N/A'
 END 

Searched CASE expression:

Apache Hive : HPL/SQL - Cursor Attributes

Cursor attributes allow you to get information about the current cursor state.

Syntax:

cursor_name%ISOPEN

cursor_name%FOUND

cursor_name%NOTFOUND

• cursor_name is the name of a declared cursor or cursor variable.

%ISOPEN Attribute

%ISOPEN returns true if the cursor is open, otherwise it returns false;

%FOUND Attribute

%FOUND returns NULL before the first fetch from the cursor, true if the last fetch returned a row, and false otherwise.

Apache Hive : HPL/SQL - Data Types

You can use the following data types in HPL/SQL programs:

Data Type Conversion

If CREATE TABLE contains a data type that is not supported by Hive, it is automatically converted by HPL/SQL.

Apache Hive : HPL/SQL - DATE Literal

DATE literal allows you to specify a date constant using a string in ‘YYYY-MM-DD’ format. Then you can use this date value in any expression that expects a DATE data type.

Examples:

DATE '2014-12-20'
DATE '2014-12-20' + 1    -- Result: 2014-12-21 of type DATE
DATE '2014-12-20' - 1    --         2014-12-19 

Compatibility: Oracle, IBM DB2 and Teradata

See also:

• TIMESTAMP Literal
• INTERVAL Expressions

Apache Hive : HPL/SQL - Declarations

You can declare variables using DECLARE block or statement.

Note that you can mix both syntaxes in a single program. DECLARE blocks and statements can appear in any part of the program.

DECLARE Block

DECLARE block has the following syntax:

DECLARE 
  var datatype [NOT NULL] [:= | = | DEFAULT expression];
  ...
BEGIN
  ...
END;

HPL/SQL also allows you to define a constant:

  var CONSTANT datatype := | DEFAULT expression

Example:

Apache Hive : HPL/SQL - Exceptions

HPL/SQL allows you to handle exceptions in your programs.

Syntax:

BEGIN
  -- Statements that can raise an exception
EXCEPTION
  WHEN condition THEN 
    -- Statements
  WHEN condition2 THEN
    -- Statements2
    ...
END

Example:

DECLARE 
  v VARCHAR(200);
BEGIN
  OPEN cur FOR 'SELECT c1 FROM t1';
  FETCH cur INTO v;
  CLOSE cur;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error');
END

Compatibility: Oracle, PostgreSQL and Netezza.

Apache Hive : HPL/SQL - Interval Expressions

Interval expressions allow you to add or subtract interval values to DATE and TIMESTAMP values.

Syntax:

[INTERVAL] expression DAYS | DAY | MICROSECONDS | MICROSECOND  

Notes:

• MICROSECOND expressions are converted to millisecond expressions due to Java limitations.

Example 1:

Add 1 day to DATE and TIMESTAMP values:

DATE '2015-03-12' + 1 DAY;
--
2015-03-13

TIMESTAMP '2015-03-12' + 1 DAY;
--
2015-03-13 00:00:00

Example 2:

Add the result of expression to to DATE and TIMESTAMP values:

Apache Hive : HPL/SQL - TIMESTAMP Literal

TIMESTAMP literal allows you to specify a timestamp constant using a string in ‘YYYY-MM-DD HH:MI:SS.FFF’ or ‘YYYY-MM-DD-HH.MI.SS.FFF’ format.

You can use this timestamp value in any expression that expects a TIMESTAMP data type.

Examples:

TIMESTAMP '2015-03-03 11:39:31.123'

TIMESTAMP '2015-03-03-11.39.31.123'   -- DB2 syntax

Notes:

• Fractional part is optional

Compatibility: Oracle, IBM DB2.

See also:

• DATE Literal
• INTERVAL Expressions
• TIMESTAMP_ISO Function
• TO_TIMESTAMP Function