-- 使用匿名过程完成工资修改
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 ;