Oracle常用命令

全局数据库名称：myoracle.Oracle9i.mynet。
系统标识符(SID):myoracle。
sys口令：change_on_install。
system口令：manager。

通过命令行启动Oracle8.1.7/9.0
cmd
SVRMGRL/DGMGRL
connect username/password@server
startup
shutdown

监听程序管理
lsnrctl
shutdown
start

用户名  口令             登录身份及说明
sys   change_on_install      SYSDBA或SYSOPER，但不能以NORMAL登录，可作为默认的系统管理员
system  Manager           SYSDBA或NORMAL，但不能以SYSOPER登录，可作为默认的系统管理员
scott  Tiger            NORMAL，普通用户
aqadm  aqadm            SYSDBA或NORMAL，高级队列管理员。
Dbsnmp  dbsnmp            SYSDBA或NORMAL，复制管理员。
数据库服务器超级密码忘记时使用：sys的默认用户别名internal的口令
Oracle9i默认的用户名scott，口令tiger。

PL/SQL里面连接数据库的命令：connect username/password @server
Oracle配制网络或者本地用户：../oracle/ora81/network/ADMIN/tnsnames.ora

oracle数据库的脱机备份（冷备份）：在C:/oracle/oradata/目录下的安装数据库时以SID名命名的文件夹例如myoracle，然后将目录下的数据库数据文件（*.dbf,*.ora）9个，日志文件（*.LOG）3个，控制文件（*.CTL）3个备份；再将位于C:/oracle/ora90/database目录下名为initmyoracle.ora的文件备份；同时将initmyoracle.ora文件内容中的目录"IFILE='C:/ORALCE/ADMIN/MYORACLE/PFILE/INIT.ORA'"下的文件备份。
Oracle数据库的脱机恢复：上面的逆过程。

通过命令行备份和恢复数据库orcle8.1.7
cmd
备份exp
恢复imp

//oracle配制文件：
# TNSNAMES.ORA Network Configuration File: C:/Oracle/Ora81/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SIIS61 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 202.96.255.61)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = siis)
    )
  )
 
//Oracle Listener配制文件：
# LISTENER.ORA Network Configuration File: C:/Oracle/Ora81/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = huzhou-siis)(PORT = 1521))
      )
    )
    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = huzhou-siis)(PORT = 2481))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:/Oracle/Ora81)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = siis)
      (ORACLE_HOME = C:/Oracle/Ora81)
      (SID_NAME = siis)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = myoracle.oracle8i.mynet)
      (ORACLE_HOME = C:/Oracle/Ora81)
      (SID_NAME = myoracle)
    )
  )

//登陆服务器sys
connect sys/change_on_install@myoracle223

//创建用户annicybc
create user annicybc
identified by "annicybc"
default tablespace system
temporary tablespace system
profile default
ACCOUNT UNLOCK;

//创建表：(在Oracle8i中没有boolean数据类型)
create table employee
(emp_name varchar2(20) not null,
emp_id number(20) not null,
emp_sex smallint not null,
emp_company varchar2(50) ,
emp_department varchar2(50));

//授予用户权限
-- Grant/Revoke object privileges
grant all on EMPLOYEE to ANNICYBC;
-- Grant/Revoke role privileges
grant dba to ANNICYBC;
-- Grant/Revoke system privileges
grant unlimited tablespace to ANNICYBC;
//**********************************************
GRANT CREATE ANY TABLE TO "TEMPUSER" WITH ADMIN OPTION;
GRANT "CONNECT" TO "TEMPUSER" WITH ADMIN OPTION;
BEGIN
  dbms_resource_manager_privs.grant_switch_consumer_group(
     grantee_name => 'TEMPUSER',
     consumer_group => 'DEFAULT_CONSUMER_GROUP',
     grant_option => TRUE
  );
END;
BEGIN
  dbms_resource_manager.set_initial_consumer_group(
    user => 'TEMPUSER',
    consumer_group => 'DEFAULT_CONSUMER_GROUP'
  );
END;
//**********************************************
//登陆服务器annicybc
connect annicybc/annicybc@myoracle223
//设立回滚点a
savepoint a
//插入行
insert into sys.employee values('annicybc',1001,1,'杭州恩普','项目部');
//设立回滚点b
savepoint b;
//查询
select * from sys.employee;
//回滚
rollback a;
//提交
//插入行
insert into sys.employee values('annicybc',1001,1,'杭州恩普','项目部');
//查询
select * from sys.employee;
//提交
commit;
//显示表结构
desc sys.employee;
//登陆服务器sys
connect sys/change_on_install@myoracle223
//收回权限
revoke dba from annicybc

包：核心包GLOG业务回退等等
SBK_MDPAY//医疗
SBK_MDREQ//医疗
SBK_NETREQ//数据报文
SBK_PUBLIC//结算
SBK_SQUAC//结算
SBK_JB//金保
SBK_INTS//结息
SBK_BANKTAX//地税

触发器Trggers
每张表有四个触发器，分别是，after insert,after update,before insert,after delete

SQL> desc sbds_syslog
Name    Type         Nullable Default Comments                      
------- ------------ -------- ------- ------------------------------
OPSENO  NUMBER(12)                    业务号::                      
ORDNUM  NUMBER(8)                     顺序号::                      
TABNAME VARCHAR2(20) Y                表名::                        
PRSENO  NUMBER(12)   Y                唯一号::                      
OPFLAG  CHAR(1)      Y                操作标志::0 新增 1 修改 2 删除

SQL> desc sbds_syslogdt;
Name     Type          Nullable Default Comments                          
-------- ------------- -------- ------- ----------------------------------
OPSENO   NUMBER(12)                     业务号::                          
ORDNUM   NUMBER(8)                      顺序号::                          
COLNAME  VARCHAR2(20)                   列名::                            
OLDVALUE VARCHAR2(800) Y                原值::                            
DATATYPE CHAR(1)       Y                数据类型::[1 CHAR.2 NUMBER.3 DATE]

SQL> select * from sbds_syslog where OPSENO like '%05%';
       OPSENO    ORDNUM TABNAME                     PRSENO OPFLAG
------------- --------- -------------------- ------------- ------
      7213105         1 SBDB_PSARCH              445556799 0
      7213105         2 SBDB_RTARCH              445556800 0
      7213105         3 SBDU_PSUPCASE            445556801 0
      7213105         4 SBDB_RTWAGE              445556802 0
      7213105         5 SBDU_WGADLOG             445556803 0
      7213105         6 SBDB_RTWAGE              445556804 0
      7213105         7 SBDB_RTWAGE              445556805 0
     
     
//数据库取前几条数据：使用rownum语句
select * from sbds_syslog where rownum<11;

//dual用于计算的系统自带的一个临时表

//cast命令的使用 作用：数据类型转换
select cast(sysdate as date)from dual;
//trunc的使用，作用：精确到哪一位
select trunc(sysdate, 'MM') from dual;
//to_char命令的使用 作用：数据类型转换
select to_char(sysdate,'YY-MM-DD hh24:mi:ss') from dual;
//trim,to_char等的综合应用
select  trim(to_char(sysdate, 'year,month,day')) from dual;

//根据tpgname升序排序
select  menucode as "编码",menuname as "名称",tpgname as "代码",muexplain as "解释",prsource as "引用"
from sbds_submenu where opatflag=1 order by tpgname asc;

//触发器触发前后数据表示及引用法
:old.prseno
:new.prseno

//SQL查询
SQL语法参考手册(所有实例在在Oracle SQL*Plus 中运行通过)
查询表格的结构用desc table_name;

一、data definition language
1、建表格
create table table_name(column1 datatype [not null] [not null primary key], column2 datatype [not null],..)
datatype - 资料的格式
not null - 不可以允许资料有空的
primary key - 是本表的主键
实例：create table randy (name char(10),age integer);

2、更改表格
alter table table_name add column column_name datatype
增加一个栏位（没有删除某个栏位的语法）
实例：alter table randy add address varchar(100); --在实际应用中不能有column关键字

alter table table_name add primary key (column_name)
更改表的定义把某个栏位设为主键
实例： alter table randy add primary key (name);

alter table table_name drop primary key (column_name)
把主键的定义删除
实例： alter table randy drop primary key; --实际应用中不需要column_name

3、建立索引
create index index_name on table_name (column_name)
说明:对某个表格的栏位建立索引可以增加查询时的速度
实例： create index randyindex on randy (name);

4、删除
删除表 drop table_name
删除索引 drop index_name
注意不能删除表中的某个栏位
实例：drop table table_name;
      drop index index_name; --删除时需要指明table 或 index 关键字

二、资料形态 datatypes
　　smallint      16 位元的整数。
　　interger      32 位元的整数。
　　decimal(p,s)　p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
　　float         32位元的实数。
　　double        64位元的实数。
　　char(n)   　　n 长度的字串，n不能超过 254。
　　varchar(n)  　长度不固定且其最大长度为 n 的字串，n不能超过 4000。
　　graphic(n)　　和 char(n) 一样，不过其单位是两个字元 double-bytes，n不能超过127。这个形态是为支援两个字元长度的字体，例如中文字。
　　vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
　　date      　　包含了年份、月份、日期。
　　time          包含了 小时、分钟、秒。
　　timestamp 　　包含了年、月、日、时、分、秒、千分之一秒。

三、资料操作 data manipulation language
1、增加资料
insert into table_name (column1,column2,...) values (value1,value2,...)
实例： insert into randy (name,age) values ('l',16);   --字符串要用' '表示。
       insert into randy values ('wei',26,'xiddian');  --没有指定column则会按照栏位顺序填入资料
注意table_name也可以是景观view_name;
   
insert into table_name (column1,column2,...) select column1x,column2x,... from another_table
说明：也可以经过一个子查询把别的表格的资料填入
实例：  insert into randy select * from copyrandy;

2、查询资料
select column1,column2 from table_name
select * from table_name where column1 = xxx [and column2 > yyy] [or column3 <> zzz]
实例： select * from randy where name<>'l' and age<27;

select * from table_name order by column2 [desc]
说明：order by 是指定以某个栏位做排序，[desc]是指从大到小排列，而默认是从小到大排列。
实例：select * from randy order by age desc; --[desc]的[]只是说明是可选项

组合查询：联合一个以上的表格才能够得到结果
select * from table1,table2 where table1.colunn1=table2.column1
实例：select * from randy,copyrandy where randy.age=copyrandy.age;

整合性查询
select count(*) from table_name where column_name = xxx
说明：符合条的资料共有几笔
实例： select count(*) from randy where age<30;
select sum(column1) from table_name
说明：计算出总和，所选的栏位必须是可数的数字形态
实例：select sum(age) from randy;
除此以外还有avg(),max(),min()等函数。
select column1,avg(column2) from table_name group by column1 having avg(column2)>xxx
说明：group by column 以column1为一组计算column2的平均值，如果column1只有一列数据，则avg(column2)则是column2本身。
      having 必须和group by 一起使用作为整合性的限制。
实例：select name,avg(age) from randy group by name having avg(age)<27; 

复合性查询
select * from table1_name where exists (select * from table2_name where conditions)
说明：exists 指存在与否
实例： select * from randy where exists (select * from copyrandy where age=26);  --where 后面的部分只是一个条件condition，如果条件为真则执行前面的部分，前面部分执行的内容和条件无关。

select * from table_name1 where column1 in (select column1 from table_name2 where conditions)
说明：in后面是一个集合表示column1存在集合里面
      select出来的资料形态必须符合column1
实例：select * from randy where name in (select name from copyrandy);

其他查询
select * from table_name1 where column1 like 'x%'
说明：like必须与后面的'x%'相呼应表示以x开头的子串
实例： select * from randy where name like 'w%';
select * from table_name1 where column1 in ('xxx','yyy',...)
说明：in后面是一个集合，表示column1在集合里面
实例：select * from randy where name in ('ling','wei','ran');
select * from table_name1 where column1 between xx and yy
说明：between表示column1的值介于xx与yy之间
实例：select * from randy where age between 1 and 27;

3、更改资料
update table_name set column1='xxx' where conditions
实例：update randy set name='ling' where age=16;

4、删除资料
delete from table_name where conditions
实例：delete from randy where name='ling';

日期的比较不同的数据库有不同的表达式
1）access数据库 where mydate>#2000-01-01#
2）Oracle数据库 where mydate>case('2000-01-01' as date)  或  where mydate>to_date('2000-01-01','yyyy-mm-dd')
//SQL查询结束

//加密储存过程
加密存储过程,授于其它用户执行   
随着社会的分工与合作越来越密切，同一台服务器里可能会运行几家公司不同的服务产品，
有时候服务之间需要互相提供数据，以实现经济上的双赢；当然各自的安全和独立性也需要保证。
如果这样的服务后台用的是ORACLE数据库，可以用加密存储过程，并授于其它用户执行的方法。
首先我们要为提供的数据写一个正确合乎逻辑的存储过程或函数test_proc，假设源代码是test.sql。
然后用wrap实用工具(默认的目录$ORACLE_HOME/bin)加密它：
使用wrap的语法是:
        $wrap iname =  input_file          [oname=output_file]
                           ↑                         ↑                                    
                    加密前存储过程或函数  加密后存储过程或函数,默认的后缀为*.plb
        举例：  $wrap iname=test.sql
                会在当前目录产生test.plb加密的存储过程或函数
        备注：　Windows和unix平台的下wrap生成*.plb加密文件可以通用，版本是向上兼容的。
        最后编译这个加密后的test.plb
        SQL> @/tmp/test.plb
        并赋于相关合作用户(another_user)执行的权限
        SQL> grant execute on test_proc to another_user;
        其它的注意事项：       
            合作用户不能有过多的权限, 使他们不能查数据字典和其它用户下的表，密码要起得难一些。

////////////////////////////////ORACLE外连接知识点
oracle中的外连接简单介绍
在讲外连接之前，先举例介绍内连接，也就是一般的相等连接。

select * from a, b where a.id = b.id;

对于外连接，oracle中可以使用“(+)”来表示，9i可以使用left/right/full outer join，下面将配合实例一一介绍。

1. left outer join：左外关联

select e.last_name, e.department_id, d.department_name 
from employees e 
left outer join departments d 
on (e.department_id = d.department_id);
 
等价于

select e.last_name, e.department_id, d.department_name 
from employees e, departments d 
where e.department_id=d.department_id(+);
 
结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

2. right outer join：右外关联

select e.last_name, e.department_id, d.department_name 
from employees e 
right outer join departments d 
on (e.department_id = d.department_id);
 
等价于

select e.last_name, e.department_id, d.department_name 
from employees e, departments d 
where e.department_id(+)=d.department_id;
 
结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

3. full outer join：全外关联

select e.last_name, e.department_id, d.department_name 
from employees e 
full outer join departments d 
on (e.department_id = d.department_id);
 
结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。 

///////////////////////////////////在oracle中实现分页查询sql
select ip,visitdate,address,url,system
from (
       Select rownum r,ip,visitdate,address,url,system
       From visitpage
       where admin='"+id+"' Order By ID Desc
     )
where r > n and r <= m;

大于n小于m条记录