SQL/PLUS

SQL/PLUS

分四类：
   DQL ：select
    DDL ：create，alert，drop，rename，truncate
    DML ：insert，update，delete
    DCL ：grant(授权)，revoke(收回)
    逻辑事务管理：commit，rollback，save-point

使用SQL*PLUS
     编辑命令：
       A[PPEND] text；
       C[HANGE] /old/new；
       C[HANGE] /text/；
       CL[EAR] BUFF[ER]；
       DEL；DEL n； DEL m n；
       I[NPUT] 插入不定数量的行；
       I[NPUT] text；
       L[IST] 列出缓冲区中；
       L[IST] m n 列出从m到n；
       R[UN]；
       N 制定该行为当前行；
       n text ：用text代替n行；
       O text ：第一行前插入
    文件命令：
       SAV[E] filename [.ext][REPLACE] APP[END]
         GET filename [.ext]
         STA[RT] filename[.ext]
         @filename
         ED[IT] [filename [.ext]]
         SPO[OL] [filename [.ext]] |OFF|OUT    将查询结果保存到文件中，OFF关闭输出文件，OUT关闭并打印
      EXIT 退出SQL*PLUS

数据类型
    字符：CHAR(n)，VARCHAR(n)，LONG   最大2G
     数字：NUMBER(m，n)
     日期：DD-MON-YY
     大对象：
       内部 LOB：BLOB，CLOB，NCLOB
                BLOB：二进制数据，如图像，影像
           CBLOB：单字节字符数据，如Microsoft Word文件
           NCLOB：多字节国家字符数据，如Microsoft Word文件
       外部LOB：
           Bfile：存放在操作系统文件中，只读，Oracle数据库不能创建，二进制
           RAW(n)：可变二进制数据。n：1-2000
               LONG RAW：可变二进制数据。最大长度2G。同一表中不能同时有LONG和 LONG RAW存在。
运算符
       算术及单行运算符：+-，+，-，*，/，=，!=，<>，<，>，<=，>=
         多行及特殊运算符：any，some，all，[NOT] BETWEEN m AND n，[NOT] EXISTS，m [NOT] LIKE n，IS [NOT] NULL，IN
         逻辑运算符： NOT，AND，OR

DQL
    语法：
      SELECT [DITINCT] *|tablename-alias.column [alias]，...]
        FROM tablename [alias]，...
      WHERE [NOT] expression operater expression [AND|OR...]
        ORDER BY column|expression [ASC|DESC]，column|expression [ASC|DESC]
            column：列名
        alias：别名
        expression：可为列名，别名，数值或表达式，字符和日期必须使用单引号引起且区分大小写
        ASC：升值序
        DESC：降值序
   规则：
      ●无大小写区分
      ●可以多行形式输入
      ●关键字不能跨行
      ●关键字对齐
      ●可使用TAB和伸缩符号
      ●ORDER字句中可以使用数字序号代替列名称
   执行：
      ●句后加(;)
        ●句后加(/)
        ●SQL提示符后加(/)
        ●SQL提示符后运行RUN

    算术表达式：对所选的数据可以进行数学运算并给出别名，如遇空值，运算结果只能是空值。
   改变列标题：使用关键字AS，也可省略。
   连接字符串：(| |)
    连接查询：从不同的表中获得查询信息。使用表名限定列名，如果FROM子句中定义了表的别名，则整个SELECT语句中使用该表名的时候都得使用该别名。连接查询可分为内外连接
            查询。内连接有等价与非等价连接。当直性子连接时，必须为表指定两个别名。
   合计计算：求最大值，平均值，合计等。
   合计函数：AVG(DISTINCT|ALL|expression)
                  COUNT(*|DISTINCT|ALL|expression)
                  MAX(DISTINCT|ALL|expression)
                  MIN(DISTINCT|ALL|expression)
                  STDDEV((DISTINCT|ALL|x)：标准偏差
             SUM(DISTINCT|ALL|expression)
                  VARIANCE(DISTINCT|ALL|expression)：方差
                 除COUNT(*)外都忽略空值
    分组计算：按指定烈进行分组，然后使用合计函数
        语法：
            SELECT columnname [alias],group-function(column)
                 FROM tablename [alias],...
                 WHERE [NOT] expression operater expression [AND|OR…]
                 GROUP BY columnname
                 HAVING expression operater expression [AND|OR…]
                 ORDER BY columnname|expression [ASC|DESC],columnname|expression [ASC|DESC],…
           规则：
            ●SELECT子句后只能有两种表达式，一种是合计函数，一种是出现在ORDER BY子句后的列名。
            ●如果使用WHERE子句，所有参加分组计算的数据必须首先满足WHERE子句的条件。
            ●系统按照ORDER BY子句中指定的列的升序排列。
            ●如按多列分组，先分大足，后分小组。
       常见错误：
            ●如果在SELECT语句中没有出现ORDER BY子句，不能在SELECT子句中同时出现单个列名和合计函数的混合现象。
            ●如果希望限制分组中的数据，只能使用HAVING而不能使用WHERE子句。

       子查询：SELECT语句中使用SELECT语句。子查询又被称为内查询。包含子查询的语句被称为外查询。

       规则：

       ●子查询必须用括号括起来    

●子查询只能出现在WHERE或HANVING子句中的比较运算的右边

●不能在子查询语句中包含ORDER BY子句，外查询语句中可以

●子查询允许嵌套多层

       ●子查询中可以使用单行或多行运算符

       种类：

       ●单行子查询：返回一个常数，可运用单行运算符  

       ●多行子查询：返回多行单列（一系列值），必须运用多行运算符

           IN ANY ALL

                   <ANY：小于最大值

                   =ANY：与IN相等

                   >ANY：大于最小值

       ●多列子查询：返回多行多列

                    语法：SELECT columnname,columnnanme,…

                                FROM tablename

                                WHERE (columnname,columnname,…)

                                IN (

                                SELECT columnname,columnname,…

                                FROM tablename

                                WHERE condition)

SELECT语句的结合：

      ●UNION 返回两个查询结果，无重复

        ●UNION ALL 返回两个查询结果，可有重复

        ●INTERSECTION 返回两个查询的相同部分

        ●MINUS 返回前查询部分减去后查询部分

   SELECT语句中的函数

         语法：

         function-name (columnname|expression,[arg1,arg2,…])

         类型：

         1．字符串函数

                LOWER(columnname|expression)

                UPPER(columnname|expression)

                INITCAP(columnname|expression)

                CONCAT(columnname1|expression1, columnname2|expression2)

                SUBSTR(columnname|expression,m[,n])返回子字符串起始位置m,长度是n

                LENGTH(columnname|expression)

                INSTR(columnname|expression,c)返回指定字符c的位置

                LPAD(columnname|expression,n,’填充’)：返回n长度对齐字符串，如长度

                           不足n，则以‘填充’补齐。

         2．数字函数

               ROUND(columnname|expression[,n])：返回小数点后n位的四舍五入后的 

               值，如n为负值，则小数点前n位为0。

               TRUNC(columnname|expression[,n])：与ROUND相似，只是不进行四舍五          

               入运算。

               MOD(m,n)：返回m除以n的余数。

         3．日期函数

                Oracle系统默认的日期格式DD-MON-YY

                日期的算术运算：日期是作为数据储存的，可以执行算术运算。

●日期+数字在日期上添加指定的日数

●日期+数字在日期上减除指定的日数

                 ●日期+日期两个日期间的日数

                 ●日期+数字/24 在日期上添加指定数量的小时数

                 SYSDATE 系统当前的日期

                 MONTHS_BETWEEN(date1,date2) 两个日期间的月数

                 ADD_MONTHS(date,n) 在指定日期上添加n个月份

                 NEXT_DAY 当前系统日期的第二天

                 NEXT_DATE 指定日期下周周(n-1)的日期

                 LAST_DAY 本月的最后一天

                 ROUND(date,’YEAR|MONTH|…’) 按指定的格式返回四舍五入后的日期。如果个实施



 

4．数据类型转换函数

   ●隐式转换

     赋值时可进行的隐式转换有

       VARCHAR2或CHAR —〉NUMBER

       VARCHAR2或CHAR —〉DATE

       NUMBER —〉VARCHAR2

       DATE —〉VARCHAR2

     表达式中可进行的隐式转换有

       VARCHAR2或CHAR —〉NUMBER

       VARCHAR2或CHAR —〉DATE

●显式转换

   TO_CHAR(number|date[,’fm___’])按格式把数字或日期转换成VARCHAR2

   TO_NUMBER(char)将数字字符串转化成数字

   TO_DATE(char[,’fm___’])按格式把字符串转换成日期

5．常规函数

   ●NVL(expression1,expression2)：如果expression1中有空值存在将它转化成

expression2的值。

   ●DECODE(columnname|expression,search1,result1[,search2,result2,…][,default])

    如果expression的值等于search1，则显示result1，否则继续向下查找。如果所有的

search都不满足，显示default值。

6．替换变量

   ●&：如果在变量前使用&符号，在执行SELECT语句时，系统会提示用户提供该变量的

值规则：

1)替换变量的类型是字符，必须用引号引起来

2)系统会显示替换变量被替换的过程

●&&：如果需要重复使用某替换变量的值而不要求用户多次键入，可以在它第一次出现

时使用&&字符。

●DEFINE：DEFINE variable*value：创建一个CHAR类型变量并给予初始值

            DEFINE variable：显示变量的名称，值和类型

            DEFINE：显示所有用户定义的变量

            UNDEFINE variable：消除变量定义

●ACCEPT：ACCEPT variable [datatype][FORMAT format][PROMPT text][HIDE]

            按指定格式创建制定类型的变量，并制定用户提示，提示用户输入制定的数

据，并可隐藏用户输入。

7．制定SQL*PLUS环境

    SET命令，SHOW命令(SHOW ALL)

DML

   包括INSERT，UPDATE，DELETE。一组DML语句形成的逻辑单元被称为事务。事物的特点是其中的操作要么全部成功，要么全部失败。

1． INSERT：两种用法：一是通过关键字VALUES直接输入值；另一种是将SELECT语句的结果插入表中。

●直接提供值：

INSERT INTO tablename (column[,column,…])

VALUES(value[,value,…])

规则：

1)一次只能插入一行数据

     2)如table名后无列名，则按照表定义的列的顺序插入数据

     3)如某列允许空值，可不提供其数值

     4)可以使用系统提供的函数。如SYSDATE

     ●间接提供值：

     INSERT INTO tablename (column[,column,…])

     AS SELECT column[,column,…]

     FROM tablename

     WHERE codition

2． UPDATE：

UPDATE table

SET column=value[,column=value]

WHERE condition

3． DELECT：

DELECT FROM table

WHERE condition

如果没指定WHERE子句，表中所有数据被删除。

4． 事务管理：

连续的执行COMMIT或ROLLBACK之间的操作成为一个事务。对事务的控制包含事务提交，事务回退（或撤销）及设立检查点。Oracle规定，提交后的更新操作是不能撤销的。

●事务提交：3种方式

 1〉显示提交：COMMIT命令

         2〉隐式提交：命令如ALERT，AUDIT，COMMENT，CONNECT，CREATE，

           DISCOUNT，DROP，EXIT，GRANT，NOAUDIT，REVOKE，RENAME，

           以及退出SQL*PLUS都隐含COMMIT操作。

         2〉自动提交：用SET命令设置自动提交环境

●事务回滚：尚未提交的事务，可以使用ROLLBACK命令撤销。

 ROLLBACK TO SAVEPOINT pointname

 EXP：Savepoint sp1；

        Savepoint sp2；

        ROLLBACK to Savepoint pointname

DDL

   包括CREATE，ALERT，DROP，RENAME，TRUNCATE

   ●表

     1．创建：CREATE TABLE table(

                  Column 数据类型，

                  Column 数据类型，

                  …)；

               或 CREATE TABLE table

                  AS SELCT column [,column,…]

                  FROM table[,table,…]

                  WHERE condition

     2．修改：增加或删除列，更改列的默认值。但不能队列进行重命名。

        语法：ALERT TABLE table

              ADD|DROP column 类型[DEFAULT value]

        规则：

1〉 当增加超过一列时，用括号把要增加的列括起来。

2〉 当数据类型别修改时，列值必须为空。

3〉 删除列时，如果该列有索引或其他约束，必须使用附加功能CASCADE CONSTRAINS。

4〉 可以先将某列设为UNUSED，然后再将其删除。

语法：ALERT TABLE table

      SET UNUSED COLUMN column

     3．重命名：对一个表进行重命名后，Oracle将自动更新相应的约束，索引和与此表相关的权限。但相应的视图，同义词，存储过程和函数为非法。

        语法：RENAME table TO new_table

4．删除：表删除后，所有数据和词表的定义都被删除，标的索引，约束，触发器和权被删除。但Oracle并不删除试图，只是标示他们非法。

PL/SQL：

三部分组成：定义部分，可执行部分和例外处理部分。三部分共同形成模块。有人称PL/SQL是模块化的过程SQL。

1． 定义部分：定义在执行部分所使用的变量，常量，游标和用户自定义的例外处理。

语法：DECLARE

      变量名[CONSTANT]数据类型[NOT NULL][:=值] DEFAULT值|sql expression

    规则：

      ●以DECLEAR开头

      ●直接赋值可用：=expression

      ●间接赋值可用SELECT INTO或PETCH INTO进行

           SELECT 值[,值,…] INTO 变量名[,变量名,…]

           FROM table

    标量类型：

      ●BINARY_INTEGER：-214783647~+214783647

             子类：NATURAL，NATRUALN，POSITIVE，POSITIVEN，SIGNTYPE

      ●NUMBER：1.0E-130~9.99E125

             子类：DEC，DECMAL，DOUBLE，PRECISION，FLOAT，INTEGER，INT，NUMBERIC，REAL，SMALLINT

      ●字符类型：CHAR：最大32767字节，定长

                  VARCHAR2：最大32767字节，变长

                  LONG：最大2G字节，变长

      ●日期类型：DD-MONTH-YY

      ●布尔类型：BOOLEAN

      ●大数据类型：BFILE，BLOB，CLOB，NCLOB

2．执行部分：包括对数据库进行操作的SQL语句以及对语句进行组织，控制PL/SQL语句。

             语法：BEGIN

                   END;

      ●控制结构：

         1）IF条件语句语法：

               IF Boolean expression THEN

                  PL/SQL语句

               ELSE

                   PL/SQL语句

               END IF;

             规则：可以嵌入多个IF…THEN…ELSEIF语句形成条件语句链

          2）CASE语句语法：

                  [<lable>]

                  CASE selector

                  WHEN expression 1 THEN statement 1;

                  WHEN expression 2 THEN statement 2;

                  ……….

                  [ELSE Statement];

                  END CASE[lable_name];

                搜索CASE语句：没有选择器，根据布尔搜索条件执行。

                   [<lable>]

                   CASE

                   WHEN search_condition1 THEN statement1;

                   WHEN search_condition2 THEN statement2;

                   ……….

                   [ELSE statement];

                   END CASE [lable];

          3）LOOP循环

                 简单循环：无限循环

                 LOOP

                    Sequence_of_statements

                 END LOOP;

                 可用EXIT或EXIT [lable] WHEN condition来退出循环。

          4）WHILE LOOP循环

                 语法：

WHILE condition LOOP

    Sequence_of_statements

END LOOP;

          5）FOR循环

                  语法：

                  FOR counter IN [REVERSE] lower_bound…higher_bound LOOP

                      Sequence_of_statements

                  END LOOP;

          6）GOTO和NULL语句

                  语法：GOTO lable

                  NULL语句：不做任何操作，只提高可读性

      ●DMS_OUTPUT.PUT_LINE程序：用于SQL*PLUS下显示运算结束

            SQL>SET SERVEROUTPUT ON

            之后可用DBMS_OUTPUT.PUT_LINE程序将结束显示在屏幕上。

●过程(PROCEDURE)与函数(FUNCTION)

           过程，函数，触发器和包都是带名的PL/SQL块，也可将过程，函数成为子程序。过程和函数类似，过程是为了执行一定任务而组合在一起的SQL或PL/SQL语句集，而函数的最终任务是返回一个值。函数的调用是表达式的一部分，而过程的条用本身就是一条PL/SQL语句。

      创建过程：

      CREATE [OR REPLACE] PROCEDURE procedurename

      [param1{IN|OUT|IN OUT}] param_type

      ………

      paramN [{IN|OUT|IN OUT}] paramN_type]

      {IS|AS} procdure_body;

      调用：

        Procedure_name(param1=>值1,param2=>值2,…);

    　创建函数：

      CREATE [OR REPLACE] FUNCTION functionname

        [Param1 [{IN|OUT|INOUT}] param1_type,

         ……

         ParamN [{IN|OUT|INOUT}] paramN_type]

      RETURN returntype {IS|AS} function_body

      函数的调用：与过程相似但需考虑函数的返回值。

      ●本地子程序的调用：在调用本地子程序前必须先声明该子程序，但在声明部分必须将子程序的声明放在最后。Oracle支持先声明后定义的模式。

      ●游标：

        定义：CURSOR cursor_name IS select_statement

        打开：即执行定义中的SELECT语句

               OPEN cursor_name;

        取值：对游标第一次执行FETCH语句时首先将指针指到第一行（成为当前行），然后将该行的数据输出

              FETCH cursor_name INTO variable [,variable,……];

        关闭：游标关闭后，系统自动释放资源。

        属性：%FOUND,%NOTFOUND,%ROWCOUNT和%OPEN李

例：

条件：

IF up_sal >= 2000 THEN

   RAISE out_range;

END IF;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     INSERT INTO TEMP VALUES(NULL,NULL,’employee not found’);

   WHEN out_range THEN   (自定义陷阱)

     INSERT INTO TEMP VALUES(NULL,NULL,’out of range SAL’);

   WHEN OTHERS THEN

     INSERT INTO TEMP VALUES(sql_code,NULL,sql_errm);

                             (系统错误,    错误内容)    

     COMMIT;

游标：

DECLARE CURSOR CURselline IS      (定义游标)

   SELECT ENAME,SAL+NVL(COMM,0) SALS

            FROM INTO TEMP VALUES(selrec.SALS,null,selrec.ENMAE);

游标取值方法1：

OPEN CURselline;

FOR selrec in CURselline LOOP

     INSERT INTO TEMP VALUES(selrec.SALS,null,selrec.ENMAE);

     COMMIT;

END LOOP;

CLOSE CURselline;

游标取值方法2：

LISTselrec CURselline%ROWTYPE; (定义与游标相同结构的结构行)

OPEN CURselline;                  (打开游标)

LOOP

    FETCH CURselline INTO LISTselrec;(循环下一条，并把数据传给结构行)

    EXIT WHEN CURselline%NOTFOUND;(判断游标是否到尾)

    INSERT INTO TEMP VALUES(LISTselrec.SALS,NULL,LISTselrec.ENMAE);

    COMMIT;

END LOOP;

CLOSE CURselline;(关闭游标，释放资源)

方法：

CREATE OR REOLACE FUNCTION 方法名    (建立方法)

    (

       参数1   类型，

       参数2   类型…...

    )

RETURN 返回值类型

IS

    [变数定义]

BEGIN

    ……….

EXCEPTION

    例外处理部

END;

ans    NUMBER(1):=0;

FOR cnt IN 1..10 LOOP

      ans:=ans+cnt;             (给变量赋值)

END LOOP;

WHILE cnt<=10 LOOP

    ans:=ans+cnt;

    cnt:=cnt+1;

END LOOP;

CREATE OR REPLACE PRECEDURE 存储过程名 (建立可返回值的存储过程)

 (

     参数1 类型 OUT/INPUT, (OUT只有返回值，INPUT需要传值的返回值)

     参数2 类型……

)

IS

    [变数定义]

BEGIN

        ……

EXCEPTION

         例外处理部

END;

CREATE OR REPLACE PACHAGE 存储过程 (单纯的数据处理过程，无参数和返回值)

IS

 PROCEDURE 可返回值的存储过程

         ……

 FUNCTION    方法

         ……

 ……………

EXCEPTION

   ………

END;

Execute 存储过程([参数1],[参数2]……) (调用存储过程)