oracle pl/sql

目标

set serveroutput on

• 作用
  set serveroutpu on是使oracle可以使用自带的输出方法
  dbms_output.put_line(‘XX’);
• 使用范围
  使用于PL/SQL COMMAND WINDOW，SQL PLUS中
• 永久生效
  为了不须要每次都输入set serveroutput on 可以在Oracle安装文件夹下查下glogin.sql文件。并在文件里添加set serveroutput on就可以。

hello world

declare
  -- 声明的变量 类型 游标
begin
   -- 执行部分
   dbms_output.put_line('hello world');
-- exception
   -- 异常处理
end;

输出如下

SQL> /

hello world

PL/SQL procedure successfully completed


SQL> 

简单查询

查询100号员工的salary与email

declare
  -- 声明变量
  -- :=0 表示默认值
  v_sal number(8,2) := 0;
  v_email varchar2(25);
begin
   -- 查询数据
   select salary,email into v_sal,v_email from employees where employee_id = 100;
   -- 输出
   dbms_output.put_line(v_sal||','||v_email);
end;

declare
  -- 声明变量 , 动态获取类型
  v_sal employees.salary%type;
  v_email employees.email%type;
begin
   -- 查询数据
   select salary,email into v_sal,v_email from employees where employee_id = 100;
   -- 输出
   dbms_output.put_line(v_sal||','||v_email);
end;

输出如下

SQL> /

24000,SKING

PL/SQL procedure successfully completed


SQL> 

记录类型的使用

declare
  -- 声明一个记录类型
  type emp_record is record(
    v_sal   employees.salary%type,
    v_email employees.email%type);
  -- 定义一个记录类型的变量
  v_emp_record emp_record;
begin
  -- 查询数据
  select salary, email
    into v_emp_record
    from employees
   where employee_id = 100;
  -- 输出
  dbms_output.put_line(v_emp_record.v_sal || ',' || v_emp_record.v_email);
end;

流程控制

if else

declare
  v_sal employees.salary%type;
  v_temp varchar2(30);
begin
  select salary into v_sal from employees where employee_id = 122;
  -- 输出
  if v_sal >= 10000 then v_temp := 'salary >= 10000';
  elsif v_sal >= 5000 then v_temp := '5000 <= salary < 10000';
  else v_temp := 'salary <= 5000';
  end if;
  
  dbms_output.put_line(v_sal || ' : ' || v_temp);
    
end;

输出

SQL> /

7900 : 5000 <= salary < 10000

PL/SQL procedure successfully completed


SQL> 

case when

declare
  v_job_id employees.job_id%type;
  v_temp varchar2(20);
begin
  select job_id into v_job_id from employees where employee_id = 122;
  
  v_temp :=
          case v_job_id when 'IT_PROG' then 'A'
                        when 'AC_MTG' then 'B'
                        when 'AC_ACCOUNT' then 'C'
                        else 'D'
          end;
  dbms_output.put_line(v_job_id || ' : ' || v_temp);
end;

输出

declare
  v_job_id employees.job_id%type;
  v_temp varchar2(20);
begin
  select job_id into v_job_id from employees where employee_id = 122;
  
  v_temp :=
          case v_job_id when 'IT_PROG' then 'A'
                        when 'AC_MTG' then 'B'
                        when 'AC_ACCOUNT' then 'C'
                        else 'D'
          end;
  dbms_output.put_line(v_job_id || ' : ' || v_temp);
end;

loop

declare
  v_i number(5) := 1;
begin
  loop     
    exit when v_i > 5;
    dbms_output.put_line(v_i);
    v_i := v_i + 1;
  end loop;
end;

输出

SQL> /

1
2
3
4
5

PL/SQL procedure successfully completed


SQL> 

while

declare
  v_i number(5) := 1;
begin
  while v_i <= 5 loop
    dbms_output.put_line(v_i);    
    v_i := v_i + 1;
  end loop;
end;

输出

SQL> /

1
2
3
4
5

PL/SQL procedure successfully completed


SQL> 

for

declare
  v_i number(5) := 1;
begin
  for v_i in 1..5 loop
    dbms_output.put_line(v_i);        
  end loop;
end;

输出

SQL> /

1
2
3
4
5

PL/SQL procedure successfully completed


SQL> 

游标

显式游标

• sql 语句

select b.department_id, b.salary_avg, a.department_name
  from departments a
 right join (select trim(to_char(avg(salary), '9999999.99')) as salary_avg,
                    department_id
               from employees
              where department_id is not null
              group by department_id) b
    on a.department_id = b.department_id;

-- 结果
DEPARTMENT_ID SALARY_AVG  DEPARTMENT_NAME
------------- ----------- ------------------------------
           10 4400.00     Administration
           20 9500.00     Marketing
           30 4150.00     Purchasing
           40 6500.00     Human Resources
           50 3475.56     Shipping
           60 5760.00     IT
           70 10000.00    Public Relations
           80 8955.88     Sales
           90 19333.33    Executive
          100 8600.00     Finance
          110 10150.00    Accounting

11 rows selected

• plsql

declare
  v_salary_avg  number(8, 2) := 0;
  v_depart_name varchar2(30);
  -- 定义游标
  cursor emp_sal_avg_cursor is
    select b.salary_avg, a.department_name
      from departments a
     right join (select trim(to_char(avg(salary), '9999999.99')) as salary_avg,
                        department_id
                   from employees
                  where department_id is not null
                  group by department_id) b
        on a.department_id = b.department_id;
begin
  -- 打开游标
  open emp_sal_avg_cursor;

  -- 提取游标
  fetch emp_sal_avg_cursor
    into v_salary_avg, v_depart_name;

  while emp_sal_avg_cursor%found loop
    dbms_output.put_line('部门: ' || rpad(v_depart_name,18,' ') || ' 平均工资为: ' ||
                         v_salary_avg);
    fetch emp_sal_avg_cursor
      into v_salary_avg, v_depart_name;
  end loop;

  -- 关闭游标
end;

运行结果

SQL> /

部门: Administration     平均工资为: 4400
部门: Marketing          平均工资为: 9500
部门: Purchasing         平均工资为: 4150
部门: Human Resources    平均工资为: 6500
部门: Shipping           平均工资为: 3475.56
部门: IT                 平均工资为: 5760
部门: Public Relations   平均工资为: 10000
部门: Sales              平均工资为: 8955.88
部门: Executive          平均工资为: 19333.33
部门: Finance            平均工资为: 8600
部门: Accounting         平均工资为: 10150

PL/SQL procedure successfully completed


SQL> 

存储过程/函数

函数/过程 区别

• 函数有返回值
• 过程没有返回值

hello world

• 新建函数

create or replace function say_hello
return varchar2
is
begin
  return 'hello world';
end;

• 调用

begin
  dbms_output.put_line(say_hello);
end;

--- 输出
SQL> /

hello world

PL/SQL procedure successfully completed


SQL> 

• 另一种调用方式

SQL> select say_hello from dual;

SAY_HELLO
--------------------------------------------------------------------------------
hello world

1 row selected


SQL>