PLSQL编程

一、块结构
[declare
--声明变量
变量名 类型[:=值]
]
begin
具体代码
[exception
--异常的处理
]
end;
二、复合类型变量
1.表类型：用于存放某一数据类型的值
定义:type 名称 is table of 存放数据类型 index by binary_integer;
声明:变量名 名称;
例：
declare
type emp_empno is table of number index by binary_integer;
v_empno emp_empno;
begin
v_empno(0):=7788;
v_empno(1):=7839;
v_empno(2):=7369;
dbms_output.put_line(v_empno(0));
end;
2.记录类型：可用于存放多种类型的值
定义：type 名称 is record(存放变量名1 存放数据类型,...);
声明：变量名 名称;
例：
declare
type emp_record is record(ename varchar2(20),sal number);
v_record emp_record;
begin
v_record.ename:='略略略';
v_record.sal:=11000;
dbms_output.put_line(v_record.ename||v_record.sal);
end;
三、条件逻辑
if 条件判断 then
...
elsif 条件判断 then
...
else
...
end if;
四、循环
1.
loop
具体代码
exit when 条件判断
end loop;
2.
for 循环变量 in [reverse] 循环开始数字..循环结束数字|(select * from 表名) loop
具体代码
end loop;
3.
while 条件判断 loop
具体代码
end loop;
五、游标
1.隐示游标
begin
for i in(select * from emp) loop
  dbms_output.put_line('姓名:'||i.ename||' 部门:'||i.deptno||' 年薪:'||12*(i.sal+nvl(i.comm,0)));
end loop;
end;
2.显示游标
declare
--声明变量
rowobj emp%rowtype;
--声明游标       
cursor mycursor is select * from emp;
begin
--打开游标
open mycursor ;
--抓取数据
loop
  fetch mycursor into rowobj;
  exit when mycursor%notfound;
  dbms_output.put_line('姓名:'||rowobj.ename||' 部门:'||rowobj.deptno||' 年薪:'||12*(rowobj.sal+nvl(rowobj.comm,0)));
end loop;
--关闭游标      
close mycursor;
end;
3.动态游标
declare 
--声明变量
rowobj emp%rowtype;
sqlVar varchar2(500);
--声明游标  
mycursor sys_refcursor;
begin
sqlVar:='select * from emp';
--打开游标
open mycursor for sqlVar;
--抓取数据
loop
fetch mycursor into rowobj;
dbms_output.put_line('姓名:'||rowobj.ename||' 部门:'||rowobj.deptno||' 年薪:'||12*(rowobj.sal+nvl(rowobj.comm,0)));
exit when mycursor%notfound;--退出条件，没有数据
end loop;
--关闭游标      
close mycursor;
end;
六、存储过程
例：实现大写字母转小写字母（lower(string)函数）的功能
create or replace function myfunction1(str1 varchar2) 
return varchar2
is
n number;
str2 varchar2(200):='';
begin
  for i in 1..length(str1) loop
  n:=ascii(substr(str1,i,i));
  if ascii(substr(str1,i,i)) between ascii('A') and ascii('Z') then
n:=n+32;
  end if;
  str2:=concat(str2,chr(n));
  end loop;
  return str2;
end;
七.触发器
当特定的DML语句(insert,update或delete)运行之前或之后自动运行的过程
create or replace trigger 触发器名
before|after insert[ or delete[ or update]] on 表名 for each row
begin
具体代码

end;

/*1.使用游标输出 scott中所有的雇员名称，部门名称，年薪*/--显示游标declare     --声明变量     rowobj emp%rowtype;     --声明游标            cursor mycursor is select * from emp;begin     --打开游标     open mycursor ;     --抓取数据     loop          fetch mycursor into rowobj;          exit when mycursor%notfound;          dbms_output.put_line('姓名:'||rowobj.ename||' 部门:'||rowobj.deptno||' 年薪:'||12*(rowobj.sal+nvl(rowobj.comm,0)));     end loop;     --关闭游标           close mycursor;end;/*2.定义存储过程 可以传入以下参数   query(ename,job,sal)   如果传入了某几个参数 以参数组合的形式查询结果   比如调用过程如下   query('Cleck',null,null);   查询的sql为   select * from emp where ename like '%Cleck%';    query('Cleck','Manager',null);   查询的sql为   select * from emp where ename like '%Cleck%' and job like '%Manager%'   要求输出查询的结果*/--(1)创建存储过程  create or replace procedure myquery(v_name varchar2, v_job varchar2, v_sal varchar2) as      --复制表所有行      rowobj emp%rowtype;      sqlVar varchar2(2000):= 'select * from emp where 1=1';      --定义动态游标      mycursor sys_refcursor;  begin      --条件逻辑      if(v_name is not null) then          sqlVar:=sqlVar||'and ename like '''||v_name||'''';      end if;      if(v_job is not null) then          sqlVar:=sqlVar||'and job like '''||v_job||'''';      end if;      if(v_sal is not null) then          sqlVar:=sqlVar||'and sal='||v_sal;      end if;      --dbms_output.put_line(sqlVar);      --使用游标      open mycursor for sqlVar;      loop        fetch mycursor into rowobj;        exit when mycursor%notfound;        dbms_output.put_line('['||rowobj.ename||']['||rowobj.job||']['||rowobj.sal||']');      end loop;      close mycursor;  end;--(2)传入参数测试begin    dbms_output.put_line('【参数1、2、3：null,null,null】');    myquery(null,null,null);    dbms_output.put_line('【参数1、2、3：null,'''||'CLERK'||''',null】');    myquery(null,'CLERK',null);    dbms_output.put_line('【参数1、2、3：'''||'SCOTT'||''',null,null】');    myquery('SCOTT',null,null);end;/*3.查找出输入的用户下，每张表的记录数，（提示：使用 tab表）以scott用户为例，结果应如下：?  DEPT...................................4?  EMP...................................14?  BONUS.................................0?  SALGRADE.............................5*/declare     v_sql varchar(2000);    cou number;    mycursor sys_refcursor ;begin    for i in (select * from user_tables) loop        v_sql:='select count(rowid) from '||i.table_name;        --游标操作        open mycursor for v_sql;        loop          fetch mycursor into v_sql;          exit when mycursor%notfound;          cou:=v_sql;        end loop;        close mycursor;        --打印语句        dbms_output.put_line(i.table_name||'...........'||cou);    end loop;end;     /*4.定义一个存储过程 传入表名    删除该表中的重复记录      比如 deleteMul(tableName)          调用 deleteMul('emp'); 必须删除表emp的重复数据  （execute immediate    using )*/create or replace procedure deleteMul(tableName varchar2)as    mycursor sys_refcursor;    v_sql varchar2(2000):='select t.column_name from user_col_comments t where t.table_name = ';    v_colnames user_col_comments.column_name%type;    leim varchar2(2000);begin    v_sql:=v_sql||''''||tableName||'''';    open mycursor for v_sql;    loop        fetch mycursor into v_colnames;        exit when mycursor%notfound;        leim:=leim||v_colnames||',';    end loop;    close mycursor;    leim:=substr(leim,1,length(leim)-1);    execute immediate 'delete from '||tableName||' e1 where  e1.rowid not in (select max(rowid) from '||tableName||' e2 group by '||leim||')';    commit;end;/*5.写出一个分页的存储过程  定义如下     tablePager(tableName,curPage,pageSize)  调用      tablePager('Emp',2,10)      查询emp表中 第二页的数据（每页显示10条 第二页就是 10-20条）*/create or replace procedure tablePager(tableName varchar2,curPage number,pageSize number)as  starIndex number:=(curPage-1)*pageSize+1;  endIndx number:=curPage*pageSize;  vSql varchar2(2000):='';  colSql varchar2(2000):='';  mycursor sys_refcursor;  myResult varchar2(2000);begin    select replace(wm_concat(column_name),',','||'' ''||') into colSql from user_tab_cols where table_name=tableName;    vSql:='select '||colSql||' from (select t.*,rownum rn from '||tableName||' t) where rn>='||starIndex||' and rn<= '||endIndx;    open mycursor for vSql;    loop       fetch mycursor into myResult;       exit when mycursor%notfound;       syso(myResult);    end loop;    close mycursor;end;