| 订阅 | 在线投稿
分享
 
 
 

Oracle数据库中获取数据的存储过程示例

2008-08-06 07:16:30  编辑来源:互联网  宽屏版  评论

本文为【Oracle数据库中获取数据的存储过程示例】的汉字拼音对照版显示拼音

>>congyigebiaoAdequdeshujucharulingyigebiaoBzhong

1duiyubiaoAhebiaoBlianggebiaojiegouwanquanxiangtongdehuaziduangeshuxiangyingziduandeleixingdengdengkeyi使shiyong

INSERT INTO B SELECT * FROM A;

INSERT INTO B(field1,field2,field3) select A.field1,A.field2A.field3 from A;

2 duiyulianggebiaoruguoziduanshubuyiyangdanshiyoujigeziduandejiegouyiyangshileisiyufuziguanxibixu使shiyong INSERT INTO B(field1,field2) select A.field1,A.field2 from A;

1.yongdaishenshudeyoubiaoshixianinsertgongneng

CREATE OR REPLACE PROCEDURE GET_DATA(

-- shenshuliebiao

n_task_id IN number, --renwubianhao

v_task_name IN varchar2, --renwumingcheng

v_name IN varchar2 -- mingcheng

)

-----------------------------------------------

-- PROCEDUREming GET_DATA --

-- chulineirong congshujuyuanbiaoqudefuhetiaojiandedeshujucharudaomubiaoshujubiao: --

-- yinshu n_tas_id renwuID, --

-- v_task_namek renwumingcheng, --

-- v_bdw_name duishujuyuanbiaoxianzhitiaojian:bendiwangmingcheng --

-----------------------------------------------

IS

--charuxingshukongzhi

i_count number(5);

--qushujuyoubiaoDATA_CURIN_NAME

--shenshubendiwangmingchengIN_NAME

CURSOR DATA_CUR(IN_NAME VARCHAR2) IS /**zhuyishenshudedingyizhongbudaijingdu**/

SELECT *

FROM GET_DATA_SRC A

WHERE A.NAME = IN_NAME;

BEGIN

--jishuqikongzhicharuxingshu

i_count := 0;

--xunhuancharushuju

FOR MYCUR IN DATA_CUR(v_name) LOOP

INSERT INTO ABC(

ROW_ID,

TASK_ID,

TASK_NAME,

GET_DATA_DT,

CUST_ID,

ASSIGN_FLAG,

DEAL_DATE

)VALUES(

SEQ_KD.NEXTVAL,

N_TASK_ID,

V_TASK_NAME,

SYSDATE,

MYCUR.CUST_ID,

'N',

NULL

);

--chengxushiyongjieduanyihouhuishanchusatrt

i_count := i_count + 1;

IF i_count >100 THEN

COMMIT;

RETURN;

END IF;

--chengxushiyongjieduanyihouhuishanchuend

END LOOP;

--shujucommit,chengxutiaoshijieduanbimiandaliangshujuzanshiguanbi

--commit;

--------liwaichulibufen----------------------------

EXCEPTION

WHEN OTHERS THEN

rollback;

END SRBZ_GET_SRBZ_KD_SPEED;

/

2.使shiyongyujupinjiefashixianinsertgongneng

zhu2.1zifuchuanchangliangdeyinhao 2.2 bianliangdeyinhao

CREATE OR REPLACE PROCEDURE ABC(

-- shenshuliebiao

task_id IN number, --renwubianhao

task_name IN varchar2, --renwumingcheng

in_NAME IN varchar2 --mingcheng

)

--------------------------- PROCEDUREming ABC

-- chulineirong congshujuyuanbiaoqudefuhetiaojiandedeshujucharudaomubiaoshujubiao

-- yinshu table_src shujuyuanbiao

-- table_to shujucharumubiaobiao

-- in_bdw_name duishujuyuanbiaoxianzhitiaojian:bendiwangmingcheng

---------------------------------------------

IS

--INSERT_STRING dongtaishengchengdeinsertyujucunfangchu

INSERT_STRING VARCHAR2(1000);

--shujuyuanbiaocunfangchu

tableSrc varchar2(500);

--shujucharumubiaobiaocunfangchu

tableTarget varchar2(1000);

--shujumubiaobiaoxuyaocharuzhideziduancunfangchu

StrFields varchar2(1000);

BEGIN

--shujuyuanbiaoweiAAA

tableTarget := ' BBB ';

--shujulaiyuan

tableSrc := ' select seq_kd.nextval,sysdate,'||task_id||','''||task_name||''', '

||'A.* from AAA A'

||' WHERE A.value='||'''IP-VPDN'''

||'AND A.remark IS NULL';

--xuyaocharudeziduan

StrFields := '(ROW_ID,GET_DATA_DT,TASK_ID,TASK_NAME,COST_CTR,SERVICE_ID,'

||'SI_ADDR,SI_SERVER_ID)';

--shengchenginsertyuju

INSERT_STRING := 'INSERT INTO ' || tableTarget || StrFields || tableSrc ;

--zhixinginsertyuju

execute immediate INSERT_STRING;

--tijiaoshiwu

commit;

--------liwaichulibufen-------------------------------------------------------

EXCEPTION

--yichangdepaochu

WHEN OTHERS THEN

--DBMS_OUTPUT.PUT_LINE('chuliguochengzhongchucuochengxu退tuichuweizhixingxiangguanneiwu');

rollback;

END SRBZ_GET_SRBZ_KD_GSVPDN_FREE;

/

原文
>>从一个表A的取得数据插入另一个表B中? (1)对于表A和表B两个表结构完全相同的话〔字段个数,相应字段的类型等等〕,可以使用 INSERT INTO B SELECT * FROM A; INSERT INTO B(field1,field2,field3) select A.field1,A.field2,A.field3 from A; (2) 对于两个表如果字段数不一样,但是有几个字段的结构一样时〔类似于父子关系〕,必须使用 INSERT INTO B(field1,field2) select A.field1,A.field2 from A; 1.用带参数的游标实现insert功能: CREATE OR REPLACE PROCEDURE GET_DATA( -- 参数列表: n_task_id IN number, --任务编号 v_task_name IN varchar2, --任务名称 v_name IN varchar2 -- 名称 ) ----------------------------------------------- -- PROCEDURE名 :GET_DATA -- -- 処理内容 :从数据源表取得符合条件的的数据插入到目标数据表: -- -- 引数 :n_tas_id 任务ID, -- -- v_task_namek 任务名称, -- -- v_bdw_name 对数据源表限制条件:本地网名称 -- ----------------------------------------------- IS --插入行数控制 i_count number(5); --取数据游标:DATA_CUR(IN_NAME) --参数:本地网名称:IN_NAME CURSOR DATA_CUR(IN_NAME VARCHAR2) IS /**注意:参数的定义中不带精度**/ SELECT * FROM GET_DATA_SRC A WHERE A.NAME = IN_NAME; BEGIN --计数器,控制插入行数 i_count := 0; --循环插入数据 FOR MYCUR IN DATA_CUR(v_name) LOOP INSERT INTO ABC( ROW_ID, TASK_ID, TASK_NAME, GET_DATA_DT, CUST_ID, ASSIGN_FLAG, DEAL_DATE )VALUES( SEQ_KD.NEXTVAL, N_TASK_ID, V_TASK_NAME, SYSDATE, MYCUR.CUST_ID, 'N', NULL ); --程序试用阶段,以后会删除satrt i_count := i_count + 1; IF i_count >100 THEN COMMIT; RETURN; END IF; --程序试用阶段,以后会删除end END LOOP; --数据commit,程序调试阶段避免大量数据,暂时关闭 --commit; --------例外处理部分---------------------------- EXCEPTION WHEN OTHERS THEN rollback; END SRBZ_GET_SRBZ_KD_SPEED; / 2.使用语句拼接法实现insert功能: 注:2.1字符串常量的引号 2.2 变量的引号 CREATE OR REPLACE PROCEDURE ABC( -- 参数列表: task_id IN number, --任务编号 task_name IN varchar2, --任务名称 in_NAME IN varchar2 --名称 ) --------------------------- PROCEDURE名 :ABC -- 処理内容 :从数据源表取得符合条件的的数据插入到目标数据表 -- 引数 :table_src 数据源表 -- table_to 数据插入目标表 -- in_bdw_name 对数据源表限制条件:本地网名称 --------------------------------------------- IS --INSERT_STRING 动态生成的insert语句存放处 INSERT_STRING VARCHAR2(1000); --数据源表存放处 tableSrc varchar2(500); --数据插入目标表存放处 tableTarget varchar2(1000); --数据目标表需要插入值的字段存放处 StrFields varchar2(1000); BEGIN --数据源表为:AAA tableTarget := ' BBB '; --数据来源 tableSrc := ' select seq_kd.nextval,sysdate,'||task_id||','''||task_name||''', ' ||'A.* from AAA A' ||' WHERE A.value='||'''IP-VPDN''' ||'AND A.remark IS NULL'; --需要插入的字段 StrFields := '(ROW_ID,GET_DATA_DT,TASK_ID,TASK_NAME,COST_CTR,SERVICE_ID,' ||'SI_ADDR,SI_SERVER_ID)'; --生成insert语句 INSERT_STRING := 'INSERT INTO ' || tableTarget || StrFields || tableSrc ; --执行insert语句 execute immediate INSERT_STRING; --提交事务 commit; --------例外处理部分------------------------------------------------------- EXCEPTION --异常的抛出 WHEN OTHERS THEN --DBMS_OUTPUT.PUT_LINE('处理过程中出错,程序退出,未执行相关内务'); rollback; END SRBZ_GET_SRBZ_KD_GSVPDN_FREE; /
󰈣󰈤
 
 
 
>>返回首页<<
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有