orale程的序块案例学习指导.docVIP

----以下程序都是在pl sql developer中开发 DECLARE v_ename emp.ename%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=v; dbms_output.put_line(雇员名：||v_ename); END; --迭代 SELECT * FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr; ---pl sql DECLARE V_ENAME EMP.ENAME%TYPE; BEGIN FOR AREC IN (SELECT ENAME, EMPNO, MGR FROM EMP START WITH EMPNO = 7839 CONNECT BY PRIOR EMPNO = MGR) LOOP IF AREC.ENAME IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE(AREC.EMPNO || - || AREC.ENAME || - || AREC.MGR); END IF; END LOOP; END; --自定义异常 DECLARE V_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO V_ENAME FROM EMP E WHERE E.EMPNO = no; DBMS_OUTPUT.PUT_LINE(顾员名称： || V_ENAME); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(请输入正确的顾员号码！); END; OUTER DECLARE v_deptno scott.dept.deptno%TYPE; v_dname scott.dept.dname%TYPE; BEGIN INNER BEGIN SELECT deptno INTO v_deptno FROM emp e WHERE LOWER(e.ename)=LOWER(name); END;--INNER SELECT dname INTO v_dname FROM scott.dept d WHERE d.deptno=v_deptno; dbms_output.put_line(部门名称||v_dname); END;--OUTER --number类型 DECLARE v_num NUMBER(5,-1); BEGIN SELECT 10099 INTO v_num FROM dual; dbms_output.put_line(v_num); END; ----Date类型 DECLARE v_date DATE; BEGIN SELECT SYSDATE INTO v_date FROM dual; dbms_output.put_line(v_date); END; --标量，变量，%type DECLARE V_ENAME EMP.ENAME%TYPE; V_SAL NUMBER(6, 2); C_TAX_RATE CONSTANT NUMBER(3, 2) := 0.03; V_TAX_SAL NUMBER(6, 2); BEGIN SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP E WHERE E.EMPNO = ENO; V_TAX_SAL := V_SAL * C_TAX_RATE; DBMS_OUTPUT.PUT_LINE(V_TAX_SAL); END; ---PL/sql记录 存储单行 DECLARE TYPE EMP_RECORD_TYPE IS RECORD( ENAME EMP.ENAME%TYPE, SALARY EMP.SAL%TYPE, TITLE EMP.JOB%TYPE); EMP_RECORD EMP_RECORD_TYPE; BEGIN SELECT ENAME, SAL, JOB INTO EMP_RECORD FROM EMP E WHERE E.EMPNO = 7369;