PLSQL初级教程.docx

课程 一 pl/sql 基本查询与排序本课重点： 1、写select语句进行数据库查询 2、进行数学运算 3、处理空值 4、使用别名aliases 5、连接列 6、在sql plus中编辑缓冲，修改sql scripts 7、order by进行排序输出。 8、使用where 字段。 一、写sql 命令： 不区分大小写。 sql 语句用数字分行，在sql plus中被称为缓冲区。 最后以；或 / 结束语句。 也可以用run来执行语句 二、例1：sql select dept_id, last_name, manager_id 2 from s_emp; 2：sql select last_name, salary * 12, commission_pct 2 from s_emp; 对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。 sql select last_name, salary, 12 * (salary + 100) 2 from s_emp; 三、列的别名aliases： 计算的时候特别有用； 紧跟着列名，或在列名与别名之间加“as”； 如果别名中含有space，特殊字符，或大小写，要用双引号引起。 例(因字体原因，读者请记住：引号为英文双引号double quotation)： sql select last_name, salary, 2 12 * (salary + 100) ”annual salary” 3 from s_emp; 四、连接符号：| 连接不同的列或连接字符串 使结果成为一个有意义的短语： sql select first_name | | last_name 2 | , | title ”employees” 3 from s_emp; 五、管理null值： sql select last_name, title, 2 salary * nvl(commission_pct,0)/100 comm 3 from s_emp; 此函数使null转化为有意义的一个值，相当于替换null。 六、sql plus的基本内容，请参考 七、order by 操作： 与其他sql92标准数据库相似，排序如： select expr from table order by column,expr asc|desc; 从oracle7 release 7.0.16开始，order by 可以用别名。 另：通过位置判断排序： sql select last_name, salary*12 2 from s_emp 3 order by 2; 这样就避免了再写一次很长的表达式。 另：多列排序： sql select last name, dept_id, salary 2 from s_emp 3 order by dept_id, salary desc; 八、限制选取行： select expr from table where condition(s) order by expr; 例1：sql select first_name, last_name, start_date 2 from s_emp 3 where start_date between 09-may-91 4 and 17-jun-91; 例2： sql select last_name 2 from s_emp 3 where last_name like _a%; /显示所有第二个字母为 a的last_name 例3： 如果有列为null sql select id, name, credit_rating 2 from s_customer 3 where sales_rep_id is null; 优先级： order evaluated operator 1 all comparison operators (=, , , =, , select first_name, last_name 2 from s_emp 3 where upper(last_name) = patel; first_name last_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)= 45trunc(45.923，-1)= 40 3、mod 余除 mod(1600，300) 实例： sql select round(45.923,2), round(45.923,0), 2 round(45.923,-1) 3 from sys.dual; 四、oracle 日期格式和日期型函数： 1、默认格式为dd-mon-yy. 2、sysdate是一个求系统时间的函数 3、dualdju:el 是一个伪表，有人称之为空表，但不确切。 sql select sysdate 2 from sys.dual; 4、日期中应用的算术运算符 例：sql select last_name, (sysdate-start_date)/7 weeks 2 from s_emp 3 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, 2 months_between (sysdate,start_date) tenure, 3 add_months(start_date,6) review 4 from s_emp 5 where months_between (sysdate,start_date)48; 我们看到： months_between (sysdate,start_date) 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 2 from s_ord 3 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,2 fmdd ”of” month yyyy) hiredate3 from s_emp4 where start_date like %91;last_name hiredate- -nagayama 17 of june 1991urguhart 18 of january 1991havel 27 of february 1991 这里要注意：fmdd 和 fmddspth之间的区别。 sql select id, total, date_ordered 2 from s_ord 3 where date_ordered = 4 to_date(september 7, 1992,month dd, yyyy); 六、独立的函数嵌套 sql select concat(upper(last_name), 2 substr(title,3) ”vice presidents” 3 from s_emp 4 where title like vp%; * 嵌套可以进行到任意深度，从内向外计算。 例： sql select to_char(next_day(add_months 2 (date_ordered,6),friday), 3 fmday, month ddth, yyyy) 4 ”new 6 month review” 5 from s_ord 6 order by date_ordered; sql select last_name, 2 nvl(to_char(manager_id),no manager) 3 from s_emp 4 where manager_id is null; 对于例子，大家重要的理解，并多做测试，并注意英文版和中文版在日期上的区别。 有些教材上的例子，不要盲目的相信其结果，实践后才有发言权，希望大家能够在学习的过程中不要忽略了用，多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。这样，我们才真正掌握了知识。 课程 三 从多个表中提取数据 本课重点：1、select from 多个表，使用等连接或非等连接2、使用外连接outer join3、使用自连接注意：以下实例中标点均为英文半角一、连接的概念：是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。连接类型：等连接、不等连接、外连接、自连接二、cartesian product ：指的是当join条件被省略或无效时，所有表的行(交叉)都被select出来的现象。cartesian product可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。sql select name, last_name2 from s_dept, s_emp;300 rows selected. 其中一个表12行，一个表25行。三、简单连接查询：select table.column, table.column.from table1, table2where table1.column1 = table2.column2;如：sql select s_emp.last_name, s_emp.dept_id,2 s_3 from s_emp, s_dept4 where s_emp.dept_id = s_dept.id;注意：表前缀的重要性：sql select s_dept.id ”department id”,2 s_region.id ”region id”,3 s_ ”region name”4 from s_dept, s_region5 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_mission_pct 0;四、表别名alias：1、使用别名进行多表查询 。2、仅在这个查询中生效，一旦用了表别名，就不能再用表的原有的名字进行连接。实例：sql select ”customer name”,2 c.region_id ”region id”,3 ”region name”4 from s_customer c, s_region r5 where c.region_id = r.id;别名最多可以30个字符，但当然越少越好。最好也能容易识别。五、非等连接 非等连接一般用在没有明确的等量关系的两个表；最简单的说：非等连接就是在连接中没有“=”出现的连接。sql select e.ename, e.job, e.sal, s.grade2 from emp e, salgrade s3 where e.sal between s.losal and s.hisal;说明：create a non-equijoin to evaluate an employees salary grade. the salary 必须在另一个表中最高和最低之间。其他操作符= 也可以实现，但是between是非常简单实用的。between .and是指闭区间的，这点要注意 ，请大家测试。六、外连接语法结构：select table.column, table.columnfrom table1, table2where table1.column = table2.column(+);实例：sql select e.last_name, e.id, 2 from s_emp e, s_customer c3 where e.id (+) = c.sales_rep_id4 order by e.id;显示.，即使有的客户没有销售代表。* 可以理解为有+号的一边出现了null，也可以做为合法的条件。外连接的限制：1、外连接符只能出现在信息缺少的那边。2、在条件中，不能用 in 或者 or做连接符。七、自连接同一个表中使用连接符进行查询；from 的后面用同一个表的两个别名。实例：sql select worker.last_name| works for |2 manager.last_name3 from s_emp worker, s_emp manager4 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), 2 sum(salary) 3 from s_emp 4 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) 2 from s_emp 3 where dept_id = 31; 返回所有非空行个数 三、group by的应用： 先看一个简单实例： sql select credit_rating, count(*) ”# cust” 2 from s_customer 3 group by credit_rating; 注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#$_，什么对象的名字都如此。当然空格也是可以的。 复杂实例： sql select title, sum(salary) payroll 2 from s_emp 3 where title not like vp% 4 group by title 5 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) 2 from s_emp 3 where avg(salary) 2000 4 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(*) 2 from s_emp 3 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 main query sql select dept_id sql select last_name, title 2 from s_emp 2 from s_emp 3 where upper(last_name)=biri; 3 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 2 from s_emp 3 where salary select dept_id, avg(salary) 2 from s_emp 3 group by dept_id 4 having avg(salary) 5 (select avg(salary) 6 from s_emp 7 where dept_id = 32); 子查询被多次执行，因为它出现在having 子句中。 sql select title, avg(salary) 2 from s_emp 3 group by title 4 having avg(salary) = 5 (select min(avg(salary) 6 from s_emp 7 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 2 from s_emp 3 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 , r.id, ”region name” from s_dept d, s_region r where d.region_id = r.id and upper() 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 2 from s_dept 3 where lower(name) = &dname; 可以正常执行了。 sql define dname 主要是显示当前的变量是否赋值，值是什么。当然，我们可以用undefinego 来使变量恢复初始，不然它会一直保持下去。 3、如果变量在sql script文件中确定 ：可以sql start l7param president 来赋值。 总结：本课主要针对较古老的sqlplus方法，在report和结果集生成方面使用变量，达到方便操作，动态修改的目的。 课程 七 其他数据库对象 sequence 创建实例：sql create sequence s_dept_id2 increment by 13 start with 514 maxvalue 99999995 nocache6 nocycle;sequence created.1、nextval和currval的用法 只有在insert中，才可以作为子查询出现。以下几个方面不可用子查询：select子句ofaview有distinct的出现的select。有groupby，having，orderby的select子句。select或delete，update中的子查询。default选项中不能用。2、编辑sequence只有owner或有alter权限的用户才能修改sequence未来的number受修改的影响。不能修改startwith，如果变，则要recreate。修改会受到某些有效性检验的限制，如maxvalue3、删除：drop sequence sequence;oracle对象之index一、index概述：是oracle的一种数据对象，用pointer来加速查询行。通过快速路径存取方法定位数据并减少i/o。index独立于表。index由oracleserver来使用和保持。二、索引如何建立？1、自动：通过primarykey和unique key约束来建立。 2、用户手工建立非唯一性索引。 三、创建方法： 语法：create index indexon table (column, column.); 何时建立index： 此列经常被放到where字段或join来作条件查询。 此列含有大量的数据。 此列含有大量的空值。 两个或几个列经常同时放到where字段进行组合查询 表很大而且只有少于2-4% 的row可能被查询的时候。 以下情况不要建立索引： 表很小； 表被更新频繁。 四、查看已经存在的索引： 1、user_indexes可以查询索引名和类型。 2、user_ind_columns包含索引名、表名、列名。 实例： sql select ic.index_name, ic.column_name,2 ic.column_position col_pos, ix.uniqueness3 from user_indexes ix, user_ind_columns ic4 where ic.index_name = ix.index_name5 and ic.table_name = s_emp; 五、删除索引： drop index index; synonyms 同义词 语法： create public synonym synonym for object; 注意：此对象不能包含在一个包里； 一个私有的同义词不能与同一user的其他对象重名。 drop synonym d_sum； 课程 八 用户访问控制 本课重点： 1、创建用户 2、创建角色来进行安全设置 3、使用grant或revoke 来控制权限 注意：以下实例中标点均为英文半角 一、概述： oracle通过用户名和密码进行权限控制。