使用Oracle的DBMSSQL包执行动态SQL语句.docx
《使用Oracle的DBMSSQL包执行动态SQL语句.docx》由会员分享，可在线阅读，更多相关《使用Oracle的DBMSSQL包执行动态SQL语句.docx（28页珍藏版）》请在冰豆网上搜索。
使用Oracle的DBMSSQL包执行动态SQL语句
ÔÚijЩ³¡ºÏÏ£¬´æ´¢¹ý³Ì»ò´¥·¢Æ÷ÀïµÄSQLÓï¾äÐèÒª¶¯Ì¬Éú³É¡£OracleµÄDBMS_SQL°ü¿ÉÒÔÓÃÀ´Ö´Ðж¯Ì¬SQLÓï¾ä¡£±¾ÎÄͨ¹ýÒ»¸ö¼òµ¥µÄÀý×ÓÀ´Õ¹Ê¾ÈçºÎÀûÓÃDBMS_SQL°üÖ´Ðж¯Ì¬SQLÓï¾ä£º
DECLARE
v_cursorNUMBER;
v_statNUMBER;
v_rowNUMBER;
v_idNUMBER;
v_noVARCHAR（100）;
v_dateDATE;
v_sqlVARCHAR（200）;
s_idNUMBER;
s_dateDATE;
BEGIN
s_id:
=3000;
s_date:
=SYSDATE;
v_sql:
='SELECTid,qan_no,sample_dateFROM"tblno"WHEREid>:
sidandsample_date<:
sdate';
v_cursor:
=dbms_sql.open_cursor;--´ò¿ªÓαꣻ
dbms_sql.parse（v_cursor,v_sql,dbms_sql.native）;--½âÎö¶¯Ì¬SQLÓï¾ä£»
dbms_sql.bind_variable（v_cursor,':
sid',s_id）;--°ó¶¨ÊäÈë²ÎÊý£»
dbms_sql.bind_variable（v_cursor,':
sdate',s_date）;
dbms_sql.define_column（v_cursor,1,v_id）;--¶¨ÒåÁÐ
dbms_sql.define_column（v_cursor,2,v_no,100）;
dbms_sql.define_column（v_cursor,3,v_date）;
v_stat:
=dbms_sql.execute（v_cursor）;--Ö´Ðж¯Ì¬SQLÓï¾ä¡£
LOOP
EXITWHENdbms_sql.fetch_rows（v_cursor）<=0;--fetch_rowsÔÚ½á¹û¼¯ÖÐÒƶ¯Óα꣬Èç¹ûδµÖ´ïĩ⣬·µ»Ø1¡£
dbms_sql.column_value（v_cursor,1,v_id）;--½«µ±Ç°ÐеIJéѯ½á¹ûдÈëÉÏÃ涨ÒåµÄÁÐÖС£
dbms_sql.column_value（v_cursor,2,v_no）;
dbms_sql.column_value（v_cursor,3,v_date）;
dbms_output.put_line（v_id||';'||v_no||';'||v_date）;
ENDLOOP;
dbms_sql.close_cursor（v_cursor）;--¹Ø±ÕÓαꡣ
END;
½á¹û£º
3095;S051013XW00010;15-10ÔÂ-05
3112;A051013XW00027;10-10ÔÂ-05
3113;A051013XW00028;13-10ÔÂ-05
3116;S051013XW00031;13-10ÔÂ-05
¸½£ºDBMS_SQLµÄÎĵµ（À´Ô´£ºhttp:
//www.psoug.org）
OracleDBMS_SQL
Version10.2
General
Note:
DMBS_SQListhetraditionalformofdynamicSQLinOracle.
Formostpurposesnativedynamicsql（NDS）willsufficebuttherearesomethingstheDBMS_SQLpackagedoesthatcannotbedoneanyotherway.Thispageemphasizesthoseareaswherethereisnosubstitute.
Purpose
Source{ORACLE_HOME}/rdbms/admin/dbmssql.sql
ConstantsNameDataTypeValue
v6INTEGER0
nativeINTEGER1
v7INTEGER2
DefinedDataTypesGeneralTypes
TYPEdesc_recISRECORD（
col_typebinary_integer:
=0,
col_max_lenbinary_integer:
=0,
col_namevarchar2（32）:
='',
col_name_lenbinary_integer:
=0,
col_schema_namevarchar2（32）:
='',
col_schema_name_lenbinary_integer:
=0,
col_precisionbinary_integer:
=0,
col_scalebinary_integer:
=0,
col_charsetidbinary_integer:
=0,
col_charsetformbinary_integer:
=0,
col_null_okboolean:
=TRUE）;
TYPEdesc_rec2ISRECORD（
col_typebinary_integer:
=0,
col_max_lenbinary_integer:
=0,
col_namevarchar2（32767）:
='',
col_name_lenbinary_integer:
=0,
col_schema_namevarchar2（32）:
='',
col_schema_name_lenbinary_integer:
=0,
col_precisionbinary_integer:
=0,
col_scalebinary_integer:
=0,
col_charsetidbinary_integer:
=0,
col_charsetformbinary_integer:
=0,
col_null_okboolean:
=TRUE）;
TYPEdesc_tabISTABLEOFdesc_rec
INDEXBYbinary_integer;
TYPEdesc_tab2ISTABLEOFdesc_rec2
INDEXBYbinary_integer;
TYPEvarchar2aISTABLEOFVARCHAR2（32767）
INDEXBYbinary_integer;
TYPEvarchar2sISTABLEOFVARCHAR2（256）
INDEXBYbinary_integer;
BulkSQLTypes
TYPEBfile_TableISTABLEOFbfile
INDEXBYbinary_integer;
TYPEBinary_Double_TableISTABLEOFbinary_double
INDEXBYbinary_integer;
TYPEBinary_Float_TableISTABLEOFbinary_float
INDEXBYbinary_integer;
TYPEBlob_TableISTABLEOFblob
INDEXBYbinary_integer;
TYPEClob_TableISTABLEOFclob
INDEXBYbinary_integer;
TYPEDate_TableISTABLEOFdate
INDEXBYbinary_integer;
TYPEinterval_day_to_second_TableISTABLEOF
dsinterval_unconstrainedINDEXBYbinary_integer;
TYPEinterval_year_to_MONTH_TableISTABLEOF
yminterval_unconstrained
INDEXBYbinary_integer;
TYPENumber_TableISTABLEOFNUMBER
INDEXBYbinary_integer;
TYPEtime_TableISTABLEOFtime_unconstrained
INDEXBYbinary_integer;
TYPEtime_with_time_zone_TableISTABLEOFTIME_TZ_UNCONSTRAINEDINDEXBYbinary_integer;
TYPEtimestamp_TableISTABLEOFtimestamp_unconstrained
INDEXBYbinary_integer;
TYPEtimestamp_with_ltz_tableISTABLEOF
TIMESTAMP_LTZ_UNCONSTRAINED
INDEXBYbinary_integer;
TYPEUrowid_TableISTABLEOFurowid
INDEXBYbinary_integer;
TYPEtimestamp_with_time_zone_tableISTABLEOF
TIMESTAMP_TZ_UNCONSTRAINED
INDEXBYbinary_integer;
TYPEVarchar2_TableISTABLEOFVARCHAR2（2000）
INDEXBYbinary_integer;
DependenciesSELECTname
FROMdba_dependencies
WHEREreferenced_name='DBMS_SQL'
UNION
SELECTreferenced_name
FROMdba_dependencies
WHEREname='DBMS_SQL';
ExceptionsErrorCodeReason
ORA-06562Inconsistenttypes:
Raisedbyprocedure"column_value"or
"variable_value"ifthetypeofthegivenoutargumentwheretoputtherequestedvalueisdifferentfromthetypeofthevalue
BIND_ARRAY
Bindsagivenvaluetoagivencollectiondbms_sql.BIND_ARRAY（
cININTEGER,
nameINVARCHAR2,
IN
[,index1ININTEGER,
index2ININTEGER）]）;
DECLARE
stmtVARCHAR2（200）;
dept_no_arraydbms_sql.number_table;
cNUMBER;
dummyNUMBER;
BEGIN
dept_no_array
（1）:
=10;dept_no_array
（2）:
=20;
dept_no_array（3）:
=30;dept_no_array（4）:
=40;
dept_no_array（5）:
=30;dept_no_array（6）:
=40;
stmt:
='deletefromempwheredeptno=:
dept_array';
c:
=dbms_sql.open_cursor;
dbms_sql.parse（c,stmt,dbms_sql.NATIVE）;
dbms_sql.bind_array（c,':
dept_array',dept_no_array,1,4）;
dummy:
=dbms_sql.execute（c）;
dbms_sql.close_cursor（c）;
EXCEPTIONS
WHENOTHERSTHEN
IFdbms_sql.is_open（c）THEN
dbms_sql.close_cursor（c）;
ENDIF;
RAISE
END;
/
BIND_VARIABLE
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable（
cININTEGER,
nameINVARCHAR2,
valueIN）
CREATEORREPLACEPROCEDUREdemo（salaryINNUMBER）AS
cursor_nameINTEGER;
rows_processedINTEGER;
BEGIN
cursor_name:
=dbms_sql.open_cursor;
dbms_sql.parse（cursor_name,'DELETEFROMempWHEREsal>:
x',
dbms_sql.NATIVE）;
dbms_sql.bind_variable（cursor_name,':
x',salary）;
rows_processed:
=dbms_sql.execute（cursor_name）;
dbms_sql.close_cursor（cursor_name）;
EXCEPTION
WHENOTHERSTHEN
dbms_sql.close_cursor（cursor_name）;
END;
/
BIND_VARIABLE_CHAR
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variabl_char（
cININTEGER,
nameINVARCHAR2,
valueINCHARCHARACTERSETANY_CS[,out_value_sizeININTEGER]）;
Seebind_variabledemo
BIND_VARIABLE_RAW
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable_raw（
cININTEGER,
nameINVARCHAR2,
valueINRAW[,out_value_sizeININTEGER]）;
Seebind_variabledemo
BIND_VARIABLE_ROWID
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable_rowid（
cININTEGER,
nameINVARCHAR2,
valueINROWID）;
Seebind_variabledemo
CLOSE_CURSOR
Closescursorandfreememorydbms_sql.close_cursor（cINOUTINTEGER）;
Seeis_opendemo
COLUMN_VALUE
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value（
cININTEGER,
positionININTEGER,
valueOUT
[,column_errorOUTNUMBER]
[,actual_lengthOUTINTEGER]）;
Seefinaldemo
COLUMN_VALUE_CHAR
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value_char（
cININTEGER,
positionININTEGER,
valueOUTCHARCHARACTERSETANY_CS
[,column_errorOUTNUMBER]
[,actual_lengthOUTINTEGER]）;
Seecolumn_valueinfinaldemo
COLUMN_VALUE_LONG
ReturnsaselectedpartofaLONGcolumn,thathasbeendefinedusingDEFINE_COLUMN_LONGdbms_sql.column_value_long（
cININTEGER,
positionININTEGER,
lengthININTEGER,
offsetININTEGER,
valueOUTVARCHAR2,
value_lengthOUTINTEGER）;
Seecolumn_valueinfinaldemo
COLUMN_VALUE_RAW
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value_raw（
cININTEGER,
positionININTEGER,
valueOUTRAW
[,column_errorOUTNUMBER]
[,actual_lengthOUTINTEGER]）;
Seecolumn_valueinfinaldemo
COLUMN_VALUE_ROWID
Undocdbms_sql.column_value_rowid（
cININTEGER,
positionININTEGER,
valueOUTROWID
[,column_errorOUTNUMBER]
[,actual_lengthOUTINTEGER]）;
Seecolumn_valueinfinaldemo
DEFINE_ARRAY
Definesacollectiontobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_array（
cININTEGER,
positionININTEGER,
IN
cntININTEGER,
lower_bndININTEGER）;
DECLARE
cnumber;
dnumber;
n_tabdbms_sql.number_table;
indxnumber:
=-10;
BEGIN
c:
=dbms_sql.open_cursor;
dbms_sql.parse（c,'selectnfromtorderby1',dbms_sql.NATIVE）;
dbms_sql.define_array（c,1,n_tab,10,indx）;
d:
=dbms_sql.execute（c）;
LOOP
d:
=dbms_sql.fetch_rows（c）;
dbms_sql.column_value（c,1,n_tab）;
exitwhend!
=10;
ENDLOOP;
dbms_sql.close_cursor（c）;
EXCEPTIONS
WHENOTHERSTHEN
IFdbms_sql.is_open（c）THEN
dbms_sql.close_cursor（c）;
ENDIF;
RAISE;
END;
/
DEFINE_COLUMN
Definesacolumntobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_column（
cININTEGER,
positionININTEGER,
columnIN）
Seefinaldemo
DEFINE_COLUMN_CHAR
Undocdbms_sql.define_column_char（
cININTEGER,
positionININTEGER,
columnINCHARCHARACTERSETANY_CS,
column_sizeININTEGER）;
Seedefine_columninfinaldemo
DEFINE_COLUMN_LONG
DefinesaLONGcolumntobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_column_long（
cININTEGER,
positionININTEGER）;
Seedefine_columninfinaldemo
DEFINE_COLUMN_RAW
Undocdbms_sql.define_column_raw（
cININTEGER,
positionININTEGER,
columnINRAW,
column_sizeININTEGER）;
Seedefine_columninfinaldemo
DEFINE_COLUMN_ROWID
Undocdbms_sql.define_column_rowid（
cININTEGER,
positionININTEGER,
columnINROWID）;
Seedefine_columninfinaldemo
DESCRIBE
升级会员 