不明白的PL/SQL题目汇总

[boshu2007]

QUESTION NO: 4
Which two tasks should be created as functions instead of as procedures? (Choose two.)
A. reference host or bind variables in a PL7SQL block of code
B. tasks that compute and return multiple values to the calling environment
C. tasks that compute a value that must be returned to the calling environment
D. tasks performed in SQL that increase data independence by processing complex data analysis
within the Oracle server, rather than by retrieving the data into an application
Answer: A,B

为什么C错了呢?

[forgetsam]

答案错的呗。

[大明湖畔_帅锅]

应该是本身说法就有问题..

[boshu2007]

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database. SCOTT creates the following trigger: CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, Cannot drop object); END: SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema? A. Only HR B. SCOTT and HR C. Only SCOTT D. SCOTT, HR, and SYS Answer: A 问题：为什么SYS和SCOTT不行？

[forgetsam]

引用 3 楼 boshu2007 的回复:

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database. SCOTT creates the following trigger: CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, Cannot drop object); END: SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema? A. Only HR B. SCOTT and HR C. Only SCOTT D. SCOTT, HR, and SYS Answer: A 问题：为什么SYS和SCOTT不行？

BEFORE DROP ON hr.SCHEMA 触发这个Trigger的条件是:发生在hr的域上的DROP动作。

[boshu2007]

谢谢,终于明白了.

引用 4 楼 forgetsam 的回复:

[quote=引用 3 楼 boshu2007 的回复:] User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database. SCOTT creates the following trigger: CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, Cannot drop object); END: SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema? A. Only HR B. SCOTT and HR C. Only SCOTT D. SCOTT, HR, and SYS Answer: A 问题：为什么SYS和SCOTT不行？

BEFORE DROP ON hr.SCHEMA 触发这个Trigger的条件是:发生在hr的域上的DROP动作。 [/quote]

[boshu2007]

问题: <代码1> CREATE OR REPLACE FUNCTION job_chk(p_empno NUMBER) RETURN BOOLEAN IS v_job emp.job%TYPE; BEGIN SELECT job INTO v_job FROM emp WHERE empno=p_empno; IF v_job='Boss' THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END job_chk; <代码2> DECLARE v_job BOOLEAN; dyn_stmt VARCHAR2(200); v_comm NUMBER :=NULL; v_empno emp.empno%TYPE; BEGIN dyn_stmt:='BEGIN:v_job:=job_chk(100);END;'; EXECUTE IMMEDIATE dyn_stmt USING OUT v_job IF v_job THEN ... END IF END; 问题,运行代码1后再代码2却出现错误 What Is the reason?答案: A. The assignment in line 7 (红色标注) is not valid. 那么问题就是 dyn_stmt:='BEGIN:v_job:=job_chk(100);END;';这个过程中 assignment 即 v_job从job_chk(100)的函数回值里取得新值的语句有问题,如果是这样,那么问题具体在哪呢?原因呢?