--匿名PL/SQL语句块的声明
declare
v_first_name varchar2(35);
v_last_name varchar2(35);
c_counter constant number :=0;
begin
select first_name,last_name into v_first_name,v_last_name from student where student_id=123;
DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || ' ' || c_counter );
end;
--异常处理情况
declare
v_first_name varchar2(35);
v_last_name varchar2(35);
c_counter constant number :=0;
begin
select first_name,last_name into v_first_name,v_last_name from student where student_id=111;
DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || ' ' || c_counter );
EXCEPTION
WHEN no_data_found THEN DBMS_OUTPUT.put_line('there is no student with ' || 'student_id is 111');
end;
--替代变量
DECLARE
v_student_id NUMBER := &sv_student_id;
v_first_name varchar2(35);
v_last_name varchar2(35);
c_counter constant number :=0;
begin
select first_name,last_name into v_first_name,v_last_name from student where student_id=v_student_id;
DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || ' ' || c_counter );
EXCEPTION
WHEN no_data_found THEN DBMS_OUTPUT.put_line('there is no student with ' || 'student_id is' || v_student_id);
end;
--SET SERVEROUTPUT OFF 输出功能关闭
--TO_CHAR(SYSDATE,'DAY') 转化为当前日
--使用Anchored数据类型(当基础对象的数据类型变化时,无需更改自己的PL/SQL代码
DECLARE
v_name student.FIRST_NAME%TYPE;
v_grade grade.NUMERIC_GRADE%TYPE;
BEGIN
DBMS_OUTPUT.put_line(NVL(v_name,'No Name') || ' has grade of ' || NVL(v_grade,0));
END;
--PL/SQL语句块中往数据库插入数据
DECLARE
v_zip ZIPCODE.ZIP%TYPE;
v_user zipcode.CREATE_BY%TYPE;
v_date zipcode.CREATE_DATE%TYPE;
BEGIN
SELECT 43234,USER,SYSDATE INTO v_zip,v_user,v_date FROM dual;
INSERT INTO zipcode(zip,CREATE_BY,CREATE_DATE,MODIFY_BY,MODIFY_DATE)
VALUES(v_zip,v_user,v_date,v_user,v_date);
END;
--从序列获取数字
CREATE TABLE test01(coll NUMBER);
CREATE SEQUENCE test_seq INCREMENT BY 5;
BEGIN
INSERT INTO test01 VALUES(test_seq.NEXTVAL);
END;
--PL/SQL语句块中使用COMMIT、ROLLBACK和SAVEPOINT
BEGIN
INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
SAVEPOINT A;
INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
SAVEPOINT B;
INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
SAVEPOINT C;
INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
ROLLBACK TO B;
END;
--使用if-then-else语句
DECLARE
v_total NUMBER;
BEGIN
SELECT COUNT(*) INTO v_total FROM enrollment e JOIN SECTION s USING(section_id) WHERE s.COURSE_id=25 ;
IF v_total >= 15 THEN DBMS_OUTPUT.put_line('section 1 of course 25 is full');
ELSE DBMS_OUTPUT.put_line('section 1 of course 25 is not full');
END IF;
END;
--使用ELSEIF 语句
DECLARE
v_num NUMBER := &sv_num;
BEGIN
IF v_num < 0 THEN DBMS_OUTPUT.put_line(v_num || ' is a negative number');
ELSIF v_num = 0 THEN DBMS_OUTPUT.put_line(v_num || ' is equal to zero');
ELSE DBMS_OUTPUT.put_line(v_num || ' is a positive number');
END IF;
END;
--使用case语句
DECLARE
v_num NUMBER := &sv_num;
v_num_flag NUMBER;
BEGIN
v_num_flag := MOD(v_num,2);
CASE v_num_flag
WHEN 0 THEN DBMS_OUTPUT.put_line(v_num || ' is even number');
ELSE
DBMS_OUTPUT.put_line(v_num || ' is odd number');
END CASE;
DBMS_OUTPUT.put_line('done');
END;
--NULLIF(expression1, expression2)如果expression1=expression2 返回null,否则返回expression1
--COALESCE(expression1,expression2,...,expressionN)每个表达式和NULL比较,并返回第一个非NULL表达式的值
--EXIT WHEN语句的处理逻辑:IF CONDITION THEN EXIT; END IF; 等价于 EXIT WHEN CONDITION;
--使用带有EXIT条件的简单循环
DECLARE
v_counter BINARY_INTEGER := 0;
BEGIN
LOOP
v_counter := v_counter + 1;
DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
IF v_counter = 5 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.put_line('done...');
END;
--使用带有EXIT条件的简单循环
DECLARE
v_counter BINARY_INTEGER := 0;
BEGIN
LOOP
v_counter := v_counter + 1;
DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
EXIT WHEN v_counter = 5 ;
END LOOP;
DBMS_OUTPUT.put_line('done...');
END;
--使用带有EXIT条件的while简单循环
DECLARE
v_counter BINARY_INTEGER := 5;
BEGIN
WHILE v_counter <= 5 LOOP
v_counter := v_counter - 1;
DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
EXIT WHEN v_counter = 2 ;
END LOOP;
DBMS_OUTPUT.put_line('done...');
END;
--使用for简单循环
DECLARE
BEGIN
FOR v_counter IN 1..5 LOOP
DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
END LOOP;
END;
--倒序for简单循环
DECLARE
BEGIN
FOR v_counter IN REVERSE 1..5 LOOP
DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
END LOOP;
END;
--EXIT 和 CONTINUE 条件之间的区别:EXIT条件会终止该循环,而continue条件会终止循环的当前迭代
--使用内置异常
DECLARE
v_num1 NUMBER := &sv_num1;
v_num2 NUMBER := &sv_num2;
v_result NUMBER;
BEGIN
v_result := v_num1 / v_num2;
DBMS_OUTPUT.put_line('v_result: ' || v_result);
EXCEPTION
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('a number cannot be divided by zero');
END;
--内置异常2
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_instructor_name VARCHAR2(50);
BEGIN
SELECT first_name || ' ' || last_name INTO v_instructor_name FROM instructor WHERE instructor_id = v_instructor_id;
DBMS_OUTPUT.put_line('Instructor name is '||v_instructor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('An error has occurred');
END;
--异常的作用范围(在语句块嵌套的情况下,外语句块所定义的任何异常都适用于内语句块)
DECLARE
v_student_id NUMBER := &sv_student_id;
v_name VARCHAR2(30);
v_total NUMBER(1);
--outer block
BEGIN
SELECT TRIM(first_name) || ' ' || TRIM(last_name) INTO v_name FROM student WHERE student_id = v_student_id;
DBMS_OUTPUT.put_line('student name is' || v_name);
--inner block
BEGIN
SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = v_student_id;
DBMS_OUTPUT.put_line('student is registered for ' || v_total || ' course(s)');
EXCEPTION
WHEN value_error OR invalid_number THEN dbms_output.put_line('an error has occurred');
END;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.put_line('there is no such student');
END;
--用户自定义异常
DECLARE
v_student_id student.STUDENT_ID%TYPE := &sv_student_id;
v_total_course NUMBER;
e_invalid_id EXCEPTION;
BEGIN
IF v_student_id < 0 THEN
RAISE e_invalid_id;
ELSE
SELECT COUNT(*) INTO v_total_course FROM enrollment WHERE student_id = v_student_id;
DBMS_OUTPUT.put_line(' The student is registered for ' || v_total_course || ' course');
END IF;
DBMS_OUTPUT.put_line('No exception has been raised');
EXCEPTION
WHEN e_invalid_id THEN
DBMS_OUTPUT.put_line('An id cannot be negative');
END;
--RAISE_APPLICATION_ERROR适用于用户定义异常 语法: RAISE_APPLICATION_ERROR(error_number,error_message);
--error_number编号范围-20999--20000之间 此异常适用于未命名的用户定义异常
DECLARE
v_student_id student.student_id%TYPE := &sv_student_id;
v_total_course NUMBER;
BEGIN
IF v_student_id < 0 THEN RAISE_APPLICATION_ERROR(-20000, ' An id cannot be negative');
ELSE
SELECT COUNT(*) INTO v_total_course FROM enrollment WHERE student_id = v_student_id;
DBMS_OUTPUT.put_line('The student is registered for ' || v_total_course || ' course');
END IF;
END;
--oracle提供了两个内置函数SQLCODE和SQLERRM用于实现OTHERS异常处理程序
DECLARE
v_zip VARCHAR2(5) := '&sv_zip';
v_city VARCHAR2(15);
v_state CHAR(2);
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
SELECT city,state INTO v_city,v_state FROM zipcode WHERE zip = v_zip;
DBMS_OUTPUT.put_line(v_city || ', ' || v_state);
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := substr(SQLERRM,1,200);
DBMS_OUTPUT.put_line('error code: ' || v_err_code);
DBMS_OUTPUT.put_line('error message: ' || v_err_msg);
END;
--隐式游标,例如 ROWCOUNT、SQL%ROWCOUNT会返回被更新数据行数
BEGIN
UPDATE student SET first_name = 'B' WHERE first_name LIKE 'B%';
DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;
--PL/SQL支持三种类型的记录:基于表的(table-based)、基于游标的(cursor-based)以及程序员自定义的(programmer-defined)
--基于表的和基于游标的可以使用%rowtype属性
DECLARE
vr_student student%ROWTYPE;
BEGIN
SELECT * INTO vr_student FROM student WHERE student_id = 123;
DBMS_OUTPUT.put_line(vr_student.first_name || ' ' || vr_student.last_name || ' has an ID of 156');
EXCEPTION
WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-2001,'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.zip || ' ' || vr_zip.city|| ' ' ||vr_zip.state);
END LOOP;
close c_zip;
END;
--使用for循环的游标
DECLARE
CURSOR c_student IS
SELECT student_id,last_name,first_name FROM student WHERE student_id < 110;
BEGIN
FOR r_student IN c_student
LOOP
INSERT INTO table_log VALUES(r_student.last_name);
END LOOP;
END;
--before触发器
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;
--after触发器
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON instructor
DECLARE
v_type VARCHAR2(10);
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 transation_name = v_type;
IF SQL%NOTFOUND THEN INSERT INTO STATISTICS VALUES('instructor',v_type,USER,SYSDATE);
END IF;
END;
--after触发器
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON instructor
DECLARE
v_type VARCHAR2(10);
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 transation_name = v_type;
IF SQL%NOTFOUND THEN INSERT INTO STATISTICS VALUES('instructor',v_type,USER,SYSDATE);
END IF;
END;