精华内容
下载资源
问答
  • oracle数据求和

    2012-02-15 13:39:00
    数据表中有8条数据,每条都包含一个时间类型的数据,要求这8个时间排序后两两相减再求和, sum((时间2-时间1) + ((时间4-时间3) + ((时间6-时间5) + ((时间8-时间7)),可否一个sql求出结果?
  • 上面语句表示,根据col1分组,在分组内部根据col2排序,这里的“别名”的值就是每内部排序后的序列号(组内连续的、唯一的),“[partition by col1] ”可以省略。
  • oracle中连续求和分析函数为sum(…) over(…)。 用法: select bdcode,sum(1) over(order by bdcode) aa from bd_bdinfo 规则: 1、sum(…) over( ),对...3、sum(…) over( partition by… ),同组内所行求和 4、...

    oracle中连续求和分析函数为sum(…) over(…)。

    用法:

    select bdcode,sum(1) over(order by bdcode) aa from bd_bdinfo

    规则:

    1、sum(…) over( ),对所有行求和

    2、sum(…) over( order by … ), 连续求和

    3、sum(…) over( partition by… ),同组内所行求和

    4、sum(…) over( partition by… order by … ),同第1点中的排序求和原理,只是范围限制在组内

    扩展资料
    oracle 单行函数

    (1)concat(str1,str2)字符串拼接函数

    (2)initcap(str)将每个单词首字母大写,其他字母小写

    (3)instr(x,find_string[,start][,occurrence])返回指定字符串在某字符串中的位置,可以指定搜索的开始位置和返回第几次搜索出来的结果

    (4)length(str)返回表达式中的字符数

    (5)lengthb(str)返回表达式中的字节数

    (6)lower(str)将字符串转换为小写

    (7)upper(str)将字符串转换为大写

    (8)lpad(str,width[,pad_string])当字符串长度不够时,左填充补齐,可以指定补齐时用什么字符补齐,若不指定,则以空格补齐

    (9)rpad(str,width[,pad_string])当字符串长度不够时,右填充补齐,原理同左填充

    (10)ltrim(x[,trim_string])从字符串左侧去除指定的所有字符串,若没有指定去除的字符串,则默认去除左侧空白符

    展开全文
  • oracle动态求和分组

    2021-03-10 00:01:50
    问题描述: 这里有很多条记录,其中需要按照数量字段,进行合并 合并规则: 按照合并数量和>=6的合并为一条记录 合并之后的记录,有新的ID 思路:先根据数量排序,再根据和为6进行分组标记,然后根据标记求和,最后筛选记录;...

    问题描述:

    这里有很多条记录,其中需要按照数量字段,进行合并
    合并规则:
    按照合并数量和>=6的合并为一条记录
    合并之后的记录,有新的ID
    在这里插入图片描述
    思路:先根据数量排序,再根据和为6进行分组标记,然后根据标记求和,最后筛选记录;

    这里的难点就是: 分组标记,也就是指定n行为一组,这里就需要使用trunc(rownum - 1) / n)+1这种来标记行;
    如果是指定行数的循环标记,则使用mod结合row_number()来实现;

    下面的方法不算完美,如果要更简洁的做法:可以使用model实现;

    -- 创建表
    drop table yusydemo;
    create table yusydemo(
           id number(18) primary key,
           prodID varchar2(4),
           money number(8),
           prodcount int
    );
    
    -- 创建序列
    create sequence seq_demo_id 
    minvalue 1
    start with 1
    increment by 1
    nomaxvalue
    nocache
    nocycle;
    
    -- 创建触发器
    create or replace trigger trigger_demo_id
    before insert on yusydemo for each row when (new.id is null)
    begin 
      select seq_demo_id.nextval into :new.id from dual;
    end;
    
    
    
    -- 初始化数据
    insert into yusydemo (prodID, money, prodcount)
    values ('aaa', '2000', 1);
    
    insert into yusydemo (prodID, money, prodcount)
    values ('aaa', '1111', 2);
    
    insert into yusydemo (prodID, money, prodcount)
    values ('aaa', '222', 3);
    insert into yusydemo (prodID, money, prodcount)
    values ('aaa', '33', 4);
    insert into yusydemo (prodID, money, prodcount)
    values ('aaa', '4444', 5);
    
    select * from yusydemo;
    
    
    --基本分组的数据已经完成 
    with dd as
     (select countflag || '生成newID' newid,
             sum(cc.prodcount) over(partition by countflag) weekSum,
             cc.*
        from (select case
                       when bb.prodcount = 1 then
                        trunc((bb.rn - 1) / 6) + 1 || '#' || bb.prodcount
                       when bb.prodcount = 2 then
                        trunc((bb.rn - 1) / 3) + 1 || '#' || bb.prodcount
                       when bb.prodcount in (3, 4, 5) then
                        trunc((bb.rn - 1) / 2) + 1 || '#' || bb.prodcount
                     end countflag, --这个就是分组标记
                     bb.*
                from (select row_number() over(partition by aa.prodcount order by prodcount) rn,
                             aa.*
                        from yusydemo aa
                       where aa.prodcount < 6) bb) cc
       order by cc.prodcount, cc.countflag)
    --如果想要筛选每组的一条记录出来,也就是 合并之后的那一条数据  
    select *
      from dd
     where (dd.countflag, dd.rn) in
           (select dd.countflag, min(dd.rn)
              from dd
             where dd.weekSum > 6
             group by dd.countflag);
     
     
     
    --2.分组合并之后的结果集小于6的单独处理
    --基本分组的数据已经完成.这里每组可能会剩余一下未合并的数据,这里手动处理一下就可以了
    with dd as
     (select countflag || '生成newID' newid,
             sum(cc.prodcount) over(partition by countflag) weekSum,
             cc.*
        from (select case
                       when bb.prodcount = 1 then
                        trunc((bb.rn - 1) / 6) + 1 || '#' || bb.prodcount
                       when bb.prodcount = 2 then
                        trunc((bb.rn - 1) / 3) + 1 || '#' || bb.prodcount
                       when bb.prodcount in (3, 4, 5) then
                        trunc((bb.rn - 1) / 2) + 1 || '#' || bb.prodcount
                     end countflag, --这个就是分组标记
                     bb.*
                from (select row_number() over(partition by aa.prodcount order by prodcount) rn,
                             aa.*
                        from yusydemo aa
                       where aa.prodcount < 6) bb) cc
       order by cc.prodcount, cc.countflag)
       select * from dd where dd.weekSum<6;
    
    展开全文
  • 在开发时,我们经常会遇到以... 可见:oracle 数据库中对空值的事不同的,count 函数认为没有此项,而sum函数不计算。  结论: 求和用累加 sum ,求行的个数用累计 count 本文来自CSDN博客,转载请标明出处: ...

    在开发时,我们经常会遇到以“累计(count)”或是“累加(sum)”为条件的查询。比如aaa表:
    ID,NAME,SHU
    1,   a,        3
    2,   a,       4
    3,   b,        5
    4,   b,       7
    5, a,
    6, a,        20

     

      例1:查询出现过2次的name。
      往往初学者会错误地认为在where 语句里直接使用count()算法,很显然这个想法是错误的,count()方法并不能被用在where子句中,为了解决问题,我们可以在group by子句后面使用HAVING来做条件限制。
      错误做法:select * from aaa where count(name)>=2 group by name;
      正确做法:select * from aaa group by name HAVING count(name)>=2 ;
      解释说明:HAVING 与 WHERE 类似,可用来决定选择哪些记录。HAVING 子句在SELECT语句中指定,显示哪些已用 GROUP BY 子句分组的记录。在GROUP BY组合了记录后, HAVING会显示 GROUP BY 子句分组的任何符合 HAVING 子句的记录。

      例2:查询单一用户的SHU总和大于10的用户。
      有前面的经验,把sum()方法写在HAVING子句中。
      正确做法:select * from aaa group by name HAVING sum(SHU)>10 ;

      注意:一个HAVING子句最多只能包含40个表达式,HAVING子句的表达式之间可以用AND和OR分割。
       
        例3:sum 与count 的区别
            select name, count(shu), sum(shu)
                from aaa
            group by name

           结果为:a      3      27
                         b       2     12
           可见:oracle 数据库中对空值的事不同的,count 函数认为没有此项,而sum函数不计算。
           结论: 求和用累加 sum ,求行的个数用累计 count


    本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/meyou123/archive/2009/12/15/5014546.aspx

    展开全文
  • 原文链接:https://page.om.qq.com/page/OO2PXCD6VZ6CVqLqjpU6uShg0以下是本人整理的ORACLE学习的一些基本的语法...-防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.2.CHECK (...

    0019bb66e8a487e1f2eac1bd645a2bb6.png

    原文链接:https://page.om.qq.com/page/OO2PXCD6VZ6CVqLqjpU6uShg0

    以下是本人整理的ORACLE学习的一些基本的语法知识,如有不对的地方,望大家批评指正,如有转载请注明出处:

    d4747e4051f67e9c30cb47739cbc753a.png

    一、ORACLE完整性约束:

    1.NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.

    2.CHECK (检查)--检查在约束中指定的条件是否得到了满足.

    3.UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.

    4.PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.

    5.POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.

    二、一些常用的关键字及用法规范

    1--设置每行显示多少字符 set linesize 300;

    2 设置每页显示多少条记录 set pagesize 30;

    3 用户名的切换: 如 conn system/tiger

    Conn sys/change_on_install as sysdba(注意超级用户 在后面加as sysdba)

    4 在超级用户下查找普通用户的表是查不到的 必须这样查找 如 select * from scott.emp(普通用户下的emp表)

    5 查看当前是那个用户身份登录: show user;

    6 查看有多少张表: select * from tab;(注意不同用户下的表是不同的)

    7查看表的结构: desc emp(emp为表名)

    8 取出重复的列(DISTINCT): 如 SELECT DISTINCT JOB EMP(去掉job的重复的值)

    9字符串的链接操作用: ||

    10 查询有奖金的员工: select* from emp where comm is not null;

    11 查询没有奖金的员工信息: select * from emp where comm is null;

    12 两个条件以上就得用and 如查询工资大雨1500和有奖金的员工 select * from emp where sal>1500 and comm is not null;

    13 表示两个条件有一个满足就可就用:or 如查询工资大于1500或者没有奖金的员工信息

    Select * from emp where sal>1500 or comm is not null;

    14取反可以用not 如 查询员工工资不大于1500和有奖金的员工信息 如:

    Select * from emp where not (sal>1500 or comm is not null);

    15 在什么什么之间用between----and----如查询工资在1500和3000之间的员工信息:

    Select * from emp where sal between 1500 and 3000;

    16 查询员工编号是2323, 4555, 2222的员工具体信息: 如

    Select * from emp where empno in(2323,4555,2222);

    17.l模糊查询 like 一般结合"%"和"_"使用其中%:表示可以匹配任意长度的内容,"_"表示匹配一个长度放入内容 如: 查询员工姓名中第二哥字母是M的员工信息:

    Select * from emp where ename LIKE '_M%';

    又如姓名中包含M的员工 Select * from emp where ename LIKE '%M%';

    18oracle中不等于有两种表示方式""和"!="

    19 排序用order by 其中asc 是升序排列 如果不写就默认按升序排列desc是按降序排列 排序语句放在sal语句的最后如: 按员工工资进行排序

    Select * from emp order by sal asc(升序)

    Selecct * from emp order by sal desc(降序)

    Select * from emp where deptno='10' order by sal desc,hiredate asc;(查询部门10的员工工资的升序排列如果工资相等就按员工的入职时间排序)

    20.group by 用于对查询的结果进行分组统计: 显示每个部门的平均工资和最高工资 如:

    Select avg(sal),max(sal) from emp group by deptno;

    Having 子句用于限制分组显示结果: 显示平均工资大于2000的的部门号和他的平均工资?

    如:select avg(sal), deptno from emp group by deptno having avg(sal)>2000;

    2. 单行函数:

    1 小写变大写: upper 如 select * from emp where ename=upper('smith');

    讲一个字符串变为小写字母表示 如: select lower('HELLO WORLD') FROM DUAL;

    将单词的首字母变大写 用 INITCAP 如: SELECT INITCAP('HELLO WORLD') FROM DUAL;

    2.字符串的操作

    Substr()截取字符串 length()字符串的长度 replace()替换字符串

    3数值函数

    四舍五入: round(); 截断小数位:trunc();

    三、一些基本语法

    1.使用命令行创建表

    使用命令行创建表的关键字是CREATE TABLE,其简单语法如下:

    CREATE TABLE table_name(

    column_name type[CONSTRAINT constraint_def DEFAULT default_exp]

    [,column_name type[CONSTRAINT constraint_def DEFAULT default_exp]...]

    )

    [ON COMMIT(DELETE|PRESERVE ROWS]

    [ORGANIZITION]

    [PARTITION BY...(...)]

    [TABLESPACE tablespace_name]

    [LOGGING|NOLOGGING]

    [COMPRESS|NOCOMPRESS];

    ·table_name:指定要分配给该表的名称。

    ·column_name:指定要分配给某个列的名称。

    ·type:指定某个列的类型。

    ·constraint_def:指定对某个列的约束。

    ·default_exp:指定一个表达式,用来为某个列赋予默认值。

    2.修改表的结构

    修改表的基本语法如下:

    ALTER TABLEtable_name

    ADD column_name|MODIFY column_name|DROP COLUMN column_name

    ·:表示当学习了如何创建用户后,从安全的角度考虑,需要为表指定其所属的用户。

    ·ADD:向表中添加列。

    ·MODIFY:修改表中已存在的列的信息。

    ·DROP COLUMN:删除表中的列,在删除表中的列时如果加上CASCADE CONSTERAINTS,表示把与该列有关的约束也一并删除。

    3.重命名表

    在创建表后,如果想要修改表的名称,可以对表进行重命名。

    如:将表Sys_User重命名为用户信息

    ALTER TABLE Sys_User

    RENAME TO 用户信息;

    4.删除表

    用户经常需要删除一些不需要的表,可使用删除表语句DROP TABLE来完成,语法如下:

    DROP TABLE table_name

    5.插入数据

    使用INSERT语句可向指定表中插入数据。INSERT语法的基本结构如下:

    INSERT INTO(column_name1,column_name2...,column_name n)

    VALUSE(values 1,values 2,...,values n)

    其中,column_name 1,column_name2,...,column_name n必须是指定表名中定义的列,

    而且必须和VALUES字句中的值values 1,values 2,...,values n一一对应,且数据类型相同。

    6.更新数据

    使用UPDATE命令修改表中的数据

    UPDATESET column_name 1=values 1,column_name 2=values 2,...,column_name n=values n

    WHERE search_conditions

    如果不带where子句,则表中的所有行都将被更新。

    7.删除数据

    使用DELETE命令删除表中的数据

    DELETE FROM table_name[WHERE search_conditions]

    如果省略了WHERE search_conditions子句,就表示删除数据表中全部的数据;如果加上了WHERE search_conditions子句就可以根据条件删除表中的数据。

    四、Oracle数据库入门基础知识

    1. 创建表空间

    create tablespace schooltbs datafile ‘D:oracledatasourceschooltbs.dbf’ size 10M autoextend on;

    2. 删除表空间

    drop tablespace schooltbs[including contents and datafiles];

    3. 查询表空间基本信息

    select *||tablespace_name from DBA_TABLESPACES;

    4. 创建用户

    create user lihua

    identified by lihua

    default tablespace schooltbs

    temporary tablespace temp;

    5. 更改用户

    alter user lihua

    identified by 123

    default tablespace users;

    6. 锁定用户

    alter user lihua account lock|unlock;

    7. 删除用户

    drop user lihua cascade;--删除用户模式

    8. oracle数据库中的角色

    connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource

    9. 授予连接服务器的角色

    grant connect to lihua;

    10.授予使用表空间的角色

    grant resource to lihua with grant option;--该用户也有授权的权限

    11.授予操作表的权限

    grant select,insert on user_tbl to scott;--当前用户

    grant delete,update on lihua.user_tbl to scott;--系统管理员

    12.修改表的结构(alter)

    Alter table 表名 add(列的名称,列的类型);

    五、SQL查询和SQL函数

    1.SQl支持的命令:

    数据定义语言(DDL):create,alter,drop

    数据操纵语言(DML):insert,delete,update,select

    数据控制语言(DCL):grant,revoke

    事务控制语言(TCL):commit,savepoint,rollback

    2.Oracle数据类型

    字符,数值,日期,RAW,LOB

    字符型

    char:1-2000字节的定长字符

    varchar2:1-4000字节的变长字符

    long:2GB的变长字符

    注意:一个表中最多可有一列为long型

    Long列不能定义唯一约束或主键约束

    long列上不能创建索引

    过程或存储过程不能接受long类型的参数。

    数值型

    number:最高精度38位

    日期时间型

    date:精确到ss

    timestamp:秒值精确到小数点后6位

    函数

    sysdate,systimestamp返回系统当前日期,时间和时区。

    更改时间的显示

    alter session set nls_date_language=’american’;

    alter session set nls_date_format=’yyyy-mm-dd’;

    Oracle中的伪列

    像一个表列,但没有存储在表中

    伪列可以查询,但不能插入、更新和修改它们的值

    常用的伪列:rowid和rownum

    rowid:表中行的存储地址,可唯一标示数据库中的某一行,可以使用该列快速定位表中的行。

    rownum:查询返回结果集中的行的序号,可以使用它来限制查询返回的行数。

    3.数据定义语言

    用于操作表的命令

    create table

    alter table

    truncate table

    drop table

    修改表的命令

    alter table stu_table rename to stu_tbl;--修改表名

    alter table stu_tbl rename column stu_sex to sex;--修改列名

    alter table stu_tbl add (stu_age number);--添加新列

    alter table stu_tbl drop(sex);--删除列

    alter table stu_tbl modify(stu_sex varchar2(2));--更改列的数据类型

    alter table stu_tbl add constraint pk_stu_tbl primary key(id);--添加约束

    4.数据操纵语言

    select,update,delete,insert

    利用现有的表创建表

    create table stu_tbl_log as select id,stu_name,stu_age from stu_tbl;--

    选择无重复的行

    select distinct stu_name from stu_tbl;--

    插入来自其他表中的记录

    insert into stu_tbl_log select id,stu_name,stu_age from stu_tbl;

    5.数据控制语言

    grant,revoke

    6.事务控制语言

    commit,savepoint,rollback

    7.SQL操作符

    算术操作符:L+-*/

    比较操作符:L=,!=,,>,=,

    逻辑操作符:Land,or,not

    集合操作符:Lunion,union all,intersect,minus

    连接操作符:L||

    8.SQL函数

    单行函数:从表中查询的每一行只返回一个值,可出现在select子句,where子句中

    日期函数

    数字函数

    字符函数

    转换函数:ToChar(),ToDate(),ToNumber()

    其他函数:

    Nvl(exp1,exp2):表达式一为null时,返回表达式二

    Nvl2(exp1,exp2,exp3):表达式一为null时返回表达式三,否则返回表达式二

    Nullif(exp1,exp2):两表达式相等时,返回null,否则返回表达式一

    分组函数:基于一组行来返回

    Avg,Min,Max,Sum,Count

    Group by,having

    分析函数

    Row_number,rank,dense_rank

    示例:

    select u.user_name,sum(oi.order_num*oi.order_price) as total,row_number() over (order by sum(oi.order_num*oi.order_price) desc) as sort from order_item_tbl

    oi,user_tbl u,order_tbl o where oi.order_id = o.id and o.user_id = u.id group by u.user_name;

    六.锁和数据库对象

    1.锁:数据库用来控制共享资源并发访问的机制。

    锁的类型:行级锁,表级锁

    行级锁:对正在被修改的行进行锁定。行级锁也被称之为排他锁。

    在使用下列语句时,Oracle会自动应用行级锁:

    insert,update,delete,select…… for update

    select……for update允许用户一次锁定多条记录进行更新。

    使用commit or rollback释放锁。

    表级锁:

    lock table user_tbl in mode mode;

    表级锁类型:

    行共享 row share

    行排他 row exclusive

    共享 share

    共享行排他 share row exclusive

    排他 exclusive

    死锁:两个或两个以上的事务相互等待对方释放资源,从而形成死锁

    2.数据库对象

    oracle数据库对象又称模式对象

    数据库对象是逻辑结构的集合,最基本的数据库对象是表

    数据库对象:

    表,序列,视图,索引

    序列

    用于生成唯一,连续序号的对象。

    创建语法:

    create sequence user_id_seq

    start with 1000

    increment by 1

    maxvalue 2000

    minvalue 1000

    nocycle

    cache 1000;--指定内存中预先分配的序号

    访问序列:

    select user_id_seq.currval from dual;

    select user_id-seq.nextval from dual;

    更改删除序列:

    alter sequence user_id_seq maxvalue 10000;--不能修改其start with 值

    drop sequence user_id_seq;

    在Hibernate中访问序列:

    user_id_seq

    视图

    以经过定制的方式显示来自一个或多个表的数据

    创建视图:

    create or replace view

    user_tbl_view (vid,vname,vage)

    as select id,user_name,age from user_tbl

    [with check option]|[with read only];

    创建带有错误的视图:

    create force view user_tbl_force_view as

    select * from user_table;--此时user_table可以不存在

    创建外联接视图:

    create view user_stu_view as

    select u.id,u.user_name,u.password,s.ddress

    from user_tbl u,stu_tbl s

    where u.s_id(+)=s.id;--哪一方带有(+),哪一方就是次要的

    删除视图:

    drop user_stu_view;

    索引

    用于提高SQL语句执行的性能

    索引类型:

    唯一索引,位图索引,组合索引,基于函数的索引,反向键索引

    创建标准索引:

    create index user_id_index on user_tbl(id) tablespace schooltbs;

    重建索引:

    alter index user_id_index rebuild;

    删除索引:

    drop index user_id_index;

    创建唯一索引:

    create unique index user_id_index on user_tbl(id);

    创建组合索引:

    create index name_pass_index on user_tbl(user_name,password);

    创建反向键索引:

    create index user_id_index on user_tbl(id) reverse;

    七.使用PL/SQL

    22ccb96d91a3b11f0e91de75bfed17aa.png

    可用于创建存储过程,触发器,程序包,给SQL语句的执行添加程序逻辑。

    支持SQL,在PL/SQL中可以使用:

    数据操纵命令

    事务控制命令

    游标控制

    SQL函数和SQL运算符

    支持面向对象编程(OOP)

    可移植性

    更佳的性能,PL/SQL经过编译执行

    分为三个部分:声明部分,可执行部分和异常处理部分

    [declare

    declarations]

    begin

    executable statements

    [exception

    handlers]

    end;

    打开输出

    set serverout on;

    --根据输入编号获取某学员的成绩--if

    declare

    score user_tbl.score%type;

    begin

    select score into score from user_tbl where id='&id';

    if score>90 then

    dbms_output.put_line('优秀');

    elsif score>80 then

    dbms_output.put_line('良好');

    elsif score>60 then

    dbms_output.put_line('及格');

    else

    dbms_output.put_line('差');

    end if;

    end;

    --根据学员姓名获取某学员的成绩--if

    declare

    score user_tbl.score%type;

    begin

    select score into score from user_tbl where user_name='&name';

    if score>90 then

    dbms_output.put_line('优秀');

    elsif score>80 then

    dbms_output.put_line('良好');

    elsif score>60 then

    dbms_output.put_line('及格');

    else

    dbms_output.put_line('差');

    end if;

    end;

    --case的使用

    declare

    grade user_tbl.grade%type;

    begin

    select grade into grade from user_tbl where id='&id';

    case grade

    when 'A' then dbms_output.put_line('优异');

    when 'B' then dbms_output.put_line('优秀');

    when 'C' then dbms_output.put_line('良好');

    else dbms_output.put_line('一般');

    end case;

    end;

    --基本循环

    declare

    i number(4):=1;

    begin

    loop

    dbms_output.put_line('loop size:'||i);

    i:=i+1;

    exit when i>10;

    end loop;

    end;

    --while循环

    declare

    i number(4):=1;

    begin

    while i

    dbms_output.put_line('while loop size='||i);

    i:=i+1;

    end loop;

    end;

    --for循环

    declare

    i number(4):=1;

    begin

    for i in 1..10 loop

    dbms_output.put_line('for loop Size:'||i);

    end loop;

    end;

    declare

    i number(2):=1;

    j number(2):=1;

    begin

    for i in reverse 1..9 loop

    for j in 1..i loop

    dbms_output.put(j||'x'||i||'='||j*i||' ');

    end loop;

    dbms_output.put_line('');

    end loop;

    end;

    --动态SQL

    declare

    userId number(2);

    sql_str varchar2(100);

    userName user_tbl.user_name%type;

    begin

    execute immediate 'create table testExe(id number,test_name varchar2(20))';

    userId:='&userId';

    sql_str:='select user_name from user_tbl where id=:id';

    execute immediate sql_str into userName using userId;

    dbms_output.put_line(userName);

    end;

    (or

    declare

    id_param number:='&id_param';

    sql_str varchar2(100);

    name_param stu_tbl.stu_name%type;

    begin

    sql_str:='select stu_name from stu_tbl where id=:p';

    execute immediate sql_str into name_param using id_param;

    dbms_output.put_line(name_param);

    end;

    /)

    --异常处理

    declare

    grade number(4);

    begin

    grade:='&grade';

    case grade

    when 1 then dbms_output.put_line('好的');

    --else dbms_output.put_line('不好');

    end case;

    exception

    when case_not_found then

    dbms_output.put_line('输入类型不匹配!');

    end;

    --系统异常

    declare

    rowD user_tbl%rowtype;

    begin

    select * into rowD from user_tbl;

    dbms_output.put_line(rowD.id||''||rowD.user_name||' '||rowD.password);

    exception

    when too_many_rows then

    dbms_output.put_line('不能将多行赋予一个属性!');

    end;

    or

    declare

    rowD user_tbl%rowtype;

    begin

    select * into rowD from user_tbl where id=5;

    dbms_output.put_line(rowD.id||' '||rowD.user_name||' '||rowD.password);

    exception

    when too_many_rows then

    dbms_output.put_line('不能将多行赋予一个属性!');

    when no_data_found then

    dbms_output.put_line('没有您要查找的数据!');

    end;

    --自定义错误

    declare

    invalidError exception;

    category varchar2(20);

    begin

    category:='&category';

    if category not in('附件','顶盘','备件') then

    raise invalidError;

    else

    dbms_output.put_line('您输入的类别是:'||category);

    end if;

    exception

    when invalidError then

    dbms_output.put_line('无法识别的类别!');

    end;

    --引发应用程序异常

    declare

    app_exception exception;

    grade user_tbl.grade%type;

    begin

    select grade into grade from user_tbl where id=&id;

    if grade='A' then

    raise app_exception;

    else

    dbms_output.put_line('查询的等级为:'||grade);

    end if;

    exception

    when app_exception then

    raise_application_error(-20001,'未知的等级!');

    end;

    八、游标管理

    游标类型:隐式游标,显式游标,REF游标

    REF游标用于处理运行时才能确定的动态SQL查询的结果

    隐式游标

    在PL/SQL中使用DML语句时自动创建隐式游标

    隐式游自动声明、打开和关闭,其名为SQL

    隐式游标的属性:

    %found SQL语句影响实质后返回true

    %notfound SQL语句没有影响实质后返回true

    %rowcount SQL语句影响的行数

    %isopen 游标是否打开,始终为false

    示例:

    begin

    update user_tbl set score=score+5;

    if SQL%found then

    dbms_output.put_line('数据被更改: '||SQL%rowcount);

    elsif sql%notfound then

    dbms_output.put_line('没有找到数据!');

    end if;

    if SQL%isopen then

    dbms_output.put_line('Open');

    else

    dbms_output.put_line('Close');

    end if;

    end;

    显式游标

    在PL/SQL的声明部分定义查询,该查询可以返回多行

    J 声明游标

    J 打开游标

    J 从游标中取回数据

    J 关闭游标

    声明游标完成两个任务:

    给游标命名

    将一个查询与游标关联

    cursor cursor_name is select statement;

    打开游标:

    open cursor_name;

    取数据:

    fetch cursor_name into record_list;

    关闭游标:

    close cursor_name;

    显式游标的属性:

    %found 执行最后一条fetch语句成功返回行时为true

    %notfound 执行最后一条fetch语句未能返回行时为true

    %rowcount 返回到目前为止游标提取的行数

    %isopen 游标是否打开

    示例:

    declare

    users user_tbl%rowtype;

    cursor boys_cur is select * from user_tbl where sex='h';

    begin

    open boys_cur;

    loop

    fetch boys_cur into users;

    exit when boys_cur%notfound;

    dbms_output.put_line(users.user_name||' '||users.password);

    dbms_output.put_line(boys_cur%rowcount);

    end loop;

    close boys_cur;

    end;

    带参的显式游标

    declare

    users user_tbl%rowtype;

    cursor boys_cur(sexParam varchar2)

    is select * from user_tbl where sex=sexParam;

    begin

    open boys_cur('&sex');

    loop

    fetch boys_cur into users;

    exit when boys_cur%notfound;

    dbms_output.put_line(users.user_name||' '||users.password);

    dbms_output.put_line(boys_cur%rowcount);

    end loop;

    close boys_cur;

    end;

    使用显式游标更新行

    declare

    cursor user_update_cur is select sex from user_tbl for update;

    usersex user_tbl.sex%type;

    begin

    open user_update_cur;

    loop

    fetch user_update_cur into usersex;

    exit when user_update_cur%notfound;

    dbms_output.put_line(usersex);

    if usersex = 'M' then

    update user_tbl set score=score-5 where current of user_update_cur;

    else

    update user_tbl set score=score+5 where current of user_update_cur;

    end if;

    end loop;

    close user_update_cur;

    commit;

    end;

    循环游标

    declare

    cursor user_cur is select * from user_tbl;

    begin

    for username in user_cur loop

    dbms_output.put_line(username.user_name||' '||username.sex);

    end loop;

    end;

    REF游标

    REF游标和游标变量用于处理运行时动态执行的SQL查询

    创建游标变量的步骤:

    J 声明REF游标类型

    J 声明REF游标类型的变量

    声明类型的语法

    Type ref_cursor_name is ref cursor [return return_type];

    打开游标变量的语法

    Open cursor_name for select_statement;

    ----声明强类型的游标

    declare

    type ref_cur is ref cursor return user_tbl%rowtype;

    users_cur ref_cur;

    ----声明弱类型的游标

    declare

    type ref_cur is ref cursor;

    users_cur ref_cur;

    示例

    ----强类型

    declare

    type ref_cur is ref cursor return user_tbl%rowtype;

    users_cur ref_cur;

    users user_tbl%rowtype;

    begin

    open users_cur for select * from user_tbl where user_name='ny2t92';

    loop

    fetch users_cur into users;

    exit when users_cur%notfound;

    dbms_output.put_line(users.user_Name);

    end loop;

    close users_cur;

    end;

    ----弱类型

    declare

    type ref_cur is ref cursor;

    my_cur ref_cur;

    users user_tbl%rowtype;

    stus stu_tbl%rowtype;

    begin

    open my_cur for select * from user_tbl;

    loop

    fetch my_cur into users;

    exit when my_cur%notfound;

    dbms_output.put_line(users.user_Name);

    end loop;

    close my_cur;

    open my_cur for select * from user_tbl where user_name='ny2t92';

    loop

    fetch my_cur into users;

    exit when my_cur%notfound;

    dbms_output.put_line(users.user_Name);

    end loop;

    close my_cur;

    open my_cur for select * from stu_tbl;

    loop

    fetch my_cur into stus;

    exit when my_cur%notfound;

    dbms_output.put_line(stus.stu_Name);

    end loop;

    close my_cur;

    end;

    ----动态SQL游标

    declare

    type ref_cur is ref cursor;

    my_cur ref_cur;

    users user_tbl%rowtype;

    username varchar2(20);

    sqlstmt varchar2(200);

    begin

    username:='&username';

    sqlstmt := 'select * from user_tbl where user_name= :name';

    open my_cur for sqlstmt using username;

    loop

    fetch my_cur into users;

    exit when my_cur%notfound;

    dbms_output.put_line(users.user_Name);

    end loop;

    close my_cur;

    end;

    九.子程序

    子程序分为:存储过程和函数,它是命名的PL/SQL块,编译并存储在数据库中。

    子程序的各个部分:声明部分,可执行部分,异常处理部分。

    过程----执行某些操作

    函数----执行操作并返回值

    存储过程(存储过程是一组为了完成特定功能的SQL语句,经编译后存储在数据库中。)

    创建过程的语法:

    create or replace procedure

    proce_name (parameter_list)

    is|as

    local variable declaration

    begin

    executable statements

    exception

    exception_handlers

    end proce_name;

    过程参数的三种模式:

    In----用于接收调用的值,默认的参数模式

    Out----用于向调用程序返回值

    In out----用于接收调用程序的值,并向调用程序返回更新的值

    执行过程的语法:

    Execute proce_name(parameter_list);

    Declare

    Variable var_list;

    Begin

    Proce_name(var_list);

    End;

    将过程执行的权限授予其他用户:

    Grant execute on proce_name to scott;

    Grant execute on proce_name to public;

    删除存储过程:

    Drop procedure proce_name;

    函数

    创建函数的语法:

    Create or replace function

    Fun_name (parameter_list)

    Return datatype is|as

    Local declarations

    Begin

    Executable statements;

    Return result;

    Exception

    Exce_handlers;

    End;

    函数只能接收in参数,不能接受out或in out参数,形参不能是PL/SQL类型

    函数的返回类型也必须是数据库类型

    访问函数的方式:

    J 使用PL/SQL块

    J 使用SQL语句

    Select fun_name(parameter_list) from dual;

    8a5f7207846d90af71026f1ce84d1328.gif

    希望大家每天都进步一点点!

    原文链接:

    企鹅号​page.om.qq.com
    5b77a04847ddaded637255184b9ed69f.png
    展开全文
  • oracle 下实现累计求和

    千次阅读 2017-10-19 16:24:21
    row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每内部排序后的顺序编号(组内连续的唯一的).    与rownum的区别在于:使用...
  • 表tab通过select条件查询,查出来288条记录,现在要对这些数据按照列cola(number型)按照12条记录一进行分页,分24页,并对列colb(number型)数据进行求和,然后按照分页得顺利输出结果,请问大神,这个sql该...
  • 有没有遇到过表中某字段是状态字段或类型字段,然后需要统计各种状态或类型的记录数,这个时候一般是怎样实现呢?假设表A,有1个字段是状态字段,状态值有1,2,3如果没有查询条件可以这样,如果查询条件很复杂时,这个语句就...
  • Oracle 分组求和(特殊处理)

    千次阅读 2018-03-26 14:58:43
    对于编程人员的我们来说,写sql应该是不可避免的,要...无论是取最大值或求和等操作,我们一般都会用到这些语句,既然是如此常用的语句,那我们就一起来看看它的一些用法吧。 知识无极限,与你来相见,欢迎点赞支持!
  • sum() over (order by ) sum(x) over( partition by y ORDER BY z ) 分析...求安照y分区,然后按z排序,连续加当前顺序号前面的数值 (求每个分区中,按照z的顺序累计求和) a b 1 2 3 4 5 6 sum(b) over (order...
  • Oracle查询所有字段,再加两个字段拼接, select a.*,(SNO||SNAME) from TEST_STUDENT a; 同理,查询所有字段,其中两个字段求和:(SNO和SAGE都是NUMBER数据类型) select a.*,(SNO+SAGE) from TEST_STUDENT...
  • Oracle组函数

    2019-11-23 17:25:04
    函数: 定义:函数(分组函数),是指将数据按照某列的值进行分组后,然后使用函数分别对每个分好的小组中的数据进行处理。 所以函数一般要结合着分组关键字group来使用 group by:在查询表中数据的时候进行...
  • 公司项目有一个需求,就是有一张故障表,要查询车道故障原因的的故障数,以及一个车道,在该车道的该故障类型的百分比, 说通俗点,假设一个公司有10个部门,求每个部门每个月发工资的总和,同时计算每个部门每个月...
  • COUNT(*|列名) 统计行数AVG(数值类型列名) 平均值SUM(数值类型列名) 求和MAX(列名) 最大值MIN(列名) 最小值函数都会忽略null值select count(*) from emp; 二、分组查询-- group by 进行分组-- 例,按部门进行分组...
  • 前100行时,会发生如下操作 1 Oracle 执行查询 2 Oracle 获取第 1个合条件的行,称为第1行 3 判断结果是否有 100 行了吗,如果没有,那么,Oracle 就再返回行,因为要满足行号小于先于100的条件,如果到了100行,...
  • oracle组函数

    2021-06-09 09:15:34
    而这个函数就属于一种统计函数,那么对于统计函数常见的有五个:count()(个数)、sum()(求和)、avg()(平均)、max()(最大值)、min()(最小值)。 范例:统计所有雇员的人数,支付的总工资、平均工资、最高工资...
  • 第六章 oracle组合函数

    2019-07-22 20:14:51
    –sum ,求和函数,对数据进行求和 –avg ,求平均数,对一数据求平均值 –min ,求最小值函数 –max ,求最大值函数 –分组函数也叫统计函数,一般用于做数据统计使用 —查询部10号部门最高工资,最低工资,...
  • oracle组查询

    2016-09-10 17:26:00
    所谓查询即将数据按照某列或者某些列相同的值进行分组,然后对该的数据进行函数运用,针对每一返回一个结果。 note: 1.函数可以出现的位置: select子句和having 子句 2.使用group by 将将行划分成若干...
  • 函数: 函数操作行集,给出每的结果。函数不象单行函数,函数对行的集合进行操作,对每给出一个结果。这些集合可能是整个表或者是表分成的函数与单行函数区别: 单行函数对查询到每个结果集...
  • ORACLE物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。通常情况下,物化视图被称为主表(在复制期间)或...
  • oracle组函数(聚组函数)总结

    千次阅读 2017-06-16 16:12:48
    知识点一:什么是函数?函数:是对数据行的集合进行操作并按给出一个结果知识点二:常用的函数有哪些?AVG ---求平均值 COUNT ---统计查询所得行数量(查询数据总数) MAX ---求最大值 MIN ---求最小值 ...
  • 根据c1,c2分组,并且根据c3排序,取第一行 select tt.* from (select row_number() over(partition by c1, c2 order by c3 desc) r, t.* from test_table t) tt where tt.r = 1 ...
  • 一. 函数|多行函数|聚合函数 即多条记录 返回一个结果。我们需要掌握如下几个函数: ...3)、sum:求和 4)、avg:平均值注意: 1、函数仅在选择列表和Having子句中有效 2、出现函数,select 只能有函数
  • 一、求和与求平均1、什么是函数函数就是我们前面我说过的多行函数。它主要针对表进行操作。因为只有表中,才有多行数据。下面我们以SUM和AVG这两个函数为例,先从它们开始,介绍函数的特性。2、SUM 求和函数...
  • group by 语句支持基本的having条件,还支持rollup和cube提供信息汇总功能,类似小计。  rollup:纵向小计,从右向左逐个对每一列进行小结并在结果中生成独立的一行。只返回第一个分组条件指定的列的统计行。...
  • oracle数据库之函数

    2018-08-08 08:37:00
    函数也叫聚合函数,用来对一值进行运算,并且可以返回...(3)sum(列名) 求和 (4)max(列名) 求最大值 (5)min(列名) 求最小值 scott是oracle中的一个示范用户,主要用于测试。它自带一些测试用的数据方...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,635
精华内容 3,854
关键字:

oracle组内求和