ORACLEPLSQL超经典面试题.doc

北京科蓝 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)Insurance table: Insurance ID and their description.PatientInsurancePat_IDIns_ID_1Ins_ID_2Ins_ID_3Ins_IDDescription151Medicare282Blue Cross34123OXFORD41141st Health Ins510715United Healthcare6576Travellers73727Medicaid84958Capital Healthplan939MVP Healthcare10110Harvard Healthplan用Join的方式来产生如下的报表，每个Ins ID后面加上相应的描述。如InsID不能在Insurance 表中发现，即用空格表示：（如ID 11 和12）Use “Join” to generate a report, each insurance ID followed by its description, if the insurance ID not in insurance table , leave blank. Pat_IDInsID1Desc-1InsID2Desc-2InsID3Desc-315United Healthcare28Capital Healthplan341st Health Ins12411510Harvard Healthplan7Medicaid1Medicare65United Healthcare7Medicaid73OXFORD7Medicaid2Blue Cross841st Health Ins9MVP Healthcare5United Healthcare93OXFORD101Medicare试题二： 数据更新，被更新的表是A， 而数据来自B表Table A will be updated with the data from table BABIDSumIDAmount111002120031320422305424664212Table 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 JoinHint: 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 rowsAccountAcct_NoType10001Savings10002Savings10003Savings10004Savings10005Savings10002Savings10002Savings10005Savings60001Checking60002CheckingThere are some duplicated records in this table. Please find all the duplicated rows and delete the redundant rowsFor 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 locked2. 确定转入账户的存在。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 transferring5. 确定转出账户在转账后满足冻结额的要求Confirm after trasferring, the debiting account balance can satisfy the holding amount requirement.6. 确定在转出账户上成功地扣去了转账的金额Confirm the debiting account was successfully debited7. 确定在转入账户上成功地加上了转账的金额 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.AcctLockingHolding字段名称描述字段名称描述字段名称描述Acct_No账号Acct_No账号Acct_No账号Balance余额Type锁定类型Amount冻结金额Table explanation: (表解释)Acct: 包括所有的账户及其余额, All the accounts and their balanceLocking: 包括所有的被锁定的账户. 账户一旦锁定，就不能做任何交易. A list of account number which was locked. Once account locked, no transaction can be performedHolding: 被冻结的账户及其冻结的金额。被冻结的账户仍可做交易，但要保证交易后的余额大于冻结金额. 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:试题一CREATE TABLE patient (pat_id NUMBER, ins_id_1 NUMBER, ins_id_2 NUMBER, ins_id_3 NUMBER);INSERT INTO patient VALUES(1, 5, NULL, NULL);INSERT INTO patient VALUES(2, 8, NULL, NULL);INSERT INTO patient VALUES(3, 4, 12, NULL);INSERT INTO patient VALUES(4, 11, NULL, NULL);INSERT INTO patient VALUES(5, 10, 7, 1);INSERT INTO patient VALUES(6, 5, 7, NULL);INSERT INTO patient VALUES(7, 3, 7, 2);INSERT INTO patient VALUES(8, 4, 9, 5);INSERT INTO patient VALUES(9, 3, NULL, NULL);INSERT INTO patient VALUES(10, 1, NULL, NULL);CREATE TABLE insurance (ins_id NUMBER, description VARCHAR2(20);INSERT INTO insurance VALUES(1, 'Medicare');INSERT INTO insurance VALUES(2, 'Blue Cross');INSERT INTO insurance VALUES(3, 'OXFORD');INSERT INTO insurance VALUES(4, '1st Health Ins');INSERT INTO insurance VALUES(5, 'United Healthcare');INSERT INTO insurance VALUES(6, 'Travellers');INSERT INTO insurance VALUES(7, 'Medicaid');INSERT INTO insurance VALUES(8, 'Capital Healthplan');INSERT INTO insurance VALUES(9, 'MVP Healthcare');INSERT INTO insurance VALUES(10, 'Harvard Healthplan');试题二CREATE TABLE a(id NUMBER, sum NUMBER);INSERT INTO a VALUES(1, NULL);INSERT INTO a VALUES(2, NULL);INSERT INTO a VALUES(3, NULL);INSERT INTO a VALUES(4, NULL);INSERT INTO a VALUES(5, NULL);INSERT INTO a VALUES(6, NULL);CREATE TABLE b(id NUMBER, amount NUMBER);INSERT INTO b VALUES(1, 100);INSERT INTO b VALUES(1, 200);INSERT INTO b VALUES(1, 320);INSERT INTO b VALUES(2, 230);INSERT INTO b VALUES(4, 246);INSERT INTO b VALUES(4, 212);试题三CREATE TABLE account(acct_no NUMBER, type VARCHAR2(10);INSERT INTO account VALUES(10001, 'Savings');INSERT INTO account VALUES(10002, 'Savings');INSERT INTO account VALUES(10003, 'Savings');INSERT INTO account VALUES(10004, 'Savings');INSERT INTO account VALUES(10005, 'Savings');INSERT INTO account VALUES(10002, 'Savings');INSERT INTO account VALUES(10002, 'Savings');INSERT INTO account VALUES(10005, 'Savings');INSERT INTO account VALUES(60001, 'Checking');INSERT INTO account VALUES(60002, 'Checking');试题四CREATE TABLE acct(acct_no NUMBER, balance NUMBER);CREATE TABLE locking(acct_no NUMBER, type VARCHAR2(8);CREATE TABLE holding(acct_no NUMBER, amount NUMBER);INSERT INTO acct VALUES(10001, 2000);INSERT INTO acct VALUES(10002, 500);INSERT INTO acct VALUES(10003, 1500);INSERT INTO acct VALUES(10004, 300);INSERT INTO locking VALUES(10004, 'locked');INSERT INTO holding VALUES(10001, 1000);INSERT INTO holding VALUES(10003, 800);答案试题一select p.pat_id,p.ins_id_1,I1.Description,P.INS_ID_2,I2.Description,P.INS_ID_3,I3.Descriptionfrom 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 > 0 THEN 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 = 0 THEN 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 > 0 THEN 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 < 0 THEN 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 < 0 THEN 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;