DECLARE V_TABLENAME VARCHAR2(100):='EMP'; V_SQL VARCHAR2(4000); BEGIN DBMS_OUTPUT.PUT_LINE('建表语句如下:'||'==>'); SELECT DBMS_METADATA.GET_DDL('TABLE',V_TABLENAME) INTO V_SQL FROM DUAL; DBMS_OUTPUT.PUT_LINE(V_SQL);
DBMS_OUTPUT.PUT_LINE('表的字段信息如下:'||'==>'); DBMS_OUTPUT.PUT_LINE('表名称'||' '||'表注释'||' '||'字段名称'||' '||'字段注释'||' '||'字段类型'||' '||'字段长度'||' '||'是否为空'); FOR I IN ( SELECT a.TABLE_NAME,c.COMMENTS AS TCOMMENTS,a.COLUMN_NAME,b.commentS AS CCOMMNETS,a.DATA_TYPE, DECODE(a.DATA_TYPE,'CHAR',a.CHAR_LENGTH,'VARCHAR2',a.CHAR_LENGTH,'NUMBER',a.DATA_PRECISION)||DECODE(a.DATA_TYPE,'NUMBER',','||a.DATA_SCALE) as length, NULLABLE FROM (SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,COLUMN_ID,NULLABLE,CHAR_LENGTH FROM USER_TAB_COLUMNS WHERE 1 = 1 ORDER BY TABLE_NAME) a, (SELECT * FROM USER_COL_COMMENTS) b, (SELECT * FROM USER_tab_COMMENTS) c WHERE a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME = c.TABLE_NAME AND A.TABLE_NAME = V_TABLENAME ORDER BY a.TABLE_NAME,a.COLUMN_ID ) LOOP DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||' '||I.TCOMMENTS||' '||I.COLUMN_NAME||' '||I.CCOMMNETS||' '||I.DATA_TYPE||' '||I.LENGTH||' '||I.NULLABLE); END LOOP;
表的字段信息如下:==> 表名称 表注释 字段名称 字段注释 字段类型 字段长度 是否为空 EMP EMPNO NUMBER 4,0 N EMP ENAME VARCHAR2 10 Y EMP JOB VARCHAR2 9 Y EMP MGR NUMBER 4,0 Y EMP HIREDATE DATE Y EMP SAL NUMBER 7,2 Y EMP COMM NUMBER 7,2 Y EMP DEPTNO NUMBER 2,0 Y 大致上是这个样子了,思路在这里.
创建一个存储过程,查看某一个表里面所有信息。 create procedure checklogin( name varchar2(20) in, pwd varchar2(20)in) as begin select * from userList where uName=@name and uPass=@pwd and flag=1 and state=0 end;这样写报错
DECLARE
V_TABLENAME VARCHAR2(100):='EMP';
V_SQL VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.PUT_LINE('建表语句如下:'||'==>');
SELECT DBMS_METADATA.GET_DDL('TABLE',V_TABLENAME) INTO V_SQL FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_SQL);
DBMS_OUTPUT.PUT_LINE('表的字段信息如下:'||'==>');
DBMS_OUTPUT.PUT_LINE('表名称'||' '||'表注释'||' '||'字段名称'||' '||'字段注释'||' '||'字段类型'||' '||'字段长度'||' '||'是否为空');
FOR I IN (
SELECT a.TABLE_NAME,c.COMMENTS AS TCOMMENTS,a.COLUMN_NAME,b.commentS AS CCOMMNETS,a.DATA_TYPE, DECODE(a.DATA_TYPE,'CHAR',a.CHAR_LENGTH,'VARCHAR2',a.CHAR_LENGTH,'NUMBER',a.DATA_PRECISION)||DECODE(a.DATA_TYPE,'NUMBER',','||a.DATA_SCALE) as length,
NULLABLE
FROM (SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,COLUMN_ID,NULLABLE,CHAR_LENGTH FROM USER_TAB_COLUMNS WHERE 1 = 1 ORDER BY TABLE_NAME) a,
(SELECT * FROM USER_COL_COMMENTS) b,
(SELECT * FROM USER_tab_COMMENTS) c
WHERE a.COLUMN_NAME = b.COLUMN_NAME
AND a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_NAME = c.TABLE_NAME
AND A.TABLE_NAME = V_TABLENAME
ORDER BY a.TABLE_NAME,a.COLUMN_ID
) LOOP
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME||' '||I.TCOMMENTS||' '||I.COLUMN_NAME||' '||I.CCOMMNETS||' '||I.DATA_TYPE||' '||I.LENGTH||' '||I.NULLABLE);
END LOOP;
END;
结果如下:
建表语句如下:==> CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
表的字段信息如下:==>
表名称 表注释 字段名称 字段注释 字段类型 字段长度 是否为空
EMP EMPNO NUMBER 4,0 N
EMP ENAME VARCHAR2 10 Y
EMP JOB VARCHAR2 9 Y
EMP MGR NUMBER 4,0 Y
EMP HIREDATE DATE Y
EMP SAL NUMBER 7,2 Y
EMP COMM NUMBER 7,2 Y
EMP DEPTNO NUMBER 2,0 Y
大致上是这个样子了,思路在这里.
create procedure checklogin(
name varchar2(20) in,
pwd varchar2(20)in)
as
begin
select * from userList where uName=@name and uPass=@pwd and flag=1 and state=0
end;这样写报错
你这样写肯定报错,select 语句查到的值送到哪里去呀?