存储过程笔记

存储过程笔记

存储过程

CREATE OR REPLACE PROCEDURE selname_emp_proc(appid IN VARCHAR2,appname OUT VARCHAR2) AS BEGIN SELECT app_name INTO appname FROM epp_ext_app_fun f WHERE f.app_id=appid; dbms_output.put_line(appname); END; ----调用 DECLARE appname VARCHAR2(60); BEGIN selname_emp_proc('DKYXTBG',appname); END; CALL update_emp_proc('DKYXTBG','wangwu'); CREATE OR REPLACE procedure selAll_emp_proctwo AS CURSOR sel_emp IS SELECT * FROM EPP_EXT_APP_FUN;--定义游标,该游标指向查询结果 rowresult EPP_EXT_APP_FUN%ROWTYPE; BEGIN OPEN sel_emp;--打开游标 LOOP FETCH sel_emp INTO rowresult;--将游标中的值赋给rowresult EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环 dbms_output.put_line('员工名：'||rowresult.APP_NAME||'工资：'||rowresult.APP_ID); END LOOP; CLOSE sel_emp;--关闭游标 END; CALL selAll_emp_proctwo(); Create or replace PROCEDURE searchEmpByDept( indeptno IN varchar, empcur OUT sys_refcursor, errorMsg OUT varchar) IS BEGIN errorMsg:=''; OPEN empcur FOR SELECT * FROM epp_ext_app_fun WHERE app_id = indeptno; EXCEPTION WHEN OTHERS THEN errorMsg:= sqlerrm;-- sqlcode是异常编号，sqlerrm是异常的详细信息 END searchEmpByDept; declare errorMsg varchar(1000); empcur sys_refcursor; emp ydc.epp_ext_app_fun%rowtype; begin searchEmpByDept('DKYJJFLGL',empcur, errorMsg); if errorMsg is not null then dbms_output.put_line(errorMsg); end if; loop fetch empcur into emp; EXIT WHEN empcur%notfound ; dbms_output.put_line(emp.app_name); end loop; close empcur; end; -------------------------------------------------------------------------------DKYJJFLGLcreate or replace procedure p_test(p_id in varchar2,p_cur out sys_refcursor,pscount out varchar)assel_emp sys_refcursor;CURSOR sel_empTwo IS SELECT * FROM EPP_EXT_APP_FUN where app_id='DKYJJFLGL';eep EPP_EXT_APP_FUN%ROWTYPE; cha varchar2(60) :='DKYJJFLGL';i number;stdId varchar2(50);begin OPEN sel_emp FOR SELECT * FROM EPP_EXT_APP_FUN where app_id='DKYXTBG';--定义游标,该游标指向查询结果 DKYCWGK if p_id is not null then open p_cur for select * from epp_ext_app_fun where app_id = p_id; dbms_output.put_line('返回当前id:'||p_id||'数据'); else open p_cur for select * from epp_ext_app_fun where app_id = cha; dbms_output.put_line('返回当前常规数据'); end if; i:=1; loop fetch sel_emp into stdId; exit when sel_emp%NOTFOUND; dbms_output.put_line(stdId); dbms_output.put_line(sel_emp); end loop; -- for eep in sel_emp LOOP --dbms_output.put_line('返回当前id:'||rowresult.app_name||'数据'); -- LOOP end;end p_test;-------------------------------------------------------------------------------declare charsname Varchar2(50):= '' ;--DKYXTBGempcur sys_refcursor; emp ydc.epp_ext_app_fun%rowtype; begin p_test(charsname,empcur); loop fetch empcur into emp; EXIT WHEN empcur%notfound ; dbms_output.put_line(emp.app_name||'ss--------s999ss'||emp.app_id); end loop; close empcur; end; ---------------------------------------------------------------------------------创建个小存储create or replace procedure p_test6(charname in varchar2,rest out varchar2) --p_test6 in如参数 out出参数as --搞点定义 chas varchar2(50) :='迪拜'; --初始化定义参数 empcur sys_refcursor; --定义游标集合 emp ydc.epp_ext_app_fun%rowtype; --集合类模式 begin --开始干活 if charname = 'true' then --如果传入参数等于true dbms_output.put_line('哈哈哈王五在的'); --输出log日志 open empcur for select * from epp_ext_app_fun; --给游标集合赋值 loop --循环开始 fetch empcur into emp; --大开集合到类方式 EXIT WHEN empcur%notfound ; --集合结束方式最后一个对象 dbms_output.put_line(emp.app_name||'ss--------s999ss'||emp.app_id); end loop; --结束遍历 close empcur; --关闭游标 else -- 否则 dbms_output.put_line('哇哈哈王五去旅游了地点是:'||chas); end if; --结束if 判断 rest := chas; --给返回阐述赋值end p_test6; --结束存储-------------------------------------------------------------------------------call p_test6('true', );-- ---------------------------------------------------------------------------------有返回值测试declare--ready charsname Varchar2(50):= '' ;--定义返回值 begin--开始 p_test6('true',charsname ); dbms_output.put_line(charsname); end;--结束-------------------------------------------------------------------------------create table hone()------------------------------------------------------------------------------------------------------create or replace type myvarray_list is varray(10) of varchar2(50); -- 创建零时数组create or replace procedure p_test9(vcs in varchar2) as cursor epp is select * from epp_ext_app_fun f ; empcur sys_refcursor; str varchar2(50); vaArray myvarray_list;begin vaArray := myvarray_list('123','222','333'); str:='rrr'; vaArray(3) := str; if vcs = '1' then open empcur for select * from epp_ext_app_fun f ; DBMS_OUTPUT.put_line('111111111111111----s' ); for x in 1..vaArray.count loop dbms_output.put_line('p_varlist('||x||')='||vaArray(x)); end loop; elsif vcs = '2' then for ex in epp loop if ex.app_id = 'DKYXTBG' then DBMS_OUTPUT.put_line('2222222222222222--------s'||ex.app_id); else DBMS_OUTPUT.put_line('ssssssssssssssss--------s'||ex.app_id); end if; end loop; else insert into susers(sid,sname,szw) select f.id,f.app_id,f.app_name from epp_ext_app_fun f where f.app_id = 'DKYXTBG'; DBMS_OUTPUT.put_line('3333333333333333----s' ); end if;end p_test9;------------------------------------------------------------------------------------------------------ call p_test9('1'); exec call p_test9('1',myvarray_list('Oracle','DB2','Sql Server','mySql','DBA'));create table susers (sid varchar2(1111) primary key,sname varchar2(1111),szw varchar2(1111) )select * from susers------------------------------------------------------------------------------------------------------ create or replace procedure p_test01(parmarsname in varchar2)as sName varchar2(500); rqmy date;begin rqmy := to_date(parmarsname,'yyyy-MM-dd'); sName := parmarsname; dbms_output.put_line('进入数据'||to_char(rqmy,'yyyyMM'));end p_test01;call p_test01('2017-02-06');------------------------------------------------------------------------------------------------------ --oracle 集合create or replace procedure p_test02(parmarsname in varchar2,rest out sys_refcursor)as --创建对象 TYPE c_users IS RECORD (id number,name varchar2(30)); ---根据自定义数据类型创建一个集合 TYPE c_user_array IS TABLE OF c_users INDEX BY BINARY_INTEGER; ---集合对象 user_array c_user_array; ---数据对象 user c_users; ---计数器 v_counter number; cursor epp is select * from epp_ext_app_fun ;begin --user.id:=1; user.name:='迪丽热巴'; user_array(user.id):=user; --user.id:=2; user.name:='貂蝉'; user_array(user.id):=user; v_counter := 1; for ex in epp loop user.id := v_counter; user.name := ex.app_name; user_array(user.id) := user; v_counter := v_counter + 1; end loop; for i in 1..user_array.count loop DBMS_OUTPUT.put_line(user_array(i).id||'...'||user_array(i).name); end loop; -- open rest for user_array; end p_test02;--------------------------------------------------declare charsname Varchar2(50):= '' ;--DKYXTBGempcur sys_refcursor; emp ydc.epp_ext_app_fun%rowtype; begin p_test02(charsname,empcur); --loop fetch empcur into emp; EXIT WHEN empcur%notfound ; -- dbms_output.put_line(emp.app_name||'ss--------s999ss'||emp.app_id); -- end loop; close empcur; end;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。