PL_SQL 复习笔记

来源：互联网发布：单片机数字电压表程序编辑：程序博客网时间：2024/05/22 03:38

declare
v_DESC VARCHAR2(30);
v_NUM NUMBER:=45;
v_count BINARY_INTEGER:=0;
v_const constant BINARY_INTEGER :=10;
v_nt VARCHAR2(20) NOT NULL :='lalala';
begin
dbms_output.put_line(v_NUM);
v_NUM:=100;
dbms_output.put_line('v_NUM='||v_NUM);
dbms_output.put_line(v_const);
dbms_output.put_line();
end;

declare
--小数会被截掉
v_num NUMBER(3):=123.2;
--小数点后面保存三位,超过四舍五入,上数点前面肯定只能有一位了,要不然就出错
v_num1 NUMBER(4,3):=2.323;
-- -3 代表小数点前面每几位四舍五入
v_num2 NUMBER(5,-3):=12354.23;
--            P S
--有效数位<=P+|S|
v_num3 NUMBER(4,6):=0.0012345;

----------------------------------------------
--BINARY_INTEGER NUMBER并不是采用二进制形式保存
--在计算之前要进行转化成二进制才能运算
--而BINARY_INTEGER 底层是采用二进制保存的   操作比较快
--只是用于常用(循环)计算,不用与数据库表交互

--varchar2 varcahr 一定给长度
--boolean true false
--DATE TIMSTAMP 日间戳   INTERVAL

begin
dbms_output.put_line(v_num); --123
dbms_output.put_line(v_num1); --2.323
dbms_output.put_line(v_num2); --12000
dbms_output.put_line(v_num3); --.001235
end;

--将数据库表中的数据取出来放在变量中
--注意声明的变量要和数据表字段类型一致
declare
v_id number(7);
v_fname varchar2(25); --当我们不知道表结构,或表结构变化时会出错长度不够的隐患
v_salary number(11,2);
begin
select id,first_name,salary into v_id,v_fname,v_salary from s_emp where id=1;
dbms_output.put_line(v_id||' '||v_fname||' '||v_salary);
end;

--解决上面的问题使用TYPE方式的变量
declare
v_id s_emp.id%TYPE;
v_fname s_emp.first_name%TYPE; --声明一个TYPE类型
v_salary s_emp.salary%TYPE;
begin
select id,first_name,salary into v_id,v_fname,v_salary from s_emp where id=1;
dbms_output.put_line(v_id||' '||v_fname||' '||v_salary);
end;

--把一组相关的变量放在一起
declare
TYPE record_emp IS RECORD(
       v_id s_emp.id%TYPE,
       v_fname s_emp.first_name%TYPE,
       v_salary s_emp.salary%TYPE
);
v_temp record_emp;
begin
select id,first_name,salary into v_temp from s_emp where id=1;
dbms_output.put_line(v_temp.v_id||' '||v_temp.v_fname||' '||v_temp.v_salary);

end;
--类型赋值
declare
TYPE record_emp IS RECORD(
       v_id s_emp.id%TYPE,
       v_fname s_emp.first_name%TYPE,
       v_salary s_emp.salary%TYPE
);
TYPE record_emp1 IS RECORD(
       v_id s_emp.id%TYPE,
       v_fname s_emp.first_name%TYPE,
       v_salary s_emp.salary%TYPE
);
v_temp record_emp;
v_temp1 record_emp;
v_temp2 record_emp1;

--注意同类型可以赋值不同类型即使结构相同也不能赋值
begin
select id,first_name,salary into v_temp from s_emp where id=1;
dbms_output.put_line(v_temp.v_id||' '||v_temp.v_fname||' '||v_temp.v_salary);

end;

---RWOTYPE 类型
--查询整个表的所有字段内容
--按照上面的定义就太麻烦了
--同样也存在一定隐患,如果表字段增加了,就会出现问题

declare
v_emp s_emp%ROWTYPE;
begin
select * into v_emp from s_emp where id=1;
dbms_output.put_line(v_emp.first_name||' '||v_emp.last_name);
end;

--TABLE 类型的使用
--关于程序中100 2000 没有什么要求是一个整型数据
declare
--声明一个TABLE类型
TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp t_emp;
begin
select * into v_emp(100) from s_emp where id=1;
select * into v_emp(2000) from s_emp where id=2;
dbms_output.put_line(v_emp(100).first_name);
dbms_output.put_line(v_emp(2000).first_name);
end;

--SQL块嵌入
--变量尽量不要重名
<<Outer>>
declare
v_Num number:=100;
begin
v_Num:=1;
declare
        v_Str varchar2(20):='hello';
        v_Num number :=200;

begin
        dbms_output.put_line('v_Str:'||v_Str);
        dbms_output.put_line('v_Num:'||v_Num);
        dbms_output.put_line('v_Num:'||Outer.v_Num);
end;
dbms_output.put_line('v_Num:'||v_Num);
end;

---条件表达式
--条件为null 或 fasle 都是false 是意思
--只能是true 的情况才执行代码

IF boolean类型表达式 THEN

ELSE

END IF:
------------------------
IF THEN

ELSE IF ..... THEN

ELSE

END IF;

-------------------------

declare
    v_flag boolean;
begin
    if v_flag then
       dbms_output.put_line('TRUE');
    elseif (not v_flag) then
       dbms_output.put_line('FALSE');
    else
       dbms_output.put_line('NULL');
    end if;
end;

declare
    v_sal s_emp.salary%TYPE;
    v_bonus number(10,2);
begin
    select salary into v_sal from s_emp where id=1;
    if v_sal>1500 then
       v_bonus:=v_sal*0.2;
    elsif v_sal > 1000 then
       v_bonus:=v_sal*0.1;
    else
       v_bonus:=v_sal*0.05;
    end if;
end;

--循环 LOOP

LOOP
        --EXIT WHEN boolean_exp
        IF boolean_exp THEN
           EXIT;
        END IF;
END LOOP;

declare
    v_ret number:=0;
    i number:=1;
begin
    loop
      v_ret :=v_ret+1;
      i:=i+1;
      dbms_output.put_line(v_ret);
      exit when i>100;
    end loop;
end;

---WHILE 循环

WHILE boolean_exp LOOP
--循环体
END LOOP;

declare
    v_ret binary_integer:=0;
begin
    while v_ret<100 loop
          v_ret:=v_ret+1;
          dbms_output.put_line(v_ret);
    end loop;
end;

--查询前五个员工的姓名
declare
TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp t_emp;
v_cnt BINARY_INTEGER :=1;
begin
loop
        select * into v_emp(v_cnt) from s_emp where id=v_cnt;
        v_cnt :=v_cnt+1;
        exit when v_cnt>5;
end loop;
v_cnt :=1;
while v_cnt<=5 loop
        dbms_output.put_line(v_emp(v_cnt).first_name||'   '||v_emp(v_cnt).last_name);
        v_cnt :=v_cnt+1;
end loop;
end;

--FOR 循环   FROM loop_count IN [REVERSE] low_bound..high_bound LOOP
--              ...........
--           END LOOP;
--使用FOR循环重写上面的例子
declare
TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp t_emp;
v_cnt BINARY_INTEGER :=1;
begin
FOR v_cnt IN    1..5 LOOP
        select * into v_emp(v_cnt) from s_emp where id=v_cnt;
END LOOP;
FOR v_cnt IN REVERSE 1..5 LOOP
        dbms_output.put_line(v_emp(v_cnt).first_name||'   '||v_emp(v_cnt).last_name);
END LOOP;
end;

--记数器不用声明   类型就是:BINARY_INTEGER 类型
-- IN 后面加 REVERSE 表示循环的方向如:从1-5   或从   5-1

--编程时不使用goto 语句

NULL --可以列表空语句

-- 可以写在PL/SQL 中的语句
-- 只胡DML 和   事务控制语句可以写在PL/SQL程序中

--DBMS_SQL 本地动态SQL包
--可以使用DBMS_SQL 包执行DDL之类的语句

declare
v_str varchar2(200);
begin
v_str :='create table lilp_temp(id number,coll varchar2(20))';
execute immediate v_str;
end;

--速度比较慢常用函数[ ]

declare
v_date varchar2(50);
begin
v_date :=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
dbms_output.put_line(v_date);
end;

---游标的使用 CURSOR
-- 使用游标 [显式|隐式游标]
-- 1.声明游标   CURSOR cursor_name IS SELECT_STATEMENT;
-- 2.打开游标   OPEN cursor_name ;
-- 3.结果处理   FETCH cursor_name INTO var1;
--               FETCH cursor_name INTO record_var;
-- 4.关闭游标   CLOSE cursor_name;

declare
    v_deptId s_emp.dept_id%type :=41;
    v_emp s_emp%rowtype;
    cursor our_emp is
           select * from s_emp where dept_id=v_deptId;
begin
    open our_emp;
    --必须先取一次,才能判断,根据%FOUND 的意思
    fetch our_emp into v_emp;
    while our_emp%FOUND loop
      dbms_output.put_line(v_emp.id ||' '||v_emp.first_name);
      fetch our_emp into v_emp;
    end loop;
    close our_emp;
    dbms_output.put_line('--------------------------------------------');
    open our_emp;
    loop
        fetch our_emp into v_emp;
        exit when our_emp%NOTFOUND;
        dbms_output.put_line(v_emp.id ||' '||v_emp.first_name);
    end loop;
    close our_emp;

    dbms_output.put_line('--------------------------------------------');
    --不处打开游标
    FOR v_emp1 IN our_emp LOOP
        dbms_output.put_line(v_emp1.id ||' '||v_emp1.first_name);
    END LOOP;
    close our_emp;
end;

declare
    v_deptId s_emp.dept_id%type :=41;
    cursor our_emp is
           select * from s_emp where dept_id=v_deptId;
begin
    --不处打开关闭游标
    FOR v_emp IN our_emp LOOP
        dbms_output.put_line(v_emp.id ||' '||v_emp.first_name);
    END LOOP;
end;

--循环取数据,如何控制循环条件å
-- %FOUND 如何FETCH 返回一行数据,则返回TRUE ,否则就返回false; 如果游标未打开,就检查%FOUND
-- %NOTFOUND --与%FOUND 相反   上一次游标撮的结果,如果上一次FECTH 到数据,就返回 FALSE,否则就返回true
-- %ISOPEN    判断游标是否打开

--游标参数不能加精度
declare
   cursor cur_emp(p_did s_emp.dept_id%type) is
          select * from s_emp where dept_id=p_did;
   v_emp s_emp%rowtype;
begin
   open cur_emp(41);
   loop
        fetch cur_emp into v_emp;
        exit when cur_emp%NOTFOUND;
        dbms_output.put_line(v_emp.id ||' '||v_emp.first_name);
   end loop;
   dbms_output.put_line('--------------------------------------------');
   close cur_emp;

   FOR v_emp1 IN cur_emp(41) LOOP
        dbms_output.put_line(v_emp1.id ||' '||v_emp1.first_name);
   END LOOP;
end;

--异常处理
   e_expname exception;                声明异常
   raise e_expname;                   抛出异常
   when e_expname then                 异常处理

declare
   v_emp s_emp%rowtype;
begin
   select * into v_emp from s_emp where id=100;
   dbms_output.put_line(v_emp.first_name);
exception
   when NO_DATA_FOUND then
        dbms_output.put_line('没有找到数据!');
end;

--自定义异常

declare
        e_myException exception;
        v_emp s_emp%rowtype;
begin
        select * into v_emp from s_emp where id=12;
        if v_emp.salary >1000 then
           raise e_myException;
        end if;
        dbms_output.put_line(v_emp.salary);
exception
        when no_data_found then
             dbms_output.put_line('没有找到数据');
        when e_myException then
             dbms_output.put_line('工资太低!');
             --update s_emp set salary=salary+1000 where id=v_emp.id;
        when others then
             dmbs_output.put_line('其它异常');
end;


--之前写的代码块无法利用,现在考虑对代码块进行复用
--存储过程

create or replace procedure lilp_proc_hello as
--声明部分

begin
    dbms_output.put_line('hello nihao!');
end;

-- 调用存储过程
begin
lilp_proc_hello;
end;

create or replace procedure lilp_proc_hello(p_id s_emp.id%type) as
--声明部分
   v_emp s_emp%rowtype;

begin
   select * into v_emp from s_emp where id=p_id;
   dbms_output.put_line(v_emp.first_name);
end;

begin
lilp_proc_hello(11);
end;

--参数模式 IN   OUT     IN OUT

create or replace procedure lilp_proc_hello(
       p_id s_emp.id%type,
       p_name OUT s_emp.first_name%type
       ) as
--声明部分
   v_emp s_emp%rowtype;

begin
   select * into v_emp from s_emp where id=p_id;
   p_name:=v_emp.first_name;
end;

declare
   v_name s_emp.first_name%type;
begin
   lilp_proc_hello(1,v_name);
   dbms_output.put_line(v_name);
end;

--out 参数从存储过程内部传值出来,并不会所值带到存储过程中
--int 模式的参数只能传值到存储过程中,并不以从存储过程中带值出来
--in out 模式的参数即可以写操作也可以做读操作

--存储形参声明时不要指定长度

--形参可以使用默认值可以使用名字标识法

--FUNCTION   一定要有 return 语句

create or replace function lilp_fun_add(
       p_a number,
       p_b number
)return number is
   v_ret number;
begin
   v_ret:=p_a+p_b;
   return v_ret;
end;

declare
   v_ret number;
begin
   v_ret:= lilp_fun_add(2,3);
   dbms_output.put_line(v_ret);
end;

--包 package

--包规范 [ 声明变量类型异常游标   存储过程函数] 和名实体[实现] 独立存在于数据当中

create or replace package pack_emp as
       v_emp s_emp%rowtype;
       procedure addEmp(p_emp s_emp%rowtype);
end pack_emp;

create or replace package body pack_emp as
       procedure addEmp(p_emp s_emp%rowtype) as
       begin
                 insert into s_emp(id,last_name,first_name,salary) values(p_emp.id,p_emp.last_name,p_emp.first_name,p_emp.salary);
                 commit;
       end;
end pack_emp;

--调用某个包下面的存储过程

declare

begin
    pack_emp.v_emp.id:=56;
    pack_emp.v_emp.last_name:='lala';
    pack_emp.v_emp.first_name:='hehe';
    pack_emp.v_emp.salary:=2000;
    pack_emp.addEmp(pack_emp.v_emp);
end;

--声明重载
create or replace package pack_emp as
       procedure addEmp(p_emp s_emp%rowtype);
       procedure addEmp(p_name,s_emp.first_name%type);
       procedure addEmp();
end pack_emp;

--类型要不是不同的类型系列