动态SQL

动态SQL

动态sql：在编写时无法完整写得，只能在程序执行时构造的临时sql

宿主变量：一个宿主指针变量指向一块内存空间，其中存有用户输入的sql语句

语法1:IMMEDIATE:要求sSQL非select语句，并且无输入的宿主变量
	EXEC SQL execute immediate "sql的内容";
	或者 EXEC SQL execute immediate:sSQL;
	---例子：EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE tbname (col1 varchar2(4))";
---------------------------------------------------------------------------------
语法2:PREPARE:要求sSQL非select语句，并且输入的变量数目、类型已知
	strcpy(sSQL,"delete from emp where empno=");
	EXEC SQL prepare s from:sSQL;       --准备SQL
	EXEC SQL execute s using :v1, :v2;  --执行sql
	---例子：scanf("%s",loc);scanf("%s",deptno);
     EXEC SQL prepare my_pre_sql FROM 'update dept set loc = :a where deptno = :b';
     EXEC SQL execute my_pre_sql using :loc, :deptno;
--------------------------------------------------------------------------------
语法3：declare：要求sSQL为select语句，且要求变量个数固定
	EXEC SQL prepare s from:sSQL;       --准备SQL
	EXEC SQL declare c cursor for s;    --定义游标
	EXEC SQL open c using :v1,:v2;      --启用游标
	EXEC SQL fetch c into :v1,:v2;      --获取数据
	EXEC SQL close c;                   --关闭游标
	---例子：
	exec sql prepare s from 'select deptno,dname,loc from dept where deptno>:a';
	exec sql declare c cursor for s;
	exec sql open c using :deptno;
	exec sql fetch c into :deptno, :dname, :loc;
	exec sql close c;
-------------------------------------------------------------------------------
语法4：sql（用到了再说）
EXEC SQL COMMIT