usedate date; beginselect to_date('201012','yyyyMM') into userdate from dual; EXECUTE IMMEDIATE 'INSERT INTO COMPARE(...) select ...from A where a.date=usedate' 居然提示红色的usedate ORA-00904:USEDATE invalid identifier 问题好多啊 。
A中的 date也是 DATE类型的
语法问题 usedate date; beginselect to_date('201012','yyyyMM') into userdate from dual; EXECUTE IMMEDIATE 'INSERT INTO COMPARE(...) select ...from A where a.date=:usedate' using in usedate;
Example 7-3 Using IN OUT Bind Arguments to Specify SubstitutionsDECLARE plsql_block VARCHAR2(500); new_deptid NUMBER(4); new_dname VARCHAR2(30) := 'Advertising'; new_mgrid NUMBER(6) := 200; new_locid NUMBER(4) := 1700; BEGIN plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;'; EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; /
SELECT NAME,CODE,GRADE FROM STUDENT'
2 begin
3 execute immediate 'create global temporary table t_test_temp
4 (id number,
5 name varchar2(100))';
6 execute immediate ' insert into t_test_temp values(1,''2344'')';
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from t_test_temp;
ID NAME
---------- --------------------------------------------------------------------------------
1 2344
SQL>
临时体现在事务结束(事务型)或者会话结束(会话型)数据就消失,而不是指表本身需要临时创建.这和SqlServer是不一样的.
NAME VARCHAR2(18),
CODE CHAR(8),
GRADE NUMBER
)ON COMMIT PRESERVE ROWS';然后INSERT INTO COMPARE
也是 表 或 视图不存在
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE COMPARE(
NAME VARCHAR2(18),
CODE CHAR(8),
GRADE NUMBER
)ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE 'INSERT INTO COMPARE...';
这个地方ORA-01031 权限不足 这个还要授权 ?
beginselect to_date('201012','yyyyMM') into userdate from dual;
EXECUTE IMMEDIATE 'INSERT INTO COMPARE(...) select ...from A where a.date=usedate'
居然提示红色的usedate ORA-00904:USEDATE invalid identifier
问题好多啊 。
usedate date;
beginselect to_date('201012','yyyyMM') into userdate from dual;
EXECUTE IMMEDIATE 'INSERT INTO COMPARE(...) select ...from A where a.date=:usedate' using in usedate;
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/