PL/SQL 实例总结

--PL/SQL 保证输出SET serveroutput ON--PL/SQL 块DECLAREv_sal emp.sal%type;BEGINSELECT sal INTO v_sal FROM emp WHERE empno=7369;dbms_output.put_line(v_sal);END;--PL/SQL 记录类型DECLAREtype emp_record IS record(v_ename emp.ename%type,v_sal emp.sal%type);v_emp emp_record;BEGINSELECT ename , sal INTO v_emp FROM emp WHERE empno=7369;dbms_output.put_line(v_emp.v_ename||' , '||v_emp.v_sal);END;--PL/SQL 流程控制DECLAREv_sal emp.sal%type;v_temp varchar2(32);BEGINSELECT sal INTO v_sal FROM emp WHERE empno=7369;IF v_sal >=3000 THEN v_temp := 'sal>=3000';ELSIF v_sal >=1000 THEN v_temp :='1000<=sal<3000'; ELSE v_temp :='sal<1000';END IF;dbms_output.put_line(v_temp);END;--PL/SQL 游标DECLARECURSOR emp_cursor IS SELECT ename , sal  FROM emp WHERE deptno=10;BEGINFOR c IN emp_cursor LOOP dbms_output.put_line(c.ename||' , '||c.sal); END LOOP;END;DECLARECURSOR emp_cursor IS SELECT empno , sal  FROM emp WHERE deptno=10;v_temp number(4,2);BEGIN      FOR c IN emp_cursor LOOP    IF c.sal >=3000 THEN v_temp := 0.03;    ELSIF c.sal >=1000 THEN v_temp :=0.02;     ELSE v_temp :=0.01;    END IF;UPDATE emp SET sal = c.sal * (1+v_temp) WHERE empno =c.empno;      END LOOP;END;--PL/SQL 隐式游标BEGINUPDATE emp SET sal = sal+10 WHERE empno='7882';IF sql%notfound THEN dbms_output.put_line('查无此人');END IF;END;--PL/SQL 异常DECLARE    v_sal emp.sal%type;BEGIN      SELECT sal INTO v_sal FROM emp WHERE deptno=10;      dbms_output.put_line(v_sal);EXCEPTION    WHEN too_many_rows THEN dbms_output.put_line('返回多行!');END;--PL/SQL 存储函数CREATE OR REPLACE FUNCTION function_helloword(v_name varchar2)RETURN varchar2ISBEGIN     RETURN 'hello '||v_name;END;BEGIN     dbms_output.put_line(function_helloword(' word!'));END;----------create or replace function function_emp_total(v_deptno number,v_sumemp out number)return numberis          v_sumsal number  := 0;cursor emp_cursor is select sal from emp where deptno =  v_deptno;begin        v_sumemp := 0;for c in emp_cursor loopv_sumsal := v_sumsal+c.sal;v_sumemp :=v_sumemp+1;end loop;        return v_sumsal;end;declare    v_sumemp number := 0;begin     dbms_output.put_line(function_emp_total(10,v_sumemp)); dbms_output.put_line(v_sumemp);end;--PL/SQL 存储过程create or replace procedure procedure_emp_total(v_deptno number,v_sumsal out number)iscursor emp_cursor is select sal from emp where deptno =  v_deptno;begin        v_sumsal := 0;for c in emp_cursor loopv_sumsal := v_sumsal+c.sal;end loop;       dbms_output.put_line(v_sumsal);end;declare    v_sumsal number := 0;begin     procedure_emp_total(10,v_sumsal);end;--PL/SQL 触发器create or replace trigger trigger_updateempafter          update on empfor each rowbegin           dbms_output.put_line('helloword');end;update emp set sal =sal+100 where deptno=10;--游标变量create or replace procedure CREATEREPORT(a_checkDate in varchar2) isv_reportkey VARCHAR2(128) ; --报告编号v_udsi VARCHAR2(4) ; --游标变量 type Ref_udsi is ref cursor; cursor_udsi Ref_udsi;  --定义游标CURSOR cursor_party IS      SELECT DISTINCT A.PARTY_ID      FROM PR11_PARTY_UDSI_TRANS A      WHERE A.CREATE_DATE = TO_DATE(a_checkDate,'YYYY-MM-DD')       AND A.UDSI != '1102' ;       begin     FOR P IN cursor_party LOOP         v_reportkey := 'B'||trim(to_char(PR11_REPORT_SEQ.nextval,'00000000'));  --报告编号         --主体触发的规则 open cursor_udsi for  SELECT A.UDSIFROM PR11_PARTY_UDSI_TRANS AWHERE A.CREATE_DATE = TO_DATE(a_checkDate,'YYYY-MM-DD')AND A.UDSI != '1102'AND A.PARTY_ID = p.party_id ; loopfetch cursor_udsi into v_udsi; exit when cursor_udsi%notfound; dbms_output.put_line(v_udsi); END loop;        END LOOP;end;