1 select * from emp;
2
3 --分页查询
4 select rownum,empno from emp where rownum>0;
5 select * from(
6 select empno,rownum r from emp
7 )e where r>10 and r<13
8
9 ---PL/SQL入门--声明-type-rowtype-record-view-table
10
11 /*
12 PL/SQL语言
13 */
14
15 declare --定义部分
16 v_name varchar2(20);--变量的 声明： 变量名 变量类型
17 v_sal number(10,2);
18 begin--执行部分
19 v_name := 'lxm'; --变量
20 select ename ,sal into v_name,v_sal from emp where empno=&eno;--这里empno不写死的话，后边可以自己输入值
21 dbms_output.put_line(v_name||v_sal);
22 end;
23 select * from emp where empno=&eno;
24
25
26
27 /*
28 万能类型
29 将一个字段的类型作为一个数据类型
30 */
31 declare
32 v_name emp.job%type; --定义v_name的数据类型和emp里的job字段类型相同
33 begin
34
35 select job into v_name from emp where empno=7788;
36 dbms_output.put_line(v_name);
37 end;
38
39
40 /*
41 万能类型
42 将一行数据分别对应的类型作为一个打的数据类型
43 */
44 declare
45 v_rowdemo emp%rowtype; --定义变量的数据类型和emp整行的数据类型
46 --那么emp有几个字段，v_rowdemo就会有几个字段
47 begin
48 select * into v_rowdemo from emp where empno=7788;
49 dbms_output.put_line(v_rowdemo.ename||v_rowdemo.sal);--输出时使用变量.列名，直接输出整个变量会报错
50 end;
51
52
53
54
55 /*
56 record，是自己声明的一种一行多列的数据类型
57 使用一个变量，存储员工姓名和员工所在的部门名称
58
59 */
60
61 declare
62 --emp_info_record 是一个类型 相当于varchar
63 type emp_info_record is record(
64 v_ename varchar(30),
65 v_dname varchar2(30)
66 );
67 v_emp_info emp_info_record;
68 begin
69
70
71 /*
72 视图，
73 相当于一个虚表，查询结果被放在视图里。下次查东西可以直接从这里边查
74 scott默认没有创建视图的权限，所以要先以管理员身份登录给他赋权限
75 grant create view to scott
76 授权之后，scott用户就有了创建视图的权限
77 */
78 create view myview --create view 视图名
79 as
80 select empno,ename,sal from emp;
81 --视图创建成功，可以从其中查数据
82 select empno from myview
83
84
85 /*
86 %record
87 */
88 declare
89 type emp_record_type is record( --定义record
90 ename emp.ename%type,
91 sal emp.sal%type,
92 comm emp.comm%type,
93 total_sal emp.sal%type
94 );
95 v_emp_record emp_record_type; --声明一个record
96 begin
97 select ename,sal,nvl(comm,0),sal+nvl(comm,0) --执行查询
98 into v_emp_record
99 from emp where empno=7369;
100 dbms_output.put_line('姓名:'||v_emp_record.ename);
101 dbms_output.put_line('薪水:'||v_emp_record.sal);
102 dbms_output.put_line('奖金:'||v_emp_record.comm);
103 dbms_output.put_line('总薪水:'||v_emp_record.total_sal);
104 end;
105
106
107 /*
108 table数据类型
109 */
110 declare --定义一个table类型
111 type dept_table_type is table of dept%rowtype--type table名字 is table of table类型
112 index by binary_integer; --将主键定义为二进制
113 v_dept_table dept_table_type;
114 begin
115 select * into v_dept_table(0) from dept where deptno=10; --每次将一行主句赋值给table的另一行
116 select * into v_dept_table(1) from dept where deptno=20;
117 dbms_output.put_line('部门编号： '||v_dept_table(0).deptno||' 部门名称：'
118 ||v_dept_table(0).dname||' 位置：'||v_dept_table(0).loc);
119 dbms_output.put_line('部门编号： '||v_dept_table(1).deptno||' 部门名称：'
120 ||v_dept_table(1).dname||' 位置：'||v_dept_table(1).loc);
121 end;
122
123
124 /*
125 批量赋值
126 bulk:体积、大块
127 collect：搜集、收集
128 bulk collect into 相当于批量赋值
129 */
130 declare
131 type v_dept_table is table of dept%rowtype
132 index by binary_integer;
133 v_dept v_dept_table;
134 begin
135 --select * bulk collect into 表明 from 表名
136 select * bulk collect into v_dept from dept; --将所有的数据赋值给v_dept
137 dbms_output.put_line(v_dept(2).dname); --取的时候，取自己想要的
138 end;
139
140
141
142 --------------流程控制-----------------------
143 /*
144 if循环
145 相当于 if循环
146 if（n==5）
147 输出n
148
149 ---
150 if
151 条件1 then
152 执行语句1；
153 elsif 条件2 then
154 执行语句2；
155 else
156 执行语句;
157 end if;
158 */
159 declare
160 v_n number := 4;
161 begin
162 if v_n=5 then
163 dbms_output.put_line(v_n);
164 elsif v_n=4 then --注意oracle里没有else if，只有elsif，不要错误写成elseif
165 dbms_output.put_line('44444is'||v_n);
166 else
167 dbms_output.put_line('未知数');
168 end if;
169 end;
170
171
172
173 /*
174 case循环
175 case 变量
176 when 条件 then 执行语句；
177 when 条件 then 执行语句；
178 else 执行语句
179 end case
180 */
181
182 declare
183 v_deptno dept.deptno%type:=&deptno;
184 begin
185 case v_deptno
186 when 10 then dbms_output.put_line('我的部门是： '||v_deptno);
187 when 20 then dbms_output.put_line('我的部门是： '||v_deptno);
188 when 30 then dbms_output.put_line('我的部门是： '||v_deptno);
189 when 40 then dbms_output.put_line('我的部门是： '||v_deptno);
190 else dbms_output.put_line('不存在该部门');
191 end case;
192 end;
193
194
195 /*
196 loop循环
197 loop
198 [exit when 条件]
199 end loop;
200
201 相当于do while循环，先执行一次，再判断是否满足条件
202 */
203 declare
204 v_n number := 10;
205 begin
206 loop
207 v_n := v_n-1; --oracle中没有 v_n--;没有自减
208 dbms_output.put_line(v_n);
209 --exit when v_n=0;
210 if v_n=0 then exit;
211 end if;
212 end loop;
213 end;
214
215
216 /*
217 loop 循环和for循环嵌套
218 for n in 1..10表明n在[1,10]之间
219 reverse 表示降序排列，
220 */
221
222
223 begin
224 -- for n in 1..10
225 for n in reverse 1..10 --inverse代表逆序
226 loop
227 dbms_output.put_line(n);
228 end loop;
229 end;
230
231
232 /*
233 `相当于while循环
234 while 条件 loop
235 执行语句
236 end loop
237 */
238 declare
239 v_n number := 10;
240 begin
241 while v_n>0 loop
242 v_n := v_n-1;
243 dbms_output.put_line('n是： '||v_n);
244 end loop;
245 end;
246
247
248 --游标--
249 /*
250 定义游标：cursor 游标名字 is select_statement
251 打开游标：open 游标名字
252 提取游标数据：fetch 游标名字 into
253 关闭游标：close 游标名字
254
255 显式游标：处理select语句返回的多行数据
256 1）：显示游标属性：
257 %ISOPEN：游标是否打开，打开为true，关闭为false
258 %FOUND：检查是否从结果集中提取到数据，提取到为true，没有提取到为false
259 %NOTFOUND:与%FOUND相反，此外 还可以用这个属性退出循环
260 %ROWCOUNT返回当前位置已经提取到的实际行数
261 属性使用方法 游标名+属性
262
263 2）：带有参数的游标，多处使用游标，传入不同的参数时，获得不同的结果
264 cursor 游标名字 (参数名字，参数类型) is select_statement
265 定义时必须声明参数的数据类型，而不能定义参数的长度
266 隐式游标：用来处理select into 和DML语句
267 */
268
269 --显示游标
270 --eg1：
271 declare
272 v_dept dept%rowtype;
273 cursor cur_dept is select * from dept where deptno>10; --声明游标
274 begin
275 open cur_dept; --打开游标
276 loop
277 fetch cur_dept into v_dept; --提取数据
278 exit when cur_dept%NOTFOUND;
279 dbms_output.put_line('编号:'||v_dept.deptno||' 名字：'||v_dept.dname||' 位置:'||v_dept.loc);
280 end loop;
281 close cur_dept;
282 end;
283
284
285 --eg2：用table结构提取游标数据
286 declare
287 type v_dept is table of dept%rowtype
288 index by binary_integer; --声明表结构
289 cursor cur_dept is select * from dept;--声明游标
290 v_dept_table v_dept;--声明一个变量是v_dept类型的
291 begin
292 open cur_dept;
293 fetch cur_dept bulk collect into v_dept_table;--将游标中的数据批块放入到表里
294 close cur_dept; --数据已经拿到，就可以关闭游标
295 for i in v_dept_table.first..v_dept_table.last loop--遍历一个表结构就用 for in
296 dbms_output.put_line('位置：' ||v_dept_table(i).loc);--循环输出，就用下表
297 end loop;
298 end;
299
300 --eg3:带参数的游标
301 declare
302 cursor cur_dpet(para_deptno number) is --定义有参数的游标
303 select dname, loc from dept where deptno=para_deptno;--将参数作为查询条件
304 dept_record cur_dpet%rowtype; --定义一个变量，类型和游标的行类型一直
305 begin
306 open cur_dpet(20); --打开游标，同时把参数传进去
307 loop
308 fetch cur_dpet into dept_record; --提取数据，放到dept_record里
309 exit when cur_dpet%notfound;
310 dbms_output.put_line(dept_record.dname||' '||dept_record.loc);
311 end loop;
312 close cur_dpet; --关闭游标
313 end;
314
315
316
317 --eg4：for循环简化游标遍历,
318 --用for循环时，oracle会隐式打开游标并且提取数据后关闭，
319 declare
320 cursor cur_dept is select * from dept;
321 begin
322 for cur_row in cur_dept loop --n相当于游标中存储的数据，所对应的一个个的行数据的对象
323 dbms_output.put_line('行编号是'||cur_dept%rowcount||' 部门名字是：'||cur_row.dname);
324 end loop;
325 end;
326
327 --eg5:使用for循环时，游标可以更简化,声明、打开、关闭、都可以省略
328
329 begin
330 for dept_row in(select dname,loc from dept) loop
331 dbms_output.put_line('部门位置是--：'||dept_row.loc);
332 end loop;
333 end;
334
335 --游标变量--
336 /*
337 游标和游标变量的区别
338 1）：游标是数据库中一个命名的工作区，与固定的SQL语句相关联，在编译时是已知的，
339 是静态的，永远指向一个相同的查询工作区
340 ---简而言之，对应的SELECT语句，在游标定义的时候，在declare声明部分就已经写好了
341 2）：游标变量
342 A:语法
343 type ref_type_name IS REF CURSOR; --先定义一个游标变量
344 v_ref ref_type_name; 再声明一个变量，这个变量的类型是 上边定义好的游标变量的类型
345 B：游标变量在运行时可不同的SQL语句先关联，这样运行不同的SQL语句就可以引用不同的工作空间
346 */
347
348 --eg：游标变量
349 declare
350 type ref_type_name is ref cursor;--定义
351 cur_emp ref_type_name; --定义游标的类型，是个游标变量
352 v_row emp%rowtype; --后边用，用来存储游标中的一行数据
353 begin
354 open cur_emp for select * from emp where empno=7369; --open for ,打开
355 loop
356 fetch cur_emp into v_row ;
357 exit when cur_emp%notfound;
358 dbms_output.put_line('行号：'||cur_emp%rowcount||' 部门编号：'||v_row.empno||';部门名字：'||v_row.ename);
359 end loop;
360 end;
361
362 /*
363 隐式游标
364 跟显示游标不同的是，游标的定义，打开，数据提取，关闭，都是由数据库隐式来操作的
365 属性只有两个 SQL%FOUND, SQL%NOTFOUND
366 */
367
368 declare
369 v_empno number(10) := 7369;
370 begin
371 update emp set ename='小绿绿' where empno=v_empno;
372 if sql%found then --判断隐含游标sql是否有影响行数
373 dbms_output.put_line('修改成功');
374 else
375 dbms_output.put_line('修改失败，员工不存在');
376 end if;
377 end;
378
379
380 ------触发器---
381 /*
382 create or replace trigger 触发器名字
383 before[after]
384 事件[update or delete or insert]
385 on 表名
386
387 :new表和:old表
388 只有用了 for each row 才能用new ，old
389 new和old 相当于虚表
390 insert：新增,把新数据存到:new，再执行新增
391 delete：删除，把老数据存到:old，再执行删除
392 update:修改，把老数据存到:old，把新数据存到:new，判断:old和:new表中数据是否一样，
393 一样不执行任何修改，不一样执行修改
394
395 create or replace trigger 触发器名字
396 before[after] --触发时机
397 update [delete、insert、select]操作的动作
398 on 表名
399 for each row --没触发一行就触发一次 触发器
400 begin
401 if(updating) then
402 执行语句
403 end if;
404 end;
405 */
406 create or replace trigger tri_dept
407 after --触发时机，before为之前，after为之后
408 update or delete or insert --哪些事件会触发 触发器
409 on dept --对哪张表操作
410 for each row --每一行操作时都出发，如果不写，比如对整个表都进行修改，但只触发一次，
411 --写了这一句，sql语句设计到几行，就出发几次事件
412 begin
413 if(updating) then
414 dbms_output.put_line('你出发的操作是update旧数据是： '||:old.loc);--old是原表中的数据
415 dbms_output.put_line('你出发的操作是update新数据是： '||:new.loc);--new是修改后的新数据
416 end if;
417 if(deleting) then
418 dbms_output.put_line('触发器触发delete...');
419 end if;
420 if(inserting) then
421 dbms_output.put_line('触发器触发insert');
422 end if;
423 end;
424
425
426
427 --存储过程--procedure
428 /*
429 第一种 无参数的过程，
430
431 语法：
432 create or repalace procedure 名字
433 is
434 声明部分
435 begin
436 执行部分
437 exception
438 异常处理部分
439 end;
440 */
441
442 --一个没有exception的无参存储过程
443 create or replace procedure pro_emp_sal--注意这里，过程名字后边没有（）
444 is
445 begin
446 update dept set loc='sahgnhai' where deptno = 20;--
447 end;
448
449 --一个有exception的无参存储过程
450 create or replace procedure pro_sel_sal
451 is
452 v_sal number(10);
453 begin
454 select sal into v_sal from emp where deptno=70;
455 dbms_output.put_line(v_sal);
456 exception
457 when no_data_found then
458 dbms_output.put_line('员工编号不存在');
459 end;
460
461 drop procedure pro_sel_sal; --删除 存储过程 drop procedure 过程名字
462 --调用无参过程的三种方法
463 call pro_sel_sal();--call 过程名字()；
464 exec pro_emp_sal; --exec 过程名字； 但是这种方式只有再sqlplus 或者命令窗口中可以用，再plsql中不可以用
465 begin --begin 过程名字； end；
466 pro_sel_sal;
467 end;
468 select * from emp;
469 update dept set loc='aa' where deptno='40'
470
471
472 /*
473 有参的存储过程（如果不指定参数模式，默认为输入参数）
474
475 有三种参数形式：
476 1）：in传参 ，定义被in，输入参数，这个in可以不写，默认就是输入传参
477 2）：out传参，该参数是用来输出的
478 3）：in out 传参 该参数既用来输入，也用来输出
479
480 有三种参数传递的形式
481 1）：按照参数顺序依次放入参数
482 2）：用=>传递参数 eg: pro_emp_sal(v_no=>'7369'); 注意，变量在前边，传过来的值在后边
483 3）：混合方式传参，参数1，放在第一个位置，后边的参数用 =>传递参数
484 不管是哪种传参方式，被in，或者out，或者 inout修饰的参数，在调用的时候一定要声明
485 */
486
487 /*
488 in传参 创建存储过程
489 */
490 create or replace procedure pro_emp_sal(v_no in emp.empno%type)
491 is
492 v_sal number(10);
493 begin
494 select sal into v_sal from emp where empno=v_no;
495 dbms_output.put_line(v_no ||' 的工资是：'||v_sal);
496 exception
497 when no_data_found then
498 dbms_output.put_line('请输入正确的员工编号');
499 end;
500 --调用 存储过程
501 declare
502 v_no number(10);
503 begin
504 v_no := &empno;
505 pro_emp_sal(v_no);
506 end;
507
508
509 /*
510 out 传递参数的存储过程
511 */
512 create or replace procedure pro_sel_al(v_sal out emp.sal%type)
513 is
514 begin
515 select sal into v_sal from emp where empno=7369;
516 dbms_output.put_line(v_sal);
517 end;
518
519 --调用存储过程
520 declare
521 v_sals number(10);
522 begin
523 pro_sel_al(v_sal=>v_sals);-- =>传递参数
524 end;
525
526 /*
527 in,out分别传递参数，的存储过程
528 */
529 create or replace procedure pro_emp_sal(v_no in emp.empno%type, v_sal out emp.sal%type)
530 is
531 begin
532 select sal into v_sal from emp where empno=v_no;
533 dbms_output.put_line('工资是： '||v_sal);
534 end;
535
536 --调用 存储过程
537 declare
538 v_no emp.empno%type;
539 v_sal emp.sal%type;
540 begin
541 v_no := &no; --输入的变量一定要在执行的时候定义出输入
542 pro_emp_sal(v_no,v_sal); --按照参数位置传参
543
544 end;
545
546 /*
547 in out传递参数
548 在这里 in out 修饰同一个变量，则这个变量既是输入变量也是输出变量
549 */
550 create or replace procedure pro_emp_sal(empnum in out number)
551 is
552 begin
553 select sal into empnum from emp where empno=empnum;
554 -- dbms_output.put_line(v_no ||'的工资是： '|| empnum);
555 end;
556
557 --调用存储过程
558 declare
559 in_outnum emp.empno%type;
560 begin
561 in_outnum := &no;
562 pro_emp_sal(in_outnum);
563 dbms_output.put_line('工资是： '||in_outnum);
564 end;
565
566
567
568 ----开发函数----
569 /*
570 create or replace function 名字()
571 return 返回值类型; --一定要有的
572 is[as]
573 变量声明部分
574 begin
575 执行语句
576 return 语句； --一定要有
577 exception
578 end;
579 */
580
581 create or replace function getsum(m number , n number)
582 return number
583 is
584 mn number;
585 begin
586 mn := m+n;
587 return mn;
588 end;
589
590 --调用function
591 declare
592 m number;
593 n number;
594 mn number;
595 begin
596 m := &m;
597 n := &n;
598 mn := getsum(m,n);
599 dbms_output.put_line(mn);
600 end;
601
602 ---过程与函数的异同-------
603 /*
604 1:相同点
605 1）：都用in模式传入数据，都用out模式传出数据
606 2）：参数的传递都可以用位置传递法和名称传递法
607 3）：输入参数时都可以有默认值，都可以传值
608 4）：都有声明部分、执行部分、异常部分
609 2：不同点
610 1）:没有返回值，或者只输出一个值时用function
611 2）：输出多个值时用存储过程
612 3）：输出多个值时也可以用function，但是这被认为是不规范的写作习惯
613 4）：一般存储过程用来完成某个动作，function用来计算或者返回一个值
614 */
615 --包--
616 /*
617 包存在的意义：满足oracle种模块化的需求，在大型项目中，每个模块有很多的过程和函数，不便于管理和维护
618 甚至造成误删，所以分包后便于管理，
619 解决了命名的问题，不同包里的函数或者过程可以命名相同
620
621
622 包头--语法： --包头规范相当于java中的接口
623 create or replace package 包名 is
624 function 函数名() return 返回类型；
625 procedure 过程名();
626 end 包名;
627
628 包体--语法： --包体规范相当于java中的接口实现
629 create or replace package body 包名 is
630 写function; （这里直接写，没有create or replace 这句话）
631 写过程；（这里直接写，没有create or replace 这句话）
632 end 包名
633
634 --调用方法：
635 decalre
636 声明部分
637 begin
638 包名.函数名；
639 包名.过程名；
640 end;
641 */
642 --包头
643 create or replace package my_package is
644 function plus(m number, n number) return number;
645 procedure cheng(x number, y number, xy out number);
646 end my_package;
647
648 --包体
649 create or replace package body my_package is
650 function plus(m number, n number) return number
651 is nm number;
652 begin
653 nm := m+n;
654 return nm;
655 end;
656 procedure cheng(x number, y number, xy out number)
657 is
658 begin
659 xy := x+y;
660 dbms_output.put_line(xy);
661 end;
662 end my_package;
663 --调用包里的方法
664 declare
665 cc number(10);
666 begin
667 -- cc:= my_package.plus(11,22);
668 --dbms_output.put_line('两个数的和是：'||cc);
669 my_package.cheng(11,2,xy=>cc);
670 end;
671
672 /*
673 数据字典：user_source
674 跟用户相关的子程序和和源代码被存在里边
675 假如要查看 包名为my_package，的源代码
676 select text from user_source where name='my_package';
677 删除子程序 :drop procedure proce_anme
678 删除包体: drop package body 包名
679 删除包头和包体 drop pacakge 包名
680 */
681 select text from user_source where name='MY_PACKAGE';
682
683 --------游标--------
684
685 /*
686
687 1:游标是啥？
688
689 游标是指向上下文区的指针，它为应用提供了一种对具有多行查询结果集的数据，中
690
691 的每一行分别进行单独处理的方法
692
693 */
694
695 declare
696
697 v_dept dept%rowtype;
698
699 cursor dept_cursor is select * from dept where deptno>10; --定义游标
700
701 begin
702
703 open dept_cursor; --打开游标
704
705 loop
706
707 fetch dept_cursor into v_dept; --提取数据
708
709 exit when dept_cursor%notfound; --判断循环退出条件
710
711 dbms_output.put_line('编号：'||v_dept.deptno
712
713 ||' 名称：'||v_dept.dname||' 地址：'||v_dept.loc);
714
715 end loop;
716
717 close dept_cursor; --关闭游标
718
719 end;
720
721
浙公网安备 33010602011771号