最先答对且答案未经编辑的puber将获得奥运章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得奥运章一枚。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
http://www.itpub.net/thread-1499223-1-1.html
原始出处：
http://www.plsqlchallenge.com/
作者：Viacheslav Stepanov
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我创建了几张表并填入数据：
CREATE TABLE plch_dept (dept_id NUMBER, dept_name VARCHAR2(30))
/
ALTER TABLE plch_dept
ADD CONSTRAINT plch_dept_pk PRIMARY KEY (dept_id)
/
CREATE TABLE plch_emp
(emp_id NUMBER, emp_name VARCHAR2(30), dept_id NUMBER)
/
ALTER TABLE plch_emp
ADD CONSTRAINT plch_emp_pk PRIMARY KEY (emp_id)
/
ALTER TABLE plch_emp
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id)
REFERENCES plch_dept (dept_id)
/
BEGIN
INSERT INTO plch_dept VALUES (1, 'Sales');
INSERT INTO plch_dept VALUES (2, 'Marketing');
INSERT INTO plch_emp VALUES(1, 'John', 1);
INSERT INTO plch_emp VALUES(2, 'Jack', 1);
INSERT INTO plch_emp VALUES(3, 'Evlampiy', 2);
COMMIT;
END;
/
然后我创建了一个名为"PLCH_CALLBACK" 的过程来作为变更通知的回调过程。这个过程的实现细节不影响本题目的结果，所以没有提供。
哪些选项能够成功执行而不会引发未处理的异常？
假设本模式具备了这个题目所有操作所要求的权限。
(A)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY, 0));
OPEN v_cursor FOR
SELECT emp_name FROM plch_emp e, plch_dept d
WHERE e.emp_name = 'Jack' or d.dept_id = 2;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
(B)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY
+ DBMS_CQ_NOTIFICATION.QOS_BEST_EFFORT, 0));
OPEN v_cursor FOR
SELECT emp_name FROM plch_emp e
UNION ALL
SELECT dept_name FROM plch_dept d;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
(C)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY, 0));
OPEN v_cursor FOR
SELECT e.emp_name, d.dept_name
FROM plch_emp e, plch_dept d
WHERE e.dept_id = d.dept_id;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
(D)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY
+ DBMS_CQ_NOTIFICATION.QOS_BEST_EFFORT, 0));
OPEN v_cursor FOR
SELECT SUM(e.dept_id) FROM plch_emp e where e.dept_id = 1;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
(E)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY, 0));
OPEN v_cursor FOR
SELECT SUBSTR(e.emp_name,1,2) FROM plch_emp e;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
(F)
DECLARE
v_cursor SYS_REFCURSOR;
regidNUMBER;
BEGIN
regid :=
DBMS_CQ_NOTIFICATION.new_reg_start (
cq_notification$_reg_info ('plch_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY
+ DBMS_CQ_NOTIFICATION.QOS_BEST_EFFORT, 0));
OPEN v_cursor FOR
SELECT d.dummy
FROM dual d;
CLOSE v_cursor;
DBMS_CQ_NOTIFICATION.reg_end;
END;
/
|
