登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

PL/SQL循序渐进全面学习教程(2)_Net things Ruyan  

2009-05-11 12:09:09|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
PL/SQL循序渐进全面学习教程(2)
2009-03-27 10:53

四、ORACLE日期格式和日期型函数:

1、默认格式为DD-MON-YY.

2、SYSDATE是一个求系统时间的函数

3、DUAL['dju:el] 是一个伪表,有人称之为空表,但不确切。

SQL> SELECT SYSDATE FROM SYS.DUAL;

4、日期中应用的算术运算符

例:

SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS FROM s_emp WHERE dept_id = 43;

DATE+ NUMBER = DATE

DATE-DATE= NUMBER OF DAYS

DATE + (NUMBER/24) = 加1小时

5、函数:

MONTHS_BETWEEN(date1, date2) 月份间隔,可正,可负,也可是小数

ADD_MONTHS(date,n) 加上N个月,这是一个整数,但可以为负

NEXT_DAY(date,‘char’) 如:NEXT_DAY (restock_date,’FRIDAY’),从此日起下个周五。

ROUND(date[,‘fmt’])

TRUNC(date[,‘fmt’])

解释下面的例子:

SQL> SELECT id, start_date, MONTHS_BETWEEN (SYSDATE,start_date) TENURE, ADD_MONTHS(start_date,6) REVIEW FROM s_emp WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;

我们看到:MONTHS_BETWEEN (SYSDATE,start_date)<48,说明至今工作未满一年的员工。

LAST_DAY (restock_date) 返回本月的最后一天

SQL> select round(sysdate,'MONTH') from dual

ROUND(SYSD

----------

01-11月-01

round(sysdate,'YEAR') = 01-1月 -02

ROUND 之后的值比基值大的最小符合值,大家可以用更改系统时间的方法测试,以15天为分界线,也是非常形象的四舍五入,而TRUNC恰好相反,是对现有的日期的截取。

五、转换函数:

1、TO_CHAR

使一个数字或日期转换为CHAR

2、TO_NUMBER

把字符转换为NUMBER

3、TO_DATE

字符转换为日期

这几个函数较为简单,但要多多实践,多看复杂的实例。

SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED FROM s_ord WHERE sales_rep_id = 11;

转换时,要注意正确的缺省格式:

SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL;//正确

SELECT TO_DATE('031092') CORRECT FROM DUAL;//不正确

SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL

输出 3月10日

SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL

输出 10月3日

4、实例:

select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;

TODAYS

-------------------------------

SIXTEENTH of 11月 2001 下午

大小写没有什么影响,引号中间的是不参与运算。

实例 :

SELECT ROUND(SALARY*1.25) FROM ONE_TABLE;

意义:涨25%工资后,去除小数位。在现实操作中,很有意义。

5、混合实例:

SQL> SELECT last_name, TO_CHAR(start_date, 'fmDD "of" Month YYYY') HIREDATE FROM s_emp WHERE start_date LIKE ’%91’;

LAST_NAME HIREDATE

------------ --------------------

Nagayama 17 of June 1991

Urguhart 18 of January 1991

Havel 27 of February 1991

这里要注意:fmDD和fmDDSPTH之间的区别。

SQL> SELECT id, total, date_ordered FROM s_ord WHERE date_ordered = TO_DATE(’September 7, 1992’,’Month dd, YYYY’);

六、独立的函数嵌套

SQL> SELECT CONCAT(UPPER(last_name), SUBSTR(title,3)) ”Vice Presidents” FROM s_emp WHERE title LIKE ’VP%’;

* 嵌套可以进行到任意深度,从内向外计算。

例:

SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS (date_ordered,6), 'FRIDAY'), 'fmDay, Month ddth, YYYY') "New 6 Month Review" FROM s_ord ORDER BY date_ordered;

SQL> SELECT last_name, NVL(TO_CHAR(manager_id),’No Manager’) FROM s_emp WHERE manager_id IS NULL;

对于例子,大家重要的理解,并多做测试,并注意英文版和中文版在日期上的区别。

有些教材上的例子,不要盲目的相信其结果,实践后才有发言权,希望大家能够在学习的过程中不要忽略了用,

多想一想为什么实例要如此设计,在何种情况下应用此实例来解决问题。这样,我们才真正掌握了知识。

课程三 从多个表中提取数据

本课重点:

1、SELECT FROM多个表,使用等连接或非等连接

2、使用外连接OUTER JOIN

3、使用自连接

注意:以下实例中标点均为英文半角

一、连接的概念:

是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。

连接类型:等连接、不等连接、外连接、自连接

二、Cartesian product:

指的是当JOIN条件被省略或无效时,所有表的行(交叉)都被SELECT出来的现象。

Cartesian product可以产生大量的记录,除非是你有意如此,否则应该加上某种条件限制。

SQL> SELECT name, last_name FROM s_dept, s_emp;

300 rows selected. 其中一个表12行,一个表25行。

三、简单连接查询:

SELECT table.column, table.column... FROM table1, table2 WHERE table1.column1 = table2.column2;

如:

SQL> SELECT s_emp.last_name, s_emp.dept_id, s_dept.name FROM s_emp, s_dept WHERE s_emp.dept_id = s_dept.id;

注意:表前缀的重要性:

SQL> SELECT s_dept.id ”Department ID”, s_region.id ”Region ID”, s_region.name ”Region Name” FROM s_dept, s_region WHERE s_dept.region_id = s_region.id;

在WHERE段中,如果没有前缀,两个表中都有ID字段,就显得的模棱两可,AMBIGUOUS。

这在实际中应该尽量避免。

WHERE字段中,还可以有其他的连接条件,如在上例中,加上:

INITCAP(s_dept.last_name) = ’Menchu’;

再如:

WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND s_emp.commission_pct > 0;

四、表别名ALIAS:

1、使用别名进行多表查询 。

2、仅在这个查询中生效,一旦用了表别名,就不能再用表的原有的名字进行连接。

实例:

SQL> SELECT c.name ”Customer Name”, c.region_id ”Region ID”, r.name ”Region Name” FROM s_customer c, s_region r WHERE c.region_id = r.id;

别名最多可以30个字符,但当然越少越好。最好也能容易识别。

五、非等连接

非等连接一般用在没有明确的等量关系的两个表;

最简单的说:非等连接就是在连接中没有“=”出现的连接。

SQL> SELECT e.ename, e.job, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

说明:Create a non-equijoin to evaluate an employee’s salary grade. The salary 必须在另一个表中最高和最低之间。

其他操作符<= >= 也可以实现,但是BETWEEN是非常简单实用的。

BETWEEN ....AND是指闭区间的,这点要注意 ,请大家测试。

六、外连接

语法结构:

SELECT table.column, table.column FROM table1, table2 WHERE table1.column = table2.column(+);

实例:

SQL> SELECT e.last_name, e.id, c.name FROM s_emp e, s_customer c WHERE e.id (+) = c.sales_rep_id ORDER BY e.id;

显示……,即使有的客户没有销售代表。

* 可以理解为有+号的一边出现了NULL,也可以做为合法的条件。

外连接的限制:

1、外连接符只能出现在信息缺少的那边。

2、在条件中,不能用IN或者OR做连接符。

七、自连接

同一个表中使用连接符进行查询;

FROM的后面用同一个表的两个别名。

实例:

SQL> SELECT worker.last_name||’ works for ’|| manager.last_name FROM s_emp worker, s_emp manager WHERE worker.manager_id = manager.id;

意味着:一个员工的经理ID匹配了经理的员工号,但这个像绕口令的连接方式并不常用。

以后我们会见到一种子查询:

select last_name from s_emp where salary=(select max(salary) from s_emp)

也可以看作是一种变向的自连接。

课程四 组函数

本课重点:

1、了解可用的组函数

2、说明每个组函数的使用方法

3、使用GROUP BY

4、通过HAVING来限制返回组

注意:以下实例中标点均为英文半角

一、概念:

组函数是指按每组返回结果的函数。

组函数可以出现在SELECT和HAVING 字段中。

GROUP BY把SELECT 的结果集分成几个小组。

HAVING 来限制返回组,对RESULT SET而言。

二、组函数:(#号的函数不做重点)

1、AVG

2、COUNT

3、MAX

4、MIN

5、STDDEV #

6、SUM

7、VARIANCE #

语法:

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

实例1:一个混合实例,说明所有问题:

SQL> SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM s_emp WHERE UPPER(title) LIKE ’SALES%’;

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)

----------- ----------- ----------- -----------

1476 1525 1400 7380

说明:很多函数,我们在讲函数的已经向大家介绍过,但在此为何叫分组函数呢,主要是因为它们可以与GROUP BY来形成对不同组的计算,相当于在很多值中进行挑选。

* MIN MAX函数可以接任何数据类型。

如果是MIN(last_name), MAX(last_name),返回的是什么呢?

千万记住,不是指LAST_NAME的长度,而是指在FIRST字母的前后顺序,第一个相同,然后比较第二个,如:xdopt > cssingkdkdk > adopt > acccc

实例2:

SQL> SELECT COUNT(commission_pct) FROM s_emp WHERE dept_id = 31;

返回所有非空行个数

三、GROUP BY的应用:

先看一个简单实例:

SQL> SELECT credit_rating, COUNT(*) ”# Cust” FROM s_customer GROUP BY credit_rating;

注意这里别名的应用,复习一下从前的课程,加了引号后,就可以用特殊字符,但也仅有三个:#$_,什么对象的名字都如此。当然空格也是可以的。

复杂实例:

SQL> SELECT title, SUM(salary) PAYROLL FROM s_emp WHERE title NOT LIKE ’VP%’ GROUP BY title ORDER BY SUM(salary);

这里要注意一下几个CLAUSE的先后次序。

WHERE在这里主要是做参与分组的记录的限制。

* 另外,如果要选取出来一个不加组函数的列,如上面的TITLE,就要把这个列GROUP BY!否则要出错的!信息为:ERROR at line 1:

ORA-00937: not a single-group group function

理论很简单,如果不GROUP BY TITLE,显示哪一个呢?这个在试题中经常出现。

结论:不加分组函数修饰的列必定要出现在GROUP BY 里。

错误实例:

SQL> SELECT dept_id, AVG(salary) FROM s_emp WHERE AVG(salary) > 2000 GROUP BY dept_id;

WHERE AVG(salary) > 2000

* ERROR at line 3:

ORA-00934: group function is not allowed here

应在GROUP BY 后面加上HAVING AVG(salary) > 2000;

因为是用来限制组的返回。

多级分组实例:

SQL> SELECT dept_id, title, COUNT(*) FROM s_emp GROUP BY dept_id, title;

就是先按照DEPT_ID分组,当DEPT_ID相同的时候,再按TITLE分组,而COUNT(*)以合成的组计数。

顺序对结果有决定性的影响。

总结:本课我们主要学习了分组函数的使用及如何进行分组查询,我们可以想像一下,SQL SERVER中有COMPUTE BY,来进行分组总数的计算,但在ORACLE中是没有的。大家可以建立一个有多个列,多个重复值的表,然后进行各种分组的演示,用得多了,自然明了。

课程五 子查询

本课重点:

1、在条件未知的情况下采用嵌套子查询

2、用子查询做数据处理

3、子查询排序

注意:以下实例中标点均为英文半角

一、概述:

子查询是一种SELECT句式中的高级特性,就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。

子查询可以用在WHERE子句,HAING子句,SELECT或DELETE语句中的FROM 子句。

注意:

1、子查询必须在一对圆括号里。

2、比较符号:>, =, 或者 IN.

3、子查询必须出现在操作符的右边

4、子查询不能出现在ORDER BY里

二、子查询的执行过程:

NESTED QUERY

SQL> SELECT dept_id FROM s_emp WHERE UPPER(last_name)='BIRI';

MAIN QUERY

SQL> SELECT last_name, title FROM s_emp WHERE dept_id =

这里 ,每个查询只运行一次。当然,子查询要首先被执行,大家设想一下,如果子查询中有一个以上的人的LASTNAME为BIRI,会如何?-----会出错,因为不能用=来连接。

ORA-1427: single-row subquery returns more than one row

以上的查询也被称为单行子查询。

DELECT子查询实例:

delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=( select pro_name from new_product where pro_addr in ('bj','sh'))

三、子查询中的GROUP 函数的应用

实例 1:

SQL> SELECT last_name, title, salary FROM s_emp WHERE salary < (SELECT AVG(salary) FROM s_emp);

实例2:

选择出工资最高的员工的家庭住址:

select emp_addr from employees where salary = (select max(salary) from employees);

这是一个简单实用的例子,可以衍生出很多情况,在实际应用经常出现,请大家多多思考。

实例3:

SQL> SELECT dept_id, AVG(salary) FROM s_emp GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM s_emp WHERE dept_id = 32);

子查询被多次执行,因为它出现在HAVING 子句中。

SQL> SELECT title, AVG(salary) FROM s_emp GROUP BY title HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM s_emp GROUP BY title);

对子查询,我们了解这么多在理论上已经覆盖了所有的知识点,对于UPDATE 和DELETE的子查询,不作为重点,但也要练习掌握。今天到这,谢谢大家。

课程六 运行时应用变量

本课重点:

1、创建一个SELECT语句,提示USER在运行时先对变量赋值。

2、自动定义一系列变量,在SELECT运行时进行提取。

3、在SQL PLUS中用ACCEPT定义变量

注意:以下实例中标点均为英文半角

一、概述:

变量可以在运行时应用,变量可以出现在WHERE 字段,文本串,列名,表名等。

1、我们这里的运行时,指的是在SQL PLUS中运行。

2、ACCEPT :读取用户输入的值并赋值给变量

3、DEFINE:创建并赋值给一个变量

4、在做REPORT时经常使用,比如对某个部门的销售信息进行统计,部门名称可以以变量代替。

SQL PLUS不支持对输入数据的有效性检查,因此提示要简单且不模棱两可。

二、应用实例:

1、

SQL> SELECT id, last_name, salary FROM s_emp WHERE dept_id = &department_number;

2、可以在赋值前后进行比较:

SET VERIFY ON

.....

1* select * from emp where lastname='&last_name'

输入 last_name 的值: adopt

原值1: select * from emp where lastname='&last_name'

新值1: select * from emp where lastname='adopt'

——如果在原语句中没有单引号,那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。

SET VERIFY OFF 之后,原值和新值这两句消失。这在ORACLE8I中是默认为ON。

3、子句为变量:WHERE &condition; 要注意引号

三、DEFINE和ACCEPT的应用:

1、SET ECHO OFF //使内容不 显示在用户界面

ACCEPT p_dname PROMPT ’Provide the department name: ’ SELECT d.name, r.id, r.name ”REGION NAME” FROM s_dept d, s_region r WHERE d.region_id = r.id AND UPPER(d.name) LIKE UPPER(’%&p_dname%’) / SET ECHO ON 存为文件:l7prompt.SQL SQL> START l7prompt Provide the department name: sales

2、

SQL> DEFINE dname = sales SQL> DEFINE dname DEFINE dname = ”sales” (CHAR) SQL> SELECT name FROM s_dept WHERE lower(name) = ’&dname’;

可以正常执行了。

SQL> DEFINE dname 主要是显示当前的变量是否赋值,值是什么。当然,我们可以用UNDEFINEGO 来使变量恢复初始,不然它会一直保持下去。

3、如果变量在SQL SCRIPT文件中确定 :可以SQL> START l7param President 来赋值。

总结:本课主要针对较古老的SQLPLUS方法,在REPORT和结果集生成方面使用变量,达到方便操作,动态修改的目的。

课程七 其他数据库对象

SEQUENCE创建实例:

SQL> CREATE SEQUENCE s_dept_id

INCREMENT BY 1

START WITH 51

MAXVALUE 9999999

NOCACHE

NOCYCLE;

Sequence created.

1、NEXTVAL和CURRVAL的用法

只有在INSERT 中,才可以作为子查询出现。

以下几个方面不可用子查询:

SELECT 子句OF A VIEW

有DISTINCT的出现的SELECT。

有GROUP BY,HAVING,ORDER BY的SELECT 子句。

SELECT 或DELETE,UPDATE 中的子查询。

DEFAULT选项中不能用。

2、编辑SEQUENCE

只有OWNER或有ALTER权限的用户才能修改SEQUENCE

未来的NUMBER受修改的影响。

不能修改START WITH,如果变,则要RE-CREATE。

修改会受到某些有效性检验的限制,如MAXVALUE

3、删除:

DROP SEQUENCE sequence;




©2009 Baidu



引文来源  PL/SQL循序渐进全面学习教程(2)_Net things Ruyan
  评论这张
 
阅读(382)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018