ORACLE PL-SQL超经典面试题
北京科蓝PL/SQL 编程摸底考试（二）
日期(date)：2010年11月1日, (Nov. 1, 2010)
考生姓名(Last Name, First Name)：黄兴超
试题一：在报表中增加描述。
Exam 1. Add description for insurance IDs on the report
在Patient 表中存有病人的ID，和他所购买的保险的ID（代号）
在Insurance 表中存有保险的ID（代号）及其描述（description）
Patient table: Patient ID and Insurance ID (Ins_ID_1, Ins_ID_2, Ins_ID_3)
用Join的方式来产生如下的报表，每个Ins ID后面加上相应的描述。如InsID不能在Insurance 表中发现，即用空格表示：（如ID 11 和12）
试题二：数据更新，被更新的表是A，而数据来自B表
Table A will be updated with the data from table B
Table A and Table B are linked by ID. ID is a primary key of Table A and foreign key of Table B.
Please summrize the amount column in Table B by ID, then update the Sum column in Table A by ID.
Please do not use cursor or sub-query, use Join
Hint: create a view to join those 2 tables, then update the sum column in the view. The table behind the view will be automatically updated.
表A和表B是用ID来关联的。ID是A的主键，B的外键
请把B中的Amount按ID累加起来，然后放到A的Sum字段里。
请不要用Cursor，也不用Sub-Query，用Join来做
提示：
创建一个视图把2个table Join 起来，然后用Update 来更新视图中的Sum字段，这样视图背后的Table也自动被更新了。
试题三：删去重复的记录
Exam 3. Remove redundant rows
There are some duplicated records in this table. Please find all the duplicated rows and delete the redundant rows
For example: 10002 need to be removed 2 rows and 10005 1 row.
在上述表中，10002重复了3次，10005重复了2次。
请找出所有的重复记录，并把多余的记录删去。
例如：10002需要删去2条，10005需要删去1条。
试题四：Transation Processing
任务：从某一账户转出一定的金额到另一个账户。
Task: Transfer money from one account to another
步骤：(steps)
1.确定转出账户没有被锁。Confirm the transfer from account (debiting account) was not locked
2.确定转入账户的存在。Confirm the existence of transfer to account (crediting account)
3.确定转入账户没有被锁。Confirm the transfer to account (crediting account) was not locked.
4.确定转出账户中有足够余额满足转账Confirm the debiting account balance can satisfy the transferring
5.确定转出账户在转账后满足冻结额的要求Confirm after trasferring, the debiting account balance can satisfy the holding
amount requirement.
6.确定在转出账户上成功地扣去了转账的金额Confirm the debiting account was successfully debited
7.确定在转入账户上成功地加上了转账的金额Confirm the crediting account was sucessfully credited
上述的任何一个步骤失败，都会导致Transaction失败。用户应该得到一个很清晰的Message，指出在哪个步骤出了问题，而不是简单地给一个Message说：“交易失败”。
Any of steps fails will cause the transaction falure. And user will get a very clear message indicating which step causes the failure.
Table explanation: (表解释)
Acct: 包括所有的账户及其余额, All the accounts and their balance
Locking: 包括所有的被锁定的账户. 账户一旦锁定，就不能做任何交易.
A list of account number which was locked. Once account locked, no transaction can be performed
Holding: 被冻结的账户及其冻结的金额。被冻结的账户仍可做交易，但要保证交易后的余额大于冻结金额.
A list of account number which was held and their holding amount. Those account is still able to do the transaction, but
need tomake sure, the balance after transaction should be greater than required holding amount.
Create a Stored Procedure：
Input parameters:
转出账户的账号Account number of transfer from (debiting account number)
转入账户的账号，Account number of transfer to (crediting account number)
转账金额Amount of transferring.
Return: A Message,
告诉用户交易成功或失败，若失败告诉用户失败在哪个环节。
Return a message to user, if transaction fails, user need to know which step causes the failure.
建表脚本：Scripts for creating tables and insert data:
试题一
CREA TE TABLE patient (pat_id NUMBER, ins_id_1 NUMBER, ins_id_2 NUMBER, ins_id_3 NUMBER); INSERT INTO patient V ALUES(1, 5, NULL, NULL);
INSERT INTO patient V ALUES(2, 8, NULL, NULL);
INSERT INTO patient V ALUES(3, 4, 12, NULL);
INSERT INTO patient V ALUES(4, 11, NULL, NULL);
INSERT INTO patient V ALUES(5, 10, 7, 1);
INSERT INTO patient V ALUES(6, 5, 7, NULL);
INSERT INTO patient V ALUES(7, 3, 7, 2);
INSERT INTO patient V ALUES(8, 4, 9, 5);
INSERT INTO patient V ALUES(9, 3, NULL, NULL);
INSERT INTO patient V ALUES(10, 1, NULL, NULL);
CREA TE TABLE insurance (ins_id NUMBER, description V ARCHAR2(20));
INSERT INTO insurance V ALUES(1, 'Medicare');
INSERT INTO insurance V ALUES(2, 'Blue Cross');
INSERT INTO insurance V ALUES(3, 'OXFORD');
INSERT INTO insurance V ALUES(4, '1st Health Ins');
INSERT INTO insurance V ALUES(5, 'United Healthcare');
INSERT INTO insurance V ALUES(6, 'Travellers');
INSERT INTO insurance V ALUES(7, 'Medicaid');
INSERT INTO insurance V ALUES(8, 'Capital Healthplan');
INSERT INTO insurance V ALUES(9, 'MVP Healthcare');
INSERT INTO insurance V ALUES(10, 'Harvard Healthplan');
试题二
CREA TE TABLE a(id NUMBER, sum NUMBER);
INSERT INTO a V ALUES(1, NULL);
INSERT INTO a V ALUES(2, NULL);
INSERT INTO a V ALUES(3, NULL);
INSERT INTO a V ALUES(4, NULL);
INSERT INTO a V ALUES(5, NULL);
INSERT INTO a V ALUES(6, NULL);
CREA TE TABLE b(id NUMBER, amount NUMBER);
INSERT INTO b V ALUES(1, 100);
INSERT INTO b V ALUES(1, 200);
INSERT INTO b V ALUES(1, 320);
INSERT INTO b V ALUES(2, 230);
INSERT INTO b V ALUES(4, 246);
INSERT INTO b V ALUES(4, 212);
试题三
CREA TE TABLE account(acct_no NUMBER, type V ARCHAR2(10));
INSERT INTO account V ALUES(10001, 'Savings');
INSERT INTO account V ALUES(10002, 'Savings');
INSERT INTO account V ALUES(10003, 'Savings');
INSERT INTO account V ALUES(10004, 'Savings');
INSERT INTO account V ALUES(10005, 'Savings');
INSERT INTO account V ALUES(10002, 'Savings');
INSERT INTO account V ALUES(10002, 'Savings');
INSERT INTO account V ALUES(10005, 'Savings');
INSERT INTO account V ALUES(60001, 'Checking');
INSERT INTO account V ALUES(60002, 'Checking');
试题四
CREA TE TABLE acct(acct_no NUMBER, balance NUMBER);
CREA TE TABLE locking(acct_no NUMBER, type V ARCHAR2(8));
CREA TE TABLE holding(acct_no NUMBER, amount NUMBER);
INSERT INTO acct V ALUES(10001, 2000);
INSERT INTO acct V ALUES(10002, 500);
INSERT INTO acct V ALUES(10003, 1500);
INSERT INTO acct V ALUES(10004, 300);
INSERT INTO locking V ALUES(10004, 'locked');
INSERT INTO holding V ALUES(10001, 1000);
INSERT INTO holding V ALUES(10003, 800);
答案
试题一
select p.pat_id,p.ins_id_1,I1.Description,P.INS_ID_2,I2.Description,P.INS_ID_3,I3.Description from Patient p,Insurance I1, Insurance I2,Insurance I3
where P.INS_ID_1 = i1.ins_id(+)
and P.INS_ID_2= i2.ins_id(+)
and p.ins_id_3 = i3.ins_id(+)
order by p.pat_id;
试题二
update a set a.sum =
(
select sum(b.amount) amount
from B
where a.id = b.id
group by B.Id
);
没用视图O(∩_∩)O~
试题三
delete Account a where exists ( select * from Account b where a.acct_no = b.acct_no and a.RowID > b.RowID);
注：按照存在或不存在的思路还有其他方法可以实现
试题四
CREATE OR REPLACE PROCEDURE TRANSFER_PRO
(
A_FROMACCOUNT IN NUMBER,
A_TOACCOUNT IN NUMBER,
A_AMOUNT IN NUMBER
)
/***********************************************************************
功能:从某一账户转出一定的金额到另一个账户
作者:黄兴超
日期：2010-11-1
源表: ACCT
目标表:ACCT
************************************************************************/
IS
V_COUNT INTEGER;
V_ERRCODE NUMBER;
V_ERRMESSAGE VARCHAR2(1000);
BEGIN
--1. 确定转出账户没有被锁。
SELECT COUNT(*) INTO V_COUNT FROM LOCKING L WHERE L.ACCT_NO = A_FROMACCOUNT;
IF V_COUNT > 0THEN
V_ERRCODE := -20001;
V_ERRMESSAGE := '转出账户被锁';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END IF;
--2. 确定转入账户的存在。
SELECT COUNT(*) INTO V_COUNT FROM ACCT A WHERE A.ACCT_NO = A_TOACCOUNT;
IF V_COUNT = 0THEN
V_ERRCODE := -20002;
V_ERRMESSAGE := '转入账户不存在';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END IF;
--3. 确定转入账户没有被锁。
SELECT COUNT(*) INTO V_COUNT FROM LOCKING L WHERE L.ACCT_NO = A_TOACCOUNT;
IF V_COUNT > 0THEN
V_ERRCODE := -20003;
V_ERRMESSAGE := '转入账户被锁';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END IF;
--4. 确定转出账户中有足够余额满足转账
SELECT A.BALANCE - A_AMOUNT INTO V_COUNT FROM ACCT A WHERE A.ACCT_NO = A_FROMACCOUNT;
IF V_COUNT < 0THEN
V_ERRCODE := -20004;
V_ERRMESSAGE := '转出账户中没有足够的余额';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END IF;
--5. 确定转出账户在转账后满足冻结额的要求
SELECT A.BALANCE - A_AMOUNT - NVL(H.AMOUNT,0) INTO V_COUNT
FROM ACCT A, HOLDING H
WHERE A.ACCT_NO = A_FROMACCOUNT
AND A.ACCT_NO = H.ACCT_NO(+);
IF V_COUNT < 0THEN
V_ERRCODE := -20005;
V_ERRMESSAGE := '转账后不满足冻结额的要求';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END IF;
--6. 确定在转出账户上成功地扣去了转账的金额
BEGIN
UPDATE ACCT A SET A.BALANCE = A.BALANCE - A_AMOUNT WHERE A.ACCT_NO = A_FROMACCOUNT;
EXCEPTION
WHEN OTHERS THEN
V_ERRCODE := -20006;
V_ERRMESSAGE := '账户转出时交易失败';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END;
--7. 确定在转入账户上成功地加上了转账的金额
BEGIN
UPDATE ACCT A SET A.BALANCE = A.BALANCE + A_AMOUNT WHERE A.ACCT_NO = A_TOACCOUNT;
EXCEPTION
WHEN OTHERS THEN
V_ERRCODE := -20007;
V_ERRMESSAGE := '账户转入时交易失败';
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);
END;