我正试图通过将光标输入临时表并使用动态sql来显示光标。
我的问题在于在for循环中调用实际的游标。我试过做一个基本的公开赛，但也不起作用。
我仔细检查了被调用的所有内容是否都具有相同的数据类型，并插入了与游标定义中相同的项数。
SQL> -- /* Given a CallNum
SQL> -- Loop through table waiting and grab all records where pCallnum = callnum
SQL> -- Assign a rank sorted by requested time */
SQL>
SQL> describe students;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SNUM NOT NULL VARCHAR2(3)
SNAME VARCHAR2(10)
STANDING NUMBER(1)
MAJOR VARCHAR2(3)
GPA NUMBER(2,1)
MAJOR_GPA NUMBER(2,1)
SQL> describe waiting;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SNUM NOT NULL VARCHAR2(3)
CALLNUM NOT NULL NUMBER(5)
REQUESTEDTIME DATE
SQL>
SQL> select * from students;
SNU SNAME STANDING MAJ GPA MAJOR_GPA
--- ---------- ---------- --- ---------- ----------
101 Andy 3 IS 2.8 3.2
102 Betty 2 3.2
103 Cindy 3 IS 2.5 3.5
104 David 2 FIN 3.3 3
105 Ellen 1 2.8
106 Frank 3 MKT 3 2
107 John 2 IS 4 2.9
108 Jacob 1 MKT 3.6 2.9
109 Heimer 3 MKT 3.1 2.9
110 Smith 3 IS 3.1 2.9
111 Jaden 2 MKT 2 1
SNU SNAME STANDING MAJ GPA MAJOR_GPA
--- ---------- ---------- --- ---------- ----------
112 Will 3 MKT 3.7 2.1
113 Ethan 2 MKT 3.5 3.1
114 Julia 1 MKT 3.8 3.5
115 Revy 1 IS 2.5 1.6
116 Goku 1 MKT 3.7 2.9
117 Ken 3 3.1
118 Natsu 2 3.1
119 Gerry 2 IS 3.1 2.9
120 Terry 2 MKT 3.1 2.9
121 Dee 3 MKT 3.1 2.9
SQL> select * from waiting;
SNU CALLNUM REQUESTED
--- ---------- ---------
113 10165 05-AUG-13
114 10165 06-AUG-13
115 10165 07-AUG-13
115 10110 07-AUG-13
SQL>
SQL> Create or replace procedure p_Get_Waiting (
2 p_CallNum number) as
3
4 CURSOR c_Get_Waiting (p_CallNum number)
5 IS
6 select
7 waiting.snum as snum,
8 sname,
9 callnum,
10 requestedtime
11 from waiting, students
12 where callnum= p_CallNum and waiting.snum = students.snum;
13 begin
14
15 -- Create temporary table
16 EXECUTE IMMEDIATE 'create table waitlistquery (
17 snum varchar2(3),
18 sname varchar2(10),
19 callnum number(5),
20 requestedtime date)';
21
22 -- For every student in cursor, insert record into temp table
23 for eachstudent in c_Get_Waiting
24 LOOP
25 insert into waitlistquery values (eachstudent.snum, eachstudent.sname, eachstudent.callnum, eachstudent.requestedtime);
26 end loop;
27
28 -- Display contents of temp table
29 EXECUTE IMMEDIATE
30 'select RANK() OVER (PARTITION BY callnum ORDER BY requestedtime) as Rank,
31 snum,
32 sname,
33 callnum,
34 to_char(requestedtime, ''mm/dd/yyyy hh12am'') as RequestedTime
35 from waitlistquery';
36
37 -- Drop temp table
38 EXECUTE IMMEDIATE 'drop table waitlistquery';
39
40 end;
41 /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL>
SQL> show err
Errors for PROCEDURE P_GET_WAITING:
LINE/COL ERROR
-------- -----------------------------------------------------------------
23/2 PL/SQL: Statement ignored
23/21 PLS-00306: wrong number or types of arguments in call to
'C_GET_WAITING'
我的目标是显示这样的结果：
Ranking SNUM SName RequestedTime
--------------------------------
1 107 John 1/1/2010 10am
2 108 Jake 1/2/2012 12pm
您需要为每列声明变量，因为您不能使用tablename%ROWTYPE因为这是两张桌子的结合。
使用带有单个语句的游标和正确的联接简化过程后，可以使用REFCURSOR绑定变量和SQL*Plus打印命令显示输出。
修改程序
CREATE OR replace PROCEDURE p_get_waiting (
p_callnum NUMBER,
c_get_waiting OUT SYS_REFCURSOR
) AS
BEGIN
OPEN c_get_waiting FOR
SELECT RANK() OVER(
PARTITION BY w.callnum
ORDER BY requestedtime
) AS rank,
s.snum,
s.sname,
w.callnum,
TO_CHAR(w.requestedtime,'mm/dd/yyyy hh12am') AS requestedtime
FROM waiting w
JOIN students s ON w.snum = s.snum
WHERE w.callnum = p_callnum;
END;
/
执行
VARIABLE get_waiting REFCURSOR
EXEC p_get_waiting ( 10165,:get_waiting)
PRINT get_waiting
结果
PL/SQL procedure successfully completed.
RANK SNUM SNAME CALLNUM REQUESTEDTIME
---------- ---------- ------ ---------- ---------------
1 113 Ethan 10165 08/05/2013 12AM
2 114 Julia 10165 08/06/2013 12AM
3 115 Revy 10165 08/07/2013 12AM
三件事：
- 打开光标时，需要传入参数，例如
for eachstudent in c_Get_Waiting(p_CallNum => p_CallNum) - 如果作为过程的一部分动态创建waitlistquery表，那么insert语句也需要是动态的，而不是当前的静态语句。(由于waitlistquery表不存在，过程编译将失败。(
execute immediate select rank()...语句不会执行任何操作，因为您没有为要存储的语句的结果指定任何变量
关于第二点和第三点，我认为这只是一个学习练习？因为如果没有，做你正在做的事情就没有意义；您可以简单地打开一个ref光标来完成这项工作。
如果我这样做的话，我会写一个返回ref游标的函数，例如
CREATE OR REPLACE FUNCTION f_get_waiting(p_callnum NUMBER) RETURN SYS_REFCURSOR IS
get_waiting_rcur SYS_REFCURSOR;
BEGIN
OPEN get_waiting_rcur FOR
SELECT rank() over(PARTITION BY callnum ORDER BY requestedtime) AS rank,
waiting.snum,
sname,
callnum,
to_char(requestedtime, 'mm/dd/yyyy hh12am') AS requestedtime
FROM waiting
INNER JOIN students
ON waiting.snum = students.snum;
RETURN get_waiting_rcur;
END f_get_waiting;
/
记住，调用此函数的代码需要在完成任务后处理光标的关闭。
这假设数据将被传递到数据库之外(例如，传递到C#调用模块、写入文件等(；如果你要使用数据来做一些事情，比如插入或更新表，如果可能的话，我会尝试在一个DML语句中完成这项工作。