Oracle数据库基础

1.desc 表名；查看表中的列名和列类型；                数据库的字符连接是用"||"
2.select table_name from user_tables;查询用户中的表名；         select username from dba_users;查询管理员中的用户；
3.set linesize n；设置数据库每行显示的长度
4.show autocommit； 显示自动提交
5.set autocommit on； 设置自动提交为开；
6.clear scr ；清屏
7.ed；   用来打开文本编辑上次写错的sql语句
8.drop table 表名；删除表；
9.rename 表名1 to 表名2；改表1为表2
10.set serverout on 表示pl/sql显示在屏幕上
11.dbms_output.put_line(v_sex);表示打印在pl/sql的屏幕上
12.show user；显示用户名；
13.create user 用户名 identified by 密码;//在管理员帐户下，创建用户
14.alter user 用户名 identified by 密码;//在管理员帐户下，修改用户
15.alter user 用户名 account {lock|onlock};//在管理员帐户下,解锁或者锁定用户
16.grant {system_privilege|role} to {用户名|role|PUBLIC} [with admin option];授予系统特权
    其中：system_privilege包括:connect<resource<dba;
        connect用create session表示，即登录权限

17.grant {object_privilege|ALL} [column] on 表名/索引 to {user|role|PUBLIC} with admin option;
    其中：object_privilege包括：insert,delete,update,select,alter
    grant alert all table to 用户名;//授予用户alert任意表的权限

18.批量导入sequence：(必须是system用户下)

select 'create sequence '||sequence_name||
' minvalue '||min_value||
' maxvalue '||max_value||
' start with '||last_number||
' increment by '||increment_by||
(case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'
from dba_sequences where sequence_owner='用户名';

19.左右连接:（+）在哪一边，则返回另一边所有的记录
**************************************************************************

oracle数据库有四种类型：char(n),varchar2(n),number(n,m),date,BLOB----保存图片的数据类型
其中：date的格式是'22-5月-83',dd-n月-yy;只有number类型不用''
1.查询：select 列名1，列名2，... from 表名；也可以给列起别名，别名和列名用空格隔开；select 列名 别名 from 表名；
2.增加：insert into 表名(列名1，列名2，...) values(值1，值2...);例：insert into table values(to_date('2008-12-14'));
3.更新：update 表名 set 列名1=值1，列名2=值2,... where condition;没有where condition则更新全部数据
4.删除：delete from 表名 where condition；
**************************************************************

表的修改：
1.增加新列：alter table 表名 add(列名 类型)；
2.删除列：alter table 表名 drop(column 列名)；
3.修改列(只能改列类型)：alter table 表名 modify(列名 新的类型)；
***************************************************************************

创建序列：
基本语法：create sequence 序列名；       使用时：序列名.nextVal   当前序列 序列名.currVal 
如：在Oracle中                           在MySql中                    在sql Server2000中
创建序列：                                           两者一起完成
create sequence seq_test;                create table test(                create table test(
创建test表：                             id number auto_increment primary key,        id int identity(1,1) primary key,
create table test(                       name varchar(2)                name varchar(4)
id number primary key,                   );                        );
name varchar2(2)                                     插入数据时
);                                                   insert into test values('heh');
插入数据
insert into test values(seq_test.nextVal,'heh');
**************************************************************************

创建视图：
基本语法：create view 视图名 as select 列名1，列名2，... from 表名；其中：视图名可以为“名字_view”的形式
也可以为视图创建与表不同字段名：create view 视图名字(新列1，新列2，...) as select 列名1，列名2，... from 表名；
***************************************************************************

创建索引：
基本语法:create index 索引名 on 表名(列名1，列名2...);其中：索引名可以为“名字_index”的形式
**************************************************************************

case 语句的用法：                     例子：
格式：select 列名1，列名2，...,              select grade 年级，sum(case sex when 'm' then 1 else 0 end) 男性总数，    
      case  
       when 列名+运算符+值1  then 列新值1                sum(case sex when 'f' then 1 else 0 end) 女性总数
           when 列名+运算符+值2  then 列新值2         from student 
       else   列新值3                 group by grade;
      end    新列
      from  表名；
其中：列新值可以取列名中的值，用法是直接写列名；
作用：列新值1，列新值2构成的新列
********************************************************************************************

select * from table where colum group by colum having 分组函数；

注意：聚合函数忽略null，Group by分组中，where子句中不能出现分组函数，having子句能出现分组函数或group by colum having colum
    to_char(date,'yyyy-MM-dd')转换日期date为字符
    NVL(列名，新值)用‘新值’来替换‘列名’中的值，类型必须匹配
***********************************************************************************************

完整性约束：
1.not null;--------->alter table 表名 modify 列名 not null；
2.unique;----------->alter table 表名 add constraint 约束名 unique(列名1，列名2，...);
3.primary key;------>alter table 表名 add constraint 约束名 primary key(列名1，列名2，...);
4.foreign key;------>alter table 表名 add constraint 约束名 foreign key(列名1，列名2，...) references 父表名(列名1，列名2);
5.check.---------------->alter table 表名 add constraint 约束名 check(列名1，列名2，...);
其中：unique，primary key和foreign key可以实现多列
例1：
create table person(
id number primary key,
name varchar2(8) not null,
phone varchar2(20) unique,
age number(3) constraint age_check check(age>0)
)
例2：
create table child(
id number references person(id)，
name varchar2(8) references person(name)
)
--------------------
以上两例子说明了单列的完整性约束

例3：
create table person1(
id number,
name varchar2(8),
phone varchar2(20),
age number(3) constraint age_check check(age>0),
primary key(id,name),
unique(name,phone)
)
例4：
create table child1(
id number，
name varchar2(8),
foreign key(id,name) references person1(id,name)
)
----------------
以上两例子说明了单列的完整性约束
***********************************************************************

级联删除用法：
1.要删除父表，必须先删除子表；
2.可以添加：on update/delete cascade直接删除父表；
  其中：当父表引用列的数据被更新或删除时，子表中相应的数据也被更新或删除。
  用法：foreign key(列名1，列名2) references 父表名(列名1，列名2) on update/delete cascade;
************************************************************************

一类重要的查询需求：
要查询在某个字段范围内最什么的(最大、最小、最多、最少等等)几个，需要通过TOP-N分析法。
其中:Top-N分析法就是用来求出某个字段上最什么的前n个值。
用法：select column_list,rownum 
      from (select column_list from table order by Top-N_字段名)
      where rownum<=N;-------------------------------------------desc为降序
************************************************************************

set serveroutput on
一般的执行：“/”

一、pl/sql程序的基本结构：
    Declare--------可选部分
      变量、常量、游标、用户定义异常的声明
    Begin----------必选部分
          sql语句和pl/sql语句构成的执行程序
    Exception------可选部分
      程序出现异常时，捕获异常并处理异常
    end；----------必选部分
二、pl/sql的变量：
     1.简单变量：变量名  数据类型；
    例：v_sex char(2);
     2.组合变量：
          表类型变量：1.Type 表类型名 is table of 数据类型 【not null】index by binary_interger；
              2.变量名 表类型名；
          例：Type table_type_name is table of Varchar2(16) index by binary_interger;
                      v_table table_type_name;
       其实质：就像数组一样，只能放一种类型的值；
         记录类型变量:1.Type 记录类型名 is record
                         (列名1 类型1，
              列名2 类型2)；
               2.变量名 记录类型名；
                   例：Type record_type_name is record
               (age number(2),
                sex char(2));
               v_record record_type_name;
     3.通过%type或%rowtype来为变量声明类型
     %type：一种类型，前缀为：表的字段名
        例： v_id emp.empno%type;
     %rowtype：多种类型，前缀为：记录类型变量、游标和表名；
        例：v_record emp%rowtype;
三、赋值：(:=)
     1.简单变量：v_sex:='女'；
     2.组合变量：
        表类型：v_table(1):='lucy';
                v_table(2):='lily';
        或者   v_table:=table_type_name('lucy','lucy1');
    记录类型：v_record.sex:='男';
        也可以通过select语句赋值，如：select 列名1，列名2 into v_record from 表名 where 列=:1 using v_sex；
        :1,占位
     3.在Begin中赋值：
    %type:
      begin
        select 列名1 into v_id from 表名；
    %rowtype:
          begin
        select 列名1，列名2 into v_record from 表名；
************************************************************

Begin部分的语句：
一、条件判断语句:
   1、if condition then statements;
      elsif condition then statements;
         else statements;
      end if;
   2、case语句，可以用case语句给变量赋值；
二、循环:                        
   1、loop循环：                    
         loop
             statement1；
             statement2；
         exit when condition；
         end loop；
   其中：when后面的条件为真，则退出循环；
   2、for...loop循环：
      for 控制变量 in【reverse】 下限..上限
         loop
         statement1；
             statement2；
     end loop；
   3、while..loop循环：
      while condition
         loop
         statement1；
             statement2；
     end loop；

对于数组：

declare
  Type channel_type is table of number(4);
  v_channel channel_type := channel_type(6,8);
begin
  for i in 1 .. v_channel.count loop
    dbms_output.put_line(v_channel(i));
  end loop;
end;
********************************************************************

游标：cursor
1、声明游标:
     declare
          cursor 游标名 is select语句但不能出现into；
2、打开游标：(在begin部分执行)
     open 游标名；
3、提取数据：(在begin部分执行)
     fetch 游标名 into 变量名列表；
   或fetch 游标名 into 记录变量；
4、关闭游标：(在begin部分执行)
     close 游标名；
例1：
   declare
    v_ename varchar2(20);
    v_job varchar2(20);

    TYPE T_CUR IS REF CURSOR;
    emp_cursor T_CUR;
   begin
    open emp_cursor for (select ename,job from emp where empno=:1) using 7788;
    loop
        fetch emp_cursor into v_ename,v_job;
        EXIT WHEN emp_cursor%NOTFOUND;
        dbms_output.put_line(v_ename||v_job);
        close emp_cursor;
    end loop;
    close emp_cursor;
   end;
************************************************************************************
例2：当游标处理多行记录数据时;
   declare
          cursor emp_cursor is select * from emp;
   begin
          for emp_record in emp_cursor loop
          dbms_output.put_line(emp_record.ename);
          end loop;
   end;

   推荐下面
   begin
          for emp_record in (select * from emp) loop
          dbms_output.put_line(emp_record.ename);
          end loop;
   end;
*******************************************************************

创建和执行存储过程：
用法：
   create [or replace] procedure 存储过程名 [
   (参数 IN|OUT|IN OUT 数据类型(不可指定长度),
    ...)
   ] 
    as|is
    [说明部分] [(游标，变量)]；
    begin
         可执行部分
    [exception]
             [错误处理部分]
        end；
     其中：IN可以被称为实参，OUT被成为形参；
运行存储过程：
   1.execute   用户名.存储过程名[(参数...)];
   2.begin 
    用户名.存储过程名[(参数...)];
     end；
例1：(无参变量)
   create or replace procedure emp_count_pro
     as
     v_total number(10);
     begin
         select count(*) into v_total from emp;
         dbms_output.put_line(v_total);
     end;
例2：(无参游标)
   create or replace procedure emp_list_pro
      as
      cursor emp_cursor is select ename,sal from emp;
      begin
         for emp_record in emp_cursor loop
             dbms_output.put_line(emp_record.ename||'********'||emp_record.sal);
         end loop;
      end;
例3:(无参游标)
    create or replace procedure emp_list_pro
      as
        TYPE T_CUR IS REF CURSOR;
        cur1 T_CUR;
        ename varchar2(1000);
    sal number;
      begin
    open cur1 for (select ename,sal from emp);
        loop
          FETCH cur1
          INTO ename, sal;
      EXIT WHEN cur1%NOTFOUND;
            dbms_output.put_line(ename||'********'||sal);
        end loop;
    close cur1;
      end;    
例4.(IN有参)
    create or replace procedure change_sal_pro(
    p_empno IN number,
    p_raise IN number)
       as
        v_ename varchar2(10);
        v_sal number(5);
       begin 
          select ename,sal into v_ename,v_sal from emp where empno=p_empno;
          update emp set sal=sal+p_raise where empno=p_empno;
      dbms.output_put.line(v_ename||v_sal);
      commit;
       end;
执行方法：
    declare
       v_empno number(5);
       v_raise number(5);
    begin
       v_empno:=7788;
       v_raise:=1000;
       change_sal_pro(v_empno,v_raise);
    end;
*************或：execute change_sal_pro（7788,1000);
    begin
       change_sal_pro（7788,1000);
    end;
例5.(有参OUT)：输出参数会把他得到的值返还给我们
    create or replace procedure emp_count_pro(
         p_total OUT number)
        as
     begin 
       select count(*) into p_total from emp;
     end;
执行方法：
    declare
       v_count number(5);
    begin
       emp_count_pro(v_count);
       dbms_output.put_line(v_count);
    end;
*****************************************************************************

创建和执行函数：
用法：
    create or replace function 函数名[
    (参数  IN 数据类型(不可以指定长度))
    ]
    return 数据类型(不可以指定长度);
    as|is
        [说明部分] [(游标，变量)]
    begin
        可执行部分
        return 表达式必须和返回的数据类型一致；
    [exception]
    end;
例：
     create or replace function avg_emp(
     p_empno IN number)
    return number
    as
    v_sal number(5);
    begin 
        select sum(sal) into v_sal from emp where empno=p_empno;
        return v_sal;
    end；

***********************************************************

包=包头+包体
1.创建包头：
  语法：
    create or replace package 包名A
        is|as
        procedure|function|variable|cursor---------------------注：不用具体的实现代码
        end；
  例：
    create or replace package comm_pack
     is
    g_comm number:=0.10;
    procedure reset_comm(p_comm IN number);
    end;
2.创建包体：
  语法：
    create or replace package body 包名A------------------------注：和包头包名一致
        is|as
        procedure|function|variable|cursor----------------------注：所有在包头中声明的存储过程和函数都要具体的实现代码 
        end；
  例：
    create or replace package body comm_pack
    is
    function validate_comm(p_comm IN number)
    return boolean
        is
        v_max_comm number;
        begin
        select max(commission_pct) into v_max_comm from employees;
        if p_comm>v_max_comm then 
            return false;
        else
            return true;
        end if;
    end;
    procedure reset_comm(p_comm IN number)
         is
    begin
        if validate_comm(p_comm) then
            g_comm:=p_comm;
        else
            raise_application_error(-20220,'不合理的数');
        end if;
    end;
    end;
**********************************************************************************

触发器：----------------------首先要设置可用：alter trigger 触发器名 enable；
在Oracle数据库中主要有三种触发器类型：
 1.DML触发器，由表上执行的insert|update|delete操作触发；
    1>.语句DML触发器语法格式：
    create or replace trigger 触发器名
        befer|after
          insert[or update or delete]  on 表名
        [when condition]
    begin 
        ...
    end;

    例：
      create or replace trigger secure_tri
         befor 
        insert or delete or update on emp
      begin 
    if (to_char(sysdate,'DY') in ('SAT','SUN')) or (to_char(sysdate,'HH24') not between '08' and '18') then
        if deleting then
            raise_application_error(-25001,'你可以删除emp表在上班时间');
        elsif  inserting then
            raise_application_error(-27840,'你可以增加emp表在上班时间');
        else
            raise_application_error(-24508,'你可以更新emp表在上班时间');
        end if；
    end if；
       end；
     2>.行级DML触发器语法格式：
     create or replace trigger 触发器名
         befer|after
          insert[or update or delete]  on 表名
        [referencing old as old|new as new]
           for each row
            [when condition]
     begin
        ...
     end;
      例1：
    create or replace trigger restrict_salary
         before 
        insert or update od salary on emp
           for each row
    begin
         if not(:new.job_id in('AD_pres','AD_vp')) and :new.salary>15000 then 
        raise_application_error(-22657,'员工不能赚到怎么多钱');
         end if；
    end；
       例2：
     1->.create table logger(
        num number(10) not null,
        message varchar2(50) not null);
         2->.create or replace trigger log_sal
        before 
           update of sal on emp
              for each row
            when(new.sal='CLERK' and ABS(new.sal-old.sal)>200)
        declare
            v_no number;
        begin
            select count(*) into v_no from logger;
            insert into logger values(v_no++,'雇员'||:new.ename||'的工资'||:old.sal||'新工资'||:new.sal);
        end;
 注意：
     1>.对于update事件，修改具体的列用update of 列名1，列名2...;
     2>.触发器触发事件产生(:old)和(:new)，
    insert时,:old='',:new=当前值；
    delete时,:old=以前值,:new=''。
     3>.在when条件中引用new和old不需要加“:”;
 2.instead of 替代触发，用于视图的操作；
      instead of 触发器的语法格式:
    create or replace trigger 触发器名
       instead of
        insert[or delete or update] on 视图名
        [referencing old as old|new as new]
        [for each row]
    begin
        ...
    end;
 3.系统触发器，系统事件触发。
      create or replace trigger 触发器名
     before|after
          [database_event1][database_event2 or ...]
          on Database|schema
      begin
     ...
      end;
例1：
     create or replace trigger logon_trig
        after
        logon on schema
     begin
    insert into log_trig_table(user_id,log_date,action) values(user,sysdate,'loggin on');
     end;
例2；
     create or replace trigger logoff_trig
    before
       logoff on schema
     begin
    insert into log_trig_table(user_id,log_data,action) values(user,sysdate,'logging off');     
     end;