[尚学堂马士兵Oracle教程笔记]

  1. 检查Oracle安装

  2. 首先,以超级管理员的身份登录oracle

  3. sqlplus sys/bjsxt as sysdba

  4. 然后,解除对scott用户的锁

  5. alter user scott account unlock;

  6. 那么这个用户名就能使用了。

  7. (默认全局数据库名orcl)

  8. 1、select ename, sal * 12 from emp; //计算年薪

  9. 2、select 2*3 from dual; //计算一个比较纯的数据用dual表

  10. 3、select sysdate from dual; //查看当前的系统时间

  11. 4、select ename, sal*12 anuual_sal from emp; //给搜索字段更改名称(双引号 keepFormat 别名有特殊字符,要加双引号)。

  12. 5、任何含有空值的数学表达式,最后的计算结果都是空值。

  13. 6、select ename||sal from emp; //(将sal的查询结果转化为字符串,与ename连接到一起,相当于Java中的字符串连接)

  14. 7、select ename||'afasjkj' from emp; //字符串的连接

  15. 8、select distinct deptno from emp; //消除deptno字段重复的值

  16. 9、select distinct deptno , job from emp; //将与这两个字段都重复的值去掉

  17. 10、select * from emp where deptno=10; //(条件过滤查询)

  18. 11、select * from emp where empno > 10; //大于 过滤判断

  19. 12、select * from emp where empno <> 10 //不等于 过滤判断

  20. 13、select * from emp where ename > 'cba'; //字符串比较,实际上比较的是每个字符的AscII值,与在Java中字符串的比较是一样的

  21. 14、select ename, sal from emp where sal between 800 and 1500; //(between and过滤,包含800 1500)

  22. 15、select ename, sal, comm from emp where comm is null; //(选择comm字段为null的数据)

  23. 16、select ename, sal, comm from emp where comm is not null; //(选择comm字段不为null的数据)

  24. 17、select ename, sal, comm from emp where sal in (800, 1500,2000); //(in 表范围)

  25. 18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; //(只能按照规定的格式写)

  26. 19、select ename, sal from emp where deptno =10 or sal >1000;

  27. 20、select ename, sal from emp where deptno =10 and sal >1000;

  28. 21、select ename, sal, comm from emp where sal not in (800, 1500,2000); //(可以对in指定的条件进行取反)

  29. 22、select ename from emp where ename like '%ALL%'; //(模糊查询)

  30. 23、select ename from emp where ename like '_A%'; //(取第二个字母是A的所有字段)

  31. 24、select ename from emp where ename like '%/%%'; //(用转义字符/查询字段中本身就带%字段的)

  32. 25、select ename from emp where ename like '%%%' escape ''; //(用转义字符/查询字段中本身就带%字段的)

  33. 26、select * from dept order by deptno desc; (使用order by desc字段 对数据进行降序排列 默认为升序asc);

  34. 27、select * from dept where deptno <>10 order by deptno asc; //(我们可以将过滤以后的数据再进行排序)

  35. 28、select ename, sal, deptno from emp order by deptno asc, ename desc; //(按照多个字段排序 首先按照deptno升序排列,当detpno相同时,内部再按照ename的降序排列)

  36. 29、select lower(ename) from emp; //(函数lower() 将ename搜索出来后全部转化为小写);

  37. 30、select ename from emp where lower(ename) like '_a%'; //(首先将所搜索字段转化为小写,然后判断第二个字母是不是a)

  38. 31、select substr(ename, 2, 3) from emp; //(使用函数substr() 将搜素出来的ename字段从第二个字母开始截,一共截3个字符)

  39. 32、select chr(65) from dual; //(函数chr() 将数字转化为AscII中相对应的字符)

  40. 33、select ascii('A') from dual; //(函数ascii()与32中的chr()函数是相反的 将相应的字符转化为相应的Ascii编码) )

  41. 34、select round(23.232) from dual; //(函数round() 进行四舍五入操作)

  42. 35、select round(23.232, 2) from dual; //(四舍五入后保留的小数位数 0 个位 -1 十位)

  43. 36、select to_char(sal, '99,999.9999')from emp; //(加符号加入千位分隔符,保留四位小数,没有的补零)

  44. 37、select to_char(sal, 'L99,999.9999')from emp; //(L 将货币转化为本地币种此处将显示¥人民币)

  45. 38、select to_char(sal, 'L00,000.0000')from emp; //(补零位数不一样,可到数据库执行查看)

  46. 39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp; //(改变日期默认的显示格式)

  47. 40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual; //(用12小时制显示当前的系统时间)

  48. 41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual; //(用24小时制显示当前的系统时间)

  49. 42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS'); //(函数to-date 查询公司在所给时间以后入职的人员)

  50. 43、select sal from emp where sal > to_number('1,250.00', '9,999.99'); //(函数to_number()求出这种薪水里带有特殊符号的)

  51. 44、select ename, sal*12 + nvl(comm,0) from emp; //(函数nvl() 求出员工的"年薪 + 提成(或奖金)问题")

  52. 45、select max(sal) from emp; // (函数max() 求出emp表中sal字段的最大值)

  53. 46、select min(sal) from emp; // (函数max() 求出emp表中sal字段的最小值)

  54. 47、select avg(sal) from emp; //(avg()求平均薪水);

  55. 48、select to_char(avg(sal), '999999.99') from emp; //(将求出来的平均薪水只保留2位小数)

  56. 49、select round(avg(sal), 2) from emp; //(将平均薪水四舍五入到小数点后2位)

  57. 50、select sum(sal) from emp; //(求出每个月要支付的总薪水)

  58. /////////////////////////组函数(共5个):将多个条件组合到一起最后只产生一个数据//////min() max() avg() sum() count()/////////////////////////////

  59. 51、select count(*) from emp; //求出表中一共有多少条记录

  60. 52、select count(*) from emp where deptno=10; //再要求一共有多少条记录的时候,还可以在后面跟上限定条件

  61. 53、select count(distinct deptno) from emp; //统计部门编号前提是去掉重复的值

  62. ////////////////////////聚组函数group by() //////////////////////////////////////

  63. 54、select deptno, avg(sal) from emp group by deptno; //按照deptno分组,查看每个部门的平均工资

  64. 55、select max(sal) from emp group by deptno, job; //分组的时候,还可以按照多个字段进行分组,两个字段不相同的为一组

  65. 56、select ename from emp where sal = (select max(sal) from emp); //求出

  66. 57、select deptno, max(sal) from emp group by deptno; //搜素这个部门中薪水最高的的值

  67. //////////////////////////////////////////////////having函数对于group by函数的过滤 不能用where//////////////////////////////////////

  68. 58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; (order by )//求出每个部门的平均值,并且要 > 2000

  69. 59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;//求出sal>1200的平均值按照deptno分组,平均值要>1500最后按照sal的倒序排列

  70. 60、select ename,sal from emp where sal > (select avg(sal) from emp); //求那些人的薪水是在平均薪水之上的。

  71. 61、select ename, sal from emp join (select max(sal) max_sal ,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno); //查询每个部门中工资最高的那个人

  72. ///////////////////////////////等值连接//////////////////////////////////////

  73. 62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; //自连接,把一张表当成两张表来用

  74. 63、select ename, dname from emp, dept; //92年语法 两张表的连接 笛卡尔积。

  75. 64、select ename, dname from emp cross join dept; //99年语法 两张表的连接用cross join

  76. 65、select ename, dname from emp, dept where emp.deptno = dept.deptno; // 92年语法 表连接 + 条件连接

  77. 66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); // 新语法

  78. 67、select ename,dname from emp join dept using(deptno); //与66题的写法是一样的,但是不推荐使用using : 假设条件太多

  79. ///////////////////////////////////////非等值连接///////////////////////////////////////////

  80. 68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); //两张表的连接 此种写法比用where更清晰

  81. 69、select ename, dname, grade from emp e

  82. join dept d on(e.deptno = d.deptno)

  83. join salgrade s on (e.sal between s.losal and s.hisal)

  84. where ename not like '_A%'; //三张表的连接

  85. 70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); //自连接第二种写法,同62

  86. 71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); //左外连接 把左边没有满足条件的数据也取出来

  87. 72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); //右外连接

  88. 73、select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);//求每个部门平均薪水的等级

  89. 74、select ename from emp where empno in (select mgr from emp); // 在表中搜索那些人是经理

  90. 75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); // 面试题 不用组函数max()求薪水的最大值

  91. 76、select deptno, max_sal from

  92. (select avg(sal) max_sal,deptno from emp group by deptno)

  93. where max_sal =

  94. (select max(max_sal) from

  95. (select avg(sal) max_sal,deptno from emp group by deptno)

  96. );//求平均薪水最高的部门名称和编号。

  97. 77、select t1.deptno, grade, avg_sal from

  98. (select deptno, grade, avg_sal from

  99. (select deptno, avg(sal) avg_sal from emp group by deptno) t

  100. join salgrade s on(t.avg_sal between s.losal and s.hisal)

  101. ) t1

  102. join dept on (t1.deptno = dept.deptno)

  103. where t1.grade =

  104. (

  105. select min(grade) from

  106. (select deptno, grade, avg_sal from

  107. (select deptno, avg(sal) avg_sal from emp group by deptno) t

  108. join salgrade s on(t.avg_sal between s.losal and s.hisal)

  109. )

  110. )//求平均薪水等级最低的部门的名称 哈哈 确实比较麻烦

  111. 78、create view v$_dept_avg_sal_info as

  112. select deptno, grade, avg_sal from

  113. (select deptno, avg(sal) avg_sal from emp group by deptno) t

  114. join salgrade s on(t.avg_sal between s.losal and s.hisal);

  115. //视图的创建,一般以v$开头,但不是固定的

  116. 79、select t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1

  117. join dept on (t1.deptno = dept.deptno)

  118. where t1.grade =

  119. (

  120. select min(grade) from

  121. v$_dept_avg_sal_info t1

  122. )

  123. )//求平均薪水等级最低的部门的名称 用视图,能简单一些,相当于Java中方法的封装

  124. 80、---创建视图出现权限不足时候的解决办法:

  125. conn sys/admin as sysdba;

  126. 显示:连接成功 Connected

  127. grant create table, create view to scott;

  128. 显示: 授权成功 Grant succeeded

  129. 81、-------求比普通员工最高薪水还要高的经理人的名称 -------

  130. select ename, sal from emp where empno in

  131. (select distinct mgr from emp where mgr is not null)

  132. and sal >

  133. (

  134. select max(sal) from emp where empno not in

  135. (select distinct mgr from emp where mgr is not null)

  136. )

  137. 82、---面试题:比较效率

  138. select * from emp where deptno = 10 and ename like '%A%';//好,将过滤力度大的放在前面

  139. select * from emp where ename like '%A% and deptno = 10;

  140. 83、-----表的备份

  141. create table dept2 as select * from dept;

  142. 84、-----插入数据

  143. insert into dept2 values(50,'game','beijing');

  144. ----只对某个字段插入数据

  145. insert into dept2(deptno,dname) values(60,'game2');

  146. 85、-----将一个表中的数据完全插入另一个表中(表结构必须一样)

  147. insert into dept2 select * from dept;

  148. 86、-----求前五名员工的编号和名称(使用虚字段rownum 只能使用 < 或 = 要使用 > 必须使用子查询)

  149. select empno,ename from emp where rownum <= 5;

  150. 86、----求10名雇员以后的雇员名称--------

  151. select ename from (select rownum r,ename from emp) where r > 10;

  152. 87、----求薪水最高的前5个人的薪水和名字---------

  153. select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5;

  154. 88、----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水--------

  155. select ename, sal from

  156. (select ename, sal, rownum r from

  157. (select ename, sal from emp order by sal desc)

  158. )

  159. where r>=6 and r<=10

  160. 89、----------------创建新用户---------------

  161. 1、backup scott//备份

  162. exp//导出

  163. 2、create user

  164. create user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M on users

  165. grant create session(给它登录到服务器的权限),create table, create view to guohailong

  166. 3、import data

  167. imp

  168. 90、-----------事务回退语句--------

  169. rollback;

  170. 91、-----------事务确认语句--------

  171. commit;//此时再执行rollback无效

  172. 92、当正常断开连接的时候例如exit,事务自动提交。 当非正常断开连接,例如直接关闭dos窗口或关机,事务自动提交

  173. 93、有3个表S,C,SC

  174. S(SNO,SNAME)代表(学号,姓名)

  175. C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)

  176. SC(SNO,CNO,SCGRADE)代表(学号,课号成绩)

  177. 问题:

  178. 1,找出没选过“黎明”老师的所有学生姓名。

  179. 2,列出2门以上(含2门)不及格学生姓名及平均成绩。

  180. 3,即学过1号课程有学过2号课所有学生的姓名。

  181. 答案:

  182. 1、

  183. select sname from s join sc on(s.sno = sc.sno) join c on (sc.cno = c.cno) where cteacher <> '黎明';

  184. 2、

  185. select sname where sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2);

  186. 3、

  187. select sname from s where sno in (select sno, from sc where cno=1 and cno in

  188. (select distinct sno from sc where cno = 2);

  189. )

  190. 94、--------------创建表--------------

  191. create table stu

  192. (

  193. id number(6),

  194. name varchar2(20) constraint stu_name_mm not null,

  195. sex number(1),

  196. age number(3),

  197. sdate date,

  198. grade number(2) default 1,

  199. class number(4),

  200. email varchar2(50) unique

  201. );

  202. 95、--------------给name字段加入 非空 约束,并给约束一个名字,若不取,系统默认取一个-------------

  203. create table stu

  204. (

  205. id number(6),

  206. name varchar2(20) constraint stu_name_mm not null,

  207. sex number(1),

  208. age number(3),

  209. sdate date,

  210. grade number(2) default 1,

  211. class number(4),

  212. email varchar2(50)

  213. );

  214. 96、--------------给nameemail字段加入 唯一 约束 两个 null值 不为重复-------------

  215. create table stu

  216. (

  217. id number(6),

  218. name varchar2(20) constraint stu_name_mm not null,

  219. sex number(1),

  220. age number(3),

  221. sdate date,

  222. grade number(2) default 1,

  223. class number(4),

  224. email varchar2(50) unique

  225. );

  226. 97、--------------两个字段的组合不能重复 约束:表级约束-------------

  227. create table stu

  228. (

  229. id number(6),

  230. name varchar2(20) constraint stu_name_mm not null,

  231. sex number(1),

  232. age number(3),

  233. sdate date,

  234. grade number(2) default 1,

  235. class number(4),

  236. email varchar2(50),

  237. constraint stu_name_email_uni unique(email, name)

  238. );

  239. 98、--------------主键约束-------------

  240. create table stu

  241. (

  242. id number(6),

  243. name varchar2(20) constraint stu_name_mm not null,

  244. sex number(1),

  245. age number(3),

  246. sdate date,

  247. grade number(2) default 1,

  248. class number(4),

  249. email varchar2(50),

  250. constraint stu_id_pk primary key (id),

  251. constraint stu_name_email_uni unique(email, name)

  252. );

  253. ?99、--------------外键约束 被参考字段必须是主键 -------------

  254. create table stu

  255. (

  256. id number(6),

  257. name varchar2(20) constraint stu_name_mm not null,

  258. sex number(1),

  259. age number(3),

  260. sdate date,

  261. grade number(2) default 1,

  262. class number(4) references class(id),

  263. email varchar2(50),

  264. constraint stu_class_fk foreign key (class) references class(id),

  265. constraint stu_id_pk primary key (id),

  266. constraint stu_name_email_uni unique(email, name)

  267. );

  268. create table class

  269. (

  270. id number(4) primary key,

  271. name varchar2(20) not null

  272. );

  273. 100、---------------修改表结构,添加字段------------------

  274. alter table stu add(addr varchar2(29));

  275. 101、---------------删除字段--------------------------

  276. alter table stu drop (addr);

  277. 102、---------------修改表字段的长度------------------

  278. alter table stu modify (addr varchar2(50));//更改后的长度必须要能容纳原先的数据

  279. 103、----------------删除约束条件----------------

  280. alter table stu drop constraint 约束名

  281. 104、-----------修改表结构添加约束条件---------------

  282. alter table stu add constraint stu_class_fk foreign key (class) references class (id);

  283. 105、---------------数据字典表----------------

  284. desc dictionary;

  285. //数据字典表共有两个字段 table_name comments

  286. //table_name主要存放数据字典表的名字

  287. //comments主要是对这张数据字典表的描述

  288. 105、---------------查看当前用户下面所有的表、视图、约束-----数据字典表user_tables---

  289. select table_name from user_tables;

  290. select view_name from user_views;

  291. select constraint_name from user-constraints;

  292. 106、-------------索引------------------

  293. create index idx_stu_email on stu (email);// 在stu这张表的email字段上建立一个索引:idx_stu_email

  294. 107、---------- 删除索引 ------------------

  295. drop index index_stu_email;

  296. 108、---------查看所有的索引----------------

  297. select index_name from user_indexes;

  298. 109、---------创建视图-------------------

  299. create view v$stu as selesct id,name,age from stu;

  300. 视图的作用: 简化查询 保护我们的一些私有数据,通过视图也可以用来更新数据,但是我们一般不这么用 缺点:要对视图进行维护

  301. 110、-----------创建序列------------

  302. create sequence seq;//创建序列

  303. select seq.nextval from dual;// 查看seq序列的下一个值

  304. drop sequence seq;//删除序列

  305. 111、------------数据库的三范式--------------

  306. (1)、要有主键,列不可分

  307. (2)、不能存在部分依赖:当有多个字段联合起来作为主键的时候,不是主键的字段不能部分依赖于主键中的某个字段

  308. (3)、不能存在传递依赖

  309. ==============================================PL/SQL==========================

  310. 112、-------------------在客户端输出helloworld-------------------------------

  311. set serveroutput on;//默认是off,设成on是让Oracle可以在客户端输出数据

  312. 113、begin

  313. dbms_output.put_line('helloworld');

  314. end;

  315. /

  316. 114、----------------pl/sql变量的赋值与输出----

  317. declare

  318. v_name varchar2(20);//声明变量v_name变量的声明以v_开头

  319. begin

  320. v_name := 'myname';

  321. dbms_output.put_line(v_name);

  322. end;

  323. /

  324. 115、-----------pl/sql对于异常的处理(除数为0)-------------

  325. declare

  326. v_num number := 0;

  327. begin

  328. v_num := 2/v_num;

  329. dbms_output.put_line(v_num);

  330. exception

  331. when others then

  332. dbms_output.put_line('error');

  333. end;

  334. /

  335. 116、----------变量的声明----------

  336. binary_integer:整数,主要用来计数而不是用来表示字段类型 比number效率高

  337. number:数字类型

  338. char:定长字符串

  339. varchar2:变长字符串

  340. date:日期

  341. long:字符串,最长2GB

  342. boolean:布尔类型,可以取值truefalsenull//最好给一初值

  343. 117、----------变量的声明,使用 '%type'属性

  344. declare

  345. v_empno number(4);

  346. v_empno2 emp.empno%type;

  347. v_empno3 v_empno2%type;

  348. begin

  349. dbms_output.put_line('Test');

  350. end;

  351. /

  352. //使用%type属性,可以使变量的声明根据表字段的类型自动变换,省去了维护的麻烦,而且%type属性,可以用于变量身上

  353. 118、---------------Table变量类型(table表示的是一个数组)-------------------

  354. declare

  355. type type_table_emp_empno is table of emp.empno%type index by binary_integer;

  356. v_empnos type_table type_table_empno;

  357. begin

  358. v_empnos(0) := 7345;

  359. v_empnos(-1) :=9999;

  360. dbms_output.put_line(v_empnos(-1));

  361. end;

  362. 119、-----------------Record变量类型

  363. declare

  364. type type_record_dept is record

  365. (

  366. deptno dept.deptno%type,

  367. dname dept.dname%type,

  368. loc dept.loc%type

  369. );

  370. begin

  371. v_temp.deptno:=50;

  372. v_temp.dname:='aaaa';

  373. v_temp.loc:='bj';

  374. dbms_output.put_line(v temp.deptno || ' ' || v temp.dname);

  375. end;

  376. 120、-----------使用 %rowtype声明record变量

  377. declare

  378. v_temp dept%rowtype;

  379. begin

  380. v_temp.deptno:=50;

  381. v_temp.dname:='aaaa';

  382. v_temp.loc:='bj';

  383. dbms_output.put_line(v temp.deptno || '' || v temp.dname)

  384. end;

  385. 121、--------------sql%count 统计上一条sql语句更新的记录条数

  386. 122、--------------sql语句的运用

  387. declare

  388. v_ename emp.ename%type;

  389. v_sal emp.sal%type;

  390. begin

  391. select ename,sal into v_ename,v_sal from emp where empno = 7369;

  392. dbms_output.put_line(v_ename || '' || v_sal);

  393. end;

  394. 123、 -------- pl/sql语句的应用

  395. declare

  396. v_emp emp%rowtype;

  397. begin

  398. select * into v_emp from emp where empno=7369;

  399. dbms_output_line(v_emp.ename);

  400. end;

  401. 124、-------------pl/sql语句的应用

  402. declare

  403. v_deptno dept.deptno%type := 50;

  404. v_dname dept.dname%type :='aaa';

  405. v_loc dept.loc%type := 'bj';

  406. begin

  407. insert into dept2 values(v_deptno,v_dname,v_loc);

  408. commit;

  409. end;

  410. 125、-----------------ddl语言,数据定义语言

  411. begin

  412. execute immediate 'create table T (nnn varchar(30) default ''a'')';

  413. end;

  414. 126、------------------if else的运用

  415. declare

  416. v_sal emp.sal%type;

  417. begin

  418. select sal into v_sal from emp where empno = 7369;

  419. if(v_sal < 2000) then

  420. dbms_output.put_line('low');

  421. elsif(v_sal > 2000) then

  422. dbms_output.put_line('middle');

  423. else

  424. dbms_output.put_line('height');

  425. end if;

  426. end;

  427. 127、-------------------循环 =====do while

  428. declare

  429. i binary_integer := 1;

  430. begin

  431. loop

  432. dbms_output.put_line(i);

  433. i := i + 1;

  434. exit when (i>=11);

  435. end loop;

  436. end;

  437. 128、---------------------while

  438. declare

  439. j binary_integer := 1;

  440. begin

  441. while j < 11 loop

  442. dbms_output.put_line(j);

  443. j:=j+1;

  444. end loop;

  445. end;

  446. 129、---------------------for

  447. begin

  448. for k in 1..10 loop

  449. dbms_output.put_line(k);

  450. end loop;

  451. for k in reverse 1..10 loop

  452. dbms_output.put_line(k);

  453. end loop;

  454. end;

  455. 130、-----------------------异常(1)

  456. declare

  457. v_temp number(4);

  458. begin

  459. select empno into v_temp from emp where empno = 10;

  460. exception

  461. when too_many_rows then

  462. dbms_output.put_line('太多记录了');

  463. when others then

  464. dbms_output.put_line('error');

  465. end;

  466. 131、-----------------------异常(2)

  467. declare

  468. v_temp number(4);

  469. begin

  470. select empno into v_temp from emp where empno = 2222;

  471. exception

  472. when no_data_found then

  473. dbms_output.put_line('太多记录了');

  474. end;

  475. 132、----------------------创建序列

  476. create sequence seq_errorlog_id start with 1 increment by 1;

  477. 133、-----------------------错误处理(用表记录:将系统日志存到数据库便于以后查看)

  478. 创建日志表:

  479. create table errorlog

  480. (

  481. id number primary key,

  482. errcode number,

  483. errmsg varchar2(1024),

  484. errdate date

  485. );

  486. declare

  487. v_deptno dept.deptno%type := 10;

  488. v_errcode number;

  489. v_errmsg varchar2(1024);

  490. begin

  491. delete from dept where deptno = v_deptno;

  492. commit;

  493. exception

  494. when others then

  495. rollback;

  496. v_errcode := SQLCODE;

  497. v_errmsg := SQLERRM;

  498. insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);

  499. commit;

  500. end;

  501. 133---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多

  502. declare

  503. cursor c is

  504. select * from emp; //此处的语句不会立刻执行,而是当下面的open c的时候,才会真正执行

  505. v_emp c%rowtype;

  506. begin

  507. open c;

  508. fetch c into v_emp;

  509. dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录

  510. close c;

  511. end;

  512. 134----------------------使用do while 循环遍历游标中的每一个数据

  513. declare

  514. cursor c is

  515. select * from emp;

  516. v_emp c%rowtype;

  517. begin

  518. open c;

  519. loop

  520. fetch c into v_emp;

  521. (1) exit when (c%notfound); //notfound是oracle中的关键字,作用是判断是否还有下一条数据

  522. (2) dbms_output.put_line(v_emp.ename); //(1)(2)的顺序不能颠倒,最后一条数据,不会出错,会把最后一条数据,再次的打印一遍

  523. end loop;

  524. close c;

  525. end;

  526. 135------------------------while循环,遍历游标

  527. declare

  528. cursor c is

  529. select * from emp;

  530. v_emp emp%rowtype;

  531. begin

  532. open c;

  533. fetch c into v_emp;

  534. while(c%found) loop

  535. dbms_output.put_line(v_emp.ename);

  536. fetch c into v_emp;

  537. end loop;

  538. close c;

  539. end;

  540. 136--------------------------for 循环,遍历游标

  541. declare

  542. cursor c is

  543. select * from emp;

  544. begin

  545. for v_emp in c loop

  546. dbms_output.put_line(v_emp.ename);

  547. end loop;

  548. end;

  549. 137---------------------------带参数的游标

  550. declare

  551. cursor c(v_deptno emp.deptno%type, v_job emp.job%type)

  552. is

  553. select ename, sal from emp where deptno=v_deptno and job=v_job;

  554. --v_temp c%rowtype;此处不用声明变量类型

  555. begin

  556. for v_temp in c(30, 'click') loop

  557. dbms_output.put_line(v_temp.ename);

  558. end loop;

  559. end;

  560. 138-----------------------------可更新的游标

  561. declare

  562. cursor c //有点小错误

  563. is

  564. select * from emp2 for update;

  565. -v_temp c%rowtype;

  566. begin

  567. for v_temp in c loop

  568. if(v_temp.sal < 2000) then

  569. update emp2 set sal = sal * 2 where current of c;

  570. else if (v_temp.sal =5000) then

  571. delete from emp2 where current of c;

  572. end if;

  573. end loop;

  574. commit;

  575. end;

  576. 139-----------------------------------procedure存储过程(带有名字的程序块)

  577. create or replace procedure p

  578. is--这两句除了替代declare,下面的语句全部都一样

  579. cursor c is

  580. select * from emp2 for update;

  581. begin

  582. for v_emp in c loop

  583. if(v_emp.deptno = 10) then

  584. update emp2 set sal = sal +10 where current of c;

  585. else if(v_emp.deptno =20) then

  586. update emp2 set sal = sal + 20 where current of c;

  587. else

  588. update emp2 set sal = sal + 50 where current of c;

  589. end if;

  590. end loop;

  591. commit;

  592. end;

  593. 执行存储过程的两种方法:

  594. (1)exec p;(p是存储过程的名称)

  595. (2)

  596. begin

  597. p;

  598. end;

  599. /

  600. 140-------------------------------带参数的存储过程

  601. create or replace procedure p

  602. (v_a in number, v_b number, v_ret out number, v_temp in out number)

  603. is

  604. begin

  605. if(v_a > v_b) then

  606. v_ret := v_a;

  607. else

  608. v_ret := v_b;

  609. end if;

  610. v_temp := v_temp + 1;

  611. end;

  612. 141----------------------调用140

  613. declare

  614. v_a number := 3;

  615. v_b number := 4;

  616. v_ret number;

  617. v_temp number := 5;

  618. begin

  619. p(v_a, v_b, v_ret, v_temp);

  620. dbms_output.put_line(v_ret);

  621. dbms_output.put_line(v_temp);

  622. end;

  623. 142------------------删除存储过程

  624. drop procedure p;

  625. 143------------------------创建函数计算个人所得税

  626. create or replace function sal_tax

  627. (v_sal number)

  628. return number

  629. is

  630. begin

  631. if(v_sal < 2000) then

  632. return 0.10;

  633. elsif(v_sal <2750) then

  634. return 0.15;

  635. else

  636. return 0.20;

  637. end if;

  638. end;

  639. 144-----------------------------创建触发器(trigger) 触发器不能单独的存在,必须依附在某一张表上

  640. //创建触发器的依附表

  641. create table emp2_log

  642. (

  643. ename varchar2(30) ,

  644. eaction varchar2(20),

  645. etime date

  646. );

  647. create or replace trigger trig

  648. after insert or delete or update on emp2 ---for each row 加上此句,每更新一行,触发一次,不加入则值触发一次

  649. begin

  650. if inserting then

  651. insert into emp2_log values(USER, 'insert', sysdate);

  652. elsif updating then

  653. insert into emp2_log values(USER, 'update', sysdate);

  654. elsif deleting then

  655. insert into emp2_log values(USER, 'delete', sysdate);

  656. end if;

  657. end;

  658. 145-------------------------------通过触发器更新数据

  659. create or replace trigger trig

  660. after update on dept

  661. for each row

  662. begin

  663. update emp set deptno =:NEW.deptno where deptno =: OLD.deptno;

  664. end;

  665. //////只编译不显示的解决办法 set serveroutput on;

  666. 145-------------------------------通过创建存储过程完成递归

  667. create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is

  668. cursor c is select * from article where pid = v_pid;

  669. v_preStr varchar2(1024) := '';

  670. begin

  671. for i in 0..v_leave loop

  672. v_preStr := v_preStr || '****';

  673. end loop;

  674. for v_article in c loop

  675. dbms_output.put_line(v_article.cont);

  676. if(v_article.isleaf = 0) then

  677. p(v_article.id);

  678. end if;

  679. end loop;

  680. end;

  681. 146-------------------------------查看当前用户下有哪些表---

  682. 首先,用这个用户登录然后使用语句:

  683. select * from tab;

  684. 147-----------------------------用Oracle进行分页!--------------

  685. 因为Oracle中的隐含字段rownum不支持'>'所以:

  686. select * from (

  687. select rownum rn, t.* from (

  688. select * from t_user where user_id <> 'root'

  689. ) t where rownum <6

  690. ) where rn >3

  691. 148------------------------Oracle下面的清屏命令----------------

  692. clear screen; 或者 cle scr;

  693. 149-----------将创建好的guohailong的这个用户的密码改为abc--------------

  694. alter user guohailong identified by abc

  695. 当密码使用的是数字的时候可能会不行

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,053评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,527评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,779评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,685评论 1 276
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,699评论 5 366
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,609评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,989评论 3 396
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,654评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,890评论 1 298
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,634评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,716评论 1 330
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,394评论 4 319
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,976评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,950评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,191评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 44,849评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,458评论 2 342

推荐阅读更多精彩内容