This shows you the differences between two versions of the page.
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]] |