Progress
SQL-92
Guide and Reference


Statements, DDL and DML Syntax in BNF

This sections lists Progress SQL-92 Data Definition Language (DDL) and Data Manipulation Language (DML) statements in Backus-Naur Form (BNF).

ALTER USER Statement

SYNTAX
alter user statement ::=
ALTER USER ’username’, ’old_password’, ’new_password’ ; 

BEGIN-END DECLARE SECTION

SYNTAX
begin declare section ::=
EXEC SQL BEGIN DECLARE SECTION
host_lang_type variable_name ;
  .
  .
  .
END DECLARE SECTION ::=
EXEC SQL END DECLARE SECTION 

Host Language Type

SYNTAX
host language type ::=
{   char
  | short 
  | long
  | float
  | double
} 

CALL Statement

SYNTAX
call statement ::=
CALL proc_name ( [ parameter ] [ , ...] ) ; 

CLOSE Statement

SYNTAX
close ::=
EXEC SQL CLOSE cursor_name ; 

COMMIT Statement

SYNTAX
commit statement ::=
COMMIT [ WORK ] ; 

CONNECT Statement

SYNTAX
connect statement ::=
CONNECT TO connect_string 
  [ AS connection_name ]
  [ USER user_name ]
  [ USING password ] ; 

CONNECT STRING Statement

SYNTAX
connect_string ::=
{ DEFAULT | db_name
  | db_type:T:host_name:port_num:db_name } 

CREATE INDEX Statement

SYNTAX
create index statement ::=
CREATE [ UNIQUE ] INDEX index_name
  ON table_name
  ( { column_name [ ASC | DESC ] } [, ... ] )
  [ AREA area_name ] ; 

CREATE PROCEDURE Statement

SYNTAX
create procedure statement ::=
CREATE PROCEDURE [ owner_name.]procname
  ( [ parameter_decl [ , ...... ] ]
  )
    [ RESULT ( column_name data_type [ , ... ] ) ]
    [ IMPORT
      java_import_clause ]
  BEGIN
      java_snippet
  END 

Parameter Declaration

SYNTAX
parameter_decl ::=
{ IN | OUT | INOUT } parameter_name data_type 

CREATE SYNONYM Statement

SYNTAX
create synonym statement ::=
CREATE [ PUBLIC ] SYNONYM synonym
  FOR [ owner_name.] {table_name |view_name |synonym } ; 

CREATE TABLE Statement

SYNTAX
create table statement ::=
CREATE TABLE [ owner_name.]table_name
  ( { column_definition | table_constraint }, ... ) 
    [ AREA area_name ] 
;

create table statement ::=
CREATE TABLE [ owner_name.]table_name
  [ (column_name [ NULL | NOT NULL] , ...) ]
  [ AREA area_name ]
  AS query_expression 
; 

Column Definition

SYNTAX
column_definition ::=
column_name data_type
  [ DEFAULT { literal | NULL | SYSDATE } ]
  [ column_constraint [ column_constraint ... ] ] 

Column Constraint

SYNTAX
column_constraint ::=
[ CONSTRAINT constraint_name ]
   NOT NULL [ PRIMARY KEY | UNIQUE ]
  | REFERENCES [ owner_name.]table_name [ ( column_name ) ]
  | CHECK ( search_condition ) 

Tabel Constraint

SYNTAX
table_constraint ::=
[ CONSTRAINT constraint_name ]
    PRIMARY KEY ( column [, ... ] )
  | UNIQUE ( column [, ...... ] )
  | FOREIGN KEY ( column [, ... ] )
    REFERENCES [ owner_name.]table_name [ ( column [, ... ] ) ]
  | CHECK ( search_condition ) 

CREATE TRIGGER Statement

SYNTAX
create trigger statement ::=
CREATE TRIGGER [ owner_name.]trigname
  { BEFORE | AFTER } 
  { INSERT | DELETE | UPDATE [ OF column_name [ , ... ] }
  ON table_name
  [ REFERENCING { OLDROW [, NEWROW ] | NEWROW [, OLDROW ] } ]
  [ FOR EACH { ROW | STATEMENT } ]
  [ IMPORT  
    java_import_clause ] 
    BEGIN
          java_snippet  
    END 

CREATE USER Statement

SYNTAX
create user statement ::=
CREATE USER ’username’, ’password’ ; 

CREATE VIEW Statement

SYNTAX
create view statement ::=
CREATE VIEW [ owner_name.]view_name
  [ ( column_name, column_name,... ) ]
  AS [ ( ] query_expression [ ) ]
  [ WITH CHECK OPTION ] ; 

DECLARE CURSOR Statement

SYNTAX
declare cursor ::=
EXEC SQL DECLARE cursor_name CURSOR FOR
  { query_expr [ ORDER BY clause ] [ FOR UPDATE clause ]
  | prepared_statement_name
  } ; 

DELETE Statement

SYNTAX
delete statement ::=
DELETE FROM [ owner_name.] {table_name |view_name }
  [ WHERE search_condition ] ; 

DESCRIBE BIND VARIABLES Statement

SYNTAX
describe bind variables ::=
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
  INTO input_sqlda_name ; 

DESCRIBE SELECT LIST Statement

SYNTAX
describe select list ::=
EXEC SQL DESCRIBE SELECT LIST FOR statement_name
  INTO output_sqlda_name ; 

DISCONNECT Statement

SYNTAX
disconnect statement ::=
  DISCONNECT { ’connection_name’ | CURRENT | ALL | DEFAULT } ; 

DROP INDEX Statement

SYNTAX
drop index statement ::=
DROP INDEX [ index_owner_name.]index_name 
  [ ON [ table_owner_name.]table_name ]  

DROP PROCEDURE Statement (ODBC Core SQL Grammar)

SYNTAX
drop procedure statement ::=
DROP PROCEDURE [ owner_name.]procedure_name ; 

DROP SYNONYM Statement

SYNTAX
drop synonym statement ::=
DROP [ PUBLIC ] SYNONYM synonym ; 

DROP TABLE Statement

SYNTAX
drop table statement ::=
DROP TABLE  [ owner_name.]table_name ; 

DROP TRIGGER Statement

SYNTAX
drop trigger statement ::=
DROP TRIGGER [ owner_name.]trigger_name ; 

DROP USER Statement

SYNTAX
drop user statement ::=
DROP USER ’username’ ; 

DROP VIEW Statement

SYNTAX
drop view statement ::=
DROP VIEW [ owner_name.]view_name ; 

EXEC SQL Statement

SYNTAX
EXEC SQL ::=
EXEC SQL  sql_statement ; 

EXECUTE Statement

SYNTAX
EXECUTE ::=
EXEC SQL EXECUTE statement_name
  [ USING { [ SQL ] DESCRIPTOR structure_name 
    | :host_variable [ [ INDICATOR ] :ind_variable ] , ... }
  ] ; 

EXECUTE IMMEDIATE Statement

SYNTAX
EXECUTE IMMEDIATE ::=
EXEC SQL EXECUTE IMMEDIATE
  { statement_string | host_variable } ; 

FETCH Statement

SYNTAX
fetch ::=
EXEC SQL FETCH cursor_name 
  { USING SQL DESCRIPTOR structure_name 
    | INTO :host_var_ref [ [ INDICATOR ] :ind_var_ref ] , ... 
  } ; 

GET DIAGNOSTICS Statement

SYNTAX
get diagnostics statement ::=
GET DIAGNOSTICS 
  :param = header_info_item 
    [ , :param = header_info_item ] ,...
; 

Header Info Item

SYNTAX
header_info_item ::=
{ NUMBER 
  | MORE 
  | COMMAND_FUNCTION 
  | DYNAMIC_FUNCTION 
  | ROW_COUNT 
} 

GET DIAGNOSTICS EXCEPTION Syntax

SYNTAX
get diagnostics exception statement ::=
GET DIAGNOSTICS EXCEPTION number 
  :param = detail_info_item
    [, :param = detail_info_item ] , ...
; 

Detail Info Item Syntax

SYNTAX
detail_info_item ::=
{ CONDITION_NUMBER
  | RETURNED_SQLSTATE
  | CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | ENVIRONMENT_NAME
  | CONNECTION_NAME
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
  | MESSAGE_TEXT
  | MESSAGE_LENGTH
  | MESSAGE_OCTET_LENGTH
} 

GRANT RESOURCE, DBA Statement

SYNTAX
grant resource, dba statement ::=
GRANT { RESOURCE, DBA } TO user_name [ , user_name  ] , ... 
; 

GRANT PRIVILEGE Statement

SYNTAX
grant privilege statement ::=
GRANT { privilege [, privilege ], ... | ALL [ PRIVILEGES] }
  ON table_name 
  TO  { user_name [, user_name ] , ... | PUBLIC }
  [WITH GRANT OPTION] ;  

PRIVILEGE

SYNTAX
privilege ::=
{ SELECT | INSERT | DELETE | INDEX 
  |UPDATE [ ( column , column , ... ) ]
  | REFERENCES [ ( column , column , ... ) ] } 

INSERT Statement

SYNTAX
insert statement ::=
INSERT INTO [ owner_name.] {table_name |view_name }
  [ ( column_name [, column_name ] , ... ) ]
  { VALUES ( value [, value ] ,... ) | query_expression } ; 

See also the Query Expressions Syntax in BNF.

LOCK TABLE Statement

SYNTAX
lock table statement ::=
LOCK TABLE table_name [ , table_name ] , ... IN { SHARE | EXCLUSIVE } MODE ; 

OPEN Statement

SYNTAX
open ::=
EXEC SQL OPEN cursor_name 
  [ USING { [ SQL ] DESCRIPTOR structure_name
    | :host_variable [ [ INDICATOR ] :ind_variable ] , ... }
  ] ; 

PREPARE Statement

SYNTAX
prepare ::=
EXEC SQL PREPARE statement_name FROM statement_string ; 

REVOKE RESOURCE, DBA Statement

SYNTAX
revoke resource, dba statement ::=
REVOKE  { RESOURCE | DBA }
    FROM { user_name [ , user_name ] ,... } ; 

REVOKE PRIVILEGE Statement

SYNTAX
revoke privilege statement ::=
REVOKE [ GRANT OPTION FOR ]
  { privilege [, privilege , ] , ... | ALL [ PRIVILEGES ] }
  ON table_name
  FROM { user_name [ , user_name ] , ... | PUBLIC }
    [ RESTRICT | CASCADE ] ; 

PRIVILEGE Syntax

SYNTAX
privilege ::=
{ SELECT | INSERT | DELETE | INDEX 
  |UPDATE [ ( column , column , ... ) ]
  | REFERENCES [ ( column , column , ... ) ] } 

ROLLBACK Statement

SYNTAX
rollback statement ::=
ROLLBACK [ WORK ] ; 

SELECT Statement

SYNTAX
select statement ::=
query_expression
  ORDER BY { expr | posn } [ ASC | DESC ]
    [ , { expr | posn } [ ASC | DESC ] , ... ]
  FOR UPDATE [ OF [ table.]column_name , ... ] [ NOWAIT ]
; 

See also section Query Expressions Syntax in BNF.

SET CONNECTION Statement

SYNTAX
set connection statement ::=
  SET CONNECTION { ’connection_name’ | DEFAULT } ; 

SET SCHEMA Statement

SYNTAX
set schema statement ::=
SET SCHEMA { ’string_literal’ | ? | :host_var | USER  } 

SET TRANSACTION ISOLATION LEVEL Statement

SYNTAX
set transaction isolation level statement ::=
SET TRANSACTION ISOLATION LEVEL isolation_level_name ; 

ISOLATION LEVEL NAME

SYNTAX
isolation_level_name ::=
  READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE 

UPDATE Statement

SYNTAX
update statement ::=
UPDATE table_name 
  SET assignment [,assignment ], ... [ WHERE search_condition ] ; 

ASSIGNMENT CLAUSE

SYNTAX
assignment ::=
column = { expr | NULL } 
  | ( column [, column ], ... ) = ( expr [, expr ], ... )
  | ( column [, column ], ... ) = ( query_expression ) 

UPDATE STATISTICS Statement

SYNTAX
update statistics statement ::=
UPDATE STATISTICS [ FOR table_name ] 

WHENEVER Statement

SYNTAX
whenever ::=
EXEC SQL WHENEVER
  { NOT FOUND | SQLERROR | SQLWARNING }
  { STOP | CONTINUE | { GOTO | GO TO } host_lang_label } ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095