sql语句

select patient_name,
patient_id,
       sex,
       age,
       marriage_status,
       charge_type,
       outp_clinic_doctor,
       admitted_dept,
       admitted_date,
       discharged_dept,
       discharged_date,
       family_address
from dm.patient_master_index
where admitted_date>=to_date(?,'dd/mm/yyyy')
and admitted_date<to_date(?,'dd/mm/yyyy')
and patient_id=?
limit 200


//插入语句
baseDao.excuteSql("insert into metadata.base_user_resource select nextval('metadata.base_user_resource_seq'),?,?", userId,resourceId);

//case 语句
SELECT
    (
        CASE C .city
        WHEN '杭州' THEN
            '浙江'
        WHEN '宁波' THEN
            '浙江'
        WHEN '南京' THEN
            '江苏'
        WHEN '苏州' THEN
            '江苏'
        ELSE
            '其他'
        END
    ) 省份,
    (SUM(C .gdp)) 总和
FROM
    rpt.city_gdp C
GROUP BY
省份
结果是  省  总和
       江苏 3211
       浙江 5454
//按字典排序
SELECT * FROM "metadata"."base_dept" order by   dept_name collate "zh_CN.utf8";

select 'select datasourceid, '''|| table_name || ''' name from ods.'|| table_name from dba_tables where owner='ODS';
结果：select datasourceid, 'TEST' name from ods.TEST

//查询某个用户下所有表的内容DP用户要大写
select * from dba_tables where owner=upper('dp')


//根据查询到的东西动态的拼接sql
select 'select datasourceid, '''|| table_name || ''' name from ods.'|| table_name from dba_tables where owner='ODS';
结果是：
1   select datasourceid, 'TEST' name from ods.TEST
2   select datasourceid, 'PEOPLE' name from ods.PEOPLE
3   select datasourceid, 'TAB_0000' name from ods.TAB_0000

//查找当前用户下某张表的字段信息(必须得是当前用户)
select *  from   user_tab_columns where table_name = upper('model_info')
//查找当前用户下的所有表信息
select * from user_tables;

//wm_concat()函数  词句只返回一条记录把查询到model_info里所有的model_name 和model_code 用逗号间隔开
select 'select * from '|| wm_concat(model_name ||' '||model_code) sss from  model_info

//通过查询另一张表批量插入（词句只插入1000条）
insert into base_dic (id, dic_code, dic_name,   isbn_code, parent_id ,is_leaf,level_num)    select     base_dic_seq.nextval, '" + result + "'," + mainDto.getDicName() + ", " + mainDto.getDicCode() + ", " + baseDic.getId()   + ",1,2" + " from ods." + mainDto.getOdsTableName() +" where rownum <1000

//查询字段信息
select * from  dba_col_comments
//查询所有字段信息包括字段类型等
select *  from all_tab_columns 
//查询某个存储过程的信息，包括该存储过程用到的表等
SELECT * FROM DBA_DEPENDENCIES

//查询所有的表
select * name from sys.all_tables


//查询错误信息 这个项目用来查询某个存储过程是否有误 如果有误的话则会有记录
select * from dba_errors
//查询某张表的sql语句（TABLE,表名，哪个用户下）
select DBMS_METADATA.GET_DDL('TABLE','BASE_DIC','PORTAL') from dual;

//递归查询
SELECT * FROM model.master_dept 
  START WITH ksmc like '幼儿%'
CONNECT BY id= PRIOR ksdm
union
SELECT * FROM model.master_dept 
  START WITH ksmc like '幼儿%'
CONNECT BY id = PRIOR flksdm

//将查询的值按特定顺序排序
order by instr('name,id',column_name)desc
//将所需要的字段，信息，注释，类型等 按照建表字段顺序排序，建立一个视图.
create or replace view metadata.field_info as
select c.table_name table_code,
       c.column_name field_code,
       case
         when c.comments is null then
          c.column_name
         else
          c.comments
       end field_name,
       "LOWER"(data_type) field_type
  from dba_col_comments c, dba_tab_columns t
 where c.owner = t.OWNER
   and c.table_name = t.table_name
   and c.column_name = t.column_name
   and c.owner = 'MODEL'
 order by t.column_id;