PLSQL中的各种SQL语句实例讲解

plsql中的sql语句

## select into 语句：用于把从中查询出的内容存入变量

declare 
    v_hire_date employees.hire_date%type;
    v_salary empployees.salary%type;
begin
    select hire_date,salary
    into v_hire_date,v_salary
    from employees
    where department_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;

注意：该语句支持单行的查询结果，如果where条件控制的不好，导致多行查询结果，则会引发to manay rows的例外

## insert,update,delete,merge语句：在plsql中执行这些语句和直接执行这些语句差不多，只不过可以在sql语句中使用plsqls声明变量。

begin
    insert into employees
    (employee_id, first_name, last_name, email,         hire_date, job_id, salary)
    values
    ((employees_seq.nextval, 'ruth', 'cores', 'rcores',sysdate, 'ad_asst', 4000)
end;

declare
    v_sal_increase employees.salary%type;
begin
    update employees
    set salary = salary + v_sal_increase;
    where job_id = 'st_clerk';
end;

declare 
    v_deptno  employees.department_id%type := 10;
begin
    delete from employees
    where department_id = v_ deptno;
end;

declare  
    v_empno employees.employee_id%type := 100; 
begin 
merge into copy_emp c      
using employees e      
on (e.employee_id = v_empno)    
when matched then      
update set c.first_name = e.first_name,       
           c.last_name  = e.last_name, 
           c.email = e.email,
           ...
when not matched then      
insert values(e.employee_id, e.first_name, e.last_name,..., e.department_id); 
end;

plsql中的控制语句

和其他语言一样，控制主要包括判断和循环；

判断语句的语法与其他语言类似：

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;    

### 循环语句的语法与其他语言类似：有基本循环、for循环、wihle循环三种 :

loop
    statement1;
    ...
    exit [when condition];
end loop    

while condition loop
    statement1;
    statement1;
    ...
end loop

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;

嵌套循环和label:

...
begin
    <>
    loop
         v_counter := v_counter+1;
         exit when v_counter > 10;
        <>
        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;
...

## plsql中的复杂自定义数据类型

概述：

plsql中常用的自定义类型就两种： 记录类型、plsql内存表类型（根据表中的数据字段的简单和复杂 程度又可分别实现类似于简单数组和记录数组的功能）

记录类型的定义举例：

type emp_record_type is record
    (last_name   varchar2(25), 
     job_id      varchar2(10),      
     salary      number(8,2));
emp_record emp_record_type;      

---

%rowtype属性：在plsql中 %rowtype 表示某张表的记录类型或者是用户指定以的记录类型，使用此属性可以很方便的定义一个变量，其类型与某张表的记录或者自定义的记录类型保持一致。极大的方便了select * into ….的语句使用。

declare 
    emp_rec employees%rowtype
begin
    select * into emp_rec
    from employees
    where employee_id = &employee_number;

    insert into retired_emps(empno, ename, job, mgr, hiredate,                                leavedate, sal, comm, deptno) ;
    values (emp_rec.employee_id, emp_rec.last_name, emp_rec.job_id,          emp_rec.manager_id, emp_rec.hire_date, sysdate,                  emp_rec.salary, emp_rec.commission_pct,      emp_rec.department_id); 
    commit;
end;

plsql内存表即index by table ,这种结构类似于数组,使用主键提供类似于数组那样的元素访问。这种类必须包含两个部分：

使用binary integerl类型构成的索引主键； 另外一个简单类型 或者用户自定义类型的字段作为具体的数组元素。 这种类型可以自动增长，所以也类似于可变长数组。

...
type ename_table_type is table of employees.last_name%type
    index by binary_integer;
ename_table ename_table_type;    
...

plsql内存表应用举例：

下面定义的两个内存表中的元素都是简单的数据类型，所以相当于定义了两个简单数组：

declare 
    type ename_table_type is table of employees.last_name%type
        index by binary_integer;
    type hiredate_table type is table of date
        index by binary_integer;
    ename_table ename_table_type;
    hiredate_table hiredate_table_type;
begin
    ename_table(1) := 'cameron';
    hiredate_table := sysdate + 7;
        if ename_table.exists(1) then
            insert into ...
        ...
end;

备注：对plsql内存表中某个元素的访问类似于数组，可以使用下表，因为binary_integer这种数据类型 的值在-2147483647 … 2147483647范围内，所以下表也可以在这个范围内。

plsql内存表的应用举例：

下面定义的两个内存表中的元素都是记录类型，所以相当于定义了真正的内存表：

declare
    type emp_table_type is table of employees%rowtype
        index by binary_integer;
    my_emp_table emp_table_type;
    v_count number(3) := 104;
begin
    for i in 100 .. v_count
    loop
        select *
        into my_emp_table(i)
        from employees
        where employee_id = i;
    end loop;
    for i in my_emp_table.first .. my_emp_table.last
    loop
        dbms_output.put_line(my_emp_table(i).last_name);
    end loop;
end;