打开oracle自带的输出方法dbms_output。
在执行set serveroutput on以后,使用dbms_output方法可以输出信息,
例如:dbms_output.put_line('This is');
---取得雇员工资的函数
CREATE OR REPLACE FUNCTION get_sal(no NUMBER)
RETURN NUMBER IS
salary NUMBER(6,2);
BEGIN
SELECT sal into salary FROM emp WHERE empno = no;
RETURN salary;
END;
/
--语句块,批量执行语句,工资低于2000的加上10%。
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp FOR UPDATE;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF emp_record.sal < 2000 THEN
UPDATE emp SET sal = sal*1.1 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
END;
---处理运行错误语句块
DECLARE
name VARCHAR2(10);
BEGIN
SELECT ename INTO name FROM emp where empno = &no;
dbms_output.put_line('雇员名:'||name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该雇员不存在');
END;
-
CREATE OR REPLACE PROCEDURE insert_dept(no NUMBER,name VARCHAR2)
IS
BEGIN
INSERT INTO dept(deptno,dname) VALUES (no,name);
END;
/
---取得雇员工资的函数
CREATE OR REPLACE FUNCTION get_sal2(name VARCHAR2)
RETURN NUMBER IS
v_sal NUMBER(6,2);
BEGIN
SELECT sal into v_sal FROM emp WHERE upper(ename) = upper(name);
RETURN v_sal;
END;
/
----&no是SQL*Plus的替代变量
CREATE OR REPLACE FUNCTION annual_income(name varchar2)
RETURN NUMBER IS
salary NUMBER(19,2);
BEGIN
SELECT sal*12 + nvl(comm,0) into salary FROM emp WHERE lower(ename) = lower(name);
RETURN salary;
END;
/