经典SQL学习笔记 (七) - pl/sql编程一

pl/sql编程

------------------------pl/sql编程----------------------------（procedual）：oracle 在标准sql语言上的扩展简单分类：块（存储过程，函数，包，触发器）declare --定义部分begin  --执行部分exception  ---异常处理部分end；   --结束set serveroutput on  --输出开关开启1.-----------------------第一个块------------------------------------------------declarev_name varchar2(20);beginv_name:='王照陆爱编程';  -- (:= 赋值)dbms_output.put_line(v_name);end;2.-------------------------------------------------------------------------------declare v_ename varchar2(20);beginselect ename into v_ename from emp where empno=7788;dbms_output.put_line(v_ename);end;3.------------------------------------------------------------------------------例子：根据员工的编号，查询出姓名、工资，并把姓名和工资输出declare v_ename varchar2(20);v_sal number(7,2);beginselect ename,sal into v_ename, v_sal from emp where empno=&no;   --&no:从键盘输入dbms_output.put_line(v_ename || '------'|| v_sal);end;4.--------------------------------------------------------------------------------插入一条数据，把插入的数据输出---------------------------------------------------------建表create table wzl(deptno number,dname varchar2(20),loc varchar2(10));returning----用于语句中所影响到的数据行into ：把表中的信息赋予一个变量--方法一：insert into  values(80,'dd','dd');declarev_deptno number;v_dname varchar2(20);v_loc varchar2(10);begininsert into wzl values(80,'dd','dd') returning deptno,dname,loc into v_deptno,v_dname,v_loc;dbms_output.put_line(v_deptno || '---'|| v_dname||'---'||v_loc);end;--方法二：declare v_info varchar2(30);begininsert into wzl values(80,'dd','dd') returning deptno||dname||loc into v_info;dbms_output.put_line(v_info);end;--更新一条数据，把更新的数据输出----------------------------------------------------declarev_deptno number;v_dname varchar2(20);v_loc varchar2(10);beginupdate wzl set dname='王照陆' where dname='dd' returning deptno,dname,locinto v_deptno,v_dname,v_loc;update wzl set loc='南京' where loc='dd' returning deptno,dname,locinto v_deptno,v_dname,v_loc;dbms_output.put_line(v_deptno || '---'|| v_dname||'---'||v_loc);end;--删除一条数据，把删除的数据输出--------------------------------------------------------复合类型、记录类型----------------------------------------------------------------语法：type 记录类型的名字 is record (v1 type,v2 type,.....);--------------------------------------------------------------------------------declaretype rec is record(deptno number(4),dname varchar2(20),loc varchar2(10));v_r rec;beginupdate wzl set deptno=80 where deptno=60 returning deptno,dname,locinto v_r.deptno,v_r.dname,v_r.loc;dbms_output.put_line(v_r.deptno || '---'|| v_r.dname||'---'||v_r.loc);end;--数组类型-----------------------------------------------------------------------语法：type 数组类型名 is varray （size）of date_typedeclaretype array is varray(4) of varchar2(10);v_a array;beginv_a:=array('dd','cc','ff','gg');--对数组的 赋值dbms_output.put_line(v_a(1)||'--'||v_a(2)||'--'||v_a(3)||'--'||v_a(4));dbms_output.put_line(v_a.first||'--'||v_a.last);end;first --取数组下标的最小值last  --取数组下标的最大值--%type----------------------------------------------------------------语法：表名.字段名%type--%rowtype-------------------------------------------------------------------------记录类型用法：表%rowtype--pl/sql的控制语句----------------------------------------------------------------if 条件 thenelsif 条件 then.........else end if;-----------------1.输入雇员的编号，查询雇员的工资，如果工资小于1500，显示工资太低了如果工资大于5000，显示工资太高了declarev_sal emp.sal%type;v_comment varchar2(20);beginselect sal into v_sal from emp where empno=&no;if v_sal<1500 then v_comment:='工资太低了';elsif v_sal>5000 then v_comment:='工资太高了';end if;dbms_output.put_line(v_comment);end;2.输入雇员的编号，查询出雇员的姓名和雇佣日期，如果雇佣日期万元万余晚于1988-01-01，奖金是1600，如果雇佣日期万元万余晚于1990-01-01，奖金是800，其他情况，奖金是2400，请输出雇员的姓名，受雇日期和奖金declarev_info emp%rowtype;v_bonus number(10);beginselect * into v_info from emp where empno=&no;if v_info.hiredate>to_date('1988-01-01','yyyy/mm/dd') thenv_bonus:=1600;elsif v_info.hiredate>to_date('1990-01-01','yyyy/mm/dd') thenv_bonus:=800;else  v_bonus:=2400;end if;dbms_output.put_line(v_info.ename||'--'||v_info.hiredate||'--'||v_bonus);end;--------------------------------------------------------------------------------case 语句case 条件表达式 when 结果1 then when 结果2 then ... else 结果 end case；1.输入成绩，是A，输出excellent，           是B 输出verygood，           是C，输出good，           其他显示 no gradedeclarev_grade char(1):='&grade';v_comment varchar2(10);begincase v_gradewhen 'A' then v_comment:='excellent';when 'B' then v_comment:='verygood';when 'C' then v_comment:='good';else v_comment :='no grade';end case;dbms_output.put_line(v_comment);end;--循环语句-----------------------------------------------------------------------loop语法：loop .....exit when 条件end loop；declarev_int number:=1;begindbms_output.put_line(v_int);loop v_int:=v_int+1;dbms_output.put_line(v_int);exit when v_int>=10;end loop;end;------------------while 循环语法：while 条件 loop.........end loop；declarev_int number:=1;beginwhile v_int<=10 loop  dbms_output.put_line(v_int);v_int:=v_int+1;end loop;end;------------------for 循环for 变量名 in [reverse] 最小值...最大值loop.....end loop变量名：名字，不需要定义，numberin：自动加1in reverse ：自动减1beginfor v_int in reverse in 1...10loop dbms_output.put_line(v_int); end loop;end;-----------练习题部分-------------------------------------------------------------1、创建一张表，先向表中插入数据10，然后依次插入20到25的值2遍create table temp_wzl(no number(3));declarev_num number(2):=10;begininsert into temp_wzl(no) values(v_num);for v_i in 1..2 loop  for v_num in 20..25 loopinsert into temp_wzl(no) values(v_num);  end loop;end loop;end;2、定义一个数组类型，并给这个数组类型赋值SALESMAN,CLERK,MANAGER,ANALYST并针对数组中的每个岗位，求出该岗位的雇员人数，并输出岗位和人数declare  type jobs_array is varray(4) of varchar2(20);  v_job jobs_array;  v_counter number(2);begin   v_job:=jobs_array('SALESMAN','CLERK','MANAGER','ANALYST');  for i in v_job.first..v_job.last loop    select count(*) into v_counter from emp where job=v_job(i);    dbms_output.put_line(v_job(i)||'---'||v_counter);    end loop;end;3、求出100到110之间的素数declarev_num number(3):=100;v_i number(3);v_counter number(3):=0;begin  while v_num<110 loop  v_i:=2;  loop    if mod(v_num,v_i)=0 then      v_i:=0;      exit;    end if;  v_i:=v_i+1;  exit when v_i>v_num-1;  end loop;  if v_i>0 then    v_counter:=v_counter+1;    dbms_output.put_line('第'||v_counter||'的素数'||v_num);    end if;    v_num:=v_num+1;    end loop;end;--块存储过程（procedure）---------------------------------------------------------or replace --替换create [or replace] procedure 存储过程的名字(arg1 {in|out|inout} type1,arg2 {in|out|inout} type2.....)is|as<变量，常量的声明>begin.....exception....end;模式in:将实参的值传递给形参，进入存储过程的内部，但只能读不能写out:会忽略调用的实参值，返回时，形参的值会赋予实参，（变量）inout：例：输入雇员名，返回雇员的工资create or replace procedure sp1(spno in number,spsal out number)isbegin  select sal into spsal from emp where empno=spno;end;exec 存储过程名（）;call 存储过程名（）;块中进行调用declarev1 number(7,2);begin  sp1(7788,v1);    dbms_output.put_line(v1);end;---------------------------create or replace procedure sp2(spno in number)isspsal emp.sal%type;begin  select sal into spsal from emp where empno=spno;      dbms_output.put_line(spsal);end;--------------------------------------------------------------------------------例：写一个过程，输入员工的编号，返回该雇员的姓名和工作create or replace procedure sp1_wzl(spno number,spname out varchar2,spjob out varchar2) --in可以省略不写asbeginselect ename,job into spname,spjob from emp where empno=spno;end;declarev_name varchar2(10);v_job varchar2(10);beginsp1_wzl(7788,v_name,v_job); dbms_output.put_line(v_name||'---'||v_job);end;--函数---------------------------------------------------------------------------create function 函数名(arg1 {in|out|inout} type1,arg2 {in|out|inout} type2.....)is|as<变量，常量的声明>begin.....return expression;exception....end;输入雇员的编号，返回雇员的一年的总收入create or replace function f1(spno number) return numberis  v_income emp.sal%type;begin  select nvl2(comm,sal+comm,sal)*12 into v_income from emp where empno=spno;  return v_income;end;declarev1 emp.sal%type;beginv1:=f1(7788); dbms_output.put_line(v1);end;--------------------------------------------------------------------------------例：写一个过程，输入员工的编号，返回该雇员的姓名create or replace function f_name(spno number) return varchar2as  v_name emp.ename%type;begin  select ename into v_name from emp where empno=spno;  return v_name;end;declarev1 emp.ename%type;beginv1:=f_name(7788); dbms_output.put_line(v1);end;--游标（cursor）-----------------------------------------------------------------分类：静态游标（显示游标、隐式游标）、动态游标静态游标显示游标（过程）1、声明游标    cursor 游标名 is select 语句;2、打开游标    open 游标名；3、提取数据    fetch 游标名 into 变量（记录类型）4、关闭游标    close 游标名；游标的属性：noutfound: 没有发现found：发现isopen：是否打开rowcount：行数，访问或修改了多少条数据用法： 游标名%属性