-- 動态 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;