oracle学习笔记(动态SQL)

-- 动态 SQL
-- 使用 dbms_sql 包
-- 步骤:
1)打开游标:dbms_sql.open_cursor, 返回游标 ID 号
2)语法分析:dbms_sql.parse( i_c INTEGER,i_statement IN VARCHAR2,i_language_flag IN INTEGER),
      i_c打开的光标 ID,i_statement 等待分析的 SQL 语句,i_language_flag,语言版本,取值:6,7,dbms_sql.native,按连接的数据库版本运行
3)绑定输入变量:dbms_sql.bind_variable,注意:DDL,ALTER session语句不能绑定变量,绑定变量前的冒号可有可无
绑定数字型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN NUMBER);
绑定varchar2型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2);
              bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2,o_value_size OUT INTEGER);
绑定char型的变量:bind_variable_char(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2[,o_value_size OUT INTEGER]);
绑定日期型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN DATE);
绑定raw型的变量:bind_variable_raw(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN RAW[,o_value_size OUT INTEGER])
绑定log型的变量:
4)执行语句:dbms_sql.EXECUTE(i_c IN INTEGER) 返回处理的行数,
5)关闭游标:dbms_sql.close_cursor(i_c IN INTEGER)

DECLARE
v_sql VARCHAR2(200);
v_age INTEGER;
v_emp_no CHAR(5);
v_id INTEGER;
v_row INTEGER;
BEGIN
  v_sql :='update emp_m set emp_age = :age where emp_no = :emp_no';
  v_age := 25;
  v_emp_no := '00001';
  v_id := dbms_sql.open_cursor;
  dbms_sql.parse(v_id,v_sql,dbms_sql.native);
  dbms_sql.bind_variable(v_id,':age',v_Age);
  dbms_sql.bind_variable(v_id,':emp_no',v_emp_no);
  v_row := dbms_sql.execute(v_id);
  dbms_sql.close_cursor(v_id);
  dbms_output.put_line('v_row = '||to_char(v_row));
END;
--执行查询语句
-- 步骤:
1)打开游标:dbms_sql.open_cursor, 返回游标 ID 号
2)语法分析:dbms_sql.parse( i_c INTEGER,i_statement IN VARCHAR2,i_language_flag IN INTEGER),
      i_c打开的光标 ID,i_statement 等待分析的 SQL 语句,i_language_flag,语言版本,取值:6,7,dbms_sql.native,按连接的数据库版本运行
3)绑定输入变量:dbms_sql.bind_variable,注意:DDL,ALTER SESSION 语句不能绑定变量,绑定变量前的冒号可有可无
4)定义选择列表项:dbms_sql.define_column
定义类型为number的:dbms_sql.define_column(i_c IN INTEGER,i_position IN INTEGER,i_column IN NUMBER)
定义类型为varchar2的:dbms_sql.define_column(i_c IN INTEGER,i_position IN INTEGER,i_column IN VARCHAR2,i_column_size IN INTEGER)
定义类型为char的:dbms_sql.define_column_char(i_c IN INTEGER,i_position IN INTEGER,i_column IN CHAR,i_column_size IN INTEGER)
定义类型为date的:dbms_sql.define_column_date(i_c IN INTEGER,i_position IN INTEGER,i_column IN DATE)
定义类型为raw的:dbms_sql.define_column_raw(i_c IN INTEGER,i_position IN INTEGER,i_column IN RAW,i_column_size IN INTEGER)        
i_position 选择列表中项的相对位置,第一个是 1 ,i_column 定义输出变量的类型和长度的变量,i_size 定义输出变量的最大长度
5)执行语句:dbms_sql.EXECUTE(i_c IN INTEGER) 返回处理的行数
6)取回查询到的行:dbms_sql.fetch_rows(i_c IN INTEGER),返回值为 0 表示结束,dbms_sql.EXECUTE 和 dbms_sql.fetch_rows 组合成一个语句:dbms_sql.execute_and_fetch(i_c IN INTEGER,i_exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER
          i_exact 如果为真,则在查询是返回一行以上是引发异常too_many_rows,但是程序仍然可以执行fetch和检索操作
          返回值表示到目前为止取回的行数
7)把查询到的结果返回到PL/SQL变量中:dbms_sql.COLUMN_VALUE
NUMBER 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT NUMBER)
        dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT NUMBER,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
VARCHAR2 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT VARCHAR2)
          dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT VARCHAR2,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
CHAR 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT CHAR)
        dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT CHAR,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
DATE 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT DATE)
        dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT DATE,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
RAW 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT RAW)
          dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT RAW,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
8)关闭游标:dbms_sql.close_cursor(i_c IN INTEGER)

DECLARE
v_sql VARCHAR2(200);
v_id INTEGER;
v_emp_name emp_m.emp_name%TYPE;
v_emp_sex emp_m.emp_sex%TYPE;
v_emp_age emp_m.emp_age%TYPE;
v_emp_birthday emp_m.emp_birthday%TYPE;
v_row INTEGER;
BEGIN
  v_sql := 'select emp_name,emp_sex,emp_age,emp_birthday from emp_m where emp_no = :emp_no';
  v_id := dbms_sql.open_cursor;
  dbms_sql.parse(v_id,v_Sql,dbms_sql.native);
  dbms_sql.bind_variable(v_id,':emp_no','00001');
  dbms_sql.define_column(v_id,1,v_emp_name,10);
  dbms_sql.define_column_char(v_id,2,v_emp_sex,1);
  dbms_sql.define_column(v_id,3,v_emp_age);
  dbms_sql.define_column(v_id,4,v_emp_birthday);
  v_row := dbms_sql.execute(v_id);
  LOOP
  IF dbms_sql.fetch_rows(v_id) = 0 THEN
    EXIT;
  END IF;
  dbms_sql.COLUMN_VALUE(v_id,1,v_emp_name);
  dbms_sql.column_value_char(v_id,2,v_emp_sex);
  dbms_sql.column_value(v_id,3,v_emp_age);
  dbms_sql.column_value(v_id,4,v_emp_birthday);
  dbms_output.put_line('emp_name='||v_emp_name||',emp_sex='||v_emp_sex||',emp_age='||to_char(v_emp_age)||',emp_birthday='||to_char(v_emp_birthday,'yyyy/mm/dd'));
  END LOOP;
  dbms_sql.close_cursor(v_id);
END;