FairCom (c-tree) 数据库概要 - 2
来源:互联网 发布:武林外传电影版 知乎 编辑:程序博客网 时间:2024/06/07 03:46
4. c-tree isql utility
Interactive SQL (often referred to throughout this manual as ISQL) is a utility supplied with ctreeSQL that lets you issue SQL statements directly from a terminal and see results displayed at the terminal. You can use interactive SQL to:
l Learn how SQL statements work
l Test and prototype SQL statements to be embedded in programs
l Modify an existing database with data definition statements
l Perform ad-hoc queries and generate formatted reports with special ISQL formatting statements
4.1. SQL-92 Standard support
4.1.1. iSQL supports SQL-92 standard
1) iSQL command Options:
Graph
2) Create a table
Type the following commands to create a table:
CREATE TABLE EMPLOYEE
(
EMPID INT,
FIRSTNAME VARCHAR(30),
LASTNAME VARCHAR(30),
MALE VARCHAR(10),
AGE SMALLINT,
BIRTH TIMESTAMP,
NOTE VARCHAR(200),
DEPARTMENTID INT,
PRIMARY KEY(EMPID)
);
CREATE TABLE DEPARTMENT
(
DEPTID INT,
NAME VARCHAR(50),
DESCRIPTION VARCHAR(200),
PRIMARY KEY(DEPTID)
);
3) Insert records
Type the following commands to insert records to a table:
INSERT INTO EMPLOYEE(EMPID, FIRSTNAME, LASTNAME, MALE, AGE, BIRTH, NOTE, DEPARTMENTID) VALUES (1, 'Clark', 'Jin', 'Male', 26, '1999-05-11 13:17:11', '', 1);
INSERT INTO EMPLOYEE(EMPID, FIRSTNAME, LASTNAME, MALE, AGE, BIRTH, NOTE, DEPARTMENTID) VALUES (2, 'Johnson', 'He', 'Male', 28, '1989-05-11 13:17:11', '', 1);
INSERT INTO DEPARTMENT VALUES (1, 'STB', 'STB development team');
4) Get records
Select * from employee;
Select (firstname + lastname) as name from employee, department where employee.departmentid = department.deptid;
5) Create a index
CREATE INDEX IDX_1 ON EMPLOYEE (AGE);
4.1.2. Transaction Processing
Use command “COMMIT WORK” and “ROLLBACK WORK” to do the transaction processing.
4.1.3. System Tables
c-tree server has many system tables which are similar as SQL Server.
E.g.
l SysIndexes
l SysColumns
For detail information, please view the iSQL help document.
4.1.4. iSQL statements
iSQL has many other functionalities. E.g.
l Format outputs
l Spool outputs to file
l Execute the script file by “@” statement
For detail information, please go to the iSQL help document.
5.2. Other utilities
5.2.1. dbload utility
The dbload utility allows loading of variable- or fixed-length records, and lets the load operation specify the set of fields and records to be stored from an input file. Data files can use multiple-character record delimiters. dbload also allows control of other characteristics, such as error handling and logging, in its command line. dbload generates a badfile that contains records from the input file that failed to load in the database.
Graph
5.2.2. dbdump utility
dbdump writes the data in a database to a file. The format of the exported data is specified by
the record description given in an input command file to dbdump.
Graph
5. c-tree server SQL Reference
Because c-tree server SQL grammar supports SQL-92 standard, most commands are same as standard SQL.
5.1. Data Types
There are several categories of SQL data types:
l Character
l Exact numeric
l Approximate numeric
l Date-time
l Bit String
All the types can be transferred to SQL Server data types.
5.2. Database objects
There are several categories of c-tree server database objects:
l Column
l Index
l Table
l View
l Trigger
l Stored Procedure
l Synonym
A stored procedure example:
CREATE PROCEDURE new_sal
(
IN deptnum INTEGER,
IN pct_incr INTEGER,
)
RESULT
(
empname CHAR(20),
oldsal NUMERIC,
newsal NUMERIC
)
BEGIN
StringBuffer ename = new StringBuffer (20) ;
BigDecimal osal = new BigDecimal () ;
BigDecimal nsal = new BigDecimal () ;
SQLCursor empcursor = new SQLCursor (
"SELECT empname, sal, (sal * ( ? /100) + NVL (comm, 0)) total FROM emp WHERE deptnum = ? " ) ;
empcursor.setParam (1, pct_incr);
empcursor.setParam (2, deptnum);
empcursor.open () ;
do
{
empcursor.fetch ();
if (empcursor.found ())
{
empcursor.getValue (1, ename);
empcursor.getValue (2, osal);
empcursor.getValue (3, nsal) ;
SQLResultSet.set (1, ename);
SQLResultSet.set (2, osal);
SQLResultSet.set (3, nsal) ;
SQLResultSet.insert ();
}
} while (empcursor.sound ()) ;
empcursor.close () ;
END
5.3. SQL Functions
5.4. Unicode support
From version 7.11(current Oberon systems use c-tree server 6.10) Unicode was supported by c-tree server.
For detail information, please view the product documents.
- FairCom (c-tree) 数据库概要 - 2
- FairCom (c-tree) 数据库概要 - 1
- How to connect to Faircom c-tree database
- 数据库概要
- c-tree数据库(c-treeACE)(2):工具篇之一
- 数据库系统实现概要
- 数据库认证技术概要
- 数据库实例概要图
- 数据库&SQL基础概要
- 【数据库】MySQL记录概要
- 数据库系统概要
- 数据库的学习--概要
- 数据库架构演变概要
- c-tree数据库大量数据bulk loading
- C语言复习概要
- C 指针概要
- Objective-C 2.0概要
- Objective-C基础概要
- SIP协议全方位概要介绍
- Java 中十进制十六进制相互转换
- 做鹰眼图
- java模式之单例模式
- “月光族”薪水节流八绝招
- FairCom (c-tree) 数据库概要 - 2
- visitor模式概念——visitor模式进一步
- hadoop
- 嵌入式3D-嵌入式3D开发环境的建立-硬件
- 太小看了XXXX和XXXX
- 一个硕士生的“技术” 生涯--转载
- Excel常见错误信息
- 常用的正则
- 堆和栈的比较