SQL Statement Changes

Sybase IQ 15.4 includes changes to Interactive SQL.

ALTER PROCEDURE Statement – The ALTER PROCEDURE statement syntax has been modified to support table UDFs and Java table UDFs.
```
ALTER PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[  RESULT (result-column, ...)]
EXTERNAL NAME ‘external-call’ [ LANGUAGE environment-name ] }
```
See: Reference: Statements and Options > SQL Statements > ALTER PROCEDURE Statement
.
CREATE PROCEDURE Statement – The CREATE PROCEDURE statement syntax has been modified to support TPFs.
parameter: [ IN ] parameter-name data-type [ DEFAULT expression ] | [ IN ] parameter-name table-type

table type: TABLE( column-name data-type [, ...] )

external call: [column-name:]function-name@library; ...

See Reference: Statements and Options > SQL Statements > CREATE PROCEDURE Statement (External Procedures) > CREATE PROCEDURE Statement (Table UDF).
CREATE FUNCTION Statement – The CREATE FUNCTION external-name parameter has been modified to support Java scalar UDFs.
external-name: EXTERNAL NAME library-call | EXTERNAL NAME java-call LANGUAGE JAVA

See Reference: Statements and Options > SQL Statements > CREATE FUNCTION statement.

FROM clause – The FROM table-expression, ... syntax has been enhanced to support table UDFs.

procedure-name :
	[  owner, ]  procedure-name ([  parameter, ...])
	[  WITH( column-name datatype,)]
	[ [   AS] correlation-name ]
	parameter :
	table-expression | table-parameter
		table-parameter :
		     TABLE(table-expression) [ OVER (table-parameter-over)]
		table-parameter-over :
		     [ PARTITION BY {ANY| NONE| table-expression } ] 
       [ ORDER BY { expression | integer } [ ASC | DESC ] [, …] ]

See Reference: Statements and Options > SQL Statements > FROM Clause.

BEGIN, CREATE VARIABLE, and DECLARE statement variable declarations – These declarations can now include an initial starting value for the variable. See Reference: Statements and Options > SQL Statements.
CREATE SEQUENCE statement – The CREATE SEQUENCE statement is new in this release. This statement is only valid for catalog store tables. The values generated by the sequence generator cannot be used for queries against IQ tables.
See the following topic in the Sybase IQ 15.4 documentation:
- Reference: Statements and Options > SQL Statements > CREATE SEQUENCE Statement
INSERT statement – INSERT [INTO] statements against catalog store tables now support multiple lists of values. For example:
```
INSERT INTO T (c1,c2,c3)
VALUES (1,10,100), (2,20,200), (3,30,300);
```
This enhancement does not apply to IQ tables. If used against an IQ table, the above syntax results in an error message.

INSERT [INTO] statements against catalog store tables now permit you to insert rows with all default values. For example:
```
INSERT INTO T ()
VALUES (), (), ();
```
This enhancement does not apply to IQ tables. If used against an IQ table, the above syntax results in an error message.
See the following topic in the Sybase IQ 15.4 documentation:
- Reference: Statements and Options > SQL Statements > INSERT Statement.
New OR REPLACE clause – The new OR REPLACE clause allows you to create or replace a profile or variable of the same name. See these topics in Reference: Statements and Options > SQL Statements:
- CREATE FUNCTION Statement
- CREATE PROCEDURE Statement
- CREATE PROCEDURE Statement [T-SQL]
- CREATE VARIABLE statement
- CREATE VIEW statement
New LIMIT clause support for SELECT statements – Use the new LIMIT clause to specify row counts and offsets in a SELECT statement.
See the LIMIT clause in Reference: Statements and Options > SQL Statements Statement > SELECT Statement.

Embedded SQL cursor behavior change – Embedded SQL cursors now default to READ ONLY. Explicit FOR READ ONLY or FOR UPDATE clauses must now be specified in the PREPARE statement and not the DECLARE statement. See:
- Reference: Statements and Options > SQL Statements Statement > DECLARE CURSOR Statement [T-SQL]
- Reference: Statements and Options > SQL Statements Statement > PREPARE Statement [ESQL]