{"id":6255,"date":"2024-08-14T13:01:03","date_gmt":"2024-08-14T05:01:03","guid":{"rendered":""},"modified":"2024-08-14T13:01:03","modified_gmt":"2024-08-14T05:01:03","slug":"\u7ecf\u5178SQL\u5b66\u4e60\u7b14\u8bb0 (\u516b) - pl\/sql\u7f16\u7a0b\u4e8c","status":"publish","type":"post","link":"https:\/\/mushiming.com\/6255.html","title":{"rendered":"\u7ecf\u5178SQL\u5b66\u4e60\u7b14\u8bb0 (\u516b) - pl\/sql\u7f16\u7a0b\u4e8c"},"content":{"rendered":"

\n <\/path> \n<\/svg> <\/p>\n

pl\/sql\u7f16\u7a0b<\/strong><\/p>\n

\u4f8b\u5b50:\u67e5\u8be2\u90e8\u95e8\u8868\u7684\u4e09\u79cd\u5faa\u73af --loop<\/span>\u5faa\u73af----------------------------------------------------------------------- declare cursor curl is<\/span> select<\/span> * from<\/span> dept; lrow dept%rowtype; begin<\/span> open curl; loop<\/span> fetch curl into<\/span> lrow; exit<\/span> when curl%notfound; dbms_output.put_line(lrow.deptno||'--'<\/span>||lrow.dname||'--'<\/span>||lrow.loc); end<\/span> loop<\/span>; close curl; end<\/span>; show error; --while<\/span>\u5faa\u73af---------------------------------------------------------------------- declare cursor curl is<\/span> select<\/span> * from<\/span> dept; lrow dept%rowtype; begin<\/span> open curl; fetch curl into<\/span> lrow; while<\/span> curl%found loop<\/span> dbms_output.put_line(lrow.deptno||'--'<\/span>||lrow.dname||'--'<\/span>||lrow.loc); fetch curl into<\/span> lrow; end<\/span> loop<\/span>; close curl; end<\/span>; --for<\/span>\u5faa\u73af------------------------------------------------------------------------ for<\/span> \u53d8\u91cf\u540d in<\/span> \u6e38\u6807 loop<\/span> ..... end<\/span> loop<\/span>; \u6ce8\u610f: 1<\/span>\u3001\u9690\u85cf\u4e86\u6253\u5f00\u6e38\u6807\uff0c\u6240\u4ee5\u6253\u5f00\u6e38\u6807\u4e0d\u9700\u8981\u5199 2<\/span>\u3001\u9690\u85cf\u4e86\u4e00\u4e2afetch\uff1b 3<\/span>\u3001\u9690\u85cf\u4e86\u68c0\u6d4b\u9000\u51fa\u5faa\u73af\u7684\u5c5e\u6027 4<\/span>\u3001\u5173\u95ed declare cursor curl is<\/span> select<\/span> * from<\/span> dept; begin<\/span> for<\/span> lrow in<\/span> curl loop<\/span> dbms_output.put_line(lrow.deptno||'--'<\/span>||lrow.dname||'--'<\/span>||lrow.loc); end<\/span> loop<\/span>; end<\/span>; -------------------------------------------------------------------------------- \u9690\u5f0f\u6e38\u6807 \u5bf9 insert update delete\u64cd\u4f5c sql--\u9690\u5f0f\u6e38\u6807\u7684\u540d\u5b57\uff0cOracle \u7cfb\u7edf\u5b9a\u4e49 sql%\u5c5e\u6027 begin<\/span> insert into<\/span> emp_wzl (empno,ename,job) values(9001<\/span>,'kack'<\/span>,'clerk'<\/span>); dbms_output.put_line(sql%rowcount); delete from<\/span> emp_wzl; dbms_output.put_line(sql%rowcount); end<\/span>; insert into<\/span> emp_wzl (empno,ename,job) values(9004<\/span>,'kack'<\/span>,'clerk'<\/span>); select<\/span> * from<\/span> emp_wzl; -------------------------------------------------------------------------------- \u4f8b\u5b50\uff1a\u67e5\u8be2\u51faemp\u8868\u4e2d\u7684\u524d10<\/span>\u6761\u8bb0\u5f55\uff0c\u5e76\u8f93\u51fa\uff08\u7528 for<\/span> \uff0cwhile<\/span>\uff0cloop<\/span> \u4e09\u79cd\u5faa\u73af\uff09 declare cursor curl is<\/span> select<\/span> * from<\/span> emp where<\/span> rownum<=10<\/span>; begin<\/span> for<\/span> lrow in<\/span> curl loop<\/span> dbms_output.put_line(lrow.empno||'---'<\/span>||lrow.ename); end<\/span> loop<\/span>; end<\/span>; -------------------------------------------------------------------------------- declare cursor curl is<\/span> select<\/span> * from<\/span> emp; begin<\/span> for<\/span> lrow in<\/span> curl loop<\/span> dbms_output.put_line(lrow.empno||'---'<\/span>||lrow.ename); if<\/span> curl%rowcount=10<\/span> then<\/span> exit<\/span>; end<\/span> if<\/span>; end<\/span> loop<\/span>; end<\/span>; \u52a8\u6001\u6e38\u6807 -------------------------------------------------------------------------------- \u5305\uff08package\uff09 \u7ec4\u6210\uff1a \u5305\u8bf4\u660e\u3001\u5305\u4f53 \u5305\u8bf4\u660e\u90e8\u5206\uff1a create<\/span> package \u5305\u540d is<\/span>|as<\/span> <\u516c\u5171\u53d8\u91cf> <\u5b58\u50a8\u8fc7\u7a0b\uff0c\u51fd\u6570\u7684\u58f0\u660e> end<\/span>; \u5305\u4f53\u90e8\u5206\uff1a create<\/span> package body \u5305\u540d is<\/span>|as<\/span> <\u79c1\u6709\u53d8\u91cf> <\u51fd\u6570\u4f53> <\u5b58\u50a8\u8fc7\u7a0b\u4f53> end<\/span>\uff1b drop package \u5305\u540d\uff1b --\u5220\u9664\u5305 create<\/span> package sal_wpac is<\/span> increment emp.sal%type<\/span>; reduce emp.sal%type<\/span>; procedure<\/span> update_sal<\/span>(p_no number,p_sal number)<\/span>;<\/span> procedure<\/span> reduce_sal<\/span>(p_no number,p_sal number)<\/span>;<\/span> end<\/span>; create<\/span> package body sal_wpac is<\/span> procedure<\/span> update_sal<\/span>(p_no number,p_sal number)<\/span>;<\/span> is<\/span> begin<\/span> update emp_wzl set<\/span> sal=sal+p_sal where<\/span> empno=p_no; end<\/span>; procedure<\/span> reduce_sal<\/span>(p_no number,p_sal number)<\/span>;<\/span> is<\/span> begin<\/span> update emp_wzl set<\/span> sal=sal+p_sal where<\/span> empno=p_no; end<\/span>; end<\/span>; select<\/span> * from<\/span> emp_wzl; insert into<\/span> emp_wzl (empno,sal) values(1001<\/span>,2500<\/span>); drop package sal_wpac; --\u67e5\u8be2\u76f8\u5173\u4fe1\u606f\uff0c\u540d\u5b57\u8981\u5927\u5199 select<\/span> * from<\/span> user_procedures where<\/span> object_name='SAL_WPAC'<\/span> type<\/span>='PACKAGE'<\/span>; --\u67e5\u770b\u8fc7\u7a0b\u51fd\u6570\u8fc7\u7a0b\u7684\u4ee3\u7801\uff0c\u540d\u5b57\u8981\u5927\u5199 select<\/span> text from<\/span> user_source where<\/span> name='F_NAME'<\/span>; select<\/span> text from<\/span> user_source where<\/span> name='F_NAME'<\/span> and<\/span> type<\/span>='PACKAGE'<\/span>; --\u5f02\u5e38\uff08exception\uff09 --1<\/span>\u3001\u9884\u5b9a\u4e49\u5f02\u5e38------------------------------------------------------------------- ora-xxxxx; exception when \u5f02\u5e38\u540d then<\/span> ...... when \u5f02\u5e38\u540d then<\/span> ...... when others then<\/span> ...... end<\/span>; \u4f8b\u5b501<\/span>\uff1a\u6839\u636e\u96c7\u5458\u59d3\u540d\uff0c\u6c42\u51fa\u96c7\u5458\u7684\u5de5\u4f5c\u548c\u5de5\u8d44\uff0c\u5e76\u5bf9\u5f02\u5e38\u8fdb\u884c\u5904\u7406 \u5bf9no_data_found\u3001too_many_rows\u6355\u6349\u5f02\u5e38 declare v_job emp.job%type<\/span>; v_sal emp.sal%type<\/span>; begin<\/span> select<\/span> job,sal into<\/span> v_job, v_sal from<\/span> emp where<\/span> ename='&name'<\/span>; exception when no_data_found then<\/span> dbms_output.put_line('\u6ca1\u6709\u53d1\u73b0\u6570\u636e'<\/span>); when too_many_rows then<\/span> dbms_output.put_line('\u8fd4\u56de\u592a\u591a\u6761\u6570\u636e'<\/span>); end<\/span>; \u4f8b\u5b502<\/span>:\u66f4\u65b0\u6307\u5b9a\u7684\u5458\u5de5\u5de5\u8d44\uff0c\u5982\u679c\u5de5\u8d44\u5c0f\u4e8e1500<\/span>\uff0c\u5219\u52a0100<\/span> \u5bf9no_data_found\u3001too_many_rows\u6355\u6349\u5f02\u5e38 select<\/span> * from<\/span> emp_wzl; insert into<\/span> emp_wzl (empno,ename,sal) values(1002<\/span>,'smith'<\/span>,600<\/span>); declare v_sal emp.sal%type<\/span>; begin<\/span> select<\/span> sal into<\/span> v_sal from<\/span> emp where<\/span> ename='&name'<\/span>; if<\/span> v_sal<1500<\/span> then<\/span> update emp_wzl set<\/span> sal=sal+100<\/span> where<\/span> sal<1500<\/span>; else<\/span> dbms_output.put_line('\u4e0d\u9700\u8981\u66f4\u65b0'<\/span>); end<\/span>\uff1b end<\/span>\uff1b --2<\/span>\u3001\u975e\u9884\u5b9a\u4e49\u5f02\u5e38----------------------------------------------------------------- 1<\/span>\u3001\u518d\u5b9a\u4e49\u90e8\u5206\uff0c\u5b9a\u4e49\u5f02\u5e38 \u5f02\u5e38\u540d exception; 2<\/span>\u3001\u628a\u5b9a\u4e49\u597d\u7684\u5f02\u5e38\u548c\u6807\u51c6\u7684oracle\u9519\u8bef\u5173\u8054 pragma exception_init(\u5f02\u5e38\u540d\uff0c\u9519\u8bef\u4ee3\u7801); (\u8981\u5199\u5728\u5b9a\u4e49\u90e8\u5206) 3<\/span>\u3001\u5728exception\u4e2d\u5bf9\u5f02\u5e38\u5904\u7406 ora-02292<\/span> \u4f8b\u5b50\uff1a\u5220\u9664\u6307\u5b9a\u90e8\u95e8\u7684\u8bb0\u5f55\uff0c\u4ee5\u786e\u4fdd\u8be5\u90e8\u95e8\u6ca1\u6709\u5458\u5de5 declare e_deptno exception; pragma exception_init(e_deptno,-02290<\/span>); begin<\/span> delete from<\/span> dept where<\/span> deptno=&no; exception when d_deptno then<\/span> dbms_output.put_line('\u8fdd\u53cd\u4e86\u5916\u952e\u7ea6\u675f\uff0c\u4e0d\u80fd\u5220\u9664\u6570\u636e'<\/span>); end<\/span>; --3<\/span>\u3001\u81ea\u5b9a\u4e49\u5f02\u5e38------------------------------------------------------------------- 1<\/span>\u3001\u5b9a\u4e49\u5f02\u5e38 \u5f02\u5e38\u540d exception 2<\/span>\u3001\u629b\u51fa\u5f02\u5e38 raise<\/span> \u5f02\u5e38\u540d 3<\/span>\u3001exception \u4e2d\u5bf9\u629b\u51fa\u7684\u5f02\u5e38\u8fdb\u884c\u5904\u7406 \u7a7a\u7684\u4ee3\u7801\u6bb5\uff1a ora-20000<\/span>\u5230ora-20999<\/span>\uff1b \u4f8b\u5b50\uff1a\u521b\u5efa\u4e00\u4e2a\u51fd\u6570\uff0c\u67e5\u8be2\u51fa\u6307\u5b9a\u90e8\u95e8\u7684\u5de5\u8d44\u603b\u548c\uff0c\u5176\u4e2d\u8981\u6355\u6349\u90e8\u95e8\u4e3a\u7a7a\u6216\u8005\u90e8\u95e8\u4e3a\u8d1f\u6570\u60c5\u51b5\u8fdb\u884c\u5904\u7406 create<\/span> or<\/span> replace function<\/span> get_sal<\/span>(spno number)<\/span> return<\/span> number<\/span> is<\/span> e_notnull<\/span> exception<\/span>;<\/span> e_error exception; v_income emp.sal%type<\/span>; begin<\/span> if<\/span> spno is<\/span> null then<\/span> raise<\/span> e_null; elsif spno<0<\/span> then<\/span> raise<\/span> e_error; else<\/span> select<\/span> sum(sal) into<\/span> v_income from<\/span> emp where<\/span> deptno=spno; end<\/span> if<\/span>; return v_income; exception when e_null then<\/span> dbms_output.put_line('\u90e8\u95e8\u4e3a\u7a7a'<\/span>); return 0<\/span>; when e_error then<\/span> dbms_output.put_line('\u90e8\u95e8\u7f16\u53f7\u4e3a\u8d1f\u6570'<\/span>); return -1<\/span>; end<\/span>; \/ declare v1 emp.sal%type<\/span>; begin<\/span> v1:=get_sal(-10<\/span>); end<\/span>; -------------------------------------------------------------------------------- raise_application_error(\u5f02\u5e38\u7684\u4ee3\u7801\uff0c\u5f02\u5e38\u4fe1\u606f); create<\/span> or<\/span> replace function<\/span> get_sal1<\/span>(spno number)<\/span> return<\/span> number<\/span> is<\/span> v_income<\/span> emp<\/span>.sal<\/span>%type<\/span>;<\/span> begin<\/span> if<\/span> spno is<\/span> null then<\/span> raise_application_error(-20000<\/span>\uff0c'\u90e8\u95e8\u4e3a\u7a7a'<\/span>); elsif spno<0<\/span> then<\/span> raise_application_error(-20001<\/span>\uff0c'\u90e8\u95e8\u53f7\u4e3a\u8d1f\u6570'<\/span>); else<\/span> select<\/span> sum(sal) into<\/span> v_income from<\/span> emp where<\/span> deptno=spno; end<\/span> if<\/span>; return v_income; end<\/span>; declare v1 emp.sal%type<\/span>; begin<\/span> v1:=get_sal1(null); end<\/span>; --\u7ec3\u4e60------------------------------------------------------------------------------ 1<\/span>\u3001\u7f16\u5199\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u7528\u4e8e\u96c7\u5458\u59d3\u540d\u7684\u9a8c\u8bc1\uff0c\u5982\u679c\u5728emp\u8868\u4e2d\u5b58\u5728\uff0c\u8fd4\u56deY\uff0c\u5426\u5219\u8fd4\u56deN\uff1b create<\/span> or<\/span> replace procedure<\/span> logincheck<\/span>(spname varchar2,flag varchar2)<\/span> is<\/span> v_count<\/span> number<\/span>;<\/span> begin<\/span> select<\/span> count(*) into v_count from emp where ename=spname; if v_count=0 then flag:='N'; else flag:='Y'; end if; end; declare v1 varchar2(2); begin logincheck('dd',v1); dbms_output.put_line(v1); end; 2\u3001\u7f16\u5199\u4e00\u4e2a\u51fd\u6570\uff0c\u8f93\u5165\u96c7\u5458\u7684\u7f16\u53f7\uff0c\u5982\u679c\u8be5\u96c7\u5458\u7684\u5de5\u4f5c\u662f\u7ecf\u7406\uff0c\u800c\u4e14\u5de5\u8d44\u5927\u4e8e5000\uff0c\u8fd4\u56de\uff08\u5de5\u8d44\u4e0a\u6da820%\uff09\uff0c\u5426\u5219\u8fd4\u56de\u5de5\u8d44\u4e0a\u6da840% \u5e76\u7f16\u5199\u4e00\u4e2a\u5757\uff0c\u6765\u904d\u5386emp\u8868 create or replace function addsal(spno number) return number is v_job emp.job%type; v_sal emp.sal%type; begin select job.sal into v_job,v_sal from emp where empno=span; if v_job='MANAGER' then if v_sal>5000 then v_sal:=v_sal*(1+0.2); else v_sal:=v_sal*(1+0.4); end if; end if; return v_sal; end; declare cursor curl is select * from emp; begin for lrow in curl loop dbms_output.put_line(addsal(lrow.empno)); end loop; end; --\u89e6\u53d1\u5668\uff08trigger\uff09--------------------------------------------------------------- \u8bed\u6cd5\uff1a create trigger \u89e6\u53d1\u5668\u540d\u5b57 {before|after} {insert|update|delete[of column[,[cloumn]]]} on \u8868\u540d [for each row] [when \u6761\u4ef6] begin \u89e6\u53d1\u4f53 end\uff1b \u89e6\u53d1\u5668\u7684\u5206\u7c7b\uff1a --dml \u89e6\u53d1\u5668\uff1a\u89e6\u53d1\u8868-------------------------------------------------------------- \u6761\u4ef6\u8c13\u8bcd: inserting\u3001updating\u3001deleting \u5c5e\u6027\uff1a : old \u548c: new \uff1aold \uff1a\u8bbf\u95ee\u64cd\u4f5c\u524d\u7684\u503c \uff1anew insert update delete \uff1a\u8bbf\u95ee\u64cd\u4f5c\u540e\u7684\u503c : old null \u6709 \u6709 : new \u6709 \u6709 null create trigger tr1 after delete on emp_cxp for each row begin dbms_output.put_line('delete emp_cxp'); end; ---------------------------------------- create trigger tr1 after insert or update or delete on emp_cxp for each row begin if inserting then dbms_output.put_line('insert'); elsif updating then dbms_output.put_line('update'); else dbms_output.put_line('delete emp_cxp'); end if; end; create trigger tr1 after insert or update or delete on emp_cxp for each row begin if inserting then dbms_output.put_line('insert'||'----'||:new.empno||'----'||:new.ename); elsif updating then dbms_output.put_line('update'||'----'||'\u4fee\u6539\u524d\u7684\u503c'||'----'||:old.ename); dbms_output.put_line('update'||'----'||'\u4fee\u6539\u540e\u7684\u503c'||'----'||:new.ename); else dbms_output.put_line('delete emp_cxp'||'----'||'\u4fee\u6539\u524d\u7684\u503c'||'----'||:old.ename); end if; end; \u4f8b\u5b50\uff1a\u7f16\u5199\u4e00\u4e2a\u89e6\u53d1\u5668\uff0c\u4fee\u6539emp_xxx\u8868\u7684\u96c7\u5458\u7684\u85aa\u6c34\uff0c\u663e\u793a\u4fee\u6539\u524d\u548c\u4fee\u6539\u540e\u7684\u503c\uff0c\u786e\u4fdd\u4fee\u6539\u540e\u7684\u5de5\u8d44\u4e0d\u4f4e\u4e8e\u539f\u5de5\u8d44 select * from emp_wzl; drop table emp_wzl; CREATE TABLE emp_wzl AS SELECT * FROM EMP; create trigger tr_wzl before update of sal on emp_wzl for each row begin dbms_output.put_line('\u4fee\u6539\u524d\u7684\u503c'||'----'||:old.sal); dbms_output.put_line('\u4fee\u6539\u540e\u7684\u503c'||'----'||:new.sal); if :old.new>:new.sal then raise_application_error(-20011,'\u53ea\u6da8\u4e0d\u964d'); end if; end; select * from emp_wzl; insert into emp_wzl(empno,ename,sal)values(1001,'ss',1000); --\u66ff\u4ee3\u89e6\u53d1\u5668\uff1a\u89e6\u53d1\u89c6\u56fe------------------------------------------------------------- create trigger \u89e6\u53d1\u5668\u540d\u5b57 instead of insert or update or delete on \u89c6\u56fe begin \u89e6\u53d1\u4f53 end; --\u7cfb\u7edf\u89e6\u53d1\u5668\uff1a\u89e6\u53d1\u6574\u4e2a\u6570\u636e\u5e93-------------------------------------------------------- create trigger \u89e6\u53d1\u5668\u540d\u5b57 {before|after} \u7cfb\u7edf\u4e8b\u4ef6 on database begin \u89e6\u53d1\u4f53 end\uff1b logon logoff startup shutdown --\u5d4c\u5957------------------------------------------------------------------------------ declare begin ........ declare exception end; ..... exception end; -------------------------------------------------------------------------------- <\/span><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"\u7ecf\u5178SQL\u5b66\u4e60\u7b14\u8bb0 (\u516b) - pl\/sql\u7f16\u7a0b\u4e8cpl\/sql\u7f16\u7a0b\u4f8b\u5b50:\u67e5\u8be2\u90e8\u95e8\u8868\u7684\u4e09\u79cd\u5faa\u73afloop\u5faa\u73af-declarecur","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"_links":{"self":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/6255"}],"collection":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/comments?post=6255"}],"version-history":[{"count":0,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/6255\/revisions"}],"wp:attachment":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/media?parent=6255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/categories?post=6255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/tags?post=6255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}