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