Oracle总结Kernel001

1.数据库介绍1.1数据库简介1.1.1数据管理技术的发展人工管理阶段：20世纪50年代中期之前文件管理阶段：20世纪的50年代末期到60年代中期缺点：数据冗余数据的不准确数据之间的联系弱数据库管理阶段：数据库技术诞生的三大标志性事件：1968年，IBM公司--->IMS1969年，DBTG发布了一系列的报告--->标准和规范1970年，IBM的研究员E.F.Codd发表了一系列论文提出了关系模型特点：采用复杂的结构化的数据模型最低的冗余度数据完整性数据库系统为用户提供了方便的接口控制功能：并发控制数据库的恢复安全性系统更加灵活信息处理方式不在以程序为中心，而是以数据为中心

1.2主流的关系型数据库产品商业型OracleOracle(甲骨文)10g11g12cDB2IBMsqlserver微软sybase开源mysqlOracle

1.3sql语言SQL(StructuredQueryLanguage):结构化查询语言SQL分为：数据查询语言(DQL):select用来查询数据中的数据使用最广泛、语法最灵活数据操作语言(DML):insertdeleteupdate用来改变数据库中的数据数据定义语句(DDL):createdropalter用来创建、删除、修改数据库对象事务控制语句(TCL):commitrollbacksavepoint用来保证数据的一致性数据控制语句(DCL):grant、revoke、createuser用来执行权限的授予和回收、创建用户等

第二步：使用sqlplus工具sqlplus输入用户名:输入密码:

现场班：telnet172.60.5.80或172.60.5.81用户：oracle密码:oracle

sqlplus输入用户名:openlab输入密码:open123

SQL>sqlplus命令：可以不以分号结尾sql语句:必须以分号结尾(学习的内容)

1.5描述表结构sqlplus命令：descdesc表名[;]descs_emp;

NameNullType-----------------------------------------------------------ID员工编号NOTNULLNUMBER(7)LAST_NAME姓NOTNULLVARCHAR2(25)FIRST_NAME名VARCHAR2(25)USERIDVARCHAR2(8)START_DATE入职日期DATECOMMENTSVARCHAR2(255)MANAGER_ID领导编号NUMBER(7)TITLE职位VARCHAR2(25)DEPT_ID部门编号NUMBER(7)SALARY工资NUMBER(11,2)COMMISSION_PCT提成NUMBER(4,2)

Name:表中的字段名Null该字段是否允许为空NOTNULL:不允许为空Type:字段的数据类型number(p,s)数字类型p:有效数字的位数(从第一个非零数字开始)1<=p<=38默认38s:精度-84<=s<=127number--->number(38,0)number(p)---->number(p,0)number(p,s)varchar2(n)变长字符串n不能省略1~4000byteschar(n)定长字符串n默认为11~2000bytesdate日期

2.select语句2.1几个概念选择：选中部分行，全部列投影：选中全部行，部分列表连接：需要的数据来自于多张表2.2select语句的子句基本的查询语句:select..fromwhere子句orderby子句单行函数表连接组函数和分组子查询

3.select..from语句select字段列表from表名;3.1列出表中一个字段select字段名from表名;--列出所有员工的工资selectsalaryfroms_emp;

3.2列出表中的多个字段select字段名,字段名,....from表名;--列出员工的编号、名字、职位、工资selectid,first_name,title,salaryfroms_emp;

3.3列出表中全部字段--selectid,last_name,first_name,userid,.....froms_emp;--使用*代替所有字段select*froms_emp;

3.4算数运算一般指的是数字类型+-*/--列出员工的信息，包括编号、名字、工资、年收入12*salary+1000selectid,first_name,salary,12*salary+1000froms_emp;3.5字段或表达式命名别名3.5.1语法字段或表达式[as]别名selectid,first_nameasname,salary,12*salary+1000yearsalfroms_emp;3.5.2使用""--屏蔽特殊字符或关键字等selectid,first_nameasname,salary,12*salary+1000"yearsal"froms_emp;--大小写敏感selectid,first_nameasname,salary,12*salary+1000"YearSal"froms_emp;

3.6sql中的字符串3.6.1字符串的表示方式单引号：'''a''Helloworld'3.6.2字符串的拼接||--把员工的first_name和last_name拼接起来selectid,first_name||last_nameasnamefroms_emp;--把员工的first_name和last_name之间拼接一个.selectid,first_name||'.'||last_nameasnamefroms_emp;--把员工的first_name和last_name之间拼接一个'使用转义字符：'selectid,first_name||''''||last_nameasnamefroms_emp;

3.7NULL值的处理--计算员工的年收入，考虑提成12*salary+12*salary*commission_pct/10012*salary*(1+commission_pct/100)--下面语句的结果集是错误的selectid,first_name,title,12*salary*(1+commission_pct/100)asyearsalfroms_emp;NULL值参与运算的表达式的结果为空

使用函数nvl处理NULL值nvl(par1,par2):当par1不为空，返回par1放par1为空，返回par2nvl(12*salary*(1+commission_pct/100),12*salary)12*salary*(1+nvl(commission_pct,0)/100)

selectid,first_name,salary,12*salary*(1+nvl(commission_pct,0)/100)asyearsalfroms_emp;

3.8排重distinct--列出员工的职位selectdistincttitlefroms_emp;--多列排重selectdistincttitle,dept_idfroms_emp;

4.where子句select...from..where条件;4.1作用根据条件对表中的数据进行筛选，挑选出符合条件的行4.2数字类型的条件--列出工资大于1400的员工的信息selectid,first_name,salaryfroms_empwheresalary>1400;4.3字符串类型的条件--列出名字为'Ben'的员工的信息selectid,first_name,salaryfroms_empwherefirst_name='Ben';--有一行结果selectid,first_name,salaryfroms_empwherefirst_name='ben';--没有结果--sql中没有==--sql中不区分大小写，但是字符串的值是区分的4.4比较运算符><>=<==!=(<>^=)4.5sql提供的运算符4.5.1表示一个闭区间[a,b]betweenaandb--列出工资在[1100,1550]之间的员工的信息selectid,first_name,salaryfroms_empwheresalarybetween1100and1550;

4.5.2表示一个列表in(值1,值2,...)addressin('北京','上海','广州')--列出部门编号为31，42，50的员工的信息selectid,first_name,dept_idfroms_empwheredept_idin(31,42,50);

4.5.3模糊查询like'包含通配符的字符串'

通配符：%:任意长度的任意字符_:一位任意字符

StuNamelike'李_%'

--列出first_name首字母为'M'的员工的信息selectid,first_namefroms_empwherefirst_namelike'M%';--列出first_name第二个字母为'a'的员工的信息selectid,first_namefroms_empwherefirst_namelike'_a%';

user_tables:数字字典保存当前用户的所有表的信息descuser_tables;--列出user_tables中以'S_'开头的表的信息selecttable_namefromuser_tableswheretable_namelike'S_%';--使用转义字符escapeselecttable_namefromuser_tableswheretable_namelike'S\_%'escape'\';

4.5.4空值的判断isnull--列出manager_id为空的员工的信息selectid,first_name,titlefroms_empwheremanager_id=null;selectid,first_name,titlefroms_empwheremanager_id!=null;--使用=或!=判断null值，结果永远为假

selectid,first_name,titlefroms_empwheremanager_idisnull;

4.6sql中的逻辑运算符andornot--使用and改写betweenand案例列出工资在[1100,1550]之间的员工的信息selectid,first_name,salaryfroms_empwheresalarybetween1100and1550;selectid,first_name,salaryfroms_empwheresalary>=1100andsalary<=1550;

--使用or改写in案例列出编号为31，42，50的部门的员工的信息selectid,first_name,dept_idfroms_empwheredept_idin(31,42,50);selectid,first_name,dept_idfroms_empwheredept_id=31ordept_id=42ordept_id=50;

对立面：><=<>==!=(<>^=)betweenandnotbetweenandinnotin(注意NULL值)likenotlikeisnullisnotnull

--列出有提成的员工的信息selectid,first_name,commission_pctfroms_empwherecommission_pctisnotnull;

5.orderby子句5.1总是出现在一条select语句的最后select字段列表from表名where子句......orderby子句;5.2语法orderby排序标准排序方式排序方式：asc升序(自然顺序、字典顺序)默认排序方式desc降序

5.3按照工资降序排序，列出dept_id为31，32，33的员工的信息selectid,first_name,salary,dept_idfroms_empwheredept_idin(31,32,33)orderbysalarydesc;

5.4多列排序selectid,first_name,salaryfroms_emporderbysalary;

orderby排序标准1排序方式,排序标准2排序方式selectid,first_name,salaryfroms_emporderbysalary,iddesc;--多列排序时，每一个排序标准的排序方式是自己定义的5.5排序时，默认按照最大值处理--根据manager_id排序，列出员工的信息selectid,first_name,manager_idfroms_emporderbymanager_iddesc;-----------------------------------------------------------------总结1.数据库介绍sql语言sqlplus的命令：desc数据类型2.select语句3.select...from4.where子句比较运算符：><>=<==!=(<>^=)sql提供的运算符betweenandinlikeisnullsql中的逻辑运算符andornot5.orderby子句

----------------------------------------------------------------练习：1.查看s_dept和s_region表的表结构2.查询s_dept和s_region表中的数据3.计算员工的年收入，列出年收入大于15000的员工的信息，并对年收入进行降序排序。

--day02--回顾：1.数据库介绍sql:dql:selectdml:insertdeleteupdateddl:createdropaltertcl:commitrollbacksavepointdcl:createuser、grant、revokedescs_dept;2.from子句selectnamefroms_dept;selectid,namefroms_dept;select*froms_dept;selectid,12*salary+1000froms_emp;selectid,12*salary+1000as"yearsal"froms_emp;selectid,first_name||''''||last_namenamefroms_emp;selectid,12*salary*(1+nvl(commission_pct,0)/100)yearsalfroms_emp;selectdistincttitle,dept_idfroms_emp;

3.where子句selectid,first_name,salaryfroms_empwheresalary>1400;selectid,first_namefroms_empwherefirst_name='Ben';selectid,first_name,salaryfroms_empwheresalarybetween1100and1550;selectid,first_name,dept_idfroms_empwheredept_idin(31,42,50);selectid,first_namefroms_empwherefirst_namelike'_a%';selecttable_namefromuser_tableswheretable_namelike'S\_%'escape'\';selectid,first_name,manager_idfroms_empwheremanager_idisnull;andornotselectid,first_name,salaryfroms_empwheresalary>=1100andsalary<=1550;selectid,first_name,dept_idfroms_empwheredept_id=31ordept_id=42ordept_id=50;selectid,first_name,commission_pctfroms_empwherecommission_pctisnotnull;

4.orderby子句selectid,first_name,salaryfroms_emporderbysalarydesc,id;

selectid,first_name,start_datefroms_emporderbystart_date;

练习：selectid,first_name,salary,12*salary*(1+nvl(commission_pct,0)/100)asyearsalfroms_empwhere12*salary*(1+nvl(commission_pct,0)/100)>15000orderbyyearsaldesc;

-----------------------------------------------------------------1.单行函数1.1单行函数和组函数的概念单行函数：针对sql语句影响的数据，每行都做处理，每行产生一个结果selectupper(first_name)froms_empwhereid<11;组函数：针对sql语句影响的数据，每组做处理，每组产生一个结果selectcount(first_name)froms_empwhereid<11;

1.2dual表descdual;1.3字符串函数upper(s):把参数s中的英文字母转换成大写返回selectupper('helloworld!')fromdual;lower(s):把参数s中的英文字母转换成小写返回selectlower('helloworld!')fromdual;initcap(s):把参数s中的每个单词转换成首字母大写、其余小写的形式selectinitcap('helloworld!')fromdual;concat(s1,s2):字符串连接||

substr(s,start[,length]):从start位置开始，截取字符串s中的length的字符start:开始位置从1计数如果写成0，按照1处理>0表示从左侧开始计数<0表示从右侧开始计数length:截取的子字符串的长度缺省时，表示截取到字符串的最后selectsubstr('helloworld!',-6,4)fromdual;length(s):返回字符串的长度selectlength('helloworld!')fromdual;

练习：使用两种方式列出s_emp中first_name的后三位selectfirst_name,substr(first_name,-3)froms_emp;

selectfirst_name,substr(first_name,length(first_name)-2)froms_emp;1.4数字函数round(x[,y]):四舍五入y:缺省时，0round(4.56)=5>0四舍五入到小数点后y位round(4.56,1)=4.6<0四舍五入到小数点前|y|位round(456.78,-2)=500trunc(x[,y]):截取y:缺省时，0trunc(4.56)=4>0截取到小数点后y位trunc(4.56,1)=4.5<0截取到小数点前|y|位trunc(456.78,-2)=400selecttrunc(456.67,-2)fromdual;

floor(x):不大于x的最大整数ceil(x)：不小于x的最小整数selectfloor(4.5)fromdual;<<5selectceil(4.5)fromdual;>>4

date数据各部分的格式表示：cc世纪21

yy2位数字的年17yyyy4位数字的年2017year年份的英文全拼twentyseventeenmm2位数字的月10mon(MON)月份单词的前三个字母oct(OCT)month(MONTH)月份单词的全拼october(OCTOBER)dd2位数字的天12dy星期的单词的前三个字母thuday星期单词的全拼thursday

hh12小时制的小时02hh2424小时制的小时14

mi分钟34ss秒15

2017-10-12'yyyy-mm-dd'

1.5.2日期类型的算术运算（在day上进行操作）1)日期+数字selectsysdate+80fromdual;2)日期-数字selectsysdate-200fromdual;3)日期1-日期2selectsysdate-to_date('01-JAN-00')fromdual;

1.5.3常用的日期函数add_months(d,n):在日期d上加n个月selectadd_months(sysdate,4)fromdual;months_between(d1,d2):返回两个日期相差的月数selectmonths_between(sysdate,'01-JAN-00')fromdual;next_day(d,dy):返回日期d的下一个dy(星期几)selectnext_day(sysdate,'FRIDAY')fromdual;selectnext_day(sysdate,'FRI')fromdual;selectnext_day(sysdate,6)fromdual;使用1~7对应星期日~星期六last_day(d):返回日期d所在月份的最后一天selectlast_day(sysdate)fromdual;

1.6转换函数1.6.1to_charto_char(d|n[,fmt]):把日期或数字按照给定的格式转换成字符串1)日期-->字符串selectto_char(sysdate,'yyyy-mm-ddhh:mi:ssam')fromdual;selectid,first_name,to_char(start_date,'yyyy-mm-dd')froms_emporderbystart_date;2)数字--->字符串格式：9小数点前代表0-9，小数点后代表1-90小数点前代表前导0，小数点后代表0-9.小数点,分隔符$美元符号L本地货币符号格式字符串以fm开头，比如：fm$099,999.00selectto_char(1234,'fm$099,999.00')fromdual;1.6.2to_numberto_number(s[,fmt])selectto_number('$001,234.00','fm$099,999.00')fromdual;--隐式转换selectid,first_name,salaryfroms_empwhereid='1';

1.6.3to_dateto_date(s[,fmt])字符串---->日期createtabletestdate_zsm_00(idnumber,start_timedate);insertintotestdate_zsm_00values(1,'12-OCT-17');insertintotestdate_zsm_00values(2,to_date('2017-10-1216:22','yyyy-mm-ddhh24:mi'));selectid,to_char(start_time,'yyyy-mm-ddhh24:mi')fromtestdate_zsm_00;

1.7函数嵌套一个函数的返回值作为另一个函数的参数--练习：列出每个员工的id,first_name和manager_id,如果manager_id为null，显示成'BOSS'nvl:参数可以是任意类型，但是两个参数的类型必须一致to_char

selectid,first_name,nvl(to_char(manager_id),'BOSS')midfroms_emp;

2.表连接2.1需求：列出每个员工的id,first_name和所在部门的名称1)列出每个员工的id,first_name和所在部门的编号selectid,first_name,dept_idfroms_emp;2)需要查询的数据来自于两张表descs_dept;NameNullType------------------------------------------------------ID部门编号NOTNULLNUMBER(7)NAME部门名称NOTNULLVARCHAR2(25)REGION_ID地区编号NUMBER(7)s_emp:id,first_names_dept:name

select*froms_dept;selects_emp.id,s_emp.first_name,s_dept.namefroms_emp,s_deptwheres_emp.dept_id=s_dept.id;

笛卡尔积：从多张表中获取数据时，没有表连接的条件的结果集，称为笛卡尔积一般把表连接的筛选条件称为连接条件(关联条件)

表连接的基本语法：select字段列表from表1,表2,..where关联条件;

2.2表的别名表名别名selecte.id,e.first_name,d.namefroms_empe,s_deptdwheree.dept_id=d.id;--表一旦命名别名，则原表名在当前语句中失效selecte.id,e.first_name,d.namefroms_empe,s_deptdwheree.dept_id=s_dept.id;--错误

--如果关联的多张表中，字段名没有冲突，可以省略该字段名前的表名或别名selecte.id,first_name,namefroms_empe,s_deptdwheredept_id=d.id;

2.3表连接的分类内连接：符合关联条件的数据才回出现在结果集中外连接：内连接的结果集+匹配不上的数据

2.4内连接2.4.1等值连接关联条件中使用的运算符是=descs_region;NameNullType----------------------------------------------------ID地区编号NOTNULLNUMBER(7)NAME地区名称NOTNULLVARCHAR2(50)

--练习：列出每个部门及其所在地区的信息，包括部门编号、部门名称、所在地区的名称s_dept:id,names_region:name关联字段：s_deptregion_ids_regionidselectd.id,d.namedname,r.namernamefroms_deptd,s_regionrwhered.region_id=r.id;

2.5外连接(+)--oracle中外连接的特有用法

表1.字段(+)运算符表2.字段：内连接的结果集+表2中匹配不上的数据表1.字段运算符表2.字段(+)内连接的结果集+表1中匹配不上的数据匹配不上的这部分数据，另一张表中所有字段添null

2.5.1自连接需求：列出普通员工的信息1）把普通员工的信息加入到结果集selecte.id,e.first_name,m.id,m.first_namefroms_empe,s_empmwheree.manager_id(+)=m.id;2)从结果集中筛选出普通员工selectm.id,m.first_namefroms_empe,s_empmwheree.manager_id(+)=m.idande.idisnull;

2.5.2等值连接--把id=1的部门改为nullupdates_empsetdept_id=nullwhereid=1;commit;--列出所有员工的id,first_name和所在部门名称selecte.id,e.first_name,d.namefroms_empe,s_deptdwheree.dept_id=d.id(+);2.5.3非等值连接--把id=1的员工的工资改为10000updates_empsetsalary=10000whereid=1;commit;--列出所有员工的工资级别信息selecte.id,e.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)andg.hisal(+);selecte.id,e.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salary(+)>=g.losalande.salary(+)<=g.hisal;-----------------------------------------------------------------练习：--向部门表中添加一行数据insertintos_dept(id,name)values(110,'Test');commit;1.列出所有没有员工的部门的信息2.列出员工的编号、名称、以及工作地区的名称三表查询：select字段列表from表1,表2,表3where关联条件1and关联条件2;

--day03--回顾：1.单行函数2.表连接oracle中的表连接内连接等值连接selecte.id,e.first_name,d.namefroms_empe,s_deptdwheree.dept_id=d.id;selectd.id,d.namedname,r.namernamefroms_deptd,s_regionrwhered.region_id=r.id;selecte.id,e.first_name,r.namefroms_empe,s_deptd,s_regionrwheree.dept_id=d.idandd.region_id=r.id;非等值连接selecte.id,e.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losalandg.hisal;自连接selectdistinctm.id,m.first_namefroms_empe,s_empmwheree.manager_id=m.id;

外连接等值连接selecte.id,e.first_name,d.namefroms_empe,s_deptdwheree.dept_id=d.id(+);selectd.id,d.namedname,r.namernamefroms_deptd,s_regionrwhered.region_id=r.id(+);非等值连接selecte.id,e.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)andg.hisal(+);自连接selectm.id,m.first_namefroms_empe,s_empmwheree.manager_id(+)=m.idande.idisnull;

selectd.id,d.namefroms_empe,s_deptdwheree.dept_id(+)=d.idande.idisnull;-----------------------------------------------------------------1.sql99标准中的表连接内连接:select字段列表from表1[inner]join表2on关联条件;

selecte.id,e.first_name,d.namefroms_empeinnerjoins_deptdone.dept_id=d.id;

三表连接：select字段列表from表1join表2on关联条件1join表3on关联条件2;selecte.first_name,d.name,r.namefroms_empejoins_deptdone.dept_id=d.idjoins_regionrond.region_id=r.id;

外连接:左外连接：内连接的结果集+左表匹配不上的数据select字段列表from左表left[outer]join右表on关联条件;selecte.id,e.first_name,d.namefroms_empeleftjoins_deptdone.dept_id=d.id;右外连接：内连接的结果集+右表匹配不上的数据select字段列表from左表right[outer]join右表on关联条件;selecte.id,e.first_name,d.namefroms_emperightjoins_deptdone.dept_id=d.id;全外连接：内连接的结果集+两表匹配不上的数据select字段列表from左表full[outer]join右表on关联条件;selecte.id,e.first_name,d.namefroms_empefulljoins_deptdone.dept_id=d.id;

selectd.id,d.namefroms_emperightjoins_deptdone.dept_id=d.idwheree.idisnull;

2.集合运算（合并结果集）union:两个结果集取并集并排重、排序unionall：两个结果集直接取并集selectidfroms_empunionselectidfroms_dept;selectidfroms_empunionallselectidfroms_dept;intersect:取两个结果集的交集selectidfroms_empintersectselectidfroms_dept;minus:第一个结果集-第二个结果集selectidfroms_empminusselectidfroms_dept;--两个结果集的字段列表的数量和数据类型必须匹配selectid,first_namefroms_empminusselectid,namefroms_dept;

selectid,first_namefroms_empunionselectnull,namefroms_dept;

3.组函数和分组3.1组函数1)常用的组函数count(par|*):统计一组数据的行数参数可以是任何类型还可以是*--统计工资高于1500的员工数selectcount(*)froms_empwheresalary>1500;max(par):统计一组数据中的最大值min(par)：统计一组数据中的最小值参数可以是数字、字符串、日期类型--列出最早和最晚入职的员工selectmax(to_char(start_date,'yyyy-mm-dd')),min(to_char(start_date,'yyyy-mm-dd'))froms_emp;sum(par):统计一组数据的和avg(par):统计一组数据的平均值参数是数字类型--列出销售部的总工资和平均工资selectsum(salary),avg(salary)froms_empe,s_deptdwheree.dept_id=d.idandd.name='Sales';

2)组函数对null的处理:忽略selectcount(commission_pct)froms_emp;3)组函数可以排重selectsum(salary),sum(distinctsalary)froms_emp;selectcount(salary),count(distinctsalary)froms_emp;

3.2分组3.2.1语法groupby分组标准

.....where....groupby......selectorderby

3.2.2分组统计每个部门的人数selectdept_id,count(*)cntfroms_empgroupbydept_id;/*分组语句中列出的字段必须是分组标准或者是组函数的参数*/selectid,dept_id,count(*)cntfroms_empgroupbydept_id;--错误

3.2.2多列分组分组统计每个部门的人数,显式部门的名称和人数selecte.dept_id,d.name,count(e.id)cntfroms_empejoins_deptdone.dept_id=d.idgroupbye.dept_id,d.name;分组统计每个部门的人数,列出人数超过2个的部门

selectdept_id,count(*)cntfroms_emp--错误：where子句中不能使用组函数--wherecount(*)>2groupbydept_id;

3.2.3having子句分组后，根据条件筛选出符合条件的组selectdept_id,count(*)cntfroms_empwhere1=1groupbydept_idhavingcount(*)>2orderbycnt;

--语法顺序select字段列表from表名where条件--从表中根据条件筛选符合条件的行groupby分组标准--根据分组标准分成多个组having条件--从分组结果中根据条件筛选符合条件的组orderby排序标准排序方式;

--执行顺序fromwheregroupbyhavingselectorderby

--练习：列出平均工资大于1000的部门的信息selectdept_id,avg(salary)avgsalfroms_empgroupbydept_idhavingavg(salary)>1000;

selectd.id,d.name,avg(e.salary)avgsalfroms_empe,s_deptdwheree.dept_id=d.idgroupbyd.id,d.namehavingavg(e.salary)>1000orderbyavgsal;

4.2having子句--列出平均工资高于公司平均工资的部门的信息selectdept_id,avg(salary)froms_empgroupbydept_idhavingavg(salary)>(selectavg(salary)froms_emp);4.3from子句一个select语句产生的结果集，可以看成是一个内视图或者匿名视图，只能在当前语句使用selectid,name,yearsalfrom(selectid,first_namename,12*salary+1000yearsalfroms_emp)ewhereyearsal>15000;--列出工资高于本部门平均工资的员工的信息a)列出每个部门的编号及其平均工资selectdept_id,avg(salary)avgsalfroms_empgroupbydept_id;--相当于包含dept_id和avgsal两个字段的一张表sb)使用表连接(s_emp、s)实现功能s_emp:id,first_name,salarys:avgsal关联字段:s_emp.dept_id和s.dept_idselecte.id,e.first_name,e.salary,s.avgsalfroms_empe,(selectdept_id,avg(salary)avgsalfroms_empgroupbydept_id)swheree.dept_id=s.dept_idande.salary>s.avgsal;4.4select之后外连接的另一种实现方式，并且更为灵活--列出员工及其所在部门的信息selectid,first_name,salary,(selectnamefroms_deptdwheree.dept_id=d.id)dnamefroms_empe;

--练习：列出所有和'Mark'在同一部门的员工的信息selectid,first_name,dept_idfroms_empwheredept_id=(selectdept_idfroms_empwherefirst_name='Mark');对象名_姓名缩写_座位号xxxx_zsm_00-----------------------------------------------------------------5.表的操作5.1表的创建和删除5.1.1标识符的命名1)由a-z、A-Z、0-9、_、$、#构成2)必须用字母开头3)不能和关键字重名4)不能和其他的数据库对象重名5)1-30位5.1.2创建表--语法createtable表名(字段名数据类型,....字段名数据类型);createtabletestid_zsm_00(idnumber,namevarchar2(20));5.1.3删除表droptable表名;droptabletestid_zsm_00;

5.2.3删除语句(delete)--整行删除1)语法delete[from]表名[where子句];2)删除表中全部数据deletefromemp_zsm_00;rollback;--撤销没有提交的操作3)带where子句的删除deletefromemp_zsm_00wherenamelike'test%';commit;

5.3事务控制语句(TCL)select:和事务无关ddl:隐式提交的事务dml:默认需要显式提交5.3.1事务控制语句的含义commit;确认事务(提交所有未提交的操作)savepoint保存点;定义保存点rollback;回滚事务(撤销所有未提交的操作)rollbackto保存点;回顾到保存点的位置5.3.2事务的四大特性(ACID)1)原子性：事务中的语句是一个不可分割的整体转账：账户表：account字段：idbalanceA账户--->B账户2000updateaccountsetbalance=balance-2000whereid='A';aupdateaccountsetbalance=balance+2000whereid='B';bifa&&bcommit;elserollback;

要么全部成功，要么全部失败

2）一致性事务执行的结果必须是使数据库从一种一致性状态变为另一种一致性状态3)隔离性一个事务对数据的改变，在提交之前，对于其他的事务是不可见的。4)持久性事务一旦提交，对数据的改变就是永久的

5.3.3部分成功部分失败--删除表中全部数据属于ddl语句，不能撤销truncatetableemp_zsm_00;

insertintoemp_zsm_00(id,name)values(1,'test1');savepointa;insertintoemp_zsm_00(id,name)values(2,'test2');savepointb;insertintoemp_zsm_00(id,name)values(3,'test3');savepointc;insertintoemp_zsm_00(id,name)values(4,'test4');select*fromemp_zsm_00;rollbacktob;commit;select*fromemp_zsm_00;----------------------------------------------------------------练习：1.使用select语句建表：droptableemp_zsm_00;createtableemp_zsm_00asselect*froms_emp;2.给所有'Carmen'的下属涨工资1)查询'Carmen'的编号2)根据'Carmen'的编号查询其下属的编号3)根据员工编号改工资3.删除和'Ben'同部门的员工

--day04--回顾：1.sql99中的表连接select字段列表from左表{[inner]|{left|right|full}[outer]}join右表on关联条件;

2.集合操作unionunionallintersectminus

3.组函数和分组3.1组函数count*maxminsumavg

distinctNULL3.2分组groupbyhaving

语法：selectfromwheregroupbyhavingorderby执行：fromwheregroupbyhavingselectorderby

4.子查询where子句selectid,first_name,salaryfroms_empwheresalary>(selectsalaryfroms_empwherefirst_name='Ben');selectid,first_namefroms_empwhereidin(selectdistinctmanager_idfroms_emp);selectid,first_namefroms_empwhereidnotin(selectdistinctmanager_idfroms_empwheremanager_idisnotnull);selectid,namefroms_deptdwhereexists(select*froms_empewheree.dept_id=d.id);having子句selectdept_id,avg(salary)avgsalfroms_empgroupbydept_idhavingavg(salary)>(selectavg(salary)froms_emp);from子句selecte.id,e.first_name,e.salary,s.avgsalfroms_empe,(selectdept_id,avg(salary)avgsalfroms_empgroupbydept_id)swheree.dept_id=s.dept_idande.salary>s.avgsal;select子句selecte.id,e.first_name,e.salary,(selectnamefroms_deptdwheree.dept_id=d.id)dnamefroms_empe;

5.表操作5.1创建表和删除表createtable表名(字段名数据类型,....字段名数据类型);droptable表名;5.2数据操作语句1)insertinsertinto表名[(字段列表)]values(值列表);2)updateupdate表名set字段=新值[,字段=新值,...][where子句];3)deletedelete[from]表名[where子句];commit;

truncatetable表名;--属于ddl语句，功能上等效于没有where子句的delete语句

5.3事务控制语句commit;rollback;savepoint保存点;rollbackto保存点;

事务的ACID特性----------------------------------------------------------------练习：2.给所有'Carmen'的下属涨工资1)查询'Carmen'的编号selectidfromemp_zsm_00wherefirst_name='Carmen';2)根据'Carmen'的编号查询其下属的编号selectidfroms_empwheremanager_id=(selectidfromemp_zsm_00wherefirst_name='Carmen');3)根据员工编号改工资updateemp_zsm_00setsalary=salary+500whereidin(selectidfromemp_zsm_00wheremanager_id=(selectidfromemp_zsm_00wherefirst_name='Carmen'));3.删除和'Ben'同部门的员工deletefromemp_zsm_00wheredept_id=(selectdept_idfromemp_zsm_00wherefirst_name='Ben')andfirst_name!='Ben';

-----------------------------------------------------------------1.约束constraint数据完整性：实体完整性域完整性引用完整性自定义完整性1.1约束的概念对表中的字段添加的限制1.2约束的具体类型主键约束：primarykey非空、唯一一张表只能有一个主键唯一约束：unique不能重复允许为null一个表中可以有多个唯一非空约束：notnull不允许为null检查约束：check(检查约束表达式)字段的值必须能够使检查约束表达式为真外键约束：foreignkeyreferences1.3约束的实现方式列级约束：定义完一个字段后，直接在后边添加约束表级约束：定义完一个表的所有字段后，逗号隔开，再添加约束

1.4主键约束1.4.1列级实现：1)系统自动为约束命名createtabletestcons_zsm_00(idnumber(7)primarykey,namevarchar2(20));insertintotestcons_zsm_00values(1,'test1');/*违反了唯一约束*/insertintotestcons_zsm_00values(1,'test2');*ERRORatline1:ORA-00001:uniqueconstraint(OPENLAB.SYS_C0042650)violated/*主键字段的值不允许为null*/insertintotestcons_zsm_00(name)values('test2');*ERRORatline1:ORA-01400:cannotinsertNULLinto("OPENLAB"."TESTCONS_ZSM_00"."ID")

2)手动为约束命名表名_字段_约束类型：testcons_id_pk字段数据类型constraint约束名约束类型droptabletestcons_zsm_00;createtabletestcons_zsm_00(idnumber(7)constrainttestcons_id_pk_zsm_00primarykey,namevarchar2(20));insertintotestcons_zsm_00values(1,'test1');/*违反了唯一约束*/insertintotestcons_zsm_00values(1,'test1');*ERRORatline1:ORA-00001:uniqueconstraint(OPENLAB.TESTCONS_ID_PK_ZSM_00)violated

1.4.2表级实现复合主键(组合键)droptabletestcons_zsm_00;createtabletestcons_zsm_00(idnumber(7)primarykey,useridnumber(10)primarykey,namevarchar2(20));useridnumber(10)primarykey,*ERRORatline3:ORA-02260:tablecanhaveonlyoneprimarykey

createtabletestcons_zsm_00(idnumber(7),useridnumber(10),namevarchar2(20),constrainttestcons_id_userid_pk_zsm_00primarykey(id,userid));insertintotestcons_zsm_00values(1,1,'test1');insertintotestcons_zsm_00values(1,2,'test1');insertintotestcons_zsm_00values(2,1,'test1');insertintotestcons_zsm_00values(2,2,'test1');/*违反了唯一约束*/insertintotestcons_zsm_00values(2,2,'test1');

注意：不建议使用复合主键

1.5唯一约束、非空约束和检查约束1.5.1列级实现droptabletestcons_zsm_00;createtabletestcons_zsm_00(idnumberprimarykey,namevarchar2(20)notnull,useridvarchar2(18)unique,salnumber(11,2)check(sal>2000));/*不能向name字段插入null(非空约束的字段不允许为null)*/insertintotestcons_zsm_00(id,userid,sal)values(1,'1234',2500);

insertintotestcons_zsm_00(id,name,userid,sal)values(1,'name1','1234',2500);/*违反了唯一约束*/insertintotestcons_zsm_00(id,name,userid,sal)values(2,'name2','1234',2350);/*唯一约束的字段允许为null*/insertintotestcons_zsm_00(id,name,sal)values(3,'name3',2350);/*唯一约束的字段可以有多个null值*/insertintotestcons_zsm_00(id,name,sal)values(4,'name4',2350);/*违反了检查约束*/insertintotestcons_zsm_00(id,name,sal)values(5,'name5',1350);*ERRORatline1:ORA-02290:checkconstraint(OPENLAB.SYS_C0042698)violated

1.5.2表级实现droptabletestcons_zsm_00;/*一个表中有两个唯一约束*/createtabletestcons_zsm_00(idnumberunique,namevarchar2(20)notnull,useridvarchar2(18)unique,salnumber(11,2)check(sal>2000));insertintotestcons_zsm_00values(1,'test1','12',3000);/*违反了id字段的唯一约束*/insertintotestcons_zsm_00values(1,'test1','123',3000);/*违反了userid的唯一约束*/insertintotestcons_zsm_00values(2,'test1','12',3000);

droptabletestcons_zsm_00;/*id和userid组合作为唯一键*/createtabletestcons_zsm_00(idnumber,namevarchar2(20)notnull,useridvarchar2(18),salnumber(11,2),unique(id,userid),check(sal>2000));

1.5.3从业务层面，没有多个字段联合非空的需要，所有Oracle没有提供非空约束的表级实现

1.6外键约束一个表中某字段的值，受另一张表中某个字段的限制主表(父表)：提供数据的表从表(子表)：外键所在的表(引用主表中唯一性字段(主键、唯一)的值)外键的值只能是主表中对应字段的值或者为null外键约束的语法：references主表(字段)constraint约束名references主表(字段)

1.6.1创建表一般先创建主表，再创建从表(除非建表时也不添加外键约束)/*创建主表*/createtableparent_zsm_00(idnumber(7)primarykey,namevarchar2(25)notnull);/*创建子表*/createtablechild_zsm_00(idnumber(7)primarykey,c_namevarchar2(20)notnull,p_idnumber(7)referencesparent_zsm_00(id));

1.6.2数据操作语句(dml)对于有外键约束的表，执行dml操作时，一定要确保子表中外键的值不能孤立(在主表中可以找到)1)insertinsertintoparent_zsm_00values(1,'Admin');commit;insertintochild_zsm_00values(1001,'test1',1);insertintochild_zsm_00values(1002,'test2',null);commit;2)update/*违反了完整性约束--添加或更新子表数据时，主表中不存在该值*/updatechild_zsm_00setp_id=2whereid=1002;*ERRORatline1:ORA-02291:integrityconstraint(OPENLAB.SYS_C0042774)violated-parentnotfound

/*违反了完整性约束--更新或删除主表数据时，数据被子表引用*/updateparent_zsm_00setid=2whereid=1;*ERRORatline1:ORA-02292:integrityconstraint(SYSTEM.SYS_C0042798)violated-childrecordfound

3)deletedeletefromparent_zsm_00whereid=1;

1.6.3删除表一般先删除子表，再删除主表droptablechild_zsm_00;droptableparent_zsm_00;--对表的级联删除(先解除外键约束，然后再删除表)droptable表名cascadeconstraits;droptableparent_zsm_00cascadeconstraints;droptablechild_zsm_00cascadeconstraints;

1.6.4表中数据的级联删除和级联置空级联删除：ondeletecascade级联置空：ondeletesetnull/*创建主表*/createtableparent_zsm_00(idnumber(7)primarykey,namevarchar2(25)notnull);/*创建子表*/createtablechild_zsm_00(idnumber(7)primarykey,c_namevarchar2(20)notnull,p_idnumber(7),constraintparent_id_child_pid_fk_zsm_00foreignkey(p_id)referencesparent_zsm_00(id)ondeletecascade);insertintoparent_zsm_00values(1,'Admin');insertintoparent_zsm_00values(2,'Test');commit;insertintochild_zsm_00values(1001,'test1',1);insertintochild_zsm_00values(1002,'test2',1);insertintochild_zsm_00values(1003,'test3',2);insertintochild_zsm_00values(1004,'test4',2);commit;deletefromparent_zsm_00whereid=1;commit;

2.其他的数据库对象和分页2.1其他的数据库对象2.1.1序列sequence1)作用给主键产生不重复的数字2)创建序列createsequence序列名;--创建一个测试表createtabletestseq_zsm_00(idnumber(10)primarykey,namevarchar2(20));--创建序列createsequencetestseq_id_zsm_00;3)使用序列nextval:产生一个新的序列值currval:当前的序列值序列名.nextval序列名.currval第一个使用一个序列时，应该首先调用nextval获取第一个值insertintotestseq_zsm_00values(testseq_id_zsm_00.nextval,'test'||testseq_id_zsm_00.currval);commit;

4)删除序列dropsequence序列名;dropsequencetestseq_id_zsm_00;

--按照工资降序排序，显示第二页select*from(selectrownumrn,id,first_name,salaryfrom(selectid,first_name,salaryfroms_emporderbysalarydesc)whererownum<11)wherern>=6;----------------------------------------------------------------练习：1.创建一张表编号数字主键名字字符串非空入职日期日期工资数字不能低于7002.把s_emp表中的数据导入新建表中insertinto表名(字段列表)select语句;3.在新建表的名字字段上创建索引4.在新建表上创建视图，并测试对视图执行操作

--day05--PLSQL

3.SQL语言的特点机器语言汇编语言高级语言结构化语言只管做什么不管怎么做没有过程和控制结构没有算法描述能力

4.plsql扩充了sql1)变量和数据类型2)控制语句3)过程和函数4)对象类型和方法

6.plsql的开发工具sqlplus命令提示符的工具plsqldeveloper图形化的开发工具begindbms_output.put_line('HelloWorld!');end;/--打开输出功能setserveroutputon

7.标识符7.1作用给变量、数据类型、游标、过程、函数、触发器、包等命名7.2使用变量declare变量名数据类型;变量名数据类型:=值;begin变量名:=值;

8.变量和数据类型8.1数据类型1)标量类型numberbinary_integer数字类型charvarchar2字符串类型date日期类型boolean布尔类型2)复合类型recordtable3)参考类型refrefcursor(参考游标)4)大类型BLOB0-4gCLOB0-4gBFILE

8.2变量的修饰符变量名constant数据类型;变量名数据类型notnull;declare--(1)--var_idconstantnumber;var_idconstantnumber:=1;--(3)--var_namevarchar(20)notnull;var_namevarchar(20)notnull:='1';begin--(2)--var_id:=1;var_name:='test';dbms_output.put_line(var_id);dbms_output.put_line(var_name);end;/

(1)用constant修饰的变量必须初始化(2)用constant修饰的变量不允许赋值(3)用notnull修饰的变量必须初始化

任何类型的变量，在赋值前初值都是null

8.3使用binary_integer和boolean定义变量truefalsenulldeclarevar_idbinary_integer:=100;var_flagboolean;beginvar_flag:=true;ifvar_flagthendbms_output.put_line(var_id);endif;end;/

获取表中字段的数据类型：表名.字段名%type使用select语句给变量赋值：select字段列表into变量列表from表名where条件;注意：字段列表和变量列表的顺序、数量和数据类型要一致select语句有且只有一行结果declarevar_ids_emp.id%type;var_names_emp.first_name%type;beginselectid,first_nameintovar_id,var_namefroms_empwhereid=1;dbms_output.put_line(var_id||','||var_name);end;/

--接收表中的全部字段，record类型8.5.3用于接收表中整行数据的类型表名%rowtype--查询s_emp表中id=3的员工的全部信息，保存在变量中并输出declarevar_emps_emp%rowtype;beginselect*intovar_empfroms_empwhereid=3;dbms_output.put_line(var_emp.id||','||var_emp.first_name||','||var_emp.title);end;/

8.6.变量的作用域declare--全局var_anumber:=1;begindeclare--局部var_bnumber:=2;begindbms_output.put_line(var_a);dbms_output.put_line(var_b);end;dbms_output.put_line(var_a);--dbms_output.put_line(var_b);end;局部既可以访问局部变量，也可以访问全局变量全局只可以访问全局变量

局部变量和全局变量重名时,局部变量会覆盖同名的全局变量--局部访问同名的全局变量：使用标签定义标签：<<标签名>>使用标签：标签名.变量名

<>declare--全局var_anumber:=1;begindeclare--局部var_anumber:=2;begindbms_output.put_line(g.var_a);end;dbms_output.put_line(var_a);end;

9.控制语句9.1if语句9.1.1语法1)简单if语句if条件then操作endif;2)if...else语句if条件then操作1else操作2endif;3)多分支ifif条件1then操作1elsif条件2then操作2....else操作nendif;4)嵌入ifif条件1thenif条件2then操作1else操作2endif;elseif条件3then操作3else操作4endif;endif;

--方法二：declarevar_anumber:=&var_a;//后面这'&'是输入相当于c语言里的scanfvar_bnumber:=&var_b;var_cnumber:=&var_c;var_tempnumber;beginvar_temp:=var_a;ifvar_b>var_tempthenvar_temp:=var_b;endif;ifvar_c>var_tempthenvar_temp:=var_c;endif;dbms_output.put_line(var_temp);end;

9.1.3NULL的运算特点declarevar_anumber;var_bnumber;beginifvar_a>var_bthendbms_output.put_line('a>b');elsifvar_a

9.2循环语句循环变量初始化循环条件循环操作条件的更新

9.2.1简单循环1)语法loop--循环操作endloop;2)结束循环的方式--方式一：if退出循环的条件thenexit;endif;--方式二：exitwhen退出循环的条件;3)使用简单循环输出1..10declarevar_inumber:=1;beginloopdbms_output.put_line(var_i);exitwhenvar_i=10;var_i:=var_i+1;endloop;end;/--使用if退出循环declarevar_inumber:=10;beginloopdbms_output.put_line(var_i);ifvar_i=1thendbms_output.put_line('loopover!');exit;endif;var_i:=var_i-1;endloop;end;/9.2.2while循环1)语法while条件loop--循环操作endloop;2)使用while循环输出1..10declarevar_inumber:=1;beginwhilevar_i<=10loopdbms_output.put_line(var_i);var_i:=var_i+1;endloop;end;--练习：从1..100累加，输出和大于2000时对应的数字declarevar_inumber:=1;var_sumnumber:=0;beginwhilevar_i<=100loopvar_sum:=var_sum+var_i;ifvar_sum>=2000thendbms_output.put_line(var_i);exit;endif;var_i:=var_i+1;endloop;end;

9.2.3for循环智能循环1)语法for循环变量in区间loop--循环操作endloop;2)使用for循环输出1..10

beginforvar_iin1..10loopdbms_output.put_line(var_i);endloop;end;

--练习使用for循环输出10..1beginforvar_iin10..1loopdbms_output.put_line(var_i);endloop;end;--上边代码循环一次也不执行使用reverse实现倒序输出beginforvar_iinreverse1..10loopdbms_output.put_line(var_i);endloop;end;--使用退出循环的方式提前结束循环beginforvar_iin1..10loopdbms_output.put_line(var_i);ifvar_i=5then--var_i:=11;错误endif;endloop;end;

9.3跳转语句goto9.3.1语法<<标签名>>--这里必须有语句NULL;--空语句

goto标签名;

9.3.2使用goto退出嵌套循环beginforvar_iin1..3loopforvar_jin1..5loopdbms_output.put_line(var_j);ifvar_j=3thengotoouter;endif;endloop;endloop;<>NULL;end;

9.3.3使用if..exit方式退出循环begin<>//提到前面来比较好，能够一眼被一眼的看出来forvar_iin1..3loopforvar_jin1..5loopdbms_output.put_line(var_j);ifvar_j=3thenexitouter;endif;endloop;endloop;dbms_output.put_line('loopover');end;10.plsql中使用sql语句1)select语句select语句要和into配合使用select字段列表into变量列表from表名where条件;2)dml语句tcl语句可以直接在plsql中使用3)ddl语句不能直接在plsql中使用需要用动态sql实现

11.动态sql11.1概念把一条字符串对应的sql语句，当成真正的sql语句去执行11.2案例：创建一张表/*错误：ddl语句不能直接在plsql中使用*/begincreatetabletestdsql_zsm_00(idnumber);end;--在plsql中可以使用函数declaresqlstrvarchar2(100);beginsqlstr:='createtabletestdsql_zsm_00(idnumber)';sqlstr:=substr(sqlstr,1,length(sqlstr)-1)||',namevarchar2(20))';--dbms_output.put_line(sqlstr);executeimmediatesqlstr;end;

11.3DML语句的动态sql1)直接在plsql中使用dml和tcl语句begininsertintotestdsql_zsm_00values(1,'test1');commit;end;2)常规字符串的拼接declaresqlstrvarchar2(100);beginsqlstr:='insertintotestdsql_zsm_00values(2,''test2'')';executeimmediatesqlstr;commit;end;3)带有变量的字符串的拼接declarevar_idnumber:=3;var_namevarchar2(20):='test3';sqlstrvarchar2(100);beginsqlstr:='insertintotestdsql_zsm_00values('||var_id||','''||var_name||''')';--dbms_output.put_line(sqlstr);executeimmediatesqlstr;commit;end;

4)使用占位符配合using解决字符串拼接的问题占位符：:占位符名executeimmediatesqlstrusing变量列表;declarevar_idnumber:=4;var_namevarchar2(20):='test4';sqlstrvarchar2(100);beginsqlstr:='insertintotestdsql_zsm_00values(:b0,:b1)';executeimmediatesqlstrusingvar_id,var_name;commit;end;

11.4select语句动态sql必须是普通的select语句(不带into)select语句有且只有一行结果declaresqlstrvarchar2(100);var_names_emp.first_name%type;beginsqlstr:='selectfirst_namefroms_empwhereid=1';executeimmediatesqlstrintovar_name;dbms_output.put_line(var_name);end;

-----------------------------------------------------------------总结：1.变量和类型定义和使用record和table类型type类型名isrecord(字段类型,....字段类型);

type类型名istableof元素类型indexbybinary_integer;

表名.字段%type表名%rowtype

2.控制语句分支：if循环：简单循环、while循环、for循环跳转语句：goto结束循环的两种方式：exitwhen退出循环的条件;

if退出循环的条件thenexit;endif;3.动态sqlsqlstrvarchar2(200):='';executeimmediatesqlstr;

'insertinto表名values(:b0,:b1,....)'executeimmediatesqlstrusing变量1,变量2,....;

2.使用动态sql语句，删除testdsql_zsm_00表中指定id的信息

--day06--回顾：

1.5游标的属性游标名%属性found当提取数据时，如果提取到了新数据，则为真没有提取到新数据，则为假如果在open之前，返回非法游标如果在fetch之前，返回nullnotfound当提取数据时，如果提取到了新数据，则为假没有提取到新数据，则为真如果在open之前，返回非法游标如果在fetch之前，返回nullisopen游标是否处理打开状态打开时返回真，没有打开返回假已经打开的游标不能再次打开已经关闭的游标不能再次关闭rowcount游标的偏移量如果没有open，返回非法游标

1.8使用for循环遍历游标智能循环（自动打开游标、提取数据、关闭游标）declarecursorempcursorisselect*froms_emp;beginforvar_empinempcursorloopdbms_output.put_line(var_emp.id||','||var_emp.first_name||','||var_emp.salary);endloop;end;

案例：使用参考游标遍历s_emp表中的全部数据declaresqlstrvarchar2(100);typeemprefcursorisrefcursor;var_empcursoremprefcursor;var_emps_emp%rowtype;beginsqlstr:='select*froms_emp';openvar_empcursorforsqlstr;loopfetchvar_empcursorintovar_emp;exitwhenvar_empcursor%notfound;dbms_output.put_line(var_emp.id||','||var_emp.first_name||','||var_emp.salary);endloop;closevar_empcursor;end;带有占位符的sql语句declaresqlstrvarchar2(100);typeemprefcursorisrefcursor;var_empcursoremprefcursor;var_emps_emp%rowtype;beginsqlstr:='select*froms_empwhereid>:b0';--using后可以使用变量，也可以使用值。一般用变量openvar_empcursorforsqlstrusing10;loopfetchvar_empcursorintovar_emp;exitwhenvar_empcursor%notfound;dbms_output.put_line(var_emp.id||','||var_emp.first_name||','||var_emp.salary);endloop;closevar_empcursor;end;

2.PLSQL中的异常2.1系统预定义异常Oracle系统自身为用户提供的、可以在plsql中使用的预定义异常，用于检查用户代码失败的一般原因。系统预定义异常由系统定义和引发用户只需要根据名字捕获和处理异常

案例：declarevar_namevarchar2(25);var_idnumber:=&id;beginselectfirst_nameintovar_namefroms_empwhereid=var_id;selectfirst_nameintovar_namefroms_empwherefirst_namelike'M%';dbms_output.put_line(var_name);exceptionwhenno_data_foundthendbms_output.put_line('noemp');whenothersthendbms_output.put_line(sqlcode||'.....'||sqlerrm);end;

常用的异常：no_data_found:select..into语句没有返回行too_many_rows:select..into语句返回多于一行的结果集invalid_cursor:非法游标cursor_already_open:游标已打开dup_val_on_index:唯一索引对应的列上有重复值zero_divide:除数为0

案例：处理多个异常declarevar_namevarchar2(25);var_idnumber:=&id;beginselectfirst_nameintovar_namefroms_empwhereid=var_id;selectfirst_nameintovar_namefroms_empwherefirst_namelike'M%';dbms_output.put_line(var_name);exceptionwhenno_data_foundthendbms_output.put_line('noemp');whentoo_many_rowsthendbms_output.put_line('toomanyrows');whenothersthendbms_output.put_line(sqlcode||'.....'||sqlerrm);end;

2.2自定义异常2.2.1定义异常的步骤：1)定义异常异常名exception;2)根据条件引发异常if引发异常的条件thenraise异常名;endif;3)捕获和处理异常when异常名then处理异常2.2.2案例：更新员工表中指定员工的工资，员工不存在时提示异常declarevar_ids_emp.id%type:=&id;/*定义异常*/no_resultexception;beginupdates_empsetsalary=salary+500whereid=var_id;ifsql%notfoundthenraiseno_result;endif;commit;exceptionwhenno_resultthendbms_output.put_line('noresult');end;

隐式游标：在执行一个sql语句时，oralce会自动创建一个隐式游标。这个游标是内存中为处理该条sql语句的工作区。隐式游标只要用于处理数据操作语句(insert、delete、update)的执行结果。隐式游标也有属性，使用属性时需要用到游标名---sql

3.存储过程procedure3.1匿名块和有名块匿名块：匿名块不保存在与数据库中每次使用都要进行编译不能在其他块调用有名块：可以存储在数据库中可以在任何需要的地方调用

有名块包括：procedurefunctionpackagetrigger存储过程函数包触发器

3.2存储过程的创建语法：create[orreplace]procedure过程名[(参数列表)]is|as--临时变量beginexceptionend;3.3无参的过程的创建和调用1）创建：输出两数中的较大值createorreplaceproceduregetmax_zsm_00isvar_anumber:=10;var_bnumber:=100;beginifvar_a>var_bthendbms_output.put_line(var_a);elsedbms_output.put_line(var_b);endif;end;

2)调用无参的存储过程begingetmax_zsm_00;end;3.4带参的存储过程3.4.1使用的注意事项1)参数的数据类型不能包含长度或精度的修饰2)参数可以有多种模式，并且可以有默认值参数名{[in]|out|inout}类型[{:=|default}值]参数的模式：in输入参数负责向过程传入值默认模式实参是值、赋值后的变量在过程内不能赋值out输出参数负责传出值实参必须是变量，不必赋值在过程内必须赋值inout输入输出参数既负责传入，又负责传出实参是赋值后的变量在过程内可以赋值只有in模式的参数才可以有默认值3.4.2案例：创建一个带参的存储过程，传入两个数字，输出最大值createorreplaceproceduregetmax_zsm_00(var_ainnumber:=10,var_bnumber)isbeginifvar_a>var_bthendbms_output.put_line(var_a);elsedbms_output.put_line(var_b);endif;end;

3.4.3带参的存储过程的调用declarevar_xnumber:=123;var_ynumber:=12;begingetmax_zsm_00(1,10);getmax_zsm_00(var_x,100);getmax_zsm_00(var_x,var_y);--getmax_zsm_00(1);end;

3.4.4参数赋值方式1)按照位置赋值2)按照名字赋值参数名=>值declarevar_xnumber:=123;var_ynumber:=12;begingetmax_zsm_00(1,10);getmax_zsm_00(var_x,100);getmax_zsm_00(var_b=>10,var_a=>200);getmax_zsm_00(var_b=>1);end;3.4.5练习：创建一个存储过程，传入两个数字，输出最大值，同时把两数之和保存在第二个变量，并测试createorreplaceproceduregetmax_zsm_00(var_ainnumber,var_binoutnumber)isbeginifvar_a>var_bthendbms_output.put_line(var_a);elsedbms_output.put_line(var_b);endif;var_b:=var_a+var_b;end;

--调用declarevar_xnumber:=100;begingetmax_zsm_00(10,var_x);dbms_output.put_line(var_x);end;

3.5查看存储过程desc过程名;

descuser_source;

4.函数function4.1plsql中的函数和过程的区别1)关键字不同，过程是procedure函数是function2)过程没有返回类型和返回值，函数有返回类型和返回值3)调用方式不同过程在plsql中是直接调用函数在plsql中需要组成表达式调用(使用变量接收返回值、作为函数或过程的参数)4.2语法create[orreplace]function函数名[(参数列表)]return数据类型is|as--临时变量begin--必须有return语句end;

4.3创建一个函数，传入两个数字，返回最小值createorreplacefunctiongetmin_zsm_00(var_anumber,var_bnumber)returnnumberisbeginifvar_a

declarevar_xnumber:=100;var_ynumber:=1;var_resultnumber;beginvar_result:=getmin_zsm_00(var_x,var_y);dbms_output.put_line(var_result);dbms_output.put_line(getmin_zsm_00(123,10));end;

--定义一个包，包含一个过程和一个函数createorreplacepackagemypackage_zsm_00isproceduregetmax(var_anumber,var_bnumber);functiongetmin(var_anumber,var_bnumber)returnnumber;end;

2)定义包的主体(包的实现)create[orreplace]packagebody包名is--函数、过程的实现end[包名];

createorreplacepackagebodymypackage_zsm_00isproceduregetmax(var_anumber,var_bnumber)isbeginifvar_a>var_bthendbms_output.put_line(var_a);elsedbms_output.put_line(var_b);endif;end;

functiongetmin(var_anumber,var_bnumber)returnnumberisbeginifvar_a

--测试declarevar_xnumber:=11;var_ynumber:=1234;var_resnumber;beginmypackage_zsm_00.getmax(var_x,var_y);var_res:=mypackage_zsm_00.getmin(var_x,var_y);dbms_output.put_line(var_res);end;

6.3语句级触发器emp_zsm_00

createorreplacetriggertri_emp_zsm_00beforeupdateonemp_zsm_00declarebegindbms_output.put_line('tableupdated');end;

updateemp_zsm_00setsalary=salary+100whereid=1;updateemp_zsm_00setsalary=salary+100whereid<1;updateemp_zsm_00setsalary=salary+100whereid>1;

6.4行级触发器createorreplacetriggertri_emp_zsm_00beforeupdateonemp_zsm_00foreachrowdeclarebegindbms_output.put_line('tableupdated');end;updateemp_zsm_00setsalary=salary+100whereid=1;updateemp_zsm_00setsalary=salary+100whereid<1;updateemp_zsm_00setsalary=salary+100whereid>1;标识符::old原值标识符:new新值标识符表名%rowtype类型

insert:newupdate:old:newdelete:oldcreateorreplacetriggertri_emp_zsm_00beforeupdateonemp_zsm_00foreachrowdeclarebegindbms_output.put_line('tableupdated');dbms_output.put_line('old:'||:old.id||','||:old.salary);dbms_output.put_line('new:'||:new.id||','||:new.salary);end;updateemp_zsm_00setsalary=salary+100whereid=1;updateemp_zsm_00setsalary=salary+100whereid<1;updateemp_zsm_00setsalary=salary+100whereid>1;

注意：触发器中不能包含任何有关事务的操作事务谁发起谁结束

6.5使用触发器产生主键的值--创建一个表createtabletesttrigger_zsm_00(idnumberprimarykey,namevarchar2(20));--创建一个序列createsequencetrigger_id_zsm_00;--创建触发器createorreplacetriggertri_pk_zsm_00beforeinsertontesttrigger_zsm_00foreachrowbeginselecttrigger_id_zsm_00.nextvalinto:new.idfromdual;end;

--插入一条语句insertintotesttrigger_zsm_00(name)values('test1');

-----------------------------------------------------------------练习：1.使用游标遍历s_dept表中的全部数据2.创建一个存储过程，传入数字n(大于1的整数)，计算1..n的累加和，保存在第二个参数，并测试。

--day07--PROC主要内容：1)proc简介2)proc程序的开发过程3)宿主变量和指示变量4)嵌入sql语句5)连接数据库6)错误处理7)数据的存取更新操作8)动态sql-----------------------------1.什么是pro程序？1.1概念在过程化的编程语言中嵌入sql语句而开发出的应用程序，叫pro程序。在通用的编程语言中嵌入的sql语句称为嵌入式sql被嵌入了sql语句的编程语言称为宿主语言1.2proc/c++在c/c++语言中嵌入sql语句而开发出的应用程序，称为proc/c++程序目的：是C/C++这种高效的语言成为访问oracle数据库的工具

intmain(void){/*连接数据库*/execsqlconnect:用户名/密码;

/*操作数据库：比如查询*/execsqlselect字段列表into变量列表from表名where条件;

/*关闭并释放连接资源*/execsqlcommitworkrelease;execsqlrollbackworkrelease;}

3.C程序的开发步骤1)编写源程序vixxx.c2)编译、链接gccxxx.c-lxxxxx3)运行./a.out4.proc程序的开发步骤1)编写源程序vixxxx.pc2)预编译

procxxx.pc----->xxx.c3)编译、链接gccxxx.c-lclntsh--linuxgccxxx.c-lorasql10(11)--windows4)运行./a.out

案例：first.pcvifirst.pcprocfirst.pcgccfirst.c-lclntsh./a.out

5.宿主变量5.1概念proc中C语言称为宿主语言在宿主语言中定义，即可以在宿主语言中使用，也可以在sql语句中使用的变量，称为宿主变量。5.2宿主变量的数据类型char字符型charvar[n]定长字符串shortint整型longfloatdouble浮点型varcharvar[n]变长字符串5.3定长字符串和变长字符串5.3.1定长字符串字符串的长度不足时用空格补齐案例：charn.pc5.3.2变长字符串案例：varcharn.pc

charname[25]---->varcharname[25]预编译时，varchar类型的数组被翻译成了同名的结构：struct{unsignedshortlen;unsignedchararr[25];}name;varchar类型的数组在sql语句中使用时，和char型数组一样在宿主语言中使用时，按照结构的用法：提取字符串的值：name.arr获取字符串的长度：name.len

使用varchar类型的数组时，可以会产生垃圾数据，解决方式：1)字符串进行初始化，把数组的所有元素初始化为'\0'varcharname[25]={0};2)手动为字符串添加结束标志name.arr[name.len]='\0';

5.3.3使用预编译选项解决变长字符串的问题oname相当于gcc的-ochar_map=charz:处理成定长，空格补齐，'\0'结尾=varchar2|charf:处理成定长，空格补齐，不以'\0'结尾=string:处理成变长，以'\0'结尾proccharn.pcchar_map=stringgcccharn.c-lclntsh./a.out

6.2语法指示变量的数据类型必须是shortshortindid;shortindname;execsqlselectid,first_nameinto:id,:namefroms_empwhereid=1;

execsqlselect字段1,字段2into:宿主变量1[indicator]:指示变量,宿主变量2from表名where条件;execsqlselectid,first_nameinto:id:indid,:name:indnamefroms_empwhereid=1;

6.3案例：把s_emp表中id=1的员工的id,first_name,manager_id查询出来，保存到相应的宿主变量，同时使用指示变量指示赋值状态。案例：indvar.pc

7.数组变量7.1数组变量使用的注意事项1)除了字符型外，其他类型的数组只能使用一维的intids[50];charname[50][25];2)不支持数组指针3)最大元素个数327674)在select语句中使用数组变量时，只能给出数组的名字，不能使用下标5)如果要指示多个变量的赋值状态，可以使用指示变量数组

7.2把s_emp表中的所有员工的id,first_name和manager_id查询出来，使用合适的数组接收查询结果，并使用指示变量数组指示manager_id的赋值状态案例：arr_var.pc

案例：sqlca.pc

9.oraca通信区一个类似于sqlca的结构，可以作为sqlca通信区的补充。当需要获取更为详细的状态信息，可以使用oraca.oraca通信区对sqlca的补充可以使用oraca获取执行的sql语句的文本

oraca的使用步骤：1)包含oracaexecsqlincludeoraca;2)打开oracaoptionexecoracleoption(oraca=yes);3)设置sql文本的保存标志oraca.orastxtf0：默认值不保存1：sql语句出错时保存2：sql语句出现错误或警告时保存3：都保存4)获取sql文本oraca.orastxt.orastxtc

案例：oraca.pc

10.proc中如何使用sql语句1)select语句在语句前加execsql，配合into使用execsqlselect字段列表into宿主变量列表from表名where条件;2)dml语句(insertdeleteupdate)ddl语句(createdropalter)tcl语句(commitrollbacksavepoint)直接在语句前加execsql注意：ddl语句中不能使用宿主变量

综合案例：sql.pc开发一个超小型的学生管理系统，完成以下功能：创建学生信息表添加学生信息显示学生信息列表根据学号更改信息根据学号删除信息循环菜单：学生管理系统，请选择：1.创建表2.学生注册3.查看4.修改信息5.删除信息0.退出

练习：

--day08--1.proc中如何使用plsql1.1使用plsql的语法execsqlexecutebegin/*相当于plsql的匿名块*/end;end-exec;

在预编译时，需要加如下两个选项：sqlcheck=semanticsuserid=用户名/密码：在预编译时时，检查调用的过程、函数等子程序是否存在及合法

1.2在proc中调用存储过程1)创建一个存储过程，传入两个参数，把两数之和存入第二个参数createorreplaceproceduregetsum_zsm_00(var_anumber,var_binoutnumber)isbeginvar_b:=var_a+var_b;end;

2)在proc中调用存储过程案例：callproc.pc

1.3在proc中使用函数1)创建一个函数，传入两个数字，返回最大值createorreplacefunctiongetmax_zsm_00(var_anumber,var_bnumber)returnnumberisbeginifvar_a>var_bthenreturnvar_a;endif;returnvar_b;end;2)编写一个proc程序，调用该函数案例：callfunc.pc

2.连接数据库2.1本地数据库连接execsqlconnect:用户名/密码;execsqlconnect:用户名identifiedby:密码;2.2远程连接数据库需要提供一个远程连接的描述字符串，包括：ip地址、端口号、oracle数据库的服务ID(服务器名)这个字符串有固定格式，程序员可以根据格式拼接字符串a文件中，一般有远程连接的$ORACLE_HOME/network/admin/tnsnames.or描述字符串的配置$ORACLE_HOME:环境变量，oracle的安装路径ip地址是要访问的数据库所在的服务器的ip地址端口号默认1521

有了远程数据库的描述字符串，使用using引入描述：execsqlconnect:用户名/密码using:描述字符串;案例：rdbconn.pc

如果面对的是多个数据库，可以他通过定义标签，然后使用标签区分访问的是哪个数据库。案例：mdbconn.pc

3.proc中的错误处理3.1局部的错误处理方式sqlca.sqlcodesqlca.sqlerrm.sqlerrmc3.2全局的错误处理方式错误处理方案只写一次，出现匹配的类型的错误，自动查找错误处理方案并执行execsqlwhenever条件动作;条件就是错误的方式，有三种：sqlerror:sql语句错误notfound:没有找到数据sqlwarning:sql语句出现警告(不常用)动作就是错误处理方案，包括：do错误处理函数():条件发生后，调用错误处理函数dobreak:退出循环continue:继续stop:停止执行代码(不常用)goto标签:跳转到标签处(不常用)

3.3举例：删除一张不存在的表案例：sqlerror.pcproc中默认对错误是忽略的(不提示、不中断)sql语句出现错误，会向上查找对应的全局处理语句，如果有，执行处理方案。如果没有，继续执行后面的代码。

4.proc中的数据处理1)单行单列的结果集使用一个宿主变量解决....charname[25];.....execsqlselectfirst_nameinto:namefroms_empwhereid=1;.....2)单行多列的结果集使用多个宿主变量或结构体变量解决execsqlselectid,first_name,salaryinto:id,:name,:salfroms_empwhereid=1;

案例：selecta.pc

3)多行单列的结果集使用一个数组解决execsqlselectfirst_nameinto:namesfroms_emp;

4)多行多列的结果集使用多个数组、结构体数组、游标解决execsqlselectid,name,region_idinto:ids,:names,:ridsfroms_dept;练习：使用多个数组接收s_dept表中的全部信息，并循环输出。添加必要的错误处理

案例：selectb.pc

使用游标时，结束循环采用：execsqlwhenevernotfounddobreak;

案例：selectc.pc

execsqlfetchfirst游标名into:宿主变量

案例：cpselectc.pcscrollcursor.pc

5.动态sqlsql在执行时，才由参数传入1)非select语句，没有占位符charsqlstr[100]="createtable....";execsqlexecueimmediate:sqlstr;

案例：dsqla.pc

2)非select语句，有占位符charsqlstr[100]="insertinto.....values(:b0,:b1...)";execsqlpreparestmtfrom:sqlstr;execsqlexecutestmtusing:宿主变量....;

案例：dsqlb.pc

3)select语句charsqlstr[100]="select....from...whereid>:b0";execsqlpreparestmtfrom:sqlstr;execsqldeclare游标名cursorforstmt;execsqlopenstmtusing:ids,:names;....案例：dsqlc.pc

----------------------------------------------------------------------总结：1.sqlselectselect..from表达式别名null值：nvlwhere子句比较运算符betweenandinlike_%escape'\'isnull逻辑运算符：andornotorderby子句orderby排序标准排序方式,排序标准排序方式groupby子句select后的字段必须是分组标准，或者是组函数having子句分组后的筛选单行函数to_dateto_char组函数表连接select字段列表from表1,表2where关联条件[and筛选条件]；(+):oracle中外连接

select字段列表from表1{{left|right|full}[outer]|[inner]}join表2on关联条件[where筛选条件];

子查询whereselecthavingfrom

ddl:tablesequenceviewindexdmlinsertdeleteupdate

tclACID:约束分页

2.plsql变量数据类型：record、table、refcursor表名.字段%type表名%rowtype流程控制动态sql游标异常有名块过程函数包触发器

3.proc宿主变量指示变量数组变量通信区嵌入sql、plsql语句连接数据库错误处理select结果集的处理动态sql

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