oracle DDL

🧙 Questions

• 常规表操作
• 查询表的所有字段
• 查询所有表
• 增删改查

☄️ Ideas

常规表操作

-- 创建表
create table users
(
    username varchar2(100),
    age      number
)

-- 插入数据 
INSERT INTO ISPONG_TABLE (USERNAME, AGE, BIRTH, LUCKY_DATE) VALUES ('zhangsan', NULL, TO_DATE('2020-12-12', 'YYYY-MM-DD'),TO_TIMESTAMP('2020-12-12 12:12:12', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

-- 查询表数据
SELECT * FROM ISPONG_TABLE;

-- 删除表
DROP TABLE ISPONG_TABLE;

查询表的所有字段

select field,
select comments,
       field,
       concat(concat(concat(type, '('), DECODE(type, 'NUMBER', DECODE(firstLen, null, '*', firstLen), '')),
              DECODE(type, 'NUMBER', concat(DECODE(firstLen, null, '', concat(',', endLen)), ')'),
                     concat(len, ')'))) as type
from (
         select col.COLUMN_NAME    as field,
                col.DATA_TYPE      as type,
                col.DATA_LENGTH    as len,
                col.DATA_PRECISION as firstLen,
                col.DATA_SCALE     as endLen,
                comments.COMMENTS  as comments
         from ALL_TAB_COLUMNS col
                  left join All_COL_COMMENTS comments
                            on col.OWNER = comments.OWNER and col.TABLE_NAME = comments.TABLE_NAME and
                               col.COLUMN_NAME = comments.COLUMN_NAME
         where col.TABLE_NAME = 'ISPONG_TABLE'
         order by col.COLUMN_ID asc
     )

查询所有表名

select table_name from user_tables;

增删改查

-- 删除
-- DELETE FROM ISPONG_TABLE WHERE 1=1;
DELETE FROM ${table_name} WHERE ${condition}
-- 查询
SELECT * FROM ${table_name} WHERE ${condition}
-- 新增 (添加数据需要commit才会保存)
INSERT INTO ISPONG_TABLE (USERNAME, AGE, BIRTH) VALUES ('zhangsan', 12, TO_DATE('2020-12-12 12:12:12', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

查询当前数据库

-- 查询当前数据库
-- 只有sysdba权限可以执行
select name from v$database;

🔗 Links

• oracle website