6.3PLSQL中的SQL语句

Oracle PLSQL

1、SELECT INTO语句：用于把从数据库查询出内容存入变量

DECLARE
 v_hire_date employees.hire_date%TYPE;
 v_salary employees.salary%TYPE;
BEGIN
 SELECT hire_date, salary
 INTO v_hire_date, v_salary
 FROM employees
 WHERE employee_id = 100;
 ...
END;
/
SET SERVEROUTPUT ON
DECLARE
 v_sum_sal NUMBER(10,2);
 v_deptno NUMBER NOT NULL := 60;
BEGIN
 SELECT SUM(salary) -- group function
 INTO v_sum_sal
 FROM employees
 WHERE department_id = v_deptno;
 DBMS_OUTPUT.PUT_LINE ('The sum salary is ' ||
 TO_CHAR(v_sum_sal));
END;
/

2、PLSQL中的控制语句

A.判断语句

IF condition THEN
 statements;
[ELSIF condition THEN
 statements;]
[ELSE
 statements;]
END IF;

CASE selector
 WHEN expression1 THEN result1
 WHEN expression2 THEN result2
 ...
 WHEN expressionN THEN resultN
 [ELSE resultN+1;]
END;

1，对NULL的判断处理

B.循环语句

1，基本循环

LOOP
 statement1;
 . . .
 EXIT [WHEN condition];
END LOOP;

2，For循环

FOR counter IN [REVERSE]
 lower_bound..upper_bound LOOP
 statement1;
 statement2;
 . . .
END LOOP;

DECLARE
 v_country_id locations.country_id%TYPE := 'CA';
 v_location_id locations.location_id%TYPE;
 v_city locations.city%TYPE := 'Montreal';
BEGIN
 SELECT MAX(location_id) INTO v_location_id
 FROM locations
 WHERE country_id = v_country_id;
 FOR i IN 1..3 LOOP
 INSERT INTO locations(location_id, city, country_id)
 VALUES((v_location_id + i), v_city, v_country_id );
 END LOOP;
END;
/

3，Wihle循环

WHILE condition LOOP
 statement1;
 statement2;
 . . .
END LOOP;

4，嵌套循环和Label

Label一般用不着，只有在使用goto语句，或者内部循环需要访问外部的同名变量的时候才需要，而一般这 种做法也是不被提倡的

...
BEGIN
 <<Outer_loop>>
 LOOP
 v_counter := v_counter+1;
 EXIT WHEN v_counter>10;
 <<Inner_loop>>
 LOOP
 ...
 EXIT Outer_loop WHEN total_done = 'YES';
 -- Leave both loops
 EXIT WHEN inner_done = 'YES';
 -- Leave inner loop only
 ...
 END LOOP Inner_loop;
 ...
 END LOOP Outer_loop;
END;