一、语法
execute immediate SQL语句/PLSQL代码块 into 变量 [using 参数1,参数2,……];
using后面的参数要与into后面的变量及变量的数据类型对应起来
在执行语句中SQL语句必须使用单引号括起来
DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM EMP WHERE EMPNO=7369' INTO V_EMP;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
END;
如果SQL语句过长,为了方便读写,也可以定义一个变量将SQL语句赋值给这个变量,并将这个变量放在执行语句中
DECLARE
V_EMP EMP%ROWTYPE;
V_SQL VARCHAR2(100);
BEGIN
V_SQL:='SELECT * FROM EMP WHERE EMPNO=7369';
EXECUTE IMMEDIATE V_SQL INTO V_EMP;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
END;
二、转义符
1.当定义number类型时
根据输入的员工编号查询
DECLARE
V_EMP EMP%ROWTYPE;
V_EMPNO NUMBER(10):=&员工编号;
V_SQL VARCHAR2(100);
BEGIN
V_SQL:='SELECT * FROM EMP WHERE EMPNO='||V_EMPNO;
EXECUTE IMMEDIATE V_SQL INTO V_EMP;
DBMS_OUTPUT.PUT_LINE(V_EMP.ename);
END;
2.当定义varchar2类型时
根据传入的员工姓名查询,用两个单引号转义一个单引号
DECLARE
V_EMP EMP% ROWTYPE;
V_ENAME VARCHAR2(20):='&员工姓名';
V_SQL VARCHAR2(100);
BEGIN
V_SQL:='SELECT * FROM EMP WHERE ENAME='''||V_ENAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_EMP;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
END;
3.q'[SQL语句]'
DECLARE
V_EMP EMP% ROWTYPE;
V_ENAME VARCHAR2(20):='&员工姓名';
V_SQL VARCHAR2(100);
BEGIN
V_SQL:=Q'[SELECT * FROM EMP WHERE ENAME=']'||V_ENAME||Q'[']';
EXECUTE IMMEDIATE V_SQL INTO V_EMP;
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
END;
三、在代码块中添加DDL语句
DECLARE
V_SQL VARCHAR2(100);
TYPE V_TYPE IS RECORD(
V_ID NUMBER(4),
V_NAME VARCHAR2(20),
V_CLASS NUMBER(5)
);
V_STU V_TYPE;
BEGIN
V_SQL:='CREATE TABLE STUDENT (ID NUMBER(4),STU_NAME VARCHAR2(20),CLASS_NO NUMBER(10))';
EXECUTE IMMEDIATE V_SQL;
V_SQL:=Q'[INSERT INTO STUDENT VALUES(1,'张三',95033)]';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='UPDATE STUDENT SET ID=2';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='SELECT * FROM STUDENT';
EXECUTE IMMEDIATE V_SQL INTO V_STU;
V_SQL:='DELETE FROM STUDENT WHERE ID=2';--一般不用
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE(V_STU.V_ID||'-'||V_STU.V_NAME||'-'||V_STU.V_CLASS);
END;