assign

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

assign [2015/09/23 20:26]
assign [2015/09/23 20:26] (current)
Line 1: Line 1:
 +====== 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:
 +
 +<code language=sql>​
 +var [:= | = ] expression;
 +</​code>​
 +
 +Example:
 +
 +<​code>​
 +code := '​A';​
 +status := 1;
 +count = 0;
 +</​code>​
 +
 +**Compatibility:​** Oracle PL/SQL, PostgreSQL PL/pgSQL and Netezza NZPLSQL.
 +
 +**Version**:​ PL/HQL 0.01
 +
 +===== Assignment Statement =====
 +
 +You can also use the SET statement to assign value to variables.
 +
 +Syntax:
 +
 +<code language=sql>​
 +SET var = expression [, ...];
 +|
 +SET (var [, var2, ...]) = (expression [, expression2,​ ...])
 +</​code>​
 +
 +Example:
 +
 +<​code>​
 +SET code = '​A';​
 +SET status = 1, count = 0;
 +SET (count, limit) = (0, 100);
 +</​code>​
 +
 +**Compatibility:​** IBM DB2, Teradata, Microsoft SQL Server and MySQL.
 +
 +**Version**:​ PL/HQL 0.01
 +
 +===== Assignment From a SELECT Statement =====
 +
 +You can also use the SET statement to assign value from the first row of a query result:
 +
 +Syntax:
 +
 +<code language=sql>​
 +SET var = (SELECT col FROM ...);
 +|
 +SET (var [, var2, ...]) = (SELECT col [, col2, ... ] FROM ...);
 +|
 +SELECT var = col [, var2 = col2, ...] FROM ...  -- since HPL/SQL 0.3.11
 +</​code>​
 +
 +Example:
 +
 +<​code>​
 +SET code = (SELECT code FROM conf WHERE name = '​A'​);​
 +SET (count, limit) = (SELECT count, limit FROM conf WHERE name = '​A'​);​
 +SELECT @count = count, @limit = limit FROM conf WHERE name = '​A';​
 +</​code>​
 +
 +**Compatibility:​** IBM DB2, Teradata, Microsoft SQL Server and MySQL.
 +
 +**Version**:​ PL/HQL 0.3.7
 +
 +See also:
 +  * [[select-into|SELECT INTO]]
 +  * [[values-into|VALUES INTO]]