SQL/PLUS

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]……) (调用存储过程)

 

 

from:http://www.cnblogs.com/liangx85/articles/1172428.html