sql - 参数化 For 循环游标中的参数错误 #/Types

Question

我试图通过将游标输入临时表并使用动态 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

标签： sqlplsqloracle10g

Answer 1

您需要为每列声明变量，因为您不能为每个学生使用 a tablename%ROWTYPE ，因为它是两个表的连接。