存储过程是:
CREATE OR REPLACE PACKAGE nb_supFind AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel(
p_Start IN NUMBER,
p_End IN NUMBER,
p_Region IN NUMBER,
p_col IN VARCHAR2,
p_table IN VARCHAR2,
v_ADDV OUT myrctype
);
END nb_supFind;create or replace package body NB_SUPFIND AS
PROCEDURE RecSel(
p_Start IN NUMBER,
p_End IN NUMBER,
p_Region IN NUMBER,
p_col IN VARCHAR2,
p_table IN VARCHAR2,
v_ADDV OUT myrctype
) IS
strsql VARCHAR2(1000);
BEGIN
if p_Region <> 0 then
strsql:='SELECT ('||p_col||')
FROM '||p_table|| ' WHERE id <= '||p_End||' AND id>='|| p_Start||' ';
else
strsql:='SELECT '||p_col||' FROM '||p_table||' ';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
OPEN v_ADDV FOR strsql;
END RecSel;
END NB_SUPFIND; ASP代码是:
<!-- #include file = "adovbs.inc" --><%
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
connstr="Provider=OraOLEDB.Oracle;Data Source=system;User Id=pass; Password=ordb"
conn.Open connstr
Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType =4
CMD.CommandTimeout = 0
CMD.Prepared = true
CMD.CommandText = "{call nb_supfind.recsel(?,?,?,?,?)}"
cmd.CommandType = adCmdText
CMD.Parameters.Append CMD.CreateParameter("p_Start", 5, 1,20,1000050)
CMD.Parameters.Append CMD.CreateParameter("p_End", 5, 1,20,1000001)
CMD.Parameters.Append CMD.CreateParameter("p_Region", 5, 1,20,1)
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,"id")
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,"vv_dl_info")
set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()
While Not Rs.EOF
%>
<%=rs("id")%>&&&&&<%=rs("mc")%>
<%
Rs.MoveNext
Wend
Rs.Close
set Rs=nothing
set cmd=nothing
%>出现的问题是:
ORA-06550: line 1, column 39: PLS-00103: Encountered the symbol "{" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in mod not range rem => .. <> or != or ~= >= <= <> and or like between is null is not || indicator is dangling
CREATE OR REPLACE PACKAGE nb_supFind AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel(
p_Start IN NUMBER,
p_End IN NUMBER,
p_Region IN NUMBER,
p_col IN VARCHAR2,
p_table IN VARCHAR2,
v_ADDV OUT myrctype
);
END nb_supFind;create or replace package body NB_SUPFIND AS
PROCEDURE RecSel(
p_Start IN NUMBER,
p_End IN NUMBER,
p_Region IN NUMBER,
p_col IN VARCHAR2,
p_table IN VARCHAR2,
v_ADDV OUT myrctype
) IS
strsql VARCHAR2(1000);
BEGIN
if p_Region <> 0 then
strsql:='SELECT ('||p_col||')
FROM '||p_table|| ' WHERE id <= '||p_End||' AND id>='|| p_Start||' ';
else
strsql:='SELECT '||p_col||' FROM '||p_table||' ';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
OPEN v_ADDV FOR strsql;
END RecSel;
END NB_SUPFIND; ASP代码是:
<!-- #include file = "adovbs.inc" --><%
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
connstr="Provider=OraOLEDB.Oracle;Data Source=system;User Id=pass; Password=ordb"
conn.Open connstr
Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType =4
CMD.CommandTimeout = 0
CMD.Prepared = true
CMD.CommandText = "{call nb_supfind.recsel(?,?,?,?,?)}"
cmd.CommandType = adCmdText
CMD.Parameters.Append CMD.CreateParameter("p_Start", 5, 1,20,1000050)
CMD.Parameters.Append CMD.CreateParameter("p_End", 5, 1,20,1000001)
CMD.Parameters.Append CMD.CreateParameter("p_Region", 5, 1,20,1)
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,"id")
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,"vv_dl_info")
set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()
While Not Rs.EOF
%>
<%=rs("id")%>&&&&&<%=rs("mc")%>
<%
Rs.MoveNext
Wend
Rs.Close
set Rs=nothing
set cmd=nothing
%>出现的问题是:
ORA-06550: line 1, column 39: PLS-00103: Encountered the symbol "{" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in mod not range rem => .. <> or != or ~= >= <= <> and or like between is null is not || indicator is dangling
解决方案 »
- Number类型的计算结果不对。
- 求思路,关于程序设计。极为复杂
- 在线等,急急急
- Oracle Performance: Hotsos 有人用吗?分享分享
- 求一复杂的函数,不是过程,利用其他表存储的字符串座位条件数据
- 高手请进(谢谢!!)为什么无法启动oracleoraHome92Management Server?
- 一个关于EXISTS的问题
- 求救!!!!用select ...start with ... connect by生成树结构后如何排序?
- vc occi连接Oracle数据库
- 触发器触发存储过程的问题
- TEMP表空间占用99.99%,如何降低TEMP的占用率
- 如何从低版本将数据库升到高版本
FROM '||p_table|| ' WHERE id <= '||p_End||' AND id>='|| p_Start||' ';
……
CMD.Parameters.Append CMD.CreateParameter("p_Start", 5, 1,20,1000050)
CMD.Parameters.Append CMD.CreateParameter("p_End", 5, 1,20,1000001)
CMD.Parameters.Append CMD.CreateParameter("p_Region", 5, 1,20,1)
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,"id")
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,"vv_dl_info") strsql := 'select (id) from vv_dl_info where id <=1000050 and id>=1000001'这句感觉不太对
CMD.ActiveConnection = conn CMD.Properties("PLSQLRSet") = True
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~试试看
CMD.CommandType =4
CMD.CommandTimeout = 0
CMD.Prepared = true
var adVarChar = 200;
估计是整型是对应3,而5是Double型.
CMD.Parameters.Append CMD.CreateParameter("p_Start",3, 1,20,1000050)
CMD.Parameters.Append CMD.CreateParameter("p_End", 3, 1,20,1000001)
CMD.Parameters.Append CMD.CreateParameter("p_Region",3, 1,20,1)
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,"id")
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,"vv_dl_info")
在ASP中调用动态ORACLE包\过程:1.所查视图表结构:
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
XK_SX NUMBER
XK_WSX_DJ NUMBER
XK_WSX_XJ NUMBER
XK_WSX_HJ NUMBER
F_ZS NUMBER
F_WZS_DJ NUMBER
F_WZS_XJ NUMBER
F_WZS_HJ NUMBER
SW_CS_DJ NUMBER
SW_CS_XJ NUMBER
SW_CS_HJ NUMBER
BZ CHAR(100)
YY CHAR(4)
XZQ NUMBER
WRR2 NUMBER
WRR3 NUMBER
WRR2NAME CHAR(50)
WRR3NAME CHAR(50)
ADDVNM CHAR(9)2.在ORACLE中包的定义:CREATE OR REPLACE PACKAGE nb_supFind
AS
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel( p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
);
END nb_supFind;CREATE OR REPLACE PROCEDURE NB_SURERFIND (
--参数声明
p_StartDate IN VIEW_NB_TYGL.YY%TYPE,
p_EndDate IN VIEW_NB_TYGL.YY%TYPE,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_item IN NUMBER,
p_table IN VARCHAR2,
)
AS
PROCEDURE RecSel(
p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
) IS
strsql VARCHAR2(500);
BEGIN
IF p_flag = 1 then
if p_Region = 'NULL' then
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= '||p_EndDate||' AND YY>='|| p_StartDate||'
GROUP BY ADDVNM,YY';
else
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region||
''' GROUP BY ADDVNM,YY';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
--打开动态游标
OPEN v_ADDV FOR strsql;
END IF;
END RecSel;
END nb_supFind; 3.在SQL PLUS中测试:
set serveroutput on
DECLARE
p_rc NB_SUPFIND.myrctype;
str varchar2(50);
BEGIN
NB_SUPFIND.RecSel('2000','2000','北京',1,'YSQ_SL','VIEW_NB_QSXK',p_rc);
LOOP
FETCH p_rc INTO str;
DBMS_OUTPUT.PUT_LINE(str);
EXIT WHEN p_rc%NOTFOUND;
END LOOP;
END;成功!4.在ASP里的调用
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
'以下两句含在globo文件中
connstr="Provider=MSDAORA;Password=water;User ID=water;Data Source=oradb_netserver"
Session("connstr")=connstr
conn.Open Session("connstr") Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType = adCmdText
CMD.CommandTimeout = 0
CMD.Prepared = true CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?)}" 这里只写输入不写输出
CMD.Parameters.Append CMD.CreateParameter("p_StartDate",129,1,4,"2000")'开始年份
CMD.Parameters.Append CMD.CreateParameter("p_EndDate",129,1,4,EndYr) '结束年份
CMD.Parameters.Append CMD.CreateParameter("p_Region",129,1,9,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,4,1) '标识分区
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,Item) '选中项目名
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,Tname) '得到表名 set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()
调试已通过~!:-)