精华内容
下载资源
问答
  • 原文链接: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强大数据库备份恢复机制的一部分,在数据库发生逻辑错误的时候,闪回技术能提供快速且最小损失的恢复(多数闪回功能都能在数据库联机状态下完成)。需要注意的是,闪回技术旨在快速恢复逻辑错误,...

    概述

    闪回技术是Oracle强大数据库备份恢复机制的一部分,在数据库发生逻辑错误的时候,闪回技术能提供快速且最小损失的恢复(多数闪回功能都能在数据库联机状态下完成)。需要注意的是,闪回技术旨在快速恢复逻辑错误,对于物理损坏或是介质丢失的错误,闪回技术就回天乏术了,还是得借助于Oracle一些高级的备份恢复工具如RAMN去完成。

    撤销段(UNDO SEGMENT)

    在讲闪回技术前,需要先了解Oracle中一个逻辑结构--撤销段。因为大部分闪回技术都需要依赖撤销段中的撤销数据。撤销数据是反转DML语句结果所需的信息,只要某个事务修改了数据,那么更新前的原有数据就会被写入一个撤销段。(事务回滚也会用到撤销段中的数据)。事务启动时,Oracle 会为其分配一个撤销段,事务和撤销段存在多对一的关系,即一个事务只能对应一个撤销段,多个事务可以共享一个撤销段(不过在数据库正常运行时一般不会发生这种情况)。

    闪回技术

    Oracle提供了四种可供使用的闪回技术(闪回查询,闪回删除,闪回归档,闪回数据库),每种都有不同的底层体系结构支撑,但其实这四种不同的闪回技术部分功能是有重叠的,使用时也需要根据实际场景合理选择最合适的闪回功能。

    闪回查询(Flashback Query) 

    a.基本闪回查询

    功能描述:可以查询过去某个时间段的数据库状态。

    工作原理:Oracle 会提取所需要的撤销数据(前提是撤销是可用的,即撤销数据还没被覆盖)进行回滚,但这种回滚是临时的,仅针对当前session可见。

    SQL> select * from  dept as of timestamp to_timestamp('2016-09-10 11:00:00','yyyy-mm-dd hh24:mi:ss');

    b.闪回表

    功能描述:可将某个表回退到过去某个时间点

    工作原理:同样,Oracle会先去查询撤销段,提取过去某个时间点之后的所有变更,构造反转这些变更的SQL语句进行回退,闪回操作是一个单独的事务,所以若由于撤销数据过期之类的原因导致无法闪回,整个操作会回滚,不会存在不一致的状态。

    步骤:

    1.启用表闪回首先要在表上支持行移动(在数据字典中设置标识来标识该操作可能会改变行ID,即同一条数据闪回成功后主键都一样,但行ID其实已经发生变化了)

     SQL> alter table emp enable row movement;

    2.闪回表操作

    SQL> flashback table dept to timestamp to_timestamp('2016-09-10 11:00:00','yyyy-mm-dd hh24:mi:ss');

    闪回表可能会失败,有可能有以下几种情况:

    a.违反了数据库约束,比如用户不小心删除了子表中的数据,现在想利用闪回表技术进行回退,恰好在这中间,父表中与该数据对应的那条记录也被删除了,在这种情况下,由于违反了外键约束,导致闪回表操作失败了;

    b.撤销数据失效,比如用于支撑闪回操作的撤销数据被覆盖了,这种情况闪回表操作自然会失败;

    c.闪回不能跨越DDL,即在闪回点和当前点之间,表结构有过变更,这种情况闪回操作也会失败。

    注意:上述闪回功能都是基于撤销数据的,而撤销数据是会被重写的(Expired会被重写,Active不会被重写),所以,在需要使用这几种闪回功能去恢复数据的时候(确切地说,是需要使用基于撤销数据的闪回功能时),最短时间发现错误,第一时间执行闪回操作,才能最大程度地保证闪回功能的成功。

    闪回删除(Flashback Drop) 

    功能描述:闪回删除可以轻松将一个已经被Drop的表还原回来。相应的索引,数据库约束也会被还原(除了外键约束)

    原理描述:Drop命令其实是Rename命令,早期的Oracle版本(10g之前),闪回删除意味着从数据字典中删除了该表的所有引用,虽然表中数据可能还存在,但已成了孤魂野鬼,没法进行恢复了,10g版本之后,Drop命令则仅仅是一个Rename操作,所以恢复就很容易了。

    闪回删除操作执行命令很简单

    SQL> flashback table emp to before

    如果要还原的表名在当前系统中已经被占用,也可以在闪回删除的时候对表重命名

    SQL> flashback table emp to before drop rename to emp_new

    也可以通过回收站查看当前用户那些表被删除了,每个用户都有一个回收站,这个回收站是个逻辑结构,它不是一块独立的存储空间,它存在在当前表空间内,所以如果有别的操作需要空间,比如现在需要创建一张表,没有足够空间可用,回收站中的数据就会被清理,这也是导致闪回删除失败的原因。

    SQL> SHOW RECYCLEBIN;

    彻底删除表,闪回删除也无能为力

    SQL> DROP TABLE EMP PURGE;

    清空回收站

    SQL> PURGE RECYCLEBIN;

    注意:闪回删除只针对Drop命令,注意区分truncate操作和drop操作,truncate称为表截断,会清空表中数据(调节Oracle高水位线实现),表结构不受影响,速度很快,弊端是此过程不会产生任何撤销数据或是重做日志,如果误删,恢复异常麻烦,要慎重使用。而Drop则会删除数据+表结构,闪回删除仅针对Drop操作。

    闪回数据归档(Flashback Data Archive )

    功能描述:闪回数据归档可使表具有回退到过去任何时间点的能力,前面提到的闪回查询,闪回表都会受限于撤销数据是否失效,如果撤销数据被覆盖重写了,闪回操作自然会失败,闪回删除则受限于表空间是否有足够可用空间,而闪回数据归档,则没有这些限制。

    创建闪回归档

    1.创建一个用户闪回数据归档的表空间,当然,也可以使用已经存在的表空间。

    SQL> create tablespace test_tb datafile 'test.dbf' size 20m;

    2.创建一个保留时间为2年的闪回归档

    SQL> create flashback archive test_fa tablespace test_tb retention 2 year;

    为scott用户下的emp表启用闪回归档

    1.赋予用户归档的权限

    SQL> grant flashback archive on test_fa to scott;

    2.连接用户

    SQL> conn scott/tiger;

    3.为emp表启用闪回归档

    SQL> alter table emp flashback archive test_fa;

    至此,emp表就拥有了可以查询或回退到过去2年任意时间点的能力!

    闪回数据库(Flashback Database) 

    功能描述:闪回数据库可将整个数据库回退到过去某个时间点,闪回表是某张表的时空穿梭,闪回数据库则是整个数据库的时空穿梭。当然,闪回点之后的所有工作就丢失了,其实就相当于数据库的不完整恢复,所以只能以resetlogs模式打开数据库。闪回数据库会造成停机时间,当然相比于传统备份恢复机制,恢复过程会快很多。

    工作原理:闪回数据库不使用撤销数据,使用另外一种机制来保留回退所需要的恢复数据,当启用闪回数据库,发生变化的数据块会不断从数据库缓冲区缓存中复制到闪回缓冲区,然后,称为恢复写入器(Recovery Writer)的后台进程会将这些数据刷新到磁盘中的闪回日志文件中。闪回的过程,则是一个 提取闪回日志-->将块映像复制回数据文件 的过程。

    配置闪回数据库(闪回数据库要求数据库为归档模式)

    1.指定闪回恢复区,也就是存放闪回日志的位置,但闪回恢复区不仅仅是为了存放闪回日志,Oracle的很多备份恢复技术都用到这个区域,比如控制文件的自动备份等都会存放到此区域。

    SQL> alter system set db_recovery_file_dest ='/flash_recovery_area';

    2.指定恢复区大小

    SQL> alter system set db_recovery_file_dest_size=4G;

    3.指定闪回日志保留时间为2小时,即通过闪回操作,可以将数据库回退到前两小时内的任意时间点

    SQL> alter system set db_flashback_retention_target=120;

    4.有序关闭数据库--mount模式下启用闪回数据库--打开数据库

    SQL> shutdown immediate;

    SQL> startup mount;

    SQL> alter database flashback on;

    SQL> alter database open;

    至此,闪回数据库配置完成!

    使用闪回数据库功能

    SQL> shutdown immediate;

    SQL> startup mount;

    SQL> flashback database to timestamp sysdate-60/1440;

    SQL> alter database open resetlogs;

    总结

    本文列举了四类闪回技术,其中,闪回查询,包括基本闪回查询,闪回表等技术都依赖于撤销数据(还有一类闪回技术为闪回事务,可以对指定事务进行闪回操作,原理类似,借助于撤销数据来构建用于反转事务的SQL语句),依赖于撤销数据,则自然受限于撤销数据的保留时间,可能会由于撤销数据被覆写而导致闪回失败。闪回删除,则是由于10g版本后对表的删除仅表现为一个rename操作,引入回收站的概念,但此回收站仅是当前表空间的一块逻辑划分,所以会受限于当前表空间的可用空间的限制;闪回归档可提供查询或回退到过去任意时间点的功能,闪回数据库则是一中更极端的数据库恢复功能,相当于不完整恢复,依赖于闪回日志。 

    来源:https://www.cnblogs.com/chengxiao

    长按二维码关注公众号

    9012fbbc8d2d35c50050d1027e63cde7.png

    展开全文
  • 主要介绍了oracle横向纵向求和的相关内容,涉及两个实例,具有一定参考价值。需要的朋友可以了解。
  • 现在有A、B两个表,两个表通过up_location和DIS_location两个字段进行连接匹配。要计算price*CA*A+price*CB*B.....+price*CZ*Z的每一行的和。这里面要注意日期,dep_location如果不在st_date和end_date范围内的行...
  • 在开发时,我们经常会遇到以... 可见: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

    展开全文
  • oracle 下实现累计求和

    千次阅读 2017-10-19 16:24:21
    rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).  dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 . lag(arg1,arg2,arg3):  ...
    row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). 
     
    与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
     
    row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序). 
    rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内). 
    dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .
    lag(arg1,arg2,arg3): 
     
     arg1是从其他行返回的表达式
    arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。
    arg3是在arg2表示的数目超出了分组的范围时返回的值。

    在oracle开发过程中经常会遇到累加、减、乘、除的问题。
    下面对这个做个小的总结


    --创建测试表
    create table aspen(parent_id number,part_id number,qnty number);
    insert into aspen values(1,1,2);
    insert into aspen values(1,2,3);
    insert into aspen values(1,3,2);
    insert into aspen values(1,4,5);
    insert into aspen values(2,2,3);
    insert into aspen values(2,3,5);
    insert into aspen values(2,4,7);


    --实现累加
    select parent_id,part_id,qnty,
    sum(qnty) over(partition by parent_id order by part_id) running_prod
    from aspen

     

     PARENT_ID    PART_ID       QNTY RUNNING_PROD
    ---------- ---------- ---------- ------------
             1          1          2            2
             1          2          3            5
             1          3          2            7
             1          4          5           12
             2          2          3            3
             2          3          5            8
             2          4          7           15


    --实现累减
    select parent_id,part_id,qnty,
     sum(decode(rn,1,qnty,-qnty)) over(partition by parent_id order by part_id) running_prod
    from (select parent_id,part_id,qnty,
          row_number() over(partition by parent_id order by part_id) rn
          from aspen)


     PARENT_ID    PART_ID       QNTY RUNNING_PROD
    ---------- ---------- ---------- ------------
             1          1          2            2
             1          2          3           -1
             1          3          2           -3
             1          4          5           -8
             2          2          3            3
             2          3          5           -2
             2          4          7           -9

    --实现累乘
    select parent_id,part_id,
    exp(sum(ln(qnty)) over(partition by parent_id order by part_id)) running_prod
    from aspen


    PARENT_ID    PART_ID RUNNING_PROD
    --------- ---------- ------------
            1          1            2
            1          2            6
            1          3           12
            1          4           60
            2          2            3
            2          3           15
            2          4          105

    --实现累除
    select parent_id,part_id,
    exp(sum(decode(rn,1,ln(qnty),-ln(qnty))) over(partition by parent_id order by part_id)) running_prod
    from (select parent_id,part_id,qnty,
          row_number() over(partition by parent_id order by part_id) rn
          from aspen )


     PARENT_ID    PART_ID RUNNING_PROD
    ---------- ---------- ------------
             1          1            2
             1          2   .666666667
             1          3   .333333333
             1          4   .066666667
             2          2            3
             2          3           .6
             2          4   .085714286

    row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 和sum(qnty) over(partition by parent_id order by part_id) 可实现累加,其余类似。



    展开全文
  • 有个经典案例我一直拿来和新导师们分享: 情况是这样的,女生突然间被自己相恋3年的前任分手,她非常伤心,也做了很多挽留,但是无论女生怎么问,他都坚持两个人没有感情了。 当然这一点我和学员都是不信的,因为...
  • oracle 按照日期区间求和

    千次阅读 2011-05-25 21:40:00
    //求两个日期之间的数据之和 //2011/03/16为起始日,2011/09/15为结束日 //没一个月为一个梯度,也就是3.16-4.15,4.16-5.15,... //数据: 日期 金额 2011/03/16 20 2011/03/17 30 2011/04/14 50 2011
  • Oracle查询所有字段,再加两个字段拼接, select a.*,(SNO||SNAME) from TEST_STUDENT a; 同理,查询所有字段,其中两个字段求和:(SNO和SAGE都是NUMBER数据类型) select a.*,(SNO+SAGE) from TEST_STUDENT...
  • oracle数据库对test_table表的三列count1,count2,count3求sum的种sql,做记录 第一种 select sum (case when count1 is not null then count1 when count2 is not null then count2 when count3 is not ...
  • 他看起来风起云淡得好似什么都没有发生,你不明白,曾经深爱过的人怎么分手两个字一说,就真的变得像陌生人一样了呢?男人真的转身就可以忘记过去的那些美好么?其实在一段感情中,男人想的永远都是这段感情对自己的...
  • ​【题目】“薪水表”中记录了员工发放的薪水。包含雇员编号,薪水、起始日期、结束日期...其中累计薪水是前N当前员工( 结束日期 = '9999-01-01')的薪水的累计和,其他以此类推。【解题步骤】1.先筛选出当前员工( ...
  • 2.oracle子查询中如果含有null,子查询:SELECT emp.deptno FROM empwhere not in (子查询)会返回空值应剔除子查询中的空值,SELECT emp.deptno FROM emp where deptno is not null3.聚合函数avg():分组之后,用sum...
  • 前100行时,会发生如下操作 1 Oracle 执行查询 2 Oracle 获取第 1合条件的行,称为第1行 3 判断结果是否有 100 行了吗,如果没有,那么,Oracle 就再返回行,因为要满足行号小于先于100的条件,如果到了100行,...
  • oracle表count值求和

    万次阅读 2018-01-10 14:14:32
    union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。 union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回...
  • oracle 分组求和小例子

    万次阅读 2015-04-09 14:19:06
    如果是不分组(即没有 Group By) 那是一定会返回一 0 的. 要让有 分组 的count返回 0 , 则需要使用外连接 参考这篇文章可以解决问题! --方式3 select * from ( select a.req_time, count (*...
  • oracle怎么计算两个常数的求和,如1+1,或者求两个表count(*)的和 用from dual;
  • 分三表及用到的字段 关联时用到的的字段 patient_id 病人id visits 就诊次数 1、病人住院信息表 patient_inhosp: 病人住院信息表 hosp_id 医院编号 inp_no 住院号 patient_id 病人...
  • //求两个日期之间的数据之和//2011/03/16为起始日,2011/09/15为结束日//没一个月为一个梯度,也就是3.16-4.15,4.16-5.15,...//数据:日期 金额2011/03/16 202011/03/17 302011/04/14 502011/04/15 502011/04/16 ...
  • oracle分组求和

    2013-11-28 14:58:00
    次count来算出个数 这样就可以解决了 如果需要使用sum来求和则修改一下就可以了 select sum(a.count) from (select count(*) as count from 表名 t where 1=1 and 其他条件 group by t.year) a ...
  • 表转置,求和,是很常用的方式 上代码: create table win ( rq varchar2(10), shengfu varchar2(2)); insert into win(rq,shengfu) values('2005-05-09','胜'); insert into win(rq,shengfu) values('2005-05-09',...
  • oracle有对应的max,min,last,first,可惜,这回是去掉两个,而不是去掉一个,或许人家国外就没有这样的评分方法,呵呵。写了几种方法,原来是用row_number(),感觉麻烦,相比之下,认为这种方式最简单,借用了...
  • 输出列: userid,amount,term,apr,numberOfRowsForEachUser 1 10 5 1 2 2 20 6 1 3 问题:我能够获得前四列,但不确定如何获得“总计没有优惠”或“每用户的总行”. 我的查询看起来像这样. select userid,apr ...
  • oracle 按照时间分组统计求和

    千次阅读 2017-08-31 18:39:50
    select t.year,   t....   sum(A)over (order by rownum rows BETWEEN unbounded preceding and current row)A, ...sum(B)over (order by rownum rows BETWEEN unbounded preceding and current row)B, ...
  • 刚开始找工作那段时间,每天要面试好几家单位,不停的重复着面试,机试。后来来到我现在这个公司,感觉...前两道题目很简单,关键是第三道题目,用webservice实现两个整数求和,看到这个题目我连webservice的基本概...
  • 最近写需求,第一次遇到对多sql count的值再进行求和,赶紧做笔记~ 对多sql count的值再求和: select count(1) countnum from user c where c.NAME = '张三' UNION ALL select count(1) countnum from ...
  • oracle 两个时间相减默认的是天数 ...oracle 两个时间相减默认的是天数*24 为相差的小时 oracle 两个时间相减默认的是天数*24*60 为相差的分钟 oracle 两个时间相减默认的是天数*24*60*60 为相差的秒
  • Oracle中纯数字的varchar2类型和number类型自动转换使用过一个关联查询,两个表的字段定义了不同的类型。一个字段是varchar2类型,另一个字段是number类型,内容如下:'00187'和187。在使用中发现会自动将varchar2...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,506
精华内容 4,602
关键字:

oracle两个数求和