exec 是执行过程或函数的
简单的sql语句不用exec
SQL> desc tb1;
名称 空值? 类型
----------------------------------------------------- -------- ------------
NAME VARCHAR2(10)SQL> select &a from tb1;
输入a的值: name
原值 1:select &a from tb1
新值 1:select name from tb1NAME
----------
GN1
GN2
简单的sql语句不用exec
SQL> desc tb1;
名称 空值? 类型
----------------------------------------------------- -------- ------------
NAME VARCHAR2(10)SQL> select &a from tb1;
输入a的值: name
原值 1:select &a from tb1
新值 1:select name from tb1NAME
----------
GN1
GN2
写 exec select * from tablename ?
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id; EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
********************************
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;
tsql:='select * from t_item' ||
' where fitemid=' || fitemid;
execute immediate tsql;
用exec +sql(语句)执行查询,
并显示出结果,在过程中好象是可以执行的,但就是说了个pl/sql执行成功,没有结果显示啊
then exec
---------- ------------------------------ -------
A TABLE
BANK_ID TABLE
EMPLOYEE TABLE
GRANTNAME_ST TABLE
GRANTNAME_ZS TABLE
HJDY_SD TABLE
HJDY_ST TABLE
PERSON TABLE
PERSON_BANK_ACCOUNT TABLE
TEST TABLE已选择10行。已用时间: 00: 00: 00.30
22:03:19 jlanzpa817>select * from test where tname = &hi;
输入 hi 的值: 'A'
原值 1: select * from test where tname = &hi
新值 1: select * from test where tname = 'A' CLUSTERID TNAME TABTYPE
---------- ------------------------------ -------
A TABLE已用时间: 00: 00: 00.10
22:03:29 jlanzpa817>
谢谢,请继续关注 !
exec select * from mytest (就要加exec)
还在过程中执行这个,如何把结果显示出来,