<七> 常用SQL语法与函数
    <1>、SQL SERVER端常用语法说明

    1、使用局部变量:

       1> 变量定义:

              DECLARE @variable_name datatype [,…]

          例:

              declare

                 @name    varchar(30),

                 @type   int

       2> 给变量赋值:

         方法一:

              例:

                  declare @int_var    int

                  select @int_var = 12

         方法二:

              例:

                  declare

                     @single_auth    varchar(40),

                     @curdate        datetime

                  select @single_auth = au_lname,

                         @curdate     = getdate()

                    from authors

                   where au_id = ‘123-45-6789’

 

2、使用T-SQL标准控制结构:

       1> 定义语句块

          语法:

              BEGIN

                 Statements

              END

 

       2> IF ... ELSE语句

          语法:

              IF boolean_expression

                 { statement | statement_block }

              ELSE

                 { statement | statement_block }

          示例:

              if (select avg(price) from titles where type = ‘business’) > $19.95

                  print ‘The average price is greater then $19.95’

              else

                  print ‘The average price is less then $19.95’

 

       3> IF EXISTS语句

          语法:

              IF [not] EXISTS (select_statement)

                 { statement | statement_block }

              [ELSE

                 { statement | statement_block }]

          示例:

              declare

                 @lname      varchar(40),

                 @msg        varchar(255)

              select @lname = ‘Smith’

              if exists(select * from titles where au_lname = @lname)

                 begin

                    select @msg = ‘There are authors named’ + @lname

                    print @msg

                 end

              else

                 begin

                    select @msg = ‘There are no authors named’ + @lname

                    print @msg

                 end

 

       4> 循环语句:

          WHILE

          语法:

              WHILE boolean_condition

                 [{ statement | statement_block }]

              [BREAK]

              [condition]

          示例:

              declare

                 @avg_price              money,

                 @max_price              money,

                 @count_rows             int,

                 @times_thru_the_loop int

              select @avg_price            = avg(price),

                     @max_price            = max(price),

                     @count_rows           = count(*),

                     @times_thru_the_loop = 0

                from titles

              while @avg_price < $25 and (@count_rows < 10 or @max_price < $50)

              begin

                 select @avg_price          = avg(price) * 1.05,

                        @max_price          = max(price) * 1.05,

                        @time_thru_the_loop = @time_thru_the_loop + 1

              end

              if @time_thru_the_loop = 0

                 select @time_thru_the_loop = 1

              update titles

                 set price = price * power(1.05, @time_thru_the_loop)

 

       4> GOTO语句

          语法:

              GOTO label

              ...

              label:

          示例:

              begin transaction

                 insert tiny(c1) values(1)

                 if @@error != 0 goto error_handler

                 commit transaction

                 return

              error_handler:

                 rollback transaction

                 return

 

       5> RETURN语句

          语法:

              RETURN

          (1)用于无条件退出一个批处理、存储过程或触发器。

               示例:

                   if not exists(select 1 from inventory

                                  where item_num = @item_num)

                   begin

                      raiseerror 51345 ‘Not Found’

                      return

                   end

                   print ‘No error found’

                   return

          (2)用于存储过程中返回状态值。

               示例:

                   create procedure titles_for_a_pub

                          (@pub_name varchar(40) = null)

                   as

                   if @pub_name is null

                      return 15

                   if not exists(select 1 from publishers

                                  where pub_name = @pub_name)

                      return –101

                   select t.tile from publishers p, titles t

                    where p.pub_id = t.pub_id

                      and pub_name = @pub_name

                   return 0

 

    3、T-SQL中的游标提取循环语句:

   (1)FETCH [NEXT FROM] cursor_name INTO @variable_1, ...@variable_n

   (2)WHILE @@FETCH_STATUS = 0

        BEGIN

           Other_statements

           FETCH [NEXT FROM] cursor_name INTO @variable_1, ...@variable_n

        END

   (3)CLOSE cursor_name

 

    4、T-SQL中的事务处理语句:

       1> 开始一个事务:

          BEGIN TRAN[SACTION [transaction_name]]

 

       2> 提交一个事务:

          COMMIT TRAN[SACTION [transaction_name]]

 

       3> 回滚一个事务:

          ROLLBACK TRAN[SACTION [transaction_name]]

 

       4> 使用事务保存点:

          BEGIN TRAN[SACTION [transaction_name]]

             SAVE TRAN[SACTION] savepoint_name

             ROLLBACK TRAN[SACTION] savepoint_name

          COMMIT TRAN[SACTION [transaction_name]]

 

    5、T-SQL中可用于错误判断或其它处理的全局变量:

       1>  @@rowcount:        前一条命令处理的行数

       2>  @@error:           前一条SQL语句报告的错误号

       3>  @@trancount:        事务嵌套的级别

       4>  @@transtate:        事务的当前状态

       5>  @@tranchained:  当前事务的模式(链接的(chained)或非链接的)

       6>  @@servername:    本地SQL SERVER的名称

       7>  @@version   :        SQL SERVER和O/S的版本级别

       8>  @@spid:            当前进程的id

       9>  @@identity:        上次insert操作中使用的identity值

       10> @@nestlevel:        存储过程/触发器中的嵌套层

       11> @@fetch_status: 游标中上条fetch语句的状态

 

    6、使用标准内置错误消息发送函数:

       函数说明:

           RAISERROR ({msg_id | msg_str}, severity, state

           [, argument1 [,argument2][,...] screen.width/2)this.style.width=screen.width/2;" border=0>

           [WITH LOG]

 

       其中,msg_id表示错误号,用户定义错误消息的错误号在50001到2147483647之

       间,特定的消息会引起错误50000。msg_str是错误消息正文,最多可有255个字

       符。Severity描述了与这个消息关联的用户定义的严重性级别,取值包括0和10

       至25之间的任何整数。State描述了错误的“调用状态”,它是1到127之间的整

       数值。Argument定义用于代替在msg_str中定义的变量或对应与msg_id的消息的

       参数。WITH LOG表示要在服务器错误日志和事件日志中记录错误。

       例1:

          RAISEERROR( ‘Invalid customer id in order.’, 16, 1)

          则返回:

              Msg 50000, Level 16, State 1

              Invalid customer id in order.

       例2:

          sp_addmessage 52000, 16, ‘Invalid customer id %s in order’

          RAISEERROR( 52000, 16, 1, ‘ID52436’)

          则返回:

              Msg 52000, Level 16, State 1

              Invalid customer id ID52436 in order.

   

    <2>、ORACLE端常用语法说明

    1、使用局部变量:

       1> 定义变量:

          VARIABLE_NAME   DATA TYPE   [ := INITIAL VALUE ] ;

          例:定义变量

              v_Num       number;

              v_string    varchar2(50);

          例:定义变量并赋初值

              v_Num       number := 1 ;

              v_string    varchar2(50) := ‘Hello world!’ ;

       2> 给变量赋值:

         方法一:

              例:

                  v_Num      := 1;

                  v_string := ‘Hello world!’;

         方法二:

              例:

                  SELECT first_name INTO v_String

                    FROM students

                   WHERE id = v_Num ;

 

    2、使用PL/SQL标准控制结构:

       1> 定义语句块

          语法:

              BEGIN

                 Statements ;

              END ;

 

       2> IF ... THEN ... ELSE语句

          语法:

              IF boolean_expression THEN

                 { statement | statement_block } ;

              [ELSIF boolean_expression THEN      /*注意此处的写法—— ELSIF */

                 { statement | statement_block } ;]

              ...

              [ELSE

                 { statement | statement_block } ;]

              END IF ;

 

          示例:

              v_NumberSeats rooms.number_seats%TYPE;

              v_Comment VARCHAR2(35);

              BEGIN

              /* Retrieve the number of seats in the room identified by ID 99999.

                 Store the result in v_NumberSeats. */

                SELECT number_seats

                  INTO v_NumberSeats

                  FROM rooms

                 WHERE room_id = 99999;

                IF v_NumberSeats < 50 THEN

                   v_Comment := 'Fairly small';

                ELSIF v_NumberSeats < 100 THEN

                   v_Comment := 'A little bigger';

                ELSE

                   v_Comment := 'Lots of room';

                END IF;

              END;

 

       3> 循环语句:

         (1)简单循环语句:

              语法:

                  LOOP

                     { statement | statement_block } ;

                     [EXIT [WHEN condition] ;]

                  END LOOP ;

                  其中,语句EXIT [WHEN condition];等价于

                      IF condition THEN

                         EXIT ;

                      END IF ;

              示例1:

                  v_Counter BINARY_INTEGER := 1;

                  BEGIN

                    LOOP

                      -- Insert a row into temp_table with the current value of the

                      -- loop counter.

                      INSERT INTO temp_table

                           VALUES (v_Counter, 'Loop index');

                      v_Counter := v_Counter + 1;

                      -- Exit condition - when the loop counter > 50 we will

                      -- break out of the loop.

                      IF v_Counter > 50 THEN

                         EXIT;

                      END IF;

                    END LOOP;

                  END;

 

              示例2:

                  v_Counter BINARY_INTEGER := 1;

                  BEGIN

                    LOOP

                      -- Insert a row into temp_table with the current value of the

                      -- loop counter.

                      INSERT INTO temp_table

                           VALUES (v_Counter, 'Loop index');

                      v_Counter := v_Counter + 1;

                      -- Exit condition - when the loop counter > 50 we will

                      -- break out of the loop.

                      EXIT WHEN v_Counter > 50;

                    END LOOP;

                  END;

 

         (2)WHILE循环语句:

              语法:

                  WHILE condition LOOP

                     { statement | statement_block } ;

                  END LOOP ;

 

              示例1:

                  v_Counter BINARY_INTEGER := 1;

                  BEGIN

                    -- Test the loop counter before each loop iteration to

                    -- insure that it is still less than 50.

                    WHILE v_Counter <= 50 LOOP

                      INSERT INTO temp_table

                           VALUES (v_Counter, 'Loop index');

                      v_Counter := v_Counter + 1;

                    END LOOP;

                  END;

 

              示例2:

                  v_Counter BINARY_INTEGER;

                  BEGIN

                    -- This condition will evaluate to NULL, since v_Counter

                    -- is initialized to NULL by default.

                    WHILE v_Counter <= 50 LOOP

                      INSERT INTO temp_table

                           VALUES (v_Counter, 'Loop index');

                      v_Counter := v_Counter + 1;

                    END LOOP;

                  END;

 

         (3)数字式FOR循环语句:

              语法:

                  FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP

                     { statement | statement_block } ;

                  END LOOP ;

                  这里,loop_counter是隐式声明的索引变量。

 

              示例1:

                  FOR循环的循环索引被隐式声明为BINARY_INTEGER。在循环前面没有

                  必要声明它,如果对它进行了声明,那么循环索引将屏蔽外层的声明,

                  如下所示

                  v_Counter  NUMBER := 7;

                  BEGIN

                    -- Inserts the value 7 into temp_table.

                    INSERT INTO temp_table (num_col)

                      VALUES (v_Counter);

                    -- This loop redeclares v_Counter as a BINARY_INTEGER, which

                    -- hides the NUMBER declaration of v_Counter.

                    FOR v_Counter IN 20..30 LOOP

                      -- Inside the loop, v_Counter ranges from 20 to 30.

                      INSERT INTO temp_table (num_col)

                        VALUES (v_Counter);

                    END LOOP;

                    -- Inserts another 7 into temp_table.

                    INSERT INTO temp_table (num_col)

                      VALUES (v_Counter);

                  END;

 

              示例2:

                  如果在FOR循环中有REVERSE关键字,那么循环索引将从最大值向最

                  小值进行循环。请注意语法是相同的——仍然首先书写的是最小值,

                  如下所示

                  BEGIN

                    FOR v_Counter IN REVERSE 10..50 LOOP

                      -- v_Counter will start with 50, and will be decremented

                      -- by 1 each time through the loop.

                      NULL;

                    END LOOP;

                  END;

 

              示例3:

                  FOR循环中的最大值和最小值没有必要必须是数字型文字,它们可以

                  是能够被转换为数字值的任何表达式,如下所示

                  v_LowValue      NUMBER := 10;

                  v_HighValue    NUMBER := 40;

                  BEGIN

                    FOR v_Counter IN REVERSE v_LowValue..v_HighValue LOOP

                      INSER INTO temp_table

                          VALUES (v_Counter, ‘Dynamically sqecified loop range’);

                    END LOOP;

                  END;

 

       4> GOTO语句

          语法:

              GOTO label;

              ...

              <<label>>

              ...

 

          示例:

              v_Counter  BINARY_INTEGER := 1;

              BEGIN

                LOOP

                  INSERT INTO temp_table

                    VALUES (v_Counter, 'Loop count');

                  v_Counter := v_Counter + 1;

                  IF v_Counter > 50 THEN

                    GOTO l_EndOfLoop;

                  END IF;

                END LOOP;

             

                <<l_EndOfLoop>>

                INSERT INTO temp_table (