oracle常用sql

-- 查看建表sql
select dbms_metadata.get_ddl('TABLE',upper('guQuotationMain')) from dual; 

select dbms_metadata.get_ddl('TABLE',upper('gguser')) from dual; 

-- 查看字段注释
SELECT 
    B.COLUMN_NAME,
    A .DATA_TYPE || '(' || A .CHAR_LENGTH || ')',
    B.COMMENTS,
    A .COLUMN_ID
FROM
    USER_TAB_COLUMNS A
INNER JOIN USER_COL_COMMENTS B ON A .TABLE_NAME = B.TABLE_NAME
AND A .COLUMN_NAME = B.COLUMN_NAME
WHERE
    A .TABLE_NAME = UPPER ('GuPolicyRelatedParty')
ORDER BY
    COLUMN_ID;

SELECT 
    B.COLUMN_NAME,
    A .DATA_TYPE || '(' || A .CHAR_LENGTH || ')',
    B.COMMENTS,
    A .COLUMN_ID
FROM
    USER_TAB_COLUMNS A
INNER JOIN USER_COL_COMMENTS B ON A .TABLE_NAME = B.TABLE_NAME
AND A .COLUMN_NAME = B.COLUMN_NAME
WHERE
    A .TABLE_NAME = UPPER ('GGSERVICECALLHISTORY')
ORDER BY
    COLUMN_ID;

-- 查看表是否有某个字段
-- 查看字段注释
SELECT DISTINCT
    B.COLUMN_NAME,
    A .DATA_TYPE || '(' || A .CHAR_LENGTH || ')' data_type,
    B.COMMENTS,
    A .COLUMN_ID
FROM
    USER_TAB_COLUMNS A
INNER JOIN USER_COL_COMMENTS B ON A .TABLE_NAME = B.TABLE_NAME
AND A .COLUMN_NAME = B.COLUMN_NAME
WHERE
    A .TABLE_NAME = UPPER ('gguser') and B.COLUMN_NAME=upper('USERCODE')

--  查询表注释
SELECT t1.TABLE_NAME,t2.comments 
FROM USER_TABLES t1 left join user_tab_comments t2 on (t1.TABLE_NAME=t2.TABLE_NAME and t2.TABLE_TYPE='TABLE')
where t1.TABLE_NAME=upper('guQuotationmain')

-- 查询表索引
select t1.index_name, t1.column_name,t2.uniqueness
from  user_ind_columns t1 LEFT JOIN user_indexes t2 on t1.index_name=t2.index_name
WHERE t1.table_name=upper('guQuotationMain');

--查看存储过程
select DISTINCT(name) from user_source where type='PROCEDURE';

select rownum , text from user_source where type='PROCEDURE' and name='GET_B2B_QUOTATIONINFO' 
order by line;

-- 创建存储过程
create or replace PROCEDURE GET_B2B_QUOTATIONINFO
IS
BEGIN
    select 1 from dual;
end GET_B2B_QUOTATIONINFO;

-- 删除存储过程
drop procedure GET_B2B_QUOTATIONINFO;

-- 调用存储过程
BEGIN
    GET_B2B_QUOTATIONINFO()
end GET_B2B_QUOTATIONINFO;