PL_SQL基础培训.

PL/SQL基础培训——刘丹2012.8.25CUC-AS1-N3PL/SQL语言PL/SQL语言的编程规范Oracle数据库的基本数据类型和函数应用PL/SQL语言编写基本的sql通过本章的学习应该掌握：一、PL/SQL语言编程规范1）命名语言---命名使用能表述对象意义的单词或者约定俗成的符号，不允许使用中文或者特殊符号。单词分隔---命名的各单词使用下划线进行分隔。保留字----命名禁止使用sql保留字（可以在v$reserved_words视图中查找所有的保留字）。命名长度---限制在30个字符内（含前缀）。2）命名规范普通变量---前缀为v_,存放字符、数字、日期型变量。游标变量---前缀为cur_,存放游标记录集。存储过程---前缀为p_,按照业务操作命名。函数---前缀为f_,按照业务操作命名。包---前缀为pkg_,按照业务操作集合命名。触发器---前缀为trig_,触发器名是前缀+表名+触发器名索引---前缀为IDX_,索引名是前缀+表名+索引字段3）使用绑定变量当一个sql语句提交后，oracle会首先检查一下共享缓冲池（sharedpool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。而唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.如果你的查询本身耗时就很长，就没必要绑定变量了，因为解析的消耗微乎其微selectsql_text,parse_callsfromv$sqlwheresql_textlike'UPDATEAA1%'eg:ls_sql:='updateAA1setstatus=''1'',sale_trade_id='''||v_trade_id||''',sale_date=sysdatewherecard_no=:1;executeimmediatels_sqlusingvi_card_no;PL/SQL语言1）基本数据类型VARCHAR2变长字符串，最长为4000字符。NUMBER数值型。LONG变长字符数据，最长为2G字节。DATE日期型。RAW二进制数据，最长为255字节。LONGRAW变长二进制数据，最长为2G字节。CHAR定长字符数据，最长为255。LOB大对象”数据类型CLOB可用于存储非结构化的XML文档。BLOB可以存储较大的二进制对象，如图形、视频剪辑和声音剪辑等。二、基本数据类型与函数PL/SQL语言2）函数单行字符串函数----ASCII(),INSTR(),LENGTH(),UPPER(),LPAD(),LTRIM(),RPAD(),RTRIM(),REPLACE(),SUBSTR(),TRIM()，CONCAT()单行数字函数----ABS(),ACOS(),ASIN(),ATAN(),CEIL(),COS(),LOG(),MOD(),ROUND(),SIN(),TRUNC()单行日期函数----ADD_MONTHS(),LAST_DAY(),MONTHS_BETWEEN(),NEW_TIME(),NEXT_DAY(),ROUND(),SYSDATE,TRUNC(),TO_CHAR(),TO_DATE(),TO_NUMBER()其他单行函数----BFILENAME(),DECODE(),DUMP(),GREATEST(),LEAST(),USERENV(),NVL()NULL和单行函数----NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为NULL值，这个规则也适合很多函数，只有CONCAT，DECODE，DUMP，NVL，REPLACE在调用了NVL参数时可以返回非NULL值。a、单行函数----返回基于单行的单一结果PL/SQL语言又名集合函数----AVG(),COUNT(),MAX(),MIN(),STDDEV(),SUM(),VARIANCE()数据进行分组----GROUPBYOracle的GROUPBY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A,B,C)的话，首先会对(A、B、C)进行GROUPBY，然后对(A、B)进行GROUPBY，然后是(A)进行GROUPBY，最后对全表进行GROUPBY操作。如果是GROUPBYCUBE(A,B,C)，则首先会对(A、B、C)进行GROUPBY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUPBY操作。grouping_id()可以美化效果b、组函数----返回基于多行的单一结果c、嵌套函数----函数可以嵌套，优先权基于位置，遵循由内到外，由左到右的原则。PL/SQL语言Oracle扩展PL/SQL简介PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，即构成一个PL/SQL程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：DECLARE---说明BEGIN---语句序列EXCEPTION---例外处理程序END；三、应用PL/SQL语言编写基本的sqlPL/SQL语言控制语句1）分支语句：IFconditionTHENSequence_of_statements;ENDIF;IFconditionTHENSequence_of_statement1;ELSESequence_of_statement2;ENDIF;PL/SQL语言2）循环语句：LOOPSequence_of_statements;IFconditionTHENEXIT;ENDIF;ENDLOOP;WHILEconditionLOOPSequence_of_statements;ENDLOOP;FORcounterINlower_bound..higher_boundLOOPSequence_of_statements;ENDLOOP;PL/SQL语言数据操纵语言命令（DML）用于在关系数据库对象中操纵数据INSERTinsertintotab_name(col1,col2)values(‘1’,’2’);UPDATEupdatetab_nameaseta.col1=‘1’wherecol2=‘1’;DELETEdeletefromtab_namewherecol1=‘1’;PL/SQL语言数据控制语言命令（DCL）用于创建与用户访问相关的对象，也控制着用户的权限分配ALTERPASSWORD改变口令GRANT为用户授予特权REVOKE从用户处收回特权事物控制命令Commit完成事务，数据修改成功并对其他用户开放。Rollback撤销事务，撤销所有操作。rollbacktosavepoint撤销在设置的回滚点以后的操作。settransaction响应事务或语句的一致性，特别对于事务使用回滚段。PL/SQL语言数据定义语言命令（DDL）Create----创建某个对象Createtabletab_name(colvarchar(2)notnull);Drop----删除某个对象Droptable(view)tab_name;Grant----赋予权限命令Grantupdateontab_nametouser_name;Revoke----收回权限命令Revokeupdateontab_namefromuser_name;PL/SQL语言SQL中操作符的使用定义：操作符是保留的字或字符，主要用于比较和算术运算。分类：比较操作符，逻辑操作符和算术运算符。1、比较操作符包括：=，,,,=,=。注意比较双方的数据类型。2、算术运算符包括：+，-，*，/等。与数学运算规则一致。PL/SQL语言SQL中逻辑操作符的使用ISNULL（是否为空）BETWEEN（在某两个值之间）IN（一系列值中）LIKE（相似值的比较）EXITS（是否存在符合条件的数据）ALL/ANY（一组数据的所有/其中的任何一个）AND/OR（逻辑与/或）PL/SQL语言子程序1）存储过程：CREATEPROCEDURE过程名(参数说明1，参数说明2，。。。)IS[局部说明]BEGIN执行语句；END过程名；2）存储函数：CREATEFUNCTION函数名(参数说明1，参数说明2，。。。)RETURN类型IS[局部说明]BEGIN执行语句；END函数名；PL/SQL语言应用PL/SQL语言编写sqlsql语句不区分大小写sql语句可以由一行或者多行组成在一行中关键字不能缩写或者被拆开子句通常书写在不同的行上采用行缩进来增强程序的阅读性PL/SQL语言CASE表达式CASEWHENcomparison_expr1THENreturn_expr1[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDPL/SQL语言DECODE表达式DECODE(col|expression,search1,result1[,search2,result2,...,][,default])DECODE语句=IF-THEN-ELSE的实现PL/SQL语言%type用法declaremyidaa2.MDATE%type;mynameaa2.SNUM%type;beginselectMDATE,SNUMintomyid,mynamefromaa2whereMDATE='20120801';dbms_output.put_line(myid);dbms_output.put_line(myname);end;/当表结构字段类型或者长度变化后,程序中无需在变PL/SQL语言%rowtype用法declaretypetype_deptistableofaa2%rowtypeindexbybinary_integer;tbtype_dept;begintb(1).MDATE:='20120812';tb(1).SNUM:='12';tb(2).MDATE:='20120813';tb(2).SNUM:='13';dbms_output.put_line(tb(1).MDATE||tb(1).SNUM);dbms_output.put_line(tb.COUNT);end;/PL/SQL语言TYPE用法，相当于结构体declarelv_order_dateDAte:=sysdate;lv_last_txtvarchar2(5)default'001';lv_lastvarchar2(10)notnull:='us';TYPEtype_testisrecord(MDATEaa2.MDATE%type,SNUMaa2.SNUM%type);rectype_test;beginlv_order_date:=sysdate;dbms_output.put_line(lv_last);selectMDATE,SNUMintorecfromaa2whereMDATE='20120801';dbms_output.put_line(rec.MDATE);dbms_output.put_line(rec.SNUM);end;PL/SQL语言游标的使用:declare--类型定义cursorc_jobisselectMDATE,SNUMfromaa2;--定义一个游标变量c_rowc_job%rowtype;beginforc_rowinc_jobloopdbms_output.put_line(c_ro