plsql使用.docx

plsql使用.docx

《plsql使用.docx》由会员分享，可在线阅读，更多相关《plsql使用.docx（36页珍藏版）》请在冰豆网上搜索。

plsql使用

本课重点：

1、写SELECT语句进行数据库查询

2、进行数学运算

3、处理空值

4、使用别名ALIASES

5、连接列

6、在SQLPLUS中编辑缓冲，修改SQLSCRIPTS

7、ORDERBY进行排序输出。

8、使用WHERE字段。

一、写SQL命令：

不区分大小写。

SQL语句用数字分行，在SQLPLUS中被称为缓冲区。

最后以；或/结束语句。

也可以用RUN来执行语句

二、举例

例1：

SQL>SELECTdept_id,last_name,manager_id

FROMs_emp;

例2：

SQL>SELECTlast_name,salary*12,commission_pct

FROMs_emp;

对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。

SQL>SELECTlast_name,salary,12*（salary+100）

FROMs_emp;

三、列的别名ALIASES：

计算的时候特别有用；

紧跟着列名，或在列名与别名之间加“AS”；

如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。

例：

SQL>SELECTlast_name,salary,

12*（salary+100）"AnnualSalary"

FROMs_emp;

四、连接符号：

||

连接不同的列或连接字符串

使结果成为一个有意义的短语：

SQL>SELECTfirst_name||’’||last_name

||’,’||title"Employees"

FROMs_emp;

五、管理NULL值：

SQL>SELECTlast_name,title,

salary*NVL（commission_pct,0）/100COMM

FROMs_emp;

此函数使NULL转化为有意义的一个值，相当于替换NULL。

六、SQLPLUS的基本内容，请参考《SQLPLUS简单实用精髓篇》

七、ORDERBY操作：

与其他SQL92标准数据库相似，排序如：

SELECTexpr

FROMtable

[ORDERBY[ASC|DESC]];

从Oracle7release7.0.16开始，ORDERBY可以用别名。

另：

通过位置判断排序：

SQL>SELECTlast_name,salary*12

FROMs_emp

ORDERBY2;

这样就避免了再写一次很长的表达式。

另：

多列排序：

SQL>SELECTlastname,dept_id,salary

FROMs_emp

ORDERBYdept_id,salaryDESC;

八、限制选取行：

SELECTexpr

FROMtable

[WHEREcondition（s）]

[ORDERBYexpr];

例1：

SQL>SELECTfirst_name,last_name,start_date

FROMs_emp

WHEREstart_dateBETWEEN’09-may-91’

AND’17-jun-91’;

例2：

SQL>SELECTlast_name

FROMs_emp

WHERElast_nameLIKE’_a%’;//显示所有第二个字母为a的last_name

例3：

如果有列为NULL

SQL>SELECTid,name,credit_rating

FROMs_customer

WHEREsales_rep_idISNULL;

优先级：

OrderEvaluatedOperator

1Allcomparisonoperators（=,<>,>,>=,<,<=,IN,LIKE,ISNULL,BETWEEN）

2AND

3OR

总结：

我们今天主要学习了如何进行查询SELECT操作，具体的组合查询与子查询将在以后的课堂中学习，同时希望大家可以工作、学习中多多摸索，实践!

课程二PL/SQL查询行函数

本课重点：

1、掌握各种在PL/SQL中可用的ROW函数

2、使用这些函数的基本概念

3、SELECT语句中使用函数

4、使用转换函数

注意：

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

一、FUNCTION的作用：

进行数据计算，修改独立的数据，处理一组记录的输出，不同日期显示格式，进行数据类型转换

函数分为：

单独函数（ROW）和分组函数

注意：

可以嵌套、可以在SELECT,WHERE,和ORDERBY中出现。

语法：

function_name（column|expression,[arg1,arg2,...]）

二、字符型函数

1、LOWER转小写

2、UPPER转大写

3、INITCAP首字母大写

4、CONCAT连接字符，相当于||

5、SUBSTRSUBSTR（column|expression,m[,n]）

6、LENGTH返回字符串的长度

7、NVL转换空值

其中，1、2经常用来排杂，也就是排除插入值的大小写混用的干扰，如：

SQL>SELECTfirst_name,last_name

FROMs_emp

WHEREUPPER（last_name）=’PATEL’;

FIRST_NAMELAST_NAME

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

VikramPatel

RadhaPatel

三、数学运算函数

1、ROUND

四舍五入：

ROUND（45.923，2）=45.92

ROUND（45.923，0）=46

ROUND（45.923，-1）=50

2、TRUNC

截取函数

TRUNC（45.923，2）=45.92

TRUNC（45.923）=45

TRUNC（45.923，-1）=40

3、MOD余除

MOD（1600，300）

实例：

SQL>SELECTROUND（45.923,2）,ROUND（45.923,0）,

ROUND（45.923,-1）

FROMSYS.DUAL;

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

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

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

3、DUAL［'dju:

el]是一个伪表，有人称之为空表，但不确切。

SQL>SELECTSYSDATE

FROMSYS.DUAL;

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

例：

SQL>SELECTlast_name,（SYSDATE-start_date）/7WEEKS

FROMs_emp

WHEREdept_id=43;

DATE+NUMBER=DATE

DATE-DATE=NUMBEROFDAYS

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>SELECTid,start_date,

MONTHS_BETWEEN（SYSDATE,start_date）TENURE,

ADD_MONTHS（start_date,6）REVIEW

FROMs_emp

WHEREMONTHS_BETWEEN（SYSDATE,start_date）<48;

我们看到：

MONTHS_BETWEEN（SYSDATE,start_date）<48，说明至今工作未满一年的员工。

LAST_DAY（restock_date）返回本月的最后一天

SQL>selectround（sysdate,'MONTH'）fromdual

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>SELECTID,TO_CHAR（date_ordered,’MM/YY’）ORDERED

FROMs_ord

WHEREsales_rep_id=11;

转换时，要注意正确的缺省格式：

SELECTTO_DATE（'03-MAR-92'）CORRECTFROMDUAL；//正确

SELECTTO_DATE（'031092'）CORRECTFROMDUAL；//不正确

SELECTTO_DATE（'031095','MMDDYY'）ERRORRFROMDUAL

输出3月10日

SELECTTO_DATE（'031095','DDMMYY'）ERRORRFROMDUAL

输出10月3日

4、实例：

selectto_char（sysdate,'fmDDSPTH"of"MONTHYYYYAM'）TODAYSFROMDUAL;

TODAYS

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

SIXTEENTHof11月2001下午

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

实例：

SELECTROUND（SALARY*1.25）FROMONE_TABLE；

意义：

涨25%工资后，去除小数位。

在现实操作中，很有意义。

5、混合实例：

SQL>SELECTlast_name,TO_CHAR（start_date,

'fmDD"of"MonthYYYY'）HIREDATE

FROMs_emp

WHEREstart_dateLIKE’%91’;

LAST_NAMEHIREDATE

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

Nagayama17ofJune1991

Urguhart18ofJanuary1991

Havel27ofFebruary1991

这里要注意：

fmDD和fmDDSPTH之间的区别。

SQL>SELECTid,total,date_ordered

FROMs_ord

WHEREdate_ordered=

TO_DATE（’September7,1992’,’Monthdd,YYYY’）;

六、独立的函数嵌套

SQL>SELECTCONCAT（UPPER（last_name）,

SUBSTR（title,3））”VicePresidents”

FROMs_emp

WHEREtitleLIKE’VP%’;

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

例：

SQL>SELECTTO_CHAR（NEXT_DAY（ADD_MONTHS

（date_ordered,6）,'FRIDAY'）,

'fmDay,Monthddth,YYYY'）

"New6MonthReview"

FROMs_ord

ORDERBYdate_ordered;

SQL>SELECTlast_name,

NVL（TO_CHAR（manager_id）,’NoManager’）

FROMs_emp

WHEREmanager_idISNULL;

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

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

多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。

这样，我们才真正掌握了知识。

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

本课重点：

1、SELECTFROM多个表，使用等连接或非等连接

2、使用外连接OUTERJOIN

3、使用自连接

注意：

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

一、连接的概念：

是指一个从多个表中的数据进行的查询。

连接一般使用表的主键和外键。

连接类型：

等连接、不等连接、外连接、自连接

二、Cartesianproduct：

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

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

SQL>SELECTname,last_name

FROMs_dept,s_emp;

300rowsselected.其中一个表12行，一个表25行。

三、简单连接查询：

SELECTtable.column,table.column...

FROMtable1,table2

WHEREtable1.column1=table2.column2;

如：

SQL>SELECTs_emp.last_name,s_emp.dept_id,

s_dept.name

FROMs_emp,s_dept

WHEREs_emp.dept_id=s_dept.id;

注意：

表前缀的重要性：

SQL>SELECTs_dept.id”DepartmentID”,

s_region.id”RegionID”,

s_region.name”RegionName”

FROMs_dept,s_region

WHEREs_dept.region_id=s_region.id;

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

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

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

INITCAP（s_dept.last_name）=’Menchu’;

再如：

WHEREs_emp.dept_id=s_dept.idANDs_dept.region_id=s_region.idANDs_mission_pct>0;

四、表别名ALIAS：

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

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

实例：

SQL>SELECTc.name”CustomerName”,

c.region_id”RegionID”,

r.name”RegionName”

FROMs_customerc,s_regionr

WHEREc.region_id=r.id;

别名最多可以30个字符，但当然越少越好。

最好也能容易识别。

五、非等连接

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

最简单的说：

非等连接就是在连接中没有“=”出现的连接。

SQL>SELECTe.ename,e.job,e.sal,s.grade

FROMempe,salgrades

WHEREe.salBETWEENs.losalANDs.hisal;

说明：

Createanon-equijointoevaluateanemployee’ssalarygrade.Thesalary必须在另一个表中最高和最低之间。

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

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

六、外连接

语法结构：

SELECTtable.column,table.column

FROMtable1,table2

WHEREtable1.column=table2.column（+）;

实例：

SQL>SELECTe.last_name,e.id,c.name

FROMs_empe,s_customerc

WHEREe.id（+）=c.sales_rep_id

ORDERBYe.id;

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

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

外连接的限制：

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

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

七、自连接

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

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

实例：

SQL>SELECTworker.last_name||’worksfor’||

manager.last_name

FROMs_empworker,s_empmanager

WHEREworker.manager_id=manager.id;

意味着：

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

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

selectlast_namefroms_empwheresalary=（selectmax（salary）froms_emp）

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

课程四组函数

本课重点：

1、了解可用的组函数

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

3、使用GROUPBY

4、通过HAVING来限制返回组

注意：

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

一、概念：

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

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

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

HAVING来限制返回组，对RESULTSET而言。

二、组函数：

（#号的函数不做重点）

1、AVG

2、COUNT

3、MAX

4、MIN

5、STDDEV#

6、SUM

7、VARIANCE#

语法：

SELECTcolumn,group_function

FROMtable

[WHEREcondition]

[GROUPBYgroup_by_expression]

[HAVINGgroup_condition]

[ORDERBYcolumn];

实例1：

一个混合实例，说明所有问题：

SQL>SELECTAVG（salary）,MAX（salary）,MIN（salary）,

SUM（salary）

FROMs_emp

WHEREUPPER（title）LIKE’SALES%’;

AVG（SALARY）MAX（SALARY）MIN（SALARY）SUM（SALARY）

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

1476152514007380

说明：

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

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

如果是MIN（last_name）,MAX（last_name），返回的是什么呢？

千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：

xdopt>cssingkdkdk>adopt>acccc

实例2：

SQL>SELECTCOUNT（commission_pct）

FROMs_emp

WHEREdept_id=31;

返回所有非空行个数

三、GROUPBY的应用：

先看一个简单实例：

SQL>SELECTcredit_rating,COUNT（*）”#Cust”

FROMs_customer

GROUPBYcredit_rating;

注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：

#$_，什么对象的名字都如此。

当然空格也是可以的。

复杂实例：

SQL>SELECTtitle,SUM（salary）PAYROLL

FROMs_emp

WHEREtitleNOTLIKE’VP%’

GROUPBYtitle

ORDERBYSUM（salary）;

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

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

*另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUPBY!

否则要出错的!

信息为：

ERRORatline1:

ORA-00937:

notasingle-groupgroupfunction

理论很简单，如果不GROUPBYTITLE，显示哪一个呢？

这个在试题中经常出现。

结论：

不加分组函数修饰的列必定要出现在GROUPBY里。

错误实例：

SQL>SELECTdept_id,AVG（salary）

FROMs_emp

WHEREAVG（salary）>2000

GROUPBYdept_id;

WHEREAVG（salary）>2000

*ERRORatline3:

ORA-00934:

groupfunctionisnotallowedhere

应在GROUPBY后面加上HAVINGAVG（salary）>2000;

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

多级分组实例：

SQL>SELECTdept_id,title,COUNT（*）

FROMs_emp

GROUPBYdept_id,title;

就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITL