Oracle PL/SQL notes

set linesize 512 
set term off verify off feedback off pagesize 999 
-- set markup html on entmap ON spool on preformat off
set heading off
spool droptable.sql on




sql*plus使用的一些技巧  
    发表于：2005年7月5日     阅读:174次  








阅读本文档之前，建议先看” http://www.cnoug.org/viewthread.php?tid=16867“一文，


Sql*plus中蕴藏着好多技巧，如果掌握这些技巧，对于在oracle数据库下进行快速开发与有效维护数据库都是有益的。
　　　　下面就接受一二，
　　　 


1．使用SQL*PLUS动态生成批量脚本
将spool与select命令结合起来使用，可以生成一个脚本，脚本中包含有可以批量执行某一任务的语句。
例1：
生成一个脚本，删除SCOTT用户下的所有的表：
a. 创建gen_drop_table.sql文件，包含如下语句：
SPOOL　c:\drop_table.sql
　　 SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
　　 SPOOL OFF
b. 以SCOTT用户登录数据库
SQLPLUS > @ …..\gen_dorp_table.sql
c. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
SQL>　　　SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 
'DROPTABLE'||TABLE_NAME||';'　　　　　　　　　　　　　 
--------------------------------------------------------------------------------
DROP TABLE DEPT;　　　　　　　　　　　　　　　　　　　　 
DROP TABLE EMP;　　　　　　　　　　　　　　　　　　　　 
DROP TABLE PARENT;　　　　　　　　　　　　　　　　　　　
DROP TABLE STAT_VENDER_TEMP;　　　　　　　　　　 
DROP TABLE TABLE_FORUM;　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 
5 rows selected.　 
　　　　　　　　　　　　　 
SQL>　　　SPOOL OFF　
d. 对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下drop table …..语句
e. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS > @ c:\dorp_table.sql


在上面的操作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的操作带来诸多不便。懒惰是人的本性，这促使我们用更简单的办法来实现上面的任务。


a. 创建gen_drop_table.sql文件，包含如下语句：
　　　　set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading　off
set timing off
set verify off
set numwidth 38
SPOOL　c:\drop_table.sql
　　 SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
　　 SPOOL OFF
b. 以SCOTT用户登录数据库
SQLPLUS > @ …..\gen_dorp_table.sql
c. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
DROP TABLE DEPT;　　　　　　　　　　　　　　　　　　　　 
DROP TABLE EMP;　　　　　　　　　　　　　　　　　　　　 
DROP TABLE PARENT;　　　　　　　　　　　　　　　　　　　
DROP TABLE STAT_VENDER_TEMP;　　　　　　　　　　 
DROP TABLE TABLE_FORUM;　　　　　　　　　　　　　　　
d. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS > @ c:\dorp_table.sql




2．将一个表中的数据导出生成一个文本文件，列与列之间以”,”隔开
set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading　off
set timing off
set verify off
set numwidth 38
SPOOL　c:\drop_table.sql
　　 select DEPTNO || ',' || DNAME FROM DEPT;
　　 SPOOL OFF
将上面的内容保存为一个文本文件后，以scott登录，执行该文件后显示结果：
10,ACCOUNTING　
20,RESEARCH　
30,SALES　
40,OPERATIONS　




通过上面的两个例子，我们可以将：
set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading　off
set timing off
set verify off
set numwidth 38
SPOOL　c:\具体的文件名
　　 你要运行的sql语句
　　 SPOOL OFF
作为一个模版，只要将必要的语句假如这个模版就可以了。


在oracle的较新版本中，还可以用set colsep命令来实现上面的功能：
SQL> set colsep ,
SQL> select * from dept;
　　　　10,ACCOUNTING　　,NEW YORK
　　　　20,RESEARCH　　　,DALLAS
　　　　30,SALES　　　　 ,CHICAGO
　　　　40,OPERATIONS　　,BOSTON
　　　　35,aa　　　　　　,bb


3．动态生成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;


4．如何从脚本文件中得到WINDOWS环境变量的值：
在windos中：
spool c:\temp\%ORACLE_SID%.txt
　select * from dept;
　...
　spool off


在上面的例子中，通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt


在UNIX中：
spool c:\temp\$ORACLE_SID.txt
　select * from dept;
　...
　spool off


在上面的例子中，通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt


5．如何指定缺省的编辑脚本的目录
　　　　在sql*plus中，可以用save命令，将上一条执行的sql语句保存到一个文件中，但是如何设置该文件的缺省目录呢？
通过SQL> set editfile c:\temp\file.sql 命令，可以设置其缺省目录为c:\tmpe，缺省文件名为file.sql。


6．如何除去表中相同的行
找到相同的行：
SELECT * FROM dept a　 
WHERE ROWID <> (SELECT MAX(ROWID)
　　　　　　　　　 FROM dept b
　　　　　　　　　 WHERE a.deptno = b.deptno
　　　　　　　　　 AND a.dname = b.dname　-- Make sure all columns are compared
　　　　　　　　　 AND a.loc = b.loc);


注释：
如果只找deptno列相同的行，上面的查询可以改为：
SELECT * FROM dept a　 
WHERE ROWID <> (SELECT MAX(ROWID)
　　　　　　　　　 FROM dept b
　　　　　　　　　 WHERE a.deptno = b.deptno)


删除相同的行：
DELETE FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID
　　　　　　　　　　　　　　　　FROM dept b
　　　　　　　　　　　　　　　　WHERE a.deptno = b.deptno
　　　　　　　　　　　　　　　　AND a.dname = b.dname -- Make sure all columns are compared
　　　　　　　　　　　　　　　　AND a.loc = b.loc);


注意：上面并不删除列值为null的行。


7．如何向数据库中插入两个单引号(’’)
Insert inot dept values(35,’aa’’’’bb’,’a’’b’);


在插入时，用两个’表示一个’。


8．如何设置sql*plus的搜寻路径，这样在用@命令时，就不用输入文件的全路径。
设置SQLPATH环境变量。
如：
SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS


9．@与@@的区别是什么？
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。
@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。


10．&与&&的区别
&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。
&&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。


如，将下面三行语句存为一个脚本文件，运行该脚本文件，会提示三次，让输入deptnoval的值：
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;


将下面三行语句存为一个脚本文件，运行该脚本文件，则只会提示一次，让输入deptnoval的值：
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;


11．引入copy的目的
Copy命令在两个数据库之间拷贝数据时特别有用，特别是该命令可以在两个数据库之间传递long型字段的数据。
缺点：
在两个数据库之间传递数据时，有可能丢失精度(lose precision)。


12．问什么在修改大量的行时，我的脚本会变得很慢？
当通过PL/SQL块修改一个表中的许多行时，你会创建在表上创建一个cursor，但是只有在你关闭cursor时，才会释放ROLLBACK SEGMENT，这样，当cursor仍然打开时，修改过程会变慢，这是因为数据库不得不搜寻大量的rollback segment以便于维护读一致性。为了避免这样情况，试着在表上加一个标志字段来描述该行是否已经被修改，然后关闭该cursor，然后再打开该 cursor。每次可以修改5000行.

ASA备份示例

asa 备份:


映像备份：
backup database directory 'e:\\temp\\backup';


backup database directory 'e:\\temp\\backup' transaction log only transaction log rename match;


档案备份：
backup database to 'e:\temp\backup2' attended on;

MySQL ini 配置文件生成

@echo off
echo "This is a demo script for auto installation of noninstall version of MySQL on Windows.  "
echo "Copyright: iihero@CSDN, when you distribute it, please copy this section above the head."
echo "================================iiihero@hotmail.com====================================="
set MYSQL_HOME=%~dp0
echo MYSQL_HOME=%MYSQL_HOME%
del /F my.ini
echo [client] >> my.ini
echo port = 3306 >> my.ini
echo default_character_set=gbk >> my.ini
echo [mysqld] >> my.ini
echo default_character_set=utf8 >> my.ini
echo default_storage_engine=InnoDB >> my.ini
echo basedir=%MYSQL_HOME%>>my.ini
echo datadir=%MYSQL_HOME%data>> my.ini
echo innodb_data_file=ibdata1:50M;ibdata2:10M:autoextend >> my.ini
echo transaction-isolation=READ-COMMITTED >> my.ini
echo port=3306 >> my.ini
echo max_allowed_packet = 64M >> my.ini
echo "my.ini in %MYSQL_HOME% created."
set PATH=%MYSQL_HOME%\bin;%PATH%
if exist "%MYSQL_HOME%\bin\mysqld-nt.exe" call mysqld-nt --install-manual mysql55 --defaults-file="%MYSQL_HOME%\my.ini"
if not exist "%MYSQL_HOME%\bin\mysqld-nt.exe" call mysqld --install-manual mysql55 --defaults-file="%MYSQL_HOME%\my.ini"

Oracle connect by 示例

SQL> create TABLE tb(ID   char(3),PID   char(3),Name  varchar(10)) ;


Table created.


SQL> INSERT  into tb SELECT   '001',NULL   ,'山东省'  from dual
  2    UNION   ALL   SELECT   '002','001','烟台市'     from dual
  3    UNION   ALL   SELECT   '004','002','招远市'     from dual
  4    UNION   ALL   SELECT   '003','001','青岛市'     from dual
  5    UNION   ALL   SELECT   '005',NULL   ,'四会市'     from dual
  6    UNION   ALL   SELECT   '006','005','清远市'     from dual
  7    UNION   ALL   SELECT   '007','006','小分市'   from dual   ;


7 rows created.




SQL> select id, pid, lpad(' ', level*2, ' ') || name newname from tb start with pid is null connect
by prior id = pid;


ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
001
  山东省


002 001
    烟台市


004 002
      招远市




ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
003 001
    青岛市


005
  四会市


006 005
    清远市




ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
007 006
      小分市




7 rows selected.


SQL>

Delete Oracle用户下的表及序列(drop.sql)

DECLARE
    v_table_name USER_TABLES.table_name%TYPE;
    v_sequence_name USER_SEQUENCES.sequence_name%TYPE;
    --两个游标定义
    CURSOR c_TableName IS 
        SELECT table_name FROM USER_TABLES;
    CURSOR c_SequenceName IS
        SELECT sequence_name FROM USER_SEQUENCES;
BEGIN
    --打开游标
    OPEN c_TableName;
    LOOP
        --获取表名
        FETCH c_TableName INTO v_table_name;




        --作删除表的操作
        DROP TABLE v_table_name;
        --DBMS_OUTPUT.PUT_LINE('表: ' || v_table_name);


        --退出循环的条件
        EXIT WHEN c_TableName%NOTFOUND;
    END LOOP;


    --关闭游标c_TableName
    CLOSE c_TableName;


    --打开序列系统游标
    OPEN c_SequenceName;
    LOOP
        --获取序列名
        FETCH c_SequenceName INTO v_sequence_name;


        --
        DROP SEQUENCE v_sequence_name;
        --DBMS_OUTPUT.PUT_LINE('表: ' || v_sequence_name);


        --
        EXIT WHEN c_SequenceName%NOTFOUND;


    END LOOP;
    --关闭游标c_SequenceName
    CLOSE c_SequenceName;
END;
/

C#捕获cmd输出示例

using System;
using System.Collections.Generic;
using System.Text;


using System.Runtime.InteropServices;
using System.Diagnostics;
using System.IO;




namespace demo0
{
    class main
    {
        static void TestImp()
        {
            Process sqlProcess = new Process();
            sqlProcess.StartInfo.FileName = @"D:\Oracle\product\11.2.0\db1\BIN\imp.exe";
            sqlProcess.StartInfo.Arguments = string.Format("{0}/{1}@{2} FILE={3} FROMUSER={4} TOUSER={5}",
                "scott", "tiger", "ora102", "mock.dmp", "scott", "scott");
            sqlProcess.StartInfo.CreateNoWindow = true;
            sqlProcess.StartInfo.UseShellExecute = false;
            sqlProcess.StartInfo.RedirectStandardOutput = true;
            sqlProcess.StartInfo.RedirectStandardError = true;
            sqlProcess.Start();
            StreamReader sr = sqlProcess.StandardOutput;
            StreamReader sr2 = sqlProcess.StandardError;


            System.Console.WriteLine(sr.ReadToEnd());
            System.Console.WriteLine(sr2.ReadToEnd());
        }


        static void Main(string[] args)
        {
            TestImp();
        }
    }
}

上例就可以把一个imp操作的结果给dump出来。

Chrome: 定制自己喜欢的Chrome

Chrome确实是非常好的一款浏览器，但是也有让人不爽的地方。
那就是它默认装到C盘里，当你选择它做默认浏览器的时候，一些配置参数也丢掉了。导致cachedir等依旧在C盘里。

本文就介绍一种折中的办法，那就是将Chrome安装在默认的位置C盘，配置参数弄进注册表。
点击它，用的是新的配置参数启动。这样就不会有什么大问题了。

注册表信息变更：
Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\ChromeHTML\shell\open\command]
@="\"C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe\" --disk-cache-size=41943040 --user-data-dir=\"D:\\tools\\Chrome\\Data\"   -- \"%1\""

[HKEY_CLASSES_ROOT\http\shell\open\command]
@="\"C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe\" --disk-cache-size=41943040 --user-data-dir=\"D:\\tools\\Chrome\\Data\"   -- \"%1\""

[HKEY_CLASSES_ROOT\https\shell\open\command]
@="\"C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe\" --disk-cache-size=41943040 --user-data-dir=\"D:\\tools\\Chrome\\Data\"   -- \"%1\""

[HKEY_CLASSES_ROOT\ftp\shell\open\command]
@="\"C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe\" --disk-cache-size=41943040 --user-data-dir=\"D:\\tools\\Chrome\\Data\"   -- \"%1\""

这里把datadir设到d:\tools\Chrome\Data里头。

当然还要改变快捷方式，将Target(目标)改为：
"C:\Program Files\Google\Chrome\Application\chrome.exe"   --disk-cache-size=41943040 --user-data-dir="D:\tools\Chrome\Data"