有没有可以动态执行存储过程,就象动态执行sql语句:
如 execute immediate "select * from a where b=:str"
是否有这样的语句:
ProName:='usp_getModlue';
execute immediate ProName || '(:str1,str2)'
敬请指教
如 execute immediate "select * from a where b=:str"
是否有这样的语句:
ProName:='usp_getModlue';
execute immediate ProName || '(:str1,str2)'
敬请指教
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;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;这个很好用.
但是8的使用如下格式:
DECALRE
CUR_ID NUMBER ;
STAT INTEGER ;
SQL_TEXT VARCHAR2(255);
SQL_TEXT1 VARCHAR2(255);
VAR_STRING VARCHAR2(100);
BEGIN
SQL_TEXT:='DELETE FROM dept WHERE deptno = :num';
CUR_ID:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CUR_ID,SQL_TEXT,DBMS_SQL.NATIVE);
STAT:=DBMS_SQL.EXECUTE(CUR_ID);
DBMS_SQL.CLOSE_CURSOR(CUR_ID)
另外在字符连接中单引号是如何连接的 ||' ?