PLSQL应用总结.docx

1、PLSQL应用总结SQL和PLSQL命令的总结查看( 723 ) / 评论( 0 ) / 评分( 0 / 0 ) 1、set verify on/off 控制输出行不显示old和new2、set define &定义变量字符3、set echo off/on 禁止或者启用sql脚本中的sql语句和命令4、accept varname vartype format a./$. prompt .；undefine varname 5、变量中被定义为&1,&2意味者第一个变量和第二个变量6、ttitle和btitle 代表的页眉和页脚7、break on 和 compute 用于为列添加小计8、内联

2、视图 select colnum from （select colum from tablename where condition）；9、union:两个表不重复的所有行，intersect：两个表共有的行，minus：第一个表有而第二个表没有的行。10、translate（x，from_string，to_string）用作密码文件的形成。11、decode（value，search_value，result，default_value）条件查询，可以实现if的功能或者case的功能。12、case when condition then result1 when condition th

3、en result2 when condition then resultN else default_result end 注意case一般存在from前面13、使用connect by和start with可以实现层次化查询，可以通过lpad命令实现目录树的功能，lpad（x，num）它指用x字符在左边填充num个。14、1、rollup可以为每个分组返回小计记录，cube，可以返回每一个列组合的小计记录，同时在末尾加上总计记录，他们都是group by的一种扩展。2、还有一个就是grouping函数，它可以接受一列，但是只能爱使用rollup和cube的查询中使用，当需要返回空值的地方显

4、示某个值时，就很有用，当列为空返回1，非空返回0。3、因此我们可以通过decode和grouping来指定非空行显示什么，空行显示什么，还有一种类似的功能就是通过nvl和nvl2也可以实现decode和grouping相结合的功能。4、还可以通过grouping sets只把小计记录显示出来，可以用来作为统计的信息显示。5、group_id()用来消除group by字句返回的重复记录，group_id()不接受任何参数。15、可以从一个表向另外一个表复制，通过insert into table1（列的种类）select from table2 where condition。16、子表引用父

5、表，子表中不能含有父表中没有的列值，但是父表中可以含有子表中没有的列值。17、在创建表列时，可以指定default 来定义默认值。18、注意merge的活用 merge into table1 using table2 on （条件下） when matched then result1 when not matched thenresult2； matched就是条件吻合的情况，not matched就是条件不吻合的情况19、使用dbms包的闪回查询，一种是时间戳，一种是scnexecute dbms_flashback.enable_at_time（sysdate ？/ 1440）；exe

6、cute dbms_flashback.enable_at_system_change_number（scn号）；20、系统授权可以传递使用with admin option，对象授权可以传递是使用with grant option可以用sys权限创建synonym（同义词）来让public都访问。21、修改列的注意事项：1、修改列的长度，条件是，改列的类型的长度可以修改，而且只有表中还没有任何行或者所有列都为空值时才可以减小列的长度。2、修改数字列的精度，同样也而且只有表中还没有任何行或者所有列都为空值时才可以减小数字列的精度。3、修改列的数据类型，如果表中还没有任何行或列为控制，就可以将列

7、修改为任何一种数据类型（包括更短的数据类型），否则，就只能将列的数据类型修改为一种兼容的数据类型，但条件时没有缩短列的长度。4、使用列的默认值，默认值只适用于新插入表中的行。22、禁用和启动约束，alter table tablename disable/enable constraint constraint_name23、如果需要删除表的所有行就需要使用truncate，而不是delete，应为truncate回重置表的存储空间，以准备接受新行，执行truncate语句不需要在数据库中使用任何undo空间，也不需要执行commit命令使删除操作永久化。24、使用序列 create sequ

8、ence namestart with start_numincrement by increment_nummaxvalue max_numminvalue min_numcycle|nocyclecache cache_num|nocacheorder|noorder cycle用于指定是否循环的，cache则指定要保留在内存中的整数的个数，order则是确保按照请求次序生成整数。其中noorder为默认值,这里注意，一个序列包含了两个伪列，为currval和nextval，在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化，也就是说，必须先nextval，才能currva

9、l。可以用序列填充表的主键，修改序列的注意：不能修改序列的初值，序列的最小值不能大于当前值，序列的最大值不能小于当前值25、一般都将表和索引存储到不同的表空间 create index index_name on table_name（column_name，） tablespace name； 如果某列的值几乎都是唯一的，而且用where使用改列查询所返回的行都小于该表总行的10，则改列就非常适合与创建索引。 如果要基于函数的索引，就必须将参数query_rewrite_enabled设置为true；通过alter system来修改。每个表创建，就会给主键自动增加一个索引。26、注意视图中

10、不存储数据，它只是会访问基表中的行。 create or replace view force/noforce view_name as 查询语句 with (check option| read only) constraint constraint_name； 在视图上插入行就相当于在基表中插入，但是只能对简单视图执行DML操作，复杂视图不支持DML操作。而且在插入行时，还要满足创建视图时大的条件。用create or replace就可以用来彻底替换一个视图，alter view可以用来修改视图的约束。比如alter view name drop constraint 27、块结构 de

11、claredeclaration_statements beginexecutable_statements exceptionexception_handing_statements end；28、dbms_output.put_line()输出字符用的。它必须设定set serveroutput on 才可以看到。29、%type可以用来定义变量的类型比如说 column1_name table_name.column1_name%type,它的意思就是column1_name的类型和表table_name中的column1_name列的类型一致30、PL/SQL中使用的条件逻辑简单循环

12、if condition1 then loop statement1statements elsif condition2 thenend loop；可以在循环中设定exit when condition 跳出statement2while循环 elsewhile condition loopstatement3statements endif； 可以嵌套if语句end loop； for循环for loop_variable in reverse lower_number.upper_number loopstatementsend loop; 默认的是增加值，使用reverse就可以减少1

13、31、游标，1、首先声明一些变量结果保存列值。一般都采用%type形式，用来和表中的列一样的类型 declare v_column table.column%type 2、声明游标，一般都和select组合在一起，如cursor cursor_name is select_statement；3、打开游标，直接open cursor_name 4、就可以从游标中取得记录了，fetch cursor_name into variable.,其中variable是declare中声明的变量，而且一般是放在循环中一行一行的读取，并设定exit when cursor_name%notfound时跳出

14、。5、最后一步一定要注意，用完游标要关闭open cursor_name。注意：一般将游标和for循环合用，因为这样可以增强在游标中访问记录的能力，而且使用for循环不用显式地打开和关闭游标，连声明都一起省略了。32、创建过程 create or replace procedure proceduce_name (parameter_name in | out |in out type,.) is | as begin procedure_body end; 这里in是默认地，它指在程序运行地时候已经具有值，而且在程序体中这个值不会改变，而out定义参数只是在过程体内部赋值。in out是指参

15、数在程序运行时可能已经具有值，但是在过程体中也可以修改的。如果发生错误，可以通过show error来显示出来。33、创建对象类型 create or replace type type_name as object（.），一个对象类型中可以引用已经建立好地对象类型比如说 column type_name 就可以指定column地属性为type_name在创建过程中，可以使用member function 函数 return 值；它和过程类似，唯一地区别式程序通常不返回值。用类型建立表 create table table_name （column type_name）或者create tab

16、le table_name of type_name；对象表还有一个特点就是可以为对象表之间地关系建立模型，而不是使用外键。使用ref例如 column ref type_name scope is table_name；这里地scope is是将对象引用限制在特定表中地对象上。 可以用set describe depth num 来显示信息地深度，通过desc就可以看到类型包含类型地信息了。SET DESCRIBE DEPTH 1 | n | ALL LINENUM ON | OFF INDENT ON | OFF34、修改.sql脚本的执行路径，dos下可以通过修改sqlpath的方法来实

17、现，就是set sqlpaht路径1；路径2，可以在sqlplus中编辑缺省的脚本目录，通过sqlset editfile 目录就可以了。35、从sqlplus环境切换到操作系统命令提示符下，可以采用host命令或者“！”，host通用，！使用于linux和unix系统中但在windows下不被支持。 。36、安装帮助文件可以通过启动$ORACLE_HOMEsqlplusadminhelphelpbld.sql和hlpbld.sql来呼用helpus.sql建立，也可以呼用helpdrop.sql来删除。37、调用show all可以查看所有的环境变量，show error用于显示当前在创建函

18、数、存储过程、触发器、包等对象的错误信息。用show parameter parametername来显示初始化参数的值。show rel来显示数据库的版本、show sga显示sga的大小，show user显示当前用户。38. &与&的区别。&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。39、与的区别是什么。等于start命令，用来运行一个sql脚本文件。命令调用当

19、前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。用在脚本文件中，用来指定用执行的文件与所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。40、在查询语句中的活用，如：就是如果突然忘了表的结果，可以通过#desc 表名先来显示表的名字SQL select deptno, empno, ename2 from emp3 where4 #desc empName Null? Type- - -EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9

20、)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)4 sal 4000;41、制作脚本的另类方法：set echo offset feedback offset newpage noneset linesize 500set verify offset pagesize 0set term offset trims onset heading off set timing offset numwidth 38SPOOL c:具体的文件名 你要运行的sql语句例如（SELECT DROP TABLE

21、 | table_name |; FROM user_tables;）可以做一个删除当前用户的所有表SPOOL OFF。这里注意（1）当前session是否对修改的数据进行自动提交 SQLSET AUTOCOMMIT ON|OFF|IMMEDIATE （2）在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 SQL SET ECHO ON|OFF（3）是否显示当前sql语句查询或修改的行数 SQL SET FEEDBACK 6|n|ON|OFF 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显

22、示查询的行数。 （4）是否显示列标题 SQL SET HEADING ON|OFF 当set heading off 时，在每页的上面不显示列标题，而是以空白行代替 （5）设置一行可以容纳的字符数 SQL SET LINESIZE 80|n 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。 （6）设置页与页之间的分隔 SQL SET NEWPAGE 1|n|NONE 当set newpage 0 时，会在每页的开头有一个小的黑方框。当set newpage n 时，会在页和页之间隔着n个空行。当set newpage none 时，会在页和页之间没有任何间隔。 （7）显示时，用te

23、xt值代替NULL值 SQL SET NULL text （8）设置一页有多少行数 SQL SET PAGESIZE 24|n 如果设为0，则所有的输出内容为一页并且不显示列标题 （9）是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。SQL SET SERVEROUTPUT ON|OFF 在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。 （10）当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。 SQL SET WRAP O

24、N|OFF 当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。 （11）是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。SQL SET TERMOUT ON|OFF 在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。 （12）将SPOOL输出中每行后面多余的空格去掉。SQL SET TRIM

25、SOUT ON|OFF （13）显示每个sql语句花费的执行时间 set TIMING ON|OFF 42、动态生成spool命令所需的文件名在我们上面的例子中，spool命令所需要的文件名都是固定的。有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？column dat1 new_value filename;select to_char(sysdate,yyyymmddhh24mi) dat1 from dual; spool c:&filename.txt select * from dept;spool off;43、常用的ora

26、cle元数据查询sequence: sql=select sequence_name from user_sequences order by sequence_nametable: sql=select table_name from user_tables order by table_nameview: sql=select view_name from user_views order by view_namePROCEDURE: sql=select object_name from user_objects where object_type=valueFUNCTION: sql=

27、select object_name from user_objects where object_type= valuePACKAGE: sql=select object_name from user_objects where object_type= valueTYPE: sql=select object_name from user_objects where object_type= value44、Oracle 表空间相关的系统表dba_tablespaces, dba_data_files, dba_temp_files, dba_free_space, dba_segmen

28、ts45、Show feedback 检查feedback的行数 Show numwidth 修改数值的宽度 Show autocommit 自动提交工作的能力，即使没有下达commit命令，有些操作（如quit、exit）以及数据定义语言（ddl）的命令也会使提交发生。46、注意delete与truncate，delete可以提交或回滚删除操作，而truncate自动删除表中的所有记录。Truncate命令的操作结果使部能被回滚或提交的，截除的记录不能被恢复。也不能靠执行闪回请求来找回被截除的数据。47、一下往一个表中插入多行，要借助select和union两个语句insert into t

29、est(id,name)select * from(select 101，swust from dualunionselect 102，studentfrom dual）；48、修改系统时间显示模式：alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;SQL alter session set nls_date_format=dd-mon-yyyy;Session altered.SQL alter session set nls_date_language=SIMPLIFIED CHINESE;SQL select sysdate

30、from dual;SYSDATE-17-8月 -2007SQL alter session set nls_date_language=AMERICAN;Session altered.SQL select sysdate from dual;SYSDATE-17-aug-200749、把一个表从一个表空间转移到另一个表空间首先,使用下面的命令移动:alter table table_name move tablespace tablespace_name;然后,如果有索引的话必须重建索引:alter index index_name rebuild tablespace tablespace_name;50、在oracle中，把一个表空间的所有索引换到另一个表空间呢？比如：例子1、在linux下操作的。1、建立一个select_index.sql，里面写入以下内容。SELECT ALTER i