Single Row Processing
Updates
現在,我開始用PL/SQL更新數據庫數據的一行或多行.讓我們先從一個簡單的update開始.
以下例子使用NO_DATA_FOUND if then else 的一段:
..
…
begin
update emp
set sal=n_sal where empno=n_empno;
n_updated
:= sql%rowcount;
exception
when
others then raise_application_error(-20007, SQLERRM||' update record');
end;
|
在UPDATE的語句中,你必須用WHERE子句作條件,否則將會全表更新.
使用SQL來執行複雜的更新
SQL UPDATE語句是非常強大的, 它在一個語句可以更新一個或多個列. 為什麼我花了這麼多時間說明和了解SQL? 因為,PL/SQL的全部目的是控制何時執行適當的SQL語句.它並不是用表現不佳的PL/SQL語句替換SQL.
在以下例子,你可以在SQL UPDATE語句一次更新多個column.
select c1.ename,c1.job,c2.ename
mgr,c1.hiredate,c1.sal,c1.comm,c3.dname from emp c1 join emp c2 on c1.mgr=c2.empno
join dept c3 on c1.deptno=c3.deptno where c1.job='SALESMAN' order by ename;
ENAME
JOB MGR HIREDATE SAL COMM DNAME
---------- --------- ---------- ---------
---------- ---------- --------------
ALLEN
SALESMAN BLAKE 20-FEB-81 1600 300 SALES
KELLY
SALESMAN KING 22-JUL-16 1400 200 SALES
MARTIN
SALESMAN BLAKE 28-SEP-81 1250 1400 SALES
SALLY
SALESMAN BLAKE 29-JUL-16 1500 300 SALES
TURNER
SALESMAN BLAKE 08-SEP-81 1500 0 SALES
WARD
SALESMAN BLAKE 22-FEB-81 1250 500 SALES
|
update emp c1 set (c1.mgr,c1.sal,c1.deptno) =(
select c2.mgr,c1.sal+5, c3.deptno from emp c2
join emp c4 on c2.mgr=c4.empno cross join dept c3
where c4.ename = case when c1.job='SALESMAN' then
'CLARK' else 'BLAKE' end
and c3.dname = case when c1.job='SALESMAN' then
'OPERATIONS' else 'SALES' end)
where c1.job='SALESMAN';
6 rows updated.
|
select c1.ename,c1.job,c2.ename
mgr,c1.hiredate,c1.sal,c1.comm,c3.dname from emp c1 join emp c2 on
c1.mgr=c2.empno join dept c3 on c1.deptno=c3.deptno where c1.job='SALESMAN'
order by ename;
ENAME
JOB MGR HIREDATE SAL COMM DNAME
---------- --------- ---------- ---------
---------- ---------- --------------
ALLEN
SALESMAN CLARK 20-FEB-81 1605 300 OPERATIONS
KELLY
SALESMAN CLARK 22-JUL-16 1405 200 OPERATIONS
MARTIN
SALESMAN CLARK 28-SEP-81 1255 1400 OPERATIONS
SALLY
SALESMAN CLARK 29-JUL-16 1505 300 OPERATIONS
TURNER
SALESMAN CLARK 08-SEP-81 1505 0 OPERATIONS
WARD
SALESMAN CLARK 22-FEB-81 1255 500 OPERATIONS
|
以上例子用了SQL的self-join, cross join, multi-update, sub-query 和 case來更新了MGR,SAL和DEPTNO
column.
使用update語句更新多項column時,必須使用sub-query. 而MGR和deptno在EMP表中是以id顯示的,當然,我們不會記得id代表什麼.所以,我們必須用self-join,把empno連接到mgr,用cross join連接到dept 以顯示emp + dept 的各種可能性. 最後用上 2個CASE,如果是SALESMAN則MGR=CLARK否則MGR= BLAKE. 如果DNAME是SALES則DNAME=OPERATIONS否則DNAME=SALES.
Deletes
在練習中, SQL DELETE 指令比起 insert, update, select 比較少用.然而,對於完整性,讓我們來看看如何在PL/SQL中使用它.
set serveroutput on size 1000000;
declare
-- 用n_count儲存delete指令的結果
n_count number;
v_mgr emp.ename%type := 'KING';
begin
begin
delete
from emp c1 where c1.ename='ALAN' and c1.mgr=
(select
c2.empno from emp c2 where c2.ename=v_mgr);
n_count
:= sql%rowcount;
exception
when
OTHERS then raise_application_error(-20001, SQLERRM||' on delete WORKERS');
end;
sys.dbms_output.put_line(to_char(n_count)||'
record(s) delete.');
end;
/
|
最後, 讓我們來看看SELECT指令.
Selects
SQL SELECT語句在PL/SQL最常用的SQL語句,在這次練習中,將討論單SELECT語句的應該返回一行的結果.多數的PL/SQL stored
procedures你會寫一個或多個的select語句.所以你可以使用PL/SQL達成. 現在我們回到最初的select例子,如下:
..
…
begin
v_ename
:= 'SALLY';
v_job := 'SALESMAN';
v_mgr := 'BLAKE';
n_sal := 1500;
n_comm := 300;
begin
select count(1) into n_count from emp c1
cross join emp c2 where
c1.ename=v_ename and c1.job=v_job and
c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;
exception
when others then
raise_application_error(-20005, SQLERRM||' on detect duplicate');
end;
if
n_count = 0 then
begin
select empno into n_mgr from emp where
ename='BLAKE';
exception
when others then
raise_application_error(-20002, SQLERRM||' get mgr number');
end;
end;
…
…..
|
紅色的statement用來偵測有沒有出現where內的條件,用果有就count(empno)並儲存到n_count, 如果沒有n_count = 0就進行下一步. 假設如果你需要update table, 但是沒有找到內容或內容多於一個,哪怎麼處理呢?
No Data Found
如之前介紹exception中有2個常用的exception.其中一個使用最多的就是NO_DATA_FOUND.當使用SELECT語句找不到WHERE的條件時,便使用NO_DATA_FOUND的exception.例子如下:
..
…
begin
select count(1) into n_count from emp c1
cross join emp c2 where
c1.ename=v_ename and c1.job=v_job and
c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;
n_selected := sql%rowcount;
exception
when NO_DATA_FOUND then
n_selected := sql%rowcount;
sys.dbms_output.put_line('Caught raised
exception NO_DATA_FOUND.')
when others then
raise_application_error(-20002, SQLERRM||' get mgr number');
end;
|
Too Many Rows
假若SELECT內容多於一個則用TOO_MANY_ROWS, 這裡注意,由於我們初學的SELECT PL/SQL只能返回一行結果,所以多於一行是會出現錯誤的.
declare
v_ename emp.ename%type;
v_job emp.job%type;
begin
v_job :=
'MANAGER';
begin
select
ename into v_ename from emp where job=v_job;
exception
when
others then raise_application_error(-20002, SQLERRM||' select');
end;
sys.dbms_output.put_line(v_ename);
end;
/
declare
*
ERROR at line 1:
ORA-20002: ORA-01422: exact fetch
returns more than requested number of rows select
ORA-06512: at line 12
|
--
在exception加入TOO_MANY_ROWS作處理
when TOO_MANY_ROWS then
sys.dbms_output.put_line('Database found more than one record');
v_ename := NULL;
|
結果:
Database found more than one record
PL/SQL procedure successfully completed.
以下是一個糟糕的Loop方案來用single row方式顯示select語句的多行結果
declare
v_ename emp.ename%type;
n_empno emp.empno%type;
function job(
a1_num in
out emp.empno%type,
a2_job in emp.job%type)
return emp.ename%type as
v_ename emp.ename%type;
begin
select
empno, ename into a1_num,v_ename from emp where empno > a1_num and job
like a2_job and rownum=1;
return
v_ename;
exception
when
no_data_found then return v_ename;
when
others then raise_application_error(-20001, SQLERRM);
end job;
begin
n_empno :=
0;
loop
v_ename :=
job(n_empno,'MANAGER');
if v_ename is null then exit;
end if;
sys.dbms_output.put_line(v_ename);
end loop;
end;
/
|
結果:
JONES
BLAKE
CLARK
PL/SQL procedure successfully completed.
總結,目前為止,應該明白到的是,一個singleton只能返回一個行的SQL語句的結果.
