plsql常用语句

说明：复制表(只复制结构,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

说明：显示文章、提交人和最后回复时间

SQL: select a.title,https://www.360docs.net/doc/12929103.html,ername,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

说明：外连接查询(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

说明：两张关联表，删除主表中已经在副表中没有的信息

SQL:

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

说明：--

SQL:

SELECT A.NUM, https://www.360docs.net/doc/12929103.html,, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1,

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM （+）

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

说明：--

SQL:

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩

说明：从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

SQL:

SELECT https://www.360docs.net/doc/12929103.html,erper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

说明：四表联查问题：

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

说明：得到表中最小的未使用的ID号

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

查找重复的记录

select * from fish where fish_name in

(select fish_name from

(select fish_name,count(fish_name) from fish

group by fish_name

having count(fish_name)>1));

统计某字段中所有不同的记录的个数

select nsrbm,count(nsrbm)

from t11_nsrjbqk

where ...

group by nsrbm

having count(nsrbm)>1

SELECT *

FROM letters

WHERE ((.ID IN (SELECT ID FROM letters As Tmp GROUP BY ID HAVING Count(*)>1 )))

ORDER BY ID;

在SQL Enterprise Manager裡面查詢Access裡面的數據

SELECT *

FROM OPENROWSET('msdasql', 'dsn=odbc數據源名;trusted_connection=yes',

'select * from table')

SQL中isnull函数的作用是将将两个参数字段中不为空的值取出

SELECT t1.a, t1.b, ISNULL(t1.c, t2.d)

FROM t1, t2

WHERE t1.a = t2.a

为查询的结果编上序列号

select IDENTITY(int,1,1)as id,a,b,c into #1 from table

select * from #1

或

SELECT rowno =

(SELECT SUM(row)

FROM (SELECT 1 AS row, *

FROM table) AS a

WHERE a.autoid <= b.autoid), *

FROM table b

ORDER BY autoid

日期型字段的操作

select * from inetsrvlog where

datepart(mm,logtime)= 3

and datepart(dd,logtime)>=1

and datepart(dd,logtime)<=15

insert into tablename (...,..) values ...,...

PL/SQL精典编程例：

說明：用SCOTT/TIGER登入ORACLE，執行看看效果，可以將某個字段中的多行放入同一行，以前以為不可能用一條SQL實現在，竟然可以。主要理解lead() over(),row_number() over()就可以啦，很奇妙的ORACLE吧，有誰有更好的辦法或SQL

SERVER的實現，也請貼出。

SQL> select deptno, dname, emps

2 from (

3 select d.deptno, d.dname, rtrim(e.ename ||', '||

4 lead(e.ename,1) over (partition by d.deptno

5 order by e.ename) ||', '||

6 lead(e.ename,2) over (partition by d.deptno

7 order by e.ename) ||', '||

8 lead(e.ename,3) over (partition by d.deptno

9 order by e.ename) ||', '||

10 lead(e.ename,4) over (partition by d.deptno

11 order by e.ename) ||', '||

12 lead(e.ename,5) over (partition by d.deptno

13 order by e.ename),', ') emps,

14 row_number () over (partition by d.deptno

15 order by e.ename) x

16 from emp e, dept d

17 where d.deptno = e.deptno

18 )

19 where x = 1

20 /

DEPTNO DNAME EMPS

------- ----------- ------------------------------------------

10 ACCOUNTING CLARK, KING, MILLER

20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

oracle FAQ

Q1.怎样创建表？

A. CREATE TABLE ROYAL_MTABLE

(

RM_INT_FIELD INTEGER,

RM_STR_FIELD VARCHAR2(64)

)

CREATE TABLE ROYAL_DTABLE

(

RD_INT_FIELD INTEGER,

RD_STR_FIELD VARCHAR2(32)

)

Q2.怎样删除表？

A. DROP TABLE ROYAL_DTABLE;

Q3.怎样创建视图？

A. CREATE OR REPLACE VIEW ROYAL_MDVIEW AS

SELECT T1.RM_STR_FIELD AS F1, T2.RD_STR_FIELD AS F2 FROM ROYAL_MTABLE T1, ROYAL_DTABLE T2 WHERE T1.RM_INT_FIELD = T2.RM_INT_FIELD

Q4.怎样删除视图？

A. DROP VIEW ROYAL_MDVIEW;

Q5.怎样给表添加字段？

A. ALTER TABLE ROYAL_DTABLE ADD RM_INT_FIELD INTEGER;

Q6.怎样删除表中某个字段？

A. ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD;

Q7.怎样给某个字段添加约束？

A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NOT NULL;

Q8.怎样去除某个字段上的约束？

A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NULL;

Q9.怎样给表加上主键？

A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT PK_ROYAL_MTABLE PRIMARY KEY (RM_INT_FIELD);

Q10.怎样删除表的主键？

A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT PK_ROYAL_MTABLE CASCADE;

Q11.怎样给表添加一个外键？

A. ALTER TABLE ROYAL_DTABLE ADD CONSTRAINT FK_ROYAL_DTABLE FOREIGN KEY (RM_INT_FIELD) REFERENCES ROYAL_MTABLE (RM_INT_FIELD) ON DELETE CASCADE;

Q12.怎样删除表的一个外键？

A. ALTER TABLE ROYAL_DTABLE DROP CONSTRAINT FK_ROYAL_DTABLE;

Q13.怎样给字段加上CHECK？

A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT CHK_RM_STR_FIELD CHECK (RM_STR_FIELD IN ('Y','N'));

Q14.怎样去掉字段上的CHECK？

A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT CHK_RM_STR_FIELD;

Q15.怎样给字段设置默认值？

A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT 'ROYAL';

Q16.怎样移去字段的默认值？

A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT NULL;

Q17.怎样创建索引？

A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD);

Q18.怎样删除索引？

A. DROP INDEX IDX_ROYAL_DTABLE;

Q19.怎样创建用户？

A. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; Q20.怎样删除用户？

A. DROP USER TESTUSER CASCADE;

21.怎样将对象权限（object privileges）授予用户？

A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTUSER;

GRANT INSERT, UPDATE, DELETE ON ROYAL_DTABLE TO TESTUSER;

GRANT SELECT, ALTER ON ROYAL_DTABLE TO TESTUSER WITH GRANT OPTION;

Q22.怎样从用户收回对象权限？

A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_DTABLE FROM TESTUSER;

Q23.怎样将角色权限（role privileges）授予用户？

A. GRANT CONNECT TO TESTUSER WITH ADMIN OPTION;

GRANT DBA TO TESTUSER;

Q24.怎样从用户收回角色权限？

A. REVOKE DBA FROM TESTUSER;

Q25.怎样将系统权限（system privileges）授予用户？

A. GRANT ALTER ANY TABLE TO TESTUSER WITH ADMIN OPTION;

Q26.怎样从用户收回系统权限？

A. REVOKE ALTER ANY TABLE FROM TESTUSER;

Q27.怎样创建序列？

A. CREATE SEQUENCE RM_INT_FIELD_SEQ

MINvalue 1

MAXvalue 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 10

ORDER;

Q28.怎样删除序列？

A. DROP SEQUENCE RM_INT_FIELD_SEQ;

Q29.怎样获取序列值？

A. SELECT RM_INT_FIELD_SEQ.NEXTVAL FROM DUAL;

Q30.怎样创建角色？

A. CREATE ROLE TESTROLE;

Q31.怎样删除角色？

A. DROP ROLE TESTROLE;

Q32.怎样将对象权限（object privileges）授予角色？

A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTROLE;

Q33.怎样从角色收回对象权限？

A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE FROM TESTROLE;

Q34.怎样将角色权限（role privileges）授予角色？

A. GRANT DBA TO TESTROLE;

Q35.怎样从角色收回角色权限？

A. REVOKE DBA FROM TESTROLE;

Q36.怎样将系统权限（system privileges）授予角色？

A. GRANT CREATE TABLE TO TESTROLE;

Q37.怎样从角色收回系统权限？

A. REVOKE CREATE TABLE FROM TESTROLE;

Q38.不等于条件有哪几种写法？（茴香豆问题:)）

A. SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD != 'Y';

SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD ^= 'Y';

SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD <> 'Y';

Q39.like子句的用法？

A. SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '%Y%';

SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '_Y%';

Q40.请举出一个where子查询简单例子？

A. SELECT * FROM ROYAL_DTABLE WHERE RM_INT_FIELD IN (SELECT RM_INT_FIELD FROM ROYAL_MTABLE WHERE RM_STR_FIELD NOT IN ('Y','B'));

Q41.Oracle常用字符串处理函数有哪些？

A. || 连接两个字符串； LENGTH 字符串长度； TRIM/LTRIM/RTRIM 截断串左（右）指定字符串（包括空串）； LOWER/UPPER 将字符串转换为小/大写，等等。

例如：SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE;

Q42.Oracle支持哪些数字处理函数？

A. Oracle支持所有常用数字函数，包括（但不限于）+、-、*、/、ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND、SIN、SINH、SQRT、TAN、TRUNC、AVG、COUNT、MAX、MIN、SUM、GREATEST、LEAST等等。

例如：

SELECT GREATEST(3, 4, 5)*4 FROM DUAL;

SELECT POWER(2,3) FROM DUAL;

Q43.怎样取得数据库服务器当前日期、时间？

A. SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

Q44.怎样将字符串转换为日期、时间格式？

A. SELECT TO_DATE('2002-11-27', 'YYYY-MM-DD') FROM DUAL;

SELECT TO_DATE('2002-11-27 09:28:55', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

Q45.常用日期函数有哪些？

A. NEXT_DAY LAST_DAY ADD_MONTHS MONTHS_BETWEEN等等。

例如：SELECT LAST_DAY(SYSDATE) FROM DUAL;

Q46.能给出一个DECODE函数用法的例子吗？

A. DECODE函数的格式为DECODE(value, if1, then1, if2, then2...,else)。假设表ROYAL_DTABLE中有如下数据：

RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD

-------------------------------------

1 royal 1

2 bill 2

3 joy 1

请观察如下SQL语句输出结果。

SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;

---------

royaltest

billgates

joy

Q47.能给出一个group by、having和order by用法的例子吗？

A. SQL> SELECT * FROM ROYAL_MTABLE;

RM_INT_FIELD RM_STR_FIELD

-------------------------

1 Y

2 N

3 Y

SQL> SELECT * FROM ROYAL_DTABLE;

RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD

--------------------------------------

1 royal 1

2 bill 2

3 joy 1

SQL> SELECT RM_INT_FIELD, SUM(RD_INT_FIELD) FROM ROYAL_DTABLE GROUP BY RM_INT_FIELD HAVING SUM(RD_INT_FIELD) >= 2 ORDER BY SUM(RD_INT_FIELD) ASC;

RM_INT_FIELD SUM(RD_INT_FIELD)

------------------------------

2 2

1 4

Q48.Oracle有哪些常用数据字典？

A. USER_TABLES(TABS)、USER_TAB_COLUMNS(COLS)、USER_VIEWS、USER_SEQUENCES(SEQ)、USER_CONSTRAINTS、USER_CONS_COLUMNS、USER_TAB_COMMENTS、USER_COL_COMMENTS、USER_INDEXES(IND)、USER_IND_COLUMNS、USER_USERS、DBA_USERS、ALL_USERS、USER_TABLESPACES等等。

例如：SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_ROYAL_DTABLE';

Q49.怎样将日期、时间插入date型字段中？

A. INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) valueS (9, 'Y', TO_DATE('2002-05-23', 'YYYY-MM-DD'));

INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) valueS (10, 'Y', TO_DATE('2002-10-10 8:23:33', 'YYYY-MM-DD HH:MI:SS'));

Q50.能介绍一下connect by的用法吗？

A. connect by子句提供了遍历“树”的手段。

假设有这样一个表：CREATE TABLE ROYAL_TREETABLE (ID INTEGER, PARENT_ID INTEGER, NAME VARCHAR2(32));

表中有如下数据：

ID PARENT_ID NAME

-----------------

2 1 AAA

3 1 BBB

4 2 CCC

5 2 DDD

6 4 EEE

8 7 GGG

假如我们现在需要从NAME = 'EEE'的记录开始，向上查找所有有父子关系的记录，可执行如下SQL语句：

SELECT * FROM ROYAL_TREETABLE START WITH NAME = 'EEE' CONNECT BY ID = PRIOR PARENT_ID;

ID PARENT_ID NAME

-----------------

6 4 EEE

4 2 CCC

2 1 AAA

还有好象01cn中的。

函数

数学函数

函数 Microsoft SQLServer Oracle

绝对值 ABS ABS(X)

大于或等于x的最小正整数 CEILING CEIL（x）

小于或等于x的最大正整数 FLOOR FLOOR（X）

X除于y的余数 USE MODULO (%) OPERATOR MOD（X，Y）

四舍五入 ROUND ROUND（x,y）

Round(1.56)得到2

Round（1.56,1）得到1.6

符号函数 SIGN SIGN

如果为NULL则转换数字 ISNULL NVL（n1,n2）

如n1为空，得到n2,否则得到n1

字符函数

函数 SQlServer Oracle

字串连接 (exp + exp) Exp1||Exp2

把字符转换为小写 LOWER LOWER

把字符转换为大写 UPPER UPPER

清除开始的空白 LTRIM LTRIM

清除尾部的空白 RTRIM RTRIM

多次重复字符串 REPLICATE RPAD

RPAD(‘a’,10) 10个a

重复空格的字串 SPACE RPAD(‘’,10) 10个空格

从数字数据转换为字符数据 STR TO_CHAR(num)

左截字符串 Left(str1,5) Substr(str1,1,5)

右截字符串 Right(str1,5) Substr(str1,-5,5)

子串 SUBSTRING SUBSTR

Substr(‘fdas’,1,3)得到fda

字符串中指定表达式的起始位置 CharIndex(exp1 , exp2 [ , start_location ])

CHARINDEX('wonderful on',’on’， 5) Instr(exp1 , exp2 [ , start_location])

Instr('wonderful on',’on’， 5)

替换字符 STUFF REPLACE

Replace(‘this and that’,‘th’,‘b’)

得到bis and bat

字符串长度 LEN LENGTH

如果为NULL则转换字串 ISNULL NVL(exp1,exp2)

如果exp1为空，得到exp2,否则得到exp1

日期函数

函数 SQLServer Oracle

两个日期之间的天数 DATEDIFF ( day , startdate , enddate ) EndDate-StartDate

两个日期之间的月数 DATEDIFF (month, startdate , enddate ) MONTHS_BETWEEN(enddate ，startdate) 两个日期之间的年数 DATEDIFF (year, startdate , enddate ) MONTHS_BETWEEN(startdate, enddate)/12 两个日期之间的小时数 DATEDIFF (hour, startdate , enddate ) （EndDate-StartDate）*24

两个日期之间的分钟数 DATEDIFF (minute, startdate , enddate ) （EndDate-StartDate）*24*60

日期相加 DATEADD (date column +/- value) or

ADD_MONTHS(d,x) 得到日期d加上x个月的日期

日期加上n天 DATEADD（day,n,date） Date+n

日期加上n月 DATEADD（month,n,date） ADD_MONTHS(d,x)

日期中的年 Year(date) To_char(date,‘YYYY’)

日期中的月 Month(date) To_char(date,‘MM’)

日期中的日 Day(date) To_char(date,‘DD’)

当前日期和时间 GETDATE() SYSDATE

转换日期成字符串 DATENAME TO_CHAR(d,[format])

字符串转换为日期 CONVERT TO_DATE(str,[format])

如果为NULL则转换日期 ISNULL NVL(d1,d2)

日期d1为空时得到d2,否则得到d1

其他函数

函数 Oracle SQLServer

数字转换为字符 TO_CHAR(num,[format]) CONVERT

字符转换为数字 TO_NUMBER(str,[format]) CONVERT

日期转换为字符 TO_CHAR(d,[format]) CONVERT

字符转换为日期 TO_DATE(str,[format]) CONVERT

得到某一位字符 Convert(bit, Column) SubStr(Column,sPos, 1)

分支语句 Decode(type，‘popular_comp’，’0’，‘mod_cook’，’1’， 'business'，‘2’，‘3’) CASE type

WHEN 'popular_comp' THEN '0’ WHEN 'mod_cook' THEN '1’

WHEN 'business' THEN '2'

ELSE '3'

END

SQL语言

一、SELECT statements语句

Oracle和Microsoft SQL Server用的SELECT语句的语法是类似的。

Oracle Microsoft SQ

SELECT [/*+ optimizer_hints*/]

[ALL | DISTINCT] select_list

[FROM

{table_name | view_name | select_statement}]

[WHERE clause]

[GROUP BY group_by_expression]

[HAVING search_condition]

[START WITH … CONNECT BY]

[{UNION | UNION ALL | INTERSECT |

MINUS} SELECT …]

[ORDER BY clause]

[FOR UPDATE] SELECT select_list

[INTO new_table_]

FROM table_source

[WHERE search_condition]

[ GR OUP BY [ALL] group_by_expression [,…n]

[ WITH { CUBE | ROLLUP } ]

[HAVING search_condition]

[ORDER BY order_expression [ASC | DESC] ]

In addition:

UNION Operator

COMPUTE Clause

FOR BROWSE Clause

OPTION Clause

SQL Server不支持面向Oracle的基于开销的优化器暗示，必须把这些暗示清除掉。建议使用SQL Server的基于开销的优化器。欲了解详细信息，请参阅本章后面的“调试SQL语句”部分。

SQL Server不支持Oracle的START WITH…CONNECT BY子句。在SQL Server中，你可以用创建一个执行同样任务的存储过程来代替。

SQL Server不支持Oracle的INTERSECT和MINUS集合。SQL Server的EXISTS和NOT EXISTS子句可以完成同样的任务。下面的例子使用INTERSECT操作符来为所有有学生的班级找到课程代码和课程名称。注意EXISTS操作符是怎样代替INTERSECT操作符的。两者返回的数据是一样的。

Oracle Microsoft SQ

SELECT CCODE, CNAME

FROM DEPT_ADMIN.CLASS

INTERSECT

SELECT https://www.360docs.net/doc/12929103.html,ODE, https://www.360docs.net/doc/12929103.html,AME

FROM STUDENT_ADMIN.GRADE G,

DEPT_ADMIN.CLASS C

WHERE https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE SELECT CCODE, CNAME

FROM DEPT_ADMIN.CLASS C

WHERE EXISTS

(SELECT 'X' FROM STUDENT_ADMIN.GRADE G

WHERE https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE)

下例使用MINUS操作符来找出那些没有学生的班级。

Oracle Microsoft SQ

SELECT CCODE, CNAME

FROM DEPT_ADMIN.CLASS

MINUS

SELECT https://www.360docs.net/doc/12929103.html,ODE, https://www.360docs.net/doc/12929103.html,AME

FROM STUDENT_ADMIN.GRADE G,

DEPT_ADMIN.CLASS C

WHERE https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE SELECT CCODE, CNAME

FROM DEPT_ADMIN.CLASSC

WHERE NOT EXISTS

(SELECT 'X' FROM STUDENT_ADMIN.GRADE G

WHERE https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE)

INSERT语句

Oracle和Microsoft SQL Server用的INSERT语句的语法是类似的。

Oracle Microsoft SQ

INSERT INTO

{table_name | view_name | select_statement} [(column_list)]

{values_list | select_statement} INSERT [INTO]

{

table_name [ [AS] table_alias] WITH ( […n])

| view_name [ [AS] table_alias]

| rowset_function_limited

}

{ [(column_list)]

{ VALUES ( { DEFAULT

| NULL

| expression

}[,…n]

)

| derived_table

| execute_statement

}

}

| DEFAULT VALUES

Transact-SQL语言支持插入表和视图，但是不支持SELECT语句中的INSERT操作。如果你的Oracle程序这么做了，则必须修改。

Oracle Microsoft SQl

INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE)

VALUES ('111111111', '1111',NULL) INSERT INTO GRADE (SSN, CCODE, GRADE)

VALUES ('111111111', '1111',NULL)

Transact-SQL的values_list参数提供了SQL-92标准的关键字DEFAULT，但这个在Oracle中是不支持的。当执行插入操作的时候，这个关键字给出了要用到的列的缺省值。如果给定的列没有缺省值，则插入一个NULL。如果该列不允许NULL，则返回一个错误消息。如果该列是作为一个时间片数据类型定义的，则插入下一个连续值。

关键字DEFAULT不能用于标识列。要产生下一个连续值，则有IDENTITY属性的列一定不能列入column_list或者values_clause。你不一定非要用DEFAUL关键字来获得一列的缺省值。在Oracle中，如果该列没有被column_list引用并且它有一个缺省值，则缺省值将放入列中。这是执行移植时最兼容的方法。

一个有用的Transact-SQL选项（EXECute procedure_name）是执行一个过程并且用管道把它的输出值输出到一个目标表或者视图。Oracle不允许你这样做。

UPDATE语句

因为Transact-SQL支持Oracle的UPDATE绝大多数语法，所以只需要很小的修改。

Oracle Microsoft SQ

UPDATE

{table_name | view_name | select_statement}

SET [column_name(s) = {constant_value | expression | select_statement | column_list |

variable_list}

{where_statement} UPDATE

{

table_name [ [AS] table_alias] WITH ( […n])

view_name [ [AS] table_alias]

| rowset_function_limited

}

SET

{column_name = {expression | DEFAULT | NULL}

| @variable = expression

| @variable = column = expression } [,…n]

{{[FROM {} [,…n] ]

[WHERE

] }

|

[WHERE CURRENT OF

{ { [GLOBAL] cursor_name } | cursor_variable_name}

] }

[OPTION ( [,…n] )]

Transact-SQL的UPDATE语句不支持依赖SELECT语句的更新操作。如果你的Oracle程序这样做了，你可以把SELECT 语句变成一个视图，然后在SQL Server的UPDATE语句中使用这个视图名字。请参看前面“INSERT”语句中的例子。

Oracle的UPDATE命令只能使用一个PL/SQL块中的程序变量。Transact-SQL语言不要求在使用变量时使用块。Oracle Microsoft SQ

DECLARE

VAR1 NUMBER(10,2);

BEGIN

VAR1 := 2500;

UPDATE STUDENT_ADMIN.STUDENT

SET TUITION_TOTAL = VAR1;

END; DECLARE

@VAR1 NUMERIC(10,2)

SELECT @VAR1 = 2500

UPDATE STUDENT_ADMIN.STUDENT

SET TUITION_TOTAL=@VAR1

在SQL Server中，关键字DEFAULT可以用来把一列设置为它的缺省值。你不能用Oracle的UPDATE命令来设置一列为它的缺省值。

Transact-SQL和Oracle SQL都支持在一个UPDATE语句中使用子查询。但是Transact-SQL的FROM子句可以用来创建一个基于节点的UPDATE。这个能力是你的UPDATE语法更加可读并且在某种意义上提高了性能。

Oracle Microsoft SQ

UPDATE

STUDENT_ADMIN.STUDENT S

SET TUITION_TOTAL = 1500

WHERE SSN IN (SELECT SSN

FROM GRADE G

WHERE G.SSN = S.SSN

AND https://www.360docs.net/doc/12929103.html,ODE = '1234') Subquery:

UPDATE

STUDENT_ADMIN.STUDENT S

SET TUITION_TOTAL = 1500

WHERE SSN IN (SELECT SSN

FROM GRADE G

WHERE G.SSN = S.SSN

AND https://www.360docs.net/doc/12929103.html,ODE = '1234')

FROM clause:

UPDATE

STUDENT_ADMIN.STUDENT S

SET TUITION_TOTAL = 1500

FROM GRADE G

WHERE S.SSN = G.SSN

AND https://www.360docs.net/doc/12929103.html,ODE = '1234'

DELETE语句

在大多数情况下，你不需要修改DELETE语句。但是如果你在Oracle中执行依赖SELECT语句的删除操作，你就必须进行修改，因为在Transact-SQL不支持这种功能。

Transact-SQL支持在WHERE子句中使用子查询，FROM子句也一样。后者可以产生更有效的语句。请参看前面在“UPDATE 语句”中的例子。

Oracle Microsoft SQ

DELETE [FROM]

{table_name | view_name | select_statement}

[WHERE clause] DELETE

[FROM ]

{

table_name [ [AS] table_alias] WITH ( […n])

| view_name [ [AS] table_alias]

| rowset_function_limited

}

[ FROM {} [,…n] ]

[WHERE

{

| { [ CURRENT OF

{

{ [ GLOBAL ] cursor_name }

cursor_variable_name

}

]

}

]

[OPTION ( [,…n])]

TRUNCATE TABLE语句

在Oracle和Microsoft SQL Server中TRUNCATE TABLE语句的语法是相似的。TRUNCATE TABLE语句用来把一个表中的所有行清除掉，并且这个操作是不能后滚的。该表的结构和索引仍然存在。DELETE触发器不会被执行。如果该表被一个FOREIGN KEY引用，则该表不能被砍掉。

Oracle Microsoft SQ

TRUNCATE TABLE table_name

[{DROP | REUSE} STORAGE] TRUNCATE TABLE table_name

在SQL Server中，这个语句只能由表的所有者给出。在Oracle中，只有当你是表的所有者或者有DELETE TABLE系统特权时才能使用该语句。

Oracle的TRUNCATE TABLE命令可以随意的释放被表中的行占据的存储空间。SQL Server的 TRUNCATE TABLE则总是回收被表中的数据和与之关联的索引占据的空间。

在identity和timestamp列中操作数据

Oracle序列是一种和任何给定的表或者列都不直接相关的数据库对象。一列和一个序列的关系是在应用程序中实现的，方法是把一个序列的值分配给一个列。因此在同序列一起工作的时候，Oracle并没有强化任何规则。但是在Microsoft SQL Server的标识列中，值是不能被更新的并且也不能使用DEFAULT关键字。

缺省情况下，数据不能直接插入到一个标识列中。标识列为新插入表的每一行自动产生一个唯一的、顺序的数字。这个缺省设置可以用下面的SET语句覆盖。

SET IDENTITY_INSERT table_name ON

当IDENTITY_INSERT设置为ON时，用户就可以在新行的标识列中插入任何值。为了防止输入重复的值，必须在该列上创建一个唯一的索引。这个语句的目的是允许用户为一行重新创建一个偶然被删除的值。@@IDENTITY可以用来获取最后一个标识值。

TRUNCATE TABLE语句把一个标识列重新设置为它原来的SEED值。如果你不想为一列重新设置标识值，可以用不带WHERE 子句的DELETE子句来代替TRUNCATE TABLE语句。你必须估计这会给你的Oracle移植带来什么样的影响，因为ORACLE SEQUENCES不会跟着TRUNCATE TABLE命令重新设置。

对时间信息（timestamp）列，你只能执行插入或者删除操作。如果你试图更新一个时间信息列，你将收到这样的错误消息。

Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.

锁定被请求的行

Oracle用FOR UPDATE子句来锁定在SELECT命令中指定的行。在Microsoft SQL Server中，你不需要使用它的等价子句，因为这是一个缺省行为。

行合计和计算子句

SQL Server的COMPUTE子句用来生成行合计函数（SUM、AVG、MIN、MAX、以及COUNT），这些函数看起来好象是查询结果的附加行。这允许你看到一组结果的详情和汇总。你可以为一个子群（subgroups）计算汇总值，还可以为同一组计算更多的合计函数。

Oracle的SELECT命令语法不支持COMPUTE子句。但是，SQL Server的COMPUTE子句就像在Oracle的SQL*Plus查询工具中能找到的COMPUTE命令一样的工作。

连接子句（Join clauses）

Microsoft SQL Server 7.0允许在一个连接子句中连接多达256个表，包括临时的和永久的表。在Oracle中，没有连接限制。

在Oracle中使用外部连接时，外部连接操作符（+）典型的放置在子列（foreign key）的后面。（+）依靠少数几个唯一值来识别该列。除非子列允许空值，否则总是这样的。如果子列允许空值，则（+）被放置在父列（PRIMARY KEY或者UNIQUE 约束）上。你不能把（+）同时放在等号（=）的两边。

用SQL Server，你可以使用（*=）和（=*）外部连接操作符。（*）用来标识一个有很多唯一值的列。如果子列不允许空值，则（*）被放在等号的父列一边。在Oracle中，（*）的放置正好相反。不能把（*）同时放在等号的两边。

（*=）和（=*）被认为是传统的连接操作符。SQL Server也支持下面列出的SQL-92标准的连接操作符。建议你使用这种语法。SQL-92标准语法比（*）操作符更强大，限制更小。

Join操作描述

CROSS JOIN 这是两个表的交叉产物。如果在一个旧式的连接中没有指定WHERE子句，则返回同一行。在Oracle中，这种类型的连接叫做笛卡儿连接。

INNER 该连接指定返回所有的内部行。任何不匹配的行将被丢弃。该连接同一个标准的Oracle表连接是一样的。

LEFT[OUTER] 这种类型的连接指定返回右边表的所有外部行，即使没有发现任何匹配行。该操作符同Oracle外部连接（+）是一样的。

RIGHT[OUTER] 这种类型的连接指定返回左边表的所有外部行，即使没有发现任何匹配行。该操作符同Oracle外部连接（+）是一样的。

FULL [OUTER] 如果来自任一表的一行不匹配选择标准，指定该行被包括到结果集中，并且它的符合其它表的输出列被设定为NULL。这和把Oracle外部连接操作符放在“=”的两端是一样的（col1(+) = col2(+)），而在Oracle中，这是不允许的。

下面的例子返回所有学生都要上的课程的一个清单。在学生表和年级表之间定义的外部连接允许显示所有的学生，甚至那些没有参加任何课程的学生。在课程表上也有一个外部连接，该连接返回课程名字。如果课程表上没有加上外部连接，则不会返回那些没有参加任何课程的学生，因为他们的课程代码（CCDOE）是空值。

Oracle Microsoft SQServer

SELECT S.SSN AS SSN,

FNAME, LNAME

FROM STUDENT_ADMIN.STUDENT S,

DEPT_ADMIN.CLASS C,

STUDENT_ADMIN.GRADE G

WHERE S.SSN = G.SSN(+)

AND https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE(+) SELECT S.SSN AS SSN,

FNAME, LNAME

FROM STUDENT_ADMIN.GRADE G

RIGHT OUTER JOIN

STUDENT_ADMIN.STUDENT S

ON G.SSN = S.SSN

LEFT OUTER JOIN

DEPT_ADMIN.CLASS C

ON https://www.360docs.net/doc/12929103.html,ODE = https://www.360docs.net/doc/12929103.html,ODE

用SELECT语句做表名

Microsoft SQL Server和Oracle都支持在执行查询时用SELECT语句作为表源。SQL Server需要一个别名（alias）；而在Oracle中是否使用别名是可选的。

Oracle Microsoft SQ

SELECT SSN, LNAME, FNAME,

TUITION_PAID, SUM_PAID

FROM STUDENT_ADMIN.STUDENT,

(SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT) SELECT SSN, LNAME, FNAME,

TUITION_PAID, SUM_PAID

FROM STUDENT_ADMIN.STUDENT,

(SELECT SUM(TUITION_PAID) SUM_PAID FROM STUDENT_ADMIN.STUDENT) SUM_STUDENT

读取和修改BLOBs

Microsoft SQL Server用text和image列来实现二进制大对象（binary large objects，BLOBs）。Oracle则用LONG 和LONG RAW列来实现BLOBs。在Oracle中，SELECT命令可以查询LONG和LONG RAW列的值。

在SQL Server中，你可以使用标准的Transact-SQL语句或者专门的READTEXT语句来读取text和image列中的数据。READTEXT语句允许你读取text和image列的一部分。Oracle没有为LONG 和LONG RAW提供等价的语句。

READTEXT语句利用了一个text_pointer，该指针可以用TEXTPTR函数获取。TEXTPTR函数返回一个指向特定行中的text 或者image的指针，如果查询有多于一行返回的话，则返回指向最后一行中的text或者image的指针。由于TEXTPTR返回的是一个16字节的二进制字符串，所以最好是声明一个内部变量来保持该文本指针，然后在READTEXT中使用这个变量。READTEXT语句说明了有几个字节要返回。位于@@TEXTSIZE函数中的值，限制了返回的字符或者字节的数量，如果该值小于READTEXT声明的值，就用后者来代替。

SET语句可以用TEXTSIZE参数来说明以字节为单位的由一个SELECT语句返回的文本数据的尺寸。如果你设置一个大小为0的TEXTSIZE，则该尺寸就重设为缺省值（4 KB）。设置TEXTSIZE对@@TEXTSIZE函数有影响。当SQL_MAX_LENGTH 语句选项改变的时候，SQL Server ODBC自动设置TEXTSIZE参数。

在Oracle中，用UPDATE和INSERT命令来改变LONG和LONG RAW列中的值。在SQL Server，你可以用标准的UPDATE 和INSERT语句，或者也可以用UPDATETEXT和WRITETEXT语句。UPDATETEXT和WRITETEXT都允许一个nonlogged选项，而且UPDATETEXT还允许对文本或者图形列的部分更新。

UPDATETEXT可以用来代替已有的数据，删除已有的数据，或者插入新数据。新插入的数据可以是一个常数值，表名，列名或者文本指针。

WRITETEX T语句完全覆盖它所影响的列中的任何已有的数据。用WRITETEXT来替换文本数据，用UPDATETEXT来修改文本数据。因为UPDATETEXT语句只修改一个文本或者图形值的一部分而不是全部的值，所以UPDATETEXT语句更灵活一些。