oracle常用操作

 

--查看oracle版本
select distinct version from Product_Component_Version;
--创建数据表
create table saiTest(
 userid number not null primary key,
 uname varchar2(50) not null,
 upassword varchar2(50) not null
);
--查看表字段
select * from usertable;
--插入
insert into usertable values();
--修改
update usertable set username='' where userid=2;
--删除
delete from usertable where userid=3;
--增加列
alter table usertable add address varchar2(100);

--修改列
alter table usertable modify address varchar2(50);
--删除列
alter table usertable drop column address;
--添加/修改表的注释
comment on table usertable is '用户信息表';
--查看表注释
select * from user_tab_comments where table_name=upper('usertable');
--为数据表的列添加注释
comment on column usertable.userid is '用户id';
comment on column usertable.uname is '用户名称';

select * from user_col_comments where table_name =upper('usertable');

--查看有哪些表
select * from user_tables;
--分页语句
  --先在表中插入多条数据
  insert into usertable values(1,'qqq','qqq','qqqq','qqq','qqqq');
  insert into usertable values(2,'wwww','www','wwww','wwwww','wwwww');
  insert into usertable values(3,'eeeee','eeee','eeee','eeeee','eeee');
  insert into usertable values(4,'rrrr','rrrr','rrrr','rrrr','rrr');
  insert into usertable values(5,'tttt','ttt','tt','ttt','ttt');
  insert into usertable values(6,'yyy','yyy','yyy','yy','yy');
  select * from usertable;
  select * from (select usertable.*, ROWNUM RN FROM(select *　from usertable)usertable where rownum<=4) where rn>=1;
  --内联
  select t.tname,u.uname from testtable t inner join usertable u on t.usid =u.userid order by t.usid;
  --左外连
  select * from testtable t left  join usertable u on t.usid=u.userid;
  --子查询
  select * from usertable where usrname in (select usrname from usertable where uname='qqq');
  --查处倒叙的第一条(最后一条)
  select * from (select * from usertable order by userid desc) where rownum=1;
  --查询数据库的系统时间
  select sysdate from usertable where rownum=1;
  --自动增长
  --在mysql、sqlserver中可以设置列的值自动增长，然后将这个字段作为主键，但是oracle没有自动增长，所以要用到sequence
  --创建sequence
  create sequence usertable_sequence
  minvalue 1
  maxvalue 99999
  start with 1
  increment by 1
  cache 2
  nocycle
  order;
  
  --序列
  create sequence scott.usertableseq
  increment by 1
  start with 1
  maxvalue 9999 
  --删除序列
  drop sequence useridsequence;
 --查看序列
  select * from user_sequences where sequence_name=upper('usertable_sequence');
 --查看当前用户拥有的权限
  select * from user_role_privs;
  --查看当前用户的会话权限
  select * from session_privs;
 --修改表名
  rename userstable to usertable;
  --创建外键
  alter table PictureClasstable add constraint pk_user foreign key(userid) references usertable(userid)

select * from usertable;
--创建临时表空间
create temporary tablespace test_temp
tempfile 'E:\test.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--创建数据表空间
create tablespace test_date
logging
datafile 'E:\date.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--创建用户并指定表空间
create user testserver_user identified by testserver_user
default tablespace test_date
temporary tablespace test_temp;

--给用户授予权限
grant connect,resource to testserver_user;

--导出导入命令：
oracle数据导入导出imp/exp就相当于oracle数据还原与备份，exp命令把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以吧dmp文件从本地
到远处的数据库服务器中，利用这个功能可以构建两个相同的数据库，一个用来测试一个用于正式使用。
执行环境：可以在sqlplus.exe或者dos中执行
dos中可以执行时由于在oracle9i中安装目录oracle9ibin被设置为全局路径，该目录下exp.exe与imp.exe文件被用来执行导入导出

oracle用java编写，sqlplus.exe,exp.exe/imp.exe着两个文件有可能是被包装后的类文件
sqlplus.exe调用exp.exe、imp.exe所包裹的类，完全导入导出功能。
--备份分为逻辑备份（exp）和物理备份,也可以是冷备份和热备份

--归档的含义是把日志文件放到另一个地方
--查看系统统计信息
select * from　V$sysstat;
--查看后台进程
select * from V$bgprocess where paddr <> '00';
--查看所有的表空间
select * from V$tablespace;
--查看所有的表空间
select * from dba_data_files order by tablespace_name;

--删除表空间
drop tablespace test_date including contents;
select * from dba_data_files order by tablespace_name;

--对数据库进行备份
backup database databasename to disk='D\backup.bak' with from
GO

--存储过程
--存储过程名为usertable_pro
create or replace procedure usertable_pro(uid in usertable.userid%type)
as inname nvarchar2;
begin
 select uname into inname from usertable where userid=uid;
 DBMS_OUTPUT.put_line(inname);
exception
 dbms_output.put_line('no result');
end;
--oracle中调用存储过程
declare
usid usertable.userid%type;
uname nvarchar2(50);
begin
usid:=2;
usertable_pro(usid);
dbms_output.put_line(uname);
end;

--java中调用存储过程
import java.sql.*;
public static void main(String args[])throw Exception
{
 --加载oracle驱动
 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 --获得oracle数据库连接
 Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL",susr,spwd);
 --创建存储过程的对象
 CallableStatement c=conn.divpareCall("{call usertable_pro(?)}");
 --给oracle存储过程的参数设置值，将第一个参数的值设置成为188
 c.setInt(1,188);
 --执行oracle存储过程
 c.execute();
 conn.close();

}

--调用带两个参数的存储过程，数据类型为整形
import java.sql.*;
public class Test{
public static void main(String args[])throws Exception{
 --加载驱动
 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 --获得oracle数据库连接
 Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCALE9I",username,password);
 --创建oracle存储过程的对象
 CallableStatement cal=conn.divparaCall("{call usertable_pro(?,?)}");
 --设置
 cal.setInt(1,123);
 --注册第二个参数
 cal.registerOutParameter(2,java.sql.Type.INTERGER);
 --执行存储过程；
 cal.execute();
 conn.close();
}
}
--删除存储过程
drop procedure usertable_pro;

 

--函数
create or replace function Test_function(userid number)
return nvarchar2 is
  testname nvarchar2(20);
begin
 select ename into testname from usertable where userid=userid;
 return testname;
 exception
 
 end
 
 --触发器
 
 create or replace trigger Test_trigger
 after update on usertable
 for each row
 when(usertable.userid ==1)
 begin
 Dbms_Output.put_line('test');
 dbms_output.put_line('fan');
 exception
 end;
 --判断复合型触发器
 create or replace trigger usertable_tri
 before insert or update or delete on usertable
 for each row
 begin
 if inserting then
 
 end if;
 if deleting then
 end if;
 exception
 .....
 end;
 
--触发器功能强大，但如滥用会造成数据库和应用程序难以维护
触发器是一个特殊的存储过程，允许为insert /update/deleter创建触发器