create or replace procedure....
declare
tsql varchar2(500);
...
begin
....
tsql:='create table tbname(col1 varchar2(10),col2 number(5))';
execute immediate tsql;
....
declare
tsql varchar2(500);
...
begin
....
tsql:='create table tbname(col1 varchar2(10),col2 number(5))';
execute immediate tsql;
....
用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;
存储过程的工具
toad
pl/sql developer
如创建表就需要create any table的特权给这个用户
其它的也一样。
调试工具建议用
pl/sql developer,是专门for oracle的,功能强大toad太大