PL/SQL练习
简单结构
set serveroutput on; DECLARE v_name varchar2(20); BEGIN v_name := 'hello'; dbms_output.put_line(v_name); END; /
异常处理：
declare v_num number :=0; begin v_num := 2/v_num; dbms_output.put_line('devide function...'); exception when others then dbms_output.put_line('error....devide by'||v_num); end; /
引用类型
declare v_empno number(4); --v_ename变量使用emp表ename字段的类型 v_ename emp.ename%type; v_sal emp.sal%type; begin v_sal := 1000.123; dbms_output.put_line(v_sal); exception when others then dbms_output.put_line('error....'); end;
复合类型：
数组：table
declare --声明一个table类型（看做数组），数组的值是emp表empno字段的类型，数组下标是binary——integer类型 type type_a is table of emp.empno%type index by binary_integer; --声明一个type——a类型的数组 v_table_empno type_a; begin --下标可以为负数 v_table_empno(-1) := 1000; v_table_empno(0) := 0000; v_table_empno(1) := 1000; v_table_empno(2) := 2000; dbms_output.put_line('打印数组第二个值：'||v_table_empno(2)); exception when others then dbms_output.put_line('error....'); end;
对象：record
declare --声明一个对象record，各个成员变量引用dept表的类型 type type_record_dept is record( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); --声明上述类对应的一个对象 v_temp type_record_dept; begin --给对象赋值： v_temp.deptno := 50; v_temp.dname := 'dname---'; v_temp.loc := 'beijing'; dbms_output.put_line('打印v_temp对象：'||v_temp.deptno||', '||v_temp.dname||', '||v_temp.loc ); exception when others then dbms_output.put_line('error....'); end;
使用rowtype声明一个record对象：
declare --声明一个对象record，她的所有字段同emp所有字段 v_emp emp%rowtype; begin --给对象赋值： v_emp.empno := 1111; v_emp.ename := 'sansan'; dbms_output.put_line('打印v_temp对象：'||v_emp.empno||', '||v_emp.ename); exception when others then dbms_output.put_line('error....'); end;
select into
declare --声明 v_ename emp.ename%type; v_deptno emp.deptno%type; begin --给对象赋值，不用游标时这个select语句只能返回一条，而且必须返回一条数据 select ename,deptno into v_ename,v_deptno from emp where empno = 7369; dbms_output.put_line('打印v_temp对象：'||v_ename||', '||v_deptno); end;
执行DDL语句：execute immediate
begin --引号中，两个单引号代替一个单引号 execute immediate 'create table ttt(id varchar2(20),name varchar2(10) default ''sansan'')'; end;
分支判定：薪水小于1000加倍，大于则减半
declare --声明 v_ename emp.ename%type; v_sal emp.sal%type; begin --给对象赋值，不用游标时这个select语句只能返回一条，而且必须返回一条数据 select ename,sal into v_ename,v_sal from emp where empno = 1111; if(v_sal < 1000) then update emp set sal = sal*2 where empno=1111; dbms_output.put_line('小于1000，加倍。。'); elsif(v_sal = 1000 )then dbms_output.put_line('等于1000。。'); else update emp set sal = sal/2 where empno=1111; dbms_output.put_line('大于1000，减半。。'); end if; commit; end;
循环
--dowhile
declare --声明 i binary_integer := 1; begin loop dbms_output.put_line('第i='||i); i := i+1; exit when(i>=5); end loop; end;
--while
declare --声明 i binary_integer := 1; begin while (i < 5) loop dbms_output.put_line('第i='||i); i := i+1; end loop; end;
--for 循环
declare --声明 i binary_integer := 1; begin --顺序循环 for k in 1..5 loop dbms_output.put_line('第k='||k); end loop; --逆序循环 for k in reverse 1..5 loop dbms_output.put_line('第k='||k); end loop; end;
异常处理：
declare --声明 v_ename emp.ename%type; v_deptno emp.deptno%type; begin --给对象赋值，不用游标时这个select语句只能返回一条，而且必须返回一条数据 select ename,deptno into v_ename,v_deptno from emp where deptno=10; dbms_output.put_line('打印v_temp对象：'||v_ename||', '||v_deptno); exception --可以把这些异常记录在一张异常表中 when too_many_rows then dbms_output.put_line('异常：记录数大于一条。。。'||SQLCODE||SQLERRM); when no_data_found then dbms_output.put_line('异常：没有记录。。。'||SQLCODE||SQLERRM); when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
游标
declare --声明一个游标，不会真正取数据 cursor c is select * from emp; --声明一个record v_each c%rowtype; begin --打开游标，执行语句。游标指向第一条记录 open c; --把游标指向的数据拿到v_each中，游标自动向下移动一个 fetch c into v_each; dbms_output.put_line(v_each.ename); --关闭游标 close c; exception when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
--带参数的游标
declare --声明一个游标，不会真正取数据 cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select * from emp where deptno=v_deptno and job=v_job; --声明一个record v_each c%rowtype; begin --for循环会自动打开游标，把游标指向的数据拿到v_each中，游标自动向下移动一个 for v_each in c(30,'SALESMAN') loop dbms_output.put_line(v_each.ename); end loop; --for循环会自动关闭游标 exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
--循环遍历游标
--for循环
declare --声明一个游标，不会真正取数据 cursor c is select * from emp; --声明一个record v_each c%rowtype; begin --打开游标，执行语句。游标指向第一条记录 --for循环会自动打开游标 --open c; --for循环自动把游标指向的数据拿到v_each中，游标自动向下移动一个 for v_each in c loop dbms_output.put_line(v_each.ename); end loop; --for循环会自动关闭游标 --close c; exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
--while循环
declare --声明一个游标，不会真正取数据 cursor c is select * from emp; --声明一个record v_each c%rowtype; begin --打开游标，执行语句。游标指向第一条记录 open c; --把游标指向的数据拿到v_each中，游标自动向下移动一个 fetch c into v_each; --如果找找记录，继续向下执行 while(c%found) loop dbms_output.put_line(v_each.ename); --游标向后移动 fetch c into v_each; end loop; --关闭游标 close c; exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
--dowhile循环
declare --声明一个游标，不会真正取数据 cursor c is select * from emp; --声明一个record --v_each c%rowtype; begin --打开游标，执行语句。游标指向第一条记录 open c; --把游标指向的数据拿到v_each中，游标自动向下移动一个，自动声明v_each变量 loop fetch c into v_each; --最近的一条记录没有返回记录，退出 exit when(c%notfound); dbms_output.put_line(v_each.ename); end loop; --关闭游标 close c; exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
存储过程：
create or replace procedure ppp is替代declare，其他的一样。
执行用exec ppp;
或者：
begin
ppp;
end;
/
简单存储过程
--带参数的游标演示存储过程 create or replace procedure ppp is --声明一个游标，不会真正取数据 cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select * from emp where deptno=v_deptno and job=v_job; --声明一个record v_each c%rowtype; begin --for循环会自动打开游标，把游标指向的数据拿到v_each中，游标自动向下移动一个 for v_each in c(30,'SALESMAN') loop dbms_output.put_line(v_each.ename); end loop; --for循环会自动关闭游标 exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
带参数的存储过程：
create or replace procedure ppp2(v_a in number, v_b number, v_c out number,v_d in out number) is --三个参数，传入、传出、传入/传出三种类型。存储过程没有返回值。没有写默认是in v_temp number := 5; begin if(v_a > v_b)then v_c := v_a; v_d := v_c*100; elsif(v_a < v_b)then v_c := v_b; v_d := v_c*10; end if; exception --可以把这些异常记录在一张异常表中 when others then dbms_output.put_line('其他异常。。。'||SQLCODE||SQLERRM); end;
调用带参数的存储过程：
declare c number; d number := 10; begin ppp2(v_a => 2,v_b => 1,v_c => c,v_d => d); dbms_output.put_line('c:'||c||'d:'||d); end;
--函数有返回值，存储过程没有返回值 create or replace function sal_tax(v_sal number) return number is begin if(v_sal < 3000) then return 0; elsif(v_sal < 5000) then return v_sal*0.1; else return v_sal*0.2; end if; end;
调用：
select sal, sal_tax(sal) from emp;
综合使用的例子：
create table departments( DEPARTMENT_ID NUMBER(4) primary key, DEPARTMENT_NAME VARCHAR2(30), MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4) ); insert into departments values(1,'技术部',1,1); insert into departments values(2,'人事部',2,2); insert into departments values(3,'市场部',3,3); insert into departments values(4,'财务部',4,4);
create table employees( EMPLOYEE_ID NUMBER(6) primary key, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, JOB_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), DEPARTMENT_ID NUMBER(4) references departments(DEPARTMENT_ID) ); insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(1,'JIA','JENNY',3000,1); insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(2,'guan','rose',4000,3); insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(3,'xu','mike',2000,2); insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(4,'zhang','billy',1000,2); insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID) values(5,'jin','dede',5000,1);
声明一个存储过程，输入工资数sal，小于sal则+1，小于sal+1000则不变，大于则-1.
CREATE OR REPLACE PROCEDURE changeSal(sal in number,cnt out number)IS --定义一个EMPLOYEES表对应的类型 eachOne EMPLOYEES%ROWTYPE; --定义一个record TYPE type_record IS RECORD( ename varchar2(45), sal EMPLOYEES.SALARY%type ); --定义一个上述record类型的对象 temp type_record; --定义一个数字 total NUMBER := 0; --定义一个游标 CURSOR c IS select * from employees; BEGIN OPEN c; --读取游标第一个值，游标向后移动 fetch c into eachOne; dbms_output.put_line('存储过程正在执行，小于'||sal||'的薪水+1，小于'||(sal+1000)||'的薪水不变，大于的-1...'); while(c%found)loop --拼接名字全称 temp.ename := eachOne.first_name||eachOne.last_name; --变化薪水 if (eachOne.salary < sal)then --变化了，计数 total := total+1; temp.sal := eachOne.salary + 1; elsif(eachOne.salary < sal+1000)then temp.sal := eachOne.salary; else --变化了，计数 total := total+1; temp.sal := eachOne.salary -1; end if; dbms_output.put_line('名字全称：'||temp.ename||', 变化后的薪水：'||temp.sal); --游标向后移动 fetch c into eachOne; end loop; CLOSE c; cnt := total; END;--END PROCEDURE
调用：
declare cnt1 number; begin changeSal(sal => 2000,cnt=>cnt1); dbms_output.put_line('变化的结果：'||cnt1); end;
结果：
SQL> declare 2 cnt1 number; 3 begin 4 changeSal(sal => 2000,cnt=>cnt1); 5 dbms_output.put_line('变化的结果：'||cnt1); 6 end; 7 / 存储过程正在执行，小于2000的薪水+1，小于3000的薪水不变，大于的-1... 名字全称：JIAJENNY, 变化后的薪水：2999 名字全称：guanrose, 变化后的薪水：3999 名字全称：xumike, 变化后的薪水：2000 名字全称：zhangbilly, 变化后的薪水：1001 名字全称：jindede, 变化后的薪水：4999 变化的结果：4 PL/SQL procedure successfully completed
