存储过程如下:
DECLARE
COLCD2 VARCHAR2(10 BYTE) :='';
TDATE2 NUMBER(8) :=0;
BLKNO1 VARCHAR2(10 BYTE) :='';
FACID1 VARCHAR2(1 BYTE) :='A';
REVNM1 VARCHAR2(20 BYTE) :='';
PRJNO1 VARCHAR2(10 BYTE) :='';
BEGIN
SELECT COLCD INTO COLCD2
FROM EVTHDR WHERE FACID='A' AND EVTCD='SP';
REVNM1 :='2012-1Q-00';
PRJNO1:='H1041';
BLKNO1:='A11C';
execute immediate 'SELECT '||COLCD2||' INTO TDATE2 FROM PLNSCH WHERE FACID=''A'' AND REVNM='||REVNM1||' AND PRJNO='||PRJNO1||' AND BLKNO='||BLKNO1 ;
END;报错信息:ORA-00933 SQL command not properly endedCause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order. Action: Correct the syntax by removing the inappropriate clauses. It may be 就是:
execute immediate 后面的语句有问题,可是我看了,没有问题啊。
谁帮忙看看!
谢谢!
SELECT COLCD INTO COLCD2
FROM EVTHDR WHERE FACID='A' AND EVTCD='SP';这个SQL语句执行后得到一个字段的名字为‘ SPSPD' ;
表PLNSCH 的结构如下:
SELECT FACID, REVNM,PRJNO,BLKNO,SPSPD,SPFPD FROM PLNSCH
FACID VARCHAR2(2 BYTE) NOT NULL,
REVNM VARCHAR2(20 BYTE) NOT NULL,
PRJNO VARCHAR2(10 BYTE) NOT NULL,
BLKNO VARCHAR2(10 BYTE) NOT NULL,
SPSPD NUMBER(8),
SPFPD NUMBER(8)
DECLARE
COLCD2 VARCHAR2(10 BYTE) :='';
TDATE2 NUMBER(8) :=0;
BLKNO1 VARCHAR2(10 BYTE) :='';
FACID1 VARCHAR2(1 BYTE) :='A';
REVNM1 VARCHAR2(20 BYTE) :='';
PRJNO1 VARCHAR2(10 BYTE) :='';
BEGIN
SELECT COLCD INTO COLCD2
FROM EVTHDR WHERE FACID='A' AND EVTCD='SP';
REVNM1 :='2012-1Q-00';
PRJNO1:='H1041';
BLKNO1:='A11C';
execute immediate 'SELECT '||COLCD2||' INTO TDATE2 FROM PLNSCH WHERE FACID=''A'' AND REVNM='||REVNM1||' AND PRJNO='||PRJNO1||' AND BLKNO='||BLKNO1 ;
END;报错信息:ORA-00933 SQL command not properly endedCause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order. Action: Correct the syntax by removing the inappropriate clauses. It may be 就是:
execute immediate 后面的语句有问题,可是我看了,没有问题啊。
谁帮忙看看!
谢谢!
SELECT COLCD INTO COLCD2
FROM EVTHDR WHERE FACID='A' AND EVTCD='SP';这个SQL语句执行后得到一个字段的名字为‘ SPSPD' ;
表PLNSCH 的结构如下:
SELECT FACID, REVNM,PRJNO,BLKNO,SPSPD,SPFPD FROM PLNSCH
FACID VARCHAR2(2 BYTE) NOT NULL,
REVNM VARCHAR2(20 BYTE) NOT NULL,
PRJNO VARCHAR2(10 BYTE) NOT NULL,
BLKNO VARCHAR2(10 BYTE) NOT NULL,
SPSPD NUMBER(8),
SPFPD NUMBER(8)
--execute immediate的语法是这样的
execute immedaite 你的语句
into 你的变量
using 你的参数
好了。谢谢!
修改如下: execute immediate 'SELECT '||COLCD2||' FROM PLNSCH WHERE FACID=:1 AND REVNM=:2 AND PRJNO= :3 AND BLKNO= :4'
INTO TDATE2
USING FACID1,REVNM1,PRJNO1,BLKNO1;
dbms_output.put_line(TDATE2);