使用Oracle的DBMSSQL包执行动态SQL语句Word文件下载.docx
- 文档编号:16664951
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:28
- 大小:21.51KB
使用Oracle的DBMSSQL包执行动态SQL语句Word文件下载.docx
《使用Oracle的DBMSSQL包执行动态SQL语句Word文件下载.docx》由会员分享,可在线阅读,更多相关《使用Oracle的DBMSSQL包执行动态SQL语句Word文件下载.docx(28页珍藏版)》请在冰豆网上搜索。
¯
É
ú
Oracleµ
DBMS_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);
--½
â
dbms_sql.bind_variable(v_cursor,'
:
sid'
s_id);
--°
ó
ë
²
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);
--Ö
LOOP
EXITWHENdbms_sql.fetch_rows(v_cursor)<
=0;
--fetch_rowsÔ
½
á
Ø
1¡
dbms_sql.column_value(v_cursor,1,v_id);
«
Ç
°
é
Ñ
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Ô
3113;
A051013XW00028;
13-10Ô
3116;
S051013XW00031;
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:
col_namevarchar2(32):
col_name_lenbinary_integer:
col_schema_namevarchar2(32):
col_schema_name_lenbinary_integer:
col_precisionbinary_integer:
col_scalebinary_integer:
col_charsetidbinary_integer:
col_charsetformbinary_integer:
col_null_okboolean:
=TRUE);
TYPEdesc_rec2ISRECORD(
col_namevarchar2(32767):
TYPEdesc_tabISTABLEOFdesc_rec
INDEXBYbinary_integer;
TYPEdesc_tab2ISTABLEOFdesc_rec2
TYPEvarchar2aISTABLEOFVARCHAR2(32767)
TYPEvarchar2sISTABLEOFVARCHAR2(256)
BulkSQLTypes
TYPEBfile_TableISTABLEOFbfile
TYPEBinary_Double_TableISTABLEOFbinary_double
TYPEBinary_Float_TableISTABLEOFbinary_float
TYPEBlob_TableISTABLEOFblob
TYPEClob_TableISTABLEOFclob
TYPEDate_TableISTABLEOFdate
TYPEinterval_day_to_second_TableISTABLEOF
dsinterval_unconstrainedINDEXBYbinary_integer;
TYPEinterval_year_to_MONTH_TableISTABLEOF
yminterval_unconstrained
TYPENumber_TableISTABLEOFNUMBER
TYPEtime_TableISTABLEOFtime_unconstrained
TYPEtime_with_time_zone_TableISTABLEOFTIME_TZ_UNCONSTRAINEDINDEXBYbinary_integer;
TYPEtimestamp_TableISTABLEOFtimestamp_unconstrained
TYPEtimestamp_with_ltz_tableISTABLEOF
TIMESTAMP_LTZ_UNCONSTRAINED
TYPEUrowid_TableISTABLEOFurowid
TYPEtimestamp_with_time_zone_tableISTABLEOF
TIMESTAMP_TZ_UNCONSTRAINED
TYPEVarchar2_TableISTABLEOFVARCHAR2(2000)
DependenciesSELECTname
FROMdba_dependencies
WHEREreferenced_name='
DBMS_SQL'
UNION
SELECTreferenced_name
WHEREname='
ExceptionsErrorCodeReason
ORA-06562Inconsistenttypes:
Raisedbyprocedure"
column_value"
or
"
variable_value"
ifthetypeofthegivenoutargumentwheretoputtherequestedvalueisdifferentfromthetypeofthevalue
BIND_ARRAY
Bindsagivenvaluetoagivencollectiondbms_sql.BIND_ARRAY(
cININTEGER,
nameINVARCHAR2,
<
table_variable>
IN<
datatype>
[,index1ININTEGER,
index2ININTEGER)]);
stmtVARCHAR2(200);
dept_no_arraydbms_sql.number_table;
cNUMBER;
dummyNUMBER;
dept_no_array
(1):
=10;
dept_no_array
(2):
=20;
dept_no_array(3):
=30;
dept_no_array(4):
=40;
dept_no_array(5):
dept_no_array(6):
stmt:
deletefromempwheredeptno=:
dept_array'
c:
dbms_sql.parse(c,stmt,dbms_sql.NATIVE);
dbms_sql.bind_array(c,'
dept_no_array,1,4);
dummy:
=dbms_sql.execute(c);
dbms_sql.close_cursor(c);
EXCEPTIONS
WHENOTHERSTHEN
IFdbms_sql.is_open(c)THEN
ENDIF;
RAISE
/
BIND_VARIABLE
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable(
cININTEGER,
nameINVARCHAR2,
valueIN<
)
CREATEORREPLACEPROCEDUREdemo(salaryINNUMBER)AS
cursor_nameINTEGER;
rows_processedINTEGER;
cursor_name:
dbms_sql.parse(cursor_name,'
DELETEFROMempWHEREsal>
x'
dbms_sql.NATIVE);
dbms_sql.bind_variable(cursor_name,'
salary);
rows_processed:
=dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
BIND_VARIABLE_CHAR
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variabl_char(
valueINCHARCHARACTERSETANY_CS[,out_value_sizeININTEGER]);
Seebind_variabledemo
BIND_VARIABLE_RAW
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable_raw(
valueINRAW[,out_value_sizeININTEGER]);
BIND_VARIABLE_ROWID
Bindsagivenvaluetoagivenvariabledbms_sql.bind_variable_rowid(
valueINROWID);
CLOSE_CURSOR
Closescursorandfreememorydbms_sql.close_cursor(cINOUTINTEGER);
Seeis_opendemo
COLUMN_VALUE
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value(
positionININTEGER,
valueOUT<
[,column_errorOUTNUMBER]
[,actual_lengthOUTINTEGER]);
Seefinaldemo
COLUMN_VALUE_CHAR
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value_char(
valueOUTCHARCHARACTERSETANY_CS
[,column_errorOUTNUMBER]
Seecolumn_valueinfinaldemo
COLUMN_VALUE_LONG
ReturnsaselectedpartofaLONGcolumn,thathasbeendefinedusingDEFINE_COLUMN_LONGdbms_sql.column_value_long(
lengthININTEGER,
offsetININTEGER,
valueOUTVARCHAR2,
value_lengthOUTINTEGER);
COLUMN_VALUE_RAW
Returnsvalueofthecursorelementforagivenpositioninacursordbms_sql.column_value_raw(
valueOUTRAW
COLUMN_VALUE_ROWID
Undocdbms_sql.column_value_rowid(
valueOUTROWID
DEFINE_ARRAY
Definesacollectiontobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_array(
cntININTEGER,
lower_bndININTEGER);
cnumber;
dnumber;
n_tabdbms_sql.number_table;
indxnumber:
=-10;
dbms_sql.parse(c,'
selectnfromtorderby1'
dbms_sql.NATIVE);
dbms_sql.define_array(c,1,n_tab,10,indx);
d:
=dbms_sql.fetch_rows(c);
dbms_sql.column_value(c,1,n_tab);
exitwhend!
RAISE;
DEFINE_COLUMN
Definesacolumntobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_column(
columnIN<
DEFINE_COLUMN_CHAR
Undocdbms_sql.define_column_char(
columnINCHARCHARACTERSETANY_CS,
column_sizeININTEGER);
Seedefine_columninfinaldemo
DEFINE_COLUMN_LONG
DefinesaLONGcolumntobeselectedfromthegivencursor,usedonlywithSELECTstatementsdbms_sql.define_column_long(
positionININTEGER);
DEFINE_COLUMN_RAW
Undocdbms_sql.define_column_raw(
columnINRAW,
DEFINE_COLUMN_ROWID
Undocdbms_sql.define_column_rowid(
columnINROWID);
DESCRIBE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 使用 Oracle DBMSSQL 执行 动态 SQL 语句