• oracle 常用sql


    --###################################################################################
    /*
      版本号: Oracle9i或者Oracle9g---i表示internet, g表示grid
      iSqlplus (DBA) URL: http://localhost:5560/isqlplus(/dba)
      完全卸载oracle: 卸载完成后, 手工删除项 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
      服务: OracleService实例名---Oracle核心服务
            OracleOraDb10g_home1TNSListener---通过网络访问数据库
            OracleOraDb10g_home1iSql*Plus---通过浏览器访问
      端口号: Oracle---5560   Sql---1433,1434
      登陆: 在cmd中输入 sqlplus; 系统用户名: system 或者 sysdba, 密码:安装时设置
      强制登陆: 在cmd中输入 sqlplus / as sysdba
      默认帐户: scott 密码:tiger, 默认是locked; hr 密码:tiger
      
      Schema方法: 是与用户关联的表、视图、簇、过程和程序包等对象的命名的集合, 该方案schema在Oracle创建用户时自动创建.  
      Oracle数据库字典:
        USER_*:用户视图,即用户schema中的视图
        ALL_*:扩展用户视图,即用户可以访问的视图, 包含自己的用户视图和一个其他的可访问的视图
        DBA_*:所有方案的视图
      缩写:
        DDL(Database Definition Language): 数据库定义语言
        DML(Database Manipulation Language): 数据库操作语言
        DCL(Database Control Language): 数据库控制语言
        DTM(Database Trasaction Management): 数据库事物管理
        知识概要:
                |---1.查询Select
                |
                |---2.数据库定义语言DDL: 对表,视图等的操作, 包括create,drop,alter,rename,truncate
                |
     数据库操作--|---3.数据库操作语言DML: 对记录进行的操作, 包括insert,delete,update
                |
                |---2.数据库控制语言DCL: 对访问权限等的操作, 包括grant,revoke
                |
                |---2.数据库事物管理DTM: 对事物的操作, 包括commit,rollback,savepoint
        约束: 主键(Primary Key)、外键(Foreign Key)、非空(not null)、唯一(unique)和检查(check)
        事物的是由DML(insert,delete,update)开启的, 在Oracle中需要执行commit才能看到DML操作的结果;
        而引起事物的提交或结束原因有:
    .DTM操作: commit,rollback,savepoint
    .系统崩溃宕机: 隐式rollback
    .正常:    隐式commit
    .DDL和DCL操作: DDL(create,drop,alter,rename,truncate)
                        DCL(grant,revoke)
        备注:
        Oracle中, 字符串和日期区分大小写, 且需要用单引号进行表示. 若要输出单引号, 用''表示一个单引号
        Oracle中使用||拼接字符串, Sql中使用+拼接字符串
        C#数据类型:
            整数: sbyte,byte,short,ushort,int,uint,long,ulong
            实数: float,double,decimal
            字符: char,string
            布尔: boolean
            日期: datetime
            对象: object
        Oracle数据类型:
            整数: number(38)    --n表示位数
            字符: char(2000), nchar(1000), varchar2(4000),nvarcahr2(2000), long
            日期: date
        Sql注入测试串: ' or 1=1 --
        解决注入攻击的手段:
    . 不拼接字符串, 有字符串操作时, 要留心.
    . 使用参数进行赋值, 尽量使用强类型.
    . 对用户输入的字符进行过滤.
    . 若非用串的话(如:用户名登陆过程), 可以用代码先到数据库中查一下是否有这个名字, 没有则禁止操作
    */
    --###################################################################################
    --查询系统资源:
    --常用命令
    set linesize
    set pagesize
    clear screen
    show user
    show errors --显示错误, 如在存储过程中显示错误
    edit    ----可以使用edit(ed)编辑刚才输入的语句, 编辑结束后用"/"提交
    list    --列出sqlplus缓冲区中的命令, 简写为l
    begin
        dbms_output.put_line('hello, world!');  --Orcle中输出字符, 注意输出语句在begin和end之间, 且注意分号
    end;
    --表及表信息
    decribe user_tables
    select * from user_tables
    select table_name from all_tables
    select procedure_name from user_procedures  --查询存储过程
    select * from user_sequences     --查询序列
    select * from all_sequences
    select Seq_Test.nextval from dual   --查询下一个递增序列的值
    select Seq_Test.currval from dual   --查询当前序列的值
    --视图
    select * from user_views
    --查看约束名称、表名字、约束类型及字段名
    select table_name, constraint_name, constraint_type, search_condition from User_Constraints --查看表约束
    select table_name,constraint_name, column_name from user_cons_columns   --查看列约束
    select c.table_name,c.constraint_name,  u.constraint_type, c.column_name
    from user_cons_columns  c inner join user_constraints u
       on c.constraint_name = u.constraint_name
    grant unlimited tablespace to scott     --无法将表空间授予角色, 只能授予用户
    revoke unlimited tablespace from scott
    --用户及用户角色信息
    show user
    select * from all_users
    select username from user_users
    select username,default_tablespace from user_users  --查看缺省用户表空间
    select * from user_role_privs   --查看用户具有的角色
    --授权及权限
    select * from user_sys_privs    --授予用户的系统权限
    select * from role_sys_privs    --授予角色的系统权限
    select * from role_tab_privs    --授予角色的表的权限
    select * from dba_sys_privs where grantee = 'SYS'  --查看'SYS'的具体权限, 注意大小写
    select grantee,owner,table_name,privilege from dba_tab_privs    --查看所有用户的权限
    select name from sys.system_privilege_map   --查看Oracle提供的系统权限
    select * from user_tab_privs_made   --用户对象创建时的权限
    select * from user_tab_privs_recd   --用户对象接收的权限
    select * from user_col_privs_made   --用户对象创建时列上的权限
    select * from user_col_privs_recd   --用户对象列接收的权限
    --查看一个用户的所有系统权限(包含角色的系统权限)
    select privilege from dba_sys_privs where grantee='RESOURCE'
        union 
        select privilege from dba_sys_privs where grantee in (
            select granted_role from dba_role_privs where grantee='RESOURCE'
        )
    select * from dba_roles
    create user TestUser identitied by TestUser account unlock
    grant create session to TestUser
    create role TestUserRole
    grant TestUserRole to scott
    grant create session, create view, resource, connect to TestUserRole    --通常给的用户权限
    grant resource, connect to scott
    drop user TestUser
    revoke create seeion from hr
    drop role TestUserRole
    revoke resource,connect from TestUserRole
    alter user hr identitied by tiger account unlock
    --###################################################################################
    --创建表, 约束类型: 主键、外键、唯一、check约束、非空约束
    --        注意事项: 序列不保证连续、关键字不必要连续、业务数据不适合作为关键字
    drop table T_Event;
    drop sequence seq_T_Event;
    create sequence seq_T_Event start with 1 increment by 1;
    create table T_Event(
        seqid number(8),
        constraint pk_T_Event primary key(seqid),   --单独写约束, 可以方便添加联合主键(在seqid后便添加即可)
        title varchar2(200) constraint nn_T_Event_title not null,
        startdate date constraint nn_T_Event_startdate not null,
        enddate date constraint nn_T_Event_enddate not null,
        constraint ck_T_Event_enddate check(enddate > startdate),
        detail varchar2(500),
        userid number(8),
        constraint fk_T_Event_userid foreign key(userid) references T_UserInfo(seqid)
    )
    drop table T_UserInfo;
    drop sequence seq_T_UserInfo;
    create sequence seq_T_UserInfo start with 1 increment by 1;
    create table T_UserInfo
    (
        seqid number(8),
        constraint pk_T_UserInfo primary key(seqid),
        username nvarchar2(30) constraint nn_T_UserInfo_username not null,
        constraint uq_T_UserInfo_username unique(username),
        create_data date default sysdate
    )
    --修改表结构
    alter table T_Event
        add userid number(8)
    alter table T_Event
        drop column test
    --修改表约束
    alter table T_Event
        add constraint fk_T_Event_userid foreign key(userid) references T_UserInfo(seqid)
    --###################################################################################
    --创建视图
    create or replace view View_DepEmp
    as
    select last_name Given_Name,salary
    from employees  e inner join departments d on e.department_id = d.department_id
    --###################################################################################
    --新增(插入)数据(DML操作, 在commit之后才会提交)
    insert into T_Event(seqid,title,startdate,enddate,detail,userid) 
        values(seq_T_Event.nextval,'吃饭',to_date('2010-11-5','yyyy-mm-dd'),
               to_date('2010-12-1 8:30','yyyy-mm-dd hh-mi'),'有人请吃大餐, 不吃白不吃!','1');
    insert into T_Event(seqid,title,startdate,enddate,detail,userid) 
        values(seq_T_Event.nextval,'喝酒',to_date('11-5-2012','mm-dd-yyyy'),
               to_date('13-12-2012 12:45','dd-mm-yyyy hh-mi'),'有人请喝酒, 不喝白不喝!','1');
    commit
    insert into T_UserInfo(seqid,username)
        values(seq_T_UserInfo.nextval,'admin');
    insert into T_UserInfo(seqid,username)
        values(seq_T_UserInfo.nextval,'user');
    commit
        
    --删除数据
    truncate table T_Event    --只删除表的数据
    delete from T_Event where title = '喝酒'
    commit
    --修改数据
    update T_UserInfo set username = 'Client' where username='user';    --注意set后边仍为=
    commit
    --###################################################################################
    --查询数据
    select distinct department_id, job_id from employees    --distinct
    select department_id as "部门编号",  job_id "职位编号" from employees    --起别名的方式as和空格, 注意""
    select department_id || ' 联合 ' || job_id as "唯一标识" from employees --别名双引号, 连接字符串用单引号
    select first_name,last_name,salary from employees where salary >= 8000 and salary <= 10000
    select first_name,last_name,salary from employees where salary between 8000 and 10000
    select department_id, job_id from employees where department_id is null
    --模糊查询呢: 通配符: %(0-任意字符), _(1个字符),[a,b,c](选择含a或b或c),[^a,b,c](选择不含a或b或c)
    select first_name, last_name from employees where last_name like 'D%' or last_name like 'E%'
    select first_name, last_name from employees where last_name like('D%') or last_name like('E%')
    select first_name, last_name from employees where last_name between 'D' and 'F' --注意这里取左不取右
    select first_name, last_name from employees where substr(last_name,1,1) in('D','E')SYSTEM
    --嵌套查询(子查询): 分为单行子查询和多行子查询, 区别就是子查询的结果是单条记录还是结果集
    --1. dual是系统的虚表, 可以用不需要From的select语句中
    --2. rownum成为伪列, 用于生成序号.原理: 生成结果集后, 再加上序号
    select next_day(sysdate,3) from dual --返回下周2的日期, 周的计算从1开始
    --利用伪列进行分页: 将子查询作为虚拟表再查询
    --1. 输出前10条排序后的记录
    select rownum,department_name from employees where rownum <= 10 --未排序
    select rownum, department_name  --排序后的记录, 因为select基本最后执行, 所以select两次
    from (
         select rownum, department_name
         from departments
         order by department_name
    )
    where rownum <= 10
    --2. 输出前11-20条排序后的记录
    select rn,department_name
    from(
          select rownum as rn, department_name 
          from (
                  select rownum, department_name
                  from departments
                  order by department_name
           )
    )
    where rn between 11 and 20 --rownum根据结果集来生成, 一直没有rownum=1的记录, 所以得不到11的记录
    --单行子查询: =、>、<
    select last_name,salary
    from employees 
    where salary > (select avg(salary) from employees)  --子查询结果为单挑记录
    --多行子查询: in(在结果集里面即可)、any(符合任一个即可)、all(完全符合才可)
    select last_name,salary     --在结果集中
    from employees
    where salary in (select salary from employees where department_id = 110)    
    select last_name,salary     --大于所有的, 也就是大于最大的(替代mssql的top?)
    from employees
    where salary > all(select salary from employees where department_id = 110)  
    selet last_name,salary      --大于任何一个, 也就是大于最小的
    from employees
    where salary > any(select salary from employees where department_id = 110)  
    --###################################################################################
    --排序
    select distinct department_id, job_id from employees order by job_id desc    --默认是升序asc
    --###################################################################################
    --聚合函数
    select sum([sid]) from T_StuScore
    select count([sid]) from T_StuScore    --count(*)表示记录数, 而count(字段)忽略掉null值
    select avg([sid]) from T_StuScore
    select max([sid]) from T_StuScore
    select min([sid]) from T_StuScore
    select distinct(department_id), job_id from employees
    select distinct department_id, job_id from employees
    --###################################################################################
    --分组函数, where用于对记录的筛选, having用于对组的筛选, 并且组函数将忽略结果为null的字段
    select gender,Counter = count(*) from T_Stuinfo group by gender
    select gender,Counter = count(*) from T_Stuinfo group by gender having count(*) >= 2
    --###################################################################################
    --表连接: 笛卡尔积(m*n条记录), 内连接, 外连接(左外连接、右外连接、全外连接), 自连接
    --内连接: 先从m和n中选择, 然后再连接
    select sname,sid,cid,score 
    from T_StuInfo s inner join T_StuScore c on s.sid = c.sid
    --左外连接(左连接): 内连接 + 左表剩余记录(右表记录置null)
    select sname,sid,cid,score 
    from T_StuInfo s left join T_StuScore c on s.sid = c.sid
    --右外连接(右连接): 内连接 + 右表剩余记录(左表记录置null)
    select sname,sid,cid,score 
    from T_StuInfo s right join T_StuScore c on s.sid = c.sid
    --全外连接(全连接): 内连接 + 左表剩余记录(右表记录置null) + 右表剩余记录(左表记录置null)
    select sname,sid,cid,score 
    from T_StuInfo s full outer join T_StuScore c on s.sid = c.sid
    --自连接(全连接): 本表与本表连接, 可以是外连接或内连接
    select sname,sid,cid,score 
    from T_StuInfo s inner join T_StuInfo c on s.sid = c.sid
    --###################################################################################
    --函数: Oracle中的函数分为单行函数和组函数两种. 组函数用于Group by字句中.
    --      单行函数包括: 字符函数、数字函数、日期函数、Sysdate以及一些其他函数.
    --系统函数      注意: 如果要在sqlplus中看到输出, 需要先执行set serveroutput on
    begin
        --nvl('expression',value)函数, expression为null便用后便的value代替
        dbms_output.put_line(nvl(null,0));
        dbms_output.put_line(to_char(sysdate));
        dbms_output.put_line(to_number(123.567));
        dbms_output.put_line(to_char(to_date('2010-08-09','yyyy-mm-dd')));
    end;
    --字符函数
    begin
        dbms_output.put_line(lower('I love CHINA!'));   --大写变小写
        dbms_output.put_line(upper('You don''t even have a dog!'));   --小写变大写
        dbms_output.put_line(initcap('poo poo! my sweaty! '));   --每单词首字母大写其余小写
        dbms_output.put_line(length('Rocho^_^J'));  --求串长
        dbms_output.put_line(substr('I love CHINA!',3,4));  --求子串, Oracle中下表从1开始
        dbms_output.put_line(instr('I love CHINA!','CHINA'));   --求字串的下表
        dbms_output.put_line(instr('IN love with CHINA!','IN',5));   --求第5位后的字串的下表
        dbms_output.put_line(concat('I love',' you'));   --串连接, 不常用. 可用||代替
        dbms_output.put_line('I love' || ' you');   --串连接||
        dbms_output.put_line(lpad('I love CHINA!',20,'*'));   --填充, 15为总长度, 不够补*
        dbms_output.put_line(rpad('I love CHINA!',20,'*'));
        dbms_output.put_line(trim('C' From 'Carton MovieC'));    --去除两边的一个字符
        dbms_output.put_line(replace('I love CHINA','CHINA','you'));    --替换子串
    end;
    --数字函数
    begin
        dbms_output.put_line(round(156.26789,2)); --四舍五入, 2为保留2位小数
        dbms_output.put_line(round(156.26789,-2));  --四射五入, -2为保留整数, 从.开始
        dbms_output.put_line(trunc(123.4567,3));    --截取.后多少位, 类似上边, 但是不进位
        dbms_output.put_line(16 mod 3 );
    end;
    --日期函数: 中文系统下, 默认日期显示格式为: 日-月-年
    --通过alter Session set nls_date_format = 'yyyy-mm-dd'
    begin
        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh:mi:ss'));    --DateTime.Now
        dbms_output.put_line(to_char(sysdate,'yyyy'));  --年份
        dbms_output.put_line(to_char(sysdate,'mm'));    --月份
        dbms_output.put_line(to_char(sysdate,'dd'));    --天书
        dbms_output.put_line(to_char(sysdate,'d'));    --星期几, 结果需要-1
        dbms_output.put_line(to_char(sysdate,'ddd'));    --一年中的第几天
        dbms_output.put_line(to_char(sysdate,'hh'));    --小时
        dbms_output.put_line(to_char(sysdate,'hh12'));    --12小时制小时
        dbms_output.put_line(to_char(sysdate,'hh24'));    --24小时制小时
        dbms_output.put_line(to_char(sysdate,'mi'));    --分
        dbms_output.put_line(to_char(sysdate,'ss'));    --秒
        dbms_output.put_line(to_char(sysdate,'q'));    --季度
        dbms_output.put_line(to_char(sysdate + 2));    --加减2,得到的前或后两天
        dbms_output.put_line(to_char(
            months_between(
                to_date('2011-08-08','yyyy-mm-dd'),
                to_date('2010-11-9','yyyy-mm-dd')
                )
            )
        );  --相差几个月
        dbms_output.put_line(to_char(
            add_months(to_date('2011-08-08','yyyy-mm-dd'),3)
                ));  --相差几个月
        dbms_output.put_line(to_char(
        add_months(to_date('2011-08-08','yyyy-mm-dd'),3)
            ));  --相差几个月
        dbms_output.put_line(to_char(
        last_day(to_date('2011-08-08','yyyy-mm-dd'))
            ));  --该月最后一天
        dbms_output.put_line(to_number(
            to_char(
                to_date('2011-8-8','yyyy-mm-dd'),'yyyy')
            )
        );  --日期变数字
    end;
    select next_day(sysdate,3) from dual --返回下周2的日期, 周的计算从1开始
    --###################################################################################
    --范式: 1NF: 原子性, 2NF: 单主键, 3NF: 去除传递依赖, BCNF: 决定每个记录的因素都包含关键字, 但不是关键字的子集
    --E-R模型(表间关系): 1对1: 任何一个表添加外键; 1对多: 在多的一方添加外键; 多对多: 需要第三个表, 添加前两表的外键
    --###################################################################################
    --Oracle程序块: declare、begin、exception、end四个关键字, 注意: end后面的";"不能省略, 且打开输出set serveroutput on
    --系统变量:
    sqlcode --在异常处理中, 返回当前的错误号
    sqlerrm --在异常处理中, 返回当前的错误信息
    %rowcount   --获得sql命令影响的行数
    %rowtype    --获得表或视图的记录类型
    %type   --变量类型参考表或视图中字段的类型
    %found  --用于游标, 表示是否找到数据
    %notfound   --用于游标, 表示是否没有找到
    --自定义变量
    declare 
        v_name nvarchar2(30);
        v_email nvarchar2(50) default 'webmaster@google.com';
    begin
        v_name := '张三';
        dbms_output.put_line(v_name);
        dbms_output.put_line(v_email);
    exception   --可省略
        when others then
            dbms_output.put_line('其他异常');
    end;
    --条件表达式: 相等为:=, 比较相等为=
    declare
        v_name nvarchar2(30) := '张三';
        v_pass nvarchar2(10) := 'zhangsan';
        v_inputname nvarchar2(30);
        v_inputpass nvarchar2(10);
    begin
        v_inputname := '张三';
        v_inputpass := 'zhangsan';
        if v_inputname <> v_name then
            dbms_output.put_line('不存在用户名' || v_inputname);
        elsif v_inputpass != v_pass then
            dbms_output.put_line('密码错误!');
        elsif v_inputname = v_name and v_inputpass = v_pass then
            dbms_output.put_line('登陆成功!');
        else
            dbms_output.put_line('用户名和密码不匹配! ');
        end if;
    exception
        when others then
            dbms_output.put_line('系统异常! ');
    end;
    --循环
    declare --输出fibonacci前10项
        v_num1 number(5) := 1;
        v_num2 number(5) := 1;
        v_num3 number(5); --前两个数的和
        v_i number(5) := 0; --控制循环次数
    begin
        dbms_output.put(v_num1);   --输出初始的1,1
        dbms_output.put(' ' || v_num2);
        loop
            exit when v_i >= 8; --前两个已有
            v_num3 := v_num1 + v_num2;
            dbms_output.put(' ' || v_num3);
            v_num1 := v_num2;
            v_num2 := v_num3;
            v_i := v_i + 1;
        end loop;
        dbms_output.put_line('');   --一定要有put_line才能显示put的信息
    --省略exception
    end;
    --case when
    --搜索case when(用于一个范围), Oracle中不支持?
    --简单case when(类似swtich, 用于一个定值)
    select
        case table_name 
            when 'JOBS' then '工作表'
            when 'REGIONS' then '地区表'
            when 'DEPARTMENTS' then '部门表'
            else '其他表'
        end
    from user_tables
    declare
        v_gender number(1) := 0;
    begin
        case v_gender 
            when 0 then dbms_output.put_line('男');
                when 1 then dbms_output.put_line('女');
                    else dbms_output.put_line('暂时未知');
        end case;
    end;
    --查询处理: 单行结果可以直接处理, 而多行结果需要用游标处理
    --单行结果: 必须有一行结果, 不能是多行也不能是空行
    declare
        v_title nvarchar2(200);
        v_detail T_Event.detail%type;   --%type参照表或视图的字段类型
    begin
        select title,detail into v_title,v_detail
        from T_Event
        where seqid = 1;
        dbms_output.put_line('记事详情: ' || v_title || '   '||v_detail);
    end;
    --定义记录类型
    declare
        type T_UserInfo is record(
            seqid   number(8),
            username  nvarchar2(30),
            create_data  date
        );
        v_row   T_UserInfo;
    begin
        select seqid,username,create_data into v_row
        from T_UserInfo
        where seqid = 3;
        dbms_output.put_line('用户详情: 序号< ' || v_row.seqid || ' > , 用户姓名: < '||v_row.username || 
                             ' > , 用户创建时间: < ' ||v_row.create_data ||' > .');
    end;
    --参考记录的类型
    declare
        v_row   T_UserInfo%rowtype; --参考原表的记录类型
    begin
        select seqid,username,create_data into v_row
        from T_UserInfo
        where seqid = 3;
        dbms_output.put_line('用户详情: 序号< ' || v_row.seqid || ' > , 用户姓名: < '||v_row.username || 
                             ' > , 用户创建时间: < ' ||v_row.create_data ||' > .');
    end;
    --多行结果--->游标: 1. 定义游标cursor 2. 打开游标open 3. 从游标中读取fetch 4. 判断游标状态 5. 关闭游标close
    declare
        v_row   T_UserInfo%rowtype; --参考原表的记录类型
        cursor v_cs is select * from T_UserInfo;    --1. 定义游标
    begin
        open v_cs;  --2. 打开游标
        loop
            fetch v_cs into v_row;  --3. 从游标读取数据
            exit when v_cs%notfound;    --4. 判断游标状态
            dbms_output.put_line('序号< ' || v_cs%rowcount || ' > ');
            dbms_output.put_line('用户姓名: < '||v_row.username || ' > ');
            dbms_output.put_line('用户创建时间: < ' ||v_row.create_data ||' > ');
        end loop;
        close v_cs; --5. 关闭游标
    end;
    --游标2:
    declare
        type type_cs is ref cursor; --系统游标
        v_cs type_cs;
        v_title    T_Event.title%type;
        v_startdate date;
        v_enddate    date;
        v_detail T_Event.detail%type;
    begin
        GetNoticesByDateParametersOut(to_date('2010-7-8','yyyy-mm-dd'),v_cs);   --掉存储过程
        loop
            fetch v_cs into v_title, v_startdate, v_enddate, v_detail;
            exit when v_cs%notfound;
            dbms_output.put_line('记事详情: 标题< ' || v_title || ' > , 开始时间: < '||v_startdate || 
                             ' > , 结束时间: < ' ||v_enddate ||' > , 内容: < ' ||v_detail ||' >. ');
        end loop;
        close v_cs;
    end;
    --###################################################################################
    --事物: 事物的特性ACID(一致性(Consistency)、原子性(Atomicity)、隔离性(Isolation)、持久性(Durability))
    --Oracle中的事物由任意一条DML语句开始, 不需要显示的开始事物
    declare
        v_username nvarchar2(30);
        v_title nvarchar2(200);
        v_startdate date;
        v_enddate date;
        v_detail    nvarchar2(500);
        v_userid    number(8);
    begin
        v_username := '李四';
        v_title := '喝喜酒';
        v_startdate := to_date('2010-11-05','yyyy-mm-dd');
        v_enddate := to_date('2010-12-05','yyyy-mm-dd');
        v_detail := '准时到, 备好红包';
        insert into T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,v_username);
        insert into T_Event(seqid,title,startdate,enddate,detail,userid)
            values(seq_T_Event.nextval,v_title,v_startdate,v_enddate,v_detail,seq_T_UserInfo.currval);
         commit;
    exception   --异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
        when others then
            rollback;
            dbms_output.put_line('数据写入过程出错, 操作已回滚! ');
    end;
    --带异常控制的事物
    --raise_application_error(-20999,'XXXXX')用于抛出xxxx的自定义异常, 异常号必须在-20000~-20999之间
    begin
        --raise zero_divide;    --即使程序没有错误, 也可以手动抛出异常
        insert into T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,'王五');
        insert into T_Event(seqid,title,startdate,enddate,detail,userid)
            values(seq_T_Event.nextval,'生日',
                    to_date('2010-11-05','yyyy-mm-dd'),
                    to_date('2010-12-05','yyyy-mm-dd'),
                    '准备蛋糕',
                    seq_T_UserInfo.currval);
         commit;
    exception   --异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
    --Orale内部定义的异常, 资源忙错误号为: -54; sqlcode和sqlerrm用来返回当前错误号和错误信息
        when no_data_found then rollback;dbms_output.put_line('错误: 没有找到数据'); --错误号:-1403
        when too_many_rows then rollback;dbms_output.put_line('错误: 返回了多行数据! '); --错误号为: -1422
        when invalid_cursor then rollback;dbms_output.put_line('错误: 无效的游标 ');
        when zero_divide then rollback;dbms_output.put_line('错误: 除0错误 ');   --错误号位: -1476
        when dup_val_on_index then 
            rollback;
            dbms_output.put_line('错误: 唯一索引不能有重复值! ');
            dbms_output.put_line('数据写入过程出错, 操作已回滚! ' || '当前错误号: ' || sqlcode || '当前错误信息: ' || sqlerrm);
        when others then
            rollback;   --手动抛出异常, 程序将中断执行, 因此提前rollback
            raise_application_error(-20011,'这是自定义的错误信息! ');    --抛出自定义异常
    end;
    --###################################################################################
    --索引: 列中包含大范围值、列中包含大量null、经常被用户查询的where或join的连接条件、表中数据多, 但常用的行少于2%-4%
    create index lower_Employees_Last_name_Idx on employees(lower(last_name))   --为提高查询效率, 先转一下小写
    select * from user_indexes  --查询索引
    select * from user_ind_columns  --索引中列的数据字典
    --###################################################################################
    --存储过程(Stored Procedure): 只需要定义参数的类型, 而不能指定参数的宽度,in、out、in out, SP用来存储程序块
    --带参数的
    create or replace procedure SP_ShowFibonacci(
        p_num in number
    )
    is
        v_num1 number(5) := 1;
        v_num2 number(5) := 1;
        v_num3 number(5) := 0;
        v_i number(5) := 0;
    begin
        dbms_output.put(v_num1 || ' ');
        dbms_output.put(v_num2 || ' ');
        loop
        exit when v_i >= p_num - 2;
            v_num3 := v_num1 + v_num2;
            dbms_output.put(v_num3 || ' ');
            v_num1 := v_num2;
            v_num2 := v_num3;
            v_i := v_i + 1;
        end loop;
        dbms_output.put_line('');    --显示结果
    end Sp_ShowFibonacci;
    --调用: sqlplus中, exec ShowFibonacci(15);
    begin
        ShowFibonacci(15);  --在程序块中调用存储过程不需要exec
    end;
    --带输入参数和普通输出参数, in表示传入参数, out表示输出参数, returning用于返回刚刚插入的记录
    create or replace procedure SP_InsertAndReturnPKFromT_Event(
        p_pkid  out number, --输出参数
        p_title in nvarchar2,
        p_startdate in date,
        p_enddate date, --默认就是in
        p_detail in nvarchar2,
        p_userid in number
    )
    is
        --v_pkid number(8);
    begin
        --v_pkid := seq_t_event.curral; --暂存currval
        insert into T_Event(seqid,title,startdate,enddate,detail,userid)
            values(seq_t_event.nextval,p_title,p_startdate,p_enddate,p_detail,p_userid)
        returning seqid into p_pkid;    --注意: returning是insert的子句, 用于返回刚插入的记录, 所以前面无分号
        --p_pkid := seq_t_event.currval;    --这种返回方法, 需要在插入前缓存currval
        commit;
    exception
        when others then
            rollback;
    end InsertAndReturnPKFromT_Event;
    --调用: 带输出参数的存储过程在Sqlplus中调用也需要写程序块
    --程序块调用, 不需要exec
    declare
        v_pkid  number(8);
    begin
        SP_InsertAndReturnPKFromT_Event(v_pkid,'吃饭',to_date('2010-11-07','yyyy-mm-dd'),
                                      to_date('2010-12-05','yyyy-mm-dd'),'带红包',1);
        dbms_output.put_line(v_pkid);
    end;
    --带输入参数和系统引用输出游标
    create or replace procedure SP_GetEVentByDate(
        p_date  in date,
        p_userid in number,
        p_cs out sys_refcursor
    )
    is
        v_cs sys_refcursor; --1. 定义游标
    begin
        open v_cs   --2. 打开游标
            for
                select title,startdate,enddate,detail,userid
                from T_Event
                where to_date(to_char(startdate,'yyyy-mm-dd'),'yyyy-mm-dd') = p_date and userid = p_userid;
                --startdate要去掉时间, 才能查到多条记录. to_char之后再to_date即可
        --3. 将查询结果以游标输出
        p_cs := v_cs;
    end GetEventByDate;
    --调用
    declare
        v_cs sys_refcursor;
        v_title nvarchar2(200);
        v_startdate date;
        v_enddate date;
        v_detail nvarchar2(500);
        v_userid number(8);
    begin
        --程序块使用游标, 不需要加exec
        SP_GetEVentByDate(to_date('2010-11-05','yyyy-mm-dd'),1,v_cs);  
        --使用输出游标, 也不需要打开游标
        loop
        fetch v_cs into v_title,v_startdate,v_enddate,v_detail,v_userid;
        exit when v_cs%notfound;
        
        dbms_output.put_line(v_title || v_startdate || v_enddate || v_detail || v_userid);
        end loop;
        close v_cs; --关闭游标
    end;
    --通过存储过程插入数据
    create or replace procedure SP_NewNoticeItem(
        p_date in date, 
        p_start in date, 
        p_end in date, 
        p_title in NoticeItem.title%type, 
        p_content in NoticeItem.content%type
    )
    is
      v_count calender.itemcount%type;
      v_calender_dateid calender.dateid%type;
    begin
         --判断calender中是否有相应的记录
         select nvl(sum(itemcount),0), sum(dateid) into v_count, v_calender_dateid
         from calender
         where dateitem = p_date;
          
         --如果有, 则更新数量, 这里同样也需要暂存dateid, 以便在noticeitem表中进行添加
         if v_count >0 then
            update calender set itemcount = v_count + 1  --update set之后是采用=号
            where dateitem = p_date;
         else
         --如果无, 则增加新纪录
          --v_calender_dateid := seq_calender.nextval;   --不能直接使用sequence, 因多人访问, 需要用局部变量暂存sequence
            select seq_calender.nextval into v_calender_dateid from dual;          --以上表达式的另外一种写法
            insert into calender(dateid,dateitem,itemcount)
                   --values(seq_calender.nextval, p_date, 1); 
                   values(v_calender_dateid,p_date, 1);
         end if;
         -- 在NoticeItem表中增加活动的内容
            insert into NoticeItem(itemid, dateid, start_time, end_time, title, content)
                 --values(seq_noticeitem.curval, seq_calender.curval)  --因为多人访问, 所以seq_calender.NextVal可能被改变, 需暂存
                 values(seq_noticeitem.nextval, v_calender_dateid, p_start, p_end, p_title, p_content);
           commit;
         --如果中间有异常, 则rollback 
    exception
             when others then
             rollback;
    end SP_NewNoticeItem;
    --###################################################################################
    --函数: 与存储过程相比, 函数必须有返回类型, 内部应只对数据进行运算, 避免在函数中处理记录--用来存储程序块
    create or replace function fn_truncdate(
        p_date date
    )
    return date
    is
        result date;
    begin
        result := to_date(to_char(p_date,'yyyy-mm-dd'),'yyyy-mm-dd');
        return result;
    exception
        when others then
            dbms_output.put_line('截取日期出现错误');
    end;
    --调用
    select fn_truncdate(to_date('1985-11-11 8:30','yyyy-mm-dd hh-mi')) from dual
    --###################################################################################
    --触发器: 分为基于表(before,after)和基于视图(instead of). :new表示新纪录, :old表示旧记录.
    --        还可以通过deleting、inserting和updating获得引起触发器的动作
    --视图存储的是Sql语句, 所以通常只对表进行DML操作的触发器
    create or replace trigger tg_noticeitem
      after delete on noticeitem  
      for each row
    declare
      v_count  calender.itemcount%type;
    begin
         --触发器中的隐士参数(:new 代表操作之后的记录 :old 代表操作之前的记录)
         --(:old.dateid)表示noticeitem中的外键
         
         --读取calender表中的活动的数量itemcount
         select itemcount into v_count
         from calender
         where dateid = :old.dateid;
         
         if v_count > 1 then
            update calender
            set itemcount = itemcount -1
            where dateid = :old.dateid;
         else--注意elsif的写法
            delete from calender
            where dateid = :old.dateid;
         end if;
    end tg_noticeitem;
    --创建监视表
    drop table T_Monitor
    create table T_Monitor(
        table_name nvarchar2(30),
        constraint pk_T_monitor primary key(table_name),
        count   number(8),
        create_date date default(sysdate)
    )
    insert into T_Monitor(table_name,count) values('T_EVENT',0)
    commit
    create or replace trigger tg_monitor    --触发器没有参数
        after insert or delete or update on T_EVENT
        --for each row  --默认为statement level表示语句级, 表示一条语句执行一次, 而each row表示每行
    declare
    begin
        if deleting then
            dbms_output.put_line('删除操作! ');
        elsif inserting then
            dbms_output.put_line('插入操作! ');
        elsif updating then
            dbms_output.put_line('更新操作! ');
        end if;
        update T_Monitor set count = count + 1
        where table_name = 'T_EVENT';
    end tg_monitor;
  • 相关阅读:
    hdu5056(找相同字母不出现k次的子串个数)
    POJ 3613 快速幂+Floyd变形(求限制k条路径的最短路)
    POJ 3613 快速幂+Floyd变形(求限制k条路径的最短路)
    Poj 3522 最长边与最短边差值最小的生成树
    Poj 3522 最长边与最短边差值最小的生成树
    POJ 1716 区间最小点个数
    POJ 1716 区间最小点个数
    POJ 1679 判断最小树是否唯一
    POJ 1679 判断最小树是否唯一
    hdu 5020 求三点共线的组合数(容器记录斜率出现次数)
  • 原文地址:https://www.cnblogs.com/itelite/p/2323592.html
Copyright © 2020-2023  润新知