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;
sql语句
最新推荐文章于 2021-09-05 23:36:04 发布