TO_CHAR( given_value [,format_string] )

Description

This function is used to convert a given date value or a numeric value into a string. The optional format string specifies the output format of the converted string.

• If the given_value is a date/time, then the format_string is the same as that of strftime in time.h.
• If the given_value is a numeric then the format_string is the same as that for the C format specification for functions like printf.

Example

LogicSQL> select to_char(date '2005-03-22') "DATE " from dual; "DATE " ---------------------- 2005-03-22 00:00:00 LogicSQL> SELECT TO_CHAR(-10000, 'RMB %.2f') "Amount" FROM DUAL; "Amount" ---------------- RMB -10000.00

Avoid to use the printf format identifiers like %d for non-integers. For example,

LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2d') "Amount" FROM DUAL; Amount --------- RMB 0 LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2f') "Amount" FROM DUAL; Amount ------------------- RMB -1000.000000 LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2.2f') "Amount" FROM DUAL; Amount --------------- RMB -1000.00

2.2.6 CHR

• Syntax

  CHR(n)

• Function

  CHR returns the character having the binary equivalent to n.

  If n > 256, then LogicSQL returns the binary equivalent of n mod 256.

• Examples

  LogicSQL> SELECT CHR(67)||CHR(65)||CHR(84) FROM INFORMATION_SCHEMA.DUAL; chr(67)||chr(65)||chr(84) ---------------------------- CAT

2.2.7 INSTR

• Syntax

  INSTR(string1,string2 [,n[,m]])

• Function

  Searches string1 beginning with its nth character for the mth occurrence of string2 and returns the position of the character in string1 that is the first character of this occurrence. If n is negative, LogicSQL counts and searches backward from the end of string1. The value of m must be positive. The default values of both n and m are 1, meaning LogicSQL begins searching at the first character of string1 for the first occurrence of string2. The return value is relative to the beginning of string1, regardless of the value of n, and is expressed in integer. If the search is unsuccessful (if string2 does not appear m times after the nth character of char1) the return value is 0.

  If the value of n or m is out of the range, it will then set to 1.

• Examples

  LogicSQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) instring_position FROM INFORMATION_SCHEMA.DUAL; instring_position -------------------- 14 LogicSQL> SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM INFORMATION_SCHEMA.DUAL; instr(string(CORPORATE FLOOR),string(OR),-3,2) ------------------------------------------------- 2

2.2.8 CURRENT_CERTIFICATE

Function: CURRENT_CERTIFICATE

• Syntax

  CURRENT_CERTIFICATE

• Function

  CURRENT_CERTIFICATE returns the (security) certificate carried by the current session.

2.2.9 CURRENT_GROUP

Function: CURRENT_GROUP

• Syntax

  CURRENT_GROUP

• Function

  CURRENT_CERTIFICATE returns the list of security groups carried by the current session.

  Usage

  The following example demonstrates how to use the current_group function to list all groups in the current group

  LogicSQL> SELECT GROUP_NAME FROM SYSTEM.DEFINITION_SCHEMA.GROUPS WHERE GROUP_NAME IN CURRENT_GROUP; group_name ------------- capital develop employee enterprise LogicSQL>

2.2.10 ALL_GROUP

Function: ALL_GROUP

• Syntax

  ALL_GROUP ('< group name>')

• Syntax restriction

  The group name must be a constant, i.e., a string representing the given group name.

• Function

  ALL_GROUP returns the list of security groups contained in the given group name, including the given group name.

The following query returns the list of all groups that are contained in the given group named 'employee'.

LogicSQL> SELECT DISTINCT GROUP_NAME FROM SYSTEM.DEFINITION_SCHEMA.GROUPS WHERE GROUP_NAME IN ALL_GROUP('employee'); group_name ------------- develop employee market LogicSQL>

Furhter, the following query returns the list of all security certificates that are contained in the given group named 'enterprise'.

LogicSQL> SELECT DISTINCT certificate FROM SYSTEM.DEFINITION_SCHEMA.GROUP_authorization_descriptors WHERE status = 'certificate' and GROUP_NAME IN ALL_GROUP('enterprise'); certificate -------------- peter phillis sarah tim LogicSQL>

2.2.11 NVL

• Syntax

  NVL(expr1, expr2)

• Function

  {NVL} returns expr2 if expr1 is NULL; otherwise, returns expr1.

• Examples

  LogicSQL> SELECT sid, name, NVL(major, 'not determined') FROM student; sid name nvl(major,not determined) ---------------------------------------------- 10 susan cs 20 peter not determined 30 sarah md 40 tom ce

  Expr1 and Expr2 must have the same datatype, though they can be any type.

2.3 SQL Commands

This section describes, in alphabetical order, LogicSQL commands and statements.

All LogicSQL statements are classified into the following catagories:

• Data Definition Language (DDL) statements
• Data Manipulation Language (DML) statements
• Transaction Control statements
• Session Control statements
• System Utility statements

Data Definition Language (DDL) statements are used to perform the following tasks

• create and drop database objects such as tables, views, indexes, and triggers,
• grant and revoke access privileges and roles

LogicSQL implicitly comments the current transaction after each DDL statements.

Data Manipulation Language (DML) statements are used to retrieve and update data stored in various database objects, including

• INSERT
• DELETE
• UPDATE
• SELECT

Transaction Control statements controls the execution of transactions. It contains the following SQL statements

• SET TRANSACTION (START TRANSACTION)
• COMMIT
• ROLLBACK

Session Control statements dynamically change the behavior of a user session.

• SET AUTO COMMIT ON/OFF
• SET ROLE
• CLEAR SESSION (NEW SESSION)

Running Example Through out this section, we will use a sample database with three tables listed below for demonstration.

student(sid, name, major, gpa) course(cid, title, description) registration(cid, sid, grade) LogicSQL> select * from student; sid name major gpa ------------------------------- 5500 Susan Law 12001 Sarah Math 12345 Peter 54321 Bob Buines LogicSQL> select * from course; cid title description --------------------------------------------------------- AR100 Cartoon Drawing A commic book BS499 Marketing How to make quick bucks CS291 Introduction to DBMS first database course MA101 Calculus An easy course LogicSQL> select * from registration; cid sid grade ------------------------ BS499 54321 B CS291 12001 CS291 54321 A MA101 12001 D

The script file to set up the running database is given below.

/* * to set up the initial database */ DROP TABLE student; DROP TABLE course; DROP TABLE registration; CREATE TABLE student ( sid INT, name VARCHAR(64) NOT NULL, major VARCHAR(10), gpa NUMERIC(5, 2), PRIMARY KEY(sid) ); CREATE TABLE course ( cid CHAR(5), title VARCHAR(64) NOT NULL, description VARCHAR(1024), PRIMARY KEY(cid) ); CREATE TABLE registration ( cid CHAR(5), sid INT, grade CHAR(1), PRIMARY KEY(cid, sid), CONSTRAINT cid_foreign_key FOREIGN KEY (cid) REFERENCES course, CONSTRAINT sid_foreign_key FOREIGN KEY (sid) REFERENCES student, CONSTRAINT grade_constraint CHECK ( grade IN ('A', 'B', 'C', 'D', 'F')) ); INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math'); INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null); INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Buines'); INSERT INTO student (sid, name, major) VALUES (5500, 'Susan', 'Law'); INSERT INTO course VALUES ('CS291','Introduction to DBMS','first database course'); INSERT INTO course VALUES ('MA101', 'Calculus', 'An easy course'); INSERT INTO course VALUES ('BS499', 'Marketing', 'How to make quick bucks'); INSERT INTO course VALUES ('AR100', 'Cartoon Drawing', 'A commic book'); INSERT INTO registration VALUES('CS291', 12001, null); INSERT INTO registration VALUES('CS291', 54321, 'A'); INSERT INTO registration VALUES('MA101', 12001, 'D'); INSERT INTO registration VALUES('BS499', 54321, 'B');

The list of All LogicSQL statements

2.3.1 ABORT		Abort the current operation
2.3.2 ALTER TABLE		Alter the table definition
2.3.3 ALTER USER		Alter the user password
2.3.4 CLOSE CURSOR		Close a given cusor
2.3.5 COMMIT		Commit the current transaction
2.3.6 CREATE DATABASE		Create a new database
2.3.7 CREATE DATATYPE		To be implemented
2.3.8 CREATE INDEX		Create an index for a given table
2.3.21 CREATE INVERTED CURSOR		Create a temporary table for text queries
2.3.9 CREATE INVERTED INDEX		Create an inverted index for text query
2.3.10 CREATE PROCEDURE		Create a stored procedure
2.3.11 CREATE ROLE		Create an authorization role
2.3.12 CREATE SCHEMA		Create tables and views and perform multiple grants in a single transaction
2.3.13 CREATE SEQUENCE		Create a sequence of integers
2.3.14 CREATE TABLE		Create a new table
2.3.15 CREATE TRIGGER		Create a new trigger
2.3.16 CREATE USER		Create a new user
2.3.17 CREATE VIEW		Create a view
2.3.18 DEALLOCATE		Deallocate a prepare statement
2.3.19 DECLARE CURSOR		Declare a session cursor
2.3.22 DELETE		Delete rows from a table
2.3.23 DROP CURSOR		Drop a session cursor
2.3.24 DROP DATABASE		Remove an existing database
2.3.25 DROP INDEX		Remove an existing index
2.3.20 DROP INVERTED CURSOR		Close an inverted cursor for the session
2.3.26 DROP PROCEDURE		Remove a stored procedure
2.3.27 DROP ROLE		Remove an existing role
2.3.28 DROP SCHEMA		Remove an existing schema
2.3.29 DROP SEQUENCE		Remove a existing sequence
2.3.30 DROP TABLE		Remove an existing table
2.3.31 DROP TRIGGER		Remove an existing trigger
2.3.32 DROP USER		Remove an existing user
2.3.33 DROP VIEW		Remove an existing view
2.3.34 EXECUTE PROCEDURE		Execute a stored procedure
2.3.35 EXECUTE STATEMENT		Execute a prepare statement
2.3.36 FETCH		Fetch a row from the result set pointed by the given cursor
2.3.37 GRANT PRIVILEGE		Grants access privilege
2.3.38 GRANT ROLE		Grants roles to users and roles
2.3.39 INSERT		Inserts new rows into a table
2.3.40 OPEN CURSOR		Open a cursor
2.3.41 PREPARE		Declare a prepare statement
2.3.42 RELEASE SAVEPOINT		Release an established savepoint
2.3.43 REMOVE LOB		Remove a BLOB/CLOB
2.3.44 REQUEST LOBLOCATOR		Request a new lob locator
2.3.45 REVOKE PRIVILEGE		Revoke access privilege
2.3.46 REVOKE ROLE		Revoke roles from users and roles
2.3.47 ROLLBACK		Aborts the current transaction
2.3.48 SAVEPOINT		Establish a savepoint for the transaction management
2.3.49 SET LANGUAGE		Set the language code for the current SQL session
2.3.50 SET ROLE		Set the role for the current SQL session
2.3.51 SELECT and Subquery		Query the database
2.3.52 SET AUTO_COMMIT		Set the auto_commit on/off for the client
2.3.53 SET SESSION USER		Set the current session user
2.3.54 SET TRANSACTION		Set the isolation level of the current transaction
2.3.55 USE DATABASE		Set the default database (catalog) for the sessin
2.3.56 UPDATE		Update column values of a table

List of extended SQL statement and functions used to implement LogicSQL Text and Search.

SQL statements	Descriptions
CREATE INVERTED INDEX	Create an inverted index for text queries
CREATE INVERTED CURSOR	Create a temporary table for text queries using the top k algorithm
DROP INVERTED CURSOR	Close an inverted cursor
SQL functions	Descriptions
CONTAINS	Return ranking factor for text queries
CURSOR_TABLE	a function mapping an inverted cursor to a temporary table
AGGREGATE FUNCTION	Return a temp table for TOP K queries

List of extended SQL statements and functions used to implement the LogicSQL's extended Unix security model.

SQL statements	Descriptions
ADD INTO GROUP	Add certificates/groups to a group
ALTER CERTIFICATE	Alter the password of the certificate
CREATE CERTIFICATE	Create a new security certificate
CREATE GROUP	Create a new security group
DROP CERTIFICATE	REMOVE a security certificate
DROP GROUP	Drop an existing security group
RELEASE CERTIFICATE	Release a certificate for the current session
REMOVE FROM GROUP	Remove certificates/groups from a group
REQUEST CERTIFICATE	Request a certificate for the current session
SQL functions	Descriptions
CURRENT_CERTIFICATE	Return the certificate carried by the current session
CURRENT_GROUP	Return the list of security groups of the current certificat
ALL_GROUP	Return the list of all groups contained in the given group

2.3.1 ABORT

• Function Abort the current operation.

• Syntax

  ABORT

• Usage

  The command aborts the current operation. It is mainly used in a trigger definition to abort the trigger event.

2.3.2 ALTER TABLE

• Function Modify the table definition

• Syntax

  ALTER TABLE < table name> < alter table action>

  This statement has been partially implemented. More specifically, the following parts of < alter table action> are currently supported.

  • ALTER TABLE < table name> MODIFY [COLUMN] < column name> DEFAULT (< literal value>)

  • ALTER TABLE < table name> ADD [COLUMN] < column definition>

  • ALTER TABLE < table name> DROP [COLUMN] < column name> [CASCADE|RESTRICT]

  • ALTER TABLE < table name> ALTER [COLUMN] < column definition>

  • ALTER TABLE < table name > ADD < table constraint definition>

• Syntax and Semantic Restrictions
  • No constraints shall be specified when a new column is added into a table. Use ADD CONSTRAINT after a column has been added if one is needed.

  • ADD COLUMN will be rejected if the table is currently updated by other transactions.

  • DROP COLUMN will be rejected if RESTRICT is specified in DROP COLUMN and the column is referenced in any constraints, triggers, or view definitions. If CASCADE is specified, on the other hand, any shema object referencing the column to be dropped will also be dropped.

    The default drop behavior of DROP COLUMN is RESTRICT. (Currently, LogicSQL supports only RESTRICT drop behavior.

• Usage

2.3.3 ALTER USER

• Function Modify user account information.

• Syntax

  ALTER USER < identifier > WITH PASSWORD < password string>

• Usage

  ALTER USER sarah WITH PASSWORD 'hgd2f23Abd'

  Ordinary users can only change their own password.
  A password must be a string of characters, digits, and '_', and the password string shall not be constructed from dictionary words.

2.3.4 CLOSE CURSOR

• Function Close a session cursor

• Syntax

  CLOSE < cursor name>

• Usage

  CLOSE current_student;

  This will close the given cusor.

See also DECLARE CURSOR, OPEN CURSOR, DROP CURSOR, and FETCH statements.

2.3.5 COMMIT

• Description Terminate the current SQL-transaction.

  All changes made by the transaction after COMMIT are guaranteed to be durable if a crash occurs.

• Syntax

  COMMIT [<WORKS>]

• Usage COMMIT

• Note

  See also SET TRANSACTION and ROLLBACK.

2.3.6 CREATE DATABASE

• Function Create a database ( catalog in SQL99 terminology ) as well as a schema, whose name is the same as the user name, under the new database

• Syntax

  CREATE <CATALOG|DATABASE> < catalog name>

• Usage

  CREATE CATALOG my_database

  This will create a database ( catalog ) my_database.

  CREATE DATABASE her_database

  This will create a database (catalog) her_database. her_database

  One needs CREATE ANY CATALOG privilege to create a catalog.

  See Also: DROP DATABASE

2.3.7 CREATE DATATYPE

2.3.8 CREATE INDEX

• Function Use the CREATE INDEX statement to create an index on one or more columns of a table. An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table and provides direct, fast access to rows. LogicSQL supports conventional (B-tree) indexes.

• Syntax

  CREATE [UNIQUE] INDEX < index name> ON < table name> ( < column list>)

• Usage

  CREATE INDEX customer_index ON customer ( customer_name )

  This will create a secondary index on column customer_name of the table customer such that the retrieval from customer can be processed efficiently as long as the customer name is given.

  If a table has the primary key, then the second index will point to the primary key. Otherwise, it will point to the whole data.

  Creating an index may improve the performance of data retrieval, but it will certainly increase the overhead of data updates. Thus, don't create an index unless your applications involve frequent retrieval based on the values of columns to be indexed.

  It is very time-consuming When an index is created after the table has been populated with some rows because LogicSQL has to build the index file.

  See Also: DROP INDEX

  To create an index for a table in a schema, including your own schema, you must have CREATE INDEX schema privilege on the schema, unless you have CREATE ANY INDEX system privilege.

2.3.9 CREATE INVERTED INDEX

• Function Use the CREATE INVERTED INDEX statement to create an inverted index on one columns of a table. An inverted index is a schema object that consists a sequence of ({key word}, key_value) pairs where key_value points to the row of the column which contains the key_word. See LogicSQL Text and Search for details.

• Syntax

  CREATE INVERTED INDEX < index name> ON < table name> (< column name>)
[DICTIONARY < table name>]
[STOP LIST < table name>]
[GROUP BY < column name>]

• Syntax Restrictions

  The following restrictions are applied to the inverted index.

  • An inverted index can be created only on text columns, that is, on columns whose datatype are VARCHAR, CHAR, CHARACTER_DATA, or CLOB.

  • An inverted index can only be created on a table with a single column as its primary key. The singular primary key column is viewed as the document ID for the inverted index.

  • The table specified in the DICTIONARY clause is called a dictionary table, that is a table with one column storing all the words in the dictionary. Similarly, the table specified in the STOP List clause is also a dictionary table that contains the list of all stop words for the inverted index. (LogicSQL currently supports only the default dictionary and stop list.)

  • All the meta objects in this statement, including the index, the text table, the disctionary table, and the stop list table, must belong to the same schema, for simplicity.

  • An inverted index is created to indexing text documents stored in one column, and a column can be indexed by at most one inverted index.

  • The default dictionary is the list of all words in the documents that are separated by white spaces, and the default stop list is an empty list.

• The group by clause specifies the column by which are documents and indeses words are partitioned.
• Usage

  CREATE INVERTED INDEX key_word_index ON book(abstract )

  This will create an inverted index on column abstract of the table book such that you may retrieve all relevant books whose abstracts contain the specified key words

  CREATE INVERTED INDEX subject_index ON book(abstract) GROUP BY subject

  This will create an inverted index the same as the privious one, except that all the words in the inverted index are partitioned according to the values of subject. Consider the following book table

  document_id	subject	abstract	isbn
  10	math	beautiful language	12345
  20	english	strange language	23456
  30	math	math is strange	34567
  40	novel	mission impossible	45678
  50	novel	as strange as impossible	56789
  60	english	language is not math	67890

  For this example the first index, key_word_index, shall consist of the following entries.

  word	document_id
  as	50
  beautiful	10
  impossible	40, 50
  is	30, 60
  language	10, 20, 60
  math	30, 60
  mission	40
  not	60
  strange	20, 50

  The second index, subject_index, shall consist of the following entries.

  subject word	document_id
  english	is	60
  english	language	20, 60
  english	math	60
  english	not	60
  english	strange	20
  math	beautiful	10
  math	is	30
  math	language	10
  math	math	30
  novel	as	50
  novel	impossible	40, 50
  novel	mission	40
  novel	strange	50

  The group by clause can be used to partition the inverted index such that the text query based on specified group can be efficiently evaluated.

  See Also: DROP INDEX

  you must have CREATE INVERTED INDEX schema privilege on the schema, unless you have CREATE ANY INVERTED INDEX system privilege.

2.3.10 CREATE PROCEDURE

• Function Use the CREATE PROCEDURE statement to create a standalone stored procedure.

  A stored procedure is a group of extended SQL statements that one can call by name.

  With the CREATE PROCEDURE command, you can create a procedure and store it in the database. You can call a stored procedure from any environment from which you can issue an SQL statement.

  Stored procedures offer advantages in the areas of development, integrity, security, and performance.

  To create a procedure in your own schema, you must have CREATE PROCEDURE system privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege.

  To call a stored procedure, you must have EXECUTE PROCEDURE privileges on the the procedure, or EXECUTE ANY PROCEDURE system privilege.

  Note that to facilitate the use of stored procedures, the default length for VARCHAR and CHAR in stored procedures has been re-set to that of string length specified in the configuration file.

• Prerequisites

  To create a procedure in your own schema, you must have CREATE PROCEDURE schema privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege.

  Similarly, one needs EXECUTE PROCEDURE schema privilege and/or DROP PROCEDURE schema privilege to execute and/or drop a procedure in his/her own schema.

  The following three statements from system will enable sarah to use procedures in her own schema.

  GRANT create procedure ON sarah TO sarah; GRANT execute procedure ON sarah TO sarah; GRANT drop procedure ON sarah TO sarah;

• Syntax

  <create procedure> ::= CREATE PROCEDURE < procedure name> [< argument list>] < SQL procedure block> <SQL procedure block> ::= [< declare clause>] < SQL procedure statement list> <declare clause> ::= DECLARE < variable name> < data type>[DEFAULT < literal value>];> [{< variable name>< data type> [DEFAULT < literal value>]; ...}] <SQL procedure statement list> :: = BEGIN < SQL procedure statement>; [{< SQL procedure statement>; }] END <SQL procedure statement> ::= < Any SQL statement> | < assignment statement | < if-then-else statement | < case statement | < for loop statement> | < while loop statement> | < repeat loop statement> | < select into statement> | < raise exception statement> | < SQL procedure block> <argument list> ::= ( < argument name> [ IN | OUT ] < data type> [{,< argument name> [ IN | OUT ] < data type> ... } ] ) <parameter list> ::= < literal value> [{, < literal value>}] <assignment statement> ::= :< variable name> = < value expression> <if-then-else statement> ::= IF < boolean value expression> THEN < SQL procedure statement list> [ELSE < SQL procedure statement list>] END IF <case statement> ::= CASE WHEN < variable name> = < integer> TEHN < SQL procedure statement list> [{ WHEN < variable name> = < integer> TEHN < SQL procedure statement list> } ... ] [ELSE < SQL procedure statement list>] END CASE <for loop statement> ::= FOR ( :< variable name> = < integer>; :< variable name> < comparison operator> < integer>; < assignment statement ) < SQL procedure statement list> END FOR <while loop statement> ::= WHILE < boolean value expression> DO < SQL procedure statement list> END WHILE <repeat loop statement> ::= REPEAT < SQL procedure statement list> UNTIL< boolean value expression> <raise exception statement> ::= RAISE EXCEPTION ( < string value expression> ) <select into statement> ::= SELECT [ <DISTINCT|ALL> ] < * | < derived column> [{,< derived column> ... }]> INTO :< variable name> [{:< variable name> ... } ] FROM < table reference> [ , < table reference> ] [WHERE < search condition>] [GROUP BY < item reference> {, < item reference> ... }] [HAVING < search condition >] [ <UNION|UNION ALL|EXCEPT|MINUS|INTERSECT> ( subquery ) ] [< order by clause>] <Any SQL statement> ::= Any LogicSQL statement, except < subquery>, which is replaced by < select into statement>.

  Some Notations

  1. The scope of any declared variable is the immedicate block after the DECLARE clause.
  2. A special variable of INTEGER type, named sqlstate, has been declared automatically to denote the execution status of the very last SQL statement within the stored procedure. The value of sqlstate is assigned by LogicSQL right after the SQL statement is executed as follows:
     1. sqlstate = 0, the execution fails,
     2. sqlstate = 1, the execution succeeds, and
     3. sqlstate = 2, the SQL statement is a SELECT statement but an empty result set is returned.
  3. When the execution of an SQL statement failed or a SELECT statemetn returns an empty result set, the execution of the stored procedure will continue. That is, LogicSQL will not rollback the current transaction when the execution of a statement inside a stored procedure or a trigger, unless the procedure/trigger raises an exception explicitly.
  4. When the raise exception is executed, LogicSQL will first rollback the current transaction, send an error message to the current client, and then exits the stored procedure.
  5. We will, in the next release, declare a special variable of ResultSet type which is used to store and return the queries to the client. We assume that all the result set will be stored in the server temporarily, and it will be removed if the subsequent statements are not SELECT FROM the procedure.name.resultset. Sept. 23, 2004 by Li-Yan Yuan

  6. All system-defined datatypes, except BLOB, CLOB, Long, and Long RAW, are valid in the SQL procedure block.

• Keywords and Explanations

• Usage

  After a stored procedure has been created, it can be called from any environment from which an SQL statement can be issued.

  See Also: EXECUTE PROCEDURE, DROP PROCEDURE.

  Example 1 The following statement creates a stored procedure that can be used to raise an employees salary.

  drop table employee; create table employee (emp_id int, salary real); insert into employee values( 100, 1000000); insert into employee values( 200, 1999900); insert into employee values( 300, 2000000); drop procedure raise_salary; CREATE PROCEDURE raise_salary ( emp_id INTEGER, increase REAL ) DECLARE salary_cap REAL; current_salary REAL; BEGIN SELECT max(salary) INTO :salary_cap FROM employee; IF (sqlstate = 0 ) THEN raise_exception('fail to find the maximum salary'); END IF; SELECT salary INTO :current_salary FROM employee WHERE emp_id = :emp_id; IF (sqlstate <> 1 ) THEN raise_exception('failed to find the salary of the given employee'); END IF; IF ( :current_salary + :increase < :salary_cap ) THEN UPDATE employee SET salary = :current_salary + :increase WHERE emp_id = :emp_id; ELSE raise_exception('no way to raise his/her salary'); END IF; END; call procedure raise_salary(100, 200); call procedure raise_salary(200, 200); call procedure raise_salary(400, 200);

  The executions of the last two call procedure statements raise different exceptions, as expected.

  Example 2 The following example demonstrates how to use sqlstat.

  create table students (sid int, sname varchar(100)); /* * This procedure can be used to pick up one student id from the given table students. * The procedure will output -1 if the selection fail, output 0 if students is * empty, and a student id if there exists at lease one student. * */ create procedure pick_one_student ( sid out integer ) begin select top 1 sid into :sid from students; if sqlstate = 2 then :sid = 0; else begin if sqlstate = 0 then :sid = -1; end if; end; end if; end; insert into students values( 10, 'susan' ); insert into students values( 20, 'peter' ); call procedure pick_one_student();

2.3.11 CREATE ROLE

• Function To create a role. A role is a set of privileges that can be granted to users or to other roles. See also Security Model.
• Syntax

  CREATE ROLE < role name> [WITH ADMIN < grantor>]

  <role name> ::= < identifier>

  <grantor> ::= CURRENT_USER | CURRENT_ROLE

• Prerequisites

  You must have CREATE ANY ROLE system privilege to create a role.

• Usage This statement will create a role authorization descriptor (of the given role name) with the admin option granted to the current user or the current role.

  If [WITH ADMIN < grantor>] is not specified, then the admin option is granted to the current user.

  See Also: DROP ROLE, GRANT ROLE, REVOKE ROLE.

2.3.12 CREATE SCHEMA

• Purpose Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in a single transaction.

  To execute a CREATE SCHEMA statement, LogicSQL executes each included statement. If all statements execute successfully, LogicSQL commits the transaction. If any statement results in an error, LogicSQL rolls back all the statements.

  Note: This statement does not necessarily create a schema. LogicSQL automatically creates a schema named after user when one creates a user (see CREATE USER). This statement, however, can be used by a user to create an additional schema.

  This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

• Syntax

  CREATE SCHEMA [< schema name>] AUTHORIZATION [< user name>] { < CREATE TABLE STATEMENT> | < CREATE VIEW STATEMENT> | < GRANT STATEMENT> }

  or,

  CREATE SCHEMA < schema name>

  The default user name (when it is omitted) is the user who issues the statement. The default schema name (when is omitted) is the same as the user name.

  If the schema name is omitted, or the same as the user name, this statement will create all the database objects in a single transaction.

  Otherwise, the statement will first create a schema with the given name, if it has not been create before, and then create tables, views and grants privileges, all in a single transaction.

• Usage

  CREATE SCHEMA AUTHORIZATION blair CREATE TABLE sox (color VARCHAR2(10) PRIMARY KEY, quantity NUMBER) CREATE VIEW red_sox AS SELECT color, quantity FROM sox WHERE color = 'RED' GRANT select ON red_sox TO waites;

  This statement creates a schema named blair for the user Blair, creates the table sox, creates the view red_sox, and grants SELECT privilege on the red_sox view to the user waites.

2.3.13 CREATE SEQUENCE

• Purpose To create a sequence.

  A sequence is a database object from which multiple users may generate unique integers. One may use sequences to automatically generate primary key values.

• Prerequisites To create a sequence in your own schema, you must have CREATE SEQUENCE privilege. To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege.

• Syntax

  CREATE SEQUENCE < sequence name> [START WITH < signed integer>] [INCREMENT BY < signed integer>] [MINVALUE < signed integer>] [MAXVALUE < signed integer>] [CYCLE|NOCYLE]

  LogicSQL will commit the current transaction following the CREATE SEQUENCE and/or DROP SEQUENCE statement.

• Using Sequences

  Sequence numbers can be used to automatically generate unique primary key values for your data, and they can also be used to coordinate the keys across multiple rows or tables.

  Values for a given sequence are automatically generated by LogicSQL routines, and only one sequence number can be generated at a time and sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

  When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

  Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

• Sequence Defaults

  You can create a sequence so that its values increment in one of following ways:

  • The sequence values increment without bound,
  • The sequence values increment to a predefined limit and then stop.
  • The sequence values increment to a predefined limit and then restart.

  To create a sequence that increments without bound, you shall not specify the MAXVALUE parameter. To create a sequence that increments to a predefined limit and then restart, one must specify the CYCLE option.

  Without specifying any option, you will create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

• Accessing Sequence Values Once a sequence is created, you can access its values in SQL statements with the following pseudocolumn:
  • NEXTVAL: increments the sequence and returns the new value.

• Example 1

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2 MAXVALUE 2000000000; SELECT NEXTVAL FROM item_id;

  The CREATE SEQUENCE statement will create a sequence named item_id and the SELECT statement will return 10. Note that the absolute value of MAXVALUE and MINVALUE cannot exceed 2000000000.

• Example 2

  One can also access the next value of a sequence in SQL statements with pseudocoumns:

  sequence.NEXTVAL,

  or qualify the name of the sequence in the schema of another user using

  schema.sequence.NEXTVAL or catalog.schema.sequence.NEXVAL.

  The following sequence of SQL statements demonstrate how to use a sequence to generate a sequence of primary keys for the application.

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2; CREATE TABLE items ( item_id int, item_name varchar(100), primary key (item_id) ); insert into items values( item_id.nextval, 'be cool'); insert into items values( item_id.nextval, 'boring'); insert into items values( item_id.nextval, 'nothing to do'); SELECT * FROM items; LogicSQL> select * from items; item_id item_name -------------------------- 10 be cool 12 boring 14 nothing to do

• Example 3

  The following example demonstrates how to use a sequence to automatically generate unique ID for a table.

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2; CREATE TABLE items ( item_id int, item_name varchar(100), primary key (item_id) ); CREATE PROCEDURE insert_one_item(item_name varchar(100) ) declare sec integer; BEGIN SELECT NEXTVAL INTO :sec FROM item_id; INSERT INTO items VALUES( :sec, :item_name); END; CALL PROCEDURE insert_one_item('Nikon 5400'); CALL PROCEDURE insert_one_item('DELL Inspiration'); CALL PROCEDURE insert_one_item('Volswagon Rabit'); SELECT * FROM items;

  The execution of the above program populates items as follows.

  item_id item_name ---------------------------- 10 Nikon 5400 12 DELL Inspiration 14 Volswagon Rabit

2.3.14 CREATE TABLE

• Function Create a table.

• Syntax

  CREATE TABLE < table name> (< table element> [{, < table element> ... } ])} CREATE TABLE < table name> AS < subquery>

  As with all other SQL statements supported by LogicSQL, see the SQL99 for the syntax rules and restrictions of the CREATE TABLE statement.

• Syntax Details

  <table element> ::= < column definition> | < table constraint definition>

  <column definition> ::= < column name> < data type> [ < default clause> | < identity clause>] [ < column constraint definition>... ]

  <column constraint definition> ::= [CONSTRAINT < constraint name> ] < column constraint>

  <column constraint> ::= NOT NULL | <UNIQUE | PRIMARY KEY > | < references specification> | < check constraint definition>

  <table constraint definition> ::= [CONSTRAINT < constraint name> ] < table constraint>

  <table constraint> ::= < unique constraint definition> | < referential constraint definition> | < check constraint definition>

  <unique constraint definition> ::= <UNIQUE | PRIMARY KEY> [ ( < column list> ) ]

  <referential constraint definition> ::= FOREIGN KEY [ ( < column list> ) ] < references specification>

  <references specification> ::= REFERENCES < table name> [ ( < column list> ) ] [ < referential triggered action> ]

  <referential triggered action> ::= < update rule> [ < delete rule> ] | < delete rule> [ < update rule> ]

  <update rule> ::= ON UPDATE < referential action>

  <delete rule> ::= ON DELETE < referential action>

  <referential action> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION

  <check constraint definition> ::= CHECK ( < search condition> )

  <default clause> ::= {<DEFAULT | := > < default option>}

  <default option> ::= <literal> | < datetime value function> | USER | CURRENT_USER | SESSION_USER | SYSTEM_USER | CURRENT_PATH | NULL

• Implementation Status

  The following features are accepted by the LogicSQL's parser, but have not yet been implemented:

  • the check constraint
  • all the constraint enforcement for the update statement.
  Further, because of (the lack of) the check constraint, the enforcement for the ferential constraint is implemented in a very limited and inefficient way (see the program comments for the details).
• Usage

  CREATE TABLE take ( student varchar(30), course integer, grade varchar(1), CONSTRAINT take_primary_key PRIMARY KEY ( student, course ) )

  This will create a table take under the default schema.

  CREATE TABLE database.sarah.take ( student varchar(30), course integer, grade varchar(1), CONSTRAINT take_primary_key PRIMARY KEY ( student, course ) )

  This will create a table take under schema database.sarah.

  See Also: DROP TABLE

2.3.15 CREATE TRIGGER

• Function

  Create and enable a database trigger which is a sequence of SQL statements associated with a table. LogicSQL automatically executes a trigger when specified conditions occur.

• Syntax

  <create trigger> ::= CREATE TRIGGER < trigger name> <BEFORE | AFTER | INSTEAD OF > < trigger event > ON < table name> [REFERENCING [OLD [AS] < correlation name>] [NEW [AS] < correlation name>]>] [FOR EACH <ROW | STATEMENT> ] < SQL procedure block>

• Syntax and Semantic Restrictions

  A CREATE TRIGGER must satisfy following restrictions.

  • The OLD reference does not apply to INSERT,
  • The NEW reference does not apply to DELETE,
  • An OLD/New reference variable is valid only if its value is uniquely determined by the event. For example, consider a variable, :OLD.employee_id referenced in a trigger that is defined on an update event with FOR EACH STATEMENT. This reference is valid only if the where clause in the update statement specifies the value for employee_id.
  • FOR EACH ROW orientation is supported for INSERT only.
  • An event can only trigger one trigger, even if there are more than one trigger related to the same event. LogicSQL will choose one trigger to be executed.
  • Instead of taking any measure to detect circular executions of triggers, LogicSQL limits the number of triggers triggered by the execution of one trigger. The limitation is specified by a configuration variable MaxTriggerCalls.

• Usage

  See Also: DROP TRIGGER

  To create a trigger for a table in a schema, including your own schema, you must have CREATE TRIGGER schema privilege on the schema, unless you have CREATE ANY TRIGGER system privilege. For an obvious reason, one shall not create a recursive trigger. LogicSQL does not provide a mechanism to detect recursive triggers. Instead, LogicSQL will limit the number of trigger operations allowed within a transaction. The limit is set to 1024, though this can be changed by modifying the configuration variable MaxTriggerCalls.

• Examples

  The following are a few examples demonstrated how to use triggers for various applications.

  DROP TABLE employee; CREATE TABLE employee( eid integer, ename varchar(100), salary int, pos varchar(100)); INSERT INTO employee VALUES ( 100, 'Peter Jenny', 100, 'New Anchor'); INSERT INTO employee VALUES ( 200, 'Sarah Smith', 300, 'President'); DROP TRIGGER salary_check; CREATE TRIGGER salary_check BEFORE INSERT ON employee DECLARE minsal integer; maxsal integer; BEGIN SELECT min(salary), max(salary) INTO :minsal, :maxsal FROM employee ; IF ( :new.salary < :minsal OR :new.salary > :maxsal) THEN raise_exception( 'salary vialation'); END IF; END; INSERT INTO employee VALUES ( 300, 'Peter Jenny', 10, 'New Anchor'); INSERT INTO employee VALUES ( 400, 'Sarah Smith', 400, 'President'); INSERT INTO employee VALUES ( 500, 'Tim Yuan', 200, 'Chair of the board');

  drop table ttt; create table ttt (tno int, tname varchar(100)); drop table sss; create table sss (sno int, sname varchar(100)); insert into sss values(10, 'just a test'); insert into sss values(20, 'be cool'); drop trigger circle; create trigger circle instead of update on sss FOR EACH STATEMENT declare x string; begin x = 'so what'; insert into ttt values(:old.sno, :new.sname); end; update sss set sname = 'no more test' where sno = 20; select * from ttt; select * from sss;

2.3.16 CREATE USER

• Purpose Use the CREATE USER statement to create a database user or an account through which one can log in to the database.

• Syntax

  CREATE USER [< catalog name>. ] < user name> [IDENTIFIED BY < password string>]

• Usage

  CREATE USER sarah

  This will create a user sarah with password sarah under the default database. When a user is created, the system will automatically create a schema with the same name as the user.

  CREATE USER other_database.sarah IDENTIFIED BY 'headf123dfa'

  This will create a user sarah under the database other_database with password headf123dfa

  Please advise new users to change the password immediately using ALTER USER.

  You must have the CRATE ANY USER system privilege to use this command.

  Note that a user is defined with respect to a given database, and consequently, the default schema for the user must be under the given database.

  See Also: DROP USER

2.3.17 CREATE VIEW

• Function Create a view, a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

• Syntax

  CREATE VIEW < view name> [< column name> {, < column name> }] AS < subquery> [ WITH <READ ONLY|CHECK OPTION> ]

  Note that the order by clause may be accepted in a create view statement, but it will be ignored.

• Usage

  CREATE VIEW database.sarah.all_tables AS SELECT t.table_catalog,t.table_schema,t.table_name, t.degree FROM system.definition_schema.tables as t, system.definition_schema.table_privileges as p, WHERE ( t.table_catalog = database AND t.table_schema = sarah ) OR ( p.grantee = sarah AND p.table_catalog = t.table_catalog AND p.table_schema = t.table_schema AND p.table_name = t.table_name ) WITH READ ONLY

  This will create a view under schema sarah that contains information of all the tables accessible to sarah.

  To create a view in a schema, including your own schema, you must have CREATE VIEW schema privilege on the schema, unless you have CREATE ANY VIEW system privilege.

A view created using a query expression (QE) is updatable if all of the following conditions are satisfied.

• QE must be a simple query expression without (a) any relational operators, such as UNION, EXCEPT, (b) it shall not contain any aggregate functions and GROUP BY, HAVING clauses.
• The SELECT clause in QE must be a list of column references, that is, direct references to columns of the tables specified in the from clause.

• For the time being, QE cannot be a join of two or more tables, and the view is defined without CHECK OPTION.

2.3.18 DEALLOCATE

• Function Deallocate (drop) a prepare statement

• Syntax

  DEALLOCATE < statement name>

• Usage

  DEALLOCATE find_student;

  See also PREPARE, EXECUTE STATEMENT.

2.3.19 DECLARE CURSOR

• Function Define a session cursor

• Syntax

  DECLARE < cursor name> CURSOR
FOR < subquery>
[ < order by clause> ]
[FOR <READ ONLY|UPDATE|SEARCH ONLY> ]

• Notes
  • All cursors in LogicSQL are scrollable, that is, the fetchh operation of a cursor can specify either NEXT, PRIOR, or any row through ABSOLUTE. See the FETCH statement
  • For now, LogicSQL supports only INSENSITIVE cursor, that is, any significant change to the underlying tables is not visible.
  • LogicSQL supports updatable cursors.

• Usage

  DECLARE current_student CURSOR FOR SELECT * FROM students FOR READ ONLY;

  This will declare a cusor that can be used later by OPEN CURSOR, CLOSE CURSOR, and FETCH statements.

  We may also use this statement to declare a cursor for text queries, shown below.

  DECLARE search_paper CURSOR FOR SELECT document_id FROM text_files WHERE contains(author, 'John Done', 1) > 0 OR contains(anchor, 'database', 2) > 0 OR contains(content, 'database' & 'b-tree', 3) > 0 ORDER BY 3 * score(1) + 5 * score(2) + 2 * score(3) FOR SEARCH ONLY

  See also OPEN CURSOR, DROP CURSOR, CLOSE CURSOR, and FETCH statements.

A cursor can be classfified by one of the following properties.

• A cursor is holdable or not. Various properties specified for a cursor determines whether or not it is a holdable cursor.
• A cursor is in eihter an open or close state.
• A cursor is updatable or not updatable. A cursor is not updatable if the table it identifies is not updatable or INSENSITIVE is specified for the cursor. Otherwise, the cursor is updatable.

• Example 1

  The following example demonstrates how to use cursors in a stored procedure.

  /* * This example demonstrates how to use cursors in a stored procedure. */ select 'Demo Cursors in Procedures ' from dual; drop table student; drop procedure get_top_gpa; CREATE TABLE student ( sid int, name varchar(64), major varchar(10), gpa numeric(5, 2), primary key(sid) ); /* * This procedure can be used to obtain the average gpa for the specified number * of students. * */ create procedure get_top_gpa (no_sid integer, gpa out numeric) declare i integer; gpa_sum numeric; one_gpa numeric; begin declare gpas cursor for select gpa from student order by gpa for read only; open gpas; gpa_sum = 0; i = 0; sqlstate = 1; while ( :i < :no_sid and sqlstate = 1 ) do if i = 0 then fetch first gpas into :one_gpa; else fetch next gpas into :one_gpa; end if; if (sqlstate = 1) then gpa_sum = one_gpa + gpa_sum; i = i+1; end if; end while; if ( i>0 ) then :gpa = gpa_sum / i; else :gpa = 0; end if; close(gpas); end; insert into student values( 10, 'susan', 'cs', 3.6 ); insert into student values( 20, 'peter', 'ee', 2.8 ); insert into student values( 30, 'sarah', 'md', 3.8 ); insert into student values( 40, 'tom', 'ce', 1.8 ); call procedure get_top_gpa(2);

2.3.20 DROP INVERTED CURSOR

• Function Close an inverted cursor for the current session

• Syntax

  DROP INVERTED CURSOR < cursor name>

• Notes

2.3.21 CREATE INVERTED CURSOR

• Function DECLARE and OPEN an inverted cursor for the current session

• Syntax

  CREATE INVERTED CURSOR < cursor name>
FOR < subquery>
[ < order by clause> ]


• Usage

  It is a difficult but very interesting task to properly order the federated search based on different inverted indexes.

  The inverted cursor of LogicSQL Text and Search, based on the top k query algorithms provides a powerfull tool for the federated search.

  Consider the following table, togehter with three inverted indexes created on different columns of the table.

  create table text_files( t_id int, t_title varchar(100), t_anchor char(1000), content clob, primary key(t_id) ); create inverted index inverted1 on text_files(content); create inverted index inverted2 on text_files(t_anchor); create inverted index inverted3 on text_files(t_title);

  The federated search shall be based on three indexed columns but with different weights for obvius reasons, which can be easily expressed with the following inverted cursor.

  CREATE INVERTED CURSOR search FOR SELECT t_id, t_title, t_anchor FROM text_files WHERE contains(t_title,'database', 1) OR contains(t_anchor, 'database'|'file management', 2)>0 OR contains(content, 'database' & 'file', 3)>0 ORDER BY 6 * score(1) + 2.5 * score(2) + 1.5 *score(3)

  The main advantages of INVERTED CURSOR are the following

  • Becuase the inverted cursor is implemented using the top k algorithm, the performance of evaluating an inverted cursor with say five (5) inverted indexes is much faster than evaluating a regular query with five (5) contains functions in its where clause, and
  • Using CURSOR_TABLE, the developer can easily join any other tables with the first 20 rows of the cursor, the next 20 rows of the cursor, and so on, which is a usefull feature in a search engine.
• Restrictions

  The inverted cursor subjects to the following restrictions:

  • Only one text file is allowed in the cursor and all the contains in the cursor must be specified on the same document. The reason behind this restriction is obvious: it does not make sense to rank documents in different text files.
  • The where clause in the cursor must be the disjunctions of various contains functions. Since we are going to rank the docuemnts according to all the contains, the conjunction of contains formulas will not serve our purpose.
  See also DROP INVERTED CURSOR, CURSOR_TABLE, and FETCH statements. current session and will be dropped automatically when the session terminates.

2.3.22 DELETE

• Function Delete one or more tuples from the given table.

• Syntax

  DELETE FROM < table name> [WHERE < search condition>]

  DELETE FROM < table name> WHERE CURRENT OF < cursor name>

• Usage

  DELETE FROM sarah.take where course = 391 DELETE sarah.take where course in (SELECT course FROM courses WHERE term = 'Fall 2007')

  This will delete all tuples from sarah.take whose course = 391.

2.3.23 DROP CURSOR

• Function Drop a session cursor

• Syntax

  DROP < cursor name>

• Usage

  DROP current_student;

  This will drop the cursor with the given name.

  A cursor, after its declaration, can be opened and closed repeately. It is valid until the end of the current session or being either explicitly dropped by the DROP CURSOR statement.

  Note that DROP CURSOR will remove the cursor definition from the system while a cursor closed by the CLOSE CURSOR statement may be opend again using the OPEN CURSOR statement.

See also DECLARE CURSOR, OPEN CURSOR, CLOSE CURSOR, and FETCH statements.

2.3.24 DROP DATABASE

• Function Remove a database (catalog) from the database.

  See Also: CREATE DATABASE

• Syntax

  DROP CATALOG < catalog name>

• Usage

  DROP CATALOG her_database

  This will drop the database named her_database and all the data objects stored in the database.

  See Also: CREATE DATABASE One needs CREATE ANY CATALOG privilege to create and/or drop a catalog.

2.3.25 DROP INDEX

• Function Remove an index from the database.

  See Also: CREATE INDEX

• Syntax

  DROP INDEX < index name>

• Usage

  DROP INDEX customer_name_index

  This will drop the index

  See Also: CREATE INDEX

One can not drop an index without CREATE INDEX schema privilege on the schema or CREATE INDEX system privilege.

2.3.26 DROP PROCEDURE

• Function Remove a stored procedure from the database.
• Syntax DROP PROCEDURE < procedure name>

  One needs the CREATE PROCEDURE schema privilege or CREATE ANY PROCEDURE system privilege to drop a procedure.

See Also: CREATE PROCEDURE

2.3.27 DROP ROLE

• Function Remove a role from the database. When a role is dropped, LogicSQL revokes it from all the users and roles to whom it has been granted and removes it from the database.

• Syntax

  DROP ROLE < role name>

• Usage

  DROP ROLE regular_user

  See Also: CREATE ROLE, GRANT ROLE, REVOKE ROLE.

• Prerequisites You must have been granted the role with the ADMIN OPTION or you must have DROP ANY ROLE system privilege.

2.3.28 DROP SCHEMA

• Function Remove a schema (not a user) from the database.

  When a schema is dropped, all the database objects in the schema will also be dropped.

  See Also: CREATE SCHEMA

• Syntax

  DROP SCHEMA < schema name>

• Usage

  DROP SCHEMA database.sarah

  See Also: CREATE USER

Only a system user (system ) is authorized to use this command.

2.3.29 DROP SEQUENCE

• Function Remove an existing sequence from the database.

• Syntax

  DROP SEQUENCE < sequence name>

  LogicSQL will commit the current transaction following the CREATE SEQUENCE and/or DROP SEQUENCE statement.

• Usage

  DROP SEQUENCE employee_id_sequence

  See Also: CREATE SEQUENCE.

• Prerequisites You must have DROP SEQUENCE privilege to drop a sequence in your own schema, and DROP ANY SEQUENCE privilege to drop a sequence in another user's schema.

2.3.30 DROP TABLE

• Function Remove a user table from the database.

  See Also: CREATE TABLE

• Syntax

  DROP TABLE < table name>

• Usage

  DROP TABLE database.sarah.testing

  This will drop table testing owned by the schema database.sarah.

  Note that when a table is dropped, all indexed created on the table as well as all triggers that are triggered by an event on the table will also be dropped.

  Only the owner of the table (and a system user) may use this command to remove the specified table.

  See Also: CREATE TABLE.

2.3.31 DROP TRIGGER

• Function

  Remove a database trigger from the database.

• Syntax

  DROP TRIGGER < trigger name>

• Usage

  DROP TRIGGER personnel_dept.salary_increase_trigger

  This will drop the trigger owned by the schema personnel_dept.

  See Also: CREATE TRIGGER. Only the owner of the table (and a system user) may user this command to remove the specified trigger.

2.3.32 DROP USER

• Function Remove a user (schema) from the database.

  When a user is dropped, the corresponding schema and all the database objects in the schema will also be dropped.

  See Also: CREATE USER

• Syntax

  DROP USER < user name>

• Usage

  DROP USER database.sarah

  See Also: CREATE USER

You must have the DROP ANY USER system privilege to use this command.

2.3.33 DROP VIEW

• Function Remove a view from the database. You can change the definition of a view by dropping and re-creating it.

  See Also: CREATE VIEW

• Syntax

  DROP VIEW < view name>

• Usage

  DROP VIEW database.yuan.all_tables

  One can not drop a view without CREATE VIEW on the schema or CREATE ANY VIEW system privilege.

2.3.34 EXECUTE PROCEDURE

• Function Execute a given stored procedure

• Prerequisites

  To execute a procedure in your own schema, you must have EXECUTE PROCEDURE schema privilege. To create a procedure in another schema, you must have EXECUTE ANY PROCEDURE system privilege.

  Any one can execute a procedure in his/her own schema, but cannot execute a procedure in another schema, unless an explicit authorization of EXECUTE on the procedure is granted.

• Syntax

  <execute procedure> ::= EXECUTE PROCEDURE < procedure name> ( < parameter list> )

  The paramter list must match the list of all non-out paramers, in the same order, specified in the procedure definition.

• Keywords and Explanations
• Usage

  After a stored procedure has been created, it can be called from any environment from which an SQL statement can be issued.

  The value of all the output parameters will be returned by the LogicSQL server in the format of

  0000008234 PROCEDURE OUTPUT 2|x|integere|0|y|string|be cool|

  where the first intger following OUTPUT stands for the number of output parameters, '|' stands for SEPARATOR, and the output value for each out-paramer will be specified by three items, i.e., the variable name, its data type, and its value. If the value of a variable is undefined, then a variable of the form $VAR(0) will be returned.

  For example, the above output represents the fact that the procedure has two out-variables, x of the integer type and y of the string type, and the returned values for x and y are 0 and 'be cool' respectively.

  See Also: CREATE PROCEDURE, DROP PROCEDURE.

2.3.35 EXECUTE STATEMENT

• Function Execute a prepare statement

• Syntax

  EXECUTE [SELECT] STATEMENT < statement name> (< parameter list>)

• Usage

  SELECT is opetional and mainly used to inform various interfaces to receive the result set.

  EXECUTE SELECT STATEMENT find_student(12345);

  See also PREPARE, EXECUTE STATEMENT.

2.3.36 FETCH

• Function Fetch a specified number of rows from the result set pointed by the given cursor.

• Syntax

  FETCH [< fetch orientation>] [FROM] < cursor name> [< target specification>]

  <fetch orientation> ::= NEXT|PRIOR|FIRST|LAST|CURRENT| <ABSOLUTE|RELATIVE>['-']< integer>] | <START> integer <RELATIVE> < integer>

  <target specification> ::= INTO < variable list>

• Syntax Rules
  • If the <fetch orientation> is omitted, then NEXT is implicit.
  • If the <target specification> is not specified, LogicSQL returns a specified rows to the client in the same way as for the SELECT statement.
• Usage

  Note that for ABSOLUTE and RELTIVE, the integer representing the distance is required.

  FETCH RELATIVE 10 student_cursor;

  This will fetch 10 rows, starting from the current row, from the result set pointed by the given cursor. After each fetch, the cursor advances to the next row (or, next specified number of rows) in the result set.

  FETCH START 100 RELATIVE 10 student_cursor;

  This will fetch 10 rows, starting from the 100th row, that is, from 100th row to 109th row, from the result set.

  After each fetch, the cursor advances to the next row (or, next specified number of rows) in the result set.

  The result set returned by the FETCH statement will be in the same format as the SELECT statement.

  The FETCH ... INTO statement will fetch the first row into the given variable list, one row at a time, and the fetched column values in the result set must correspond to the number of variables and their data types in the variable list. Of course, the FETCH ... INTO statement will also advance the cursor to point into the next row in the result set.

  FETCH student_cursor INTO :s_id, s_name, s_gpa;

  This will fetch the first row of the result set pointed by the given cursor and store the values into the corresponding variables.

  The FETCH ... INTO statement can only be used by stored procedure, as specified by CREATE PROCEDURE. See the fetch-procedure example.

If the cursor is not in the open state then an exception is raise: invali cursor state.

See also DECLARE CURSOR, OPEN CURSOR, and CLOSE CURSOR.

2.3.37 GRANT PRIVILEGE

• Function Grant access privileges to a list of users

• Syntax

  GRANT < privileges > TO < grantee> [ { , < grantee> }... ] [ WITH GRANT OPTION ]

• Usage

  GRANT SELECT, UPDATE on my_database.your_schema.her_table TO PUBLIC

  This will grant the SELECT and UPDATE privileges on table my_database.your_schema.her_table to all users of the system, without the grant option.

  According to its scope, privileges can be classified as

  • system privileges : valid for all schemas, such as create any view, create any index, and all schema privileges;
  • schema privileges: valid for a specified schema, with the default as the grantees own schema, such as create view and create index;
  • object privileges: valid for the specified objects, such as select on her_account.

  For obvious reasons, the following object privileges are granted to all users on all objects in their own schema:

  select, insert, delete, update, create table.

  See also REVOKE PRIVILEGE.

• Prerequisites To grant an object or schema privilege, you must own the object or schema or the owner of the object or schema must have granted you the privileges with the GRANT OPTION. This rule applies to users with any privileges.

  To grant a system privilege, you must either have been granted the system privilege with the GRANT OPTION or have been granted GRANT ANY PRIVILEGE system privilege.

2.3.38 GRANT ROLE

• Function To grant roles to users and roles

• Syntax

  GRANT < role name > [{, < role name> } ... ] TO < grantee> [ { , < grantee> }... ] [ WITH ADMIN OPTION ]

• Usage

  Assume we are going to create a role called sys_dba and will grant all schema privileges to {sys_dba}, and then grant this role to a user my_assistant. Logon as system, one can use the following sequence of SQL-statements. CREATE USER my_assistant; CREATE ROLE sys_dba; GRANT ALL SCHEMA PRIVILEGES TO sys_dba; GRANT sys_dba TO my_assistant; This will accomplish the task. After logon to LogicSQL, my_assistant can then do almost anything about this database. However, my_assistant can choose to disable this role by issuing SET ROLE ALL EXCEPT sys_dba; This will disable the role sys_dba.

  This will grant the role named regular_user to john and sarah without the admin option.

• Prerequisites You must either have been granted the role with the ADMIN OPTION or have been granted GRANT ANY ROLE system privilege. See also Security Model.

  See Also: DROP ROLE, GRANT ROLE, REVOKE ROLE.

2.3.39 INSERT

• Function Insert a tuple or a set of tuples as specified by a subquery in to the given table.

• Syntax

  INSERT INTO < table name> [(< column list>)] VALUES (< literal value> [{, < literal value>}] )

  or,

  INSERT INTO < table name> [(< column list>)] < subquery>

• General Rules

  The INSERTION BY QUERY, as specified by the second definition above, may involve a large number of disk operations due to the size of the source tables in the subquery.

  To avoid seriously slow down the system performance, it should be used in an atomic transaction, unless the size of the result set of the subquery is less than the MaxTransactionSize, a configuration variable whose value can be modified.

  LogicSQL will rollback the transaction and return an error message if the number of rows to be inserted in INSERTION BY QUERY within an active transaction exceeds MaxTransactionSize.

• Usage

  INSERT INTO sarah.take VALUES ( 'Peter', 391, 'B' )

  This will insert the tuple into sarah.take.

  Consider a table created with the following statement

  CREATE TABLE schedule (sno int default 100 not null, sname varchar(100))

  Then the following INSERT statement

  INSERT INTO schedule (sname) VALUES( 'nothing to do' )

  will insert a new tuple <100, 'nothing to do'> into the table.

2.3.40 OPEN CURSOR

• Function Open a session cursor

• Syntax

  OPEN < cursor name>

• Usage

  OPEN current_student;

  This will open the given cusor and point the current row before the first row of the underline SELECT statement.

See also DECLARE CURSOR, CLOSE CURSOR, and FETCH statements.

2.3.41 PREPARE

• Function Prepare (create) a prepared statement

  A prepared statement, similar to a stored procedure, is an executable program stored inside the LogicSQL server that can be used to optimize performance. Unlike a stored procedure which specifies a sequence of SQL-statements and other programming statements, a prepared statement represents a single SQL statement, such as SELECT and UPDATE.

  Prepared statements can take parameters, that is, values that are substituted into the statement when it is executed. The parameters in a prepared statement are represented by position by position using $1, $2, etc. When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to EXECUTE STATEMENT for more information about that.

  When this prepare statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed.

  A prepared statement can be dropped using the DEALLOCATE statement.

• Syntax

  PREPARE < statement name> FROM < prepare statement>

  <prepare statement> ::= any SELECT, UPDATE, DELETE, and INSERT statement with parementers represented by $1, $2, ..., etc.

• Usage

  PREPARE find_student FROM SELECT * FROM student WHERE sid = $1; EXECUTE STATEMENT find_student (100); DEALLOCATE STATEMENT find_student;

  See also DEALLOCATE, EXECUTE STATEMENT.

2.3.42 RELEASE SAVEPOINT

• Syntax

  RELEASESAVEPOINT < savepoint name>

• Description

  Release an establish a savepoint

• Usage

  RELEASE SAVEPOINT last_mile_stone

  See also SAVEPOINT and ROLLBACK.

2.3.43 REMOVE LOB

• Function Remove a BLOB/CLOB with the given lob-locator

• Syntax

  REMOVE LOB WITH < loblocator>

• Usage

  This will statement is used to retrieve a new lob locator for uploading a BLOB or CLOB into the server.

  LogicSQL will automatically generate a unique lob-locator whenever requested.

  The statement will return a lob locator when it succeeds, and raise an error code when it fails.

2.3.44 REQUEST LOBLOCATOR

• Function Request a new lob locator for uploading a BLOB/CLOB

• Syntax

  REQUEST LOBLOCATOR

• Usage

  This will statement is used to retrieve a new lob locator for uploading a BLOB or CLOB into the server.

  LogicSQL will automatically generate a unique lob-locator whenever requested.

  The statement will return a lob locator when it succeeds, and raise an error code when it fails.

2.3.45 REVOKE PRIVILEGE

• Function Revoke access privileges from a list of users

• Syntax

  REVOKE < privileges > FROM < grantee> [ { , < grantee> }... ]

• Usage

  REVOKE SELECT, UPDATE on my_database.your_schema.her_table FROM peter

  This will revoke the SELECT and UPDATE privileges on table my_database.your_schema.her_table from peter.

  Note that the revoke statement will revoke all privileges that implied by the specified privileges from all the specified grantees, and revoke all privileges that imply the specified privileges.

  For example, revoke all schema privileges from someone will revoke all privileges granted to someone.

  Only the owner of the objects, or those who originally granted the privilege may revoke the privilege. All default privileges on the owners schema cannot be revoked.

  See also GRANT PRIVILEGE.

2.3.46 REVOKE ROLE

• Function Revoke role authorizations from a list of uses and roles

• Syntax

  REVOKE [ADMIN OPTION FOR] < role name> [{, < role name>} ...]
FROM < grantee> [ { , < grantee> }... ]
[ GRANTED BY <CURRENT_USER|CURRENT_ROLE>]

• Usage

  REVOKE students FROM peter This will revoke a role named students from peter.

• Prerequisites

  You must have been granted the role with the ADMIN OPTION or you must have DROP ANY ROLE system privileges.

  See Also: CREATE ROLE, DROP ROLE, GRANT ROLE, GRANT PRIVILEGE.

2.3.47 ROLLBACK

• Syntax

  ROLLBACK

  ROLLBACK TO SAVEPOINT < savepoint name>

• Description

  Rolls back the current transaction. All the updates made by the current transaction are discarded.

  Warning: This may cause cascading roll backs: All the transactions that are affected by a rolled back transaction will be forced to roll back.

• Usage

  ROLLBACK

  Note

  Use COMMIT to successfully terminate a transaction.

  See also SET TRANSACTION, COMMIT and SAVEPOINT.

2.3.48 SAVEPOINT

• Syntax

  SAVEPOINT < savepoint name>

• Description

  Establish a savepoint for the transaction management.

• Usage

  SAVEPOINT first_mile_stone

  See also RELEASE SAVEPOINT and ROLLBACK.

• Examle

  The following example demonstrates the effects of the SAVEPOINT and ROLLBACK statements.

  SET AUTO COMMIT OFF; CREATE TABLE student ( sid INT, name VARCHAR(64) NOT NULL, major VARCHAR(10), gpa NUMERIC(5, 2), PRIMARY KEY(sid) ); INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math'); SAVEPOINT temp; INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null); INSERT INTO student (sid, name, major) VALUES (55003, 'Susan', 'Law'); ROLLBACK TO SAVEPOINT temp; INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Buines');

  After the insertion of the first row, a SAVEPOINT temp was declared, and therefore, the rollback statement will only rollback all the update statements after the declaration of the savepoint.

  The student table will be populated as following:

  LogicSQL> SELECT * FROM student; sid name major gpa ------------------------------- 12001 Sarah Math 54321 Bob Buines

2.3.49 SET LANGUAGE

• Function to set the language code for the current session. Currently, LogicSQL support the following language code:
  • UTF8
  • GBK
  with the default code is GBK.

• Syntax

  SET LNAGUAGE <UTF8|GBK>>

• Usage
  • SET LANGUAGE UTF8 > will assume all the strings are coded according to the UTF8 standard.

2.3.50 SET ROLE

• Function
• Purpose

  To enable and disable roles for your current session.

• Prerequisites You must already have been granted the roles to be enabled in the SET ROLE statement. See also Privilege Domains

• Syntax

  SET ROLE < role list> | ALL [ EXCEPT < role list> ] | NONE >

  <role list> ::= < role name> [ { < role name> ... } ]

• Usage
  • SET ROLE < role list> enables the given list of roles for the current session. Any roles not listed are disabled for the current session.

  • SET ROLE NONE disables all roles for the current session.

  • SET ROLE ALL enables all roles granted to you explicitly or through other roles for the current session.
  • SET ROLE ALL EXCEPT < role list> enables all roles granted to you for the current session, except those listed in the EXCEPT clause.

    Note that roles listed in the EXCEPT clause must be roles granted directly to you; they cannot be roles granted to you through other roles. Any roles, however, granted to you only through roles listed in the EXCEPT clause will not be enabled.

  Note that the number of roles to be enabled shall not exceed the limit specified by the configuration parameter MaxEnabledRoles for the current session.

• Examples

  SET ROLE regular_student

• Related Topics

  Security Model
CREATE ROLE
GRANT ROLE

2.3.51 SELECT and Subquery

• Function Retrieve data from one or more tables and/or views.
• Prerequisites One must have SELECT privileges on the table/view unless it is in one's own schema.
• Syntax

  <query> ::= subquery [< order by clause>]

  A subquery specifies a table that can be retrieved from tables and/or views. It is used to specify the SELECT statement as well as many other statements, such as CREATE VIEW, CREATE TABLE, and INSERT.

  <subquery> ::=

SELECT [< select quantifier>] [<DISTINCT|ALL> ] < * | < derived column> [{,< derived column> }] >
FROM < table reference> [ , < table reference> ]
[WHERE < search condition>]
[GROUP BY < item reference> {, < item reference> }]
[HAVING < search condition >]
[ <UNION|UNION ALL|EXCEPT|MINUS|INTERSECT> ( subquery ) ]


  <order by clause> ::= ORDER BY < value expression> [ASC|DESC] [{,< value expression>[ASC|DESC] }...]

  <select quantifier> ::= [TOP < integer>] [LIMIT TO < integer>]

• Order By

  An ORDER BY clause is used to order the set of all rows returned by a query. An ORDER BY clause may specify multiple expressions. LogicSQL first sorts the rows according to their values on the first expression, and then sorts the rows with the same values on the first expression according to their values on the second expression, and so on. An expression can be either an expression appearing in the SELECT clause, or a relative position that the corresponding expression holds in the SELECT clause.

  Using the position is useful for you need not repeat the same expression in the order by clause. Also, one must use positions, rather than expressions in the query that involves relational algebra operators, such as UNION, EXCEPT, and INTERSECT.

  Note that, for now, the order by clause cannot use correlation names for specifying the columns to be sorted.

• Select Quantifier

  The SELECT QUANTIFIER clause specifies the number of rows returned by a query, and the number of rows that processed by the query processor of LogicSQL. This clause, however, has no effect on subqueries used in both CREATE VIEW and CREATE TABLE.

  Example 1 To select the top 10 rows from STUDENT, one issues SELECT TOP 10 * FROM student

  This will return the first 10 rows of the query to the client. It will return all rows if the number of rows is less than or equal to 10.

  Using the top clause together with the order by clause, one can select the first N rows in a sorted result table.

  Example 2 The following query selects the top 5 students according to their GPA. SELECT TOP 5 * FROM student ORDER BY gpa DESC

  Note that the TOP clause works properly only in the SELECT statement, but not in other uses of subquery statements because the ORDER BY clause is not supported for these applications. With the order by clause, the rows returned are not necessarily the top ones.

  The LIMIT TO clause specified the max number of rows LogicSQL' may consider during the query evaluation process. The default number of rows considered is 100000, and this can be changed by the configuration variable TUPLE_LIMIT.

  One may change this limit for a particular query by adding LIMIT TO 100, for example, in the SELECT statement.

  Example 3 The following query will select at most 100 rows in each of its evaluation steps, and thus the number of rows returned will not exceed 100. SELECT LIMIT TO 100 * FROM student

  Note that the following constraints applied to the SELECT statement:

  • All items in the group by list must be < item reference>, i.e, columns. Of source, it cannot be a column with an aggregate function, and all the none-aggregate columns must be in the group by list.
  • All aggregate functions are applied only to < item reference>, i.e. columns, without DISTINCT.
  • An order by specification is specified using (Value_Exp, Order_Specification), where Value_Exp is either an integer referencing one column in the result set or a value expression appearing in the SELECT clause; while Order_Specification can be either ASC or DESC with the default being ASC.

  The labels of all the derived columns are column names in the answer tables. You can specify the label for a derived column using [AS] <correlation name>. Otherwise, LogicSQL will use the default label for the derived column.

  If a derived column is a column of a table in the from clause, then its default label will the column name. The default name of the derived column that is an expression will be constructed from the given expression.

  The length of a label in LogicSQL is limited to the configuration parameter MAX_LABEL_LENGTH, and thus one has to specify a label for any derived column of an expression if the length of the expression exceeds this limit.

  [TOP <integer>] is used to specify the number of rows to be returned.

• Nested Queries

  LogicSQL supports the subqueries nested in the both where and having clauses , as specified above.

  The following example demonstrates how it works.

  Example 4 drop table ttt; create table ttt (tno int, tname varchar(100)); insert into ttt values(100, 'be cool'); insert into ttt values(200, 'so what'); insert into ttt values(300, 'are you crazy'); insert into ttt values(400, 'just say no'); drop table sss; create table sss (sno int, sname varchar(100)); insert into sss values(100, 'so be it'); insert into sss values(100, 'be cool'); insert into sss values(200, 'so what'); insert into sss values(300, 'are you crazy'); select * from ttt; select * from sss; select * from ttt where tno in (select sno from sss where sname = 'be cool' or sno = 300) or tname = 'just say no'; select * from ttt where tno in (select sno from sss where sname = 'be cool' or sno = 300 ); select * from ttt where tno in ( select sno from sss where tname = 'so what' ); select * from ttt where tno in ( select sno from sss where tname = sname ); select * from ttt where tno in (select sno from sss where sno = 100) or tno in (select sno from sss where sname = 'are you crazy' );

• Joins

  A join is a query that combines rows from two or more tables and/or views. LogicSQL performs a join whenever multiple tables appear in the query's FROM clause.

  Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition .

  In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

  Examle 5 Consider a query to list all students' name for each course with at lease one student registered. The query can be expressed by the following join operation.

  SELECT r.cid, s.name FROM registration r, student s WHERE r.sid = s.sid The query result is shown below: cid name ---------------- BS499 Bob CS291 Bob CS291 Sarah MA101 Sarah

  Example 6 Now consider the query to list the number of all regitered students for each course, and the following SQL query.

  SELECT cid, count(sid) FROM registration GROUP BY cid The query result is shown below: cid count(sid) --------------------- BS499 1 CS291 2 MA101 1

  This SQL statement, however, fails to list the number of regitered students for any courses with no student regitered yet.

• Outer Joins

  The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join.

  Using outer join, we are able to expression the privous query.

  Example 7 Now conider the query to list the number of all regitered students for each course, and the following SQL query.

  SELECT c.cid, count(r.sid) FROM course c left join registration r on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  A subquery in the FROM clause may also be used to expression joined queries, as demonstrated below:

  SELECT c.cid, count(r.sid) FROM ( select * from course) as c left join registration r on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  Example 8 The following uses the right join to list the number of all regitered students for each course, and the following SQL query.

  SELECT c.cid, count(r.sid) FROM registration r right join course c on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  Similarly, we have the following query using the subquery in the FROM clause with a right join.

  SELECT c.cid, count(r.sid) FROM (SELECT * FROM registration) r right join course c on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  Example 9 The following demonstrates the use of outer joins in nested queies.

  SELECT * FROM course WHERE cid in ( SELECT c.cid FROM course c left join registration r on r.cid = c.cid ); cid title description --------------------------------------------------------- AR100 Cartoon Drawing A commic book BS499 Marketing How to make quick bucks CS291 Introduction to DBMS first database course MA101 Calculus An easy course

  Example 10 The following example shows that the natural join can be specified without giving the join condition, as long as both tables have one and exact one attributes wth the same name.

  SELECT student.sid, name, cid, grade FROM student NATURAL JOIN registration; student.sid name cid grade -------------------------------------- 12001 Sarah CS291 12001 Sarah MA101 D 54321 Bob BS499 B 54321 Bob CS291 A

  There are two approaches to expression an outer join in LogicSQL.

  • SQL99 Style

    The following example uses an outer join to extend the join of students and registration.

    SELECT s.name, c.title FROM student s left outer join registration r on s.sid = r.sid, course c WHERE r.cid = c.cid

    This query returns all course titles with their students names, even if a course has no registered students.

  • Oracle Style Note that the Oracle style has not yet been implemented!.

    The following example demonstrates a different approach to express the above query.

    SELECT s.name, c.title FROM student s right, registration r, course c WHERE s.sid (+) = r.sid and r.cid = c.cid

  To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, LogicSQL returns NULL for any select list expressions containing columns of B.

  This is the basic syntax of an outer join of two tables from Oracle.

  Outer join queries are subject to the following rules and restrictions:

  • The (+) operator can appear only in the WHERE clause, not in the select list, and can be applied only to a column of a table or view.
  • If A and B are joined by multiple join conditions, the (+) operator must be used in all of these conditions.
  • The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator.
  • A condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
  • A condition cannot use the IN comparison operator to compare with another expression a column marked with the (+) operator.
  • A condition cannot compare with a subquery any column marked with the (+) operator.

  Example Consider a simple table created by the following statement: create table ttt(tno int, tname varchar(100)) The following are all valid SELECT statements select * from ttt; select count(*) from ttt; select avg(tno), max(tno) from ttt; select 'just a ' || 'test' , report.tno * 2 from ttt as report; select user from ttt; select tno, count(tname) from ttt group by tno; select tname from ttt order by tname asc; select * from ttt where tno = 10 + 10 or tname = 'just a test' and tno = 100; select * from ttt where not tno = 20; select * from ttt where tno <> 20; select * from ttt t1, ttt t2 where t1.tno = 20; select * from ttt t1, ttt t2 where t1.tno < t2.tno + 30; select * from ttt t1, ttt t2 where t1.tno = t2.tno + 130 or t1.tno = 20; select tno from ttt group by tno order by count(tname); select tno from ttt group by tno having count(tname) > 1; select tno * 2 from ttt; select tno , count(tname) from ttt group by tno; select t1.tno + t2.tno, count(t1.tname), count(t2.tname) from ttt t1, ttt t2 group by t1.tno,t2.tno; select t1.tno + t2.tno, count(t1.tname), count(t2.tname) from ttt t1, ttt t2 group by t1.tno,t2.tno having count(t1.tname) = count(t2.tname); select t1.tno + t2.tno, t2.tno from ttt t1, ttt t2; select tname || tname from ttt; select t1.tno + 2 * t2.tno from ttt t1, ttt t2; select tno, user from ttt; select t1.tno + 2 * t2.tno from ttt t1, ttt t2; select * from ttt t1, ttt t2 where t1.tno + t2.tno = 200; select t1.tno + t2.tno, user from ttt t1, ttt t2 where t1.tno + t2.tno = 200; select * from ttt where tno in (100, 200) and tno = 100; select * from ttt where tno in (tno * 2 -200, 150 ) or tname = null; select * from ttt where tno in (tno * 2 -200, 150 ); select 'just a ' || 'test' as title, report.tno * 2 from ttt as report; select tno as numbers, count(tname) from ttt group by tno; select * from ttt where tname in ('one more test', 'nothing much'); select * from ttt where tname not in ('one more' || ' test', 'nothing much'); select * from ttt where tname not in (null, so what'); select * from ttt where tname not like 'one more_'; select * from ttt where tname like 'one more_'; select * from ttt where tno between 10 and 50 or tno in (20, 150); select * from ttt where tname between 'abc' and so s'; select * from ttt where tname between 'nothing' and so'; select * from ttt order by tname desc; select * from ttt order by 2 desc; select tno, tname from ttt order by tno desc; select tname, sum(tno) from ttt group by tname order by sum(tno); The following queries are not supported: select avg( distinct tno ) from ttt; select sum(distinct tno) from ttt; select avg( sum(tno) * 2 ) from ttt;

2.3.52 SET AUTO_COMMIT

• Syntax SET AUTO COMMIT <ON|OFF>

• Description

  To set on (or off) the auto_commit state for the current client.

  A clients auto_commit status is on if each statement issued from the client is considered as an atomic transaction and thus the system will automatically commit or rollback the statement, unless a transaction has been previously set up by the client using the SET TRANSACTION statement.

  Note that the default auto_commit state for clients is ON.

2.3.53 SET SESSION USER

• Syntax SET SESSION USER

• Description

  Note implemented yet.

2.3.54 SET TRANSACTION

• Syntax SET TRANSACTION isolation level serializable

• Usage SET TRANSACTION isolation level serializable

• Description

  This will start a new transaction.

• Note

  See also ROLLBACK and COMMIT.

2.3.55 USE DATABASE

• Syntax USE DATABASE < catalog name>

• Description

  To set the default database to the given catalog name for the current session.

2.3.56 UPDATE

• Function Update a given table

• Syntax

  UPDATE < table name> SET < set clause list> [WHERE < search condition>]

  UPDATE < table name> SET < set clause list> [WHERE CURRENT OF < cursor name>]

  UPDATE < table name> aliasA
SET (< column name>, ..., < column name> ) =
( SELECT < column name>, ..., < column name>)
FROM < table name> aliasB
WHERE <aliasA.column_name> = <aliasB.column_name>
)

• Usage

  UPDATE sarah.take SET grade = 'B' where student = 'Peter' and course = 391

  This will update Peters grade for 391.

List of extended SQL statements/functions used to implement the LogicSQL's 2.8 extended Unix security model.

2.3.57 ADD INTO GROUP

• Function To add security certificates and groups to a group

• Syntax

  ADD INTO GROUP < group name> WITH < security name>[{,< security name> }...] <security name> ::= < certificate name>|< group name>

• Usage

  Assume that we have four security certificates, peter, sarah, susan, and tom; and that we are going to create three security groups, engineers, developers, and testers such that peter and sarah are engineers, susan and tom are testers, and both developers and testers are contained in engineers. This can be established with the following three statements: ADD INTO GROUP engineers WITH developes, testers; ADD INTO GROUP developes WITH peter, sarah; ADD INTO GROUP testers WITH developers, testers;

• Prerequisites

  You must have been granted GRANT ANY GROUP system privilege to use this statement. See also Security Model.

  See Also: CREATE GROUP, DROP GROUP, and REMOVE FROM GROUP.

2.3.58 ALTER CERTIFICATE

• Function Modify the security certificate information

• Syntax

  ALTER CERTIFICATE < certificate name > WITH PASSWORD < password string>

• Usage

  ALTER CERTIFICATE sarah WITH PASSWORD 'hgd2f23Abd'

  You must have the CREATE ANY CERTIFICATE system privilege as well as have requested the same certificate for the session to use this command.

  See Also: CREATE CERTIFICATE, REQUEST CERTIFICATE, RELEASE CERTIFICATE.

2.3.59 CREATE CERTIFICATE

• Purpose Create a security certificate, simulating the unix user, for the extended Unix security model.

• Syntax

  CREATE CERTIFICATE < certificate name> [IDENTIFIED BY < password string>]

• Usage

  CREATE CERTIFICATE sarah

  This will create a security certificate sarah with password sarah.

  CREATE USER peter IDENTIFIED BY 'headf123dfa'

  This will create a security certificate peter with password headf123dfa

  Please change the password immediately using ALTER CERTIFICATE.

  You must have the CRATE ANY CERTIFICATE system privilege to use this command.

  See Also: ALTER CERTIFICATE, REQUEST CERTIFICATE, RELEASE CERTIFICATE.

2.3.60 CREATE GROUP

• Function To create a security group.

  A (security) group, similar to a group in the Unix security model, is a security label that can be used to specify the record-based access control. See Security Model for details.

• Syntax

  CREATE GROUP < group name>

• Prerequisites

  You must have CREATE GROUP system privilege to create a group.

• Usage

  CREATE GROUP developer

  This will create a new group named developer.

See also DROP GROUP, ADD INTO GROUP, REMOVE FROM GROUP.

2.3.61 DROP CERTIFICATE

• Function Remove a security certificate.

  When a security certificate is dropped, it will be also be removed from all the security groups.

• Syntax

  DROP CERTIFICATE < certificate name>

• Usage

  DROP CERTIFCATE sarah

You must have the DROP ANY CERTIFICATE system privilege to use this command.

See Also: < ALTER CERTIFICATE>, < CREATE CERTIFICATE>,< REQUEST CERTIFICATE>, < RELEASE CERTIFICATE>.

2.3.62 DROP GROUP

• Function Remove an existing security group.

• Syntax

  DROP GROUP < group name>

• Usage

  DROP GROUP developer

  See Also: CREATE GROUP.

• Prerequisites You must have CREATE GROUP system privilege to use this statement.

See also CREATE GROUP, ADD INTO GROUP, REMOVE FROM GROUP.

2.3.63 RELEASE CERTIFICATE

• Function Release a security certificate for the current session

• Syntax

  RELEASE CERTIFICATE < certificate name>

• Usage

• Prerequisites A session cannot release a security certificate that is not currently held.

  See Also: CREATE CERTIFICATE, ALTER CERTIFICATE, REQUEST CERTIFICATE.

2.3.64 REMOVE FROM GROUP

• Function Remove security certificates and/or groups from a group

• Syntax

  REMOVE < security name> [{,< security name>}...] FROM GROUP < group name>

• Usage

  REMOVE sarah FROM GROUP developers

  This will remove the certificate sarah from any groups contained in the group developers, as well as the group developer itself.

• Prerequisites

  You must have CREATE GROUP system privileges to use this statement.

  See Also: CREATE GROUP, DROP GROUP, ADD INTO GROUP.

2.3.65 REQUEST CERTIFICATE

• Function Request a security certificate for the current session

• Syntax

  REQUEST CERTIFICATE < certificate name> WITH PASSWORD < password string>

• Usage

• Prerequisites

  See Also: CREATE CERTIFICATE, ALTER CERTIFICATE, RELEASE CERTIFICATE.

2.4 System Utility Commands

System Utility statements are LogicSQL commands used by a system user to perform system operations, such as RECOVER DATABASE and SHUTDOWN the server. It is also called System Utility Commands.

2.4.1 SET CHECKPOINT		Set the check point
2.4.2 SET ADMISSION		Set admission control level
2.4.3 RECORD SNAPSHOT		Record the snapshot in the log system
2.4.4 RECOVER DATABASE		Recover the database after crash
2.4.5 SHUTDOWN		Shutdown the database server
2.4.7 STATISTICS		Find the statistics of the server
2.4.6 STORED FACTS		Check if stored facts grow infinitely

2.4.1 SET CHECKPOINT

• Function Set the checkpoint.
  

• Syntax SET CHECKPOINT

• Usage SET CHECKPOINT

• Description

  This will set the checkpoint. That is, the system will first force the disk updates, and then record the checkpoint on the backup log.

• Note

  Only a system user can issue this statement.

2.4.2 SET ADMISSION

• Function
  

  Set the admission control level. That is, to determine which requests from clients will be rejected and which ones will be processed.

• Syntax
  

  SET ADMISSION LEVEL < admission control level>

  <admission control level> ::= OPEN | EXISTING CLIENT | WITHIN TRANSACTION | BLOCK

• Usage

  The command used by a system user to set the control level of the admission of client requests.

  1. OPEN all requests are admitted to the system.
  2. EXISTING CLIENT only requests from existing clients are admitted.
  3. WITHIN TRANSACTION only requests that are within the current transaction are admitted. That means, neither a request for new connection nor a request that starting a new transaction is admissible.
  4. BLOCK all requests will be rejected.

  For example, SET ADMISSION LEVEL WITHIN TRANSACTION will process only requests from an active transaction, and thus reject all requests from new transactions. This will allow the system to finish all current active transactions but stop all other requests.

Note that (1) a system user does not subject to the admission control, and (2) all requests will be rejected if it is not allowed at the current admission control.

2.4.3 RECORD SNAPSHOT

• Function
  

  Record SNAPSHOT in the log system to mark the starting point for crash recovery.

• Syntax
  

  RECORD SNAPSHOT

• Usage

  The command is used by a system user to mark SNAPSHOT in the log system. It should used only after a snapshot is secured in the backup media since the crash recovery cannot go beyond the mark.

Note that (1) a system user does not subject to the admission control, and (2) all requests will be rejected if it is not allowed at the current admission control.

2.4.4 RECOVER DATABASE

• Function Recover the database after crash
• Syntax

  RECOVER DATABASE [SINCE < LAST SNAPSHOT | BEGINNING > ]

• Description

  Recover the database after crash. A standard recovery process is to redo all updates of committed transactions. The database first scans the log system to construct the list of all committed transactions that need to be redone, and then execute all updates of the transactions in the list.

  There are one option for this command, which to specifies the type of recovering. It is normal recovery by default, and catastrophic recovering if SINCE <LAST SNAPSHOT|BEGINNING> is specified.

  The catastrophic recovery will be based on all the log records upto the last snapshot if SINCE LAST SNAPSHOT is specified and all the log records in the log system if SINCE BEGINNING is specified.

  See Database Recovery for details.

• Usage
  • RECOVER DATABASE recovers the database from the (default) local log system with normal recovery.
  • RECOVER DATABASE SINCE LAST SNAPSHOT recovers the database from the log system, upto the last snapshot, with catastrophic recovery.
  • RECOVER DATABASE SINCE BEGINNING recovers the database from the log system with catastrophic recovery.

• Note

  Only the system user can use this command.

  FROM STANDBY DB has yet to be implemented. (It is better to copy the log files from the site in which the standby database is located to the local log directory directly.)

2.4.5 SHUTDOWN

• Function Shutdown the database server

• Syntax

  SHUTDOWN

• Usage

  SHUTDOWN

  See also shutdown_server.

  One nees SHUTDOWN SERVER or ALL SCHEMA PRIVILEGES to use this statement.

2.4.6 STORED FACTS

• Function Display the number of various facts stored in the internal database. The increasing number implies memory leak.

  This is for the internal use of debug.

• Syntax

  SELECT ALL FROM stored_prolog_facts

  LogicSQL> select all from stored_prolog_facts; stored_fact arity number ---------------------------------------------- cleared_tid 1 1 tables 9 0 stored_procedures 8 64 table_privileges 7 0 op_record 9 0 select_record 6 0 read_by 7 2 commit_wait_for 2 0 active_transaction 1 19 committed_transaction 1 0 table_removed 4 0 current_roles 2 35 current_securities 3 0 get_key_list 7 181 get_column_list 5 161 get_datatype_list 5 161 get_default_list 5 161 get_is_nullable_list 5 161 get_unique_column_list 5 0 get_real_columns 4 161 get_used_tables 4 2 system_limit 2 28 table_constraints 9 0 crt_oid 1 4 crt_code 1 1 admission 1 2 crt_log_table 1 2 get_trigger 8 1 triggers 9 0 triggers 10 1 is_literal_list 1 1 is_literal_value 1 0 prolog_info 2 1 check_constraint_formula 7 49 get_query_term_for_unique 7 4 fk2reference 11 40 table_select_blocks 9 54 table_constraint_list 4 80 meta_data 2 0 table_created 5 329 referential_constraints 4 14 get_unique_key_list 6 4 table_index_list 4 82 table_inverted_list 4 46 get_view_table_query 6 1 view_tables 4 0 inverted_query_table 5 0 cursor_list 9 0 temp_tables 4 0 cursor_state 3 0 triggers_of_transaction 2 0 crt_sibling 3 0 sequence_orientation 7 0 client_crt_result 3 0 savepoint 3 0 prepared_delete_with_query 3 0

2.4.7 STATISTICS

• Function Display the statistics of the server
• Syntax

  SELECT * FROM statistics

  LogicSQL> select * from statistics; parameter size_used free ----------------------------------------------------- memory 7700400 0 program 3016432 3254312 global_stack 827456 443788 local_stack 212 43572 trail 79324 17576 atoms 6875 219672 atom_garbage_collection 158 58854684 choice 152 17498 runtime 3722260 1006960 no_clients 28 Current Tid 86105 No Transactions 18 Current Committed not available Current Active 86085

2.5 XML Commands

( It is currently under development. )

2.5.1 SELECT RETURN		Search the SQL database and return a table
2.5.2 FOR WHERE
2.5.3 FOR WHERE RETURN		Search the XML database and return an XML document

2.5.1 SELECT RETURN

2.5.2 FOR WHERE

2.5.3 FOR WHERE RETURN

<constructor> ::= < element constructor> | < xml comment> | < xml processing instruction> | < c data section> <element constructor> ::= "<" < q name> < attribute list> "/>" | "<" < q name> < attribute list> ">" {< element content>} "</" < q name> ">" <element content> ::= < char> | <"{{"> | <"}}"> | < element constructor> | < enclosed expr> | < c data section> | < char ref> | < predefined entity ref> | < xml comment> | < xml processing instruction> <xml comment> ::= "<!--" < string> "-->" <xml processing instruction> ::= "<?" < pi target> < string> "?>" <c data section> ::= "<![CDATA[" < string> "]]>" <pi target> ::= < identifier> <predefined entity ref> ::= "&" < IT | GT | AMP | QUOT | APOS > ";" <char ref> ::= "&#"< integer> | "x"< hex integer> <enclosed expr> ::= { < expr sequence> } <expr sequence> ::= { < expr> {, < expr> } <expr> ::= Xquery expression <q name> ::= [:] < identifier> { : < identifier>} <attribute list> ::= { q name "=" < attribute value> } <attribute value> ::= <string> ::= {< char>} <char> ::= a specified subset of ASCII letters

2.6 Security Model

• the discretionary security model as outlined in SQL99; and
• the extended Unix security model, similar to that of the Unix system, for record-based access control.