PLSQL 存储函数SQL编程

版本信息：oracle19c

用户/密码 scott/tiger

 连接PL/SQL 新建——测试窗口 如下图

1. hellowrold 程序

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

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

一般在end;（注意有分号）后加反斜杠，代表是要执行某个存储过程，如果没有反斜杠，则代表是普通换行。这种情况一般用于oracle中的命令窗口。

2. 使用一个变量

-- Created on 2022/6/10 by ME666 
declare 
  -- Local variables here   局部变量在这里

  v_name varchar2(25);
begin
  -- 通过 select ... into ... 语句为变量赋值
  select ename into v_name from emp where empno = 7369;
  -- 打印变量值
  dbms_output.put_line(v_name);
end;

---

 查看表字段数据类型

select column_name,data_type,DATA_LENGTH From all_tab_columns 
where table_name=upper('EMP') AND owner=upper('SCOTT')

3. 使用多个变量

-- Created on 2022/8/1 by ME666 
declare 
  -- Local variables here 局部变量在这里
  v_empno number(22);
  v_name varchar2(25);
  v_job varchar(9);
begin
  -- Test statements here 通过 select ... into ... 语句为变量赋值  被赋值的变量与 SELECT 中的列名要一一对应
  select empno,ename,job into v_empno,v_name,v_job  from emp where empno = 7369;
  -- 拼接 打印变量值
  dbms_output.put_line('v_empno - ' || v_empno || 'v_name - '||  v_name || 'v_job - ' ||v_job);
end;

 细节注意 只能返回单行数据

4. 自定义记录类型1

将一个或多个标量封装成一个对象进行操作。是一种临时复合对象类型。

记录可以直接赋值。RECORD1 :=RECORD2；

记录不可以整体比较.

记录不可以整体判断为空。

语法：
TYPE  记录名  IS RECORD
(
filed1 type1 [NOT NULL] [：=eXPr1],
....... ,
filedN typen [NOT NULL] [：=exprn]
)

①创建记录类型

②声明记录类型变量

        记录类型变量名 记录类型名 类型在前 名在后

③填充记录。

        记录类型.列名 := '值'；

④访问记录成员

        即输出成员

-- Created on 2022/8/1 by ME666 
declare 
  -- Local variables here 定义一个记录类型
  type customer_type is record(
       v_cust_name varchar(20),
       v_cust_id number(10)
  );
  -- 声明自定义记录类型的变量
  v_customer_type customer_type;
begin
  -- Test statements here
  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;

---

5. 自定义记录类型2

自定义记录类型 通过select 。。。into 封装到自定义类型中 进行数据输出

-- Created on 2022/8/1 by ME666 
declare 
  -- Local variables here 定义一个记录类型
  type orders_record is record(
       v_ORDER# number(10),
       v_SHIPCITY varchar2(25),
       v_SHIPSTATE varchar2(10),
       v_SHIPCOST number(8,2)
  );
  -- 声明自定义记录类型的变量
  v_orders_record orders_record;
  
begin
  -- Test statements here 通过 select ... into ... 语句为变量赋值
  
     select ORDER#,SHIPCITY,SHIPSTATE, SHIPCOST into v_orders_record from orders where  order# = 1000;
     -- 打印变量的值
     dbms_output.put_line(
     'v_ORDER#' || '-' || v_orders_record.v_ORDER# || '######' ||
      'v_SHIPCITY' || '-' || v_orders_record.v_SHIPCITY || '######'||
       'v_SHIPSTATE' || '-' || v_orders_record.v_SHIPSTATE || '######'||
        'v_SHIPCOST' || '-' || v_orders_record.v_SHIPCOST || '######'

       );
  
end;

 有部分空格  全角的空格 注意 在写这类代码的时候 一定要规范格式 能用Tab美化代码 就用Tab (前后文) 不是一行中的代码书写

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

-- Created on 2022/8/1 by ME666 
declare 
  -- 定义一个记录类型
  type emp_record is record(
       v_LASTNAME customers.LASTNAME%type,
       v_FIRSTNAME customers.FIRSTNAME%type,
       v_ZIP customers.ZIP%type,
       v_EMAIL customers.EMAIL%type
  );
  -- 声明自定义记录类型的变量
  v_emp_record emp_record;
  
begin
  -- 通过 select ... into ... 语句为变量赋值
 select LASTNAME, FIRSTNAME, ZIP, EMAIL into v_emp_record from customers where CUSTOMER# = 1001;
 -- 打印变量的值
 dbms_output.put_line(
 'LASTNAME' || v_emp_record.v_LASTNAME || '@@' ||
  'FIRSTNAME' || v_emp_record.v_FIRSTNAME || '@@' ||
   'ZIP' || v_emp_record.v_ZIP || '@@' ||
    'EMAIL' || v_emp_record.v_EMAIL || '@@' 
 );
end;

7. 使用 %rowtype

-- Created on 2022/8/1 by ME666 
declare 
  -- 声明一个记录类型的变量      
  v_emp_record emp%rowtype;
begin
  -- -通过 select ... into ... 语句为变量赋值
  select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO into v_emp_record from emp where EMPNO = 7369;
  dbms_output.put_line(v_emp_record.EMPNO);
  dbms_output.put_line(v_emp_record.ENAME);
  dbms_output.put_line(v_emp_record.JOB);
  dbms_output.put_line(v_emp_record.MGR);
  dbms_output.put_line(v_emp_record.HIREDATE);
  dbms_output.put_line(v_emp_record.SAL);
  dbms_output.put_line(v_emp_record.COMM);
  dbms_output.put_line(v_emp_record.DEPTNO);
end;

8. 赋值语句：通过变量实现查询语句

declare
  v_emp_record emp%rowtype;
  v_dept_deptno emp.empno%type;
begin
  -- 使用赋值符号位变量进行赋值
  v_dept_deptno := 7521;
  --通过 select ... into ... 语句为变量赋值
  select * into v_emp_record  from emp where  empno = v_dept_deptno;
  -- 打印变量的值
  
  dbms_output.put_line(v_emp_record.DEPTNO);
end;

9. 通过变量实现 DELETE、INSERT、UPDATE 等操作

declare
  v_emp_empno emp.deptno%type;
begin
  v_emp_empno := 40;
  delete from emp where deptno = v_emp_empno; -- 执行命令之后 换个SQL窗口进行select测试

  --insert into emp (empno) values(v_emp_empno);

  -- insert into emp (EMPNO, DEPTNO) values(7777,40);
  commit;
end;

10. 使用 IF ... THEN ... ELSIF ... THEN ...ELSE ... END IF;

要求: 查询出 150 号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000'; 若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'

(方法一)

declare 
  v_paideach orderitems.paideach%type;
begin
  -- dbms_output.put_line(1111);
  --通过 select ... into ... 语句为变量赋值
  select paideach into v_paideach from orderitems where ORDER# = 1000;
  dbms_output.put_line('paideach:' || v_paideach);
  -- -- 打印变量的值
  if v_paideach >= 100 then
    dbms_output.put_line('paideach > 100');
    elsif  v_paideach >= 20 then
      dbms_output.put_line(' 20 <= paideach <= 100');
      else
        dbms_output.put_line('paideach < 20');
        end if;
end;

(方法二)

declare
  v_orderitems_ISBN orderitems.ISBN%type;
  v_orderitems_PAIDEACH orderitems.PAIDEACH%type;
  v_PAIDEACH_level varchar(50);
begin
  
   select ISBN, PAIDEACH into v_orderitems_ISBN,v_orderitems_PAIDEACH  from orderitems where ORDER# = 1011;
   
   if(v_orderitems_PAIDEACH >= 100) then v_PAIDEACH_level :=  'v_orderitems_PAIDEACH >= 100';
   elsif(v_orderitems_PAIDEACH >= 50) then v_PAIDEACH_level :=  '50<= v_orderitems_PAIDEACH < 100';
   else v_PAIDEACH_level :=  'v_orderitems_PAIDEACH < 50';
   end if;
   dbms_output.put_line(v_PAIDEACH_level);
end;

 字符串缓冲区太小 解决：修改数据类型varchar2(50)

-- 11. 使用 CASE ... WHEN ... THEN ...ELSE ... END 完成上面的任务

declare 
  -- Local variables here
  v_RETAIL books.RETAIL%type;
  v_msg varchar2(250);
begin
  select RETAIL into v_RETAIL from books where ISBN = '1059831198';
  v_msg := case trunc(v_RETAIL / 5) when 0 then 'v_RETAIL < 5'
    when 1 then '5<v_RETAIL <10'
      else 'v_RETAIL >= 10'
      end;
      dbms_output.put_line('RETAIL' || v_RETAIL || v_msg);
end;

-- trunc函数的概念
-- case when 语法问题

12.
/*
 使用 CASE ... WHEN ... THEN ... ELSE ... END;
要求: 查询出 122 号员工的 JOB_ID, 若其值为 'IT_PROG', 则打印 'GRADE: A';
            6379                             
'AC_MGT', 打印 'GRADE B',
'AC_ACCOUNT', 打印 'GRADE C';
否则打印 'GRADE D'
*/

declare 
  -- Local variables here 当字段值into到 变量中 case 判断的是 变量名  oracle case when 语法中 不能带算数运算符
  v_job emp.job%type;
  v_msg varchar2(250);
begin
  
  select job into v_job from emp where empno = '7369';
  v_msg := 
        case v_job when 'CLERK' then 'A'
          when 'CLERKB' then 'B'
            when 'CLERKC' then 'C'
              else 'D'
                end;
                dbms_output.put_line('job' || v_msg);
  -- Test statements here
end;

loop...exit when ... end loop 

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

declare 
  -- loop ..exit when .....end loop
  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;
  -- Test statements here
  
end;

/*
如果loop ....exit when.....end loop; 跟java for循环相比较
int i = 0；  ==   v_i number(3) = 1;
i < 100  === exit when v_i = 100; 
i++ v_i := v_i +1;   起于loop 终于end loop

for (int i = 0; i< 100;i++){
  soutv(i)
  }
*/

第二种 while 判断条件 loop sout 终结语句条件 end loop;

-- Created on 2022/8/5 by ME666 
declare 
  -- Local variables here
  v_i number(3) := 1;
begin
  while v_i <= 100 loop
        dbms_output.put_line(v_i);
        v_i := v_i +1;
       end loop; 
  -- Test statements here
  
end;

第三种 最简单 for 变量 in 1 .. 100 loop end loop ;

declare
begin
     for v_i in 1..100 loop
       dbms_output.put_line(v_i);
       end loop;
end;

while循环实现1-100之间的素数的输出

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;

-- sqrt 函数的用法 mod函数的用法

使用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;