PL/SQLcookbook 第一章

一、返回记录

1）  返回所有行和列

1 select *

2   from emp

使用*效率不高,应该把所有列列出来:

select empno,ename,job,sal,mgr,hiredate,comm,deptno

          from emp

 

2）  从table返回行

在where子句中使用=, <, >, <=, >=, !, <>约束返回行，当有多个约束条件，可以使用and, or

1 select *

2   from emp

3  where deptno = 10

 

select *

         from emp

        where (     deptno = 10

                or comm is not null

                or sal <= 2000

              )

          and deptno=20

        EMPNO ENAME  JOB     MGR  HIREDATE      SAL       COMM  DEPTNO

        ----- ------ ----- -----  ----------- ----- ----------  ------

         7369 SMITH  CLERK  7902  17-DEC-1980   800                 20

         7876 ADAMS  CLERK  7788  12-JAN-1983  1100                 20

 

3）

4）指定返回列，把所需列放入select子句：

1 select ename,deptno,sal

2   from emp

 

5）别名：当你需要返回的列名更易读和理解时使用别名，其中as可以省略

1 select sal,comm

2   from emp

1 select sal as salary, comm as commission

2   from emp

        SALARY   COMMISSION

        -------  ----------

            800

           1600         300

           1250         500

           2975

           1250        1300

           2850

           2450

           3000

           5000

           1500           0

           1100

            950

           3000

           1300

6）别名可以在其它子句中被使用：

1 select *

        2   from (

        3 select sal as salary, comm as commission

        4   from emp

        5        ) x

        6  where salary < 5000

7）连接多列：

DB2, Oracle, PostgreSQL

        1 select ename||' WORKS AS A '||job as msg

        2   from emp

        3  where deptno=10

 

MySQL

        1 select concat(ename, ' WORKS AS A ',job) as msg

        2   from

        3  where deptno=10

 

SQL Server

        1 select ename + ' WORKS AS A ' + job as msg

        2   from emp

        3  where deptno=10

 

8）使用逻辑运算处理返回：

1 select ename,sal,

        2        case when sal <= 2000 then 'UNDERPAID'

        3             when sal >= 4000 then 'OVERPAID'

        4             else 'OK'

        5        end as status

        6   from emp

译者注：when 后面的条件子句和where是一样的，可以使用and,或者or，在mySql和oracle中测试：

在Oracle/PL/SQL手册中：

Compound IF Statements

--复合if条件语句

If the last name is Vargas and the salary is more than 6500:

-- last name 等于Vargas，并且the salary大于6500

Set department number to 60

. . .

IF v_ename = ’Vargas’ AND salary > 6500 THEN

v_deptno := 60;

END IF;

. . .

9）限制返回行数：

DB2

        1 select *

        2   from emp fetch first 5 rows only

 

MySQL and PostgreSQL

        1 select *

        2   from emp limit 5

 

Oracle

        1 select *

        2   from emp

        3  where rownum <= 5

 

SQL Server

        1 select top 5 *

        2   from emp

Many vendors provide clauses such as FETCH FIRST and LIMIT that let you specify the number of rows to be returned from a query. Oracle is different, in that you must make use of a function called ROWNUM that returns a number for each row returned (an increasing value starting from 1).

Here is what happens when you use ROWNUM <= 5 to return the first five rows:

1.    Oracle executes your query.

2.    Oracle fetches the first row and calls it row number 1.

3.    Have we gotten past row number 5 yet? If no, then Oracle returns the row, because it meets the criteria of being numbered less than or equal to 5. If yes, then Oracle does not return the row.

4.    Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5.    Go to step 3.

 

Using an equality condition in conjunction with ROWNUM is a bad idea. Here is what happens when you try to return, say, the fifth row using ROWNUM = 5:

1.    Oracle executes your query.

2.    Oracle fetches the first row and calls it row number 1.

3.    Have we gotten to row number 5 yet? If no, then Oracle discards the row, because it doesn't meet the criteria. If yes, then Oracle returns the row. But the answer will never be yes!

4.    Oracle fetches the next row and calls it row number 1. This is because the first row to be returned from the query must be numbered as 1.

5.    Go to step 3.

Study this process closely, and you can see why the use of ROWNUM = 5 to return the fifth row fails. You can't have a fifth row if you don't first return rows one through four!

10）返回任取的n条:

DB2

        1 select ename,job

        2   from emp

        3  order by rand() fetch first 5 rows only

 

MySQL

        1 select ename,job

        2   from emp

        3  order by rand() limit 5

 

PostgreSQL

Use the built-in RANDOM function in conjunction with LIMIT and ORDER BY:

        1 select ename,job

        2   from emp

        3  order by random() limit 5

 

Oracle

        1 select *

        2   from (

        3  select ename, job

        4    from emp

        6   order by dbms_random.value()

        7        )

        8   where rownum <= 5

 

SQL Server

        1 select top 5 ename,job

        2   from emp

        3  order by newid()

上面都是基于排序的，下面oracle例子:

 select *

           from (

         select dbms_random.value() num

            from emp

                )

           where rownum <= (5+ num) and rownum >= num

11)     null：

1 select *

        2   from emp

        3  where comm is null

12)null转化：

        select case

               when comm is null then 0

               else comm

               end

          from emp

oracle中：

1 select nvl(comm,0)

        2   from emp

12)字段字符匹配,like：

        1 select ename, job

        2   from emp

        3  where deptno in (10,20)

        4    and (ename like '%I%' or job like '%ER')