那就来简化一下的
CREATE PROCEDURE test @sdate varchar(8)
AS
declare @sql varchar(8000)
set @sql='select count(1) a from history_thunder t1 left join history_thunder t2 on t1.id = t2.id+1 where sqrt(power(cast(t2.latitude as float)-cast(t1.latitude as float),2)+power(cast(t2.longitude as float)-cast(t1.longitude as float),2))>0.5 and t1.thunder_date='''+@sdate+''''
exec(@sql)
go
CREATE PROCEDURE test @sdate varchar(8)
AS
declare @sql varchar(8000)
set @sql='select count(1) a from history_thunder t1 left join history_thunder t2 on t1.id = t2.id+1 where sqrt(power(cast(t2.latitude as float)-cast(t1.latitude as float),2)+power(cast(t2.longitude as float)-cast(t1.longitude as float),2))>0.5 and t1.thunder_date='''+@sdate+''''
exec(@sql)
go
IS
sql varchar2(8000);sql :='select count(1) a from history_thunder t1 left join history_thunder t2 on t1.id = t2.id+1 where sqrt(power(cast(t2.latitude as float)-cast(t1.latitude as float),2)+power(cast(t2.longitude as float)-cast(t1.longitude as float),2))>0.5 and t1.thunder_date='''+sdate+''''EXECUTE IMMEDIATE sql;
2 as
3 type cursorType is ref cursor;
4 end;
5 /Package created.Elapsed: 00:00:00.08
super@ora81(8.1.6)>create or replace procedure test111
2 ( p_cursor out li2_type.cursorType)
3 is
4 begin
5 open p_cursor for select * from user_tables where rownum<10;
6 end;
7 /Procedure created.Elapsed: 00:00:00.04
super@ora81(8.1.6)>var
SP2-0568: No bind variables declared.
super@ora81(8.1.6)>desc
Usage: DESCRIBE [schema.]object[@db_link]
super@ora81(8.1.6)>var a int
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR ] ]
super@ora81(8.1.6)>var a refcursor
super@ora81(8.1.6)>exec test111(:a)PL/SQL procedure successfully completed.Elapsed: 00:00:00.00
super@ora81(8.1.6)>print :aTABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ---------------
AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOC
---------- ---------- ----------- ------------------------- -----------------
DDL_LOG TOOLS
7367 0 39 7367 LI2_SESSON_WAIT SYSTEM
LI2_SQLAREA DATAMOVE
1104 0 344 0
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ---------------
AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOC
---------- ---------- ----------- ------------------------- -----------------
PLAN_TABLE SYSTEM
SQLN_EXPLAIN_PLAN SYSTEM
TMP TOOLS
0 0 0 0
6 rows selected.Elapsed: 00:00:00.02
PROCEDURE test1(sdate IN varchar2,rc out dstype)
is
tsql varchar2(400);
begin
tsql :='select * from Testtable where dm=:sdm';--sdm将外部读取
open rc for tsql using sdate;--using sdate表示用sdate代替上述tsql中的sdm标量
end;
将返回一个Testtable中dm字段等于sdate的记录集,rc为ref cursor。这个只是包体部分,希望对搂主有帮助。