Oracle命令

1.字段

增加字段：

alter table <table_name> add <column_name> <type>;

增加备注：

comment on column <table_name>.<column_name> is '备注';

删除字段：

alter table <table_name> drop <column_name>;

2.表

创建表：

create table <table_name>(
		<column1> <type>,
		<column2> <type>
	);

删除表：

drop table <table_name>;

3.索引

位图索引，值固定，如性别列；唯一索引，列值唯一

普通索引：create index <index_name> on <table_name>(<column_name>);

位图索引：create bitmap index <bit_index_name> on <table_name>(<column_name>);

唯一索引：create unique index <uni_index_name> on <table_name>(<column_name>)

4.plsql破解

Product Code(产品编号)：4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz 

serial Number(序列号)：601769 

password(口令)：xs374ca

5.锁表查看/解锁

--查询锁表情况
select b.username, b.sid, b.serial#, logon_time
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 order by b.logon_time;
--解锁
alter system kill session '53,31871' --SID,SERIAL#

6.查看时间差

select ceil(to_date('2021/11/26', 'yyyy-mm-dd') - to_date('2021/10/26', 'yyyy-mm-dd')) as nums from dual 

7.创建方法

CREATE OR REPLACE FUNCTION testFunction(pName varchar, pAge varchar)
return varchar
is pResult  varchar(80);
begin
	select testId into pResult
	from test_table
	where name=pName and age=pAge;
	return pResult;
end;

8.merge用法

merge into table_a a
using (select *
         from schema_name.table_b tb
        where tb.id = '111') b
on (a.bid = b.id)
when matched then
  update
     set a.col_x  = b.col_x,
         a.col_y = b.col_y
when not matched then
  insert 
    (col_1,col_2,col_3)
  values
    (b.v1,b.v2,b.v3)

参考博客：

blog.csdn.net/spw55381155…

9.将一张表数据备份

create table new_table_name as select * from source_table_name s where s.id='111';
--注：没有拷贝源表的健，其他的一样