plsql美化文件配置
--general
FUNCTION MGRNAME(P_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP.ENAME%TYPE IS RESULT EMP.ENAME%TYPE; I INTEGER; BEGIN RESULT := NULL; I := 1; IF P_EMPNO IS NULL THEN -- If empno is null, return an empty name RESULT := NULL; ELSE -- Fetch the name of the manager SELECT M.ENAME INTO RESULT FROM EMP E, EMP M WHERE E.EMPNO = P_EMPNO AND M.EMPNO = E.MGR AND D.DEPTNO IN (10, 20, 30, 40); END IF; RETURN(RESULT); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN(NULL); END;
contrl
BEGIN FOR EMP_CURSOR IN (SELECT * FROM EMP) LOOP IF EMP_CURSOR.MGR IS NULL OR EMP_CURSOR.MGR = 0 THEN DBMS_OUTPUT.PUT_LINE('No manager'); ELSE DBMS_OUTPUT.PUT_LINE('Manager = ' || TO_CHAR(EMP_CURSOR)); END IF; END LOOP; END;
dml
BEGIN -- Select SELECT DEPNO AS DEPARTMENT_NUMBER, DNAME AS DEPARTMEN_NAME, LOC AS DEPARTMENT_LOCATION FROM DEPT, EMP WHERE EMP.EMPNO = P_EMPNO AND DEPT.DEPTNO = EMP.DEPTNO; -- Insert INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'Accounting', 'New York'); -- Update UPDATE DEPT SET DNAME = 'Accounting', LOC = 'New York' WHERE DEPTNO = 10; END;
pd
PROCEDURE INSERTDEPT(P_DEPTNO IN OUT DEPT.DEPTNO%TYPE, P_DNAME IN DEPT.DNAME%TYPE, P_LOC IN DEPT.LOC%TYPE) IS BEGIN -- Determine the maximum department number if necessary IF P_DEPTNO IS NULL THEN SELECT NVL(MAX(DEPTNO), 0) + 1 INTO P_DEPTNO FROM DEPT; END IF; -- Insert the new record INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (P_DEPTNO, P_DNAME, P_LOC); END;
rtd
DECLARE TYPE DEPT_RECORD IS RECORD( DEPTNO NUMBER(2), DNAME VARCHAR2(13), LOC VARCHAR2(13)); BEGIN NULL; END;
本文来自博客园,作者:diablo-427,转载请注明原文链接:https://www.cnblogs.com/diablo-427/p/17171784.html