ORACLE PL-SQL超经典面试题南京廖华答案网

ORACLE PL-SQL超经典面试题下载本文

文章发布时间 : 2024/11/21 2:34:27星期四

北京科蓝 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.

Patient

Pat_ID Ins_ID_1 Ins_ID_2 Ins_ID_3

1 5

2 8

3 4 12

4 11

5 10 7 1

6 5 7

7 3 7 2

8 4 9 5

9 3

10 1

Insurance Ins_ID Description 1 Medicare 2 Blue Cross 3 OXFORD 4 1st Health Ins 5 United Healthcare 6 Travellers 7 Medicaid 8 Capital Healthplan 9 MVP Healthcare 10 Harvard 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_ID InsID1 Desc-1 1 5 United Healthcare 2 8 Capital Healthplan 3 4 1st Health Ins 4 11 5 10 Harvard Healthplan 6 5 United Healthcare 7 3 OXFORD 8 4 1st Health Ins 9 3 OXFORD 10 1 Medicare

InsID2 12 7 7 7 9 Desc-2 InsID3 Desc-3 Medicaid 1 Medicare Medicaid Medicaid 2 Blue Cross MVP Healthcare 5 United Healthcare 试题二：数据更新，被更新的表是A，而数据来自B表 Table A will be updated with the data from table B

A ID 1 2 3 4 5 6 Sum

ID 1 1 1 2 4 4 B Amount 100 200 320 230 246 212 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

Account Acct_No Type 10001 Savings 10002 Savings 10003 Savings 10004 Savings 10005 Savings 10002 Savings 10002 Savings 10005 Savings 60001 Checking 60002 Checking 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. Acct 字段名称描述 Acct_No 账号 Balance 余额

Table explanation: (表解释)

Acct: 包括所有的账户及其余额, All the accounts and their balance

Locking: 包括所有的被锁定的账户. 账户一旦锁定，就不能做任何交易.

Locking

字段名称描述

Acct_No 账号

Type 锁定类型

字段名称 Acct_No Amount Holding 描述账号冻结金额 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:

试题一

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.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 > 0 THEN V_ERRCODE := -20001;

V_ERRMESSAGE := '转出账户被锁';