Orale游标sql

我声明并使用记录，动态类型和游标来计算卖方的表现 .

这是我的代码：

DECLARE

    TYPE performance_type IS RECORD
                        (person_code    person.person_code%TYPE,
    person_name     person.last_name%TYPE,
    current_sales    NUMBER (8, 2),
    perform_percent NUMBER (8,1),
    status      varchar2(30) ) ;
    one_perform performance_type;
    CURSOR person_cur IS
    SELECT * FROM persoon;

CREATE OR REPLACE PROCEDURE current_performance
(a_person          persoon%ROWTYPE,
 a_perform OUT performance_type)
BEGIN
    CURSOR history_cur (pers varchar2) IS
        SELECT AVG(tab2.product_price * tab1.quantity)  avg_order
        FROM puurchase_archive  tab1,    prooduct  tab2
        WHERE tab1.product_name = tab2.product_name
        GROUP BY tab1.salesperson
        HAVING tab1.salesperson = pers;
    hist_rec            history_cur%ROWTYPE;
    current_avg_sales NUMBER(8,2) := 0;

    a_perform.person_code  := a_person.person_code;
    a_perform.person_name := a_person.last_name;
    a_perform.status := NULL;
    BEGIN
    SELECT SUM(tbl2.product_price * tbl1.quantity),
        AVG tbl2.product_price * tbl1.quantity)
        INTO    a_perform.current_sales,
        current_avg_sales
        FROM puurchase  tbl1,  prooduct  tbl2
        WHERE tbl1.product_name = tbl2.product_name
        GROUP BY tbl1.salesperson
        HAVING tbl1.salesperson = a_person.person_code;
        EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            a_perform.status := 'Current purchases exception';
            a_perform.current_sales := 0;
    END;
    OPEN history_cur (a_person.person_code);
    FETCH history_cur INTO hist_rec;
    IF (history_cur%NOTFOUND) THEN 
        a_perform.perform_percent := 0;
    IF (a_perform.status IS NULL) THEN
        a_perform.status := 'Erroneous or no history';
    END IF;
    ELSE
        a_perform.perform_percent := 100 * (current_avg_sales - hist_rec.avg_order)/
        hist_rec.avg_order;
        a_perform.status := 'All fine';
    END IF;
    CLOSE history_cur;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        a_perform.status := 'Exceptions found';
END current_performance;

BEGIN
FOR person_rec IN person_cur
LOOP
current_performance(person_rec, one_perform);
dbms_output.put_line(one_perform.person_code || ' ' ||
one_perform.person_name     || ' ' || 
one_perform.current_sales     ||  ' ' ||
one_perform.perform_percent || ' ' ||
one_perform.status);
END LOOP;
END;

此代码不适用于declare . 错误：遇到以下其中一项时遇到符号“CREATE”：begin function package pragma procedure子类型使用form current cursor .

如果没有DECLARE，我会收到警告：[1]：语句在0,62秒内处理并发出警告[1] :(警告）PLS-00103：遇到以下其中一项时遇到符号“BEGIN”：使用外部确定性parallel_enable流水线将authid作为簇顺序使用符号“是在”BEGIN“之前插入以继续 . [1] :(警告）PLS-00103：当遇到以下之一时遇到符号” . “：常量异常table LONG_ double ref char time timestamp interval date二进制国家字符nchar符号“”代替“ . ”继续 . [1] :(警告）PLS-00103：遇到以下其中一项时遇到符号“ . ”：常量异常表LONG_ double ref char时间时间间隔日期二进制国家字符nchar符号“”代替“ . ”继续 . [1] :(警告）PLS-00103：遇到符号“ . ”时遇到其中一个以下：常量异常表LONG_ double ref char time timestamp interval date二进制国家字符nchar符号“”代替“ . ”继续 . [1] :(警告）PLS-00103：遇到符号“TBL2”时遇到一个以下内容:(符号“（”取代“TBL2”继续 . [1] :(警告）PLS-00103：遇到符号“OPEN”[1] :(警告）PLS-00103：遇到以下其中一项时遇到符号“FETCH”：begin function package pragma procedure subtype type use form当前游标用符号“begin”代替“FETCH”继续 .

我的错是什么？

我的数据库：

CREATE TABLE person (    
person_code VARCHAR2(3) PRIMARY KEY,    
first_name VARCHAR2(15),    
last_name VARCHAR2(20),    
hire_date DATE    
);
CREATE INDEX person_name_index    
ON person(last_name, first_name);    
ALTER TABLE person    
ADD CONSTRAINT person_unique UNIQUE (    
first_name,    
last_name,    
hire_date    
);    
INSERT INTO person VALUES    
('CA', 'Charlene', 'Atlas', '01-ФЕВ-02');
INSERT INTO person VALUES    
('GA', 'Gary', 'Andersen', '15-ФЕВ-02' );
INSERT INTO person VALUES    
('BB', 'Bobby', 'Barkenhagen', '28-ФЕВ-02');    
INSERT INTO person VALUES    
('LB', 'Laren', 'Baxter', '01-МАР-02 ');    
INSERT INTO person VALUES    
('LN', 'Linda', 'Norton', '01-ИЮН-03'); 

CREATE TABLE product (
product_name VARCHAR2(25) PRIMARY KEY,
product_price NUMBER(4,2),
quantity_on_hand NUMBER(5,0),
last_stock_date DATE
);
ALTER TABLE product ADD (
CONSTRAINT positive_quantity CHECK(
quantity_on_hand IS NOT NULL
AND
quantity_on_hand >= 0)
);
INSERT INTO product VALUES 
('Small Widget', 99, 1, '15-ЯНВ-03' );
INSERT INTO product VALUES
( 'Medium Wodget', 75, 1000, '15-ЯНВ-02' );
INSERT INTO product VALUES
('Chrome Phoobar', 50, 100, '15-ЯНВ-03' );
INSERT INTO product VALUES
('Round Chrome Snaphoo', 25, 10000, null);
INSERT INTO product VALUES
('Extra Huge Mega Phoobar +', 9.95, 1234, '15-ЯНВ-04' );
INSERT INTO product VALUES ('Square Zinculator',
45, 1, TO_DATE ('Декабрь 31, 2002, 11:30',
'Month dd, YYYY, HH:MI'));
INSERT INTO product VALUES (
'Anodized Framifier', 49, 5, NULL);
INSERT INTO product VALUES (
'Red Snaphoo', 1.95, 10, '31-ДЕК-01');
INSERT INTO product VALUES (
'Blue Snaphoo', 1.95, 10, '30-ДЕК-01'); 

CREATE TABLE purchase (
product_name VARCHAR2(25),
salesperson VARCHAR2(3),
purchase_date DATE,
quantity NUMBER(4,2)
);
ALTER TABLE purchase
ADD PRIMARY KEY (product_name,
salesperson,
purchase_date
);
ALTER TABLE purchase ADD (
CONSTRAINT reasonable_date CHECK(
purchase_date IS NOT NULL
AND
TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2000-06-30')
);
ALTER TABLE purchase
ADD CONSTRAINT purchase_fk_product FOREIGN KEY
(product_name) REFERENCES product;
ALTER TABLE purchase
ADD CONSTRAINT purchase_fk_person FOREIGN KEY
(salesperson) REFERENCES person;
CREATE INDEX purchase_product
ON purchase(product_name);
CREATE INDEX purchase_salesperson
on purchase(salesperson);
INSERT INTO purchase VALUES
('Small Widget', 'CA', '14-ИЮЛ-03', 1);
INSERT INTO purchase VALUES
('Medium Wodget', 'BB', '14-ИЮЛ-03', 75);
INSERT INTO purchase VALUES
('Chrome Phoobar', 'GA', '14-ИЮЛ-03', 2);
INSERT INTO purchase VALUES
('Small Widget', 'GA', '15-ИЮЛ-03', 8);
INSERT INTO purchase VALUES
('Medium Wodget', 'LB', '15-ИЮЛ-03', 20);
INSERT INTO purchase VALUES
('Round Chrome Snaphoo', 'CA', '16-ИЮЛ-03', 5);
INSERT INTO purchase VALUES
('Small Widget', 'CA', '17-ИЮЛ-03', 1); 
CREATE TABLE purchase_archive (
product_name VARCHAR2 (25),
salesperson VARCHAR2(3),
purchase_date DATE,
quantity NUMBER (4, 2)
);
INSERT INTO purchase_archive VALUES
('Round Snaphoo', 'BB', '21-ИЮН-01', 10);
INSERT INTO purchase_archive VALUES
('Large Harflinger ' , 'GA', '22-ИЮН-01', 50);
INSERT INTO purchase_archive VALUES
('Medium Wodget', 'LB', '23-ИЮН-01', 20);
INSERT INTO purchase_archive VALUES
('Small Widget', 'ZZ', '24-ИЮН-02', 80);
INSERT INTO purchase_archive VALUES
('Chrome Phoobar', 'CA', '25-ИЮН-02', 2);
INSERT INTO purchase_archive VALUES
('Small Widget', 'JT', '26-ИЮН-02', 50);