《信创数据库运维管理》课件-项目九：数据库编程开发

信创数据库运维管理存储过程YOURLOGO存储过程智能办事员处理统计成绩生成学号校验数据存储过程三个核心能力认识存储过程创建存储过程调用与进阶应用存储过程存储过程的定义存储在数据库中的预编译SQL语句集合，能像小程序一样接收输入、处理数据、返回结果，可重复调用。写清楚每一步该做什么，任何人照着做都能不出错存储过程存储过程的优势效率高易维护更安全不用每次写一堆重复的查询语句CREATEORREPLACEPROCEDUREGet_Stu_Avg(p_stu_idININT,--输入：学生IDp_avgOUTDECIMAL(5,2)--输出：平均分)ASBEGINSELECTAVG(score)INTOp_avgFROMscoreWHEREstu_id=p_stu_id;END;CREATEORREPLACEPROCEDURE存储过程名(参数1[IN/OUT]类型,参数2[IN/OUT]类型)AS--声明变量（可选）变量名类型;BEGIN--要执行的SQL语句SQL操作;END;存储过程INOUT创建存储过程（基础语法）案例参数没写IN/OUT默认是IN，但输出参数必须标OUT，否则拿不到结果DECLAREv_avgDECIMAL(5,2);--准备个“文件夹”装结果BEGINCALLGet_Stu_Avg(1001,v_avg);--调用存储过程，查1001号学生PRINT'1001号学生平均分：'||v_avg;--打印结果END;存储过程CALL

调用存储过程存储过程返回DECIMAL，变量也得是DECIMAL存储过程存储过程是数据库里的“标准化操作手册”，高效又好维护创建时要注意参数类型和IN/OUT标记，调用时用CALL命令条件判断能让它处理更复杂的业务场景回顾信创数据库运维管理存储函数YOURLOGO存储函数认识存储函数创建存储函数掌握调用方法

SQL逻辑处理存储函数

SQL语句CREATEORREPLACEFUNCTION函数名(参数名类型)RETURN返回值类型AS变量声明；BEGINSQL逻辑；RETURN结果；END;存储函数创建存储函数CREATEORREPLACEFUNCTIONScore_To_GPA(p_scoreININT)RETURNDECIMAL(3,1)ASv_gpaDECIMAL(3,1);BEGINIFp_score<60THENv_gpa:=0;ELSIFp_score<=70THENv_gpa:=1;ELSIFp_score<=80THENv_gpa:=2;ELSIFp_score<=90THENv_gpa:=3;ELSEv_gpa:=4;ENDIF;RETURNv_gpa;END;存储函数RETURN

案例DECLAREv_gpaDECIMAL(3,1);BEGINv_gpa:=Score_To_GPA(85);PRINT'85分对应的绩点是：'||v_gpa;END;PL/SQLDECLAREv_gpaDECIMAL(3,1);BEGINv_gpa:=Score_To_GPA(85);PRINT'85分对应的绩点是：'||v_gpa;END;存储函数SELECTstu_id,score,Score_To_GPA(score)ASgpaFROMscoreWHEREclass_id=101;DECIMALINT用时参数数量和类型要和定义一致PL/SQL案例CREATEORREPLACEFUNCTIONGet_Total_Credits(p_stu_idININT)RETURNINTASv_totalINT;BEGINSELECTCOUNT(*)*2INTOv_totalFROMscoreWHEREstu_id=p_stu_idANDscore>=60;RETURNv_total;END;存储函数SELECTGet_Total_Credits(2023001)AStotal_credits;

空值（NULL）IFv_totalISNULLTHENv_total:=0;ENDIF;

售罄案例信创数据库运维管理触发器管理YOURLOGO触发器管理触发器它会自动执行预设的操作自动维护数据完整性特殊程序触发器管理触发时机BEFOREAFTER操作类型INSERTUPDATEDELETE触发器管理CREATEORREPLACETRIGGERcheck_score_after_insertAFTERINSERTONscoreFOREACHROWBEGINIF:NEW.score<0OR:NEW.score>100THENRAISE_APPLICATION_ERROR(-20001,'成绩必须在0-100分之间');ENDIF;END;触发器管理

AFTERINSERT触发器:NEW.score如果不合规，就会抛出错误提示，阻止无效数据进入系统触发器管理选课表（course_selection）课程表（course）CREATEORREPLACETRIGGERupdate_course_num_before_updateBEFOREUPDATEONcourse_selectionFOREACHROWBEGINIF:OLD.course_id!=:NEW.course_idTHENUPDATEcourseSETstudent_num=student_num-1WHEREcourse_id=:OLD.course_id;UPDATEcourseSETstudent_num=student_num+1WHEREcourse_id=:NEW.course_id;ENDIF;END;案例触发器管理SELECT*FROMuser_triggersWHEREtable_name='SCORE';DROPTRIGGERcheck_score_after_insert;查看触发器删除触发器查看修改删除触发器管理触发时机触发事件合理的触发体逻辑1.COMMIT或ROLLBACK易错点2.:NEW和:OLD3.触发器逻辑太复杂信创数据库运维管理游标管理YOURLOGO游标“指针”存储过程或函数里游标管理逐条访问c游标管理SELECT*FROMscoreWHEREclass_id='202301';游标隐式游标显式游标数据库自动创建手动定义、打开、使用和关闭游标管理步骤打开游标定义游标OPENc_scholarship;DECLARECURSORc_scholarshipISSELECT*FROMrewardWHEREclass_id='202301'ANDtype='奖学金';提取数据FETCHc_scholarshipINTOv_stu_id,v_name;处理数据可以用循环语句逐条判断，统计符合条件的人数，类似在点名册上做标记关闭游标CLOSEc_scholarship;游标管理完整的示例代码DECLAREv_countINT:=0;v_stu_idVARCHAR(20);v_nameVARCHAR(50);CURSORc_scholarshipISSELECTstu_id,nameFROMrewardWHEREclass_id='202301'ANDtype='奖学金';BEGINOPENc_scholarship;LOOPFETCHc_scholarshipINTOv_stu_id,v_name;EXITWHENc_scholarship%NOTFOUND;--没有更多记录时退出循环游标管理完整的示例代码v_count:=v_count+1;DBMS_OUTPUT.PUT_LINE('获奖学生：'||v_name);ENDLOOP;CLOSEc_scholarship;DBMS_OUTPUT.PUT_LINE('2023级1班奖学金获得者共'||v_count||'人');END;c_scholarship%NOTFOUND是游标的属性游标管理重点和难点“定义-打开-提取-关闭”游标与循环结合的逻辑控制退出循环的条件设置打开游标后忘记关闭未打开时就提取数据混淆游标属性比如用%FOUND还是%NOTFOUND判断批量提取FETCH...BULKCOLLECTINTO易错点总结c清晰的处理逻辑数据处理的条理性保证准确又提高效率有序处理的思维信创数据库运维管理if语句YOURLOGOif语句if语句(一种条件判断语句)是否成立存储过程、函数、触发器等程序if语句if-then结构IF条件THEN操作语句；ENDIF;DECLAREv_leave_daysINT:=5;--假设学生请假5天BEGINIFv_leave_days>3THENDBMS_OUTPUT.PUT_LINE('需要系主任审批');ENDIF;END;例子if语句DECLAREv_scoreINT:=75;--学生成绩75分v_resultVARCHAR(10);BEGINIFv_score>=60THENv_result:='及格';ELSEv_result:='不及格';ENDIF;DBMS_OUTPUT.PUT_LINE('该学生成绩评定为：'||v_result);END;if-then-else结构与例子IF条件THEN操作语句1;ELSE操作语句2;ENDIF;if语句if-then-elsif-else结构IF条件1THEN操作语句1;ELSIF条件2THEN操作语句2;...ELSE操作语句n;ENDIF;if语句DECLAREv_scoreINT:=85;--学生成绩85分v_scholarshipVARCHAR(20);BEGINIFv_score>=90THENv_scholarship:='一等奖学金';ELSIFv_score>=80THENv_scholarship:='二等奖学金';ELSIFv_score>=70THENv_scholarship:='三等奖学金';ELSEv_scholarship:='未获得奖学金';ENDIF;DBMS_OUTPUT.PUT_LINE('该学生获得：'||v_scholarship);END;例子if语句重点条件的正确书写难点不同结构的适用场景多个条件之间的逻辑关系用比较运算符（如>、<、=、>=、<=等）连接的表达式简单的条件判断用if-then，二选一的情况用if-then-else，多个条件的情况用if-then-elsif-else在使用if-then-elsif-else结构时，要注意条件的顺序易错点忘记写ENDIF条件表达式书写错误在if语句中嵌套过多的逻辑总结严谨的态度逻辑思维的严谨保证数据处理的准确性信创数据库运维管理循环语句YOURLOGO循环语句重复执行一段代码的语句循环语句LOOP

循环WHILE循环FOR循环c循环语句LOOP循环LOOP操作语句；EXITWHEN退出条件；--满足条件时退出循环ENDLOOP;WHILE循环WHILE条件LOOP操作语句；ENDLOOP;FOR循环FOR变量IN起始值..结束值LOOP操作语句；ENDLOOP；循环语句DECLAREv_stu_idINT:=2023001;--起始学号BEGINLOOPINSERTINTOstudent(id,password)VALUES(v_stu_id,SUBSTR(v_stu_id,4));v_stu_id:=v_stu_id+1;--学号递增EXITWHENv_stu_id>2023010;--当学号超过2023010时退出ENDLOOP;DBMS_OUTPUT.PUT_LINE('10名学生初始密码生成完毕');END;LOOP循环

例子循环语句DECLAREv_countINT:=0;--计数器v_scoreINT;v_cursorCURSORISSELECTscoreFROMexam;--成绩游标BEGINOPENv_cursor;FETCHv_cursorINTOv_score;WHILEv_cursor%FOUNDLOOP--只要有数据就继续循环IFv_score<60THENv_count:=v_count+1;ENDIF;FETCHv_cursorINTOv_score;ENDLOOP;CLOSEv_cursor;DBMS_OUTPUT.PUT_LINE('不及格成绩共'||v_count||'条');END;WHILE循环

例子循环语句DECLAREv_course_idINT:=1001;--课程IDBEGINFORv_dayIN1..5LOOP--循环1到5（代表周一到周五）INSERTINTOcourse_schedule(course_id,week_day)VALUES(v_course_id,v_day);ENDLOOP;DBMS_OUTPUT.PUT_LINE('课程一周排课完成');END;WHILE循环

例子循环语句重点明确循环的目的选对循环类型必须设置退出条件难点循环与游标结合使用时的逻辑控制在WHILE循环中，要注意游标提取数据的时机易错点忘记设置退出条件循环变量递增/递减错误循环中执行大量耗时操作总结优化工作流程“自动化思维”追求高效既能发挥它的自动化优势又能时刻注意逻辑的严谨性信创数据库运维管理case语句YOURLOGOcase语句多条件分支判断语句case语句简单case语句和搜索case语句ccase语句简单case语句和例子CASE表达式WHEN值1THEN结果1WHEN值2THEN结果2...ELSE默认结果ENDSELECTstu_id,score_code,CASEscore_codeWHEN'A'THEN'优秀'WHEN'B'THEN'良好'WHEN'C'THEN'及格'WHEN'D'THEN'不及格'ELSE'未评级'ENDASscore_levelFROMstudent_score;case语句搜索case语句和例子CASEWHEN条件1THEN结果1WHEN条件2THEN结果2...ELSE默认结果ENDSELECTstu_id,score,CASEWHENscore>=90THEN'90分及以上'WHENscore>=80THEN'80-89分'WHENscore>=60THEN'60-79分'ELSE'60分以下'ENDASscore_rangeFROMexam_result;case语句UPDATEcourseSETstatus=CASEWHENstudent_num>=50THEN'已满员'WHENstudent_num>=30THEN'剩余少量名额'ELSE'可正常选课'END;例子（更新操作）case语句重点和难点注意条件的顺序合理使用ELSE子句多个条件之间的逻辑关系条件是按顺序判断的避免出现NULL值准确安排顺序易错点忘记写END表达式和值的类型不匹配条件顺序颠倒总结逻辑判断的条理性逻辑的准确性分类处理思维多条件判断功能信创数据库运维管理顺序结构语句YOURLOGO顺序结构语句程序中的语句按照书写顺序依次执行顺序结构语句顺序结构是最基本的组成部分顺序结构语句--第一步：插入学生基本信息INSERTINTOstudent(stu_id,name,class_id)VALUES('2024001','张三','计算机2401');--第二步：为该学生设置初始密码（学号后6位）UPDATEstudentSETpassword=SUBSTR('2024001',3)WHEREstu_id='2024001';SQL语句序列DECLAREv_sumINT:=0;--总分v_countINT:=0;--人数v_avgNUMBER(5,2);--平均分v_scoreINT;--定义游标查询计算机2401班的成绩CURSORc_scoresISSELECTscoreFROMexamWHEREclass_id='计算机2401';BEGIN--第一步：打开游标OPENc_scores;例子(查询和计算）顺序结构语句--第二步：循环提取成绩并累加LOOPFETCHc_scoresINTOv_score;EXITWHENc_scores%NOTFOUND;v_sum:=v_sum+v_score;v_count:=v_count+1;ENDLOOP;--第三步：关闭游标CLOSEc_scores;--第四步：计算平均分（避免除数为0）IFv_count>0THENv_avg:=v_sum/v_count;ELSEv_avg:=0;ENDIF;--第五步：输出结果DBMS_OUTPUT.PUT_LINE('计算机2401班平均分：'||v_avg);END;例子(查询和计算）顺序结构语句注意涉及依赖关系的步骤步骤打开游标计算平均分累加成绩关闭游标输出结果难点当顺序结构包含多个步骤时每一步的正确性会影响最终结果易错点忽略语句间的依赖关系在批量操作中漏写步骤复杂程序中把语句顺序写反总结按顺序、讲逻辑的思维“循序渐进”“先做什么、再做什么”信创数据库运维管理异常处理YOURLOGO异常处理专门处理“意外情况”的机制存储过程、函数等程序异常处理c“违反主键唯一性”（DUP_VAL_ON_INDEX）、“数据不存在”（NO_DATA_FOUND）异常处理异常预定义异常自定义异常数据库已经“认识”的错误根据业务需求自己定义的错误“成绩超过100分”，就像某些特殊病症，需要我们专门制定治疗方案异常处理DECLAREv_stu_idVARCHAR(20):='2024001';BEGININSERTINTOstudent(stu_id,name)VALUES(v_stu_id,'李四');DBMS_OUTPUT.PUT_LINE('学生信息插入成功');EXCEPTIONWHENDUP_VAL_ON_INDEXTHENDBMS_OUTPUT.PUT_LINE('错误：学号'||v_stu_id||'已存在，请检查');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生未知错误：'||SQLERRM);END;预定义异常DUP_VAL_ON_INDEX异常EXCEPTION块异常处理DECLAREv_scoreINT:=110;--定义自定义异常“成绩超出范围”score_out_of_rangeEXCEPTION;BEGINIFv_score<0ORv_score>100THEN--触发异常RAISEscore_out_of_range;ENDIF;INSERTINTOexam(stu_id,score)VALUES('2024001',v_score);EXCEPTIONWHENscore_out_of_rangeTHENDBMS_OUTPUT.PUT_LINE('错误：成绩'||v_score||'无效，必须在0-100之间');END;自定义异常异常处理DECLAREv_stu_idVARCHAR(20):='2024001';v_course_idVARCHAR(10):='C001';BEGIN--第一步：删除选课记录DELETEFROMcourse_selectionWHEREstu_id=v_stu_idANDcourse_id=v_course_id;--第二步：更新课程人数UPDATEcourseSETstudent_num=student_num-1WHEREcourse_id=v_course_id;COMMIT;--两步都成功才提交DBMS_OUTPUT.PUT_LINE('退选成功');EXCEPTIONWHENOTHERSTHENROLLBACK;--出错就回滚，恢复到操作前状态DBMS_OUTPUT.PUT_LINE('退选失败：'||SQLERRM);END;异常处理和事务结合保证数据一致性OTHERS异常处理易错点忘记处理异常自定义异常时忘了用RAISE触发在异常处理中使用COMMIT或ROLLBACK时逻辑错误难点异常处理的粒度把握总结提前防范、妥善应对的思维“未雨绸缪”“如果出了这个问题该怎么办”信创数据库运维管理事务的使用YOURLOGO达梦数据库中，事务是保障数据一致性的核心机制，如同交通信号灯，让复杂操作有序进行、避免混乱。事务是一组不可分割的数据库操作，要么全成功，要么全失败回滚（如网购套装，不可单买）；信创数据库运维管理事务的ACID特性是一套"质量标准"：原子性：操作要么全成，要么全败，无中间状态一致性：事务前后数据符合业务规则隔离性：多事务同时执行互不干扰持久性：事务完成后结果永久保存，断电不丢失信创数据库运维管理教务系统选课场景中，学生选课需同时完成两项操作：在选课表插入记录，将课程剩余名额减1，这可用事务处理。事务处理的SQL语句：BEGINTRANSACT