《Oracle PLSQL实例精解》读书笔记

《Oracle PLSQL实例精解》读书笔记

• 导读
• 第一章 PL/SQL概念
• 第二章 PL/SQL语言基础
• 第三章 在PL/SQL中的SQL
• 第四章 条件控制：IF语句
• 第五章 条件控制：CASE语句
• 第六章 迭代控制：第一部分
• 第七章 迭代控制：第二部分
• 第十九章 过程
• 第二十章 函数

导读

以下是一部分读书笔记，内容相对零散，希望在用到时会有一点提示作用。

第一章 PL/SQL概念

1. PL/SQL是Oracle对SQL过程语言的扩展。PL/SQL块的PL/SQL语句由PL/SQL语句处理器（PL/SQL引擎）处理，PL/SQL的SQL语句由SQL语句处理器处理。
2. PL/SQL块把代表单个逻辑任务的语句组合在一起。PL/SQL块包含三个部分：声明部分、可执行部分和异常处理部分。其中，可执行部分是块中唯一必需的部分。
3. PL/SQL基础知识：DBMS_OUTPUT.PUT_LINE语句；替代变量功能，使得PL/SQL能够在运行时接受来自用户的输入，变量名一般以and符号（&）或双and符号（&&）字符作为前缀。

第二章 PL/SQL语言基础

1. PL/SQL变量：必须以一个字母开头，PL/SQL不区分大小写。
2. PL/SQL保留字：BEGIN、END和SELECT。
3. 挂靠的数据类型：v_name student.first_name%TYPE
4. 声明和初始化变量：变量可以使用赋值运算符“:=”来赋值

第三章 在PL/SQL中的SQL

1.Select into语法可用于初始化变量（赋值）
2.使用Oracle序列：CURRVAL（返回该序列的当前值）、NEXTVAL（递增序列并返回新值）、

// Oracle序列创建语句:
CREATE SEQUENCE eseqINCREMENT BY 5

3 .PL/SQL中的事务控制：COMMIT、ROLLBACK和SAVEPOINT

第四章 条件控制：IF语句

一个ELSIF语句可以包含任意数量的ELSIF子句。

第五章 条件控制：CASE语句

1.CASE语句：WHEN和ELSE子句都包含一个可执行语句，每个可执行语句都以分号结束；以保留词END CASE结束。
2.CASE表达式：WHEN和ELSE子句都包含不以分号结束的表达式；以保留字END结束；CASE表达式常被当作一个列使用；CASE表达式返回单个值，因此具有单一的数据类型，故有时会用到TO_CHAR函数。
3.NULLIF函数：NULLIF(表达式1，表达式2)，如果表达式1=表达式2，则返回null，如果表达式1不=表达式2，则返回表达式1。
4.COALESCE函数：COALESCE(表达式1，表达式2，…，表达式N)，返回表达式列表中第一个非null表达式的值。

个人感悟：本人在做某行的一个报表导出功能，包括导出明细表和统计报表。该功能涉及了两张表，表关系如下：

一、明细表：在对应实体类的导出字段的get方法上添加@ExcelField

private String instmpdtpcd;	// 产品类型(分期产品类型) SysInbox
@ExcelField(title="产品类型", align=2, sort=0)
public String getInstmpdtpcd() {return instmpdtpcd;
}
public void setInstmpdtpcd(String instmpdtpcd) {this.instmpdtpcd = instmpdtpcd;
}

再将查询到的符合SQL语句的实体类的list，用项目系统自带的报表导出类导出明细表。

/*** 自定义Excel格式导出* @param request* @param response* @throws IOException*/@RequiresPermissions("lch:lch:edit")@RequestMapping("/export")public void export(HttpServletRequest request, HttpServletResponse response )throws IOException{List aList = getList();HSSFWorkbook wb = lchService.export(aList);response.setContentType("application/vnd.ms-excel");String fname = request.getParameter("fname");String fileName =fname+DateUtils.getDate("yyyyMMddHHmmss");response.addHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode(fileName, "UTF-8")+".xls");  OutputStream outputStream = response.getOutputStream();wb.write(outputStream);outputStream.flush();outputStream.close();}private List getList() {ArrayList infoList = new ArrayList();List sysInboxList = sysInboxService.findAllList(new SysInbox());List sysTypeinList = sysTypeinService.findAllList(new SysTypein());for (SysInbox sysInbox : sysInboxList) {ApplyInformation info = new ApplyInformation();info.setInstmpdtpcd(sysInbox.getInstmpdtpcd());// 产品类型(分期产品类型) SysInboxinfo.setInstmacptinsid(sysInbox.getInstmacptinsid());	// 受理机构代码 SysInboxinfo.setT3instchnshrtnm(sysInbox.getT3instchnshrtnm());	//受理机构名称 SysInboxinfo.setMarketingorganizationcode(sysInbox.getCmpninsid());	// 营销机构代码 【新增】info.setMarketingorganizationname(sysInbox.getCmpninsnm());	//营销机构名称【新增】info.setInstmacptempid(sysInbox.getInstmacptempid());	// 受理人员代码 SysInboxinfo.setMarketercode(sysInbox.getCmpnempid());	// 营销人员代码 【新增】info.setCrcrdaplid(sysInbox.getCrcrdaplid());	//申请书编号(信用卡申请编号) SysInboxinfo.setAplypsnnm(sysInbox.getAplypsnnm());	// 申请人姓名 SysInboxinfo.setDatabsndt(sysInbox.getDatabsndt());	//进件日期(数据业务日期) SysInbox	infoList.add(info);}	return infoList;} 

这里有两种生成SQL语句的写法，一种是使用mybatis的外键关联标签（较复杂，不推荐使用）：

SELECTa.id AS "id",a.instmpdtpcd AS "instmpdtpcd",a.blnglvl2insid AS "blnglvl2insid",a.blnglvl2instshrtnm AS "blnglvl2instshrtnm",a.blnglvl3insid AS "blnglvl3insid",a.blnglvl3instshrtnm AS "blnglvl3instshrtnm",a.instmacptinsid AS "instmacptinsid",a.t3instchnshrtnm AS "t3instchnshrtnm",a.cmpninsid AS "cmpninsid",a.cmpninsnm AS "cmpninsnm",a.t2usrnm AS "t2usrnm",a.cmpnempnm AS "cmpnempnm",a.crcrdaplid AS "crcrdaplid",a.aplypsnnm AS "aplypsnnm",a.databsndt AS "databsndt",b.id AS "bid",b.inboxid AS "inboxid",b.fileno AS "fileno",b.bookletnmtotal AS "bookletnmtotal",b.bookletnm AS "bookletnm",b.filematerialtype AS "filematerialtype",b.evidsentdate AS "evidsentdate",b.filesentinstno AS "filesentinstno",b.loanprocessingmode AS "loanprocessingmode",b.origicopysentdate AS "origicopysentdate",b.submissiontime AS "submissiontime",b.typeinremarks AS "typeinremarks"FROMSYS_INBOX aRIGHT JOIN SYS_TYPEIN b ON a.id = b.INBOXID1=1AND DATABSNDT >= to_date(#{paramMap.startTime},'yyyy-MM-dd HH:mi:ss')	AND DATABSNDT <= to_date(#{paramMap.endTime},'yyyy-MM-dd HH:mi:ss')

另一种是直接用左右关联，返回符合条件的Map(实例省略)。

二、统计报表：当操作复杂的统计报表时，SQL语句根本无法满足一次性查出需求，会迫使java逻辑代码处理来自mybatis查询到的多条SQL，非常不便。此时，就要用到PL/SQL，通过PL/SQL块把代表单个逻辑任务的语句组合在一起，使得在mybatis的xml文件仅用一条PL/SQL语句块，就将整个报表的所需字段查出。

Mybatis.xml：

SELECTA .INSTMPDTPCD,A .BLNGLVL3INSID,A .BLNGLVL3INSTSHRTNM,SUM (CASEWHEN FILEMATERIALTYPE = '申请材料' THEN1ELSE0END) AS fileMaterialType1_1,SUM (CASEWHEN FILEMATERIALTYPE = '请款材料' THEN1ELSE0END) AS fileMaterialType2_1,SUM (CASEWHEN FILEMATERIALTYPE = '审批补件' THEN1ELSE0END) AS fileMaterialType3_1,SUM (CASEWHEN FILEMATERIALTYPE = '请补款件' THEN1ELSE0END) AS fileMaterialType4_1,SUM (CASEWHEN FILEMATERIALTYPE = '放款后补档案' THEN1ELSE0END) AS fileMaterialType5_1,SUM(CASEWHEN inboxid IS NULL THENNOSENTFILETYPE1ELSE'0'END )AS noSentFileType1,SUM(CASEWHEN inboxid IS NULL THENNOSENTFILETYPE2ELSE'0'END )AS noSentFileType2,SUM(CASEWHEN inboxid IS NULL THENNOSENTFILETYPE3ELSE'0'END )AS noSentFileType3,SUM(CASEWHEN inboxid IS NULL THENNOSENTFILETYPE4ELSE'0'END )AS noSentFileType4,SUM(CASEWHEN inboxid IS NULL THENNOSENTFILETYPE5ELSE'0'END )AS noSentFileType5,SUM (CASEWHEN (LOANPROCESSINGMODE = '后抵押' OR LOANPROCESSINGMODE = '回执抵押') THEN1ELSE0END) AS fileMaterialType1_2,SUM (CASEWHEN (LOANPROCESSINGMODE = '后抵押' OR LOANPROCESSINGMODE = '回执抵押') AND SUBMISSIONTIME IS NOT NULL THEN1ELSE0END) AS fileMaterialType2_2,SUM (CASEWHEN (LOANPROCESSINGMODE = '后抵押' OR LOANPROCESSINGMODE = '回执抵押') AND SUBMISSIONTIME IS NULL THEN1ELSE0END) AS fileMaterialType3_2,SUM (CASEWHEN LOANPROCESSINGMODE = '替代抵押' THEN1ELSE0END) AS fileMaterialType1_3,SUM (CASEWHEN LOANPROCESSINGMODE = '替代抵押' AND ORIGICOPYSENTDATE IS NOT NULL THEN1ELSE0END) AS fileMaterialType2_3,SUM (CASEWHEN LOANPROCESSINGMODE = '替代抵押' AND ORIGICOPYSENTDATE IS NULL THEN1ELSE0END) AS fileMaterialType3_3FROMSYS_INBOX ALEFT JOIN SYS_TYPEIN B ON A . ID = B.inboxidLEFT JOIN SYS_NOSENT c ON a.crcrdaplid=c.crcrdaplid1=1AND DATABSNDT >= to_date(#{paramMap.startTime},'yyyy-MM-dd HH:mi:ss')	AND DATABSNDT <= to_date(#{paramMap.endTime},'yyyy-MM-dd HH:mi:ss')GROUP BYA .BLNGLVL3INSID,A .BLNGLVL3INSTSHRTNM,A .INSTMPDTPCDORDER BYA .BLNGLVL3INSID,A .BLNGLVL3INSTSHRTNM,A .INSTMPDTPCD

Controller：

    /*** ②分期档案上送情况报表（日报、月报）* @param sysInbox* @param request* @param response* @param redirectAttributes* @throws IOException */@RequiresPermissions("file:sysInbox:view")@RequestMapping(value = "table2")public void table2(SysInbox sysInbox, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) throws IOException {//sysInbox.setAssignedtask("cxgw");//拥有与查询岗位相同的查询功能HashMap timeParamMap;String startTime = request.getParameter("startTime");String endTime = request.getParameter("endTime");timeParamMap = Maps.newHashMap();timeParamMap.put("startTime", startTime);timeParamMap.put("endTime", endTime);sysInbox.setParamMap(timeParamMap);User user = UserUtils.getUser();sysInbox.setUserCompanyId(user.getCompany().getId());String fname = request.getParameter("fname");List> table2MapList = sysInboxDao.getTable2(sysInbox);//主表是sys_inbox、一对多、右连接HSSFWorkbook wb = exportService.table2(table2MapList,fname);response.setContentType("application/vnd.ms-excel");String fileName = fname+DateUtils.getDate("yyyyMMddHHmmss");response.addHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode(fileName, "UTF-8")+".xls");  OutputStream outputStream = response.getOutputStream();wb.write(outputStream);outputStream.flush();outputStream.close();}

Service：

/*** ②分期档案上送情况报表（日报、月报）* @param list* @return*/@SuppressWarnings("deprecation")public HSSFWorkbook table2(List> table2MapList, String fname) {// 声明String数组，并初始化元素（表头名称）//第一行表头字段，合并单元格时字段跨几列就将该字段重复几次String[] excelHeader0 = { "产品类型", "二级分行机构代码", "二级分行名称","档案上送情况", "档案上送情况", "档案上送情况", "后补档案上送情况","后补档案上送情况", "后补档案上送情况", "发票抵扣联原件上送管理部门情况", "发票抵扣联原件上送管理部门情况", "发票抵扣联原件上送管理部门情况" };//  “0,2,0,0”  ===>  “起始行，截止行，起始列，截止列”String[] headnum0 = { "0,1,0,0", "0,1,1,1", "0,1,2,2", "0,0,3,5", "0,0,6,8", "0,0,9,11" };//第二行表头字段，其中的空的双引号是为了补全表格边框String[] excelHeader1 = { " ", "档案材料类型", "已上送量", "未上送量", "应上送量","已上送量", "未上送量", "应上送量", "已上送量", "未上送量" };// 合并单元格String[] headnum1 = { "1,1,3,3", "1,1,4,4", "1,1,5,5", "1,1,6,6", "1,1,7,7", "1,1,8,8", "1,1,9,9", "1,1,10,10", "1,1,11,11" };// 声明一个工作簿HSSFWorkbook wb = new HSSFWorkbook();// 生成一个表格HSSFSheet sheet = wb.createSheet(fname);HSSFCellStyle style = getCellStyle1(wb);HSSFCellStyle style2 = getCellStyle2(wb);// 生成表格的第一行// 第一行表头HSSFRow row = sheet.createRow(0);for (int i = 0; i < excelHeader0.length; i++) {sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度HSSFCell cell = row.createCell(i);cell.setCellValue(excelHeader0[i]);cell.setCellStyle(style);// System.out.println(excelHeader0[i]);if (i >= 0 && i <= 18) {for (int j = 0; j < excelHeader0.length; j++) {// 从第j列开始填充cell = row.createCell(j);// 填充excelHeader1[j]第j个元素cell.setCellValue(excelHeader0[j]);cell.setCellStyle(style);}}// 设置列宽// sheet.setColumnWidth(0, 5500);// sheet.setColumnWidth(1, 6500);// sheet.setColumnWidth(2, 6500);// sheet.setColumnWidth(3, 6000);// sheet.setColumnWidth(4, 6500);// sheet.setColumnWidth(5, 6500);// sheet.setColumnWidth(6, 6500);// sheet.setColumnWidth(7, 6500);// sheet.setColumnWidth(8, 6500);// sheet.setColumnWidth(9, 6500);// sheet.setColumnWidth(10, 6500);// sheet.setColumnWidth(11, 6500);// sheet.setColumnWidth(12, 6500);// sheet.setColumnWidth(13, 6500);// sheet.setColumnWidth(14, 6500);// sheet.setColumnWidth(15, 6500);// sheet.setColumnWidth(16, 6500);// sheet.setColumnWidth(17, 6500);// sheet.setColumnWidth(18, 6500);// sheet.setDefaultRowHeight((short) 360);// 设置行高}// 动态合并单元格for (int i = 0; i < headnum0.length; i++) {sheet.autoSizeColumn(i, true);String[] temp = headnum0[i].split(",");Integer startrow = Integer.parseInt(temp[0]);Integer overrow = Integer.parseInt(temp[1]);Integer startcol = Integer.parseInt(temp[2]);Integer overcol = Integer.parseInt(temp[3]);sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));}// 第二行表头row = sheet.createRow(1);for (int i = 0; i < excelHeader1.length; i++) {sheet.autoSizeColumn(i, true);// 自动调整宽度HSSFCell cell = row.createCell(i + 1);cell.setCellValue(excelHeader1[i]);cell.setCellStyle(style);if (i >= 1 && i <= 18) {for (int j = 0; j < excelHeader1.length; j++) {// 从第j+1列开始填充cell = row.createCell(j + 2);// 填充excelHeader1[j]第j个元素cell.setCellValue(excelHeader1[j]);cell.setCellStyle(style);}}}// 动态合并单元格for (int i = 0; i < headnum1.length; i++) {sheet.autoSizeColumn(i, true);String[] temp = headnum1[i].split(",");Integer startrow = Integer.parseInt(temp[0]);Integer overrow = Integer.parseInt(temp[1]);Integer startcol = Integer.parseInt(temp[2]);Integer overcol = Integer.parseInt(temp[3]);sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));}//======================================================================================// 从第三行起，动态生成单元格Integer startrow = 2;for (int j = 0; j < table2MapList.size(); j++) {Map map = table2MapList.get(j);String[] excelHeader2 = { map.get("INSTMPDTPCD").toString(), map.get("BLNGLVL3INSID").toString(), map.get("BLNGLVL3INSTSHRTNM").toString(),"申请材料", map.get("FILEMATERIALTYPE1_1").toString(), map.get("NOSENTFILETYPE1").toString(), map.get("FILEMATERIALTYPE1_2").toString(), map.get("FILEMATERIALTYPE2_2").toString(), map.get("FILEMATERIALTYPE3_2").toString(), map.get("FILEMATERIALTYPE1_3").toString(), map.get("FILEMATERIALTYPE2_3").toString(), map.get("FILEMATERIALTYPE3_3").toString() };HSSFRow rowx = sheet.createRow(startrow + 5*j);HSSFRow rowx2 = sheet.createRow(startrow + 5*j + 1);HSSFCell cellx21 = rowx2.createCell(3);cellx21.setCellValue("请款材料");cellx21.setCellStyle(style2);HSSFCell cellx22 = rowx2.createCell(4);cellx22.setCellValue(map.get("FILEMATERIALTYPE2_1").toString());cellx22.setCellStyle(style2);HSSFCell cellx23 = rowx2.createCell(5);cellx23.setCellValue(map.get("NOSENTFILETYPE2").toString());cellx23.setCellStyle(style2);HSSFRow rowx3 = sheet.createRow(startrow + 5*j + 2);HSSFCell cellx31 = rowx3.createCell(3);cellx31.setCellValue("审批补件");cellx31.setCellStyle(style2);HSSFCell cellx32 = rowx3.createCell(4);cellx32.setCellValue(map.get("FILEMATERIALTYPE3_1").toString());cellx32.setCellStyle(style2);HSSFCell cellx33 = rowx3.createCell(5);cellx33.setCellValue(map.get("NOSENTFILETYPE3").toString());cellx33.setCellStyle(style2);HSSFRow rowx4 = sheet.createRow(startrow + 5*j + 3);HSSFCell cellx41 = rowx4.createCell(3);cellx41.setCellValue("请补款件");cellx41.setCellStyle(style2);HSSFCell cellx42 = rowx4.createCell(4);cellx42.setCellValue(map.get("FILEMATERIALTYPE4_1").toString());cellx42.setCellStyle(style2);HSSFCell cellx43 = rowx4.createCell(5);cellx43.setCellValue(map.get("NOSENTFILETYPE4").toString());cellx43.setCellStyle(style2);HSSFRow rowx5 = sheet.createRow(startrow + 5*j + 4);HSSFCell cellx51 = rowx5.createCell(3);cellx51.setCellValue("放款后补档案");cellx51.setCellStyle(style2);HSSFCell cellx52 = rowx5.createCell(4);cellx52.setCellValue(map.get("FILEMATERIALTYPE5_1").toString());cellx52.setCellStyle(style2);HSSFCell cellx53 = rowx5.createCell(5);cellx53.setCellValue(map.get("NOSENTFILETYPE5").toString());cellx53.setCellStyle(style2);for (int k = 0; k < excelHeader2.length; k++) {sheet.autoSizeColumn(k, true);// 根据字段长度自动调整列的宽度HSSFCell cell = rowx.createCell(k);cell.setCellValue(excelHeader2[k]);cell.setCellStyle(style2);}hebin(sheet, startrow, j, wb);}return wb;}

Dao：

//获得table2报表的数据Map
public List> getTable2(SysInbox sysInbox);

第六章 迭代控制：第一部分

1. 简单循环：LOOP语句，使用EXIT WHEN添加无限循环的退出条件。
2. WHILE循环：
3. 数字FOR循环：保留字FOR标志着循环结构的开始。变量loop_counter是一个隐含定义的索引变量。没有必要再PL/SQL块的声明部分中定义循环计数器。当使用数字FOR循环时，循环计数器是隐含地定义和递增的。

第七章 迭代控制：第二部分

继续条件：CONTINUE语句和CONTINUE WHEN语句，退出条件会终止循环，而继续条件会终止循环的当前迭代。

第十九章 过程

第二十章 函数

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！