PLSQL应用总结.docx
《PLSQL应用总结.docx》由会员分享，可在线阅读，更多相关《PLSQL应用总结.docx（27页珍藏版）》请在冰豆网上搜索。
PLSQL应用总结
SQL和PLSQL命令的总结
查看（723）/评论（0）/评分（0/0）
1、setverifyon/off控制输出行不显示old和new
2、setdefine‘&’定义变量字符
3、setechooff/on禁止或者启用sql脚本中的sql语句和命令
4、acceptvarnamevartypeformata../$...prompt‘....’；
undefinevarname
5、变量中被定义为&1,&2意味者第一个变量和第二个变量
6、ttitle和btitle代表的页眉和页脚
7、breakon和compute用于为列添加小计
8、内联视图selectcolnumfrom（selectcolumfromtablenamewherecondition）；
9、union:
两个表不重复的所有行，intersect：
两个表共有的行，minus：
第一个表有而第二个表没有的行。
10、translate（x，from_string，to_string）用作密码文件的形成。
11、decode（value，search_value，result，default_value）条件查询，可以实现if的功能或者case的功能。
12、case
whenconditionthenresult1
whenconditionthenresult2
····
whenconditionthenresultN
elsedefault_result
end 注意case一般存在from前面
13、使用connectby和startwith可以实现层次化查询，可以通过lpad命令实现目录树的功能，lpad（x，num）它指用x字符在左边填充num个。
14、1、rollup可以为每个分组返回小计记录，cube，可以返回每一个列组合的小计记录，同时在末尾加上总计记录，他们都是groupby的一种扩展。
2、还有一个就是grouping函数，它可以接受一列，但是只能爱使用rollup和cube的查询中使用，当需要返回空值的地方显示某个值时，就很有用，当列为空返回1，非空返回0。
3、因此我们可以通过decode和grouping来指定非空行显示什么，空行显示什么，还有一种类似的功能就是通过nvl和nvl2也可以实现decode和grouping相结合的功能。
4、还可以通过groupingsets只把小计记录显示出来，可以用来作为统计的信息显示。
5、group_id（）用来消除groupby字句返回的重复记录，group_id（）不接受任何参数。
15、可以从一个表向另外一个表复制，通过insertintotable1（列的种类）select···fromtable2wherecondition。
16、子表引用父表，子表中不能含有父表中没有的列值，但是父表中可以含有子表中没有的列值。
17、在创建表列时，可以指定default''来定义默认值。
18、注意merge的活用
mergeintotable1
usingtable2on（条件下）
whenmatchedthen
result1
whennotmatchedthen
result2； matched就是条件吻合的情况，notmatched就是条件不吻合的情况
19、使用dbms包的闪回查询，一种是时间戳，一种是scn
executedbms_flashback.enable_at_time（sysdate－？
/1440）；
executedbms_flashback.enable_at_system_change_number（scn号）；
20、系统授权可以传递使用withadminoption，对象授权可以传递是使用withgrantoption
可以用sys权限创建synonym（同义词）来让public都访问。
21、修改列的注意事项：
1、修改列的长度，条件是，改列的类型的长度可以修改，而且只有表中还没有任何行或者所有列都为空值时才可以减小列的长度。
2、修改数字列的精度，同样也而且只有表中还没有任何行或者所有列都为空值时才可以减小数字列的精度。
3、修改列的数据类型，如果表中还没有任何行或列为控制，就可以将列修改为任何一种数据类型（包括更短的数据类型），否则，就只能将列的数据类型修改为一种兼容的数据类型，但条件时没有缩短列的长度。
4、使用列的默认值，默认值只适用于新插入表中的行。
22、禁用和启动约束，altertabletablenamedisable/enableconstraintconstraint_name
23、如果需要删除表的所有行就需要使用truncate，而不是delete，应为truncate回重置表的存储空间，以准备接受新行，执行truncate语句不需要在数据库中使用任何undo空间，也不需要执行commit命令使删除操作永久化。
24、使用序列
createsequencename
startwithstart_num
incrementbyincrement_num
maxvaluemax_num
minvaluemin_num
cycle|nocycle
cachecache_num|nocache
order|noorder
cycle用于指定是否循环的，cache则指定要保留在内存中的整数的个数，order则是确保按照请求次序生成整数。
其中noorder为默认值,这里注意，一个序列包含了两个伪列，为currval和nextval，在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化，也就是说，必须先nextval，才能currval。
可以用序列填充表的主键，修改序列的注意：
不能修改序列的初值，序列的最小值不能大于当前值，序列的最大值不能小于当前值
25、一般都将表和索引存储到不同的表空间
createindexindex_nameontable_name（column_name，····）tablespacename；
如果某列的值几乎都是唯一的，而且用where使用改列查询所返回的行都小于该表总行的10％，则改列就非常适合与创建索引。
如果要基于函数的索引，就必须将参数query_rewrite_enabled设置为true；通过altersystem来修改。
每个表创建，就会给主键自动增加一个索引。
26、注意视图中不存储数据，它只是会访问基表中的行。
createorreplaceviewforce/noforceview_nameas查询语句[with（checkoption|readonly）constraintconstraint_name]；
在视图上插入行就相当于在基表中插入，但是只能对简单视图执行DML操作，复杂视图不支持DML操作。
而且在插入行时，还要满足创建视图时大的条件。
用createorreplace就可以用来彻底替换一个视图，alterview可以用来修改视图的约束。
比如alterviewnamedropconstraint····
27、块结构
declare
declaration_statements
begin
executable_statements
exception
exception_handing_statements
end；
28、dbms_output.put_line（''）输出字符用的。
它必须设定setserveroutputon才可以看到。
29、%type可以用来定义变量的类型比如说column1_nametable_name.column1_name%type,它的意思就是column1_name的类型和表table_name中的column1_name列的类型一致
30、PL/SQL中使用的条件逻辑 简单循环
ifcondition1then loop
statement1 statements
elsifcondition2then endloop；可以在循环中设定exitwhencondition跳出
statement2 while循环
else whileconditionloop
statement3 statements
endif；可以嵌套if语句 endloop；
for循环
forloop_variablein[reverse]lower_number..upper_numberloop
statements
endloop; 默认的是增加值，使用reverse就可以减少1
31、游标，1、首先声明一些变量结果保存列值。
一般都采用%type形式，用来和表中的列一样的类型declarev_columntable.column%type2、声明游标，一般都和select组合在一起，如cursorcursor_nameisselect_statement；3、打开游标，直接opencursor_name4、就可以从游标中取得记录了，fetchcursor_nameintovariable..,其中variable是declare中声明的变量，而且一般是放在循环中一行一行的读取，并设定exitwhencursor_name%notfound时跳出。
5、最后一步一定要注意，用完游标要关闭opencursor_name。
注意：
一般将游标和for循环合用，因为这样可以增强在游标中访问记录的能力，而且使用for循环不用显式地打开和关闭游标，连声明都一起省略了。
32、创建过程createorreplaceprocedureproceduce_name（parameter_namein|out|inouttype,.....）is|as
begin
procedure_body
end;
这里in是默认地，它指在程序运行地时候已经具有值，而且在程序体中这个值不会改变，而out定义参数只是在过程体内部赋值。
inout是指参数在程序运行时可能已经具有值，但是在过程体中也可以修改的。
如果发生错误，可以通过showerror来显示出来。
33、创建对象类型createorreplacetypetype_nameasobject（...），一个对象类型中可以引用已经建立好地对象类型比如说columntype_name就可以指定column地属性为type_name
在创建过程中，可以使用memberfunction函数return值；它和过程类似，唯一地区别式程序通常不返回值。
用类型建立表createtabletable_name（columntype_name）或者createtabletable_nameoftype_name；对象表还有一个特点就是可以为对象表之间地关系建立模型，而不是使用外键。
使用ref例如columnreftype_namescopeistable_name；这里地scopeis是将对象引用限制在特定表中地对象上。
可以用setdescribedepthnum来显示信息地深度，通过desc就可以看到类型包含类型地信息了。
SETDESCRIBE[DEPTH{1|n|ALL}][LINENUM{ON|OFF}][INDENT{ON|OFF}]
34、修改.sql脚本的执行路径，dos下可以通过修改sqlpath的方法来实现，就是setsqlpaht＝路径1；路径2····，可以在sqlplus中编辑缺省的脚本目录，通过
sql>seteditfile目录就可以了。
35、从sqlplus环境切换到操作系统命令提示符下，可以采用host命令或者“！
”，host通用，！
使用于linux和unix系统中但在windows下不被支持。
。
36、安装帮助文件可以通过启动$ORACLE_HOME\sqlplus\admin\help\helpbld.sql和hlpbld.sql来呼用helpus.sql建立，也可以呼用helpdrop.sql来删除。
37、调用showall可以查看所有的环境变量，showerror用于显示当前在创建函数、存储过程、触发器、包等对象的错误信息。
用showparameterparametername来显示初始化参数的值。
showrel来显示数据库的版本、showsga显示sga的大小，showuser显示当前用户。
38.&与&&的区别。
&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。
&&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。
当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。
39、@与@@的区别是什么。
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。
@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。
40、在查询语句中‘＃’的活用，如：
就是如果突然忘了表的结果，可以通过#desc表名先来显示表的名字
SQL>selectdeptno,empno,ename
2fromemp
3where
4#descemp
NameNull?
Type
---------------------------------------------------------------
EMPNONOTNULLNUMBER（4）
ENAMEVARCHAR2（10）
JOBVARCHAR2（9）
MGRNUMBER（4）
HIREDATEDATE
SALNUMBER（7,2）
COMMNUMBER（7,2）
DEPTNONUMBER
（2）
4sal>4000;
41、制作脚本的另类方法：
setechooff
setfeedbackoff
setnewpagenone
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setheading off
settimingoff
setnumwidth38
SPOOL c:
\具体的文件名
你要运行的sql语句例如（SELECT'DROPTABLE'||table_name||';'FROMuser_tables;）可以做一个删除当前用户的所有表
SPOOLOFF。
这里注意
（1）当前session是否对修改的数据进行自动提交SQL>SETAUTO[COMMIT]{ON|OFF|IMM[EDIATE]}
（2）在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句SQL>SETECHO{ON|OFF}
（3）是否显示当前sql语句查询或修改的行数SQL>SETFEED[BACK]{6|n|ON|OFF}默认只有结果大于6行时才显示结果的行数。
如果setfeedback1，则不管查询到多少行都返回。
当为off时，一律不显示查询的行数。
（4）是否显示列标题SQL>SETHEA[DING]{ON|OFF}当setheadingoff时，在每页的上面不显示列标题，而是以空白行代替
（5）设置一行可以容纳的字符数SQL>SETLIN[ESIZE]{80|n}如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
（6）设置页与页之间的分隔SQL>SETNEWP[AGE]{1|n|NONE}当setnewpage0时，会在每页的开头有一个小的黑方框。
当setnewpagen时，会在页和页之间隔着n个空行。
当setnewpagenone时，会在页和页之间没有任何间隔。
（7）显示时，用text值代替NULL值SQL>SETNULLtext
（8）设置一页有多少行数SQL>SETPAGES[IZE]{24|n}如果设为0，则所有的输出内容为一页并且不显示列标题
（9）是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL>SETSERVEROUT[PUT]{ON|OFF}在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
（10）当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL>SETWRA[P]{ON|OFF}当输出的行的长度大于设置的行的长度时（用setlinesizen命令设置），当setwrapon时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
（11）是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL>SETTERM[OUT]{ON|OFF}在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置settermspooloff后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。
（12）将SPOOL输出中每行后面多余的空格去掉。
SQL>SETTRIMS[OUT]{ON|OFF}
（13）显示每个sql语句花费的执行时间setTIMING{ON|OFF}
42、动态生成spool命令所需的文件名
在我们上面的例子中，spool命令所需要的文件名都是固定的。
有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？
columndat1new_valuefilename;
selectto_char（sysdate,'yyyymmddhh24mi'）dat1fromdual;
spoolc:
\&&filename.txt
select*fromdept;
spooloff;
43、常用的oracle元数据查询
sequence:
sql='selectsequence_namefromuser_sequencesorderbysequence_name'
table:
sql='selecttable_namefromuser_tablesorderbytable_name'
view:
sql='selectview_namefromuser_viewsorderbyview_name'
PROCEDURE:
sql='selectobject_namefromuser_objectswhereobject_type=value'
FUNCTION:
sql='selectobject_namefromuser_objectswhereobject_type=value'
PACKAGE:
sql='selectobject_namefromuser_objectswhereobject_type=value'
TYPE:
sql='selectobject_namefromuser_objectswhereobject_type=value'
44、Oracle表空间相关的系统表
dba_tablespaces,dba_data_files,dba_temp_files,dba_free_space,dba_segments
45、Showfeedback检查feedback的行数
Shownumwidth修改数值的宽度
Showautocommit自动提交工作的能力，即使没有下达commit命令，有些操作（如quit、exit）以及数据定义语言（ddl）的命令也会使提交发生。
46、注意delete与truncate，delete可以提交或回滚删除操作，而truncate自动删除表中的所有记录。
Truncate命令的操作结果使部能被回滚或提交的，截除的记录不能被恢复。
也不能靠执行闪回请求来找回被截除的数据。
47、一下往一个表中插入多行，要借助select和union两个语句
insertintotest（id,name）
select*from
（select101，‘swust’fromdual
union
select102，‘student’fromdual）；
48、修改系统时间显示模式：
altersessionsetnls_date_format='yyyy-mm-ddhh24:
mi:
ss';
SQL>altersessionsetnls_date_format='dd-mon-yyyy';
Sessionaltered.
SQL>altersessionsetnls_date_language='SIMPLIFIEDCHINESE';
SQL>selectsysdatefromdual;
SYSDATE
----------------
17-8月-2007
SQL>altersessionsetnls_date_language='AMERICAN';
Sessionaltered.
SQL>selectsysdatefromdual;
SYSDATE
--------------
17-aug-2007
49、把一个表从一个表空间转移到另一个表空间
首先,使用下面的命令移动:
altertabletable_namemovetablespacetablespace_name;
然后,如果有索引的话必须重建索引:
alterindexindex_namerebuildtablespacetablespace_name;
50、在oracle中，把一个表空间的所有索引换到另一个表空间呢？
比如：
例子1、在linux下操作的。
1、建立一个select_index.sql，里面写入以下内容。
SELECT'ALTERi
升级会员 