Oracle PL/SQL笔记
declarev_first_name varchar2(35);v_last_name varchar2(35);c_student_id constant number := 2223;beginselect first_name, last_nameinto v_first_name, v_last_namefrom studentwhere student_id = c_student_id; dbms_output.put_line('Student name: ' || v_first_name || ' ' || v_last_name);exception when no_data_found then dbms_output.put_line('There is no student with student id '||c_student_id);end;BEGIN INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE); COMMIT;EXCEPTION WHEN OTHERS THEN DECLARE v_err_code NUMBER := SQLCODE; v_err_msg VARCHAR2(100) := SUBSTR(SQLERRM, 1, 100); BEGIN DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code); DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg); END;END;
declare --rowtype:行记录类型vr_student student%rowtype;beginselect *into vr_studentfrom studentwhere student_id = 156; --用“.”来访问行记录属性dbms_output.put_line(vr_student.first_name || ' ' || vr_student.street_address || ' has an ID of 156');exceptionwhen no_data_found then raise_application_error(-20001,'the student is not in the database');end;declare cursor c_zip is-- 声明游标 select * from zipcode; vr_zip c_zip%rowtype; --游标行类型begin open c_zip;--打开游标 loop fetch c_zip into vr_zip; --对游标集合内的每个数据行操作 exit when c_zip%notfound; dbms_output.put_line(vr_zip.city); end loop; -- loop结束，自动关闭游标end;DECLARE v_sid student.student_id%TYPE; CURSOR c_student IS SELECT student_id FROM student WHERE student_id < 110;BEGIN OPEN c_student; LOOP FETCH c_student INTO v_sid; EXIT WHEN c_student%NOTFOUND; DBMS_OUTPUT.PUT_LINE('STUDENT ID : '||v_sid); END LOOP; CLOSE c_student; EXCEPTION WHEN OTHERS THEN IF c_student%ISOPEN THEN CLOSE c_student; END IF;END;DECLARE CURSOR c_student_enroll IS SELECT s.student_id, first_name, last_name, COUNT(*) enroll, (CASE WHEN count(*) = 1 Then ' class.' WHEN count(*) is null then ' no classes.' ELSE ' classes.' END) class FROM student s, enrollment e WHERE s.student_id = e.student_id AND s.student_id <110 GROUP BY s.student_id, first_name, last_name; r_student_enroll c_student_enroll%ROWTYPE;BEGIN OPEN c_student_enroll; LOOP FETCH c_student_enroll INTO r_student_enroll; EXIT WHEN c_student_enroll%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Student INFO: ID '|| r_student_enroll.student_id||' is '|| r_student_enroll.first_name|| ' ' || r_student_enroll.last_name|| ' is enrolled in '||r_student_enroll.enroll|| r_student_enroll.class); END LOOP; CLOSE c_student_enroll;EXCEPTION WHEN OTHERS THEN IF c_student_enroll %ISOPEN THEN CLOSE c_student_enroll; END IF;END;DECLARE CURSOR c_group_discount IS SELECT DISTINCT s.course_no FROM section s, enrollment e WHERE s.section_id = e.section_id GROUP BY s.course_no, e.section_id, s.section_id HAVING COUNT(*)>=8;BEGIN FOR r_group_discount IN c_group_discount LOOP UPDATE course SET cost = cost * .95 WHERE course_no = r_group_discount.course_no; END LOOP; COMMIT;END;DECLARE CURSOR c_group_discount IS SELECT DISTINCT s.course_no FROM section s, enrollment e WHERE s.section_id = e.section_id GROUP BY s.course_no, e.section_id, s.section_id HAVING COUNT(*)>=8; v_course_no c_group_discount%rowtype;BEGIN open c_group_discount; LOOP fetch c_group_discount into v_course_no; UPDATE course SET cost = cost * .95 WHERE course_no = v_course_no.course_no; END LOOP; COMMIT;END;
select count(*) from student;select * from student;select * from student where created_by = 'xls';create or replace trigger student_bibefore insert on studentfor each rowdeclare v_student_id student.student_id%type;begin select student_id_seq.nextval into v_student_id from dual; :new.student_id := v_student_id; :new.created_by := user; :new.created_date := sysdate; :new.modified_by := user; :new.modified_date := sysdate;end;INSERT INTO student (student_id, first_name, last_name, zip, registration_date, created_by, created_date, modified_by, modified_date)VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '00914', SYSDATE, USER, SYSDATE, USER, SYSDATE); INSERT INTO student (first_name, last_name, zip, registration_date)VALUES ('John', 'Smith', '00914', SYSDATE);create or replace trigger instructor_audafter update or delete on instructordeclare v_type varchar2(10); pragma autonomous_transaction;begin if UPDATING then v_type := 'UPDATE'; elsif deleting then v_type := 'DELETE'; end if; update statistics set transaction_user = user, transaction_date = sysdate where table_name = 'INSTRUCTOR' and transaction_name = v_type; if sql%notfound then insert into statistics values('INSTRUCTOR', v_type, user, sysdate); end if; commit;end;UPDATE instructor SET phone = '7181234567' WHERE instructor_id = 101; SELECT * FROM statistics;
页:
[1]