复杂sql语句和存储过程

SQL语句语句一、简单一、简单sqlsql语句语句二、复杂二、复杂sqlsql语句语句三、综合应用三、综合应用四、四、SQLSQL编程编程五、存储过程五、存储过程内容大纲基本结构基本结构 SELECT distinct*,column alias,FROM TABLE ORDER BY columnSELECT:选择要查询的数据列*:选择所有的列COLUMN:选择的列名FROM:数据列所在的表ALIAS:给选定的列或表一个别名（sql和oracle区别）简单的数据查询简单的数据查询查询结果排序查询结果排序 使用ORDER BY子句对查询返回的结果按一列或多列排序。ORDER BY子句的语法格式为：ORDER BY column_name ASC|DESC,n其中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。例如：Select*FROM usertableORDER BY age desc,userid ASC另外，可以根据表达式进行排序。简单的数据查询简单的数据查询distinctdistinct 删除所有重复的值，排序操作增加系统开销。注意查表的前查表的前N N行记录行记录select top n*from table_name select*from table_name where rownumROLLBACK;SQLROLLBACK;3)COMMIT WORK3)COMMIT WORK：提交。：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。到所做的事情，别人只有在最后提交完成后才可以看到。简单的数据查询简单的数据查询操作符操作符 算术操作符算术操作符 关系操作符关系操作符比较操作符比较操作符逻辑操作符逻辑操作符 简单的数据查询简单的数据查询算术操作符算术操作符 加：加：+减：-乘：*除：/Sql：%取模 oracle：Mod 简单的数据查询简单的数据查询关系操作符关系操作符 主要用于条件判断语句或用于where子串中，关系操作符检查条件和结果是否为true或false 小于操作符 大于操作符=大于或等于操作符=等于操作符!=不等于操作符:=:=赋值操作符（oracle）日期、字符串比较例子简单的数据查询简单的数据查询比较操作符比较操作符 IS NULL IS NULL 如果操作数为如果操作数为NULLNULL返回返回TRUETRUE LIKE LIKE 比较字符串值比较字符串值(通配符，走索引？)BETWEEN BETWEEN 验证值是否在范围之内验证值是否在范围之内 IN,NOT IN,EXISTS,NOT EXISTSIN,NOT IN,EXISTS,NOT EXISTS验证操作数在验证操作数在设定的一系列值中设定的一系列值中 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1 简单的数据查询简单的数据查询逻辑操作符逻辑操作符 AND AND 两个条件都必须满足两个条件都必须满足 OR OR 只要满足两个条件中的一个只要满足两个条件中的一个 NOT NOT 取反取反 优先（例）优先（例）简单的数据查询简单的数据查询操作数字型数据操作数字型数据 SELECT FUNETION_NAME(PARAMETERS)FUNCTION 参数 结果 1.绝对值 S:select abs(-1)value O:select abs(-1)value from dual 2.取整(大)S:select ceiling(-1.001)value O:select ceil(-1.001)value from dual 3.取整（小）S:select floor(-1.001)value O:select floor(-1.001)value from dual 4.取整（截取）S:select cast(-1.002 as int)value O:select trunc(-1.002)value from dual 5.四舍五入 S:select round(1.23456,4)value 1.23460O:select round(1.23456,4)value from dual 简单的数据查询简单的数据查询字符串处理字符串处理 SUBSTRING，SUBSTR （区别）CONCAT()字符串可以做最简单的“加”法，即合并运算：|例：SELECT column1|.|column2|$FROM tablenameSTUFF()Replace()Len,datalength lengthlower,upperltrim,rtrim ltrim,rtrim,trim简单的数据查询简单的数据查询操作日期型数据操作日期型数据 系统时间系统时间S:select getdate()value O:select sysdate value from dual 求日期 S:select convert(char(10),getdate(),20)value O:select trunc(sysdate)value from dual select to_char(sysdate,yyyy-mm-dd)value from dual 前后几日 直接与整数相加减 求时间 S:select convert(char(8),getdate(),108)value O:select to_char(sysdate,hh24:mm:ss)value from dual 简单的数据查询简单的数据查询空处理空处理 NVL:在数据列的值为空时（并非为零），以真实的值进行替代处理例：NVL(base_salary,2000)注：任何未被声明为NOT NULL 或PRIMARY KEY 的列都可包含空值，注意，空值就是空值，并不是值为零或空格，零是一个数值，而空格是一个字符。简单的数据查询简单的数据查询数据类型转换数据类型转换 CAST(expression AS data_type)CONVERT(data_type(length),expression,style)(r9)CHAR|TO_DATE TO_NUMBER DATE|TO_CHAR NUMBER|TO_CHAR TO_DATEHEXTORAW CHARTOROWID 简单的数据查询简单的数据查询子查询子查询 in,existsin,exists not in,not exists not in,not exists 复杂复杂sql语句语句结果的并、交、差运算结果的并、交、差运算 unionunionunion allunion all UNION 会去掉结果集中的重复记录，而 UNION ALL 会返回合并后的全部结果集。(a union b a中重复记录也去掉)IntersectIntersectminusminus（例）复杂复杂sql语句语句集合函数集合函数 AVGAVG求平均值求平均值 （整型）（整型）COUNTCOUNT 值的计数值的计数MAXMAX求最大值求最大值 （字符串）（字符串）MINMIN求最小值求最小值SUMSUM求和求和复杂复杂sql语句语句分组分组 group group by,havingby,having GROUP BY的特征：能按列或表达式分组,一般同集合函数一起用,为每组产生一个值 rollup,cube 对于GROUP BY语句来说，只能返回对于每个GROUP BY分组的汇总数据。使用ROLLUP除了返回分组汇总数据外，还可以返回对于分组汇总的汇总值。CUBE操作除了提供ROLLUP的功能外，还可以对统计的其他维度进行汇总计算。grouping grouping 函数函数 （例子）（例子）compute by compute by 可以统计排序中结果完全相同的列，统计值作为查询结果以附加行的形式显示复杂复杂sql语句语句多表查询多表查询 自连接查询，对同一个表进行连接操作内连接查询，外连接查询，交叉连接查询，也作无条件查询。联合查询 复杂复杂sql语句语句自连接查询自连接查询 一个表自己与自己建立连接称为自连接或自身连接一个表自己与自己建立连接称为自连接或自身连接 select x.select x.*from sclass x,sclass y from sclass x,sclass ywhere o=101 and x.degreey.degree and where o=101 and x.degreey.degree and y.sno=9505201 and o=101y.sno=9505201 and o=101order by x.degree descorder by x.degree desc复杂复杂sql语句语句内连接内连接(INNER JOIN)(INNER JOIN)它只返回两个数据集合之间匹配关系的那些行它只返回两个数据集合之间匹配关系的那些行.将位于两个互相交叉的数据集合中重叠部分以内的那些数据行连接起来将位于两个互相交叉的数据集合中重叠部分以内的那些数据行连接起来 select select 字段名列表字段名列表 from from 表名表名 inner join inner join 表名表名 on on 连接条件连接条件 where where 条件表达式条件表达式 等值连接 不等值连接 自然连接 复杂复杂sql语句语句外连接外连接(OUTER JOIN)(OUTER JOIN)左外连接(LEFT OUTER JOIN或LEFT JOIN)右外连接(RIGHT OUTER JOIN或RIGHT JOIN)全外连接(FULL OUTER JOIN或FULL JOIN)替换in，not in(例)复杂复杂sql语句语句交叉连接交叉连接 交叉连接不使用任何连接条件来限制结果集合,将各表的记录以“笛卡尔”积的方式组合起来 select select*from student,sclass from student,sclassselect select*from student a CROSS JOIN sclass ORDER BY from student a CROSS JOIN sclass ORDER BY a.snoa.sno 复杂复杂sql语句语句复制表结构复制表结构行列转换行列转换找含匹配列的行找含匹配列的行创建复杂的视图创建复杂的视图综合应用综合应用程序段程序段SQLSERVERSQLSERVERBEGINBEGIN ENDENDORACLEORACLEd declareeclare 变量声明；变量声明；beginbegin 语句部分语句部分EXCEPTION EXCEPTION-exception statements-exception statements end;end;Sql编程编程控制结构控制结构顺序结构顺序结构选择结构选择结构循环结构循环结构Sql编程编程选择结构选择结构sqlserversqlserver if if 条件条件begin begin SQL SQL语句语句endendelse ifelse ifbeginbegin SQL SQL语句语句endendelse else b beginegin SQLSQL语句语句EndEndSql编程编程oracleoracleif thenif thenend if;end if;if thenif thenelseelseEnd if;End if;if thenif thenelsif thenelsif thenend if;end if;casecase语句语句Sql编程编程例子：例子：declare szbz intset szbz=3if szbz=1 select szbz=1 else if szbz=2 select szbz=2else select szbz=3Sql编程编程set serveroutput ondeclare szbz int;begin szbz:=3;if szbz=1 then dbms_output.put_line(szbz=1);elsif szbz=2 then dbms_output.put_line(szbz=2);else dbms_output.put_line(szbz=3);end if;end;/Sql编程编程casecase语句语句case nwhen 1 then Action1;when 2 then Action2;when 3 then Action3;else ActionOther;end case;Case-CASE 表达式 when n=1 then Action1;when n=2 then Action2;when n=3 then Action3;when(n 3 and n 1 then Action3;when q 1 then Action3;end case;end case;exceptionexceptionwhen when case_not_foundcase_not_foundthen then Dbms_Output.Put_LineDbms_Output.Put_Line(Trapped:case_not_foundTrapped:case_not_found););.Sql编程编程例子例子 declare i int;begin i:=2;case iwhen 0 then dbms_output.put_line(i=0);when 1 then dbms_output.put_line(i=1);else dbms_output.put_line(i=2);end case;end;/Sql编程编程循环结构循环结构sqlsql while 语句 declare count intdeclare count int set count=0 set count=0 while(countwhile(count 10)10)begin begin set count=count+1 set count=count+1 print count print count end end在循环中常用的语句有break和continue,break为跳出while,而continue为跳出当前循环，进入下一循环。有时候也用到return和goto语句Oracle PL/SQL EXIT 关键字等同。Oracle 没有 CONTINUE 关键字 Sql编程编程ORACLEORACLE looploop if if then exit;then exit;end loop;end loop;looploop exit when exit when;end loop;end loop;while while loop loopend loop;end loop;for for 变量变量 in in 下界下界上界上界 loop loopend loop;end loop;Sql编程编程游标游标定义定义属性属性打开打开提取提取关闭关闭参数化游标参数化游标游标变量游标变量Sql编程编程游标定义游标定义sqlsqlDECLARE cursor_name INSENSITIVE SCROLL CURSORDECLARE cursor_name INSENSITIVE SCROLL CURSORFOR select_statementFOR select_statementFOR READ ONLY|UPDATE OF column_name,.nFOR READ ONLY|UPDATE OF column_name,.nINSENSITIVE对基本表的修改并不影响游标提取的数据,默认可修改SCROLL表明所有的提取操作（如FIRST、LAST、PRIOR、NEXT、RELATIVE(+-)、ABSOLUTE(+-)）都可用，如果不使用该保留字，那么只能进行NEXT 提取操作Sql编程编程oracleoraclecursor cursor 游标名游标名 is select is select;属性属性%FOUND:判断当前游标是否指向有效的一条行记录.是则返回TRUE,否则返回FALSE%NOTFOUND:与%FOUND 恰好相反.%ISOPEN:判断游标是否打开,打开则返回TRUE,否则返回FALSE.%ROWCOUNT:判断当前游标在所指向的结果集中提取的行数.并非所有总记录数 Sql编程编程游标打开游标打开open游标名;提取游标提取游标Fetch next from 游标名 into 变量(sql)Fetch 游标名 into 变量;(ora)关闭游标关闭游标close 游标名;deallocate游标名(sql用)Sql编程编程例子例子 sqldeclare zffsdm nvarchar(20)-declare zffsdm nvarchar(20)-定义变量来保存定义变量来保存IDID号号declare mycursor cursor for select zffsdm from zb_zffs where declare mycursor cursor for select zffsdm from zb_zffs where kjnd=2009 and gsdm=000 -kjnd=2009 and gsdm=000 -为所获得的数据集指定游标为所获得的数据集指定游标open mycursor -open mycursor -打开游标打开游标fetch next from mycursor into zffsdm -fetch next from mycursor into zffsdm -开始抓第一条数据开始抓第一条数据while(fetch_status=0)-while(fetch_status=0)-如果数据集里一直有数据如果数据集里一直有数据beginbegin select zffsdm,zffsmc from zb_zffs where zffsdm=zffsdm and select zffsdm,zffsmc from zb_zffs where zffsdm=zffsdm and kjnd=2009 and gsdm=000kjnd=2009 and gsdm=000 fetch next from mycursor into zffsdm -fetch next from mycursor into zffsdm -跳到下一条数据跳到下一条数据endendclose mycursor -close mycursor -关闭游标关闭游标deallocate mycursor -deallocate mycursor -删除游标删除游标Sql编程编程OracleOracle set serveroutput onset serveroutput ondeclare declare szzffsdm varchar2(20);-szzffsdm varchar2(20);-定义变量来保存定义变量来保存IDID号号 szdm varchar2(20);szdm varchar2(20);szmc varchar2(20);szmc varchar2(20);cursor mycursor is select zffsdm from zb_zffs where kjnd=2008 cursor mycursor is select zffsdm from zb_zffs where kjnd=2008 and gsdm=111001;-and gsdm=111001;-为所获得的数据集指定游标为所获得的数据集指定游标beginbegin open mycursor;-open mycursor;-打开游标打开游标 loop loop fetch mycursor into szzffsdm;fetch mycursor into szzffsdm;exit when mycursor%notfound;exit when mycursor%notfound;select zffsdm,zffsmc into szdm,szmc from zb_zffs where zffsdm select zffsdm,zffsmc into szdm,szmc from zb_zffs where zffsdm=szzffsdm and kjnd=2008 and gsdm=111001;=szzffsdm and kjnd=2008 and gsdm=111001;dbms_output.put_line(szdm|=|szmc);dbms_output.put_line(szdm|=|szmc);end loop;end loop;close mycursor;close mycursor;end;end;Sql编程编程参数化游标（参数化游标（oracleoracle才有）才有）声明声明 cursor c_KMXX(cKMXZ char)iscursor c_KMXX(cKMXZ char)is select kmdm,kmmc from gl_kmxx select kmdm,kmmc from gl_kmxx where kmxz=cKMXZ;where kmxz=cKMXZ;打开打开 open c_KMXX(1);open c_KMXX(1);Sql编程编程事务控制事务控制事务的概念事务的概念 事务可以看作是由对数据库的若干操作组成的一个单事务可以看作是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求据满足一致性的要求。事务的一个典型例子例子是银行中的转帐操作，帐户A把一定数量的款项转到帐户B上，这个操作包括两个步骤，一个是从帐户A上把存款减去一定数量，二是在帐户B上把存款加上相同的数量。这两个步骤显然要么都完成，要么都取消，否则银行就会受损失。显然，这个转帐操作中的两个步骤就构成一个事务。Sql编程编程语法语法 oracleoraclecommit;commit;rollback;rollback;savepoint savepoint 点名点名;rollback to savepoint rollback to savepoint 点名点名;sqlsqlBegin Transaction Commit Transaction|Begin Transaction Commit Transaction|Rollback TransactionRollback TransactionSql编程编程事务设置及类型的区别事务设置及类型的区别sqlserversqlserver隐式事务（隐式事务（Set Implicit_Transactions OnSet Implicit_Transactions On命命令设置的事务类型令设置的事务类型）显式事务显式事务自动提交事务自动提交事务（默认）（默认）Oracle中没有SQL Server的这些事务类型，缺省情况下任何一个DML语句都会开始一个事务，直到用户发出Commit或Rollback操作，这个事务才会结束，这与SQL Server的隐式事务模式相似。(锁问题)Sql编程编程DDLDDL语句对事务的影响语句对事务的影响在在OracleOracle中，执行中，执行DDLDDL语句语句(如如Create TableCreate Table、Create ViewCreate View等等)时时，会在执行之前自动发出一个，会在执行之前自动发出一个CommitCommit命令，并在随后发出一个命令，并在随后发出一个CommitCommit或者或者RollbackRollback命令，也就是说，命令，也就是说，DDLDDL会象如下伪码一样执行会象如下伪码一样执行Commit;Commit;DDL_Statement;DDL_Statement;If(Error)thenIf(Error)thenRollback;Rollback;ElseElseCommit;Commit;End if;End if;Sql编程编程例子例子Insert into some_table values(Before);Create table T(x int);Insert into some_table values(After);Rollback;在SQL Server中，隐式事务时DDL语句对事务的影响与其他DML语句相同，也就是说，在DML语句发出之前或之后，都不会自动发出Commit命令。用户断开数据库连接对事务的影响Ora是commit，sql是rollback隐式有影响Sql编程编程异常处理异常处理oracleoracle声明声明系统预定义异常如：被0除，约束破坏，数据类型转换失败等用户自定义异常declare epError Exception;Sqlserver2005Sqlserver2005 try.try.cache.cache.方式方式Sql编程编程异常的抛出异常的抛出用户自定义的异常由raise语句触发如：raise epError;系统预定义的异常自动触发 处理处理Exception when 异常名1 then 处理语句序列;when 异常名2 then 处理语句序列;when others then 处理语句序列;Sql编程编程例子例子declaredeclare iCount int;iCount int;epNoRecord exception;epNoRecord exception;beginbegin select count(select count(*)into iCount from zb_zffs where zffsmc=)into iCount from zb_zffs where zffsmc=支付支付;if iCount=0 then if iCount=0 then raise epNoRecord;raise epNoRecord;end if;end if;dbms_output.put_line(dbms_output.put_line(成功成功););exception exception when epNoRecord then when epNoRecord then rollback;rollback;dbms_output.put_line(dbms_output.put_line(没有相应记录没有相应记录););when others then when others then rollback;rollback;dbms_output.put_line(dbms_output.put_line(其他错误其他错误););end;end;Sql编程编程sqlsql处理异常处理异常方法方法ErrorError全局变量全局变量Update TableAUpdate TableASet FieldA=ABCSet FieldA=ABCWHERE TableKey=XYZWHERE TableKey=XYZIF Error 0IF Error 0BEGINBEGIN PRINT PRINT 执行失败执行失败 RETURNRETURNENDENDSql Server 2000每执行一条语句，都会设置Error，因此Error的值只能表明上一条语句的正确与否。这种处理方法在Sql Server 2005中同样有效Sql编程编程SQL SQL 2 2005005的错误的错误等级等级轻微错误：严重性级别为0-10中等错误：严重性级别为11-19 严重错误：严重性级别为20-25 1.轻微错误：严重性级别为0-10 =默认情况下不会给客户程序发送错误消息，继续工作。也就是说它无法被CATCH到2.中等错误：严重性级别为11-19=能够被CATCH到（不管是在T-SQL里面还是在客户程序里面）3.严重错误：严重性级别为20-25=SQL Server将强制把连接关掉。很显然这也不可能被CATCH到Sql编程编程综合应用综合应用行列转换行列转换程序块（凭证录入）程序块（凭证录入）Sql编程编程函数函数系统预置函数系统预置函数自定义函数自定义函数Sqlserver分类1、标量型函数（例）2、内联表值型函数3、多声明表值型函数Oracle不能返回table类型（解决）1、返回游标2、返回table类型的结果集3、以管道形式输出限制（返回类型、限制（返回类型、dmldml语句修改函数的局部表语句修改函数的局部表变量）变量）Sql编程编程存储过程存储过程结构介绍结构介绍创建修改创建修改调用调用调试调试Sql编程编程