四阶段录屏-plsql基础使用

使用PL/SQLPL/SQL简介PL/SQL是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言PL/SQL是对SQL的扩展支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性PL/SQL的优点2-1支持SQL，在PL/SQL中可以使用：数据操纵命令(DML)事务控制命令(TCL)游标控制SQL函数和SQL运算符支持面向对象编程(OOP)可移植性，可运行在任何操作系统和平台上的Oralce数据库更佳的性能，PL/SQL经过编译执行用户将整个语句块发送给OracleOracleProcedureBeginProcedureCallSQLCommand…EndPL/SQL的优点2-2与SQL紧密集成，简化数据处理。支持所有SQL数据类型支持NULL值支持%TYPE和%ROWTYPE属性类型在PL/SQL中可以直接使用DML和TCL语句。可以间接以特殊的方式执行DDL和DCL语句安全性，可以通过存储过程限制用户对数据的访问PL/SQL的体系结构PL/SQL引擎驻留在Oracle服务器中该引擎接受PL/SQL块并对其进行编译执行将PL/SQL块发送给Oracle服务器用户执行过程语句引擎将SQL语句发送给SQL语句执行器Oracle服务器PL/SQL引擎SQL

语句

执行器过程语句

执行器执行SQL语句将结果发送给用户PL/SQL块简介将逻辑上相关的声明和语句组合在一起PL/SQL分为三个部分，声明部分、可执行部分和异常处理部分PL/SQL块是构成PL/SQL程序的基本单元[DECLAREdeclarations]BEGINexecutablestatements[EXCEPTIONhandlers]END;7PLSQL示例DECLAREqty_on_handNUMBER(5);BEGINSELECTquantityINTOqty_on_handFROMProductsWHEREproduct='芭比娃娃'FORUPDATEOFquantity;IFqty_on_hand>0THENUPDATEProductsSETquantity=quantity+1WHEREproduct='芭比娃娃';INSERTINTOpurchase_recordVALUES('已购买芭比娃娃',SYSDATE);ENDMIT;EXCEPTION/*异常处理语句*/WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('出错:'||SQLERRM);END;变量和常量1-2PL/SQL块中可以使用变量和常量在声明部分声明，使用前必须先声明声明时必须指定数据类型，每行声明一个标识符在可执行部分的SQL语句和过程语句中使用声明变量和常量的语法：identifier[CONSTANT]datatype[NOTNULL][:=|DEFAULTexpr];给变量赋值有两种方法：使用赋值语句:=使用SELECTINTO语句变量和常量2-2DECLAREicodeVARCHAR2(6);p_catgVARCHAR2(20);p_rateNUMBER;c_rateCONSTANTNUMBER:=0.10;BEGIN...icode:='i205';SELECTp_category,itemrate*c_rateINTOp_catg,p_rateFROMitemfileWHEREitemcode=icode;...END;10PLSQL数据类型标量类型数字、字符、布尔型、日期时间LOB类型:存储非结构化数据块BLOB、CLOB、BFile属性类型%TYPE：提供某个变量或数据库表列的数据类型%ROWTYPE提供表示表中一行的记录类型11数字数据类型BINARY_INTEGER：存储有符号整数，所需存储空间少于NUMBER类型值NUMBER：存储整数、实数和浮点数PLS_INTEGER：存储有符号整数，可使算术计算快速而有效字符数据类型字符数据类型包括：CHARVARCHAR2LONGRAWLONGRAWPL/SQL的数据类型与SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONGRAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767日期时间和布尔数据类型日期时间类型存储日期和时间数据常用的两种日期时间类型DATETIMESTAMP布尔数据类型此类别只有一种类型，即BOOLEAN类型用于存储逻辑值(TRUE、FALSE和NULL)不能向数据库中插入BOOLEAN数据不能将列值保存到BOOLEAN变量中只能对BOOLEAN变量执行逻辑操作LOB数据类型2-1用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB数据类型可存储最大4GB的数据。LOB类型包括：BLOB将大型二进制对象存储在数据库中CLOB将大型字符数据存储在数据库中NCLOB存储大型UNICODE字符数据BFILE将大型二进制对象存储在操作系统文件中LOB数据类型2-2LOB类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置DBMS_LOB程序包用于操纵LOB数据SETSERVEROUTPUTONDECLAREclob_varCLOB;amountINTEGER;offsetINTEGER;output_varVARCHAR2(100);BEGINSELECTchapter_textINTOclob_varFROMmy_book_textWHEREchapter_id=5;amount:=24;--要读取的字符数

offset:=1;--起始位置

DBMS_LOB.READ(clob_var,amount,offset,output_var);DBMS_OUTPUT.PUT_LINE(output_var);END;/从表中选择CLOB定位符到clob_var变量中从CLOB数据中读取24个字符存储到output_var变量中显示读到的信息属性类型用于引用数据库列的数据类型，以及表示表中一行的记录类型属性类型有两种：%TYPE-引用变量和数据库列的数据类型%ROWTYPE-提供表示表中一行的记录类型使用属性类型的优点：不需要知道被引用的表列的具体类型如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变icodeitemfile.itemcode%TYPE;emp_recscott.emp%ROWTYPE;表达式关系运算符说明=比较两个变量是否相等，如果值相当，则返回True<>,!=比较两个变量，如果不相等，则返回True<比较两个变量，检查值1是否小于值2>比较两个变量，检查值1是否大于值2<=比较两个变量，检查变量1是否小于等于变量2>=比较两个变量，检查变量1是否大于等于变量2控制结构PL/SQL支持的流程控制结构：条件控制IF语句CASE语句循环控制LOOP循环WHILE循环FOR循环顺序控制GOTO语句NULL语句条件控制2-1IF语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIFDECLAREicodeVARCHAR2(4);irateNUMBER;BEGINicode:='i203';SELECTitemrateINTOirateFROMitemfileWHEREitemcode=icode;IFirate>200THENUPDATEitemfileSETitemrate=itemrate-200WHEREitemcode=icode;ELSEUPDATEitemfileSETitemrate=itemrate-50WHEREitemcode=icode;ENDIF;DBMS_OUTPUT.PUT_LINE('itemrate='||irate);END;20ifvar_snameisnotnullandvar_sname!=''thenvar_sql:=var_sql||'andsnamelike''%'||var_sname||'%''';endif;条件控制2-2CASE语句用于根据单个变量或表达式与多个值进行比较执行CASE语句前，先计算选择器的值BEGINCASE‘&grade’WHEN’A’THENDBMS_OUTPUT.PUT_LINE(’优异’);WHEN’B’THENDBMS_OUTPUT.PUT_LINE(优秀’);WHEN’C’THENDBMS_OUTPUT.PUT_LINE(’良好’);WHEN’D’THENDBMS_OUTPUT.PUT_LINE(’一般’);WHEN’F’THENDBMS_OUTPUT.PUT_LINE(’较差’);ELSEDBMS_OUTPUT.PUT_LINE(’没有此成绩’);ENDCASE;END;循环控制循环控制用于重复执行一系列语句循环控制语句包括：LOOP、EXIT和EXITWHEN循环控制的三种类型：LOOP-无条件循环WHILE-根据条件循环FOR-循环固定的次数FORcounterIN[REVERSE]value1..value2LOOPsequence_of_statementsENDLOOP;顺序控制顺序控制用于按顺序执行语句顺序控制语句包括：GOTO语句-无条件地转到标签指定的语句NULL语句-什么也不做的空语句

24顺序控制实例DECLAREqtyhanditemfile.qty_hand%type;relevelitemfile.re_level%type;BEGINSELECTqty_hand,re_levelINTOqtyhand,relevelFROMitemfileWHEREitemcode='i201';IFqtyhand<relevelTHENGOTOupdation;ELSEGOTOquit;ENDIF;<<updation>>UPDATEitemfileSETqty_hand=qty_hand+re_levelWHEREitemcode='i201';<<quit>>NULL;END;错误处理2-1在运行程序时出现的错误叫做异常发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分异常有两种类型：预定义异常-当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发用户定义异常-用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发26预定义异常Access_info_null（ora-06530）：当访问没有初始化的对象时触发。Case_not_found(ora-06592)：在case过程中when后没有包含必要的条件分支并且没有else子句，则会触发本异常。Collection_is_null(06531)：访问未初始化的集合元素（嵌套表或者varray）。Cursor_already_open(ora-06511)：重新打开已经打开的游标。Dup_val_on_index(ora-00001)：当中唯一索引所对应的列上键入重复值时。Invalid_cursor(ora-01001)：试图在不合法的游标上执行操作时，譬如没打开游标就提取内容Invalid_number(ora-01722)：当试图将非法的字符串转换为数字类型时。No_data_found(ora-01403)：执行selectinto未返回行，或者引用了索引表未初始化的元素时。Too_many_rows(ora-01422)：执行selectinto返回超过一行数据时。Zero_divide(ora-01476)：0作为被除数时。Subscript_beyond_count(ora-06533)：使用嵌套表或者varray集合时，如果引用下标超过last。27预定义异常Subscript_outside_limit(ora-06532)：使用嵌套表或varray集合时，如果引用下标小于first。Value_error(ora-06502)：在执行赋值操作时，如果变量长度不足以容纳实际数据。Login_denied(ora-01017)：连接数据库时提供了不正确的用户名或口令。Not_logged_on(ora-01012)：在程序没有连接到oracle数据库时执行plsql代码则会触发。Program_error(ora-06501)：plsql内部问题。Rowtype_mismatch(ora-06504)：执行赋值操作时，如果宿主游标变量和PLSQL游标变量返回类型不兼容时。Self_is_null(ora-30625)：使用对象类型时，如果在null实例上调用成员方法。Storage_error(ora-06500)：超出内存空间或者内存被损坏。Sys_invalid_rowid（ora-01410）：无效字符串企图转换为rowid类型时。Timeout_on_resource(ora-00051)：等待资源时出现超时错误。Others：其它的异常信息。declarevar_usert_user%rowtype;var_inumber;begin--Select*intovar_userfromt_userwhereuserid=1;--Select*intovar_userfromt_user;var_i:=11/0;exception--whenNo_data_foundthen--dbms_output.put_line(SQLcode);--dbms_output.put_line(sqlerrm);whenToo_many_rowsthendbms_output.put_line(SQLcode);dbms_output.put_line(sqlerrm);dbms_output.put_line('返回的行太多');whenothersthendbms_output.put_line(SQLcode);dbms_output.put_line(sqlerrm);dbms_output.put_line(sys.sysevent);dbms_output.put_line(sys.login_user);dbms_output.put_line(sysdate);end;RAISE_APPLICATION_ERROR过程用于创建用户定义的错误信息可以在可执行部分和异常处理部分使用错误编号必须介于–20000和–20999之间错误消息的长度可长达2048个字节引发应用程序错误的语法：

RAISE_APPLICATION_ERROR(error_number,error_message);引发应用程序错误30declaree_oneexception;var_inumber;var_amountt_user.amount%type;var_messagevarchar2(100);beginvar_i:=1000;selectamountintovar_amountfromt_userwhereuserid=4;ifvar_i>var_amoun