Oracle之PL/SQL学习笔记

-- 使用匿名过程完成工资修改 SET SERVEROUT ON ; DECLARE l_salary NUMBER(5) ; l_empno NUMBER(4) ; BEGIN l_empno := 7369 ; -- initialize must be execution section SELECT sal INTO l_salary FROM emp WHERE empno=l_empno ; IF l_salary > 500 THEN UPDATE emp SET sal = l_salary+500 WHERE empno=l_empno ; END IF; EXCEPTION -- Handle Exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ; END ; -- 创建命名嵌套存储过程完成工资修改 SET SERVEROUT ON ; CREATE OR REPLACE PROCEDURE change_salary IS -- DECLARE -- 命名存储过程不需要DECLARE l_salary NUMBER(5) ; l_empno NUMBER(4) := 7369 ; BEGIN -- l_empno := 7369 ; -- initialize must be execution section SELECT sal INTO l_salary FROM emp WHERE empno=l_empno ; IF l_salary > 500 THEN UPDATE emp SET sal = l_salary-500 WHERE empno=l_empno ; END IF; BEGIN DBMS_OUTPUT.PUT_LINE('NESTED SECSION') ; END ; EXCEPTION -- Handle Exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ; END ; -- 定义变量 identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; v_hirdate DATE ; v_empno emp.empno%TYPE := 7369 ; DECLARE emp_count NUMBER ; BEGIN SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id=&department_id; DBMS_OUTPUT.PUT_LINE('count'||':'||emp_count) ; END ; -- Returning 子句 DECLARE myname employees.last_name%TYPE ; mysal employees.salary%TYPE ; BEGIN FOR rec IN (SELECT * FROM employees) LOOP UPDATE employees SET salary = rec.salary/1 WHERE rec.employee_id = employee_id -- 此处不可加分号 RETURNING last_name,salary INTO myname,mysal ; -- 将修改后的值放入到变量中 DBMS_OUTPUT.PUT_LINE('myname:'||myname||',sal:'||mysal) ; END LOOP ; END ; -- IF Statement DECLARE v_start NUMBER:=1000 ; BEGIN IF v_start>100 THEN v_start:=2*v_start ; ELSIF v_start>50 THEN v_start:=5*v_start ; ELSE v_start:=0.1*v_start ; END IF ; DBMS_OUTPUT.PUT_LINE(v_start) ; END ; -- CASE Statement(Start with 'CASE' end by 'END') SET SERVEROUT ON ; DECLARE -- no NUMBER :=100 ; no2 NUMBER:= &i ; BEGIN CASE no2 WHEN 100 THEN DBMS_OUTPUT.PUT_LINE(100) ; WHEN 200 THEN DBMS_OUTPUT.PUT_LINE(200) ; WHEN 300 THEN DBMS_OUTPUT.PUT_LINE(300) ; ELSE DBMS_OUTPUT.PUT_LINE('DEFAULT') ; END CASE ; END ; -- Select语句中使用Case块 SELECT empno,CASE empno WHEN 7369 THEN 'SMITH' WHEN 7499 THEN 'ALLEN' ELSE 'AKWOLF' END FROM emp ; SELECT COUNT(CASE WHEN sal<1000 THEN 1 ELSE NULL END) count1, COUNT(CASE WHEN sal>=1000 AND sal<2000 THEN 1 ELSE NULL END) count2 from emp ; -- Basic Loop DECLARE v_orderid NUMBER:=1014 ; v_counter NUMBER:=1 ; BEGIN LOOP INSERT INTO item VALUES(v_orderid,v_counter) ; v_counter := v_counter+1 ; EXIT WHEN v_counter>10 ; END LOOP ; END ; -- For Loop DECLARE v_orderid NUMBER :=199 ; BEGIN FOR i IN 1..20 LOOP INSERT INTO item VALUES(v_orderid,i) ; END LOOP ; END; -- While Loop DECLARE v_qty NUMBER :=1 ; v_total NUMBER :=0 ; BEGIN WHILE v_total< &input LOOP v_qty :=v_qty+1 ; v_total := v_qty*&price ; DBMS_OUTPUT.PUT_LINE(v_total) ; END LOOP ; END ; -- CURSOR游标 DECLARE CURSOR emp_cur IS SELECT * FROM emp ; BEGIN FOR emp_rec IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE('empno: '||emp_rec.empno||' ,ename: '||emp_rec.ename) ; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('FOUND -->'||emp_rec.empno) ; ELSIF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('NOTFOUND -->') ; ELSIF NOT SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('NOT OPEN -->') ; ELSE DBMS_OUTPUT.PUT_LINE('THIS SESCTION HAS EXECTION'||SQL%ROWCOUNT) ; END IF ; END LOOP ; END ;