Oracle Savepoint

现在的位置: 首页 > 综合 > 正文

RSS

2013年10月06日 ⁄ 综合 ⁄ 共 9546字 ⁄ 字号小中大 ⁄ 评论关闭

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

--输出0到9这9个数字

SQL> set serveroutput on

SQL> begin

2 dbms_output.enable;

3 for ctr in 0..9 loop

4 dbms_output.put(ctr||' ');

5 end loop;

6 dbms_output.put_line('loop exited');

7 end;

8 /

0 1 2 3 4 5 6 7 8 9 loop exited

PL/SQL procedure successfully completed

--循环打印ASCII表

SQL> set serveroutput on size 10240

SQL> declare

2 i number;

3 j number;

4 k number;

5 begin

6 for i in 2..15 loop

7 for j in 1..16 loop

8 k:=i*16+j;

9 dbms_output.put((to_char(k,'000'))||':'||chr(k)||' ');

10 if k mod 8=0 then

11 dbms_output.put_line('');

12 end if;

13 end loop;

14 end loop;

15 end;

16 /

033:! 034:" 035:# 036:$ 037:% 038:& 039:' 040:(

041:) 042:* 043:+ 044:, 045:- 046:. 047:/ 048:0

049:1 050:2 051:3 052:4 053:5 054:6 055:7 056:8

057:9 058:: 059:; 060:< 061:= 062:> 063:? 064:@

065:A 066:B 067:C 068:D 069:E 070:F 071:G 072:H

073:I 074:J 075:K 076:L 077:M 078:N 079:O 080:P

081:Q 082:R 083:S 084:T 085:U 086:V 087:W 088:X

089:Y 090:Z 091:[ 092:/ 093:] 094:^ 095:_ 096:`

097:a 098:b 099:c 100:d 101:e 102:f 103:g 104:h

105:i 106:j 107:k 108:l 109:m 110:n 111:o 112:p

113:q 114:r 115:s 116:t 117:u 118:v 119:w 120:x

121:y 122:z 123:{ 124:| 125:} 126:~ 127: 128:€

129: 130: 131: 132: 133: 134: 135: 136:

137: 138: 139: 140: 141: 142: 143: 144:

145: 146: 147: 148: 149: 150: 151: 152:

153: 154: 155: 156: 157: 158: 159: 160:

161: 162: 163: 164: 165: 166: 167: 168:

169: 170: 171: 172: 173: 174: 175: 176:

177: 178: 179: 180: 181: 182: 183: 184:

185: 186: 187: 188: 189: 190: 191: 192:

193: 194: 195: 196: 197: 198: 199: 200:

201: 202: 203: 204: 205: 206: 207: 208:

209: 210: 211: 212: 213: 214: 215: 216:

217: 218: 219: 220: 221: 222: 223: 224:

225: 226: 227: 228: 229: 230: 231: 232:

233: 234: 235: 236: 237: 238: 239: 240:

241: 242: 243: 244: 245: 246: 247: 248:

249: 250: 251: 252: 253: 254: 255: 256:

PL/SQL procedure successfully completed

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

--显示表结构

SQL> desc emp;

Name Type Nullable Default Comments

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

EMPNO NUMBER(4)

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2) Y

SQL> create table kkk(id number(4),name varchar2(10),dept number(5));

Table created

SQL> select * from kkk;

ID NAME DEPT

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

--批量导入数据

SQL> insert into kkk(id,name,dept) select empno,ename,deptno from emp where deptno=10;

3 rows inserted

SQL> select * from kkk;

ID NAME DEPT

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

7782 CLARK 10

7839 KING 10

7934 MILLER 10

SQL> drop table kkk;

Table dropped

SQL> select * from kkk;

select * from kkk

ORA-00942: 表或视图不存在

SQL> select * from emp where ename='SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

SQL> update emp set (job

2 ;

update emp set (job

ORA-00907: 缺失右括号

SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';

1 row updated

SQL> rollback;

Rollback complete

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

SQL> commit;

Commit complete

SQL> savepoint a;

Savepoint created

SQL> delete from emp;

14 rows deleted

SQL> rollback a;

rollback a

ORA-02181: 无效的 ROLLBAC WORK 选项

--全部回滚

SQL> rollback;

Rollback complete

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

--创建保存点

SQL> savepoint a;

Savepoint created

SQL> delect * from emp where ename='SMITH';

delect * from emp where ename='SMITH'

ORA-00900: 无效 SQL 语句

SQL> delete * from emp where ename='SMITH';

delete * from emp where ename='SMITH'

ORA-00903: 表名无效

SQL> delete from emp where ename='SMITH';

1 row deleted

SQL> select * from emp where ename='SMITH';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

SQL> select count(*) from emp;

COUNT(*)

----------

【上篇】hdu 1055 Color a Tree(树上贪心，4级)
【下篇】【转】VS 2005 全局键盘钩子相关

作者: drinker

该日志由 drinker 于11年前发表在综合分类下，最后更新于 2013年10月06日.
转载请注明: Oracle Savepoint | 学步园 +复制链接

抱歉!评论已关闭.

返回首页

（其他合作也可洽谈）

必威体育

必威电竞

学步园