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;