视图sql挂起

在PL/SQL中使用SQLSQL基本命令分类语句SQL语句可以分为六类数据操纵语言:select,delete,update,insert,settransaction,explainplan数据定义语言:drop,create,alter,grant,revoke事务控制:commit,rollback,savepoint会话控制:altersession,setrole系统控制:altersystem嵌入式SQL:connect,declarecursor,allocatePL/SQL使用静态联编,所以DDL语句被禁止使用静态联编:是指联编工作是在程序编译连接阶段进行的,这种联编又称为早期联编;因为这种联编是在程序开始运行之前完成的.
动态联编:编译程序在编译阶段并不能确切地知道将要调用的函数,只有在程序执行时才能确定将要调用的函数,为此要确切地知道将要调用的函数,要求联编工作在程序运行时进行,这种在程序运行时进行的联编工作被称为动态联编,或动态束定,又叫晚期联编例:考虑下面的PL/SQL块Begincreatetablemy_table1(idnumber,namevarchar2(8));Insertintomy_table1(id,name)Values(1,'hello!
');End;该块不能运行.
DML和事务控制语句是唯一的不会修改数据模式对象或对其操作的特权的SQL语句,是唯一可在PL/SQL中运行的SQL语句.
使用DDL是非法的.
使用DDL:DBMS_SQL允许运行时刻动态地创建SQL,并分析执行他.

5.
2数据定义语言SQL命令功能Alterprocedrue修改存储过程Altertable增加表列、重定义表列、更改存储分配Analyze收集数据库对象的性能统计值并送入基于代价的优化器Altertableaddconstraint在已有的表上增加约束Createprocedure创建存储过程Createtable创建表Createindex创建索引Dropindex删除索引Droptable删除表Grant授权Truncate删除表中的所有行revoke收回权限5.
3数据操纵语言DML在SQL语句中允许在使用表达式的地方使用变量.
例:Declarev_numcreditsclasses.
num_credit%type;Beginv_numcredits:=3;updateclassessetnum_credits:=v_numcreditswheredepartment='His'andcourse='101';End;并非所有的SQL中的实体都可以用变量替换,只有表达式是可以的,表名和列名必须都要知道.
SELECT:语法:Selectselect_list_itemintopl_sql_record/variablefromtablewherewhere_clause;这里select只返回一条记录,如果多于一条,则出错,应该用游标.
例:Declarev_studentrecordstudents%rowtype;v_departmentclasses.
department%type;v_courseclasses.
course%type;Beginselect*intov_studentrecordfromstudentswhereid=10000;Selectdepartment,courseintov_department,v_coursefromclasseswhereroom_id=99997;End;字符比较:当两个字符进行比较的时候,oracle可以使用填充格式和非填充格式进行比较.
填充格式使用下面的语法(string1和string2比较)1)如果string1和string2长度不同,则在较短的字符串上填充空格,使得两个字符串的长度相同.
2)逐个字符比较字符串,从左边开始,假设string1的字符是char1,string2的字符是char2.
3)如果ASCII(CHAR1)ASCII(CHAR2),则string1>string2.

如果ASCII(CHAR1)=ASCII(CHAR2),则继续比较下应该字符.
4)如果达到了字符串的末尾,则认为两个字符串是相同的.
使用填充格式比较语意,则下面的条件都返回TRUE.
'abc'='abc''abc'='abc''ab''abcc'非填充格式的比较算法如下:1)逐个字符比较字符串,从左边开始,假设string1的字符是char1,string2的字符是char2.
2)如果ASCII(CHAR1)ASCII(CHAR2),则string1>string2.

如果ASCII(CHAR1)=ASCII(CHAR2),则继续比较下一个字符.
3)如果达到了string1的末尾,但是没有达到string2的末尾,那么string1string2.

使用非填充格式比较语意,则下面的条件都返回TRUE.
'abc'='abc''ab'abcc'但是,下面的比较将返回FALSE'abc'='abc'仅当两个比较的字符串具有固定的长度时,才使用填充格式比较语意,如果其中一个是变长的,就会使用非填充格式比较语意.
Char是定长的,varchar2是变长的,字符文字被认为是定长的.
例:下面的块不会删除任何行,因为,department是char类型,而v_department是varchar2类型.
Declarev_departmentvarchar2(3);Beginv_department:='CS';deletefromclasseswheredepartment=v_department;End;为了确保where子句带来期望的结果,一定要使变量与要比较的数据库列有相同的类型,用%type可以保证这一点.
表引用所有的DML都对指向表的引用进行处理,这个引用通常会采用如下的形式:[schema.
]table[@dblink]schema标识该表的拥有者,Dblink标识远程数据库中的一个表.
为了建立一个数据库连接,应该给出特定模式的用户名和口令.
在会话中其他后继的SQL语句将缺省地指向这个数据模式.
如果没有指明数据模式,那么给出的表应该是缺省数据模式下的一个表.
缺省地,数据模式是在执行SQL语句以前连接的数据模式.
如果该表是在另一个数据模式中,可以指明数据模式来标识他.
如:Updateexample.
studentssetmajor='music'whereid=10005;数据链接:数据库链接是指向远程数据库的引用,该数据库可以位于与本地数据库完全不同的系统中.
下面的DDL语句会创建一个数据库链接.
Createdatabaselinklink_nameconnecttousernameidentifiedbypasswordusingsql_string;Link_name跟在用于数据库标识符的通用规则之后.
Sql_string是一个有关远程数据库的有效连接字符串.
例:Createdatabaselinkexample_bakkupbackupconnecttoexampleindentifiedbyexampleusing'backup_database';在进行了前面的连接之后,可以使用以下语句对student表进行远程更新.
Updatestudents@example_backupsetmajor='Music'whereid=10005;当有关数据库连接被用作某个事务的一部分时,该事务被称作分布式事务.
替代名:可以为复杂的表引用创建替代名.
语法如下:Createsynonymsynonym_nameforreference;这里reference可以是一个表、过程、序列或其他的数据库对象.
例:Createsynonymbackup_studentsforStudent@example_backup;有了上面的替代名以后,可以重写前面的分布式语句.
Updateback_studentssetmajor='Music'whereid=10005;伪列:是仅能从SQL语句中进行调用的其他函数.
是作为SQL语句的一部分进行求值和被执行的.
Currval和nextval:和序列一起用.
Currval返回序列的当前值,nextval返回序列的下一个值.
Rowid:返回特定行的标识符例:selectrowidfromrooms;Rownum:返回当前行的序号,第一行的序号为1,第二行的序号为2.
例:Select*fromstudentswhererownum3;**where作用于基本表或视图having作用于组,从中选择满足条件的组连接查询等值连接与非等值连接[表名1].
[表名2.
]或[表名1].
between[表名2.
]and[表名3.
]当连接运算符为=时,成为等值连接例1:Selectstudent.
*,sc.
*fromstudent,scwherestudent.
sno=sc.
sno;例2:在上例的等值连接中,除去重复列,为自然连接Selectstudent.
sno,sname,ssex,sdept,cno,gradefromstudent,scWherestudent.
sno=sc.
sno;自身连接:一个表与自己连接例:查询每一门课的间接先修课Selectfirst.
cno,second.
cpnofromcoursefirst,coursesecondWherefirst.
cpno=second.
cno;外连接例:列出student表每个学生的基本情况及其选课情况,若某个学生没有选课,只输出其基本情况信息,其选课信息为空值.
Selectstudent.
sno,sname,ssex,sage,sdept,cno,gradeFromstudent,scWherestudent.
sno(+)=sc.
sno;此例为左连接+号出现在右边为右连接复合条件连接:where子句中有多个连接条件例1:查询选修2号课程且成绩在90分以上的所有学生Selectstudent.
sno,snamefromstudent,scWherestudent.
sno=sc.
snoandsc.
cno='2'andsc.
grade>90;例2:查询每个学生的学号、姓名、选修的课程名及成绩Selectstudent.
sno,sname,cname,gradefromstudent,sc,courseWherestudent.
sno=sc.
snoandsc.
sno=course.
cno;嵌套查询在SQL语言中一个SELECT–FROM–WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询例:selectsnamefromstudent--外查询wheresnoinselectsnofromscwherecno='2';--内查询**内查询的select语句中不能使用orderby子句.
嵌套查询的求解方法为由里向外带有IN谓词的子查询例1:查询与刘晨在同一个系学习的学生SelectsdeptfromstudentWheresdeptin(selectsdeptfromstudentwheresname='刘晨');本例也可用自身连接来完成:Selects1.
sno,s1.
sname,s1.
sdeptfromstudents1,students2Wheres1.
sdept=s2.
sdeptands2.
sname='刘晨';也可将内外查询的表用不同的别名区分Selectsno,sname,sdeptfromstudents1Wheres1.
sdeptin(selectsdeptfromstudents2wheres2.
sname='刘晨');例2:查询选修了课程名为信息系统的学生学号和姓名Selectsno,snamefromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname='信息系统'));本查询也可以用连接查询实现:Selectsno,snamefromstudent,sc,coursewherestudent.
sno=sc.
snoandsc.
cno=course.
cnoandcourse.
cname='信息系统';*以上两例中的子查询都只执行一次,其结果用于父查询.
子查询的查询条件不依赖于父查询,这类子查询称为不相关子查询.
带有比较运算符的子查询:父查询和子查询之间用比较运算符进行连接例:查询与刘晨在同一个系学习的学生子查询的结果是一个值时,可以用=代替INSelectsno,sname,sdeptfromstudentwheresdept=selectsdeptfromstudentwheresname='刘晨');**子查询一定要跟在比较运算符之后,下列写法是错误的:Selectsno,sname,sdeptfromstudentwhere(selectsdeptfromstudentwheresname='刘晨')=sdept带有ANY或ALL完成的子查询例1:查询其它系中比信息系某一学生年龄小的学生姓名和年龄Selectsname,sagefromstudentwheresage'IS';本例也可以用集函数实现selectsname,sagefromstudentwheresage'IS';例2:查询其它系中比信息系所有学生年龄小的学生姓名和年龄Selectsname,sagefromstudentwheresage'IS';本例也可以用集函数实现selectsname,sagefromstudentwheresage'IS';**allmax>anymin带有EXISTS谓词的子查询EXISTS代表存在.
带有EXISTS的子查询不返回任何数据,只产生"true"或"false"例1:查询所有选修了1号课程的学生姓名Selectsnamefromstudentwhereexists(select*fromscwheresno=student.
snoandcno='1');此子查询为相关子查询,求解时,依赖于父查询.
首先取外层查询表中的一个元组,根据他与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表中;然后再取父查询表的下一个元组;继续这一过程,直到父查询表结束.
例2:查询没有选修1号课程的学生姓名Selectsnamefromstudentwherenotexists(select*fromscwheresno=student.
snoandcno='1');带有EXISTS的子查询不能被其它形式的子查询等价替换,但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换例3:查询与刘晨在同一个系学习的学生Selectsno,sname,sdeptfromstudents1whereexists(select*fromstudents2wheres2.
sdept=s1.
sdeptands2.
sname='刘晨');**用存在量词代替全称量词(对于任意x)P==not(existsx(notP))例4:查询选修了全部课程的学生姓名selectsnamefromstudentwherenotexists(select*fromcoursewherenotexists(select*fromscwheresno=student.
snoandcno=course.
cno));**P->Q===notPorQ例5:查询至少选修了学生95002选修的全部课程的学生号码.
本题可表达为:查询学号为x的学生,对于所有的课程y,只要95002学生选修了课程y,则x也选修了y.
P:表示学生95002选修了课程yQ:表示学生X选修了课程y因此上述查询可转换为:Notexistsy(PandnotQ)用SQL语言表达如下:Selectdistinctsnofromscscxwherenotexists(select*fromscscywherescy.
sno='95002'andnotexists(select*fromscsczwherescz.
sno=scx.
snoandscz.
cno=scy.
cno));集合查询UNION并运算语法:select子句1UNIONselect子句2〔orderby子句〕注意:(1)两个子句都不能包含orderby子句,orderby子句是用来指定全部UNION操作结果的顺序的(2)两个子查询的列数和列的类型应相同(3)orderby子句中指定的排序必须用select列表中列的位置序号例1:查询计算机科学系的学生及年龄不大于19岁的学生Select*fromstudentwheresdept='CS'UNIONselect*fromstudentwheresage19;数据更新插入数据插入单个元组语法:insertinto表名[属性1,属性2,values(常量1,常量2,.
.
);注意:在表定义时说明了NOTNULL的属性列不能去空值.
例1:将一个新学生记录插入到student表中Insertintostudentvalues('95020','陈冬','男','IS',18);例2:插入一条选课记录('95020','1')Insertintosc(sno,cno)Values('95020','1');**新插入的记录在grade列上为空值.
插入子查询结果例:对每一个系,求学生的平均年龄,并把结果存入数据库.
Createtabledeptage(sagechar(15),avgagenumber(2));Insertintodeptage(sdept,avgage)selectsdept,avg(sage)fromstudentgroupbysdept;修改数据修改某一个元组的值语法:update表名set列名=,=,…[where条件];例:将学生95001的年龄改为22岁Updatestudentsetsage=22wheresno='95001';修改多个元组的值例:将所有学生的年龄增加1岁Updatestudentsetsage=sage+1;带子查询的修改语句语法:update表名set(列名1,列名2,…)=(select列名A,列名B,…from表名2where条件)Whereupdate条件;**括号的位置非常重要例:Updatemonthly_salesset(sales_amount)=(selectsum(sales_amount)fromdaily_saleswheresale_datebetweento_date('01-JAN-99'andto_date('31-JAN-99'))Wheresales_month=to_date('JAN-99','MON-YY');使用相关子查询语法:update表名1set(列名1,列名2,…)=(select列名a,列名b,…from表名2where表名2.
列x=表名1.
列Iand条件)Where条件例:Updatetype_of_seaweedsetper_pound=2*per_pound,last_change_date=sysdatewhereedible='edible';Updateseaweed_samplessset(selling_price)=(selectts.
per_pound*20fromtype_of_seaweedtswherets.
type_id=ss.
type_idandedible='edible')Wheress.
type_idin(selectts1.
type_idfromtype_of_seaweedts1wherets1.
edible='edible');删除数据语法:deletefrom表名[where条件];删除某一个元组的值例:删除学号为95019的学生的记录Deletefromstudentwheresno='95019';删除多个元组的值例:删除所有学生选课记录Deletefromsc;带子查询的删除语句例:Deletefromseaweed_samplewheretype_idin(selecttype_idfromtype_of_seaweedwhereedible='inedible');视图定义视图建立视图语法:createview视图名[列名,列名…]as子查询[withcheckoption];Withcheckoption表示对视图进行update、insert、delete时:要保证满足视图定义的条件注意:在以下情况下必须指明组成视图的所有列名(1)某个目标列不是单纯的属性名,而是集函数或表达式(2)多表连接时选出了几个同名的列作为视图的字段(3)需要在视图中为列启用其它的名字例1:建立信息系学生的视图Createviewis_studentasselectsno,sname,sagefromstudentwheresdept='IS';例2:例1:建立信息系学生的视图,并要求进行修改和插入操作时仍需保证视图只有信息系的学生Createviewis_studentasselectsno,sname,sagefromstudentwheresdept='IS'WITHCHECKOPTION;*只从单个基本表导出的视图为行列子集视图.
例3:建立信息系选修了1号课程的学生的视图Createviewis_s1(sno,sname,grade)asselectstuden.
sno,sname,gradefromstudent,scwheresdept='IS'andstudent.
sno=sc.
snoandsc.
cno='1';例4:建立信息系选修了1号课程且成绩在90分以上的学生的视图CreateviewIS_S2asselectno,sname,gradefromis_s1wheregrade>=90;例5:定义一个反映学生出生年份的视图Createviewbt_s(sno,sname,sbirth)asselectsno,sname,2002-sagefromstudent;例6:将学生的学号及他的平均成绩定义为一个视图Createviews_g(sno,gavg)asselectsno,avg(grade)fromscgroupbysno;例7:将student表中的所有女生记录定义为视图Createviewf_student(stdnum,name,sex,age,dept)asselect*fromstudentwheressex='女';删除视图语法:dropview视图名例:删除视图is_s1Dropviewis_s1;查询视图视图的查询和基本表的查询一样例1:在信息系学生的视图中找出年龄小于20岁的学生Selectsno,sagefromis_studentwheresage=90groupbysno;而where子句中是不能用集函数作为条件表达式的.
正确的语句为:Selectsno,avg(grade)fromscgroupbysnohavingavg(grade)>=90;更新视图视图的更新最终要转换为基本表的更新在视图定义时加上withcheckoptin可防止对视图进行非法修改.
例1:将信息系学生视图is_student中学号为95002的学生姓名改为刘晨Updateis_studentsetsname='刘晨'wheresno='95002';例2:向信息系学生视图is_student中插入一个新的学生记录,其中学号为92029,姓名为赵新,年龄为20岁.
Insertintois_studentvalues('95029','赵新',20);例3:删除学生视图is_student中学号为95029的记录Deletefromis_studentwheresno='95029';**在更新数据库中,并不是所有的视图都是可更新的,有些视图的更新不能转换成对基本表的更新.
例:updates_gsetgavg=90wheresno='95001';一般地,行列子集视图是可更新的.
DB2规定:(1)若视图是由两个以上基本表导出的,则此视图不有些更新(2)若视图的字段来自字段表达式或常数,则不有些对此视图执行insert和update,但允许执行delete(3)若视图的字段来自集函数,则此视图不允许更新(4)若视图定义中含有groupby,则此视图不允许更新(5)若视图定义中含有distinct,则此视图不允许更新(6)若视图定义中有嵌套查询,并且子查询的from表也是导出该视图的基本表,则此视图不允许更新.
(7)一个不允许更新的视图上定义的视图也不允许更新.

视图的作用(1)能够简化用户的操作(2)视图使用户能以多种角度看待同一数据(3)视图对充够数据库提供了一定程度的逻辑独立性(4)视图能够对机密数据提供安全保护为表名、字段名取别名表的别名的定义是通过在表名后加一个名字实现的,两者用空格分开,在下面的这段查询中,H是human的别名.
Selecth.
human_id,h,first_name,p.
land_descfromhumanh,property_listpwhereh.
human_id=p.
human_id;可定义字段的别名,执行查询时,他会作为字段出现在显式结果中.
例:Selecthuman_id"humanid",property_id"prop#last_descdescriptionfromproperty_listwhereproperty_id>'B';注意:1.
只有当要使用一些特殊符号,如#,%,逗号,空格以及要在表头中保留小写字母时,才需要引号.
2.
where子句中不能用别名,必须使用准确的全名.

用户、角色和权限控制用户和角色的创建用户的创建语法:createuser用户名[identifiedby口令]例:创建一个新用户JUN,口令为WANG.
CreateuserJUNidentifiedbyWANG;角色的创建角色定义了一组相关的权限.
语法:createrole角色名[identifiedby口令]例:建立一个角色DEPT,口令为MDB.
CreateroleDEPTidentifiedbyMDB;用户和角色的删除删除用户的语法:drop用户名例:删除用户JUNdropJUN;删除角色的语法:drop角色名例:删除DEPT角色DropDEPT;预定义用户和角色预定义用户某个Oracle数据库都有两个预定义的用户:SYS和SYSTEM.
SYS用户拥有Oracle数据库字典表和相关的数据库对象.
SYSTEM用户拥有Oracle应用开发工具所使用的表.