v_sql := 'create table temp_stu as select * from student'execute immediate v_sql;
解决方案 »
- oracle 8 導入 oracle 10
- 为什么之前改了监听器配置后可以用jdbc连接数据库,现在连sql plus都等不了??
- ORA-00907: 缺失右括号
- 求助一个关于sql左连接的问题
- 空值数据排序问题
- 关于SQL developer访问oracle
- 求一条SQL语句
- oracle system建表 user不能访问
- Oracle 10g 在windows 2003 下安装时的网络配置,请教高手,急!
- [求助]安装oracle8i补丁(glibc-2.1.3-stubs.tar.gz)的一个问题(redhat8.0)
- 请教一条查询语句的写法
- 【高分求助】ASP.NET+Oracle8的论坛
create or replace function test return varchar2 as
v_cursor number;
v_string varchar2(200);
v_row number;
begin
v_cursor:=dbms_sql.open_cursor;
v_string:='create table testdb (text varchar2(200))';
dbms_sql.parse(v_cursor,v_string,dbms_sql.native);
v_row:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
return ('成功执行'||v_row||'行!');
exception
when others then
dbms_sql.close_cursor(v_cursor);
return ('执行失败!'||sqlcode||sqlerrm);
raise;
end;
wylwyl1130(落雪山林) 告诉你了
---------------------
The Need for Dynamic SQL
You need dynamic SQL in the following situations:
n You want to execute a SQL data definition statement (such as CREATE), a data
control statement (such as GRANT), or a session control statement (such as
ALTER SESSION). In PL/SQL, such statements cannot be executed statically.
n You want more flexibility. For example, you might want to defer your choice of
schema objects until run time. Or, you might want your program to build
different search conditions for the WHERE clause of a SELECT statement. A more
complex program might choose from various SQL operations, clauses, etc.
n You use package DBMS_SQL to execute SQL statements dynamically, but you
want better performance, something easier to use, or functionality that DBMS_
SQL lacks such as support for objects and collections. (For a comparison with
DBMS_SQL, see Oracle8i Application Developer’s Guide - Fundamentals.)
-----------------------
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];
--------------------
CREATE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ’ WHERE ’ || condition;
BEGIN
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE ’DELETE FROM ’ || table_name || where_clause;
EXCEPTION
...
END;------------------------
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE ’DROP TABLE :tab’ USING table_name;
END;
--------------
那确实!
begin
select * from student;
end;
declare
v_char varchar(50);
ls_sql varchar2(1000);
begin
ls_sql :='create table temp_stu as select * from student';
execute immediate ls_sql;
--v_char:='ok';
end;