用dbms_sql来实现, 例: CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS cursor_name INTEGER; rows_processed INTEGER; BEGIN cursor_name := dbms_sql.open_cursor; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x', dbms_sql.native); DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary); rows_processed := dbms_sql.execute(cursor_name); DBMS_SQL.close_cursor(cursor_name); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cursor_name); END;
select * from a,b where a.dept_id in (函数?(b.depts)) 这样的格式是不行的,即使函数返回一个象'1','2','3'这样的字符串,oracle会把这个字符串作为一个整体来处理,所以结果不对。
其实简单的用EXECUTE IMMEDIATE V_SQL就可以了.如果要动态创建VIEW: V_SQL:='CREATE VIEW V_NAME AS SELECT * FROM .....;'; EXECUTE IMMEDIATE V_SQL; 注意:没有返回值.
select * from a,b where a.dept_id in (函数?(b.depts))因为b.depts的值刚好是'1,2,3,4,5',而a.dept_id的值为number型的,所以如果oracle有象javascript的eval那样函数可以把b.depts的值解释为代码的话就好了
例:
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
DECLARE
PROPACK VARCHAR2(200);
V_CURSOR NUMBER;
V_NUMROWS INTEGER;
BEGIN
PROPACK := 'BEGIN' || CHR(10) || ' PRZK1_PRONAME(1);' || CHR(10);
PROPACK := PROPACK || ' EXCEPTION' || CHR(10) || ' WHEN OTHERS THEN' || CHR(10) || ' NULL;' || CHR(10);
PROPACK := PROPACK || 'END;' + CHR(10);
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(V_CURSOR,PROPACK,DBMS_SQL.V7);
V_NUMROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
where a.dept_id in (函数?(b.depts))
这样的格式是不行的,即使函数返回一个象'1','2','3'这样的字符串,oracle会把这个字符串作为一个整体来处理,所以结果不对。
V_SQL:='CREATE VIEW V_NAME AS SELECT * FROM .....;';
EXECUTE IMMEDIATE V_SQL;
注意:没有返回值.
where a.dept_id in (函数?(b.depts))因为b.depts的值刚好是'1,2,3,4,5',而a.dept_id的值为number型的,所以如果oracle有象javascript的eval那样函数可以把b.depts的值解释为代码的话就好了