Oracle 常用Sql

创建表空间与用户

/*
    说明:若已经存在相应的用户和表空间，则需要先删除相应的用户和表空间
        然后再全部重新建立
*/

--删除用户
drop user test cascade;

--删除表空间
drop tablespace test_data_temp including contents and datafiles;
drop tablespace test_data      including contents and datafiles;


--创建临时表空间
create temporary tablespace test_data_temp tempfile 'test_data_temp.dbf' 
       size 100m autoextend on;

--创建表空间
create tablespace test_data logging datafile 'test_data.dbf' 
       size 100m autoextend on;

--创建用户并指定表空间
create user test 
       identified by test 
     default tablespace test_data 
     temporary tablespace test_data_temp
     profile default;


--给用户授予角色权限
grant connect to test;
grant resource to test;
--给用户授予系统权限
grant unlimited tablespace to test;
--给用户授予管理员权限
grant dba to test;

exit;

帐号相关

--重置密码
alter user test identified by password;
--锁定/解锁用户
alter user test account lock;
alter user test account unlock;

创建表及修改表相关信息

--删除表
drop table t_persion;
--创建表
create table t_persion(
 pid varchar2(32) not null,
 pname varchar(32) not null,
 age number,
 sex char(1)
);
--修改表名
alter table t_persion rename to t_persion2;
--修改列名
alter table t_persion rename column pid to ppid;
--增改删表字段
alter table t_persion add (asd number);
alter table t_persion modify (asd char(1) default '0' not null);
alter table t_persion drop (asd);
--增删主键约束
alter table t_persion add constraint pk_t_persion primary key(pid);
alter table t_persion drop constraint pk_t_persion;
--增删外键约束
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid);
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid) ON DELETE CASCADE;--外键约束,级联删除
alter table t_persion drop constraint fk_t_persion;
--添加表注释
COMMENT ON table t_persion IS '个人信息表';
--添加字段注释
comment on column t_persion.pid is 'id';
comment on column t_persion.pname is '姓名';
comment on column t_persion.age is '年龄';
comment on column t_persion.sex is '性别';

查询表相关信息

查询表名,表注释

-- 查询表名,表注释
select rpad(t.TABLE_NAME,32),concat('--',t.COMMENTS) 
from user_tab_comments t
where t.table_name like 'T_ACCT_%';
-- 查询表及字段的注释创建语句
select 'comment on table '||table_name||' is '||''''||comments||''';' from user_tab_comments t where t.table_name in ('TABLE_NAME')
union all
select 'comment on column '||table_name||'.'||column_name||' is '||''''||comments||''';' from user_col_comments t2 where t2.table_name in ('TABLE_NAME');

查询所有表的记录数

-- 查询所有表的记录数
select t.table_name,t.num_rows from user_tables t order by t.num_rows desc
-- 如果以上语句查询不到,则创建下面的方法,再执行最后的sql
create or replace function count_rows(table_name in varchar2,
                                      owner      in varchar2 default null)
  return number authid current_user IS
  num_rows number;
  stmt     varchar2(2000);
begin
  if owner is null then
    stmt := 'select count(*) from "' || table_name || '"';
  else
    stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  end if;
  execute immediate stmt
    into num_rows;
  return num_rows;
end;

select table_name, count_rows(table_name) nrows from user_tables;

查询含有大字段的表

-- 查询含有大字段的表
select table_name from User_Tab_Columns 
where data_type = 'CLOB'
group by table_name
order by table_name

存储过程

无参+游标

--无参+游标
create or replace procedure demo1  
 as
 cursor cur_data is -- //(游标:一个可以遍历的结果集) 
       select * from t_persion t;
 begin 
   for cur_row in cur_data loop
     dbms_output.put_line(cur_row.pid||':'||cur_row.pname);
   end loop;
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 

insert into t_persion values('1','张三',18,1);
insert into t_persion values('2','李四',17,0);
call demo1();

入参

--入参
create or replace procedure demo2(
       p_pid in test.t_persion.pid%type,
       p_pname in varchar2,
       p_age in number,
       p_sex in char
       )
 is      
 begin 
    insert into t_persion(pid,pname,age,sex) values(p_pid,p_pname,p_age,p_sex);
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 
call demo2('3','王五',18,0);

入参+出参

--入参+出参
create or replace procedure demo3(
       p_pid in test.t_persion.pid%type,
       p_pname out varchar2
       )
 is      
 begin 
    select pname into p_pname from t_persion t where t.pid = p_pid;
    delete t_persion t where t.pid = p_pid;
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 
--在sqlplus中调用
var pname varchar2(32);
call demo3(3,:pname);
print :pname;

jdbc调用存储过程

public class JdbcProcedureTest {
    public static void main(String[] args) {
        Connection connection = null;
        //用于执行 SQL 存储过程的接口
        CallableStatement statement = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String user = "test";
            String password = "123456";
            connection = DriverManager.getConnection(url, user, password);
            String sql = "call demo3(?,?)";

            //调用存储过程
            statement = connection.prepareCall(sql);
            statement.setInt(1, 3);
            statement.registerOutParameter(2, Types.VARCHAR);
            statement.execute();
            System.out.println(statement.getString(2));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

序列相关

-- 创建序列
create sequence seq_name
increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
nomaxvalue -- 不设置最大值
nocycle -- 一直累加，不循环
cache 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------nocache
-- 获取序列下一个值
select seq_name.nextval from dual;
--获取序列当前值
select seq_name.currval from dual;
-- 删除序列
drop sequence seq_name;