动态SQL(学习笔记)

浏览数：25 / 时间：2015年06月12日

动态SQL

EXECUTE IMMEDIATE 动态SQL字符串 [BUCK COLLECT] INTO 自定义的变量,,|记录类型

USING [IN |OUT|IN OUT]绑定的参数]

[RETURNING |RETURN ][BULK COLLECT]INTO 绑定参数

示例1

--根据用记输入的员工ID来输入员工信息

DECLARE
v_sql_smst             VARCHAR2(200);    --定义变量用来存放SQL语句
v_emp                  emp%ROWTYPE;      --定义量行变量
v_id                   emp.empno%TYPE:=&empno;     --定义用户输入的ID
BEGIN
v_sql_smst:=‘SELECT * FROM EMP WHERE EMPNO=:EMPNO‘;
EXECUTE IMMEDIATE v_sql_smst INTO v_emp USING v_id;
dbms_output.put_line(‘员工编号: ‘||v_id||‘ 姓名:‘||v_emp.ename||‘ 职位‘||v_emp.job);
END;

动态游标返回多行数据

--根据输入的工资，返回员工信息

DECLARE
 cur_emp       SYS_REFCURSOR;  --定义游标
 v_sal         emp.sal%TYPE:=&sal;    --定义用户输入工资
 v_emp          emp%ROWTYPE;    --定义行变量
BEGIN
  OPEN cur_emp  FOR ‘SELECT * FROM EMP WHERE SAL>:SAL ORDER BY SAL‘ USING v_sal;   --打开游标并执行SQL查询多行，返回结果
 
  dbms_output.put_line(‘工资高于 ‘||v_sal||‘员工有:‘);
    LOOP
       FETCH cur_emp INTO v_emp;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(‘员工编号：‘||v_emp.empno||‘ 姓名:‘||v_emp.ename||‘ 工资:‘||v_emp.sal);
  END LOOP;
  CLOSE cur_emp;
END;

--动态创建表和插入表

DECLARE 
 v_sql_creat VARCHAR2(220):=‘CREATE TABLE stuinfo(ID NUMBER(5),NAME VARCHAR2(20),sex VARCHAR2(5))‘;
 v_into1 VARCHAR2(220):=‘INSERT INTO stuinfo VALUES(1,‘‘张三‘‘,‘‘男‘‘)‘;
 v_into2 VARCHAR2(220):=‘INSERT INTO stuinfo VALUES(2,‘‘李四‘‘,‘‘女‘‘)‘;
BEGIN
 EXECUTE IMMEDIATE v_sql_creat;
 EXECUTE IMMEDIATE v_into1;
 EXECUTE IMMEDIATE v_into2;
commit
END;

--动态增加

DECLARE 
 v_id      stuinfo.id%TYPE:=&ID;
 v_name    stuinfo.name%TYPE:=‘&name‘;
 v_sex     stuinfo.sex%TYPE:=‘&sex‘;
 v_into1 VARCHAR2(220):=‘INSERT INTO stuinfo VALUES(:id,:name,:sex)‘;

BEGIN
 EXECUTE IMMEDIATE v_into1 USING v_id,v_name,v_sex;
 COMMIT;
END;

--动态删除

DECLARE 
 v_id      stuinfo.id%TYPE:=&ID;
 
 v_sql_del VARCHAR2(220):=‘delete from stuinfo where id=:id ‘;
BEGIN
 EXECUTE IMMEDIATE v_sql_del USING v_id;
 
EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
END;

--动态更新

--动态更新
DECLARE 
 v_id      stuinfo.id%TYPE:=&ID;
 v_name    stuinfo.name%TYPE:=‘&name‘;
 v_sex     stuinfo.sex%TYPE:=‘&sex‘;
 v_sql_update VARCHAR2(200):=‘UPDATE stuinfo SET name=:1,sex=:2 where id=:3‘;
BEGIN
 EXECUTE IMMEDIATE v_sql_update USING v_name,v_sex,v_id;
 IF SQL%ROWCOUNT > 0 THEN
   COMMIT;
   dbms_output.put_line(‘ok‘);
 END IF;
EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line(SQLERRM);
END;
--USING中的绑定顺序与执行语句中的顺序要一致
SELECT * FROM stuinfo;

--动态查询

DECLARE 
 stu      stuinfo%ROWTYPE;
 cur_r    SYS_REFCURSOR;
 v_id      stuinfo.id%TYPE:=&ID;
 v_sql_sel VARCHAR2(220):=‘select * from stuinfo where id=:id ‘;
BEGIN
 OPEN  cur_r FOR v_sql_sel USING v_id;
 LOOP
   FETCH cur_r INTO stu;
   EXIT WHEN cur_r%NOTFOUND;
   dbms_output.put_line(stu.id||‘ ‘||stu.name||‘ ‘||stu.sex);
 END LOOP;
 
EXCEPTION
   WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
END;