Oracle存储过程学习
显示的开启游标CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY(GENTEE IN VARCHAR2) AS
CURSOR CUR IS
SELECT FIRST_NAME || LAST_NAME EMPLOYEE_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEESEX = GENTEE;
R EMPLOYEES % ROWTYPE;
BEGIN
FOR R IN CUR LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || R.EMPLOYEE_NAME ||
' EMPLOYEE_SALARY: ' || R.SALARY);
END;
END LOOP;
END EMPLOYEE_SALARY;
显示的开启游标方法2：
CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY (GENTEE IN VARCHAR2)AS
EMPN VARCHAR2(30);
WAGE NUMBER(20);
C1 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEESEX=GENTEE;
LOOP
FETCH C1 INTO EMPN,WAGE;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || EMPN || ' SALARY: ' || WAGE);
EXIT WHEN C1%NOTFOUND;
END LOOP;
END EMPLOYEE_SALARY;
该语句的隐式游标方式：
CREATE OR REPLACE PROCEDURE EMPLOYEE_SALARY(GENTEE IN VARCHAR2) AS
R EMPLOYEES % ROWTYPE;
BEGIN
FOR R IN (SELECT FIRST_NAME || LAST_NAME EMPLOYEE_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEESEX = GENTEE) LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' || R.EMPLOYEE_NAME ||
' EMPLOYEE_SALARY: ' || R.SALARY);
END;
END LOOP;
END EMPLOYEE_SALARY;
页:
[1]