项目终于结束了,总结的时候到了... hehe :)
在项目中我们遇到了很多的问题,标准SQL使用就是其中一个。 因为我们在做BI package的时候,一开始都是基于MS SQL 来做的,所以Universe的设计上也没有太多的考虑。 当后来老大告诉我偶们要同时release 一个基于 Oracle 的版本时,我们才发现问题来了。 因为在Universe里面我们使用的SQL很多都不是标准的SQL,很多都是MSSQL所独有的,也就是 T-SQL(Transcate SQL), 所以当把universe移植到Oracle上的时候,我们不得不为Oracle重新做了一套Universe。又因为Dashboard和Crystal Reports都是基于Universe来做的,所以最后我们的efforts 被扩大了一倍 ... 杯具啊!!
项目做完了,现在来研究下哪些SQL是通用的,防止以后不要犯同样的错误。 以下内容均为本人google 所得,转载喽~~ hehe~
【概念总结】
1. 基本应用上,sql server 和oracle都遵循sql 92语法标准。
2. 实际应用中大家都会超出以上标准,使用各家数据库厂商都提供的丰富的自定义标准函数库和语法。
3. 微软sql server的sql 扩展叫T-SQL(Transcate SQL).
4. Oracle 的sql 扩展叫PL-SQL.
5. 最简单的插入、删除操作都差不多,但是稍微复杂一点,都不一样了。比如涉及到跨数据表,跨数据库,跨用户的操作的语法不一样。
6. 一个很简单的例子,去数据库系统时间的写法
tsql: select getdate()
pl-sql: select sysdate from dual
【SQL 92国际标准】
1. HOWTO - SQL92 Syntax
- http://owen.sj.ca.us/rkowen/howto/sql92F.html
2. Wikipedia
- http://en.wikipedia.org/wiki/SQL-92
- http://en.wikipedia.org/wiki/SQL
【前人总结】
1. Oracle与SQL Server的几点不同
- http://www.programfan.com/blog/article.asp?id=9101
2. SQL 92语法标准
以下内容来自CSDN博客,转载请标明出处:http://blog.csdn.net/wnhoo/archive/2007/04/11/1560941.aspx
==================================================
SQL92 Syntax and common or <Oracle> additions (not = '!')
数据类型:
CHAR(n) | CHARACTER(n)
VARCHAR(n) | CHARACTER VARYING(n) | <VARCHAR2(n)>
INTEGER | INT | SMALLINT
DECIMAL(p,s) | DEC(p,s) | NUMERIC(p,s)
FLOAT(p) | REAL | DOUBLE PRECISION
DATE | TIME
INTERVAL year-month | INTERVAL day
BOOLEAN | BLOB
条件:
< > <= >= <> = AND OR NOT
IS [NOT] NULL [NOT] LIKE
[NOT] IN ( [,...] ) [NOT] BETWEEN x AND y
[conditional] ANY ( [,...] )
[conditional] ALL ( [,...] )
函数:
AVG | MAX | MIN | SUM | COUNT
GREATEST|LEAST(x,y,...)
<{ROUND|TRUNC<!ATE>}({x,places|date,format})>
POSITION( s1 IN s2)
EXTRACT( datetime FROM datetime_value)
CHAR_LENGTH( s1 ) <LENGTH( s1 )>
SUBSTRING(string FROM start [FOR length])|<SUBSTR(string,start,length)>
<INSTR(str,substr,start,mnth)>
{<INITCAP>|UPPER|LOWER}(string)
TRIM({BOTH|LEADING|TRAILING} char FROM string)|<{L|R}TRIM(str,chrset)>
{TRANSLATE|CONVERT}( char USING value) |<TRANSLATE(str,from,to)>
<{L|R}PAD(str,to_len,str2)>
<DECODE(expr,search1,result1,...[,default])>
<NVL(expr,replace)>
<Date Format - ROUND|TRUNC|TO_CHAR|TO_DATE(value,fmt)>:
SYYYY|YYYY|YEAR|SYEAR|YY|IYYY|RR|RRRR MONTH|MON|MM|RM
DDD|DD|J DAY|DY|D HH|HH12|HH24 MI SS|SSSSS
IW AM|PM BC Q WW(year week) W (month week)
Table 约束:
[CONSTRAINT cname] {{UNIQUE|PRIMARY KEY}(col,...)|
CHECK(condition)|FOREIGN KEY (col,...) REFERENCES table(col,...)}
Column 约束:
[CONSTRAINT cname] {[NOT] NULL|UNIQUE|PRIMARY KEY|
REFERENCES table(col,...) ON DELETE CASCADE|CHECK(condition)}
===================================================
数据库命令
ALTER TABLE
Description: 修改table结构
ALTER TABLE table [ * ]
ADD [<!COLUMN>] column type
ALTER TABLE table [ * ]
DROP [ COLUMN ] column
ALTER TABLE table [ * ]
MODIFY [<!COLUMN>] column { <!SET> DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
MODIFY [<!COLUMN>] column column_constraint
ALTER TABLE table [ * ]
RENAME [<!COLUMN>] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table_constraint
ALTER TABLE table
{ENABLE|DISABLE} {NO}VALIDATE CONSTRAINT constraint
ALTER USER
Description: 设置用户密码
ALTER USER username IDENTIFIED BY passwd
CREATE TABLE
Description: Creates a new table
CREATE <![TEMPORARY|TEMP]> TABLE table (
column type
[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]
[column_constraint_clause | PRIMARY KEY } [ ... ] ]
[, ... ]
[, PRIMARY KEY ( column [, ...] ) ]
[, CHECK ( condition ) ]
[, table constraint ]
)
<CREATE TABLE table AS select query>
(CREATE <OR REPLACE> TRIGGER) (非SQL92语法)
Description: Creates a new trigger
CREATE TRIGGER name { BEFORE | AFTER |INSTEAD OF}
{DELETE| INSERT | UPDATE [OF (col,...)] [OR ...] }
ON {table|view} FOR EACH { ROW | STATEMENT }
[ WHEN (condition) ]
[<!EXECUTE PROCEDURE func ( arguments )>| pl/sql block]
CREATE SEQUENCE (非SQL92语法)
Description: 创建一个序号生成器
CREATE SEQUENCE seqname [ INCREMENT BY increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache | <WITH> NOCACHE ] [ CYCLE ]
(use seqname.CURRVAL & seqname.NEXTVAL)
CREATE INDEX (非SQL92语法)
Description: Constructs a secondary index
CREATE [UNIQUE|<BITMAP>] INDEX index_name ON table
[<!USING acc_name>] ( column [ASC|DESC][,...]) [NOSORT|REVERSE]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
(CREATE <OR REPLACE> VIEW)
Description: Constructs a virtual table
CREATE VIEW view AS select query
ALTER VIEW view COMPILE
CREATE SYNONYM (非SQL92语法)
Description: 创建一个对象的别名
CREATE SYNONYM synname FOR object
COMMENT (Oracle)
Description: Comment on objects and view in USER_{TAB|COL}_COMMENTS
COMMENT ON TABLE table IS 'string'
COMMENT ON COLUMN table.col IS 'string'
TRUNCATE TABLE (Oracle)
Description: Remove all table rows
TRUNCATE TABLE table
RENAME TABLE (Oracle)
Description: Rename the object
RENAME table TO newtable
DROP
Description: Removes existing objects from database
DROP TABLE name [,...] <CASCADE CONSTRAINTS>
DROP VIEW name
DROP SEQUENCE name [,...]
...
-------------------------------------------------------------------------------
INSERT
Description: Inserts new rows into a table
INSERT INTO table [ ( column [, ...] ) ]
{ VALUES ( expression [, ...] ) | SELECT query }
UPDATE
Description: Replaces values of columns in a table
UPDATE table SET col = expression [,...]
[ FROM fromlist ]
[ WHERE condition ]
DELETE
Description: Removes rows from a table
DELETE FROM table [ WHERE condition ]
SELECT query
Description: Retrieve rows from a table or view
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ <![AS]> name ] [,...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM {table | (select query)} [ alias ] [,...] ]
[ {{LEFT | RIGHT} [OUTER] | NATURAL |[FULL] OUTER} JOIN table alias
{ON condition | USING(col1,col2,...)} ]
[ WHERE {condition | EXISTS (correlated subquery)} ]
[ GROUP BY column [,...] ]
[ HAVING condition [,...] ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } select ]
[ ORDER BY {column | int} [ ASC | DESC | USING operator ] [,...] ]
[ FOR UPDATE [ OF class_name [,...] ] ]
LIMIT { count | ALL } [ { OFFSET | ,} start ]
DECLARE (Oracle)
Description: Defines a cursor for table access
DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR query
[ FOR { READ ONLY | UPDATE [ OF column [,...] ] ]
FETCH (Oracle)
Description: Gets rows using a cursor
FETCH [ selector ] [ count ] { IN | FROM } cursor
FETCH [ RELATIVE ] [{ [ # | ALL | NEXT | PRIOR ] }] FROM cursor
Command: CLOSE (Oracle)
Description: Close a cursor
CLOSE cursor