informixvsoracle中英文(编辑修改稿)

informixvsoracle中英文(编辑修改稿)内容摘要：

r will occur after the OPEN or EXECUTE instructions. • IFX: [] ORA:substr(,5,6) Substrings in SQL INFORMIX SQL statements can use subscripts on columns defined with the character data type : SELECT ... FROM tab1 WHERE col1[2,3] = 39。 RO39。 SELECT ... FROM tab1 WHERE col1[10] = 39。 R39。 Same as col1[10,10] UPDATE tab1 SET col1[2,3] = 39。 RO39。 WHERE ... SELECT ... FROM tab1 ORDER BY col1[1,3] .. while ORACLE provides the SUBSTR( ) function, to extract a substring from a string expression : SELECT .... FROM tab1 WHERE SUBSTR(col1,2,2) = 39。 RO39。 SELECT SUBSTR(39。 Some text39。 ,6,3) FROM DUAL Gives 39。 tex39。 • ORACLE不支持临时表 Temporary tables ORACLE does not support temporary tables as Informix does. ORACLE provides GLOBAL TEMPORARY TABLEs which are shared among processes (only data is temporary and local to a SQL process). INFORMIX does not shared temp tables among SQL processes, each process can create its own temp table without table name conflicts. • IFX: MATCHES ‘[0123]’ ORA:IN (‘0’,’1’,’2’,’3’) 注： 1) MATCHES 包含在 IF STATEMENTS中则不用改。 2）若组 SQL时， MATCHES后为变数，可用 cl_parse()。 （范例： ) MATCHES and LIKE in SQL conditions INFORMIX : WHERE col MATCHES 39。 [09][09][09]39。 ORACLE : WHERE TRANSLATE(col,39。 012345678939。 ,39。 999999999939。 )=39。 99939。 9. UPDATE的文法 • IFX :UPDATE tab_name set (…) = (…) • ORA:UPDATE tab_name set …= …, …=… Syntax of UPDATE statements INFORMIX allows an specific syntax for UPDATE statements : UPDATE table SET ( collist ) = ( vallist ) UPDATE statements using the nonANSI syntax are converted to the standard form : UPDATE table SET column=value [,...] ORACLE 10. SCROLL CURSOR • _out()， _b_fill()段 declare的 cursor不可有cursor,因为 oracle不支持 cursor Scroll cursors INFORMIX provides scroll cursors to move backwards in a record set resulting from a SELECT statement. ORACLE does not support scroll cursors. Solution : Scroll cursors are simulated by the ORACLE database interface with a temporary file (a record set buffer). BY • ORACLE不支持用 COLUMN NUMBER,如下： IFX :GROUP BY 1,2,3 ORA:GROUP BY ima06,ima08,ima01。