1.EXCEPTION:
DECLARE
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER := 0.72;
samp_num CONSTANT NUMBER := 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
2.
CREATE OR REPLACE PROCEDURE
get_student_major
(v_student_id IN students.student_id%TYPE,
v_name OUT students.student_name%TYPE) IS
BEGIN
SELECT student_name
INTO v_name
FROM students
WHERE student_id = v_student_id;
exception
WHEN TOO_MANY_ROWS THEN dbms_output.put_line('TMR error'); -- 行数 > 1
WHEN NO_DATA_FOUND THEN dbms_output.put_line('NDF error'); -- 行数为 0
END;
3.
CREATE OR REPLACE FUNCTION
update_salaries(new_sal IN professors.salary%TYPE) -- %TYPE 某一字段的类型作为类型
RETURN NATURAL IS
BEGIN
UPDATE professors2 SET salary = new_sal;
RETURN SQL%ROWCOUNT; -- 判断更新的行数
END update_salaries;
4.
CREATE OR REPLACE PROCEDURE
add_student(rec IN OUT students%ROWTYPE) IS -- students%ROWTYPE作为表的某一行作为变量类型
BEGIN
SELECT 'A'||students_pk_seq.nextval
INTO rec.student_id
FROM dual;
INSERT INTO students (student_id, student_name,
college_major, status, state, license_no)
VALUES (rec.student_id, rec.student_name,
rec.college_major, rec.status,
rec.state, rec.license_no);
END add_student;
5.
DECLARE
dept_rec1 dept%ROWTYPE; --dept是表名,则dept_rec1 和 dept_rec2 可以相互赋值
dept_rec2 dept%ROWTYPE;
CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
dept_rec3 c1%ROWTYPE;
BEGIN
...
dept_rec1 := dept_rec2;
However, because dept_rec2 is based on a table and dept_rec3 is based on a cursor, the following assignment is illegal:
dept_rec2 := dept_rec3; -- illegal
6.
DECLARE
CURSOR my_cursor IS
SELECT sal + NVL(comm, 0) wages, ename FROM emp;
my_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec;
EXIT WHEN my_cursor%NOTFOUND;
IF my_rec.wages > 2000 THEN
INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename);
END IF;
END LOOP;
CLOSE my_cursor;
END;
7. *****
-- 包,函数,存储过程
CREATE OR REPLACE PACKAGE BODY students_pkg IS
FUNCTION add_student(rec IN students%ROWTYPE)
RETURN students.student_id%TYPE
IS
ID students.student_id%TYPE;
BEGIN
SELECT 'A'||students_pk_seq.nextval INTO ID FROM dual;
INSERT INTO students (student_id, student_name,
college_major, status, state, license_no)
VALUES (ID, rec.student_name, rec.college_major,
rec.status, rec.state, rec.license_no);
RETURN ID;
END add_student;
PROCEDURE add_vehicle(rec IN student_vehicles%ROWTYPE) IS
BEGIN
INSERT INTO student_vehicles (state, tag_no,
vehicle_desc, student_id, parking_sticker)
VALUES (rec.state, rec.tag_no,
rec.vehicle_desc, rec.student_id, rec.parking_sticker);
END add_vehicle;
END students_pkg;
-- 执行
DECLARE
student students%ROWTYPE;
vehicle student_vehicles%ROWTYPE;
BEGIN
student.student_name := 'Jack';
student.college_major := 'HI';
student.status := 'Degree';
student.state := 'CA';
student.license_no := 'MV-232-14';
student.student_id := students_pkg.add_student(student);
vehicle.state := 'CA';
vehicle.tag_no := 'CA-1234';
vehicle.vehicle_desc := 'Mustang';
vehicle.parking_sticker := 'A-101';
vehicle.student_id := student.student_id;
students_pkg.add_vehicle(vehicle);
END;
8.
怎样从一个过程返回一个结果集
create or replace procedure p_stu_lst(result out sys_refcursor) is
BEGIN
OPEN RESULT FOR SELECT * FROM test;
end p_stu_lst;
SQL> select * from test;
NAME KM CJ
---------- ---------- ----------
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78
李四 物理 90
已选择7行。
SQL> exec p_stu_lst(:aaa);
PL/SQL 过程已成功完成。
SQL> print aaa
NAME KM CJ
---------- ---------- ----------
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78
李四 物理 90
已选择7行。