PL/SQL Practise第10章的代码也很多错

显示全部楼层 · 2012-5-21 10:19:41

10-1
原文：
CREATE FUNCTION f_get_name (ip_emp_id in number ) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
OPEN c;
FETCH c INTO lv_ename;
CLOSE c;
RETURN lv_ename;
END;
如果emp是scott用户的，没有emp_id列，其实不能算错，改为：
CREATE or replace FUNCTION f_get_name (ip_emp_id in number ) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE empno = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
OPEN c;
FETCH c INTO lv_ename;
CLOSE c;
RETURN lv_ename;
END;

千问 · 2012-5-21 10:19:41

10-2
原文：
CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
OPEN c;
FETCH c INTO lv_ename;
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
ENDIF;
FETCH c INTO lv_ename;
IF (SQL%FOUND) THEN
RAISE TOO_MANY_ROWS;
ENDIF;
CLOSE c;
RETURN lv_ename;
END;
这个就是真的有错了，endif不能连着写
编译错误：
SQL> show err
FUNCTION F_GET_NAME 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/4 PLS-00103: 出现符号 ";"在需要下列之一时：
if
改为：
CREATE or replace FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE empno = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
OPEN c;
FETCH c INTO lv_ename;
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
FETCH c INTO lv_ename;
IF (SQL%FOUND) THEN
RAISE TOO_MANY_ROWS;
END IF;
CLOSE c;
RETURN lv_ename;
END;

千问 · 2012-5-21 10:19:41

10-3
原文
CREATE OR REPLACE PROCEDURE refresh_store_feed AS
TYPE prod_array IS TABLE OF store_products%ROWTYPEINDEX BY BINARY_INTEGER;
l_prod
prod_array;
CURSOR c IS
SELECTproduct
FROMlisted_products@some_remote_site;
BEGIN
OPEN C;
LOOP
FETCH C BULK COLLECT INTO l_prod LIMIT 100;
FOR i IN 1 .. l_csi.COUNT
LOOP
/*... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
END LOOP;
FORALL i IN 1 .. l_csi.COUNT
INSERT INTO store_products (product) VALUES (l_prod(i));
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE C;
END;
/
先创建要用到的表：
SQL> create table listed_products(product varchar(10));
表已创建。
已用时间:00: 00: 02.35
SQL> show user
USER 为 "SCOTT"
SQL> insert into listed_products select level from dual connect by level create table store_products(product varchar(10));
编译错误
SQL> show err
PROCEDURE REFRESH_STORE_FEED 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/4 PL/SQL: Statement ignored
11/18PLS-00201: 必须声明标识符 'L_CSI.COUNT'
15/19PL/SQL: Statement ignored
15/24PLS-00201: 必须声明标识符 'L_CSI.COUNT'
SQL> show err
PROCEDURE REFRESH_STORE_FEED 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
16/55PLS-00382: 表达式类型错误
需要修改2处，一个是数组名，另一个是增加数组的列
CREATE OR REPLACE PROCEDURE refresh_store_feed AS
TYPE prod_array IS TABLE OF store_products%ROWTYPEINDEX BY BINARY_INTEGER;
l_prod
prod_array;
CURSOR c IS
SELECTproduct
FROMlisted_products;
BEGIN
OPEN C;
LOOP
FETCH C BULK COLLECT INTO l_prod LIMIT 100;
FOR i IN 1 .. l_prod.COUNT
LOOP
null;/*... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
END LOOP;
FORALL i IN 1 .. l_prod.COUNT
INSERT INTO store_products (product) VALUES (l_prod(i).product);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE C;
END;
/

千问 · 2012-5-21 10:19:41

你现在翻译到第10章了啊？

千问 · 2012-5-21 10:19:41

10-4
原文：
DECLARE
prod_cursor sys_refcursor;
BEGIN
IF ( input_param = 'C' )
THEN
OPEN prod_cursor FOR
SELECT * FROM prod_concepts
WHEREconcept_type = 'COLLATERAL'

AND concept_dt< TO_DATE( '01-JAN-2003', 慏D-MON-YYYY?;
ELSE
OPEN prod_cursor FOR
SELECT * FROM prod_concepts

WHEREconcept_category = 'ADVERTISING';
END IF;
LOOP
FETCH prod_cursor BULK COLLECT INTO .... LIMIT 500;
...procedural code to process results here...
EXIT WHEN prod_cursor%NOTFOUND;
END LOOP;
CLOSE prod_cursor;
END;
创建表
create table prod_concepts(concept_type varchar(20),concept_category varchar(20),concept_dt date);
insert into prod_concepts select 'COLLATERAL','ADVERTISING',TO_DATE( '01-JAN-2002', 'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')+level from dual connect by level<=500;
补充了部分变量后没有问题
DECLARE
prod_cursor sys_refcursor;
TYPE prod_array IS TABLE OF prod_concepts%ROWTYPEINDEX BY BINARY_INTEGER;
l_prod
prod_array;
input_param varchar(10):='C';
BEGIN
IF ( input_param = 'C' )
THEN
OPEN prod_cursor FOR
SELECT * FROM prod_concepts
WHEREconcept_type = 'COLLATERAL'

AND concept_dt< TO_DATE( '01-JAN-2003', 'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN');
ELSE
OPEN prod_cursor FOR
SELECT * FROM prod_concepts

WHEREconcept_category = 'ADVERTISING';
END IF;
LOOP
FETCH prod_cursor BULK COLLECT INTO l_prod LIMIT 500;
--...procedural code to process results here...
dbms_output.put_line(l_prod.count);
EXIT WHEN prod_cursor%NOTFOUND;
END LOOP;
CLOSE prod_cursor;
END;
/

千问 · 2012-5-21 10:19:41

solomon_007 发表于 2012-2-2 15:18
你现在翻译到第10章了啊？
没有，仅仅测试代码

千问 · 2012-5-21 10:19:41

10-5
原文
CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
lv_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO lv_ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
RETURN lv_ename;
END;
这个没什么问题
CREATE or replace FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
lv_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO lv_ename FROM emp WHERE empno = f_get_name.ip_emp_id;
RETURN lv_ename;
END;

千问 · 2012-5-21 10:19:41

这东西没有必要放到论坛上啊

千问 · 2012-5-21 10:19:41

dingjun123 发表于 2012-2-2 15:59
这东西没有必要放到论坛上啊
刚好可以作为反面教材

千问 · 2012-5-21 10:19:41

10-12
PROCEDURE get_concepts (ip_inputINNUMBER,

ip_con_type INVARCHAR2,

ip_con_dt INDATE,

ip_con_catINVARCHAR2,

op_rcOUTmy_cv)
IS
BEGIN
IF ( ip_input = 1 )
THEN
OPEN op_rc FOR

'SELECT * FROM prod_concepts

WHERE concept_type= ''||ip_con_type||''

AND concept_dt< ''||ip_con_dt||''' ;
ELSE

OPEN op_rc FOR

'SELECT * FROM prod_concepts

WHEREconcept_category = ''||ip_con_cat||''' ;
END IF;
END get_concepts;
主要是缺少引号
CREATE OR REPLACE PROCEDURE get_concepts (ip_input INNUMBER,

ip_con_typeINVARCHAR2,

ip_con_dtINDATE,

ip_con_cat INVARCHAR2,

op_rc OUTmy_cv)
IS
BEGIN
IF ( ip_input = 1 )
THEN
OPEN op_rc FOR

'SELECT * FROM prod_concepts

WHERE concept_type= '''||ip_con_type||'''

AND concept_dt < '''||ip_con_dt||'''';
ELSE

OPEN op_rc FOR

'SELECT * FROM prod_concepts

WHEREconcept_category = '''||ip_con_cat||'''';
END IF;
END get_concepts;