Oracle SQL&PL/SQL笔记

qmya00

　　一.单行函数

• 　　字符函数
  　　(1).LOWER() 字符小写
  　　(2).UPPER() 字符大写
  　　(3).INITCAP() 首字符大写
  　　(4).CONCAT() 字符串连接
  　　(5).SUBSTR() 字符串截取
  　　(6).LENGTH() 返回字符串长度
  　　(7).INSTR()  返回字符串的位置
  　　(8).LPAD()   左对齐
  　　(9).RPAD()   右对齐
  　　(10).TRIM()  去除字符串首尾的字符
  　　(11).REPLACE() 替换字符串中所有的字符
  

　　2.数字函数
　　(1).round() 四舍五入
　　(2).trunc() 截取
　　(3).mod()   取余
　　3.日期函数
　　(1).sysdate 返回当前日期和时间
　　(2).months_between() 两个日期相差的月数
　　(3).add_months() 向指定日期中加上若干月数
　　(4).next_day() 指定日期的下一个日期
　　(5).last_day() 本月的最后一天
　　(6).round() 日期四舍五入
　　(7).trunc() 日期截断
　　4.转换函数
　　(1).to_char 转换为字符
　　(2).to_date 转换为日期
　　(3).to_number 转换为数值
　　5.通用函数
　　(1).nvl() 将控制转换为一个已知的值,可以使用的数据类型有日期,字符,数字
　　(2).nvl2()
　　(3).nullif()
　　(4).coalesce()
　　二.多行函数(分组函数)
　　分组函数作用于一组数据，并对一组数据返回一个值
　　分组函数忽略空值,NVL函数使分组函数无法忽略空值,例如avg(nvl(expr1,expr2))
　　(1).avg()  求平均值
　　(2).count() 求记录总数
　　a.count(expr) 返回expr不为空的记录总数
　　b.count(*) 返回表中记录总数
　　c.count(distinct expr) 返回expr非空且不重复的记录总数
　　(3).max()    求最大值
　　(4).min()      求最小值
　　(5).stddev()
　　(6).sum()    求和值
　　可以使用group by子句将表中的数据分成若干组,在select列表中所有未包含在组函数中的列都应该包含在group by 子句中(所有包含于select列表中而未包含于组函数中的列都应该包含在group by子句中),包含在group by 子句中的列不必包含在select 列表中
　　不能在where子句中使用分组函数,可以在having子句中使用分组函数
　　group by. .  having不分前后顺序
　　三.条件表达式
　　(1).case ...when ...then
　　when ... then
　　else ...
　　end
　　(2).decode(expression,search1,result1[,search2,result2...])
　　四.多表查询

• 　　笛卡尔积  查询中省略连接条件,表行数相乘为返回结果
  
• 　　多表查询有相同列时,列名之前需加入表前缀
  
• 　　内连接
  

　　a.等值连接
　　b.连接N个表至少需要N-1个连接条件
　　c.非等值连接
　　4.外连接
　　a.使用外连接可以查询不满足连接条件的数据
　　b.外连接的符号是(+), 理解"(+)"的位置:以左外连接为例，因为左表需要返回更多的记录，    右表就需要"加上"更多的记录,所以在右表的连接条件上"(+)"
　　两边都加上"(+)"符号,会发生语法错误
　　这种语法为oracle所独有,不能在其他数据库中使用，其他数据库需使用SQL 1999语法标准
　　c.左外连接
　　d.右外连接
　　e.全外连接
　　5.自连接
　　五.子查询

• 　　子查询要包含在括号内
  
• 　　讲子查询放在比较条件的右侧
  
• 　　单行 操作符对应单行子查询,多行操作符对应多行子查询
  
• 　　单行子查询操作符:
  
  
  
  • 　　>
    
  • 　　<
    
  • 　　>=
    
  • 　　 parameter [,…]
    　　c.组合传递 即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法其后所有的参数都必须使用名称表示法
    　　
    　　create or replace function name(....argument...type )
    　　return type
    　　is|as
    　　begin
    　　.......
    　　return variable;
    　　end;
    　　使用过程与函数具有如下优点：
    　　1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。
    　　2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。
    　　3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。
    　　4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
    　　5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。
    　　6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。
    　　过程与函数的相同功能有：
    　　1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
    　　2、 输入参数都可以接受默认值，都可以传值或传引导。
    　　3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
    　　4、 都有声明部分、执行部分和异常处理部分。
    　　5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
    　　使用过程与函数的原则：
    　　1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。
    　　2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。
    　　3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。
    　　.函数执行方法:
    　　1.作为参数传递给子程序
    　　execute dbms_output.put_line(function(parameter));
    　　2.使用局部变量赋值
    　　declare var type;
    　　begin
    　　var:=function(parameter);
    　　end;
    　　3.在SQL语句中作为表达式使用
    　　4.使用本地变量
    　　variable var type;
    　　execute :var:=function(parameter);
    　　.在SQL语句中调用函数限制条件
    　　1.参数只能使用位置传递法
    　　2.必须有执行函数的权限
    　　3.只接收IN类型的参数,同时返回SQL的数据类型而非PL/SQL类型(复合记录类型，表类型，参数类型游标这种)
    　　4.函数只能有一个返回值(return type)
    　　十三.程序包
    　　1.程序包是对相关过程,函数,变量,游标和异常等对象的封装
    　　2.程序包有规范和主体两部分组成
    　　十四.触发器
    　　1.DML触发器(insert,update,delelte):
    　　a.语句触发器
    　　raise_application_error(argument1,argument2) oracle提供的抛错误过程,接受两个参数
    　　第一个参数-20000~-20999之间
    　　第二个参数为提示错误信息
    　　使用条件谓词
    　　inserting
    　　updating
    　　deleting
    　　b.行触发器
    　　2.系统事件触发器(与系统相关的触发器比如用户登录，退出启动，关闭数据库)
    　　3.DDL触发器