PLSQL系列培训之一：基本语法(幻灯说明稿).doc

PL/SQL系列培训之一：基本语法第一讲分四个部分：SQL基本语法、基本逻辑控制、事务和异常SQL基本语法SQL分类数据定义语言DDL：CREATE/DROP/ALTER/COMMENT/TRUNCATE等数据操作语言DML：UPDATE/DELETE/INSERT/CALL/SELECT FOR UPDATE等数据控制语言DCL：GRANT/REVOKE/COMMIT/ROLLBACK等SELECT FOR UPDATESELECT FOR UPDATE锁住某个表的相关行。如果是多表查询：SELECT FOR UPDATE of table.column -用来表连接时锁住其中一个表的相关行；-否则将锁住所有表（包括from子查询，不包括条件子查询）的相关行举例：SELECT t1.id, FROM test_table1 t1, test_table2 t2WHERE t1.id = t2.idFOR UPDATE of t1.idLOCK TABLELOCK TABLE table_name IN lockmode MODE (NOWAIT)LOCK TABLE tanjie21 IN SHARE UPDATE MODE; -同下LOCK TABLE tanjie21 IN ROW SHARE MODE; -行共享锁，其他会话无法获得排他锁LOCK TABLE tanjie22 IN ROW EXCLUSIVE MODE; -行排他锁，禁止SHARE锁定-UPDATE/DELETE/INSERT自动获得LOCK TABLE tanjie23 IN SHARE MODE; -表共享锁LOCK TABLE tanjie24 IN EXCLUSIVE MODE; -表排他锁LOCK TABLE tanjie25 IN SHARE ROW EXCLUSIVE MODE; -表级共享行级排他WAIT/NOWAITNOWAIT -报ora-00054WAIT integer(秒) -报ora-30006EXECUTE IMMEDIATE处理动态SQL的两种方式：（1）EXECUTE IMMEDIATE（2）OPEN-FOR, FETCH, and CLOSE性能优化（以后再讲）：BULK EXECUTE IMMEDIATEEXECUTE IMMEDIATE的完整语法：EXECUTE IMMEDIATE dynamic_SQL_stringINTO defined_variable1, defined_variable2, .USING IN | OUT | IN OUT bind_argument1, bind_argument2, .RETURNING INTO | RETURN bind_argument1, bind_argument2, .使用INTO子句确定返回的是单行查询结果（与SELECT INTO类似）SUB QUERY标准子查询：和主查询没有直接联系，在ORACLE中首先执行且只执行一次关联子查询：在执行过程中需要与主查询发生联系，如子查询的条件依赖于主查询传递的条件。单值：= -注意要确保子查询出来的记录最多只有一条，如果没有返回记录，需要注意空的处理-尤其是update多值：IN/NOT IN/EXISTS/NOT EXISTS/ANY/SOME/ALLANY/SOME/ALL前必须加 =,=,15，那么将会右向左被截断。LTRIM/RTRIMSELECT LTRIM( hello world ) FROM DUAL;SELECT RTRIM( hello world ) FROM DUAL;SELECT LTRIM(hhllo world, h) FROM DUAL;SELECT RTRIM(hello world, ld) FROM DUAL;LOWER/UPPERSELECT UPPER(hhllo world) FROM DUAL;SELECT LOWER(hello world) FROM DUAL;ABS（绝对值）FUNCTION ABS (n NUMBER) RETURN NUMBER;SELECT ABS(-66) FROM DUAL;SELECT ABS(55-66) FROM DUAL;SIGN（符号判断）SELECTsign(100),sign(-100),sign(0)FROMDUAL; CEIL（大于x的最小整数）SELECTceil(15.7), ceil(-100/13)FROMDUAL; FLOOR（小于x的最大整数）SELECTfloor(15.7), floor(-100/13)FROMDUAL; MOD（取余）SELECTmod(11,4),mod(11,-4),mod(-11,4),mod(-11,-4) FROMDUAL;注：MOD(M,N) 相当于 DECODE(SIGN(M),-1,-)|MOD(ABS(M),ABS(N)GREATEST/LEASTSELECT greatest(3, 5, 2) FROM DUAL; SELECT least(3, 5, 2) FROM DUAL;日期处理TO_CHAR/TO_DATESELECTto_char(sysdate,yyyy/mm/dd hh24:mm:ss) FROMDUAL;SELECTto_date(01-01-2009,dd-mm-yyyy) FROMDUAL;NUMTODSINTERVALSELECTnumtodsinterval(150,DAY)FROMDUAL;SELECTnumtodsinterval(150,SECOND)FROMDUAL;SELECTnumtodsinterval(150, MINUTE)FROMDUAL;SELECTnumtodsinterval(150, HOUR)FROMDUAL;NUMTOYMINTERVALSELECTnumtoyminterval(100,YEAR) FROMDUAL;SELECTnumtoyminterval(100,MONTH) FROMDUAL;SYSDATE/CURRENT_DATESYSDATE 系统时间 CURRENT_DATE 会话时间SELECTsysdate,current_date FROMDUAL;一般情况下，二者相同，但有时CURRENT_DATE比SYSDATE快一秒。但如果修改了当前会话的时区，则会不同。SELECTSESSIONTIMEZONEFROMDUAL;ALTER SESSIONSET TIME_ZONE=+09:00;SELECTsysdate,current_date FROMDUAL;LAST_DAYSELECTLAST_DAY(to_date(2009-03-10,yyyy-mm-dd) FROMDUAL;SELECT LAST_DAY(to_date(2009-03,yyyy-mm) FROM DUAL;其他常用知识点ROUND/ TRUNCSELECTround(55.5),round(-55.4),trunc(55.5),trunc(-55.5), trunc(0.5)FROMDUAL;SELECTround(3456.789,2),round(3456.789,1),round(3456.789,0),round(3456.789,-1),round(3456.789,-2) FROMDUAL;(思考题：想要得到3456.7890，即保留4位小数，用round(3456.789,4)可以吗？该怎么写？)-日期处理SELECTround(to_date(2009/03/20,yyyy/mm/dd),mm) FROMDUAL;SELECTround(sysdate,dd) FROMDUAL; -若当前为20点则显示为2009-3-11SELECTtrunc(sysdate,YYYY) FROMDUAL;SELECTtrunc(sysdate,MM) FROMDUAL;SELECTtrunc(sysdate,MONTH) FROMDUAL;SELECTtrunc(sysdate,DD) FROMDUAL; -本月第一天SELECTtrunc(sysdate,DAY) FROMDUAL; -本周第一天SELECTtrunc(sysdate,HH) FROMDUAL;SELECTtrunc(sysdate,MI) FROMDUAL;其他常用知识点ROWID/ROWNUMBERROWID1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。2、利用rowid可以删除表中的重复记录,只剩1条delete cctest a where a.rowid not in (select min(b.rowid) from cctest b where a.id = b.id);3、在plsqldeveloper中的sqlwindow中对cctest的数据后进行手工修改，则必须使用select rowid,t.* from cctest t;ROWNUMBER对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。注意事项：（1）rownum不能跟，=，等使用（2）在同一个查询或子查询中，rownum尽量不与order by混用select rownum, t.* from tanjie21 t where rownum 10 order by name desc; select rownum, t.* from tanjie21 t where rownum 3 order by name desc; （3）rownum用于分页：select * from ( select rownum as myrownum , t.* from (select * from tanjie10) t where rownum 10;需要注意的是，如果执行计划发生改变，查询出来的结果集排序可能变化，此时rownum分页可能变化。其他常用知识点RETURNING/CONNECT BY/MERGE/分析函数RETURNING INTOreturning是用来获取刚处理的一个或多个字段的信息，可用在任何DML语句末尾；CONNECT BY树结构记录处理时会很有用MERGEMERGE INTO products pUSING newproducts np ON (duct_id = duct_id)WHEN MATCHED THENUPDATESET duct_name = duct_name,p.category = np.categoryDELETE WHERE (p.category = ELECTRNCS)WHEN NOT MATCHED THENINSERTVALUES (duct_id, duct_name, np.category)分析函数ORACLE提供的专门针对数据进行统计分析时使用的函数，非常强大。诸如：COUNT、ROLLUP、CUBE、CORR、over(order by .)、over(partition by .)、LAG、LEAD、MAX、MIN、SUM、AVG、RANK 等等事务TRANSACTION事务的四个基本特性（ACID）： 原子性：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分 一致性：在事务处理执行之前和之后，数据是一致的。 隔离性：一个事务处理对另一个事务处理没有影响。 持续性：当事务处理成功执行到结束的时候，其效果在数据库中被永久纪录下来。显式提交两阶段savepoint A;insert into tanjie21 values(6, 6);savepoint B;insert into tanjie21 values(7, 7);select * from tanjie21;rollback to A;rollback to B;自治事务CREATE OR REPLACE PROCEDURE tanjie_at_test_spAS PROCEDURE write_log(pi_log_info VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO tanjie_log VALUES(SEQ_TANJIE_LOG.NEXTVAL, pi_log_info); COMMIT; END;BEGIN INSERT INTO tanjie21 VALUES(1, hello); IF (TRUE) THEN write_log(ERROR); RAISE_APPLICATION_ERROR(-20023, error); END IF;END;异常EXCEPTION基本结构：DECLARE v_id NVARCHAR2(10);BEGIN SELECT id INTO v_id FROM tanjie21 WHERE 11; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line(TEST.NO_DATA_FOUND); WHEN OTHERS THEN RAISE;END;异常的嵌套（传播）DECLARE v_id NVARCHAR2(10);BEGIN SELECT id INTO v_id FROM tanjie21 WHERE rownum = 1; BEGIN SELECT id INTO v_id FROM tanjie21 WHERE 11; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE; END;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(TEST.NO_DATA_FOUND);END;常见系统异常NO_DATA_FOUND -没值TOO_MANY_ROWS -多值ZERO_DIVIDE -除0VALUE_ERROR -类型不匹配DUP_VALUE_ON_INDEX -违反唯一约束自定义异常DECLARE v_id NVARCHAR2(10); tanjie_exception EXCEPTION;BEGIN SELECT id INTO v_id FROM tanjie21 WHERE rownum = 1; IF (v_id IS NOT NULL) THEN RAISE tanjie_exception; END IF;EXCEPTION WHEN tanjie_exception THEN DBMS_OUTPUT.put_line(TEST.TANJIE EXCEPTION); WHEN OTHERS THEN DBMS_OUTPUT.put_line(TEST.OTHER EXCEPTION);END;RAISE_APPLICATION_ERROR匿名的自定义异常RAISE_APPLICATION_ERROR(error_number, error_message);orRAISE_APPLICATION_ERROR(error_number, error_message, keep_errors);Error_number: -20,999 and -20,000Error_message: 最多512字节Keep_errors: 默认false，为true表示将该异常添加到异常列表中，否则异常列表清空DECLARE v_id NVARCHAR2(10);BEGIN SELECT id INTO v_id FROM tanjie21 WHERE rownum = 1; IF (v_id IS NOT NULL) THEN RAISE_APPLICATION_ERROR(-20050, HELLO WORLD); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLCODE | - | SQLERRM);END;RAISE_APPLICATION_ERROR -2DECLARE v_id NVARCHAR2(20); tanjie_exception EXCEPTION; PRAGMA EXCEPTION_INIT(tanjie_exception