Oracle题：创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

创建一个函数，以部门号为参数，返回该部门的平均工资。

createorreplacefunctionf_sxt6(v_deptnoempdeptno%type)returnempsal%typeis  

vr_salempsal%type;  

begin  

selectavg(sal)intovr_salfromempwheredeptno=v_deptno;  

returnvr_sal;  

end;  

执行  

selectf_sxt6(20)部门平均工资fromdual;  

扩展资料：

创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资。  

createorreplacefunctionf_sxt7(v_empnoempempno%type)returnempsal%typeis  

vr_salempsal%type;  

begin  

selectavg(sal)intovr_salfromempwheredeptno=(selectdeptnofromempwhereempno=v_empno);  

returnvr_sal;  

end;  

执行： 

select f_sxt7(7369)fromdual;  

SELECT

　　FROM dept_costs

　　WHERE dept_total > (SELECT dept_avg

　　FROM avg_cost)

　　ORDER BY department_name;

　　GROUP BY 扩展

　　带有ROLLUP和CUBE *** 作的GROUP BY子句

　　- 使用带有ROLLUP和CUBE *** 作的GROUP BY子句产生多种分组结果

　　- ROLLUP产生n + 1种分组结果，其是对GROUP BY子句的扩展

　　- CUBE产生2的n次方种分组结果，其是对GROUP BY子句的扩展

　　注：其中的n指的是group_by_expression的数目。

　　ROLLUP *** 作符 ：ROLLUP产生n + 1种分组结果，顺序是从右向左

　　SELECT [column,] group_function(column)

　　FROM table

　　[WHERE condition]

　　[GROUP BY [ROLLUP] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　CUBE *** 作符：CUBE会产生类似于笛卡尔集的分组结果

　　SELECT [column,] group_function(column)

　　FROM table

　　[WHERE condition]

　　[GROUP BY [CUBE] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　GROUPING函数：能够实现更加直观的分组结果显示提示

　　SELECT [column,] group_function(column) , [GROUPING(group_by_expression)]

　　FROM table

　　[WHERE condition]

　　[GROUP BY [ROLLUP] [CUBE] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　- GROUPING函数可以和CUBE或ROLLUP结合使用

　　- 使用GROUPING函数，可以找到哪些列在该行中参加了分组

　　- 使用GROUPING函数, 可以区分空值产生的原因

　　- GROUPING函数返回0 或1

　　GROUPING SETS：

　　- GROUPING SETS是对GROUP BY子句的进一步扩充

　　- 使用GROUPING SETS可以实现在同一个查询中定义多个分组集

　　- Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL *** 作将各分组结果结合起来

　　- Grouping set 的优点：

　　– 只进行一次分组即可

　　– 不必书写复杂的UNION语句

　　– GROUPING SETS中包含的分组项越多性能越好

　　以下例子实现了对department_id, job_id分组，对job_id, manager_id分组，最终形成两个分组：

　　SELECT department_id, job_id, manager_id, avg(salary)

　　FROM employees

　　GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));

　　复合列

　　- 复合列是被作为整体处理的一组列的集合

　　- 使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行 *** 作

　　- 在ROLLUP或CUBE中，复合列可以避免产生不必要的分组结果

　　例如：GROUP BY ROLLUP( department_id, (job_id, manager_id)); //小括号实现复合列

　　连接分组集

　　- 连接分组集可以产生有用的对分组项的结合

　　- 将各分组集、ROLLUP 和CUBE用逗号连接，Oracle自动在GROUP BY子句中将各分组集进行连接

　　- 连接的结果是对各分组生成笛卡尔集

　　例如：GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d);

　　例如：GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);

①登录用户后，执行下面语句查看当前用户有哪些存储过程：

select  name from user_source where  type='PROCEDURE';

或者：select object_name from user_procedures;

或者：select object_name from user_objects where object_type='PROCEDURE' ;

②找到你所要查询的存储过程名字后，执行下面 *** 作，可以看到存错过程内容：

select  text from user_source where name ='xxxx';--(xxxx代表所要查询的存储过程名字)

一 基本概念

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的 适应高吞吐量的数据库解决方案。

二 特点

1、完整的数据管理功能:

1)数据的大量性

2)数据的保存的持久性

3)数据的共享性

4)数据的可靠性

2、完备关系的产品:

1)信息准则---关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示;

2)保证访问的准则

3)视图更新准则---只要形成视图的表中的数据变化了，相应的视图中的数据同时变化

4)数据物理性和逻辑性独立准则

3、分布式处理功能:

ORACLE数据库自第5版起就提供了分布式处理能力，到第7版就有比较完善的分布式数据库功能了，一个ORACLE分布式数据库由oraclerdbms、sqlNet、SQLCONNECT和其他非ORACLE的关系型产品构成。

4、用ORACLE能轻松的实现数据仓库的 *** 作。

这是一个技术发展的趋势，不在这里讨论。

根据意思错误的里面decode里应该是101 115 125 103这4个值

decode函数返回的值不固定,是根据你设置的值

这里decode(category,'COMPUTER',01,'FITNESS',015,'SELF HELP',025,003)返回的是数值

改为decode(category,'COMPUTER','01','FITNESS','015','SELF HELP','025','003')则返回字符型

　许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle SQL Server DB )的应用程序的重要性 它可以让客户们选择自己习惯的平台 一般来说 软件开发人员都能够识别出他们的负责数据库维护的客户 和必须使用现有平台和个性化的客户

　关于Oracle和SQL Server之间的区别 已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别 在这篇文章里面 我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别 并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法 与此同时 我不会再论讨对于应用程序来说 两个平台之间显而易见的区别 例如表的分割和索引

　定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库 想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的 这是一家志愿者成员的组织(用私人基金运转) 他们在有关设备和程序等广泛领域内开发了国家承认的标准 在数据库领域 ANSI定义了编写SQL命令的标准 假设命令可以运行在任何的数据库上 而不需要更改命令的语法

　ODBC是开放数据库连接(ODBC)接口 微软定义的 它可以让应用程序访问数据库管理系统(DBMS)中的数据 使用SQL作为访问数据的标准 ODBC允许最大的互联性 这意味着一个单个的应用程序可以访问不同的数据库管理系统 然后 应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上 OLE DB是ODBC的继承者 是一组允许例如基于VB C++ Access等任何可以连接到类似SQL Server Oracle DB MySQL等后台的 前台 的软件组件 在许多种情况下 OLE DB组件都比原有的ODBC提供了好得多的性能

　JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库 SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间 进行不依赖于数据库的连接的行业标准 JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口 真实世界中的通用接口不幸的是 并不是所有数据库级别的命令都是ANSI 每个数据库平台都有自己的扩展功能 对于ANSI或者通用接口 一般来说都代表着几本功能 因此也可能意味着丧失了性能方面的竞争力 对于小型数据库和小型应用程序来说 要维护对数据库的通用访问是简单的 但是当数据库和/或应用程序变得越来越大 越来越复杂 你就不得不向代码中添加功能

　SQL Server和Oracle的常用函数对比

　 数学函数

　 绝对值

　S:select abs( ) value

　O:select abs( ) value from dual

　 取整(大) S:select ceiling( ) value O:select ceil( ) value from dual

　 取整（小）

　S:select floor( ) value O:select floor( ) value from dual

　 取整（截取）

　S:select cast( as int) value O:select trunc( ) value from dual

　 四舍五入S:select round( ) value O:select round( ) value from dual

　 e为底的幂S:select Exp( ) value O:select Exp( ) value from dual

　 取e为底的对数S:select log( ) value O:select ln( ) value from dual;

　 取 为底对数

　S:select log ( ) value

　O:select log( ) value from dual;

　 取平方

　S:select SQUARE( ) value

　O:select power( ) value from dual

　 取平方根

　S:select SQRT( ) value

　O:select SQRT( ) value from dual

　 求任意数为底的幂S:select power( ) value O:select power( ) value from dual

　 取随机数

　S:select rand() value

　O:select sys dbms_random value( ) value from dual;

　 取符号

　S:select sign( ) value

　O:select sign( ) value from dual

　 三角函数相关

　 圆周率S:SELECT PI() value

　O:不知道

　 sin cos tan 参数都以弧度为单位

　例如 select sin(PI()/ ) value得到 （SQLServer）

　 Asin Acos Atan Atan 返回弧度

　 弧度角度互换(SQLServer Oracle不知道)

　DEGREES 弧度 〉角度

　RADIANS 角度 〉弧度

　 数值间比较

　 求集合最大值

　S:select max(value) value from

　(select value

　union

　select value

　union

　select value

　union

　select value)a

　O:select greatest( ) value from dual

　 求集合最小值

　S:select min(value) value from

　(select value

　union

　select value

　union

　select value

　union

　select value)a

　O:select least( ) value from dual

　 如何处理null值(F 中的null以 代替) S:select F IsNull(F ) value from Tbl O:select F nvl(F ) value from Tbl

　 字符串函数

　 求字符序号

　S:select ascii( a ) value

　O:select ascii( a ) value from dual

　 从序号求字符

　S:select char( ) value

　O:select chr( ) value from dual

　 连接

　S:select + + value

　O:select CONCAT( )|| value from dual

　 子串位置 返回 S:select CHARINDEX( s sdsq ) value O:select INSTR( sdsq s ) value from dual

　 模糊子串的位置 返回 参数去掉中间%则返回 // 本文转自 C++Builder 研究 i= &d=dwn rn S:select patindex( %d%q% sdsfasdqe ) value O:oracle没发现 但是instr可以通过第四个参数控制出现次数select INSTR( sdsfasdqe sd ) value from dual 返回

　 求子串S:select substring( abcd ) value O:select substr( abcd ) value from dual

　 子串代替 返回aijklmnef S:SELECT STUFF( abcdef ijklmn ) value O:SELECT Replace( abcdef bcd ijklmn ) value from dual

　 子串全部替换

　S:Replace

　O:select Translate( fasdbfasegas fa 我 ) value from dual

　 长度S:len datalength

　O:length

　 大小写转换 lower upper

　 单词首字母大写

　S:没发现

　O:select INITCAP( abcd dsaf df ) value from dual

　 左补空格（LPAD的第一个参数为空格则同space函数）

　S:select space( )+ abcd value

　O:select LPAD( abcd ) value from dual

　 右补空格（RPAD的第一个参数为空格则同space函数）

　S:select abcd +space( ) value

　O:select RPAD( abcd ) value from dual

　 删除空格S:ltrim rtrim O:ltrim rtrim trim

　 重复字符串S:select REPLICATE( abcd ) value

　O:没发现

　 发音相似性比较(这两个单词返回值一样 发音相同) S:SELECT SOUNDEX ( Smith ) SOUNDEX ( Smythe ) O:SELECT SOUNDEX ( Smith ) SOUNDEX ( Smythe ) from dual SQLServer中用SELECT DIFFERENCE( Smithers Smythers ) 比较soundex的差返回 为同音 最高

　 日期函数

　 系统时间

　S:select getdate() value

　O:select sysdate value from dual

　 前后几日

　直接与整数相加减

　 求日期S:select convert(char( ) getdate() ) value

　O:select trunc(sysdate) value from dual

　select to_char(sysdate yyyy mm dd ) value from dual

　 求时间S:select convert(char( ) getdate() ) value O:select to_char(sysdate hh :mm:ss ) value from dual

　 取日期时间的其他部分

　S:DATEPART和DATENAME函数（第一个参数决定）

　O:to_char函数第二个参数决定

　参数 下表需要补充

　year yy yyyy quarter qq q (季度) month mm m (m O无效) dayofyear dy y (O表星期) day dd d (d O无效) week wk ww (wk O无效)

　weekday dw (O不清楚)

　Hour hh hh hh (hh hh S无效) minute mi n (n O无效) second ss s (s O无效)

　millisecond ms (O无效)

　

　 当月最后一天

　S:不知道

　O:select LAST_DAY(sysdate) value from dual

　 本星期的某一天（比如星期日）

　S:不知道

　O:SELECT Next_day(sysdate ) vaule FROM DUAL;

　 字符串转时间

　S:可以直接转或者select cast( as datetime) value

　O:SELECT To_date( : : yyyy mm dd hh mi ss ) vaule FROM DUAL;

　 求两日期某一部分的差（比如秒）

　S:select datediff(ss getdate() getdate()+ ) value O:直接用两个日期相减（比如d d = ）

　SELECT (d d ) vaule FROM DUAL;

　 根据差值求新的日期（比如分钟）

　S:select dateadd(mi getdate()) value

　O:SELECT sysdate+ / / vaule FROM DUAL;

　 求不同时区时间

　S:不知道

　O:SELECT New_time(sysdate ydt gmt ) vaule FROM DUAL;

　 时区参数 北京在东 区应该是Ydt

　AST ADT大西洋标准时间

　BST BDT白令海标准时间

　CST CDT中部标准时间

　EST EDT东部标准时间

　GMT格林尼治标准时间

　HST HDT阿拉斯加—夏威夷标准时间

　MST MDT山区标准时间

　NST纽芬兰标准时间

　PST PDT太平洋标准时间

　YST YDT YUKON标准时间

　Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:

　函数Oracle Microsoft SQL Server

　把字符转换为ASCII ASCII ASCII

　字串连接CONCAT (expressiοn + expressiοn)

　把ASCII转换为字符CHR CHAR

　返回字符串中的开始字符（左起）INSTR CHARINDEX

　把字符转换为小写LOWER LOWER

　把字符转换为大写UPPER UPPER

　填充字符串的左边LPAD N/A

　清除开始的空白LTRIM LTRIM

　清除尾部的空白RTRIM RTRIM

　字符串中的起始模式（pattern）INSTR PATINDEX

　多次重复字符串RPAD REPLICATE

　字符串的语音表示SOUNDEX SOUNDEX

　重复空格的字串RPAD SPACE

　从数字数据转换为字符数据TO_CHAR STR

　子串SUBSTR SUBSTRING

　替换字符REPLACE STUFF

　将字串中的每个词首字母大写INITCAP N/A

　翻译字符串TRANSLATE N/A

　字符串长度LENGTH DATELENGTH or LEN

　列表中最大的字符串GREATEST N/A

　列表中最小的字符串LEAST N/A

lishixinzhi/Article/program/Oracle/201311/16561