搜索
PLSQL速成手册By晨稳1PLSQL目录PLSQL语言基础---------------------------------------------------------------------02PLSQL流程控制---------------------------------------------------------------------04PLSQL存储过程---------------------------------------------------------------------08PLSQL触发器------------------------------------------------------------------------11PLSQL函数---------------------------------------------------------------------------16PLSQL游标---------------------------------------------------------------------------22PLSQL索引---------------------------------------------------------------------------24PLSQL异常处理--------------------------------------------------------------------28PLSQL包的创建与管理----------------------------------------------------------31PLSQL动态SQL--------------------------------------------------------------------36PLSQL使用EXPLAINPLAN获取SQL语句执行计划---------------------432PL/SQL语言基础PL/SQL是过程化的SQL语言，是Oracle对SQL语言的扩展，在普通SQL语言上面增加了编程语言的特点，使得该语言不仅具有编程语言的特点，如循环、条件分支等，同时也具有对象编程语言的特点，如重载、继承等。一、PL/SQL语言的组成主要由块组成：一个块由三个基本部分组成，分别是声明、执行体、异常处理典型的块结构如下:[DECLARE--声明部分declaration_statements]BEGINexecutable_statements--执行体部分[EXCEPTIONexception_handling_statements--异常处理部分]END;/二、PL/SQL的语法要素PL/SQL语法要素包括字符集、标识符、文本、分隔符、注释等。字符集：包括英文大小写0－数字空白符、制表符、空格一些特殊符号，如～、！、@、＃等PL/SQL字符集不区分大小写标识符：变量、常量、子程序的名称以字母开头、最大长度个字符包含空格等特殊符号时，要用英文双引号括起来分隔符:()、:=(赋值)、，(表表项的分隔）、||（字符串连接）（标号开始）(标号的结束)--单行注释/**/多行注释%属性指示器，一般与TYPE、ROWTYPE等一起用三、PL/SQL中常用的变量赋值方式1.在定义时赋值v_empnonumber:=7788c_tax_rateconstantnumber(3,2):=3.35;2.使用select...into来赋值3SELECTexpressionINTOvar_listFROMtable_nameWHEREcondition--例：将号部门的名称和工作地点显示出来DECLAREv_namedept.dname%TYPE;v_locdept.loc%TYPE;BEGINselectdname,locINTOv_name,v_locFROMdeptWHEREdeptno=10;DBMS_OUTPUT.PUT_LINE('10DEPTNAME:'||v_name);DBMS_OUTPUT.PUT_LINE('10DEPTloc:'||v_loc);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('NOTFOUNDRECORD');END;/--例：执行UPDATEscott@ORCLDECLARE2v_salemp.sal%TYPE;3old_salemp.sal%TYPE;4v_idemp.empno%TYPE;5BEGIN6v_sal:=&inputsal;v_id:=&inputid;7v_id:=&inputid;8SELECTsalINTOold_salFROMempWHEREempno=v_id;9UPDATEempSETsal=v_salWHEREempno=v_id;10DBMS_OUTPUT.PUT_LINE('oldsal:'||old_sal);11DBMS_OUTPUT.PUT_LINE('newsal:'||v_sal);12END;13/Entervalueforinputsal:3500old6:v_sal:=&inputsal;new6:v_sal:=3500;Entervalueforinputid:7788old7:v_id:=&inputid;new7:v_id:=7788;oldsal:3000newsal:35004PLSQL流程控制一、条件分支结构1.IF...THEN...ENDIFIFconditionTHENstatementENDIF;判断condition是否成立，成立就执行IF与ENDIF之间的语句。--例：输入员工编号，查询其工资，如果他们的职位是CLERK，则工资增加%，再显示修改前后的工资数。2.IF...THEN...ELSE...ENDIFIFconditionTHENstatements1;ELSEstatements2;ENDIF;判断condition是否成立，成立就执行IF与ELSE之间的语句，否则执行ELSE与ENDIF之间的语句。--例：输入员工编号,查询其工资,如果他们的职位是CLERK,则工资增加%,如果不是CLERK,工资增加8%,再显示修改前后的工资数。3.IF...THEN...ELSIF...THEN...ELSE...ENDIFIFcondition1THENstatements1;ELSIFcondition2THENstatements2;5ELSEelse_statements;ENDIF;--例:输入员工编号,查询其工资,如果其职位是CLERK,则工资增加%,如果是SALESMAN工资增加%,其它的加%,显示修改前后的工资数。DECLAREv_empidemp.empno%TYPE;v_jobemp.job%TYPE;v_old_salemp.sal%TYPE;v_new_salemp.sal%TYPE;BEGINv_empid:=&inputid;SELECTjob,salINTOv_job,v_old_salFROMempWHEREempno=v_empid;IFv_job='CLERK'THENv_new_sal:=v_old_sal*1.1;ELSIFv_job='SALESMAN'THENv_new_sal:=v_old_sal*1.08;ELSEv_new_sal:=v_old_sal*1.05;ENDIF;UPDATEempSETsal=v_new_salWHEREempno=v_empid;DBMS_OUTPUT.PUT_LINE('Oldsal:'||v_old_sal);DBMS_OUTPUT.PUT_LINE('Newsal:'||v_new_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('NotFOUNDRECORD');END;/4.等值比较的CASE多分支CASEexpressionWHENresult_1THENstatements1;WHENresult_2THENstatemnts2;......[ELSEelse_statements;]ENDCASE;--使用case分支完成前面的示例--例:输入员工编号,查询其工资,如果其职位是CLERK,则工资增加%,如果是SALESMAN工资增加%,其它的加%,显示修改前后的工资数。6DECLAREv_empidemp.empno%TYPE;v_jobemp.job%TYPE;v_old_salemp.sal%TYPE;v_new_salemp.sal%TYPE;BEGINv_empid:=&inputid;SELECTjob,salINTOv_job,v_old_salFROMempWHEREempno=v_empid;CASEv_jobWHEN'CLERK'THENv_new_sal:=v_old_sal*1.1;WHEN'SALESMAN'THENv_new_sal:=v_old_sal*1.08;ELSEv_new_sal:=v_old_sal*1.05;ENDCASE;UPDATEempSETsal=v_new_salWHEREempno=v_empid;DBMS_OUTPUT.PUT_LINE('Oldsal:'||v_old_sal);DBMS_OUTPUT.PUT_LINE('Newsal:'||v_new_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('NotFOUNDRECORD');END;/二、循环结构1.LOOP循环LOOPstatement;EXIT[WHENcondition];ENDLOOP;--例：用LOOP写一个程序求1＋＋＋＋...+10之和DECLAREv_nINT:=1;v_sINT:=0;BEGINLOOPv_s:=v_s+v_n;v_n:=v_n+1;EXITWHENv_n10;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+3+...+10='||v_s);END;72.WHILE循环WHIELconditionLOOPstatement;ENDLOOP;--下面使用while循环完成loop循环中的示例DECLAREv_sNUMBER:=0;v_nNUMBER:=1;BEGINWHILEv_n=10LOOPv_s:=v_s+v_n;v_n:=v_n+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+3+...+10='||v_s);END;/3.FOR循环FORloop_indexIN[reverse]lowest_number..highest_numberLOOPstatements;ENDLOOP;--下面使用for循环完成loop循环中的示例DECLAREv_sNUMBER:=0;BEGINFORv_nIN1..10LOOPv_s:=v_s+v_n;DBMS_OUTPUT.PUT_LINE(v_n);ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+3+...+10='||v_s);END;/DECLAREv_sNUMBER:=0;BEGINFORv_nINREVERSE1..10LOOP--注意reverse是反向循环，即从到v_s:=v_s+v_n;DBMS_OUTPUT.PUT_LINE(v_n);ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+3+...+10='||v_s);END8PLSQL存储过程存储过程：执行一个任务，该任务包括了一系列的PLSQL语句，存储在数据库中，成为数据库一个对象。效率比较高的，但你创建一个存储过程它会进行一个判断编