PL/SQL 实例总结

--PL/SQL 保证输出
SET serveroutput ON

--PL/SQL 块
DECLARE
	v_sal emp.sal%type;
BEGIN
	SELECT sal INTO v_sal FROM emp WHERE empno=7369;
	dbms_output.put_line(v_sal);
END;

--PL/SQL 记录类型
DECLARE
	type emp_record IS record(
		v_ename emp.ename%type,
		v_sal emp.sal%type
	);
	v_emp emp_record;
BEGIN
	SELECT 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 流程控制
DECLARE
	v_sal emp.sal%type;
	v_temp varchar2(32);
BEGIN
	SELECT 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 游标
DECLARE
	CURSOR emp_cursor IS SELECT ename , sal  FROM emp WHERE deptno=10;
BEGIN
	FOR c IN emp_cursor LOOP
		 dbms_output.put_line(c.ename||' , '||c.sal);
		 END LOOP;
END;

DECLARE
	CURSOR 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 隐式游标
BEGIN
	UPDATE 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 varchar2
IS
BEGIN
	     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 number
is
          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 loop
					v_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)
is
					cursor emp_cursor is select sal from emp where deptno =  v_deptno;
begin
	        v_sumsal := 0;
					for c in emp_cursor loop
					v_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_updateemp
after
          update on emp
		for each row
begin 
          dbms_output.put_line('helloword');	
end;


update emp set sal =sal+100 where deptno=10;


--游标变量
create or replace procedure CREATEREPORT(a_checkDate in varchar2) is
			v_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.UDSI
				FROM PR11_PARTY_UDSI_TRANS A
				WHERE A.CREATE_DATE = TO_DATE(a_checkDate,'YYYY-MM-DD')
				AND A.UDSI != '1102'
				AND A.PARTY_ID = p.party_id ;
			 loop
			fetch cursor_udsi into v_udsi;
			 exit when cursor_udsi%notfound;
			 dbms_output.put_line(v_udsi);		
					
			 END loop;
			 
       END LOOP;
end;