PL/SQL CHALLENGE 每日一题

solomon_007

refer to  <<Expert PL SQL Practices>> chapter 7....

newkid

楼上贴的代码起什么作用，能不能说明一下？

2011-11-29 答案CD
A:在此选项中do_stuff被重载，所以DECLARATION 和 DEFINITION的显示行数是正确的。但是，并没有对do_stuff的调用。
B:为了使得DECLARATION 和 DEFINITION各显示两行，do_stuff必须被重载。
C:既有重载又有调用，正确。
D:同上，只是调用的地方不同。
=================================================
2011-11-30 一致读

作者：Steven Feuerstein
难度：低


我创建了如下的表并填入数据:
CREATE TABLE plch_employees
(
   employee_id   INTEGER
,  last_name     VARCHAR2 (100)
,  salary        NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Smith', 100000);

   INSERT INTO plch_employees
        VALUES (200, 'Jones', 1000000);

   COMMIT;
END;
/

哪些选项在执行后会输出下列两行文本？
Smith
Jones
   
   
(A)
BEGIN
   FOR emp_rec IN (  SELECT *
                       FROM plch_employees
                   ORDER BY employee_id)
   LOOP
      DELETE FROM plch_employees;

      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
END;

(B)
BEGIN
   FOR emp_rec IN (  SELECT *
                       FROM plch_employees
                   ORDER BY employee_id)
   LOOP
      DELETE FROM plch_employees;

      COMMIT;

      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
END;

(C)
DECLARE
   CURSOR emps_cur
   IS
        SELECT * FROM plch_employees
      ORDER BY employee_id;

   emp_rec   emps_cur%ROWTYPE;
BEGIN
   OPEN emps_cur;
   DELETE FROM plch_employees;

   LOOP
      FETCH emps_cur INTO emp_rec;
      EXIT WHEN emps_cur%NOTFOUND;

      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
   CLOSE emps_cur;
END;

(D)
DECLARE
   CURSOR emps_cur
   IS
        SELECT * FROM plch_employees
      ORDER BY employee_id;

   emp_rec   emps_cur%ROWTYPE;
BEGIN
   OPEN emps_cur;
   LOOP
      FETCH emps_cur INTO emp_rec;
      EXIT WHEN emps_cur%NOTFOUND;

      DELETE FROM plch_employees;

      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
   CLOSE emps_cur;
END;

solomon_007

ACD

gyhgood

ACD

gyhgood

我为什么要把B 去掉呢
游标只要打开，其他操作都不会影响游标中的结果。不知道会不会有特殊情况呢？

solomon_007

gyhgood 发表于 2011-12-2 09:41
我为什么要把B 去掉呢
游标只要打开，其他操作都不会影响游标中的结果。不知道会不会有特殊情况呢？ ...

哈哈，我做完后再验证，我也内牛满面

newkid

2011-11-30答案ABCD, 打开游标的时间点决定了这个游标里会看到什么数据，即使后来数据变化了，ORACLE仍然会从UNDO里找出来为你恢复数据的镜像。
===================
2011-12-01 SQL注入的防备

作者：Steven Feuerstein
难度：高

我创建如下的两张表并填入数据:
CREATE TABLE plch_names1 (name VARCHAR2 (100))
/

CREATE TABLE plch_names2 (name VARCHAR2 (100))
/

BEGIN
   INSERT INTO plch_names1 VALUES ('Paul');
   INSERT INTO plch_names1 VALUES ('Ringo');
   INSERT INTO plch_names1 VALUES ('John');
   INSERT INTO plch_names1 VALUES ('George');

   INSERT INTO plch_names2 VALUES ('Jerry');
   INSERT INTO plch_names2 VALUES ('Bob');
   INSERT INTO plch_names2 VALUES ('Phil');

   COMMIT;
END;
/

然后我创建了这个函数来返回一个游标，里面包含了指定表名的name列的数据：

CREATE OR REPLACE FUNCTION plch_get_names (table_in IN VARCHAR2)
   RETURN SYS_REFCURSOR
IS
   l_cv   SYS_REFCURSOR;
BEGIN
   OPEN l_cv FOR 'select name from ' || table_in || ' order by name';

   RETURN l_cv;
END plch_get_names;
/

这里是一个“帮手”过程来显示这个函数所返回的数据：
CREATE OR REPLACE PROCEDURE plch_show_names (table_in IN VARCHAR2)
IS
   l_cv     SYS_REFCURSOR;
   l_name   plch_names1.name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Names in ' || table_in);
   l_cv := plch_get_names (table_in);

   LOOP
      FETCH l_cv INTO l_name;

      EXIT WHEN l_cv%NOTFOUND;
      DBMS_OUTPUT.put_line (l_name);
   END LOOP;

   CLOSE l_cv;
END plch_show_names;
/

当我执行下列这个代码块，我可以看到Beatles 和 Grateful Dead乐队的成员。（即上述两张表中的数据）
BEGIN
   plch_show_names ('plch_names1');
   plch_show_names ('plch_names2');
END;
/

不幸的是，如果我这样来执行：
BEGIN
   plch_show_names (
      'plch_names2 where 1=2 union select username from all_users --');
END;
/

我看到了数据库实例中所有用户的名字，这属于安全违规（是一种SQL注入）
下面哪些plch_get_names函数会抛出异常，假如调用时传入了那个“注入”参数值，但是如果传入像plch_names1 和 plch_names2这样的“真实”表名你还可以看到表中数据？

(A)
BEGIN
   OPEN l_cv FOR
         'select name from '
      || DBMS_ASSERT.simple_sql_name (table_in)
      || ' order by name';

   RETURN l_cv;
END plch_get_names;

(B)
BEGIN
   DBMS_ASSERT.simple_sql_name (table_in);

   OPEN l_cv FOR 'select name from ' || table_in || ' order by name';

   RETURN l_cv;
END plch_get_names;

(C)
BEGIN
   OPEN l_cv FOR
         'select name from '
      || DBMS_ASSERT.qualified_sql_name (table_in)
      || ' order by name';

   RETURN l_cv;
END plch_get_names;

(D)
BEGIN
   OPEN l_cv FOR 'select name from :table_name order by name' USING table_in;

   RETURN l_cv;
END plch_get_names;

solomon_007

看了DBMS_ASSERT的说明，感觉是C

newkid

2011-12-01答案AC.

DBMS_ASSERT.SIMPLE_SQL_NAME检查一个名字是否为SQL中可用的简单名字：

  名字必须以字母开头，随后可跟随数字、字母或_, $, # 字符；
  允许带双引号,双引号之间可以是任意字符；
  假如双引号之内的名字本身就带有双引号，那么必须重复双引号两次来表示；
  输入参数如果前后带有空格被忽略。
  名字的长度没有被检测。

DBMS_ASSERT.qualified_sql_name则更宽松一些，允许带.(小数点，比如用在记录成员、PACKAGE里面的函数、SCHEMA OWNER等)和@ (用在DBLINK)

答案B: simple_sql_name是函数不是存储过程，必须将返回值赋给变量。
答案D: 表名不可以用绑定变量。

========================================
2011-12-02 带约束的SUBTYPE
哪些块在执行之后会抛出VALUE_ERROR异常？

(A)
DECLARE
   l_string10   VARCHAR2 (10);

   SUBTYPE string10_subtype IS l_string10%TYPE;

   l_string20   string10_subtype (20);
BEGIN
   l_string20 := RPAD ('abc', 15, 'def');
END;


(B)
DECLARE
   l_string10   VARCHAR2 (10);

   l_string20   l_string10%TYPE;
BEGIN
   l_string20 := RPAD ('abc', 15, 'def');
END;


(C)
DECLARE
   l_string10   VARCHAR2 (10);

   SUBTYPE string10_subtype IS l_string10%TYPE;

   l_string20   string10_subtype;
BEGIN
   l_string20 := RPAD ('abc', 15, 'def');
END;


(D)
DECLARE
   l_string10   VARCHAR2 (10);

   SUBTYPE string20_subtype IS l_string10%TYPE (20);

   l_string20   string20_subtype;
BEGIN
   l_string20 := RPAD ('abc', 15, 'def');
END;


(E)
DECLARE
   l_string10   VARCHAR2 (10);

   l_string20   l_string10%TYPE (20);
BEGIN
   l_string20 := RPAD ('abc', 15, 'def');
END;

solomon_007

猜的BC