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;

 

posted @ 2023-03-02 14:52  diablo-427  阅读(197)  评论(0编辑  收藏  举报