oracle常见sql面试题,ORACLEPLSQL超经典面试题

oracle常见sql⾯试题,ORACLEPLSQL超经典⾯试题《ORACLEPLSQL超经典⾯试题》由会员分享，可在线阅读，更多相关《ORACLEPLSQL超经典⾯试题(7页珍藏版)》请在⼈⼈⽂库⽹上
搜索。

1、北京科蓝 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。

2、_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的⽅式来产⽣如下的报表。

3、，每个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 Ins124。

4、11510Harvard Healthplan7Medicaid1Medicare65United Healthcare7Medicaid73OXFORD7Medicaid2Blue Cross841st
Health Ins9MVP Healthcare5United Healthcare93OXFORD101Medicare试题⼆： 数据更新，被更新的表是A， ⽽数据来⾃B表
Table A will be updated with the data from table BABIDSumIDAmount111002120031320422305424664212Table A and
Ta。

5、ble 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 。

6、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_NoType10001Savings10002。

7、
Savings10003Savings10004Savings10005Savings10002Savings10002Savings10005Savings60001Checking60002CheckingTh 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次，100。

8、05重复了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。

9、 (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 requir。

10、ement.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. A。

11、nd 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 accoun。

12、t 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 。

13、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。

14、 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 VALUE。

15、S(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。

16、, 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);IN。

17、SERT 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 TA。

18、BLE 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(。

19、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。

20、, 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(600。

21、02, 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 。

22、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。

23、.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) amountfrom Bwhere a.id = b.idgroup 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.。

24、RowID);注：按照存在或不存在的思路还有其他⽅法可以实现试题四CREATE OR REPLACE PROCEDURE
TRANSFER_PRO(A_FROMACCOUNT IN NUMBER,A_TOACCOUNT IN NUMBER,A_AMOUNT IN NUMBER )/*功能:从某⼀账户转出⼀定的⾦额到另⼀个账户作者:黄兴超⽇期：2010-11-1源表: ACCT⽬标表:ACCT*/ISV_COUNT INTEGER;V_ERRCODE NUMBER;V_ERRMESSAGE VARCHAR2(1000);BEGIN -1. 确定转出账户没有被锁。

SELECT COUNT(*) I。

25、NTO V_COUNT FROM LOCKING L WHERE L.ACCT_NO = A_FROMACCOUNT;IF V_COUNT 0 THENV_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 THENV_ERRCODE := -20002;V。

26、_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 THENV_ERRCODE := -20003;V_ERRMESSAGE := 转⼊账户被锁;RAISE_APPLICATION_ERROR(V_ERRCODE,
V_ERRMESSAGE);END IF;-4.确定转出账户中有⾜够余额满⾜。

27、转账SELECT A.BALANCE - A_AMOUNT INTO V_COUNT FROM ACCT A WHERE A.ACCT_NO = A_FROMACCOUNT;IF
V_COUNT 0 THENV_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。

28、, HOLDING H WHERE A.ACCT_NO = A_FROMACCOUNTAND A.ACCT_NO = H.ACCT_NO(+);IF V_COUNT 0
THENV_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_FROMAC。

29、COUNT;EXCEPTION WHEN OTHERS THENV_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;EXCEPTIONWHEN OTHERS THENV_ERRCODE := -20007;V_ERRMESSAGE := 账户转⼊时交易失败;RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);END; COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK;RAISE_APPLICATION_ERROR(V_ERRCODE, V_ERRMESSAGE);END。