游标和动态SQL - 数据库编程

ELECT id,name FROM employees; FETCH refcur INTO e_id,e_name; WHILE refcur%FOUND LOOP dbms_output.put_line(‘#’||e_id||’:’||e_name); FETCH refcur INTO e_id,e_name; END LOOP; CLOSE refcur; END;

根据用户的输入（员工、部门）打印信息

DECLARE
      TYPE refcur_t IS REF CURSOR;
      refcur refcur_t;
      p_id NUMBER;
      p_name VARCHAR2(50);
      selection VARCHAR2(1) :=UPPER(SUBSTR(‘&tab’,1,1));
BEGIN
      IF selection = ‘E’ THEN
             OPEN refcur FOR
                    SELECT id,name FROMemployees;
             dbms_output.put_line(‘===员工信息===’);
      ELSEIF selection = ‘D’ THEN
             OPEN refcur FOR
                    SELECTdepartment_id,department_name FROM departments;
             dbms_output.put_line(‘===部门信息===’);
      ELSE
             dbms_output.put_line(‘请输入员工信息E或部门信息D’);
             RETURN;
      END IF;
      FETCH refcur INTO p_id,p_name;
      WHILE refcur%FOUND LOOP
             dbms_output.put_line(‘#’||p_id||’:’||p_name);
             FETCH refcur INTO p_id,p_name;
      END LOOP;
      CLOSE refcur;
END;

创建动态SQL 语句。

静态SQL，编译时确定。

动态SQL，不编译，执行时动态确定；根据用户输入参数等才能确定SQL语句；解决PL/SQL中不支持DDL语句的问题。

创建动态DML.DDL的SQL语句语法：

EXECUTEIMMEDIATE ‘DML、DDL语句’;[INTO<变量序列>] [USING <参数序列>];只能执行返回一行或0行的语句。

如果后面的语句是个select语句，则可以使用into子句用于接收select语句选择的记录值。可以是一个变量序列，或者一个记录型变量也就是record型的变量。如果SQL语句中有参数需要动态确定，那么我们使用USING子句，USING子句用于绑定输入的参数变量。SQL语句中若有参数，使用”:参数名”

示例：动态创建表

BEGIN
      EXECUTE IMMEDIATE
             ‘CREATE TABLE bonus(id NUMBER,amtNUMBER)’;
END;

示例：动态查询一个员工电话

DECLARE
      sql_stmt VARCHAR2(200);
      emp_id NUMBER(10) :=’&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt :=’select * from employees WHEREid =:id’;
      EXECUTE IMMEDIATE sql_stmt INTO emp_recUSING emp_id;
END;

示例:动态插入记录

DECLARE
      Sql_stmt varchar2(200);
      emp_id NUMBER(10) := ‘&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt := ‘INSERT INTO employees(id)values(:id)’;
      EXECUTE IMMEDIATE sql_stmt USING emp_id;
      Dbms_output.put_line(emp.rec.phone);
END;

EXECUTEIMMEDIATE语句只能返回一行或没有返回，如果编写返回多行的SQL语句，可以使用ref动态游标，他的语法：OPEN cursor_name FOR [USING <参数序列>];

示例：动态输出工资大于某个数额的员工信息

DECLARE
      e_id NUMBER(10);
      e_name VARCHAR2(50);
      s_salary NUMBER(8);
      TYPE c_type is REF CURSOR;
      cur c_type;
      p_salary NUMBER := ‘&p_id’;
BEGIN
      OPEN cur FOR ‘selecte.id,e.name,e.salaryvalue from employees e,salary s where e.id=s.employeeid ands.salaryvalue >:sal ORDER BY id ASC’;
      USING p_salary;
      dbms_output.put_line(‘薪水大于’||p_salary||’的员工有：’);
      LOOP
             FETCH cur INTOe_id,e_name,e_salary;
             EXIT WHEN cur%NOTFOUND;
             dbms_output.put_line(‘编号：’||e_id||’姓名：’||e_name||’薪水：’||e_salary);
END LOOP
      CLOSE cur;
END;

游标和动态SQL(二)