PLSQL学习教程.docx

PLSQL学习教程.docx

《PLSQL学习教程.docx》由会员分享，可在线阅读，更多相关《PLSQL学习教程.docx（56页珍藏版）》请在冰点文库上搜索。

PLSQL学习教程

课程一PL/SQL基本查询与排序　　

　　本课重点：

　　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_idFROMs_emp;　　

　　2：

SQL>SELECTlast_name,salary*12,commission_pct　FROMs_emp;　　

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

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

　　三、列的别名ALIASES：

　　计算的时候特别有用；　　

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

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

　　例（因字体原因，读者请记住：

引号为英文双引号DoubleQuotation）：

　　SQL>SELECTlast_name,salary,12*（salary+100）”AnnualSalary”FROMs_emp;

　　四、连接符号：

||　　

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

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

SQL>SELECTfirst_name||’’||last_name||’,’||title”Employees”FROMs_emp

SQL>selectdivid||''||divnamefrompub_t_division_testwheresuperid='001'

效果如下图：

　　五、管理NULL值：

　　SQL>SELECTlast_name,title,salary*NVL（commission_pct,0）/100COMMFROMs_emp;　　

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

selectdivid,divname,NVL（addr,0）frompub_t_division_testwheresuperid='001'

效果如下图：

　　六、SQLPLUS的基本内容，请参考

　　七、ORDERBY操作：

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

　　SELECTexpr　　FROMtable　　[ORDERBY{column,expr}[ASC|DESC]];　　

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

　　另：

通过位置判断排序：

SQL>SELECTlast_name,salary*12FROMs_empORDERBY2;

select*frompub_t_division_testwheresuperid='001'orderby3　　

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

　　另：

多列排序：

　　SQL>SELECTlastname,dept_id,salaryFROMs_empORDERBYdept_id,salaryDESC;

SQL>select*frompub_t_division_testwheresuperid='001'orderby1,3desc

　　八、限制选取行：

　　SELECTexpr　FROMtable　[WHEREcondition（s）]　　[ORDERBYexpr];　　

　　例1：

　　SQL>SELECTfirst_name,last_name,start_dateFROMs_empWHEREstart_dateBETWEEN’09-may-91

AND’17-jun-91’;

　　例2：

SQL>SELECTlast_nameFROMs_empWHERElast_nameLIKE’_a%’;　

//显示所有第二个字母为a的last_name，第一个字母’_’为一个占位符

　　例3：

　　如果有列为NULL　　

　　SQL>SELECTid,name,credit_ratingFROMs_customerWHEREsales_rep_idISNULL;

　　优先级：

　　Order　EvaluatedOperator　　

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

　　2　　AND　　

　　3　　　OR　　

　　总结：

我们今天主要学习了如何进行查询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、SUBSTR　SUBSTR（column|expression,m[,n]）　　

　　6、LENGTH　返回字符串的长度　　

　　7、NVL　　转换空值

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

　　SQL>SELECTfirst_name,last_nameFROMs_empWHEREUPPER（last_name）=’PATEL’;　　

　　FIRST_NAMELAST_NAME　　

　　Vikram　　Patel　　

　　Radha　　Patel

　　三、数学运算函数　　

　　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>SELECTSYSDATEFROMSYS.DUAL;

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

　　例：

SQL>SELECTlast_name,（SYSDATE-start_date）/7WEEKSFROMs_empWHEREdept_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_empWHEREMONTHS_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’）ORDEREDFROMs_ordWHEREsales_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、实例：

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

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

　　实例：

　　SQL>SELECTROUND（SALARY*1.25）FROMONE_TABLE；　　

　　意义：

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

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

　　5、混合实例：

　　SQL>SELECTlast_name,TO_CHAR（start_date,’fmDD”of”MonthYYYY’）HIREDATEFROMs_emp

WHEREstart_dateLIKE’%91’;

　　LAST_NAMEHIREDATE　　

　　Nagayama17ofJune1991　　

　　Urguhart18ofJanuary1991　　

　　Havel27ofFebruary1991　　

　　这里要注意：

fmDD和fmDDSPTH之间的区别。

　　SQL>SELECTid,total,date_orderedFROMs_ordWHEREdate_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_ordORDERBYdate_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_nameFROMs_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.nameFROMs_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）

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

　　1476　　　1525　　　　　1400　　　　7380

说明：

很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP

BY来形成对不同组的计算，相当于在很多值中进行挑选。

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

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

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

如：

xdopt

　>cssingkdkdk　>　adopt　>ac