过程中的游标错误

Question

创建了一个过程，该过程将接收服务名称，然后打印保留表中带有服务名称的所有相关信息。

Set serveroutput ON;
Create or replace procedure Service_Report (service_name_par IN varchar2) 
As --creates a report and takes in one input
Cursor service_cursor IS Select reservation_id, event_place_id, event_type, 
reservation_status, numb_people_attend, event_start_date, event_end_date
from reservation
where service_type_id = service_id;
service_id      services.service_type_id%type;
service_info    service_cursor%rowtype;  
BEGIN
SELECT service_type_id INTO service_id from services
WHERE service_name = service_name_par;
FOR service_info IN service_cursor
LOOP
dbms_output.put_line(service_cursor.reservation_id || ‘   ‘ || service_info.event_place_id || ‘   ‘ || service_info.event_type
|| ‘   ‘ || service_info.reservation_status || ‘   ‘ || service_info.numb_people_attend || ‘   ‘ || service_info.event_start_date
|| ‘   ‘ || service_info.event_end_date);
END LOOP;
Exception 
When no_data_found then 
dbms_output.put_line(‘No services found.’); 
End;

Answer 1

两个主要问题：

• 您在DBMS_OUTPUT中使用的花哨的单引号。PUT_LINE调用(通常在您用例如 Word 编写查询并将其复制到用于访问 Oracle 数据库的客户端时发生Microsoft((注意行 #21(
变量 - 在游标
• 中使用 - 必须在游标之前声明(行 #6(

表优先(只有几列(：

SQL> create table reservation
2    (reservation_id number,
3     service_type_id number
4    );
Table created.
SQL> create table services
2    (service_type_id number,
3     service_name varchar2(20)
4    );
Table created.
SQL> insert into reservation values (1, 100);
1 row created.
SQL> insert into services values (100, 'Littlefoot');
1 row created.
SQL>

程序：

SQL> create or replace procedure service_report(
2    service_name_par in   varchar2
3  )
4  as
5    --creates a report and takes in one input
6    service_id     services.service_type_id%type;
7
8    cursor service_cursor is
9      select reservation_id
10      from reservation
11      where service_type_id = service_id;
12
13    service_info   service_cursor%rowtype;
14  begin
15    select service_type_id
16        into service_id
17        from services
18        where service_name = service_name_par;
19
20    for service_info in service_cursor loop
21      dbms_output.put_line( service_info.reservation_id || ' ' || service_id);
22    end loop;
23
24  exception
25    when no_data_found then
26      dbms_output.put_line('no services found.');
27  end;
28  /
Procedure created.

测试：

SQL> set serveroutput on
SQL> exec service_report ('Littlefoot');
1 100
PL/SQL procedure successfully completed.
SQL>