Oracle数据库知识-PLSQL 培训胶片V1.0课件

1、Oracle基础知识PLSQLCC&CRM新员工报表培训PL/SQL简介PL/SQL概述PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构。其优点大致如下： PL/SQL是一种高性能的基于事务处理的语言，支持所有数据处理命令，支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型。 PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，具有很好的可重用性。 可以将功能划分为不同的逻辑代码块编程的模块化为应用程序开发环境提供了更大的灵活性。 对于SQL，ORAC

2、LE必须在同一时间处理每一条SQL语句，每一个独立的调用都必须被Oracle服务器处理，占用大量的服务器时间，并导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。PL/SQL块结构PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：DECLARE变量说明部分；包括定义的变量和其它的对象（如常量和指针），游标 也在这里定义；可选。BEGIN执行语句部分；PL/SQL块中的指令部分，所有的可执行语句都放在这 里，其他的PL/SQL块也可以放在这一部分；必选。EXCEPTION异常处理部

3、分；包含如何处理指定的错误并按用户的定义进行处理；可 选。END;PL/SQL块类型 PL/SQL块主要有两种类型，即命名块和匿名块： 匿名块：以DECLARE或BEGIN开始，每次使用时都要进行编译，除此之外，该类块不在数据库中存储并且不能直接从其他的PL/SQL块中调用。 命名块没有匿名块的限制，独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块，如过程，函数，包和触发器都属于命名块CREATE OR REPLACE PROCEDURE name (parameter,parameter,.) IS local declarationsBEGIN

4、execute statementsEXCEPTION exception handlersEND name; 过程（PROCEDURE）PL/SQL程序PL/SQL过程、函数的参数形式模式形式说明IN 当过程被调用时，实参的值将传入该过程。在该过程内部，形参类似P L / S Q L使用的常数，即该值具有只读属性不能对其修改。当该过程结束时，控制将返回到调用环境，这时，对应的实参没有改变。OUT 当过程被调用时，实参具有的任何值将被忽略不计。在该过程内部，形参的作用类似没有初始化的P L / S Q L变量，其值为空（ N U L L）。该变量具有读写属性。当该过程结束时，控制将返回调用环境

5、，形参的内容将赋予对应的实参。IN OUT 该模式是模式IN 和O U T的组合。当调用过程时，实参的值将被传递到该过程中。在该过程内部，形参相当于初始化的变量，并具有读写属性。当该过程结束时，控制将返回到调用环境中，形参的内容将赋予实参形参可以有三种模式， IN，OUT或IN OUT。默认为IN。CREATE OR REPLACE TRIGGER schema. Trigger_name BEFORE | AFTER DELETE | INSERT | UPDATE OF column, column. ON schema.table REFERENCING OLD AS old NEW A

6、S new | NEW AS new OLD AS old FOR EACH ROWDECLARE local declarationsBEGIN execute statementsEXCEPTION exception handlersEND; 触发器（TRIGGER）PL/SQL程序 触发器的应用场景： 维护在表创建阶段通过声明限制无法实现的复杂完整性限制。 通过记录修改内容和修改者来审计表中的信息。 在表内容发生变更时，自动通知其他程序采取相应的处理。 尽量在业务设计逻辑上用存储过程来代替触发器。PL/SQL程序CREATE OR REPLACE PACKAGE package_nam

7、e AS PROCEDURE procedure1 (global_variable1 datatype, .); PROCEDURE procedure2 (global_variable1 datatype, .);END package_name;CREATE OR REPLACE PACKAGE BODY package_name AS PROCEDURE procedure1 (global_variable1 datatype, .) IS BEGIN . END procedure1; PROCEDURE procedure2 (global_variable1 datatype

8、, .) IS BEGIN . END procedure2;END package_name; 包（PACKAGE）： 包是由存储在一起的相关对象组成的PL/SQL结构。包有两个独立的部分，即说明部分和包体，这两部分独立地存储在数据字典中。PL/SQL程序PL/SQL常用数据类型名称类型说明NUMBER数字型能存放整数值和实数值，并且可以定义精度和取值范围BINARY_INTEGER数字型可存储带符号整数，为整数计算优化性能DEC数字型NUMBER的子类型，小数DOUBLE PRECISION数字型NUMBER的子类型，高精度实数INTEGER数字型NUMBER的子类型，整数INT数字型NU

9、MBER的子类型，整数NUMERIC数字型NUMBER的子类型，与NUMBER等价REAL数字型NUMBER的子类型，与NUMBER等价SMALLINT数字型NUMBER的子类型，取值范围比INTEGER小VARCHAR2字符型存放可变长字符串，有最大长度CHAR字符型定长字符串LONG字符型变长字符串，最大长度可达32,767DATE日期型以数据库相同的格式存放日期值BOOLEAN布尔型TRUE OR FALSEROWIDROWID存放数据库的行号DECLARE部分DECLARE CURSOR Oper_cur IS -声明游标 SELECT AgentID, Name FROM icd.t

10、AgentInfo WHERE AgentID 2001; -变量之间以“;”分隔 v_FaxNo AutoFaxLog.FaxNo%TYPE;-v_FaxNo与字段FaxNo数据类型相同 v_AutoFax t_SceAutoFax%ROWTYPE; -v_AutoFax与表得行数据类型相同 v_Date DATE := SYSDATE; -日期型常量 v_ErrCode NUMBER(8); v_ErrMsg VARCHAR2(200);条件语句IF (a b) AND (a c) THENg := a; -每一个赋值语句都以“;”结束ELSIF (a b) AND (a g THENg

11、:= b + c;END IF; -”END IF”也以“;”结束END IF;LOOP循环X := 100;LOOPX := X + 10;IF X 1000 THENEXIT;END IF;END LOOP;Y := X;X := 100;LOOP X := X + 10; EXIT WHEN X 1000;END LOOP;Y := X;WHILE LOOPX := 100;WHILE X = 1000 LOOP X := X + 10;END LOOP;Y = X; FOR LOOPX:=100;FOR v_Counter IN 1.10 LOOP X := X + 10;END LO

12、OP;Y := X;PL/SQL异常处理PL/SQL异常处理DECLAREX NUMBER;BEGINX:= yyyy;-Error HereEXCEPTION WHEN VALUE_ERROR THEN /*Oracle预定义的异常*/ DBMS_OUTPUT.PUT_LINE(EXCEPTION HANDED); WHEN OTHERS THEN /*OTHERS异常处理器必须排在最后，它处理所有没有明确列出的异常*/ DBMS_OUTPUT.PUT_LINE(未知异常); END; ORACLE预定义的异常异常名称ORACLE错误代码说明CURSOR_ALREADY_OPENORA-65

13、11试图打开一个已打开的光标DUP_VAL_ON_INDEXORA-0001试图破坏一个唯一性限制INVALID_CURSORORA-1001试图使用一个无效的光标INVALID_NUMBERORA-1722试图对非数字值进行数字操作LOGIN_DENIEDORA-1017无效的用户名或者口令NO_DATA_FOUNDORA-1403查询未找到数据NOT_LOGGED_ONORA-1012还未连接就试图数据库操作PROGRAM_ERRORORA-6501内部错误ROWTYPE_MISMATCHORA-6504主变量和光标的类型不兼容STORAGE_ERRORORA-6500内部错误TIMEOU

14、T_ON_RESOURCEORA-0051发生超时TOO_MANY_ROWSORA-1422SELECT INTD命令返回的多行TRANSACTION_BACKED_OUTORA-006由于死锁提交被退回VALUE_ERRORORA-6502转换或者裁剪错误ZERO_DIVIDEORA-1476试图被零除PL/SQL捕获异常DECLARESALARY_CODE VARCHAR2(1); v_ErrCode NUMBER; v_ErrMsg VARCHAR2(200);BEGINSALARY_CODE := XX;EXCEPTION WHEN OTHERS THEN v_ErrCode := S

15、QLCODE; v_ErrMsg := SUBSTR(SQLERRM,1,200); DBMS_OUTPUT.PUT_LINE(v_ErrMsg);END; 使用ORACLE游标DECLAREln_dno NUMBER;lvs_dname VARCHAR2(40);BEGINSELECT DEPT_NO,DEPT_NAMEINTO ln_dno,lvs_dnameFROM deptWHERE DEPT_NO=1;DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)|.|lvs_dname);EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTP

16、UT.PUT_LINE(NO DATA_FOUND); WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE(TOO_MANY_ROWS);END; 隐式游标 隐式游标用PL/SQL中单条记录的查询，须与INTO子句一起使用，查询的返回值被赋予INTO子句中的变量，变量的声明是在DELCARE中。 当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。数据类型必须一致返回多行或零数据时均会走异常定义显式游标 当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用SELECT INTO语句。 显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部

17、分打开，取数据，关闭。DECLAREVNAME VARCHAR2(40);VNAME ALL_VIEWS.VIEW_NAME%TYPE; CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM = 10 ORDER BY VIEW_NAME;BEGINEND; 使用%TYPE属性游标属性属性含量%FOUND布尔型属性，当最近一次该记录时成功返回，则值为TRUE%NOTFOUND布尔型属性，它的值总与%FOUND属性的值相反%ISOPEN布尔型属性，当光标是打开时返回TRUE%ROWCOUNT数字型属性，返回已从光标中读取的记录数显式游标

18、打开、取数、关闭 使用游标中的值之前应该首先打开游标。 从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。 不需要使用游标时切记关闭游标以释放内存。 DECLAREBEGINOPEN C1;FETCH C1 INTO VNAME;WHILE C1%FOUND LOOPDBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)| |VNAME; FETCH C1 INTO VNAME;END LOOP; CLOSE C1;END; 在游标中使用FOR循环DECLARECURSOR C1 IS SELECT VIEW_NAME FROM AL

19、L_VIEWS WHERE ROWNUM = 10 ORDER BY VIEW_NAME;BEGINFOR I IN C1 LOOPDBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);END LOOP; CLOSE C1;EXCEPTION WHEN OTHERS THEN IF C1%ISOPEN THEN CLOSE C1; END IF;END; 异常处理时如何关闭游标在游标中使用LOOP循环DECLARE CURSORc_empIS SELECTename, salary FROMemp; R_empc_emp%ROWTYPE;R_empEMP%ROWTYPE; BEGIN OPENc_emp; LOOP FETCHc_empINTOr_emp; EXITWHENc_emp%NOTFOUND; DBMS_OUT.PUT.PUT_LINE(SalaryofEmployee|r_emp.ename| is|r_emp.salary); ENDLOOP; CLOSEc_emp; END; 记录变量的使用游标中的删除和更新DELCARE CURSORc1IS SELECTempno,salary FROMemp WHEREcommISNULL FOR