SQL basics- complete reference guide - part8 SQL System Commands

Part8: SQL System Commands Reference guide

TOPICTEXTSYNTAXExample
ARRAY_GETReturns one element of an array.ARRAY_GET(arrayExpression, indexExpression)
ARRAY_LENGTHReturns the length of an array.ARRAY_GET(arrayExpression)
AUTOCOMMITReturns true if auto commit is switched on for this session.AUTOCOMMIT()
CANCEL_SESSIONCancels the currently executing statement of another session.CANCEL_SESSION(sessionInt)
CASEWHEN FunctionReturns 'a' if the boolean expression is true, otherwise 'b'.CASEWHEN(boolean, aValue, bValue)
CASTConverts a value to another data type.CAST(value AS dataType)
COALESCEReturns the first value that is not null.COALESCE(aValue, bValue [,...])
CONVERTConverts a value to another data type.CONVERT(value, dataType)
CURRVALReturns the current (last) value of the sequence, independent of the session.CURRVAL( [ schemaName, ] sequenceString )
CSVREADReturns the result set of reading the CSV (comma separated values) file.CSVREAD(fileNameString [, columnsString [, csvOptions ] ] )
CSVWRITEWrites a CSV (comma separated values).CSVWRITE ( fileNameString, queryString [, csvOptions [, lineSepString] ] )
DATABASEReturns the name of the database.DATABASE()
DATABASE_PATHReturns the directory of the database files and the database name, if it is file
based.
DATABASE_PATH()
FILE_READReturns the contents of a file.FILE_READ(fileNameString [,encodingString])
GREATESTReturns the largest value that is not NULL, or NULL if all values are NULL.GREATEST(aValue, bValue [,...])
IDENTITYReturns the last inserted identity value for this session.IDENTITY()
IFNULLReturns the value of 'a' if it is not null, otherwise 'b'.IFNULL(aValue, bValue)
LEASTReturns the smallest value that is not NULL, or NULL if all values are NULL.LEAST(aValue, bValue [,...])
LOCK_MODEReturns the current lock mode.LOCK_MODE()
LOCK_TIMEOUTReturns the lock timeout of the current session (in milliseconds).LOCK_TIMEOUT()
LINK_SCHEMACreates table links for all tables in a schema.LINK_SCHEMA(targetSchemaString, driverString, urlString,
userString, passwordString, sourceSchemaString)
MEMORY_FREEReturns the free memory in KB (where 1024 bytes is a KB).MEMORY_FREE()
MEMORY_USEDReturns the used memory in KB (where 1024 bytes is a KB).MEMORY_USED()
NEXTVALReturns the next value of the sequence.NEXTVAL ( [ schemaName, ] sequenceString )
NULLIFReturns NULL if 'a' is equals to 'b', otherwise 'a'.NULLIF(aValue, bValue)
READONLYReturns true if the database is read-only.READONLY()
ROWNUMReturns the number of the current row.ROWNUM()
SCHEMAReturns the name of the default  schema for this session.SCHEMA()
SCOPE_IDENTITYReturns the last inserted identity value for this session for the current scope.SCOPE_IDENTITY()
SESSION_IDReturns the unique session id number for the current database connection.SESSION_ID()
SETUpdates a variable with the given value.SET(@variableName, value)
TABLEReturns the result set.{ TABLE | TABLE_DISTINCT } ( { name dataType = expression } [,...] )
TRANSACTION_IDReturns the current transaction id for this session.TRANSACTION_ID()
USERReturns the name of the current user of this session.{ USER | CURRENT_USER } ()Select User();

No comments :

Post a Comment

Your Comment and Question will help to make this blog better...