PL/SQLʹö̬SQL  
ߣFLYTIGER 

PL/SQLƹУܶʹö̬sqlĵطoracleϵͳṩDMBS_SQL԰⡣
(һ)
DBMS_SQLϵͳṩ˺ܶຯ,ڼҪʹƵʽϸߵļ:

function open_cursor:һ̬α,һ;

procedure close_cursor(c in out integer);رһ̬α,Ϊopen_cursor򿪵α;

procedure parse(c in integer, statement in varchar2, language_flag in integer):Զ̬αṩsqlн,Cʾα,statementΪsql,language-flagΪsqloracle汾,һV6,V7native(ڲdatabase汾ʱ,ʹnative);

procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):嶯̬αܵõĶӦֵ,cΪ̬α,positonΪӦ̬sqlеλ(1ʼ),columnΪֵӦı,Ϊκ,column_sizeֻcolumnΪ峤ȵʹVARCHAR2,CHAR(ùкܶ,˴ֻһʹõͽб);

function execute(c in integer):ִα,شһ,(insert,delete,update,selectԿԺ);

function fetch_rows(c in integer):αѭȡ,һ,Ϊ0ʱʾѾȡαĩ;

procedure column_value(c in integer, position in integer, value):ȡõαݸֵӦı,cΪα,positionΪλ,valueΪӦı;

procedure bind_variable(c in integer, name in varchar2, value):嶯̬sql(DML)Ӧֶεֵ,cΪα,nameΪֶ,valueΪֶεֵ;

ڳоʹõļ,oracleṩdbmssql.sql

()һ
һselect,ʹö̬sqlҪ¼:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
dml(insert,update)Ҫ¼:
open cursor--->parse--->bind variable--->execute--->close cursor;
deleteֻҪ¼:
open cursor--->parse--->execute--->close cursor;

()尸
ͱϵͳĳһ
ùΪһƱ߼,ݴӼʱݱȡ,ռߵĹʽ,Щݽм,浽߱.
--**********************************
--procedure name:R_Ma_Main
--ڲPIDƱ,PENDʱ,pintervalʱ,totabĿݱ
--úR_GetSql1,R_GetSql2
--ܣ㵥֧Ʊma
--ʱ䣺2001-06-20
--**********************************
create or replace procedure R_Ma_Main
(
pid varchar2,
pend varchar2,
pinterval varchar2,
totab varchar2
) is 

--
type Date_type is table of varchar2(12) index by binary_integer;
type Index_type is table of number index by binary_integer;

TempDate Date_Type;--ʱ
TempIndex Index_Type;--Ʊ̼
TempMa Index_Type;--ma

cursor1 integer;--α
cursor2 integer;--α
rows_processed integer;--ִα귵

TempInter integer;--ֵ
TempVal integer;--ʱ
TempSql varchar2(500);--̬sql
MyTime varchar2(12);--ʱ
MyIndex number;--ֵ
MidIndex number;--м
i integer := 999;
j integer;
begin
TempInter := to_number(substr(pinterval,1,4));
TempVal := to_number(substr(pinterval,5,2));
TempSql := R_GetSql1(pid, pend, TempVal);--õѡݵsql

--õļʱ,α浽
cursor1 := dbms_sql.open_cursor; --α
dbms_sql.parse(cursor1, TempSql, dbms_sql.native); --̬sql,ȡֶ,ʱ估۸,ʱ14λvarchar2ʾ
dbms_sql.define_column(cursor1, 1, MyTime, 12); --ֱsqlиֶӦ
dbms_sql.define_column(cursor1, 2, MyIndex);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows(cursor1) > 0 then
begin
dbms_sql.column_value(cursor1, 1, MyTime);
dbms_sql.column_value(cursor1, 2, MyIndex);
TempDate(i) := MyTime;
TempIndex(i) := MyIndex;
i := i - 1;--ķ
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cursor1);

--ȡõ,
if i > 999-TempInter then
goto JumpLess;
end if;

--ʼм
MidIndex := 0;
TempIndex(i) := 0;
for j in i..i+TempInter-1 loop
MidIndex := MidIndex + TempIndex(j);
end loop; 

--ζԵݼmaֵ,浽ma
for j in i+TempInter..999 loop
MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);
TempMa(j) := MidIndex/TempInter;
end loop; 

if TempVal < 6 then--ǷӸma
begin
cursor2 := dbms_sql.open_cursor;
TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';
dbms_sql.parse(cursor2, TempSql, dbms_sql.native); 
for j in i+TempInter..999 loop
dbms_sql.bind_variable(cursor2, 'r_no', pid);
dbms_sql.bind_variable(cursor2, 'i_interval', pinterval);
dbms_sql.bind_variable(cursor2, 'i_time', TempDate(j));
dbms_sql.bind_variable(cursor2, 'i_index', TempMa(j));
rows_processed := dbms_sql.execute(cursor2);--
end loop;
end;
end if; 
commit;
dbms_sql.close_cursor(cursor2);
--
<<JumpLess>>
null;

--exception,޹ر
end;
/

()˹۵
ʹdbms_sqlϵͳĹ,䷽򵥶ֲʧ,ҪעһЩ:
1,ƹ,αĲвʡԵĲ,һʡĳһ,̼ȸʧ,ڳβδԸαйرղ,ٴεùʱִ.
2,dbms_sql˿һselect,insert,update,deleteȾ̬sqlڹ,ִcreateDDL,ִиʱӦʽִûӦϵͳȨ,create table.ֻopen cursor--->prase--->close cursor.

Ϊڹжdbms_sqlһ㿴,֮,ָ.
˽dbms_sql,Ķdbmssql.sqlļ.
 
