好用的sql

@ 复制表结构

create table table_name_new as select * from <table_name> where 1=2; 
--复制表结构和数据 
create table table_name_new as select * from <table_name>

@ 查看表信息

select * from tabs where table_name ='<table_name>' 
--判断表是否存在 
select count(*) from tabs where table_name ='<table_name>'

@ 查看表字段信息

select * from cols where table_name=upper('<table_name>') and column_name =upper('<column_name>') 
--判断表中是否存在某字段 
select count(*) from cols where table_name=upper('<table_name>') and column_name =upper('<column_name>')

@ 添加表字段

alter table <table_name> add <column_name> <data_type>

@ 删除表字段

alter table <table_name> drop COLUMN <column_name>;

@ 字段值追加

update <table_name> set <column_name>=<column_name>||'追加的值'

 @ 查询某列的值转换为字符串

select wm_concat(<column_name>) from <table_name>

 @ 查询当前用户下所有存储过程（DBA权限）

select * from DBA_objects where object_type='PROCEDURE' and owner='user_name' 

  查询所有：select * from all_objects where object_type='PROCEDURE'

 

 

转载于:https://www.cnblogs.com/zhutouying/p/3248330.html