直接循环写入数据,导出数据字典文档

有时候我们需要想数据中写数据 declare@iint set@i=1 while@i30 begin
insertintotable(id)values(@i) set@i=@i+1 end

–定义函数NoPassInfoByCourse,输入课程号,返回该课程未及格同学的学号。CREATEFUNCTIONNoPassInfoByCourse(@CcodeVARCHAR(50))RETURNStableASBEGINRETURNselectScodefromScoreswhereCcode=@CcodeandSumPage=60END–定义存储过程PrtNoPassByCourse(),按不同课程输出未及格同学的学生姓名、学号、班级createprocedurePrtNoPassByCourseasbegindeclare@iint—存放循环变量declare@countint—存放课程的数量select@count=count(*)fromCoursesset@i=0;while@i@countbeginDBMS_OUTPUT.put_line(‘—《’+(selecttop@i*fromCourses).Cname+’》课程不及格名单–‘);DBMS_OUTPUT.put_line(selectSNameas姓名,SCodeas学号,CNameas班级fromselect*fromScoreswhereSumPage60andCName=(selecttop@i*fromCourses).Cname);set@i=@i+1endend为什么会编译错误?

项目上需要整理目前数据库的数据字典文档。项目不规范,这种文档只要后期来补。这么多张表,每个字段都写到word文档里真心头大。就算前面写了个查询表结构的sql,但是最后整理到word里还是感觉有点麻烦。以前写过一个oracle直接生成表结构的html文档,所以现在也想再弄个postgresql
版本的。查了一番文档,发现pg9.4不支持写文件。无奈放弃。最后选了一个这种方案,利用sql脚本中打印消息的功能。把生成的html文档打印出来,最后拷贝html文档代码到文本文件中保存,虽然比oracle那个麻烦了点,总算能得到想要的html文档了。

slq脚本:

–1.0–2015-11-30–postgresql-9.4.5–打印出数据字典html–执行完毕,在pgAdmin的消息窗口,把打印内容拷贝到文本文件中,替换掉多余的输出:[PGSCRIPT
] ,删除头部的[QUERY   
]及打印出的查询语句,–最后把文件另存为.html文件。–用浏览器打开保存的网页,然后拷贝页面内容到word文档中,下面整理格式就可以了–注意:–脚本里包含了详细版,和简版两个版本的数据字典,使用的时候注意切换到对应的标题–‘列名类型长度主键约束唯一约束外键约束可否为空描述’;–‘列名类型描述’;begin–查询表名set@table=selectdistinctrelname,
relname||'(‘||(selectdescriptionfrompg_descriptionwhereobjoid=oidandobjsubid=0)||’表’||’)’astable_namefrompg_class
c,pg_attribute
awherec.oid=a.attrelidandattstattarget=-1andattrelidin(selectoidfrompg_classwhererelnamein(selectrelnameastable_namefrompg_classwhererelkind=’r’andrelnamelike’exg_%’orderbyrelname))orderbytable_name;–数据字典(详细版):列名
类型 长度 主键约束 唯一约束 外键约束 可否为空
描述set@att=select(selectrelnamefrompg_classwhereoid=a.attrelid)astable_name,”||a.attname||”||”||format_type(a.atttypid,a.atttypmod)||”||”||(casewhenatttypmod-4>0thenatttypmod-4else0end)||”||”||(casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype=’p’)>0then’Y’else’N’end)||”||”||(casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype=’u’)>0then’Y’else’N’end)||”||”||(casewhen(selectcount(*)frompg_constraintwhereconrelid=a.attrelidandconkey[1]=attnumandcontype=’f’)>0then’Y’else’N’end)||”||”||(casewhena.attnotnull=truethen’Y’else’N’end)||”||”||col_description(a.attrelid,a.attnum)||”frompg_attribute
awhereattstattarget=-1andattrelidin(selectoidfrompg_classwhererelnamein(selectrelnameastable_namefrompg_classwhererelkind=’r’andrelnamelike’exg_%’orderbyrelname))orderbytable_name,attnum;/*–数据字典(简版):列名
类型 描述

set @att = select (select relname from pg_class where oid=a.attrelid)
as table_name,

‘||a.attname||’

‘||format_type(a.atttypid,a.atttypmod)||’

‘||col_description(a.attrelid,a.attnum)||’

from pg_attribute a

where attstattarget=-1

and attrelid in(select oid from pg_class where relname in (select
relname as table_name from pg_class where relkind=’r’ and relname like
‘exg_%’ order by relname))

order by
table_name,attnum;*/–打印html文档print”;print”;print”;print”;print”;print”;set@i=0;while@i’;print’列名类型长度主键约束唯一约束外键约束可否为空描述’;–print
‘列名类型描述’;set@j=0;while@j’;set@i=@i+1;endend–附:/*–数据字典–详细版

select

(select relname ||’–‘||(select description from pg_description where
objoid = oid and objsubid = 0) from pg_class where oid=a.attrelid) as
表名,

a.attname as 列名,

format_type(www.ewinvip.com,a.atttypmod) as 类型,

(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,

(case when (select count(www.jnha.info) from pg_constraint where
conrelid=a.attrelid and conkey[1]=attnum and contype=’p’)>0 then
‘Y’ else ‘N’ end) as 主键约束,

(case when (select count(*) from pg_constraint where
conrelid=a.attrelid and conkey[1]=attnum and contype=’u’)>0 then
‘Y’ else ‘N’ end) as 唯一约束,

(case when (select count(*) from pg_constraint where
conrelid=a.attrelid and conkey[1]=attnum and contype=’f’)>0 then
‘Y’ else ‘N’ end) as 外键约束,

(case when a.attnotnull=true then ‘Y’ else ‘N’ end) as 可否为空,

col_description(a.attrelid,a.attnum) as 描述

from pg_attribute a where attstattarget=-1 and attrelid in(select oid
from pg_class where relname in (select relname as table_name from
pg_class where relkind=’r’ and relname like ‘exg_%’ order by relname))

order by 表名,attnum;

–数据字典–简版

select

(select relname from pg_class where oid=a.attrelid) as table_name,

(select (select description from pg_description where objoid = oid and
objsubid = 0) ||’表’||'(‘||relname ||’)’ from pg_class where
oid=a.attrelid) as 表名,

a.attname as 列名,

format_type(a.atttypid,a.atttypmod) as 类型,

col_description(a.attrelid,a.attnum) as 描述

from pg_attribute a where attstattarget=-1 and attrelid in(select oid
from pg_class where relname in (select relname as table_name from
pg_class where relkind=’r’ and relname like ‘exg_%’ order by relname))

order by table_name,attnum;*/

发表评论

电子邮件地址不会被公开。 必填项已用*标注