oracle的存储过程1

逃离我推掉我的手 2023-01-05 14:10 131阅读 0赞

## oracle的存储过程 ##

1.准备工作：  
使用set serveroutput on 命令设置环境变量serveroutput为打开状态，输出结果

set   serveroutput   on;

**2.hellowrold 程序**

begin
    dbms_output.put_line('hello world');
    end;

**3. 语法格式**

--declare
      --声明的变量、类型、游标
    begin
      --程序的执行部分（类似于java里的main()方法）
      dbms_output.put_line('helloworld');
    --exception
      --针对begin块中出现的异常，提供处理的机制
      --when .... then ...
      --when .... then ...
    end;

### 基本语法 ###

1.使用一个变量

declare
      --声明一个变量
      v_name varchar2(25);
    begin
      --通过 select ... into ... 语句为变量赋值
     select last_name into v_name
     from employees
     where employee_id = 186;
     
     -- 打印变量的值
     dbms_output.put_line(v_name);
    end;

2.使用多个变量

declare
      --声明变量
      v_name varchar2(25);
      v_email varchar2(25);
      v_salary number(8, 2);
      v_job_id varchar2(10);
    begin
      --通过 select ... into ... 语句为变量赋值
      --被赋值的变量与SELECT中的列名要一一对应
     select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
     from employees
     where employee_id = 186;
     
     -- 打印变量的值
     dbms_output.put_line(v_name || ', ' || v_email || ', ' ||  v_salary || ', ' ||  v_job_id);
    end;

##### 记录类型 #####

**3.1 自定义记录类型**

declare
      --定义一个记录类型
      type customer_type is record(
        v_cust_name varchar2(20),
        v_cust_id number(10));
    
      --声明自定义记录类型的变量
      v_customer_type customer_type;
    begin
      v_customer_type.v_cust_name := '刘德华';
      v_customer_type.v_cust_id := 1001;
      
      dbms_output.put_line(v_customer_type.v_cust_name||','||v_customer_type.v_cust_id);
    end;

**3.2 自定义记录类型**

declare
      --定义一个记录类型
      type emp_record is record(
        v_name varchar2(25),
        v_email varchar2(25),
        v_salary number(8, 2),
        v_job_id varchar2(10));
        
      --声明自定义记录类型的变量
      v_emp_record emp_record;
    begin
      --通过 select ... into ... 语句为变量赋值
     select last_name, email, salary, job_id into v_emp_record
     from employees
     where employee_id = 186;
     
     -- 打印变量的值
     dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||  
                                            v_emp_record.v_salary || ', ' ||  v_emp_record.v_job_id);

**4. 使用 %type 定义变量，动态的获取数据的声明类型**

declare
      --定义一个记录类型
      type emp_record is record(
        v_name employees.last_name%type,
        v_email employees.email%type,
        v_salary employees.salary%type,
        v_job_id employees.job_id%type);
        
      --声明自定义记录类型的变量
      v_emp_record emp_record;
    begin
      --通过 select ... into ... 语句为变量赋值
     select last_name, email, salary, job_id into v_emp_record
     from employees
     where employee_id = 186;
     
     -- 打印变量的值
     dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||  
                                            v_emp_record.v_salary || ', ' ||  v_emp_record.v_job_id);
    end;

**5. 使用 %rowtype**

declare
    --声明一个记录类型的变量
      v_emp_record employees%rowtype;
    begin
      --通过 select ... into ... 语句为变量赋值
     select * into v_emp_record
     from employees
     where employee_id = 186;
     
     -- 打印变量的值
     dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||  
                                            v_emp_record.salary || ', ' ||  v_emp_record.job_id  || ', ' ||  
                                            v_emp_record.hire_date);
    end;

**6.1 赋值语句：通过变量实现查询语句**

declare
      v_emp_record employees%rowtype;
      v_employee_id employees.employee_id%type;
    begin
      --使用赋值符号位变量进行赋值
      v_employee_id := 186;
    
      --通过 select ... into ... 语句为变量赋值
     select * into v_emp_record
     from employees
     where employee_id = v_employee_id;
     
     -- 打印变量的值
     dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||  
                                            v_emp_record.salary || ', ' ||  v_emp_record.job_id  || ', ' ||  
                                            v_emp_record.hire_date);
    end;

**6.2 通过变量实现DELETE、INSERT、UPDATE等操作**

declare
      v_emp_id employees.employee_id%type;
    
    begin
      v_emp_id := 109;
      delete from employees
      where employee_id = v_emp_id;
      --commit;
    end;

### 流程控制 ###

**7. 使用 IF … THEN … ELSIF … THEN …ELSE … END IF;**  
要求: 查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 ‘salary >= 10000’;  
若在 5000 到 10000 之间, 则打印 ‘5000<= salary < 10000’; 否则打印 ‘salary < 5000’  
(方法一)

declare
      v_salary employees.salary%type;
    begin
      --通过 select ... into ... 语句为变量赋值
     select salary into v_salary
     from employees
     where employee_id = 150;
     
     dbms_output.put_line('salary: ' || v_salary);
     
     -- 打印变量的值
     if v_salary >= 10000 then
        dbms_output.put_line('salary >= 10000');
     elsif v_salary >= 5000 then
        dbms_output.put_line('5000 <= salary < 10000');
     else
        dbms_output.put_line('salary < 5000');
     end if;

(方法二)

declare
         v_emp_name employees.last_name%type;
         v_emp_sal employees.salary%type;
         v_emp_sal_level varchar2(20);
    begin
         select last_name,salary into v_emp_name,v_emp_sal from employees where employee_id = 150;
         
         if(v_emp_sal >= 10000) then v_emp_sal_level := 'salary >= 10000';
         elsif(v_emp_sal >= 5000) then v_emp_sal_level := '5000<= salary < 10000';
         else v_emp_sal_level := 'salary < 5000';
         end if;
         
         dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
    end;

**7+ 使用 CASE … WHEN … THEN …ELSE … END 完成上面的任务**

declare
           v_sal employees.salary%type;
           v_msg varchar2(50);
    begin     
           select salary into v_sal
           from employees
           where employee_id = 150;
           
           --case 不能向下面这样用
           /* case v_sal when salary >= 10000 then v_msg := '>=10000' when salary >= 5000 then v_msg := '5000<= salary < 10000' else v_msg := 'salary < 5000' end; */
     
           v_msg := 
                 case trunc(v_sal / 5000)
                      when 0 then 'salary < 5000'
                      when 1 then '5000<= salary < 10000'
                      else 'salary >= 10000'
                 end;
           
           dbms_output.put_line(v_sal ||','||v_msg);
    end;

**8. 使用 CASE … WHEN … THEN … ELSE … END;**  
要求: 查询出 122 号员工的 JOB\_ID, 若其值为 ‘IT\_PROG’, 则打印 ‘GRADE: A’;  
‘AC\_MGT’, 打印 ‘GRADE B’,  
‘AC\_ACCOUNT’, 打印 ‘GRADE C’;  
否则打印 ‘GRADE D’

declare
           --声明变量
           v_grade char(1);
           v_job_id employees.job_id%type;
    begin
           select job_id into v_job_id
           from employees
           where employee_id = 122;
           
           dbms_output.put_line('job_id: ' || v_job_id);
           
           --根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值
           v_grade :=  
                   case v_job_id when 'IT_PROG' then 'A'
                                 when 'AC_MGT' then 'B'
                                 when 'AC_ACCOUNT' then 'C'
                                 else 'D'
                    end;
                    
           dbms_output.put_line('GRADE: ' || v_grade);
    end;

#### 循环结构 ####

**9. 使用循环语句打印 1 - 100.（三种方式）**

1).  LOOP ... EXIT WHEN ... END LOOP
    declare
           --初始化条件
           v_i number(3) := 1;
    begin
           loop
           --循环体
            dbms_output.put_line(v_i);
    	--循环条件
            exit when v_i = 100;
    	--迭代条件
            v_i := v_i + 1;
           end loop;
    end;
    
    2). WHILE ... LOOP ... END LOOP
    declare
           --初始化条件
           v_i number(3) := 1;
    begin
           --循环条件
           while v_i <= 100 loop
    	     --循环体
                 dbms_output.put_line(v_i);
    	     --迭代条件
                 v_i := v_i + 1;
           end loop;
    end; 
    
    3).
    begin
           for i in 1 .. 100 loop
                 dbms_output.put_line(i);
           end loop;
    end;

1.  综合使用 if, while 语句, 打印 1 - 100 之间的所有素数  
    (素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, …).

declare
      v_flag number(1):=1;
      v_i number(3):=2;
      v_j number(2):=2;
    begin
    
      while (v_i<=100) loop
            while v_j <= sqrt(v_i) loop
                  if (mod(v_i,v_j)=0) then v_flag:= 0;
    	      end if;
                 
    	      v_j :=v_j +1;
            end loop;
            
    	if(v_flag=1) then dbms_output.put_line(v_i);
    	end if;
    
            v_flag :=1;
            v_j := 2;
            v_i :=v_i +1;
       end loop;
    
    end;

法二)使用for循环实现1-100之间的素数的输出

declare
      --标记值, 若为 1 则是素数, 否则不是
      v_flag number(1) := 0;
    begin
       for i in 2 .. 100 loop
    
           v_flag := 1;     
             
           for j in 2 .. sqrt(i) loop
               if i mod j = 0 then
                  v_flag := 0;	
               end if;        
           end loop;
           
           if v_flag = 1 then
               dbms_output.put_line(i);
           end if;
           
       end loop;
    end;
    
    11. 使用 goto
    declare
      --标记值, 若为 1 则是素数, 否则不是
      v_flag number(1) := 0;
    begin
       for i in 2 .. 100 loop
           v_flag := 1;     
             
           for j in 2 .. sqrt(i) loop
               if i mod j = 0 then
                  v_flag := 0;
                  goto label; 
               end if;        
           end loop;
           
           <<label>>
           if v_flag = 1 then
               dbms_output.put_line(i);
           end if;
           
       end loop;
    end;

11+.打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”

(方法一)
    begin
      for i in 1..100 loop
          dbms_output.put_line(i);
          if(i = 50) then 
          goto label;
          end if;
      end loop;
          
          <<label>>
          dbms_output.put_line('打印结束');
    
    end;
    (方法二)
    begin
      for i in 1..100 loop
          dbms_output.put_line(i);
          if(i mod 50 = 0) then dbms_output.put_line('打印结束');
          exit;
          end if;
      end loop;
    end;

## 游标的使用 ##

12.1 使用游标

要求: 打印出 80 部门的所有的员工的工资:salary: xxx

declare
      --1. 定义游标
      cursor salary_cursor is select salary from employees where department_id = 80;
      v_salary employees.salary%type;
    begin
     --2. 打开游标
     open salary_cursor;
    
     --3. 提取游标
     fetch salary_cursor into v_salary;
     
     --4. 对游标进行循环操作: 判断游标中是否有下一条记录
    while salary_cursor%found loop
          dbms_output.put_line('salary: ' || v_salary);
          fetch salary_cursor into v_salary;
    end loop;  
     
     --5. 关闭游标
     close  salary_cursor;
    end;

**12.2 使用游标**

--要求: 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx
     
    declare
      cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
      v_sal number(10);
      v_name varchar2(20);
    begin
      open sal_cursor;
      
      fetch sal_cursor into v_sal,v_name;
      
      while sal_cursor%found loop
            dbms_output.put_line(v_name||'`s salary is '||v_sal);
            fetch sal_cursor into v_sal,v_name;
      end loop;
      
      close sal_cursor;
      
    end;

1.  使用游标的练习:  
    打印出 manager\_id 为 100 的员工的 last\_name, email, salary 信息(使用游标, 记录类型)

declare  
               --声明游标 
               cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
               
               --声明记录类型
               type emp_record is record(
                    name employees.last_name%type,
                    email employees.email%type,
                    salary employees.salary%type
               );
               
               -- 声明记录类型的变量
               v_emp_record emp_record;
    begin
               --打开游标
               open emp_cursor;
               
               --提取游标
               fetch emp_cursor into v_emp_record;
               
               --对游标进行循环操作
               while emp_cursor%found loop
                      dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );                
                      fetch emp_cursor into v_emp_record;
               end loop;
               
               --关闭游标
               close emp_cursor;
    end;
    (法二：使用for循环)
    declare
       
          cursor emp_cursor is 
          select last_name,email,salary
          from employees
          where manager_id = 100;
    
    begin
    
    
          for v_emp_record in emp_cursor loop
              dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
          end loop;
    end;