PL/Sql循序渐进全面学习教程(上)

一、写SQL 命令：
     不区分大小写。
     SQL 语句用数字分行，在SQL PLUS中被称为缓冲区。
     最后以；或 / 结束语句。
     也可以用RUN来执行语句
二、对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。
     SQL> SELECT last_name, salary, 12 * (salary + 100)
     2 FROM s_emp;
三、列的别名ALIASES：
 计算的时候特别有用；
 紧跟着列名，或在列名与别名之间加“AS”；
 如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。
 例(因字体原因，读者请记住：引号为英文双引号Double Quotation)：
 SQL> SELECT last_name, salary,
 2 12 * (salary + 100) ”Annual Salary”
 3 FROM s_emp;
四、连接符号：||
 连接不同的列或连接字符串
 使结果成为一个有意义的短语：
 SQL> SELECT first_name || ’ ’ || last_name
 2 || ’, ’|| title ”Employees”
 3 FROM s_emp;
五、管理NULL值：
 SQL> SELECT last_name, title,
 2 salary * NVL(commission_pct,0)/100 COMM
 3 FROM s_emp;
 此函数使NULL转化为有意义的一个值，相当于替换NULL。
七、ORDER BY 操作：
 与其他SQL92标准数据库相似，排序如：
 SELECT expr FROM table
 [ORDER BY {column,expr} [ASC|DESC]];
 从Oracle7 release 7.0.16开始，ORDER BY 可以用别名。
 另：通过位置判断排序：
 SQL> SELECT last_name, salary*12
 2 FROM s_emp
 3 ORDER BY 2;
 这样就避免了再写一次很长的表达式。
 另：多列排序：
 SQL> SELECT last name, dept_id, salary
 2 FROM s_emp
 3 ORDER BY dept_id, salary DESC;
八、限制选取行：
 SELECT expr FROM table
 [WHERE condition(s)]
 [ORDER BY expr];
 SQL> SELECT first_name, last_name, start_date
 2 FROM s_emp
 3 WHERE start_date BETWEEN ’09-may-91’
 4 AND ’17-jun-91’;
 例如：SQL> SELECT last_name
 2 FROM s_emp
 3 WHERE last_name LIKE ’_a%’;  //显示所有第二个字母为a的last_name
 如果有列为NULL
 SQL> SELECT id, name, credit_rating
 2 FROM s_customer
 3 WHERE sales_rep_id IS NULL;
 优先级：
 Order        Evaluated Operator
 1   All comparison operators  (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)
 2   AND
 3   OR
九、FUNCTION的作用：
 进行数据计算，修改独立的数据，处理一组记录的输出，不同日期显示格式，进行数据类型转换
 函数分为：单独函数(ROW)和分组函数
 注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出现。
 语法：function_name (column|expression, [arg1, arg2,...])
十、字符型函数
    1、LOWER 转小写                          
    2、UPPER                                 
    3、INITCAP  首字母大写                   
    4、CONCAT  连接字符，相当于 ||           
    5、SUBSTR  SUBSTR(column|expression,m[,n])
    6、LENGTH   返回字符串的长度   
    7、NVL    转换空值
    其中，1、2经常用来排杂，也就是排除插入值的大小写混用的干扰，如：
    SQL> SELECT first_name, last_name
          2 FROM s_emp
          3 WHERE UPPER(last_name) = ’PATEL’;
         
       FIRST_NAME LAST_NAME
       -------------------- --------------------
         Vikram    Patel
         Radha     Patel
十一、数学运算函数
 1、ROUND
          四舍五入：ROUND(45.923，2)  = 45.92
          ROUND(45.923，0)  = 46
          ROUND(45.923，-1)  = 50
 2、TRUNC
          截取函数
          TRUNC(45.923，2)= 45.92
          TRUNC(45.923)= 45
          TRUNC(45.923，-1)= 40
    3、MOD  余除
          MOD(1600，300)
          实例：
       SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
           2 ROUND(45.923,-1)
           3 FROM SYS.DUAL;
十二、ORACLE 日期格式和日期型函数：
 1、默认格式为DD-MON-YY.
    2、SYSDATE是一个求系统时间的函数
 3、DUAL［'dju:el] 是一个伪表，有人称之为空表，但不确切。
       SQL> SELECT SYSDATE
          2 FROM SYS.DUAL;
 4、日期中应用的算术运算符
       例：SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS
             2 FROM s_emp
             3 WHERE dept_id = 43;
       DATE+ NUMBER = DATE
       DATE-DATE= NUMBER OF DAYS
       DATE + (NUMBER/24) = 加1小时
    5、函数：
        MONTHS_BETWEEN(date1, date2) 月份间隔，可正，可负，也可是小数
        ADD_MONTHS(date,n)  加上N个月，这是一个整数，但可以为负
        NEXT_DAY(date,‘char’) 如：NEXT_DAY (restock_date,’FRIDAY’),从此日起下个周五。
        ROUND(date[,‘fmt’])
        TRUNC(date[,‘fmt’])
        解释下面的例子：
        SQL> SELECT id, start_date,
          2 MONTHS_BETWEEN (SYSDATE,start_date) TENURE,
          3 ADD_MONTHS(start_date,6) REVIEW
          4 FROM s_emp
          5 WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48;
        我们看到：  MONTHS_BETWEEN (SYSDATE,start_date)<48，说明至今工作未满一年的员工。
        LAST_DAY (restock_date) 返回本月的最后一天
       SQL> select round(sysdate,'MONTH') from dual
       ROUND(SYSD
        ----------
        01-11月-01
      round(sysdate,'YEAR') = 01-1月 -02   
       ROUND 之后的值比基值大的最小符合值，大家可以用更改系统时间的方法测试，以15天为分界线，也是非常形象的四舍五入，而TRUNC恰好相反，是对现有的日期的截取。
十三、转换函数：
 1、TO_CHAR
         使一个数字或日期转换为CHAR
 2、TO_NUMBER
         把字符转换为NUMBER
 3、TO_DATE
         字符转换为日期
    这几个函数较为简单，但要多多实践，多看复杂的实例。
    SQL> SELECT ID,TO_CHAR(date_ordered,’MM/YY’) ORDERED
         2 FROM s_ord
         3 WHERE sales_rep_id = 11;
    转换时，要注意正确的缺省格式：
      SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL；//正确
      SELECT TO_DATE('031092') CORRECT FROM DUAL；//不正确
      SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL
            输出 3月10日
      SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL
            输出 10月3日
    4、实例：
      select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;
      TODAYS
      --------------------------------
      SIXTEENTH of 11月 2001 下午
      大小写没有什么影响，引号中间的是不参与运算。
      实例 ：
      SELECT ROUND(SALARY*1.25) FROM ONE_TABLE；
      意义：涨25%工资后，去除小数位。在现实操作中，很有意义。
    5、混合实例：
  SQL> SELECT last_name, TO_CHAR(start_date,
  2 ’fmDD ”of” Month YYYY’) HIREDATE
  3 FROM s_emp
  4 WHERE start_date LIKE ’%91’;
  LAST_NAME HIREDATE
  ------------ --------------------
  Nagayama 17 of June 1991
  Urguhart 18 of January 1991
  Havel 27 of February 1991
  这里要注意：fmDD 和 fmDDSPTH之间的区别。
     SQL> SELECT id, total, date_ordered
         2 FROM s_ord
         3 WHERE date_ordered =
         4 TO_DATE(’September 7, 1992’,’Month dd, YYYY’);
十四、独立的函数嵌套
    SQL> SELECT CONCAT(UPPER(last_name),
         2 SUBSTR(title,3)) ”Vice Presidents”
         3 FROM s_emp
         4 WHERE title LIKE ’VP%’; 
    * 嵌套可以进行到任意深度，从内向外计算。      
    SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS                                       
    2 (date_ordered,6),’FRIDAY’), 
    3 ’fmDay, Month ddth, YYYY’)
    4 ”New 6 Month Review”
    5 FROM s_ord
    6 ORDER BY date_ordered;  
    SQL> SELECT last_name,
         2 NVL(TO_CHAR(manager_id),’No Manager’)
         3 FROM s_emp
         4 WHERE manager_id IS NULL;
十五、连接的概念：
 是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。
 连接类型：等连接、不等连接、外连接、自连接
十六、Cartesian product ：
 指的是当JOIN条件被省略或无效时，所有表的行(交叉)都被SELECT出来的现象。          
 Cartesian product可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。
 SQL> SELECT name, last_name                                                     
 2 FROM s_dept, s_emp;                                                           
 300 rows selected. 其中一个表12行，一个表25行。                                 
十七、简单连接查询--表别名ALIAS：
 实例：
 SQL> SELECT c.name ”Customer Name”,
 2 c.region_id ”Region ID”,
 3 r.name ”Region Name”
 4 FROM s_customer c, s_region r
 5 WHERE c.region_id = r.id;
 别名最多可以30个字符，但当然越少越好。最好也能容易识别。
十八、非等连接
 非等连接一般用在没有明确的等量关系的两个表；
 最简单的说：非等连接就是在连接中没有“=”出现的连接。
 SQL> SELECT e.ename, e.job, e.sal, s.grade
 2 FROM emp e, salgrade s
 3 WHERE e.sal BETWEEN s.losal AND s.hisal;
 说明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必须在另一个表中最高和最低之间。
 其他操作符<= >= 也可以实现，但是BETWEEN是非常简单实用的。
 BETWEEN ....AND是指闭区间的，这点要注意 ，请大家测试。
十九、外连接
 语法结构：SELECT table.column, table.column FROM table1, table2
 WHERE table1.column = table2.column(+);
 实例：
 SQL> SELECT e.last_name, e.id, c.name
 2 FROM s_emp e, s_customer c
 3 WHERE e.id (+) = c.sales_rep_id
 4 ORDER BY e.id;
 显示.....，即使有的客户没有销售代表。
 * 可以理解为有+号的一边出现了NULL，也可以做为合法的条件。
 外连接的限制：
 1、外连接符只能出现在信息缺少的那边。
 2、在条件中，不能用IN或者OR做连接符。
二十、组函数
   1. 组函数是指按每组返回结果的函数。
      组函数可以出现在SELECT和HAVING 字段中。
      GROUP BY把SELECT 的结果集分成几个小组。
      HAVING 来限制返回组，对RESULT SET而言。
   2.组函数：(#号的函数不做重点)
      1、AVG
      2、COUNT
      3、MAX
      4、MIN
      5、STDDEV #
      6、SUM
      7、VARIANCE #
    实例1：一个混合实例，说明所有问题：
     SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
      2 SUM(salary)
      3 FROM s_emp
      4 WHERE UPPER(title) LIKE ’SALES%’;
        AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
        ----------- ----------- ----------- -----------
         1476       1525          1400        7380
 说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。
 * MIN MAX函数可以接任何数据类型。
 如果是MIN(last_name), MAX(last_name)，返回的是什么呢？
 千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt>cssingkdkdk>adopt>acccc
 实例2：
       SQL> SELECT COUNT(commission_pct)
         2 FROM s_emp
         3 WHERE dept_id = 31;
         返回所有非空行个数
   3.GROUP BY的应用：
   SQL> SELECT credit_rating, COUNT(*) ”# Cust”
      2 FROM s_customer
      3 GROUP BY credit_rating;    
    注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#$_，什么对象的名字都如此。当然空格也是可以的。
 复杂实例：
     SQL> SELECT title, SUM(salary) PAYROLL
          2 FROM s_emp
          3 WHERE title NOT LIKE ’VP%’
          4 GROUP BY title
          5 ORDER BY SUM(salary);
    这里要注意一下几个CLAUSE的先后次序。
    WHERE在这里主要是做参与分组的记录的限制。
    **另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUP BY !否则要出错的!信息为：ERROR at line 1:
 ORA-00937: not a single-group group function
 理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。
    结论：不加分组函数修饰的列必定要出现在GROUP BY 里。
    错误实例：
      SQL> SELECT dept_id, AVG(salary)
        2 FROM s_emp
        3 WHERE AVG(salary) > 2000
        4 GROUP BY dept_id;
       
    WHERE AVG(salary) > 2000
 *
 ERROR at line 3:
 ORA-00934: group function is not allowed here
    应在GROUP BY 后面加上HAVING AVG(salary) > 2000;
    因为是用来限制组的返回。
    多级分组实例：
     SQL> SELECT dept_id, title, COUNT(*)
          2 FROM s_emp
          3 GROUP BY dept_id, title;
     就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。
     顺序对结果有决定性的影响。
   SQL SERVER中有COMPUTE BY，来进行分组总数的计算，但在ORACLE中是没有的。
二十一、子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。
      子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。
      注意：1、子查询必须在一对圆括号里。
            2、比较符号：>, =, 或者 IN.
            3、子查询必须出现在操作符的右边
            4、子查询不能出现在ORDER BY里   (试题中有时出现找哪行出错)
 1.子查询的执行过程
   NESTED QUERY      MAIN QUERY
      SQL> SELECT dept_id                       SQL> SELECT last_name, title
       2 FROM s_emp                            2 FROM s_emp
       3 WHERE UPPER(last_name)=’BIRI’;      3 WHERE dept_id =
      这里 ，每个查询只运行一次。当然，子查询要首先被执行，大家设想一下，如果子查询中有一个以上的人的LASTNAME为BIRI，会如何？-----会出错，因为不能用=来连接。
      ORA-1427: single-row subquery returns more than
       one row
      以上的查询也被称之为 单行子查询。
      DELECT子查询实例：
      delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
          select pro_name from new_product where pro_addr in ('bj','sh'))
    2.子查询中的GROUP 函数的应用
  实例 1：
     SQL> SELECT last_name, title, salary
       2 FROM s_emp
       3 WHERE salary <
       4 (SELECT AVG(salary)
       5 FROM s_emp);
     实例2：
      选择出工资最高的员工的家庭住址：
     select emp_addr from employees where salary =
          (select max(salary) from employees);
      这是一个简单实用的例子，可以衍生出很多情况，在实际应用经常出现，请大家多多思考。
     实例3：
      SQL> SELECT dept_id, AVG(salary)
         2 FROM s_emp
         3 GROUP BY dept_id
         4 HAVING AVG(salary) >
         5 (SELECT AVG(salary)
         6 FROM s_emp
         7 WHERE dept_id = 32);
     子查询被多次执行，因为它出现在HAVING 子句中。
        SQL> SELECT title, AVG(salary)
          2 FROM s_emp
          3 GROUP BY title
          4 HAVING AVG(salary) =
          5 (SELECT MIN(AVG(salary))
          6 FROM s_emp
          7 GROUP BY title);
二十二、运行时应用变量
 本课重点：
     1、创建一个SELECT语句，提示USER在运行时先对变量赋值。
     2、自动定义一系列变量，在SELECT运行时进行提取。
     3、在SQL PLUS中用ACCEPT定义变量
 变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。
  1、我们这里的运行时，指的是在SQL PLUS中运行。
  2、ACCEPT ：读取用户输入的值并赋值给变量
  3、DEFINE：创建并赋值给一个变量
  4、在做REPORT时经常使用，比如对某个部门的销售信息进行统计，部门名称可以以变量代替。
  SQL PLUS不支持对输入数据的有效性检查，因此提示要简单且不模棱两可。
 1.应用实例：
  （1）SQL> SELECT id, last_name, salary
            2 FROM s_emp
            3 WHERE dept_id = &department_number;
        （2）可以在赋值前后进行比较：
         SET VERIFY  ON
         .....
   1*  select * from emp where lastname='&last_name'
   输入 last_name 的值:  adopt
   原值    1:  select * from emp where lastname='&last_name'
   新值    1:  select * from emp where lastname='adopt'
  ----如果在原语句中没有单引号，那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。
  SET VERIFY OFF 之后，原值和新值这两句消失。这在ORACLE8I中是默认为ON。
  （3）子句为变量：WHERE &condition;  要注意引号
 2.DEFINE和ACCEPT的应用：
 （1）SET ECHO OFF   //使内容不显示在用户界面
        ACCEPT p_dname PROMPT ’Provide the department name: ’
         SELECT d.name, r.id, r.name ”REGION NAME”
          FROM s_dept d, s_region r
          WHERE d.region_id = r.id
          AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
          /
       SET ECHO ON
        存为文件：l7prompt.SQL
        SQL> START l7prompt
           Provide the department name: sales
      （2）SQL> DEFINE dname = sales
          SQL> DEFINE dname
           DEFINE dname = ”sales” (CHAR)
          SQL> SELECT name
           2 FROM s_dept
           3 WHERE lower(name) = ’&dname’;
        可以正常执行了。
         SQL> DEFINE dname  主要是显示当前的变量是否赋值，值是什么。当然，我们可以用UNDEFINEGO 来使变量恢复初始，不然它会一直保持下去。
   （3）如果变量在SQL SCRIPT文件中确定 ：可以SQL> START l7param President 来赋值。
 本课主要针对较古老的SQLPLUS方法，在REPORT和结果集生成方面使用变量，达到方便操作，动态修改的目的。
二十三、其他数据库对象
 1.创建实例：
  SQL> CREATE SEQUENCE s_dept_id
  2 INCREMENT BY 1
  3 START WITH 51
  4 MAXVALUE 9999999
  5 NOCACHE
  6 NOCYCLE;
  Sequence created.
  (1)NEXTVAL和CURRVAL的用法
　　   只有在INSERT中，才可以作为子查询出现。
　　 以下几个方面不可用子查询：
　　　　SELECT子句OF A VIEW
　　　　有DISTINCT的出现的SELECT。
　　　　有GROUP　BY，HAVING，ORDER　BY的SELECT　子句。
　　　　SELECT或DELETE，UPDATE中的子查询。
　　　　DEFAULT选项中不能用。
  (2)编辑SEQUENCE
　　 只有OWNER或有ALTER权限的用户才能修改SEQUENCE
　　 未来的NUMBER受修改的影响。
　　 不能修改START　WITH，如果变，则要RE－CREATE。
　　 修改会受到某些有效性检验的限制，如MAXVALUE
　　 (3)删除：
　　 DROP SEQUENCE sequence;
 2.创建索引
  index是ORACLE的一种数据对象，用POINTER来加速查询行。通过快速路径存取方法定位数据并减少I/O。　INDEX独立于表。INDEX由ORACLE　SERVER来使用和保持。
  index的建立：（1）自动：通过PRIMARY　KEY和UNIQUE KEY约束来建立。(2)用户手工建立非唯一性索引。
  何时建立index：（1） 此列经常被放到WHERE字段或JOIN来作条件查询。（2）此列含有大量的数据。(3)此列含有大量的空值。(4)两个或几个列经常同时放到WHERE字段进行组合查询(5)表很大而且只有少于2-4% 的ROW可能被查询的时候。
  以下情况下不要建立索引：表很小；表被更新频繁。
  查看已经存在的索引：
   （1）USER_INDEXES可以查询索引名和类型。
   （2）USER_IND_COLUMNS包含索引名、表名、列名。
   实例：SQL> SELECT ic.index_name, ic.column_name,
    2 ic.column_position col_pos, ix.uniqueness
    3 FROM user_indexes ix, user_ind_columns ic
    4 WHERE ic.index_name = ix.index_name
    5 AND ic.table_name = ’S_EMP’;
  删除索引：
   DROP INDEX index;
 3. SYNONYMS 同义词
  CREATE [PUBLIC] SYNONYM synonym for object;
      注意：此对象不能包含在一个包里；
        一个私有的同义词不能与同一USER的其他对象重名。
     DROP SYNONYM D_SUM；
二十四、用户访问控制
 A.概述：
  ORACLE通过用户名和密码进行权限控制。
      数据库安全：系统安全和数据安全
      系统权限：使用户可以访问数据库
      对象权限：操纵数据库中的对象
      SCHEMA：各种对象的集合
    B.系统权限：
       1、超过80个权限可用。
       2、DBA有最高的系统权限：
               CREATE NEW USER
               REMOVE USERS
               REMOVE ANY TABLE
               BACKUP ANY TABLE
 C.创建用户：
    1、CREATE USER user IDENTIFIED BY password;
       2、系统权限：CREATE SESSION Connect to the database.
                    CREATE TABLE Create tables in the user’s schema.
                   CREATE SEQUENCE Create a sequence in the user’s schema.
                   CREATE VIEW Create a view in the user’s schema.
                   CREATE PROCEDURE Create a stored procedure, function, or package in
                   the user’s schema. 
       3、授权用户系统权限：
         GRANT privilege [, privilege...] TO user [, user...];
         GRANT CREATE TABLE TO SCOTT；
 D.角色的使用：
         1、概念：角色是一组权限的命名，可以授予给用户。这样就如同给了某个用户一个权限包。
        2、创建、授予给角色：
          CREATE ROLE MANAGER；
          GRANT CREATE TABLE，CREATE VIEW TO MANAGER；
          GRANT MANAGER TO CLARK
 E.修改密码：
      ALTER USER user IDENTIFIED BY password;
 F.对象权限：
   1、语句：
    GRANT {object_priv(, object_priv...)|ALL}[(columns)]
   ON object
   TO {user[, user...]|role|PUBLIC}
   [WITH GRANT OPTION];
      2、实例：
         最简单：
      SQL> GRANT select
   2 ON s_emp
   3 TO sue, rich;
     稍复杂：
       SQL> GRANT update (name, region_id)
        2 ON s_dept
        3 TO scott, manager;
     SQL> GRANT select, insert
   2 ON s_dept
   3 TO scott
   4 WITH GRANT OPTION;