Oracle的FUNCTION

打开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;
/