oracle中dbms_sql的使用，oracledbms_sql

oracle中dbms_sql的使用，oracledbms_sql

一、使用dbms_sql執行查詢

利用dbms_sql執行select語句，其順序為 open cursor-->parse-->define column-->execute-->fetch rows-->close cursor;

1、建立班組表結構，如所示：

proteamid：主鍵ID、proteamname：班組名稱，jctype：機車類型，workflag：工作標識

2、編寫預存程序，使用dbms_sql從dict_proteam中查詢資料資訊，並輸出結果：

create or replace procedure pro_dict_proteam is/**  利用dbms_sql執行select語句，其順序為  open cursor-->parse-->define column-->execute  -->fetch rows-->close cursor;**/       --定義變數       v_cursor number;--遊標ID       v_sql varchar2(500);--用於存放sql語句       v_proteam_id number;--班組ID       v_proteam_name varchar2(50);--班組名稱       v_count number;--在這裡沒有實際的含義，只是存放函數傳回值       --v_jctype varchar(20):=',SS3B,';       v_workflag number:=1;--查詢條件欄位begin       --:v_workflag是預留位置       --select語句中的第一列是proteamid，第二列是proteamname       v_sql:='select proteamid,proteamname from dict_proteam where workflag=:v_workflag';       v_cursor:=dbms_sql.open_cursor;--開啟遊標       dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);--解析動態sql語句       --綁定輸入參數，v_workflag的值傳給:v_workflag       dbms_sql.bind_variable(v_cursor,':v_workflag',v_workflag);       --定義列，v_proteam_id對應select語句中的第一列       dbms_sql.define_column(v_cursor,1,v_proteam_id);       --定義列，v_proteam_name對應select語句中的第二列，長度為50       dbms_sql.define_column(v_cursor,2,v_proteam_name,50);       --執行動態sql語句       v_count:=dbms_sql.execute(v_cursor);       dbms_output.put_line('v_count='||v_count);       loop              --fetch_rows在結果集中移動遊標，如果未抵達末尾，返回1              --從遊標中把資料檢索到緩衝區(buffer)中，緩衝區的值只能被函數              --column_value()所讀取              exit when dbms_sql.fetch_rows(v_cursor)<=0;              --把緩衝區的列的值讀入到相應變數中              --將當前行的查詢結果寫入上面定義的列中              --第一列的值被讀入v_proteam_id中              dbms_sql.column_value(v_cursor,1,v_proteam_id);              --第二列的值被讀入v_proteam_name中              dbms_sql.column_value(v_cursor,2,v_proteam_name);              --列印變數的值              dbms_output.put_line(v_proteam_id||' '||v_proteam_name);       end loop;       dbms_sql.close_cursor(v_cursor);--關閉遊標end;

3、執行預存程序

begin  -- Call the procedure  pro_dict_proteam;end;

4、測試輸出結果

v_count=0
1 電器上車組
2 電機上車組
3 台車上車組
4 受電弓組
5 制動組
6 行安裝置群組
8 儀錶組
9 充電組
10 探傷組
二、使用dbms_sql執行DML語句

insert、update其順序為：open cursor-->parse-->bind variable-->execute-->close cursor;

delete其順序為：open cursor-->parse-->execute-->close cursor;

1、建立測試表結構：

create table TB_TEST2(  ID   NUMBER not null,  NAME VARCHAR2(100),  SEX  CHAR(5))

2、建立預存程序，使用dbms_sql往tb_test2中插入資料

create or replace procedure pro_tb_test2/**  利用dbms_sql執行DML語句  insert、update其順序為  open cursor-->parse-->bind variable-->execute-->close cursor;  delete其順序為  open cursor-->parse-->execute-->close cursor;**/is       v_cursor number;       v_id number;       v_name varchar2(100);       v_sex char(5);       v_sql varchar2(100);       v_count number;begin       v_id:=1;       v_name:='tom';       v_sex:='男';       v_sql:='insert into tb_test2(id,name,sex) values(:v_id,:v_name,:v_sex)';       v_cursor:=dbms_sql.open_cursor;       dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);       dbms_sql.bind_variable(v_cursor,':v_id',v_id);       dbms_sql.bind_variable(v_cursor,':v_name',v_name);       dbms_sql.bind_variable(v_cursor,':v_sex',v_sex);       v_count:=dbms_sql.execute(v_cursor);       dbms_sql.close_cursor(v_cursor);       dbms_output.put_line('資料插入成功!'||v_count);       commit;Exception       when others then       dbms_output.put_line('出現異常資訊！');end;

3、測試預存程序

begin  -- Call the procedure  pro_tb_test2;end;

4、結果輸出：資料插入成功!1

5、查詢tb_test2表中資料資訊：

id  name  sex

1   tom   男

總結：

 DBMS_SQL包提供一個介面，用於執行動態SQL（包括DDL 和DML）。
 DBMS_SQL定義了一個實體叫遊標ID，遊標ID 是一個PL/SQL整型數，通過遊標ID，可以對遊標進行操作。
DBMS_SQL包和本地動態SQL在功能上有許多重疊的地方，但是有的功能只能通過本地動態SQL實現，而有些功能只能通過DBMS_SQL實現。