搜索
Oracle的存储过程和自定义函数存储过程、存储函数数据库对象：表、视图、索引、序列、同义词存储过程：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。创建和使用存储过程用createprocedure命令建立存储过程和存储函数。语法：create[orreplace]procedure过程名（参数列表）ASPLSQL子程序体;第一个存储过程：打印Helloworldcreateorreplaceproceduresayhelloworldas--说明部分begindbms_out.put_line(“HelloWorld”);end;/两种调用存储过程：1.execsayhelloworld();2.beginsayhelloworld();sayhelloworld();end;/3.setserveroutputon:”打开输出开关”4.sqlplusscott/tiger@localhost:1521/orcl：“连接数据库”5.hostcls带参数的存储过程举例：为指定的员工，涨100块钱的工资；并且打印涨前和涨后的薪水descdbms_output程序包（desc查看命令，desc表示按“表排序字段名”倒序显示，不加desc,表示正序显示）参数是输入还是输出参数如何调用:beginraisesalay(7839);raisesalay(7566);commit;end;/如何调试存储过程不推介远程调试本地调试（虚拟机调试）调试必须有debugconnectsession和debuganyprocedure用户权限grantDEBUGCONNECTSESSION,DEBUGANYPROCEDUREtoscoot;存储函数函数（Function）为一命名的存储程序，可带参数，并返回以计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。存储过程和存储函数的相同点：完成特定功能的程序存储过程和存储函数的区别：是否用return语句返回值（存储函数有）Eg:查询某个员工的年收入--存储函数：查询某个员工的年收入createororreplacefunctionqueryempincom(enoinnumber)returnnumberas--定义变量保存员工的薪水和奖金psalemp.sal%type;pcommemp.comm%type;begin--得到该员工的月薪和奖金selectpsal,commintopsal,pcommfromempwhereempno=eno;--直接返回年收入returnpsal*12+pcomm;end;/远程调用可以，远程存储不可以调用方法：DECLAREENONUMBER;v_ReturnNUMBER;BEGINENO:=7499;v_Return:=QUERYEMPINCOME(ENO=ENO);DBMS_OUTPUT.PUT_LINE(‘v_Return=’||v_Return);v_Return:=v_Return;--rollback;END;若果一个表达式中含有一个空值，那么这个表达式就为空。nvl(pcomm,0);遇空值将他变为0In和Out参数一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。存储过程和存储函数都可以有out参数存储过程和存储函数都可以有多个out参数存储过程可以通过out参数来实现返回值什么时候用存储过程/存储函数？原则：—如果只有一个返回值，用存储函数；否则，就用存储过程。--out参数：查询某个员工姓名月薪和职位createorreplaceprocedurequeryempinform(enoinnumber,penameoutvarchar2,psaloutnumber,pjoboutvarchar2)asbegin--得到该员工的姓名月薪和职位selectename,sal,empjobintopename,psal,pjobfromempwhereempno=eno;end;/概述在应用程序中访问存储过程和存储函数访问存储过程访问存储函数关于JUnit的使用请参看课程《JUnit——Java单元测试必备工具》也可以在类中使用main方法进行测试在Out参数中使用光标申明包结构包头：只负责申明CreateorreplacepackagemypackageasTypeempcursorisrefcursor;ProcedurequeryEmpList(dnoinnumber,empListoutempcursor);ENDMYPAGE;包体:需要实现包头中声明的所有方法CreateorreplacepackagebodymypackageasProcedurequeryEmpList(dnoinnumber,empListoutempcursor)ASBeginOpenempListforselect*fromempwheredeptno=dno;ENDqueryEmpList;ENDmypackage;案例：查询某部门中所有员工的所有信息注意：要带上包名高级查询分组查询：求每个部门的平均工资分组函数的概念分组函数作用于一组数据，并对一组数据返回一个值。一组员工表中的工资最大值分组函数的使用常用的分组函数AVG：平均值SUM：求和MIN：最小值COUNT：求个数WM_CONCAT：（CONCAT）字符的拼加前面加WM叫行转列求出员工的平均工资和工资的总额Selectavg(sal),sum(sal)fromemp;求出员工工资的最大值和最小值Selectmax(sal),min(sal)fromemp;求出员工的总人数Selectcount（*）fromemp;这里的星号代表的是所有的列也可以是具体的列Selectcount（empno）fromemp;Count函数当中也可以使用DISTINCT（distinct）关键字求出部门数Distinct用于去掉重复的记录示例：Selectdeptno部门号,wm_concat(ename)部门中员工的姓名fromempgroupbydeptno;Setlinesize200设置列的宽度200Col部门中员工的姓名fora60列与列之间的宽度分组函数与空值举例1：统计员工的平均工资Selectsum(sal)/count(*)一，sum（sal）/count(sal)二，avg(sal)三Fromemp;举例2：统计员工的平均奖金Selectsum（com）/count(*)一，sum（comm）/count(comm)二，avg(comm)三Fromemp;奖金中含有空值，所以第一种方法算出来的结果与其他两种不一样Selectcount（*），与count（comm）算出的结果不一样，一将空值都算进去了，二没有。分组函数会自动忽略空值注意：NVL函数使分组函数无法忽略空值Selectcount（nvl(comm.,0)）fromemp;Nvl函数的意思是当括号里第一个值为空时，返回第二个值。使用Groupby子句数据分组求出员工表（EMPLOYEES）中各部门的平均工资Groupby子句语法GROUPBYgroup_by_expressionGROUPBYcolumn可以使用GROUPBY子句将表中的数据分成若干组示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资Selectdeptno,avg(sal)FromempGroupbydeptno;抽象：Selecta,组函数（X）FromtableGroupbya;在select列表中所有未包含在组函数中的列都应该包含在GROUPBY子句中包含在GROUPBY子句中的列不必包含在select列表中SelectAVG（sal）FromempGroupbydeptno;使用多个列分组示例：按部门、不同的职位，统计员工的工资总额Selectdeptno,job,sum(sal)FromempGroupbydepton,jobOrderbydeptno/非法使用组函数所用包含select列表中，而未包含于组函数中的列都必须包含于GROUPBY子句中。错的Having子句的使用以及和where的区别使用Having子句过滤分组结果集求平均工资大于2000的部门,要求显示：部门号，平均工资Having子句的语法Ed修改上一条语句Selectdeptno，AVG（sal）FromempGroupbydeptnoHavingavg(sal)2000Where与having的区别不能在WHERE子句中使用组函数（注意）。可以在HAVING子句中使用组函数。示例：错的示例：查询10号部门的平均工资注意：从SQL优化的角度上看，尽量使用whereSelectdeptno,avg(sal)FromempGroupbydeptnoHavingdeptno=10;Selectdeptno,avg(sal)FromempWheredeptno=10GroupbydeptnoHaving先分组，后过滤，起过滤作用Where先过滤，后分组，员工表中有一亿个员工，十号部门的员工只有100人，如果使用having先分组在过滤，分组的记录数就是一亿条，过滤完了以后再来选出十号部门。而where先过滤在分组，先从这一亿条记录当中选出10号部门的100个员工，然后再来分组，这个时候分组的时候就只有一百条记录。Wher使得分组记录数大大降低，从而提高效率、注意：where子句当中不能使用分组函数在分组查询中使用orderby子句示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资，并且按照工资升序排列可以按照：列、别名、表达式、序号进行排序(默认升序排列)Selectdeptno,avg(sal)FromempGroupbydeptnoOrderbyavg(sal);如果给AVG（sal）起个别名，平均工资，那么orderby后面直接写别名就好了（平均工资）Orderby后写2，表示select语句中的第二列。Orderby必须是select—list表达式的数目Desc降序排列在orderby2desc--a命令append使用a命令时a后面的空格一定是两个或者两个以上，如果是一个的话追加出来的语句是错误的分组函数的嵌套求部门平均工资的最大值1、通过AVG函数求出每个部门的平均工资2、嵌套MAX函数求出部门平均工资的最大值Selectmax（AVG（sal））FromempGroupbydeptno;Groupby语句的增强Selectdeptno,job,sum(sal)fromempgroupbydeptno,job+Selectdeptno,sum(sal)fromempgroupbydeptno+Selectsum(sal)fromempSelectdeptno,job,sum(sal)fromempgroupbyrollup(deptno,job);Groupby语句的增强语法：Groupbyrollup(a,b)等价于：Groupbya,b+Groupbya+GroupbynullBreakondeptnoskip2:表示相同的部门号只显示一次，不同的部门号要条过两行Setpagesize30:设置每页30大小SQL*Plus的报表功能报表包括：标题、页码、别名等。ttitlecol15‘我的报表’col35sql.pno:col15表示空15个列，sql.pno表示报表的页码coldeptnoheading部门号coljobheading职位colsum(sal)heading工资总额breakondeptnoskip1getd:\temp\report.sql:读取d盘报表@d:\temp\report.sqlSelectdeptno,job,sum(sal)fr