PL/SQLcookbook第一章
11642 ワード
一、返却記録
1)すべての行と列を返す
*の使用は効率的ではありません.すべての列を列挙する必要があります.
2)tableから行を返す
where句で=,<,>,<=,>=,!,<>を使用するコンストレイントは行を返し、複数のコンストレイントがある場合はand,orを使用できます.
3)
4)戻り列を指定し、必要な列をselect句に挿入します.
5)別名:返されるカラム名をより読みやすく理解する必要がある場合に別名を使用します.ここでasは省略できます.
6)別名は、他の句で使用できます.
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)論理演算処理を使用して返す:
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
12)null変換:
1)すべての行と列を返す
1 select *2 from emp*の使用は効率的ではありません.すべての列を列挙する必要があります.
select empno,ename,job,sal,mgr,hiredate,comm,deptno from emp2)tableから行を返す
where句で=,<,>,<=,>=,!,<>を使用するコンストレイントは行を返し、複数のコンストレイントがある場合はand,orを使用できます.
1 select *2 from emp3 where deptno = 10select * 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 203)
4)戻り列を指定し、必要な列をselect句に挿入します.
1 select ename,deptno,sal2 from emp5)別名:返されるカラム名をより読みやすく理解する必要がある場合に別名を使用します.ここでasは省略できます.
1 select sal,comm2 from emp1 select sal as salary, comm as commission2 from emp SALARY COMMISSION ------- ---------- 800 1600 300 1250 500 2975 1250 1300 2850 2450 3000 5000 1500 0 1100 950 3000 13006)別名は、他の句で使用できます.
1 select * 2 from ( 3 select sal as salary, comm as commission 4 from emp 5 ) x 6 where salary < 50007)複数の列を接続する:
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 empwhenの後の条件句は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 null12)null変換:
select case when comm is null then 0 else comm end from emporacle ： 1 select nvl(comm,0) 2 from emp12) ,like： 1 select ename, job 2 from emp 3 where deptno in (10,20) 4 and (ename like '%I%' or job like '%ER')