Oracle PL/SQL笔记

220xg6kmnh

declare
v_first_name varchar2(35);
v_last_name varchar2(35);
c_student_id constant number := 2223;
begin
select first_name, last_name
into v_first_name, v_last_name
from student
where 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;
begin
select *
into vr_student
from student
where student_id = 156;
--用“.”来访问行记录属性
dbms_output.put_line(vr_student.first_name || ' ' || vr_student.street_address || ' has an ID of 156');
exception
when 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_bi
before insert on student
for each row
declare
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_aud
after update or delete on instructor
declare
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;