oracle系列（一）-- 常用脚本汇总--过程、函数、流程控制、触发器

            1
            select
            *
            from
             emp;
  
            2
            3
            --
            分页查询
            4
            select rownum,empno from  emp where rownum>0;
  5select*from(
  6select  empno,rownum r from emp 
  7 )e where r>10and r<1389---PL/SQL入门--声明-type-rowtype-record-view-table1011/*
 12   PL/SQL语言
 13 */1415declare--定义部分16    v_name varchar2(20);--变量的 声明： 变量名 变量类型17    v_sal number(10,2);
 18begin--执行部分19   v_name :=‘lxm‘; --变量20select ename ,sal into v_name,v_sal from emp where empno=&eno;--这里empno不写死的话，后边可以自己输入值21   dbms_output.put_line(v_name||v_sal);
 22end;
 23select*from emp where empno=&eno;
 24252627/*
 28   万能类型
 29   将一个字段的类型作为一个数据类型
 30 */31declare32     v_name emp.job%type; --定义v_name的数据类型和emp里的job字段类型相同33begin3435select job  into v_name from emp  where empno=7788;
 36   dbms_output.put_line(v_name);
 37end;
 383940/*
 41   万能类型
 42   将一行数据分别对应的类型作为一个打的数据类型
 43 */44declare45    v_rowdemo emp%rowtype; --定义变量的数据类型和emp整行的数据类型46--那么emp有几个字段，v_rowdemo就会有几个字段47begin48select*into v_rowdemo from emp where empno=7788;
 49    dbms_output.put_line(v_rowdemo.ename||v_rowdemo.sal);--输出时使用变量.列名，直接输出整个变量会报错50end;
 5152535455/*
 56  record，是自己声明的一种一行多列的数据类型
 57   使用一个变量，存储员工姓名和员工所在的部门名称
 58 
 59 */6061declare62--emp_info_record 是一个类型  相当于varchar63    type emp_info_record is record(
 64         v_ename varchar(30),
 65         v_dname varchar2(30)
 66    );
 67   v_emp_info  emp_info_record;
 68begin697071/*
 72   视图，
 73   相当于一个虚表，查询结果被放在视图里。下次查东西可以直接从这里边查
 74   scott默认没有创建视图的权限，所以要先以管理员身份登录给他赋权限
 75   grant create view to scott
 76   授权之后，scott用户就有了创建视图的权限
 77 */78createview myview --create view  视图名79as80select empno,ename,sal from emp;
 81--视图创建成功，可以从其中查数据82select  empno  from  myview
 838485/*
 86    %record
 87 */88declare89    type emp_record_type is record( --定义record90         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; --声明一个record96begin97select ename,sal,nvl(comm,0),sal+nvl(comm,0) --执行查询98into v_emp_record
 99from 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);
104end;
105106107/*
108    table数据类型
109 */110declare--定义一个table类型111    type dept_table_type istableof dept%rowtype--type table名字 is table of table类型 112indexby binary_integer; --将主键定义为二进制113    v_dept_table dept_table_type;
114begin115select*into v_dept_table(0) from dept where deptno=10; --每次将一行主句赋值给table的另一行116select*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);
121end;
122123124/*
125   批量赋值 
126   bulk:体积、大块
127   collect：搜集、收集
128   bulk collect into 相当于批量赋值
129 */130declare131   type v_dept_table istableof dept%rowtype
132indexby binary_integer;
133   v_dept v_dept_table;
134begin135--select *  bulk collect into 表明 from 表名136select*bulk collect into v_dept from dept; --将所有的数据赋值给v_dept137   dbms_output.put_line(v_dept(2).dname); --取的时候，取自己想要的138end;
139140141142--------------流程控制-----------------------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 */159declare160    v_n number :=4;
161begin162if v_n=5then163     dbms_output.put_line(v_n);
164   elsif v_n=4then--注意oracle里没有else if，只有elsif，不要错误写成elseif165     dbms_output.put_line(‘44444is‘||v_n);
166else167      dbms_output.put_line(‘未知数‘);
168endif;
169end;
170171172173/*
174   case循环
175   case 变量
176     when 条件 then 执行语句；
177     when 条件 then 执行语句；
178     else 执行语句
179    end case
180 */181182declare183   v_deptno dept.deptno%type:=&deptno;
184begin185case v_deptno
186when10then dbms_output.put_line(‘我的部门是：  ‘||v_deptno);
187when20then dbms_output.put_line(‘我的部门是：  ‘||v_deptno);
188when30then dbms_output.put_line(‘我的部门是：  ‘||v_deptno);
189when40then dbms_output.put_line(‘我的部门是：  ‘||v_deptno);
190else dbms_output.put_line(‘不存在该部门‘);
191endcase;
192end;
193194195/*
196  loop循环
197  loop
198    [exit when 条件]
199  end loop;
200  
201  相当于do while循环，先执行一次，再判断是否满足条件
202 */203declare204  v_n number :=10;
205begin206   loop
207     v_n := v_n-1; --oracle中没有 v_n--;没有自减208     dbms_output.put_line(v_n);
209--exit when v_n=0;210if v_n=0thenexit;
211endif;
212end loop;
213end;
214215216/*
217    loop 循环和for循环嵌套
218    for n in 1..10表明n在[1,10]之间
219    reverse 表示降序排列，
220 */221222223begin224-- for n in 1..10 225for n inreverse1..10--inverse代表逆序226     loop
227       dbms_output.put_line(n);
228end loop;
229end;
230231232/*
233 `相当于while循环
234   while 条件 loop
235     执行语句
236   end loop
237 */238declare239   v_n number :=10;
240begin241while v_n>0 loop
242     v_n := v_n-1;
243     dbms_output.put_line(‘n是： ‘||v_n);
244end loop;
245end;
246247248--游标--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 */268269--显示游标270--eg1：271declare272   v_dept dept%rowtype;
273cursor cur_dept isselect*from dept where deptno>10; --声明游标274begin275open cur_dept;  --打开游标276   loop
277fetch cur_dept into v_dept; --提取数据278exitwhen cur_dept%NOTFOUND;
279     dbms_output.put_line(‘编号:‘||v_dept.deptno||‘ 名字：‘||v_dept.dname||‘  位置:‘||v_dept.loc);
280end loop;
281close cur_dept;
282end;
283284285--eg2：用table结构提取游标数据286declare287       type v_dept istableof dept%rowtype 
288indexby binary_integer;  --声明表结构289cursor cur_dept isselect*from dept;--声明游标290       v_dept_table v_dept;--声明一个变量是v_dept类型的291begin292open cur_dept;
293fetch cur_dept bulk collect into v_dept_table;--将游标中的数据批块放入到表里294close cur_dept; --数据已经拿到，就可以关闭游标295for i in v_dept_table.first..v_dept_table.last loop--遍历一个表结构就用 for in296        dbms_output.put_line(‘位置：‘||v_dept_table(i).loc);--循环输出，就用下表297end loop;
298end;
299300--eg3:带参数的游标301declare302cursor cur_dpet(para_deptno number) is--定义有参数的游标303select dname, loc  from dept where deptno=para_deptno;--将参数作为查询条件304      dept_record cur_dpet%rowtype; --定义一个变量，类型和游标的行类型一直305begin306open cur_dpet(20); --打开游标，同时把参数传进去307     loop
308fetch cur_dpet into dept_record; --提取数据，放到dept_record里309exitwhen cur_dpet%notfound;
310         dbms_output.put_line(dept_record.dname||‘‘||dept_record.loc);  
311end loop;
312close cur_dpet; --关闭游标313end;
314315316317--eg4：for循环简化游标遍历,318--用for循环时，oracle会隐式打开游标并且提取数据后关闭，319declare320cursor cur_dept isselect*from dept; 
321begin322for cur_row in cur_dept loop --n相当于游标中存储的数据，所对应的一个个的行数据的对象323       dbms_output.put_line(‘行编号是‘||cur_dept%rowcount||‘   部门名字是：‘||cur_row.dname);
324end loop;
325end;
326327--eg5:使用for循环时，游标可以更简化,声明、打开、关闭、都可以省略328329begin330for dept_row in(select dname,loc from dept) loop
331       dbms_output.put_line(‘部门位置是--：‘||dept_row.loc);
332end loop;
333end;
334335--游标变量--  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  */347348--eg：游标变量349declare350          type ref_type_name is ref cursor;--定义351          cur_emp ref_type_name; --定义游标的类型，是个游标变量352          v_row emp%rowtype; --后边用，用来存储游标中的一行数据353begin354open cur_emp forselect*from emp where empno=7369; --open for ,打开355   loop
356fetch cur_emp into v_row ;
357exitwhen cur_emp%notfound;
358     dbms_output.put_line(‘行号：‘||cur_emp%rowcount||‘   部门编号：‘||v_row.empno||‘;部门名字：‘||v_row.ename);
359end loop;
360end;
361362/*
363   隐式游标
364       跟显示游标不同的是，游标的定义，打开，数据提取，关闭，都是由数据库隐式来操作的
365       属性只有两个  SQL%FOUND, SQL%NOTFOUND
366  */367368declare369       v_empno number(10) :=7369;
370begin371update emp set ename=‘小绿绿‘where empno=v_empno;
372if sql%found then--判断隐含游标sql是否有影响行数373      dbms_output.put_line(‘修改成功‘);
374else375         dbms_output.put_line(‘修改失败，员工不存在‘);
376endif;
377end;
378379380------触发器---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 */406createorreplacetrigger  tri_dept
407 after --触发时机，before为之前，after为之后408updateordeleteorinsert--哪些事件会触发 触发器409on dept  --对哪张表操作410for each row --每一行操作时都出发，如果不写，比如对整个表都进行修改，但只触发一次，411--写了这一句，sql语句设计到几行，就出发几次事件412begin413if(updating) then414        dbms_output.put_line(‘你出发的操作是update旧数据是： ‘||:old.loc);--old是原表中的数据415        dbms_output.put_line(‘你出发的操作是update新数据是： ‘||:new.loc);--new是修改后的新数据416endif;
417if(deleting) then418        dbms_output.put_line(‘触发器触发delete...‘);
419endif;
420if(inserting) then421        dbms_output.put_line(‘触发器触发insert‘);
422endif;
423end;
424425426427--存储过程--procedure428/*
429                  第一种 无参数的过程，
430     
431     语法：
432     create or repalace procedure 名字
433     is
434            声明部分
435     begin
436       执行部分
437       exception
438         异常处理部分
439      end;
440 */441442--一个没有exception的无参存储过程443createorreplaceprocedure pro_emp_sal--注意这里，过程名字后边没有（）444is445begin446update dept set loc=‘sahgnhai‘where deptno =20;--
447end;
448449--一个有exception的无参存储过程  450createorreplaceprocedure pro_sel_sal
451is452     v_sal number(10);
453begin454select sal into v_sal from emp where deptno=70;
455    dbms_output.put_line(v_sal);
456    exception
457when no_data_found then458        dbms_output.put_line(‘员工编号不存在‘);
459end;
460461dropprocedure pro_sel_sal; --删除 存储过程 drop procedure 过程名字462--调用无参过程的三种方法463   call pro_sel_sal();--call 过程名字()；464exec pro_emp_sal; --exec 过程名字；  但是这种方式只有再sqlplus 或者命令窗口中可以用，再plsql中不可以用465begin--begin 过程名字； end；466        pro_sel_sal;
467end;
468select*from emp;
469update dept set loc=‘aa‘where deptno=‘40‘470471472/*
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 */486487/*
488   in传参 创建存储过程
489 */490createorreplaceprocedure pro_emp_sal(v_no in emp.empno%type)
491is492   v_sal number(10);
493begin494select sal into v_sal from emp where empno=v_no;
495    dbms_output.put_line(v_no ||‘ 的工资是：‘||v_sal);
496     exception  
497when no_data_found  then498         dbms_output.put_line(‘请输入正确的员工编号‘);
499end;
500--调用 存储过程501declare502  v_no number(10);    
503begin504  v_no :=&empno;
505  pro_emp_sal(v_no);
506end;
507508509/*
510  out 传递参数的存储过程
511 */512createorreplaceprocedure pro_sel_al(v_sal out emp.sal%type)
513is514begin515select sal into v_sal  from emp where empno=7369;
516   dbms_output.put_line(v_sal);
517end;
518519--调用存储过程520declare521  v_sals number(10);
522begin523   pro_sel_al(v_sal=>v_sals);-- =>传递参数524end;
525526/*
527   in,out分别传递参数，的存储过程
528 */529createorreplaceprocedure pro_emp_sal(v_no in emp.empno%type, v_sal out emp.sal%type)
530is531begin532select sal into v_sal from emp where empno=v_no;
533   dbms_output.put_line(‘工资是： ‘||v_sal);
534end;
535536--调用 存储过程537declare538    v_no emp.empno%type;
539    v_sal emp.sal%type;
540begin541   v_no :=&no; --输入的变量一定要在执行的时候定义出输入542  pro_emp_sal(v_no,v_sal); --按照参数位置传参543544end;
545546/*
547   in out传递参数
548   在这里 in out 修饰同一个变量，则这个变量既是输入变量也是输出变量
549 */550createorreplaceprocedure pro_emp_sal(empnum in out number)
551is552begin553select  sal into empnum from emp where empno=empnum;
554--  dbms_output.put_line(v_no ||‘的工资是： ‘|| empnum);555end;
556557--调用存储过程558declare559  in_outnum emp.empno%type;
560begin561   in_outnum :=&no;
562   pro_emp_sal(in_outnum);
563   dbms_output.put_line(‘工资是： ‘||in_outnum);
564end;
565566567568----开发函数----569/*
570    create or replace function 名字()
571       return 返回值类型; --一定要有的
572      is[as]
573        变量声明部分
574     begin
575              执行语句
576              return 语句；  --一定要有
577        exception
578     end;
579 */580581createorreplacefunction getsum(m number , n number)
582returnnumber583is584   mn number;
585begin586   mn := m+n;
587return mn;
588end;
589590--调用function591declare592   m number;
593   n number;
594   mn number;
595begin596   m :=&m;
597   n :=&n;
598   mn := getsum(m,n);
599   dbms_output.put_line(mn);
600end;
601602---过程与函数的异同-------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--包头643createorreplace package my_package is644function plus(m number, n number)  returnnumber;
645procedure cheng(x number, y number, xy out number);
646end my_package;
647648--包体649createorreplace package body my_package is650function plus(m number, n number) returnnumber651is  nm number;
652begin653                nm := m+n;
654return nm;
655end;
656procedure cheng(x number, y number, xy out number)
657is658begin659               xy := x+y;
660               dbms_output.put_line(xy);        
661end;
662end my_package;
663--调用包里的方法664declare665     cc number(10);
666begin667-- cc:= my_package.plus(11,22);668--dbms_output.put_line(‘两个数的和是：‘||cc);669     my_package.cheng(11,2,xy=>cc);
670end;
671672/*
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 */681selecttextfrom user_source where name=‘MY_PACKAGE‘;
682683--------游标--------684685/*
686 
687    1:游标是啥？
688 
689     游标是指向上下文区的指针，它为应用提供了一种对具有多行查询结果集的数据，中
690 
691     的每一行分别进行单独处理的方法
692 
693 */694695declare696697   v_dept dept%rowtype;
698699cursor dept_cursor isselect*from dept where deptno>10;  --定义游标700701begin702703open dept_cursor;  --打开游标704705     loop
706707fetch dept_cursor into v_dept;  --提取数据708709exitwhen dept_cursor%notfound;  --判断循环退出条件710711       dbms_output.put_line(‘编号：‘||v_dept.deptno
712713||‘  名称：‘||v_dept.dname||‘  地址：‘||v_dept.loc);
714715end loop;
716717close dept_cursor;  --关闭游标718719end;
720721