Oracle用法总结

Oracle 用法总结

(一)用户管理

1.创建用户
create user user_name --创建用户
identified by password
[default tablespace def_tablespace] --指定用户默认表空间
[temporary tablespace temp_tablespace] --指定用户临时表空间

2.用户授权与收回权限
grant DBA to user_name --给用户授予DBA角色权限
grant create user,create table to user_name(用户名)[with admin option](with admin option 选项表示该用户可以将这种系统权限转授予其他用户) --给用户授予系统权限
grant select ,update ,insert on table_name(表名) to user_name(用户名) [with grant option](with grant option 选项表示允许该用户将当前的对象权限转授予其他用户) --为用户添加对象权限
revoke create table ,create user from user_name(用户名) --收回用户的系统权限(建表，建用户)
revoke selece ,insert ,update ,delete on table_name(表名) from user_name (用户名) --收回用户的对象权限(查询，插入，更新，删除).

3.修改用户密码
alter user user_name --为用户修改密码
identified by new_password;

或者直接输入:password --修改当前用户密码.

4.修改用户默认表空间
alter user user_name --修改用户的默认表空间
default tablespace new_def_tablespace
temporary tablespace new_tem_tablespace;

5.锁定或解锁用户帐号
alter user 用户名; --锁定或解锁一个已存在的用户帐号
account lock|unlock (锁定/解锁)

6.删除用户
drop user user_name(用户名); --删除用户
drop user user_name(用户名)cascade; --删除用户的同时还要删掉用户所拥有的数据库对象（如表，索引，簇，视图等）；

7.创建角色
create role role_name(角色名)
[identified by role_password] ; --为角色创建密码

revoke role_name(角色名) from user_name(用户名); --收回用户的角色权限
drop role role_name; --删除角色

8.查看用户和系统相关信息
set timing on --查看执行效率。
set timing off --关掉效率记录。
select * from role_sys_privs; --查看当前用户的角色信息
select * from user_sys_privs; --查看当前用户授予的权限；
select * from user_role_privs; --查看当前用户的角色信息；
SELECT * FROM USER_PASSWORD_LIMITS; --查看授予用户的密码配置文件参数
select * from user_users; --查看当前用户的帐号信息
select * from RESOURCE_COST; --查看所有资源的耗费情况
select constraint_name,constraint_type,status from user_constraints where table_name='表名'; --查看指定表的约束信息

9.常用用户系统权限和对象权限
(常用Oracle系统权限)
create user 可以创建用户的帐号
drop user 可以删除用户的帐号
create session 连接到数据库服务器并能创建对话
cr

eate table 在用户自己的模式中创建表
create any table 在数据库的任何模式下创建表
create view 在用户自己的模式中创建视图
create any view 在数据库的任何模式下创建视图
create type 在用户自己的模式中创建类型
create any type 在数据库的任何模式下创建类型
create procedure 在用户自己的模式中创建过程
create trigger 在用户自己的模式中创建触发器
create synonym 在用户自己的模式中创建同义词
create sequence 在用户自己的模式中创建序列
insert any table 在数据库的所有表中进行插入记录操作
select any table 在数据库的所有表中进行查询操作
drop any table 可以删除或卸掉数据库中的任何表
update any table 在数据库的所有表中进行更新操作
delete any table 在数据库的所有表中进行删除记录操作
execute any procedure 在数据库中执行任何过程
execute any type 在数据库中能引用和执行数据库中任何类型的方法
由于系统权限过于强大，Oracle推荐尽量避免授予非DBA用户带有any的系统权限，尤其避免用户拥有对于数据字典操作的权限。

(常用Oracle对象权限)
select 可以执行检索操作
insert 可以执行数据插入操作
update 可以执行数据更新操作
delete 可以执行删除数据操作
execute 可以执行过程等操作
alter 可以修改对象属性操作




(二)表操作

connect system/manager --连接数据库 (cmd中连接数据库：sqlplus system/manager@byfwl)

1.创建表
create [global temporary]table table_name
( column_name type[constraint])
[on commit {delete|preserve}now]
[tablespace tablespace_name]; --指定该表所在的表空间，如果没有指定，那么该表将被放置在当前用户默认的表空间中。

global temporary 选项用于指定该表中的数据是临时的，表示创建一个临时表，临时表中的数据的有效持续时间由on commit选项指定。
on commit 选项控制临时表中的数据的持续时间
delete 选项表示临时表中的数据将在事务结束时被删除
preserve 选项表示临时表中的数据将在会话结束时被删除。

2.查看表结构
desc/describe table_name； --查看表结构

3.修改表结构
alter table table_name --增加列 alter table table_name --更新列
add column_name type; modify column_name type;

alter table table_name --删除列
drop column column_name ;

alter table table_name --更新列值为空或不为空
modify column_name not null/null;

alter table table_name --创建主键约束
add constraint constraint_name(约束名) primary key(column_name[,column_name]);

alter table table_name --创建惟一约束
add constraint constraint_name(约束名) unique(column_nam

e[,column_name];

alter table table_name
add constraint constraint_name(约束名) foreign key(column_name) references ref_table_name(参照的表名)(ref_column_name)(参照的字段名)
[on delete cascade] --级联删除
[on update cascade]; --级联更新

alter table table_name --创建检查约束
add constraint constraint_name check(expression)(检查的条件表达式);

alter table table_name --创建缺省约束
modify column_name type(列的类型) default(constraint_expression)(指定缺省值)

alter table table_name --禁止或激活约束
disable(禁止)/enable(激活)[novalidate](表示激活约束时不验证表中已有的数据是否满足约束的定义，没有这个关键字，则需要验证) constraint constraint_name(约束名);

alter table table_name --删除约束
drop constraint constraint_name(约束名);

rename old_table_name(旧表名) to new_table_name(新表名); --修改表名
drop table table_name --删除表

truncate table table_name; --清空表资料(完全清空，不具有事务特性，不能回滚)

4.注释表
comment on table table_name(表名) is table_comment(注释内容) --为表加注释
comment on column table_name.column_name is column_comment --为表中目标列加注释





(三)合并操作
merger into table_name_1 u
using table_name_2
on(condition_expression)
when matched then
update_statement
when not matched then
insert_statement

table_name_1 表示将要把数据合并进来的目标表名称
table_name_2 表示在合并数据时另外一个数据来源表名称
contidition_expression 表示合并数据是否相同的判断条件
update_statement 表示当合并条件匹配时执行的更新数据语句。与一般update语句惟一不同的是，在这里不需要提供表名称，因为表名称已经在table_name_1参数中指定了。
insert_statement 表示当合并条件不匹配时执行的插入数据语句。与一般insert语句惟一不同的是，在这里不需要提供表名称，因为表名称已经由table_name_1参数指定了。

(例子: merge into authors_old o
using authors_new n
on (o.author_id=n.author_id)
when matched then
update
set o.first_name=n.first_name,
st_name=st_name
o.birthdate=n.birthdate
when not matched then
insert (o.author_id,o.first_name,st_name,o.birthdate)values(n.author_id,n.first_name,st_name,n.birthdate);
注：表结构应该完全相同)




(四)查询操作

1.闪回查询
闪回查询是通过使用dbms_flashback程序包完成，由sys用户拥有，必须拥有该程序包的execute权限才可以执行闪回查询。为用户授予权限：
grant execute on dbms_flashback to user_name(用户帐号)
基于时间的闪回查询语法：
execute dbms_flashback.enable_at_time(datetime) --datetime表示将要闪回查询的时间
如：execute dbms_flashback.enabl

e_at_time(sysdate -1/1440);
基于系统改变号的闪回查询语法：
execute dbms_flashback.enable_at_system_change_number(scn); --scn表示将要把数据闪回查询至的系统改变号，系统改变号可以使用dbms_flashback 程序包的get_system_change_number()过程获得。
如：execute :current_scn :=dbms_flashback.get_system_change_number();
execute dbms_flashback.enable_at_system_change_number(:current_scn);
闪回查询执行完毕后，应该关闭闪回查询的功能，语法：execute dbms_flashback.disable

2.查询分类：
按连接类型查询分为：内查询，外查询，交叉查询，自查询
按子查询分为：单列单行子查询，单列多行子查询，多列单行子查询，多列多行子查询，关联子查询，嵌套子查询。
集合查询(union ,union all)，层次查询；

3.集合查询
union all --返回查询语句中的所有行，包括重复的数据行
union --返回查询语句中的所有行，但是不包括重复的数据行
intersect --返回在两个查询语句中都出现的数据行
minus --返回第一个查询中的数据行减去出现在第二个查询语句中的数据行后的差；

4.层次查询语法：
select level,column_name,expression,..from table_name [where where_condition]
start with start_condition
connect by prior prior_condition

5.情景查询语法：
decode(value_expression,search_value,result_value,default_value);

value_expression 参数表示将要与搜索值比较的值或表达式
search_value 参数表示将要的搜索的值
result_value 参数表示search_value 参数与value_expression参数相等时value_expression参数的取值结果
default_value 参数表示search_value 参数与value_expression参数不相等时value_expression参数的取值结果

如：select isbn,title_chinese,book_type_id ,decode(book_type_id,'11','经济类图书','12','历史类图书','其他类型图书') from books;
当图书类型代码是11时，显示'经济类图书'，当图书类型代码是12时,显示'历史类图书'，两者都不是时显示其他类型图书'。

6.分类汇总
select column_list from table_name where where_condition group by group_column_list having group_condition;

7. case 表达式
case search_expression
when expression_1 then display_result_1
when expression_2 then display_result_2
.......
when expression_n then display_result_n
else default_display_result
end





(五)常用函数

1.字符函数：
select ascii('a') from dual; --返回字符a的ascii值；
select char(35) from dual ; --返回ascii值35对应的的字符；
select length('hahahah') from dual; --统计'hahahah'的字符个数；
select lengthb('哈哈中国') from dual; --统计'哈哈中国'的字节数；
select lower('AAADsss') from dual; --将'AAADsss'中大写字母转换成小写字母；
select substr('dhdhhdhd',2,6)

from dual; --返回从字符串'dhdhhdhd'的第2位开始往后数6个字符的字符串；
select nvl2(emp_name,'白云飞','中国') from dual; --如果emp_name列为空则返回白云飞，否则返回中国；
ltrim(x[,trim_string]) --删除左空格。
rtrim(x[,trim_string]) --删除右空格
trim() --删除左右空格
select concat(emp_id,emp_name) from emp; --返回字段emp_id与emp_name的连接，这种连接是紧密连接，两个字符串之间没有空格；

2.数学函数
select ceil(12.345) from dual; --返回大于或等于12.345的最大整数值，该执行结果为13；
select mod(10,3) from dual; --返回10除3的余数；

3.转换函数
select to_char(123.456,'11.11.11') from dual; --将123.456以11.11.11的格式转换成字符形式；
select to_number('123456') from dual; --将'123456'转换成数字类型；
select to_date('2006,6月15','yyyy,month,dd') from dual; --将'2006,6月15'转换成以'yyyy,month,dd'格式的日期形式；

4.合计函数
avg() --平均值
count() --统计数量
max() --最大值
min() --最小值
median() --中位数
stddev() --标准差
sum() --汇总
variance() --方差

5.日期函数
add_months(x,y) --在x上增加y个月，如果y是负数，表示从x中减去y个月
last_day(x) --返回包含在x中的月份的最后一天
months_between(x,y) --返回x和y之间的月数，从日历上来看，如果x在y之前，返回负数，否则，返回正数；
next_day(x,day) --返回紧接着x的下一天，day是一个字符串；
round(x[,unit]) --圆整x，在默认情况下，x被圆整到最接近的一天，可以使用unit参数提供圆整的单位，例如mm表示圆整到最接近月的第一天；
sysdate() --返回系统当前时间；
trunk(x[,unit]) --截断x，默认情况下，x被截断为当天的开始。可以使用unit参数提供截断的单位，例如mm表示截断到该月的第一天；




(六)数据库编程结构

1.条件结构：

if condition_1 then
executable_statements_1
elseif condition_2 then
executable_statements_2
elseif condition_3 then
executable_statements_3
else
executable_statements_4
end if

2.循环结构：

简单循环结构：loop .....executable_statements..... end loop;

while循环： while condition loop
executable_statements
end loop;

for循环结构: for loop_variable_name in [reverse] lower_bound .. upper_bound loop
executable_statements
end loop;
loop_variable_name 参数指定循环变量。
reverse关键字指定循环变量按照递减方式增长，这时，循环变量的初始值是最高边界，然后按照步长递减。
lower 参数指定循环变量的最低边界
upper 参数指定循环变量的最高边界
executable

_statements 循环体中的语句





(七)其他创建操作

1.创建游标
cursor cursor_name(游标名) is select_statement(包括的结果集); --声明游标
open cursor_name --打开游标
fetch cursor_name into variable_list(变量列表,与select中的select_list列表相对应) --从游标中取数据
close cursor_name --关闭游标

2.创建存储过程
create [or replace](判断系统是否有重名，如果有则覆盖)procedure procedure_name(存储过程名)
[(parameter_name [in|out|in out]type[,....])](参数列表，需要带括号)
is|as
begin
procedure_body
end procedure_name(可不带过程名);

in|out|in out 关键字指定过程的模式，in 是默认值，表示该参数的值在过程运行过程中不被改变；out关键字表示该参数的值只在运行过程中赋值；in out 表示该参数的值既可以事先赋值，也可以在过程运行过程中设置；


execute procedure_name --执行存储过程

3.创建函数
create[or replace] function function_name
[(parameter_name [in|out|in out]type[,....])](参数列表，需要带括号)
return type
is|as
begin
function_body
end function_name(可不带函数名);
in|out|in out 关键字指定过程的模式，in 是默认值，表示该参数的值在过程运行过程中不被改变；out关键字表示该参数的值只在运行过程中赋值；in out 表示该参数的值既可以事先赋值，也可以在过程运行过程中设置；

4.创建程序包
create [or replace] package package_name --创建程序包规格说明
is|as
package_specification(指定该程序包的规格说明，包括过程列表，函数列表，变量和游标等)
end package_name;

create [or replace] package body package_name --创建程序包体
is|as
package_body
end package_name;

5.创建触发器
create or replace trigger trigger_name
{before|after|instead of} trigger_event
on table_name
[for each row[when trigger_condition]]
begin
trigger_body
end trigger_name;

before|after|instead of关键字用于指定触发器何时执行。before关键字指定在触发事件执行之前执行，after关键字指定在触发事件执行之后执行；instead of 关键字指定触发事件不执行，而执行触发器本身的操作；
for each row关键字指定触发器为行级触发器，表示该触发器对影响到的每一行数据都触发执行一次。如果没有指定使用该关键字，那么表示创建语句触发器，这时，无论影响到的行有多少，触发器只执行一次，既所有语句执行完后，触发触发器。

Oracle触发器也有两个临时表，分别为new 和old,在引用时需要加上冒号，如:if :old.emp_id=emp.emp_id then ....


6.创建序列
create sequence sequence_name
[start with start_number]
[increment by increment_number]
[{maxvalue maximum_number|nomaxvalue}]
[{minvalue minimum_number|nominvalue}]
[{cycle|nocycle}]
[{cache cache_

number|nocache}]
[{order|noorder}]

start_number参数指定序列的起始整数值，默认值是1；
increment_number参数用于指定序列的步长，即增量值。默认值是1。该参数指定的步长的绝对值应该小于maximum_number参数和minimum_number参数之差。
maximum_number参数用于指定序列的最大值，nomaxvalue关键字表示没有最大值的限制，这是默认值；
minimum_number参数用于指定序列的最小值，nominvalue关键字表示没有最小值的限制，这是默认值；
cycle关键字表示序列数值循环生成。当序列按照升序增长时，如果达到了最大值，则下一个值是最小值。如果按照降序增长，则当序列达到了最小值时，则下一个值是最大值；
nocycle关键字表示序列不能生成循环值。这是默认值。
cache_number参数指定保留在内存中整数的数量。默认值是20，最小值是2；
nocache关键字表示不指定存储在内存中的整数的数量；
order关键表示按照顺序生成序列值
noorder关键字不强调按照顺序生成序列值。


例子：
create sequence test_seq increment by 1 start with 1 maxvalue 100 minvalue 1 cycle ; --创建序列.
select test_seq.currval from dual; --查看当前序列值。
select test_seq.nextval from dual; --取出当前序列的下一个值。

实用用法： insert into emp(id) values(to_char(sysdate,'yyyymmdd')||test_seq.nextval);

注意：sequence 每提取select一次，nextval则其值自动按increment by制定的间隔增加，提取当前值不会对sequence的值有所改变。


7.创建索引
create [unique](惟一索引) index index_name(索引名)
on table_name(column_name[,column_name...])
[tablespace tablespace_name](指定索引所要位于的表空间名称);

alter index index_name --删除索引
drop index index_name;

8.创建视图
create [or replace] view view_name
[(alias_name[,alias_name,.....])]
as
select_statement
[with{check option|read only} constraint constraint_name];

alias_name 参数用于指定将要在视图中使用的列名称，该列名称与select_statement参数中的列名称想对应，是这些列名称的别名；
constraint constraint_name 子句用于指定check option 关键字或read only关键字起作用的约束条件；
例子：
create or replace view v_author_title
(frame,lname,title,old_price,new_price)
as
select first_name,last_name,title_chinese,price ,price*0.8 from author join(select author_id ,title_chinese,price
from write_book inner join books on write_book.book_id=books.book_id )a
on authors.author_id=a.author_id
where price>=50;

9.创建同义词
create [public](表示创建所有用户都可以使用的公共同义词) synonym synonym_name(同义词名) for object_name(表示将要替代的对象名称).
创建同义词应该具有create synonym 系统权限，创建公共同义词需要具有create publ

ic synonym系统权限。



(八)其他知识点

1.事务
commit --提交
rollback --回滚

2.SQL*PLUS常用命令：
help 命令，功能：可以查询命令的用法。 语法：help[topic] --topic参数表示要查询的命令名称；
prompt命令，功能：可以在屏幕上输出一行数据。语法：prompt prompt_text; --prompt_text 参数用于指定将要在屏幕上显示的提示信息；
spool 命令，功能：可以把查询结果保存在一个文件中。 语法：spool file_name|一系列执行语句|off (关掉功能) 如：spool test.sql (开始) ..........spool off (结束);
show 命令，功能：可以得到许多有用的信息。如：show users;

3.变量
临时变量：临时变量也被称为替换变量，在select 语句中，如果在某个变量前面使用了&符号，那么表示该变量是一个临时变量。在执行select语句过程中，系统会提示用户为该变量提供一个具体的数据；如果不希望系统提示，应该使用&&临时变量。
给变量赋值也需要加冒号：如：declare emp_id int --声明变量emp_id，类型int;
赋值: emp_id:=15;
4.列别名
select emp_id as 编号 from emp;或者select emp_id 编号 from emp;

5.合并列的输出结果
select emp_id ||':'|| emp_name 编号姓名 from emp; --Oracle 中用||实现字符串连接;

6.空值判断函数
select nvl(emp_name,'白云飞') from emp; --判断emp_name列是否有空值，如果有则替换为‘白云飞’；

7. distinct 关键字
select distinct emp_name from emp; --去除重复行

8. rownum的使用(功能相当于SQL Server中top关键字)
select * from emp where rownum<=10 --取出emp表中前10条记录；

9. 过滤数据
比较运算符(需要注意的) ：!= 不等于, any 使用清单中的任何一个值来比较, all 使用清单中的所有值来比较

10. SQL运算符
like 按照指定的模式匹配 , in 匹配值的清单, between 匹配范围内的值 , is null 与空值匹配 , is nan 与非数字值匹配, is infinite 与无穷binary_float和binary_double值匹配；
not 取反 , not like ,not in ,not between, is not null ,is not nan, is not infinite;


(九)实用操作与案例

1. 创建相关表

--创建人员花名册
create table emp
(
emp_id char(5) primary key, --工号
emp_name char(20) not null, --姓名
dept_id char(2) ,
age int, --年龄
tel char(10), --号码
bdate date --入职时间
);

--人员数据初始化
insert into emp(emp_id,emp_name,dept_id) values('00001','胡','01');
insert into emp(emp_id,emp_name,dept_id) values('00002','温','02');
insert into emp(emp_id,emp_name,dept_id) values('00003','李','03');
insert into emp(emp_id,emp_name,dept_id) values('00004','张','03');

insert into emp(emp_id,emp_name,dept_id) values('00005','A','01');
insert into emp(emp_id,emp_name,dept_id) values('00006','B','02');
insert into emp(emp_id,emp_name,dept_id) values('00007','C','03');
insert into emp(emp_id,emp_name,dept_id) values('00008','D','03');

--创建薪水表
create table salary
(
emp_id char(5), --工号
dept_id char(5), --部门编号
salary numeric(7,2), --薪水
sdate char(6) --年月
);

--创建考勤表
create table work
(
emp_id char(5), --工号
dept_id char(2), --部门编号
sdate date, --上班时间
edate date --下班时间
);

insert into work(emp_id,dept_id,sdate,edate) select emp_id,dept_id, add_months(sysdate,1),last_day(sysdate) from emp; --insert into table_name select 的用法.

2. 移动手机业务管理案例(存储过程实现)
create or replace procedure sp_bus_release_resource(
lv_telephone in varchar2,--手机号
ln_return out number --返回值
-- 0-正确 -1无该手机用户, -2资料备份出错, -3删除客户资料出错,
-- -4删除用户资料出错, -5删除新功能资料出错
)
as
lv_contract_id cbtd_bus_user.contract_id%type;--用户合同号
ln_user_id cbtd_bus_er_id%type; --用户ID
lv_hear_id cbtd_bus_user.hear_id%type; --受理序号
lv_status_id cbtd_bus_user.status_id%type; --用户状态
ln_ret number; --备份资料存储过程的返回值：0－正确，其它－错误
lv_ret varchar2(100);--错误信息
begin
ln_return:=0;
begin --由手机号取用户ID、合同号、受理序号和用户状态
select user_id,contract_id,hear_id,status_id
into ln_user_id,lv_contract_id,lv_hear_id,lv_status_id
from cbtd_bus_user
where telephone=lv_telephone;
exception
when no_data_found then
dbms_output.put_line('not found the user data'||sqlerrm);
ln_return:=-1; --无该手机用户
return;
end;

if lv_status_id<>'4' then --该用户状态不为'拆机'则作资料全备份
sp_backup_all_busi_data('核销',ln_user_id,ln_ret,lv_ret);
if ln_ret<>0 then
dbms_output.put_line('error:'||lv_ret);
ln_return:=-2; --资料备份出错
end if;
end if;

begin --删除客户资料
delete cbtd_bus_customer where contract_id=lv_contract_id;
exception
when others then
dbms_output.put_line('del customer error:'||sqlerrm);
ln_return:=-3; --删除客户资料出错
end;

begin --删除用户资料
delete cbtd_bus_user where user_id=ln_user_id;
exception
when others then
dbms_output.put_line('del user error:'||sqlerrm);
ln_return:=-4; --删除用户资料出错
end;

begin --删除新功能资料
delete cbtd_bus_new_func where user_id=ln_user_id;
exception
when others the

n
dbms_output.put_line('del user error:'||sqlerrm);
ln_return:=-5; --删除新功能资料出错
end;
dbms_output.put_line(sqlcode||sqlerrm);
end;

3. 移动用户积分回馈业务管理案例(触发器实现)
--用户积分回馈奖项库写赠送时长记录时向赠送时长记录表写相应记录
create or replace trigger tr_grd_exchange_to_time_log
after insert on cbtd_grd_exchange for each row
declare v_tel varchar2(13);
begin
if :new.award_type='0' then--赠送时长
if :new.acpt_tel is null then
v_tel:=:new.telephone;
else
v_tel:=:new.acpt_tel;
end if;
begin
insert into cbtb_grd_time_log
(exch_id,exch_item,telephone,exch_date,
start_time,stop_time,time_long)
values (:new.exch_id,:new.exch_item,v_tel,:new.exch_time,
:new.start_time,:new.stop_time,:new.award_value);
exception when others then
dbms_output.put_line('写赠送时长记录表出错!'||sqlerrm);
raise;
end;
end if;
end;
/


4. 移动答题有奖业务管理案例

--统计交互式开始日到结束日的得分情况
--统计答题卡的得分情况需将'99'替换为'98'
--统计某段时间的将mdate>=to_char(sysdate,'yyyymmdd')中sysdate替换为开始日期
-- 将mdate<=to_char(sysdate,'yyyymmdd')中sysdate替换为结束日期

select s1.areaname 地局,score0 总参与人数,score1 得1分人数,score2 得2分人数,score3 得3分以上人数
from
(
select a.area_id,a.area_name areaname, nvl(s.mcount,0) score0
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='0' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and a.area_id<>'Z'
union
select 'Z','全省', sum(nvl(s.mcount,0)) score0
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='0' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and not a.area_id in ('Z','S','W')
) s0,
(
select a.area_id,a.area_name areaname, nvl(s.mcount,0) score1
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='1' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and a.area_id<>'Z'
union
select 'Z','全省', sum(nvl(s.mcount,0)) score1
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='1' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')

group by method,mscore,marea
) s
where s.marea(+)=a.area_id and not a.area_id in ('Z','S','W')
) s1,
(
select a.area_id,a.area_name areaname, nvl(s.mcount,0) score2
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='2' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and a.area_id<>'Z'
union
select 'Z','全省', sum(nvl(s.mcount,0)) score2
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='2' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and not a.area_id in ('Z','S','W')
) s2,
(
select a.area_id,a.area_name areaname, nvl(s.mcount,0) score3
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='3+' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and a.area_id<>'Z'
union
select 'Z','全省', sum(nvl(s.mcount,0)) score3
from tb_pub_area a,
(
select marea,sum(mcount) mcount
from td_smss_score_stat
where method='99' and
mscore='3+' and
mdate>=to_char(sysdate,'yyyymmdd') and
mdate<=to_char(sysdate,'yyyymmdd')
group by method,mscore,marea
) s
where s.marea(+)=a.area_id and not a.area_id in ('Z','S','W')
) s3
where s0.areaname=s1.areaname and
s1.areaname=s2.areaname and
s2.areaname=s3.areaname
order by s0.area_id;