成都笔试——PL/SQL准备

1. 英文邮件写作

先需要发一封邮件给客户，就一些需求的讨论确定一个会议时间，我们这边在周三下午和周五早上有空，讯问客户的空闲时间。

Dear all,

Good day!

Thank you for your last email!

We have got your general demand. And for deep understanding, we suggest to have a meeting together to discuss more details about it.

Wednesday afternoon and Friday morning this week are the available time for us. Would you please offer the feedback whether they are also convenient for you? If both not, please help offer your available time list. And we will try to book another time for this meeting and reply to you.

If any further questions, please feel free to contact us.

We are looking forward to your kind reply.

Thank you & Best regards!

Jay Cui

2. 测试题A

1) Other than Function, what other types of PL/SQL code blocks can be stored in the database?

Answers:

PROCEDURE 过程, PACKAGE 包

2) Define a LOOP statement that increments a variable “var_count” by 1 from 1 to 10.

Answers:

DECLARE  var_i INT:=1;  var_count INT:=0;BEGIN  LOOP      var_count:=var_count+var_i;      EXIT WHEN var_i=10;      var_i:=var_i+1;---步长为1  END LOOP;END;

3) Define a LOOP statement that retrieves all rows using the CURSOR defined in Question(4) above and define a VARIABLE “var_rows” that counts the number of rows retrieved.
Answers:

DECLARE  var_rows INT:=0;  var_ID Customer.ID%TYPE;---初始化  CURSOR ID_norder IS  SELECT Distinct A.ID FROM Customer A  WHERE NOT EXISTS     (SELECT 1 FROM Order_entry B      WHERE A.ID=B.ID);BEGIN  OPEN ID_norder;  LOOP      FETCH ID_norder into var_ID;      EXIT WHEN ID_norder%NOTFOUND;      ---DBMS_OUTPUT.PUT_LINE(var_ID);   END LOOP   var_rows:=ID_norder%ROWCOUNT;----行数   CLOSE ID_norder;END;

4) If you have given 2 tables: Customer table and Order entry table

Customer table contains Customer ID, customer name
Order entry table contains Customer ID, order number, Item ordered, quantity
Not all the customer has a record in Order entry table.
Please construct a SQL statement to retrieve all customers ID which does not have a record in order entry table.

Answers:

SELECT Distinct A.ID FROM Customer AWHERE NOT EXISTS     (SELECT 1 FROM Order_entry B      WHERE A.ID=B.ID);

5) There are 2 tables: Table A and Table B. Table A has 10 records. Table B has 3 records. How may records will the following SQL statement produce?

Select * from A,B

Answers: 30

3. 测试题B

1) a. How does a bulk operation improve PL/SQL performance?
b. Write a section of code that performs a BULK COLLECT which makes use of the following explicit cursor:

CURSOR c_data IS SELECT *    FROM table_data;

Answers:

a.
For one code block, PL/SQL engine is responsible to handle procedural scripts, while SQL engine handles SQL scripts and offers feedback to PL/SQL.
In default situation, the engine switch/feedback process will be handled for each record. So the time consuming and efficiency will be not desirable when the count of records is huge.
Bulk operation offers the service to set the engine switch into bundle handling. So in the ideal situation, for all records, only one feedback cycle needs to be finished. The efficiency is improved obviously.

---b.DECLARE   TYPE a_data IS TABLE OF table_data%ROWTYPE;   b_data a_data:=a_data();---初始化   CURSOR c_data IS          SELECT * FROM table_data;BEGIN    OPEN c_data;    FETCH c_data BULK COLLECT INTO b_data;    CLOSE c_data;END;

2) Assuming we have an SQL DML statement into a table column. Write a section of code that fetches all the rows of the table “tab_command” and dynamically executes the SQL statement that is stored in column “sql_cmd” from this table.
desc TAB_COMMAND
Name Type
—— ——
sql_cmd VARCHAR2(100)

Answers:

DECLARE   sql_exc tab_command.sql_cmd%TYPE;   CURSOR sql_script IS      SELECT sql_cmd FROM tab_command;BEGIN   OPEN sql_script;   LOOP     FETCH sql_script into sql_exc;     EXIT WHEN sql_script%NOTFOUND;     EXECUTE IMMEDIATE sql_exc;     COMMIT;   END LOOP;END;

3) Assuming our system uses a special date(e.g Thai year) and date conversion function that are called frequently from many other PL/SQL programs. What would be a good way to improve the performance of the date conversion functions?
Answers:

a) 标量子查询scalar subquery
b) RESULT_CACHE
c) deterministic

4) a.What is the difference between definer and invoker execution of a stored routine?
b. By default, is a routine stored in a database executed by definer rights or invoker rights?

Answers:

a.
1) To execute store routines, the privileges of definer are same. For invokers, they could have different privileges.
2) Through definer rights to execute store routines, the current user’s role is invalid. And invoker rights are what current users have.

b. Definer Rights

http://www.360doc.com/content/16/0901/11/7662927_587490185.shtml