宝宝Oracle学习总结

Oracle：一个数据库有多个用户,一个用户拥有一组数据库对象,这个用户我们俗称为方案,
      通常一个业务系统对应一个数据库用户。
★Oracle安装成功后会自动创建十来个系统界别的账户。默认情况下这些账户中只有四个可用，其它的帐号都被设置成了锁定或是设置为过   期状态，这四个帐户就是SYS，SYSTEM，SYSMAN，DBSNMP。SYSTEM帐户有着访问数据库中所有对象的权限。

★Oracle操作工具很多，如果是黑窗口就用sqlplus命令。

★Oracle常用操作工具：PL/SQL Development。
★导出命令：exp，将用户的对象导出成dmp文件（备份） 。
★导入命令：imp，将用户导出的dmp文件导入到数据库（恢复）。   
★dual是一个虚拟表，用来在特定情况下写出符合规范的SQL语句。
  如:(1)用sql来完成数学运算：SELECT 1+1 FROM dual;
     (2)用sql来调用系统函数(当前时间)：SELECT sysdate FROM dual;
     (3)用sql来调用某张表序列:SELECT seq_temp.nextval FROM dual;
★两个系统函数sysdate，to_date
    to_date将一个字符串转换成日期类型:
    SELECT to_date(‘2009-03-22 23:22:15’,’yyyy-mm-dd hh24:mi:ss’) FROM dual;
★在执行SQL时字符串一定要用单引号('')括起来。
★Oracle中关键字和表名、字段名都不区分大小写，相当灵活。
★Oracle中:=是赋值运算符，=是比较运算符。
★nvl(comm,0)是个函数，当comm为null时，返回0。
★在oracle中这里的表名用别名时不能加as关键字。

SQL(Structured Query Language，结构查询语言)，这里的练习只针对Oracle数据库。
SQL所有操作：
    DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。
    DML包括：  SELECT：用于检索数据；
        INSERT：用于增加数据到数据库；
        UPDATE：用于从数据库中修改现存的数据
        DELETE：用于从数据库中删除数据。

    DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。
    DDL包括：DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令：
        CREATE TABLE：创建表
        ALTER TABLE
        DROP TABLE：删除表
        CREATE INDEX
        DROP INDEX

    DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。
    DCL包括：
        ALTER PASSWORD
        GRANT
        REVOKE
        CREATE SYNONYM

一：DCL（数据控制语言）
    1、创建用户test2，密码也是test2（记得最有以分；号结束）：
        create user test2 identified by test2;
    2、给test2授权：create session;（允许用户登陆Oracle）：
        grant create session to test2;
    3、给test2分配创建表的权限；
        grant create table to test2;
    4、给test2分配表空间的使用权限；
        grant unlimited tablespace to test2;

二：DDL(数据定义语言练习)
    1、创建表：SQL> create table t_user(
                userid number(30) not null primary key,
                username varchar(20) not null,
                age number(3),
                sex varchar(2),
                departid number(30) not null,
                foreign key(departid) references t_depart(departid)
            );
    ★alter table student add primary key(userid)这样用alter也可以创建关联主外键。

    2、删除表：SQL> drop table t_depart;
   
    3、创建序列：
        create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache;

三：DML（数据操作语言）：
    1、insert插入SQL：
        (1)insert into t_depart (departid,departname,createdate)
           values (1,'市场部',sysdate);
        (2)insert into t_user values (seq_user.nextval,'马文涛',23,'男');

    2、delete删除SQL:
        (1)delete t_user;(太可怕了，如果在删除时不加条件，则把此表中的所有数据都会删除！)
        (2)delete t_user where userid = 3;

    3、update更新SQL:
        (1) update t_user set username = '争伟',sex = '男';
            (太恐怖了,更新时不加条件表中所有行记录的姓名都被修改了！)
        (2)update t_user set username = '文涛' where userid = 7;
    ★小结：我发现在增、删、改的SQL语句中用不到from关键字。
   
    4、select查询SQL:格式——>select···from···where···group by···having···order by···;
        (1)查询所有的用户：select * from t_user;
         ★在oracle中这里的表明用别名时不能加as关键字
         如：    select * from t_user u;正确
            select * from t_user as u;错误

select count(*) tablename; 查询总数

        (2)查询指定的列： select username,sex from t_user;

        (3)as给列以别名显示:select username as 用户名 from t_user;(这里的as关键字可以省略)

        (4)distinct去掉重复的行：select distinct username from t_user;

        (5)使用运算符：select age+10 from t_user;(给每个人的年龄都加10岁)

        (6)连接字符串： select '用户名：' || username from t_user;(Oracle用||做连接字符串操作符)

        (7)where子句：select * from t_user where username = '宝宝';

        (8)between and在···之间：
            select * from t_user where userid between 9 and 10;这也等价于下面这条SQL：
            select * from t_user where userid >=9 and userid <=10;

        (9)in匹配集合中的任意值：select * from t_user where username in('马文涛','宝宝');

        (10)like模糊查询：%匹配0个或多个任意字符串，_匹配1个任意字符串。

            select * from t_user where username like '%涛%';

        (11)null判断某列为空：select * from t_user where sex is null;
            (这里用is，不能用=，如果要返回不为null的记录就可以用is not null)
           
        (12)order by排序：ASC: 升序排列（可以省略）,DESC: 降序排列
            升序:select u.userid,u.username from t_user u order by u.userid;
            降序:select u.userid,u.username from t_user u order by u.userid desc;

        (13)系统函数(对一组数据进行处理，返回一个值):
            AVG–求平均值，COUNT–统计记录数，MAX–最大值，MIN–最小值，SUM–求和
            <1>返回最小和最大的用户编号： select min(userid),max(userid) from t_user;
   (1)select * from code_info where scan_time=(select min(scan_time) from code_info) and his_or_fav = 0;
            <2>返回总记录数： select count(*) from t_user;
            <3>返回某个字段不为空的记录数： select count(sex) from t_user;
            <4>返回不为空且不重复的记录数： select count(distinct sex) from t_user;

        (14)group by分组(分组了就不能直接返回*，经常和聚合函数count(age)一起使用):
             <1>按姓名分组，并统计每组人数:select count(*),username from t_user group by username;
             <2>根据多个字段分组: select username,age,count(*) from t_user group by username,age;
        ◆group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

        (15)having过滤分组：select username from t_user group by username having count(*) > 2;

        (16)子查询（子查询自身只能返回一个单独的值）：
            <1>子查询放在select后面，作为其中的一个字段返回。
                select u.username,(select d.departname from t_depart d where d.departid = u.departid) from t_user u;
                (返回用户和所属的部门，这中子查询理论上先执行外面的查询,只是我的感觉哦，嘿嘿！)
            <2>子查询放在from后面，作为一张临时表。
                select * from (select username,sex s from t_user where departid=1)
                where s = '男';
                (这种子查询应该先执行里面)
            <3>子查询放在where后面，作为条件的一部分。
                select * from t_user where departid = (select departid from t_depart where departname = '财务部');
                (这种子查询也应该先执行里面)

        (17)联合查询(当n张表连接时, 需要n-1个连接条件)：
            <1>等值连接(内连接)：select u.username,d.departname from t_user u,t_depart d
                     where u.departid = d.departid;
            <2>外连接：即把不满足条件的记录也返回，用个+就行了，
            （+）操作符在哪边就代表另外一边不满足联合条件的记录可以被输出。这个感觉不太常用。
                select b.book_id,b.book_name from book_info as b,book_click_num as c
                 where b.book_id = c.book_id(+);


四、触发器trigger:当特定事件发生时自动执行的代码块。
    这些事件包括：
    (1)DML语句(INSERT，UPDATE，DELETE):before在DML语句被执行前触发操作,after在DML语句被执行后触发操作。
    (2)DDL语句（CREATE 及 ALTER）
    (3)系统事件，例如启动/关闭[startup/shutdown]、错误[error]
    (4)用户事件, 例如登录/退出[logon/logoff]
★两个特殊的变量——>:new新的记录值，:old保留原来的记录值
    简单示例：create or replace trigger update_depart_trigger
            after update on t_depart for each row
          begin update t_user u set u.departid = :new.departid
            where u.departid = :old.departid;
          end update_depart_trigger;
    此示例的作用:当部门的id改变后，由于用户中有外键引用了部门，所以用户的departid这个外键自动执行更新操作。
    (这个触发器很奇怪，在黑窗口用sqlplus就不能创建，用plsql Developer工具就可以创建，很有意思，嘿嘿！)

五、游标cursor:以循环取SQL语句的SELECT内容，它是存放结果集的数据对象,使用游标,我们只能逐条记录地得到查询结果。
    作用：查询数据库，获取记录集合(结果集)的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
    使用：用游标有四种基本的步骤:声明游标(declare)、打开游标(open)、提取数据(fetch)、关闭游标(close)。
    ★当你要往每一行插入一个数据只能用游标，或者更新结果集中的每行记录时也可用游标,用存储过程返回一个结果集。
    ★触发器和存储过程会和数据库绑定，即一直保存在数据库中，而游标不会，它是任意时刻创建再打开再执行再关闭，
      与数据库没有任何直接关系。
    ★在Oracle中,不需要显示销毁游标.因为在Oracle中,很多东西是由JAVA写的.Oracle会自动销毁游标。

    简单示例：   
        -- 定义一个游标
        declare
           cursor cursor_user
             is
             select username,age
               from t_user;
           //变量的定义也可以放到游标定义上面
           a t_user.username%type;//定义个a变量，类型是t_user表中username列的类型。
           b t_user.age%type;//同上
        begin//SQL中可执行代码都在begin和end之间。
          -- 打开游标
          open cursor_user;
         
          -- 遍历游标
          loop//循环抓取数据(loop是其中一种循环方式)
             fetch cursor_user into a,b; -- 将一行记录放入到变量中
            dbms_output.put_line(a || '   ' || b);打印到输出控制台
             exit when cursor_user%notfound; -- 当没有记录时退出循环
          end loop;//退出循环
          -- 关闭游标
          close cursor_user;
        end;

        加if条件判断游标示例：
            declare
               cursor cursor_user
                 is
                 select username,age
                   from t_user;
               a t_user.username%type;
               b t_user.age%type;
            begin
              open cursor_user;
              loop
                 fetch cursor_user into a,b;
                   if a='宝宝'and b=43 then
                     dbms_output.put_line(a || '   ' || b);
                    end if;
                 exit when cursor_user%notfound;
              end loop;
              close cursor_user;
            end;

   

六、存储过程procedure
    概念:其实就是一组存放在数据库中SQL语句，普通SQL操作都在项目中写死的，而他只跟数据库进行绑定。
         更准确的说存储过程是数据库服务器端的一段程序，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户(oracle存储过程本身没返回值，只是用out参数代替)。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。

    优点： 
        1、提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。
        2、提高安全性。假如将SQL语句混合在JSP代码中，代码外漏以后，也就意味着库结构外漏。
        3、有利于SQL语句的重用。
    ★oracle函数有返回值，但存储过程没有返回值，它的所有返回值都是通过out参数来替代的。
    ★什么时候需要用存储过程
        　　如果服务器定义了存储过程，应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：
        .服务器往往具有强大的计算能力和速度。
        .避免把大量的数据下载到客户端，减少网络上的传输量。
        　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。
        不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。


    ★存储过程的参数
    　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：
    　　第一种称为输入参数(in)，由客户程序向存储过程传递值。
    　　第二种称为输出参数(out)，由存储过程向客户程序返回结果。
    　　第三种称为输入/输出参数(in out),既可以由客户程序向存储过程传递值,也可以由存储过程向客户程序返回结果。
    　　第四种称为状态参数，由存储过程向客户程序返回错误信息。
    要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。


    简单示例：
        1、无返回值存储过程(插入一个用户)
            create or replace procedure saveuser
            (    username in varchar2 ,
                age in number,
                departid in number
            //定义两个输入参数,参数类型可以是自己写死，也可以用某个列的类型
            比如：username t_user.username%type;
            ) as
            begin
               insert into t_user (userid,username,age,departid) values (seq_user.nextval,username, age,departid);
            end saveuser;
            (1)在sql中执行存储过程call saveuser('文涛',23,1);，也可以用execute和exec。
          
            (2)在java里调用时就用下面的代码：
            package com.hyq.src;
             
            import java.sql.*;
            import java.sql.ResultSet;
             
            public class TestProcedureOne {
             public TestProcedureOne() {
             }
             public static void main(String[] args ){
                String driver = "oracle.jdbc.driver.OracleDriver";
                String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:ORCLA";
                Statement stmt = null;
                ResultSet rs = null;
                Connection conn = null;
                CallableStatement cstmt = null;
             
                try {
                  Class.forName(driver);
                  conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
                  CallableStatement proc = null;
                  proc = conn.prepareCall("{ call saveuser(?,?,?) }");
                  proc.setString(1, "马文涛");
                  proc.setInt(2,23);
                  proc.setInt(3,1);
                  proc.execute();
                }
                catch (SQLException ex2) {
                  ex2.printStackTrace();
                }
                catch (Exception ex2) {
                  ex2.printStackTrace();
                }
                finally{
                  try {
                if(rs != null){
                  rs.close();
                  if(stmt!=null){
                    stmt.close();
                  }
                  if(conn!=null){
                    conn.close();
                  }
                }
                  }
                  catch (SQLException ex1) {
                  }
                }
             }
            }

    2、有返回值的存储过程(oracle存储过程本身没返回值)只是用out参数代替（非列表）
    //获得某部门下的所有用户姓名和年龄
    create or replace procedure pro_getUserByDepart//oracle中不区分大小写，这里只是为了自己方便。
    (    i_departid in t_user.departid%type,
        o_username out t_user.username%type,
        o_age out t_user.age%type
    )as
    begin
       select username,age into  o_username,o_age from t_user  where  departid = i_departid;
    end pro_getUserByDepart;

    ★在pl/sql Developer中右击存储过程选择测试就可以在下面输入值直接看打印结果了。
   
    ★由于这个存储过程会返回行记录集，所有在Developer中为了测试成功就在此过程中加入rownum=1条件判断，
    即:select username,age into  o_username,o_age from t_user  where  departid = i_departid and rownum=1;
    如果不加那个rownum=1(当然你也可以让它等于2)，多行记录会报“实际返回的行数超出请求的行数”这个异常。

    ★其实我们用上面这种存储过程一般都只返回一行记录集，如果是返回多行那就要用到要用包pagkage和游标cursor了。

    ★在利用select...into...语法时(把查询的结果放入输出参数中)，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

    3、返回列表(必须要用包了,package里包含了游标)，分两个步骤：
        ★为什么要在存储过程中用到游标时，要把此游标封装到一个包中呢，个人理解：由于游标是机开机关的一个
          对象，它无法被存储过程调用，而包中恰恰能封装游标、函数等这些对象，所有就把游标放在一个包中。
        (1)1， 建一个程序包。如下：
            create or replace package userpackage as
             type usercursor is ref cursor;
            end userpackage;

        (2)建立存储过程，存储过程为：
            create or replace procedure pro_getalluser
            (   
                pro_cursor out userpackage.usercursor
            )is
            begin
                open pro_cursor for select * from t_user;
            end pro_getalluser;
            可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

        ★在java里调用时就用下面的代码(列出主要代码)：
            package com.mwt.test;

                import java.sql.CallableStatement;
                import java.sql.Connection;
                import java.sql.DriverManager;
                import java.sql.ResultSet;

                import oracle.jdbc.OracleTypes;

                public class TestProcedure {
                    public static void main(String...args){
                     try{  
                        Class.forName("oracle.jdbc.driver.OracleDriver");  
                        String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL9I";  
                        String user = "test2";  
                        String password = "test2";       
                        Connection con = DriverManager.getConnection(url, user, password);  
                        String sql = "{call pro_getalluser(?)}";  
                        CallableStatement cs = con.prepareCall(sql);  
                        cs.registerOutParameter(1, OracleTypes.CURSOR);  
                        cs.execute();                    
                        //返回第一个存储过程的输出参数，我只返回了一个游标也就只有一个输出参数，即就是获得第一个返输出参数getObject(1);              
                        ResultSet rs = (ResultSet)cs.getObject(1);  
                        while(rs.next()){  
                            System.out.println(rs.getString(1)+"----"+rs.getString(2)+"----"+rs.getString(3));  
                        }                    
                        rs.close();  
                        cs.close();  
                        con.close();  
                        }catch(Exception e){  
                        e.printStackTrace();  
                        }    
                    }
                }


七、包
    存储过程可以理解为编译好的sql块，包的范畴大一些，不仅可以包括存储过程，还可以包括函数、游标、类型的定义，常、变量的定义等等。
    ★在存储过程中返回一个记录集时就用到包，因为包中可以包含游标、函数等等。