获取ORACLE建表语句SHELL

Oracle根据Excel或者world数据字典⾃动⽣成建表语句和注释⼯作中⼀般会出现这种情况，和其他单位合作开发项⽬时，对⽅会给我们⼀份他们的数据字典，excl或者world的，这种情况下我们要根据数据字典将表结构建好，但是⼀个字段⼀个字段的去复制粘贴会点到⼿疼，⽽且还很容易出错，在这个情况下我⾃⼰做了⼀个⽣成建表脚本的⼩⽅法，详情如下：1、创建⽣成建表语句的存储过程：create table t_OUTString(Tname varchar2(50),OUTString clob,OUTSTRING2 clob,createddate date);create or replace procedure Pro_ExToSql(SStrin in varchar2 ,TString in varchar2) isRString varchar2(30000);RowString varchar2(30000);LeaveString varchar2(30000);midStr varchar2(30000);LeftStr varchar2(30000);RightStr varchar2(30000);V_Cname varchar2(100);V_Ctype varchar2(100);V_Ccomment varchar2(500);a number;b number;i number;j number;insertSql varchar2(30000);OUTString varchar2(30000);OUTString2 varchar2(30000);cursor curets isselect cname,ctype,ccomment from ets_temp order by createddate desc;beginj:=1;select replace(SStrin,'','#') into RString from dual;select instr(RString,chr(10)),substr(RString , j) into i,LeaveString from dual;while i>0 loopselect substr(LeaveString , j) into LeaveString from dual;select instr(LeaveString,chr(10)) into i from dual;if i is nullthengoto next;end if;select substr(LeaveString , 1 ,i ) into RowString from dual;j:=i+1;select instr(RowString,'#',1,1) ,instr(RowString,'#',1,2) into A,b from dual;select substr(RowString,a+1,b-a-1) ,substr(RowString,1,a-1),substr(RowString,b+1) into midStr,LeftStr,RightStrfrom dual;insertSql:='insert into ETS_temp(CComment,Cname,Ctype,createddate) values('''||midStr||''','''||LeftStr||''','''||RightStr||''',sysdate)';Execute IMMEDIATE insertSql;commit;end loop;<<next>>null;open curets;loopfetch curetsinto V_Cname, V_Ctype,V_Ccomment;exit when curets %NOTFOUND;OUTString:=''||OUTString||'COMMENT ON COLUMN '||TString||'.'||V_Cname||' is '''||V_Ccomment||''';'||chr(10)||'';outstring2:=''||outstring2||''||V_Cname||''||V_Ctype||','||chr(10)||'';end loop ;close curets;outstring2:='create table '||TString||''||chr(10)||'('||outstring2||');';select replace(replace(outstring2,''||chr(10)||',',','),','||chr(10)||');',');') into outstring2 from dual;insert into t_OUTString(Tname,OUTString,OUTSTRING2,createddate) values(Tstring,OUTString,OUTSTRING2,sysdate);commit;delete from ETS_temp;commit;EXCEPTIONWHEN OTHERS THENbegindelete from ETS_temp;commit;end;end Pro_ExToSql;2、执⾏过程会将建表语句insert到t_outstring表中outstring为创建注释语句，outstring2为建表语句：call pro_extosql('JGXLID 监管线路ID numberXLMC 线路名称 varchar2(50)DDFS 调度⽅式 varchar2(10)JHZLC 计划总⾥程 number(10,3)JHYYLC 计划营运⾥程 number(10,3)KSRQ 开始⽇期 datetimeZJZX 周⼏执⾏ varchar2(15)JHPCS 计划配车数 numberJHBCS 计划班次数 numberJHZGFPCS 计划早⾼峰配车数 numberJHWGFPCS 计划晚⾼峰配车数 numberJHZGFBCS 计划早⾼峰班次数 numberJHWGFBCS 计划晚⾼峰班次数 numberSZMC ⾸站名称 varchar2(50)MZMC 末站名称 varchar2(50)SZSB ⾸站⾸班 varchar2(5)SZMB ⾸站末班 varchar2(5)','TableName')3、在T_outstring表中取得以下信息：--outstring字段COMMENT ON COLUMN TableName.JGXLID is'监管线路ID';COMMENT ON COLUMN TableName.SZMB is'⾸站末班';COMMENT ON COLUMN TableName.DDFS is'调度⽅式';COMMENT ON COLUMN TableName.JHZLC is'计划总⾥程';COMMENT ON COLUMN TableName.JHYYLC is'计划营运⾥程';COMMENT ON COLUMN TableName.KSRQ is'开始⽇期';COMMENT ON COLUMN TableName.ZJZX is'周⼏执⾏';COMMENT ON COLUMN TableName.JHPCS is'计划配车数';COMMENT ON COLUMN TableName.JHBCS is'计划班次数';COMMENT ON COLUMN TableName.JHZGFPCS is'计划早⾼峰配车数';COMMENT ON COLUMN TableName.JHWGFPCS is'计划晚⾼峰配车数';COMMENT ON COLUMN TableName.JHZGFBCS is'计划早⾼峰班次数';COMMENT ON COLUMN TableName.JHWGFBCS is'计划晚⾼峰班次数';COMMENT ON COLUMN TableName.SZMC is'⾸站名称';COMMENT ON COLUMN TableName.MZMC is'末站名称';COMMENT ON COLUMN TableName.SZSB is'⾸站⾸班';COMMENT ON COLUMN TableName.XLMC is'线路名称';--outstring2字段create table TableName(JGXLID number,SZMB varchar2(5),DDFS varchar2(10),JHZLC number(10,3),JHYYLC number(10,3),KSRQ datetime,ZJZX varchar2(15),JHPCS number,JHBCS number,JHZGFPCS number,JHWGFPCS number,JHZGFBCS number,JHWGFBCS number,SZMC varchar2(50),MZMC varchar2(50),SZSB varchar2(5),XLMC varchar2(50));PS:这样就可以直接将SQL提取出来执⾏建表了！有更好的办法的希望⼤家在评论区留⾔，⼀起探讨！。

shell脚本操作oracle删除表空间、创建表空间、删除⽤户oracle下表空间的导出，⽤户的删除，表空间删除，⽤户新建，表空间新建，数据导⼊的shell使⽤⾮oracle⽤户执⾏该脚本参数说名$1：base表空间的⽤户名$2：同步表空间的⽤户名使⽤场景测试⽤，base表空间⽤于升级建⽴⼀些固化数据。

同步表空间⽤于测试⽤，每次去和base表空间拉平数据复制代码代码如下:#!/bin/shoraclehome=$ORACLE_HOMEecho $oraclehomelocaldir="/oracle/data"echo $localdir#删除已经存在的临时dmp⽂件rm -rf $localdir/$2temp.dmprmresult=$?echo "rm $2temp.dmp result:$rmresult"#将⽤户$1的表空间导出su - oracle -c "exp dba/dba file=$localdir/$2temp.dmp owner=$1"expresult=$?if [ "$expresult" != "0" ];thenecho "exp $1 tablespace failure"fi#先删除⽤户$2及其表空间,然后再新建该⽤户及表空间su - oracle -c "${ORACLE_HOME}/bin/sqlplus /nolog" <<EOFconnect / as sysdbadrop user $2 cascade;drop tablespace $2 including contents and datafiles;create tablespace $2 datafile '/oracle/product/10.2.0/oradata/$2.dbf' size 5M autoextend on;create user $2 identified by "$2" default tablespace $2 temporary tablespace TEMP profile DEFAULT;grant connect to $2;grant resource to $2;grant create any table to $2;grant create any trigger to $2;grant create any type to $2;grant create any view to $2;grant unlimited tablespace to $2;exitEOFcrdrresult=$?if [ "$crdrresult" != "0" ];thenecho "drop user and tablespace failure"echo "create user and tablespace failure"else#刚建完的⽤户不能马上使⽤,等候10秒sleep 10s#更换dmp⽂件中的表空间名sed -i 's/TABLESPACE "$1"/TABLESPACE "$2"/g' $localdir/$2temp.dmp#使⽤imp命令导出表空间数据到⽤户$2的表空间su - oracle -c "imp dba/dba file=$localdir/$2temp.dmp fromuser=$1 touser=$2"impresult=$?if [ "$impresult" != "0" ];thenecho "imp failure"elseecho "imp success"fifi。

如何获取⼀个数据库的所有建表语句与创建索引的语句？如何获取⼀个数据库的所有建表语句与创建索引的语句？⽣成建表语句操作 SSMS中右击数据库-》⽣成脚本-》选择仅限架构利⽤T-SQL⽣成建表语句--⽣成当前数据库下所有表的创建脚本select'create table ['+ +'] ('+ o.list +')'+CASE WHEN tc.Constraint_Name IS NULL THEN''ELSE'ALTER TABLE '+ +' ADD CONSTRAINT '+ tc.Constraint_Name +' PRIMARY KEY '+' ('+LEFT(j.List, Len from sysobjects socross apply(SELECT' ['+column_name+'] '+data_type +case data_typewhen'sql_variant'then''when'text'then''when'decimal'then'('+cast(numeric_precision_radix as varchar) +', '+cast(numeric_scale as varchar) +')'else coalesce('('+case when character_maximum_length =-1then'MAX'else cast(character_maximum_length as varchar) end+')','') end+''+case when exists (select id from syscolumnswhere object_name(id)=and name=column_nameand columnproperty(id,name,'IsIdentity') =1) then'IDENTITY('+cast(ident_seed() as varchar) +','+cast(ident_incr() as varchar) +')'else''end+''+(case when IS_NULLABLE ='No'then'NOT 'else''end ) +'NULL '+case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE''END+', 'from information_schema.columns where table_name = order by ordinal_positionFOR XML PATH('')) o (list)left joininformation_schema.table_constraints tcon tc.Table_name = AND tc.Constraint_Type ='PRIMARY KEY'cross apply(select'['+ Column_Name +'], 'FROM information_schema.key_column_usage kcuWHERE kcu.Constraint_Name = tc.Constraint_NameORDER BYORDINAL_POSITIONFOR XML PATH('')) j (list)where xtype ='U'AND name NOT IN ('dtproperties')View Code⽣成表索引创建、删除语句--⽣成表索引的创建删除语句;WITH TBAS ( SELECT TB.object_id ,Schema_name = ,table_name = FROM sys.tables TBINNER JOIN sys.schemas Sch ON TB.schema_id = Sch.schema_idWHERE TB.is_ms_shipped =0),IXCAS ( SELECT IXC.object_id ,IXC.index_id ,IXC.index_column_id ,IXC.is_descending_key ,IXC.is_included_column ,column_name = FROM SYS.index_columns IXCINNER JOIN SYS.columns C ON IXC.object_id= C.object_idAND IXC.column_id = C.column_id),IXAS ( SELECT IX.object_id ,index_name = ,index_type_desc = IX.type_desc ,IX.is_unique ,IX.is_primary_key ,IX.is_unique_constraint ,IX.is_disabled ,index_columns_TEMP =STUFF(IXC_COL.index_columns, 1, 1,N'') ,index_columns =CASE WHEN IXC_COL_INCLUDE.index_columns_includes IS NOT NULLTHEN STUFF(LEFT(IXC_COL.index_columns,DATALENGTH(IXC_COL.index_columns)-DATALENGTH(IXC_COL_INCLUDE.index_columns_includes)),1, 1, N'')ELSE STUFF(IXC_COL.index_columns,1, 1, N'')END ,index_columns_includes =STUFF(IXC_COL_INCLUDE.index_columns_includes,1, 1, N'')FROM sys.indexes IXCROSS APPLY ( SELECT index_columns = ( SELECTN','+QUOTENAME(column_name)FROMIXCWHEREobject_id= IX.object_idAND index_id = IX.index_idORDER BY index_column_idFOR XML PATH('') ,ROOT('r') ,TYPE).value('/r[1]', 'nvarchar(max)')) IXC_COLOUTER APPLY ( SELECT index_columns_includes = ( SELECTN','+QUOTENAME(column_name)FROMIXCWHEREobject_id= IX.object_idAND index_id = IX.index_idAND is_included_column =1ORDER BY index_column_idFOR XML PATH('') ,ROOT('r') ,TYPE).value('/r[1]', 'nvarchar(max)')) IXC_COL_INCLUDEWHERE index_id >0)SELECT DB_NAME() AS N'数据库名' ,TB.Schema_name AS N'架构' ,TB.table_name AS N'表名' ,IX.index_name AS N'索引名' ,IX.index_type_desc AS N'索引类型' ,IX.is_unique AS N'是否唯⼀索引' ,IX.is_primary_key AS N'是否主键' ,IX.is_unique_constraint AS N'是否唯⼀约束' ,IX.is_disabled AS N'是否禁⽤索引' ,IX.index_columns AS N'索引列' ,IX.index_columns_includes AS N'索引包含列' ,N'CREATE INDEX '+ N'['+ IX.index_name + N']'+ N' ON '+ N'['+QUOTENAME(DB_NAME()) + N'.'+QUOTENAME(TB.SCHEMA_NAME) + N'.'+QUOTENAME(TB.table_name) + N'('+ IX.index_columns + N')'+CASE WHEN IX.index_columns_includes IS NOT NULLTHEN CHAR(13) + N'INCLUDE ('+ IX.index_columns_includes+ N')'ELSE N''END AS N'创建索引' ,N'DROP INDEX '+QUOTENAME(IX.index_name) + N' ON '+QUOTENAME(DB_NAME()) + N'.'+QUOTENAME(TB.SCHEMA_NAME) + N'.'+QUOTENAME(TB.table_name) AS N'删除索引'FROM TBINNER JOIN IX ON TB.object_id= IX.object_idORDER BY Schema_name ,table_name ,IX.index_nameView Code查看表详情、数据字典--查看表的详细信息SELECT表名=CASE WHEN C.column_id =1THEN ELSE N''END ,表说明=ISNULL(CASE WHEN C.column_id =1THEN PTB.[value]END, N'') ,字段序号= C.column_id ,字段名= ,主键=ISNULL(IDX.PrimaryKey, N'') ,标识=CASE WHEN C.is_identity =1THEN N'√'ELSE N''END ,计算列=CASE WHEN C.is_computed =1THEN N'√'ELSE N''END ,类型= ,长度= C.max_length ,精度= C.precision ,⼩数位数= C.scale ,允许空=CASE WHEN C.is_nullable =1THEN N'√'ELSE N''END ,默认值=ISNULL(D.definition, N'') ,字段说明=ISNULL(PFD.[value], N'') ,索引名=ISNULL(IDX.IndexName, N'') ,索引排序=ISNULL(IDX.Sort, N'') ,创建时间= O.Create_Date ,修改时间= O.Modify_dateFROM sys.columns CINNER JOIN sys.objects O ON C.[object_id]= O.[object_id]AND O.type ='U'AND O.is_ms_shipped =0INNER JOIN sys.types T ON er_type_id = er_type_idLEFT JOIN sys.default_constraints D ON C.[object_id]= D.parent_object_idAND C.column_id = D.parent_column_idAND C.default_object_id = D.[object_id]LEFT JOIN sys.extended_properties PFD ON PFD.class =1AND C.[object_id]= PFD.major_idAND C.column_id = PFD.minor_id-- AND ='Caption' -- 字段说明对应的描述名称(⼀个字段可以添加多个不同name的描述) LEFT JOIN sys.extended_properties PTB ON PTB.class =1AND PTB.minor_id =0AND C.[object_id]= PTB.major_id-- AND ='Caption' -- 表说明对应的描述名称(⼀个表可以添加多个不同name的描述) LEFT JOIN-- 索引及主键信息( SELECT IDXC.[object_id] ,IDXC.column_id ,Sort =CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END ,PrimaryKey =CASE WHEN IDX.is_primary_key =1THEN N'√'ELSE N''END ,IndexName = FROM sys.indexes IDXINNER JOIN sys.index_columns IDXC ON IDX.[object_id]= IDXC.[object_id]AND IDX.index_id = IDXC.index_idLEFT JOIN sys.key_constraints KC ON IDX.[object_id]= KC.[parent_object_id]AND IDX.index_id = KC.unique_index_idINNER JOIN-- 对于⼀个列包含多个索引的情况,只显⽰第1个索引信息( SELECT[object_id] ,Column_id ,index_id =MIN(index_id)FROM sys.index_columnsGROUP BY[object_id] ,Column_id) IDXCUQ ON IDXC.[object_id]= IDXCUQ.[object_id]AND IDXC.Column_id = IDXCUQ.Column_idAND IDXC.index_id = IDXCUQ.index_id) IDX ON C.[object_id]= IDX.[object_id]AND C.column_id = IDX.column_id-- WHERE like '%name%'-- 如果只查询指定表,加上此条件ORDER BY ,C.column_idView Code。

shell调用oracle语句Shell脚本可以通过调用Oracle语句与数据库进行交互。

在以下内容中，我们将讨论如何使用Shell脚本调用Oracle语句来执行一些常见的数据库操作，如查询数据、插入数据、更新数据和删除数据等。

1. 连接到Oracle数据库在Shell脚本中，可以使用`sqlplus`命令与Oracle数据库建立连接，如下所示：```shell#!/bin/bash# 设置数据库连接信息ORACLE_SID=<ORACLE_SID>ORACLE_USER=<ORACLE_USER>ORACLE_PASSWORD=<ORACLE_PASSWORD># 连接Oracle数据库sqlplus -S$ORACLE_USER/$ORACLE_PASSWORD@$ORACLE_SID << EOF-- 执行SQL语句-- ...EOF```其中，`$ORACLE_SID`是Oracle实例的标识符，`$ORACLE_USER`和`$ORACLE_PASSWORD`是数据库用户的用户名和密码。

2. 查询数据在Shell脚本中，可以使用`select`语句查询Oracle数据库中的数据，如下所示：```shellsqlplus -S$ORACLE_USER/$ORACLE_PASSWORD@$ORACLE_SID << EOF-- 查询数据SELECT * FROM employees WHERE department_id = 10; EOF```以上示例中，查询了`employees`表中`department_id`为10的所有记录。

3. 插入数据在Shell脚本中，可以使用`insert into`语句向Oracle数据库中插入数据，如下所示：```shellsqlplus -S$ORACLE_USER/$ORACLE_PASSWORD@$ORACLE_SID<< EOF-- 插入数据INSERT INTO employees (id, name, salary) VALUES (1, 'John', 5000);COMMIT;EOF```以上示例中，向`employees`表中插入了一条记录，插入的字段包括`id`、`name`和`salary`。