plsql 语言-秋月博客

组合索引前面放的值要重复值少一点，性能高 plsql 过程化查询块结构 1 匿名的plsql 没有取名字，以块的结构在使用，注意用来测试，不能被调用语法：以declare 开头，也是声明部分

组合索引前面放的值要重复值少一点，性能高

plsql 过程化查询
块结构

1 匿名的plsql

        没有取名字，以块的结构在使用，注意用来测试，不能被调用
        语法：以declare 开头，也是声明部分，是可选可不选的，当有变量声明的时候需啊哟有declare，没有变量的时候可以不写declare，变量，常量，数据类型，游标写在declare后面
,declare 后面跟上执行语句，以begin 开头
declare
        变量声明，
        常量声明(constant)，
        begin
                赋值，
                sql（select，dml，ddl，dcl）
                结构/分支/循环
        exception when not exis then -- 程序的异常处理
        end；

2 有名的plsql

        有自己的名字，能被程序调用
        存储过程，函数，包，触发器
        语法：
        create or replace procdure <> (name in|out type,****) is|as
        声明
        declare
                begin
                        sql语句
                exception
                end procude_name
        函数：

create or replace procdure <> (name in|out type,****) is|as
        声明
        declare
                begin
                        sql语句
                exception
                end procude_name
        函数：
                create or replace function <> (name in|out type,***)
                return type
                is|as
                declare
                        begin
                                return ***
                        excepion
                                return ***
                        end function_name

        包：过程和包封装在一起
                包头：声明变量常量，过程，函数
                        create or replace package <>
                        is|as
                        变量，常量，过程，函数，游标（共有的）
                        end package_name

                包体：create or replacey package day <> is|as
                变量，常量，过程，函数，（私有的）
                        begin
                                sql
                        excepion

end

触发器：trigger

DML触发：insert，update，delete 的时候触发某个动作
替代：instead of,create,alter,drop
事物:database,schema,logon|login

                create or replace trigger <>
                [insert|update***]
                begin
                        DML
                END trigger_name

____________________________

1 变量的声明
var datatype；变量必须是字符开头的，不可以包含特殊字符，可以字符或者数组下划线，不能超过30个字符，必须是分号结束；
char(); 32767个字符
varchar2();变长的字符类型，长度可以达到32767个字符
number：包括浮点性的字符性，可以达到30位
int:整型
long：长整型，2G-1 个长度
clob：二进制字符长度，也叫做大对象字符类型4G-1个长度
blog：多媒体字符，4G-1
date：存储年月日十分秒，微秒
rowid:指的是数据块在数据库存储的位置
注意：变量在声明的时候可以赋值：
        var number；
        var number := 10;
        var number := &e; &e 需要在键盘上手动输入
        var varchar2(20);
        var varchar2(20) := 'hello word';
        var varchar2(20) := '&abc';

var date today :=sysdate|to_date('2016-11-18','yyyy-mm-dd');

%type 匹配数据类型
var v_name emp.ename%type; 就是匹配数据库类型

SQL> declare  2  v_sal emp.sal%type;  3  v_name emp.ename%type;  4  begin  5  select a.ename,a.sal into v_name,v_sal from emp a where a.empno = 7788;  6  dbms_output.put_line(v_name||' '||v_sal);  7  end;  8  /SCOTT 3000PL/SQL procedure successfully completed.

%rowtype 匹配所有的列属性

SQL> declare  2  v_emp emp%rowtype;  3  begin  4  select a.* into v_emp from emp a where empno = 7788;  5  dbms_output.put_line(v_emp.ename);  6  dbms_output.put_line(v_emp.sal);  7  end;  8  /SCOTT3000

自定义数据类型,符合变量

type type_name is record(name1,name2,**)

上面的是全部匹配表中所有字段，那如果我只要匹配部分字段，怎么办了？

SQL> declare   2  type empsal is record (vname varchar(20),vsal number(6));  3  v_name_sal empsal;  4  begin  5  select a.ename,a.sal into v_name_sal from emp a where a.empno = 7788;  6  dbms_output.put_line(v_name_sal.vname||' '||v_name_sal.vsal);  7  end;  8  /SCOTT 3000PL/SQL procedure successfully completed.# 自定义数据类型，也叫做符合类型，当然自定义类型里面的类型如果不自动原表中字段是什么类型，可以在里面使用%type

2 游标

2.1 隐式游标

由oracle内部自动定义的游标，叫做隐式游标。隐式游标通常是以sql开头的，一般由DML 调用，用来返回某个值。

sql%rowcount 统计记录中的总行数

sql%found 用来判断数据在表中是否存在。值是true

sql%notfound 用来判断是否找不到，返回值为true

sql%isopen 用户判断游标是否已经打开

2.2 显式游标

# loop 循环SQL> declare  2  v_name emp.ename%type;  3  v_sal emp.sal%type;  4  cursor cur_emp is  5  select ename,sal from emp;  6  begin  7  if cur_emp%isopen then  8  close cur_emp;   9  end if; 10  open cur_emp;                           # 打开游标 11  loop 12  fetch cur_emp into                      # 取数据 13  v_name,v_sal; 14  exit when cur_emp%notfound; 15  dbms_output.put_line(v_name||'    '||v_sal); 16  end loop; 17  close cur_emp;                          # 关闭游标 18  end;# 下面的 用自定义类型  1  declare  2  cursor cur_dept is  3  select * from dept;  4  mess cur_dept%rowtype;  5  begin  6  if cur_dept%isopen then  7  close cur_dept;  8  end if;  9  open cur_dept; loop 10  fetch cur_dept into mess; 11  exit when cur_dept%notfound; 12  dbms_output.put_line(mess.deptno||'  '||mess.dname); 13  end loop; 14  close cur_dept; 15* end;SQL> /10  ACCOUNTING20  RESEARCH30  SALES40  OPERATIONS50  DBA60  SA70  SA171  SA272  SA380  4G90  IOS# 用for循环，不需要打开游标，自动抓取SQL> declare  2  begin  3  for i in 1 .. 10 loop  4  dbms_output.put_line(i);  5  end loop;  6  end;  7  /12345678910PL/SQL procedure successfully completed.SQL> begin  2  for i in (select * from dept) loop  3  dbms_output.put_line(i.deptno||'   '||i.dname);  4  end loop;  5  end;  6  /10   ACCOUNTING20   RESEARCH30   SALES40   OPERATIONS50   DBA60   SA70   SA171   SA272   SA380   4G90   IOSPL/SQL procedure successfully completed.# while 循环  1  declare  2  v1 number :=1;  3  begin  4  while v1 < 11 loop  5  v1 := v1+1;  6  dbms_output.put_line(v1);  7  end loop;  8* end;SQL> /234567891011PL/SQL procedure successfully completed.

游标后面还可以传值，cursor cur_emp (v_name,****) select ****[这里可以更上for update of column wait|no wait 锁]

当有事务执行这个操作的时候，可以等待或者不等待。

游标用完要关闭，游标存放在pga中，如果不关闭的话，pga内存会一直会占用。

SQL> l  1  declare  2  cursor cur_d is  3  select deptno,dname from dept;  4  d_mess cur_d%rowtype;  5  6  cursor cur_e (v_deptno number) is  7  select ename from emp where deptno = v_deptno;  8  e_mess cur_e%rowtype;  9 10  begin 11  if cur_d%isopen then 12  close cur_d; 13  end if; 14  open cur_d; 15  loop 16  fetch cur_d into d_mess; 17  exit when cur_d%notfound; 18  dbms_output.put_line(d_mess.dname||'------'); 19  if cur_e%isopen then 20  close cur_e; 21  end if; 22  open cur_e(d_mess.deptno); 23  loop 24  fetch cur_e into e_mess; 25  exit when cur_e%notfound; 26  dbms_output.put_line('------'||e_mess.ename); 27  end loop; 28  close cur_e; 29  end loop; 30  close cur_d; 31* end;# 游标嵌套循环传参数

3 动态plsql

execute immediate ‘’；

  1  begin  2  execute immediate 'create table tmp1 (int number)';  3* end;SQL> /PL/SQL procedure successfully completed.# 程序中建表必须要用动态sqlSQL> begin        2  execute immediate 'insert into tmp1 (int) values (:1)' using 10;  3  commit;  4  end;  5  /PL/SQL procedure successfully completed.SQL> select * from tmp1;       INT----------	10# 动态sql 使用站位符

alter index index_name rebuild

declarecursor cur_index isselect index_name from user_indexes;v_index cur_index%rowtype;beginif cur_index%isopen thenclose cur_index;end if; open cur_index;loopfetch cur_index into v_index;exit when cur_index%notfound;dbms_output.put_line(v_index.index_name);execute immediate 'alter index '|| v_index.index_name||' rebuild';end loop;close cur_index;end;/SYS_C0010994PK_EMPPK_DEPTPL/SQL procedure successfully completed.

碎片整理

alter table table_name enable row movement;

alter table table_name shrink space ;

alter table table_name disable row movement;

declarecursor cur_index isselect table_name from user_tables;v_index cur_index%rowtype;beginif cur_index%isopen thenclose cur_index;end if; open cur_index;loopfetch cur_index into v_index;exit when cur_index%notfound;dbms_output.put_line(v_index.table_name);execute immediate 'alter table '|| v_index.table_name||' enable row movement';execute immediate 'alter table '|| v_index.table_name||' shrink space';execute immediate 'alter table '|| v_index.table_name||' disable row movement';end loop;close cur_index;end;/EMPNEWTMP1EMP1DEPT1TMP_WZL_20161125_1SALGRADEBONUSEMPDEPTPL/SQL procedure successfully completed.

批量删除表，闪会表

declarecursor cur_index isselect table_name from user_tables;v_index cur_index%rowtype;beginif cur_index%isopen thenclose cur_index;end if; open cur_index;loopfetch cur_index into v_index;exit when cur_index%notfound;dbms_output.put_line(v_index.table_name);execute immediate 'drop table '|| v_index.table_name;end loop;close cur_index;end;/EMPNEWTMP1EMP1DEPT1TMP_WZL_20161125_1SALGRADEBONUSEMPDEPTPL/SQL procedure successfully completed# 所有表都已经删除

declarecursor cur_index isselect object_name from recyclebin whereto_date(droptime,'yyyy-mm-dd:HH24:MI:SS') > sysdate -1; v_index cur_index%rowtype;beginif cur_index%isopen thenclose cur_index;end if; open cur_index;loopfetch cur_index into v_index;exit when cur_index%notfound;dbms_output.put_line(v_index.object_name);execute immediate 'flashback table "'|| v_index.object_name||'" to beforedrop';end loop;close cur_index;end;/

4 type

type type_name is table of number;

5 bulk collect into v_1

bulk connect 是用列plsql 表，数据into 里面就相当与一个数组，可以放入多条数据，可以不使用游标

declaretype v_type is table of number;v_mess v_type;beginexecute immediate 'select sal  from emp where deptno = 10' bulk collect into v_mess;dbms_output.put_line(v_mess(1));dbms_output.put_line(v_mess(2));dbms_output.put_line(v_mess(3));dbms_output.put_line(v_mess.count);end;/3675750019503

6 关联游标

type r1 is ref cursor

  1  declare  2  type cur_type is ref cursor;  3  v1 cur_type;  4  v_ename emp.ename%type;  5  v_sal emp.sal%type;  6  begin  7  open v1 for 'select ename,sal from emp';  8  loop  9  fetch v1 into v_ename,v_sal; 10  exit when v1%notfound; 11  dbms_output.put_line(v_ename||'  '||v_sal); 12  end loop; 13* end;SQL> /SMITH  800ALLEN  1600WARD  1250JONES  2975MARTIN	1250BLAKE  2850CLARK  3675SCOTT  3000KING  7500TURNER	1500ADAMS  1100JAMES  950MILLER	1950PL/SQL procedure successfully completed.

关联游标的优点就是不在申明部分写死游标，只需要申明一个游标类型，在begin 部分动态的取定义游标即可

7 分支判断

7.1 if ……then ……end if;

7.2 if ……then ……else ……end if;

7.3 if ……then ……elsif ……then ……else ……end if;

SQL> declare  2  type v_type is ref cursor;  3  v_name emp.ename%type;  4  v_sal emp.sal%type;  5  v_cur v_type;  6  begin  7  open v_cur for 'select ename,sal from emp';  8  loop  9  fetch v_cur into v_name,v_sal; 10  exit when v_cur%notfound; 11  if v_sal < 1500 then 12  dbms_output.put_line(v_name||' your sal is too low,please hurry up'); 13  elsif v_sal >= 1500 and v_sal <3000 then 14  dbms_output.put_line(v_name||' your sal is good'); 15  else 16  dbms_output.put_line(v_ename||' you are tuhao'); 17  end if; 18  end loop; 19  close v_cur; 20  end; 21  /dbms_output.put_line(v_ename||' you are tuhao');                     *ERROR at line 16:ORA-06550: line 16, column 22:PLS-00201: identifier 'V_ENAME' must be declaredORA-06550: line 16, column 1:PL/SQL: Statement ignoredSQL> l16 16* dbms_output.put_line(v_ename||' you are tuhao');SQL> c/v_ename/v_name 16* dbms_output.put_line(v_name||' you are tuhao');SQL> l  1  declare  2  type v_type is ref cursor;  3  v_name emp.ename%type;  4  v_sal emp.sal%type;  5  v_cur v_type;  6  begin  7  open v_cur for 'select ename,sal from emp';  8  loop  9  fetch v_cur into v_name,v_sal; 10  exit when v_cur%notfound; 11  if v_sal < 1500 then 12  dbms_output.put_line(v_name||' your sal is too low,please hurry up'); 13  elsif v_sal >= 1500 and v_sal <3000 then 14  dbms_output.put_line(v_name||' your sal is good'); 15  else 16  dbms_output.put_line(v_name||' you are tuhao'); 17  end if; 18  end loop; 19  close v_cur; 20* end;SQL> /SMITH your sal is too low,please hurry upALLEN your sal is goodWARD your sal is too low,please hurry upJONES your sal is goodMARTIN your sal is too low,please hurry upBLAKE your sal is goodCLARK you are tuhaoSCOTT you are tuhaoKING you are tuhaoTURNER your sal is goodADAMS your sal is too low,please hurry upJAMES your sal is too low,please hurry upMILLER your sal is good

7.4 case

case 条件 when 1 then ；when 2 then ；end case；

case when 条件1 then ；when 条件2 then ；end case；

7.5 条用程序

exec <>

call <>

8 异常

8.1 预定义异常

由oracle内部已经定义好的异常，在用户的使用过程中遇到某种错误，由oracle内部自动引发的异常

（异常名字，异常的代码，异常的信息）

SQL> select text from dba_source where name = 'STANDARD' AND TEXT LIKE '%EXCE%';TEXT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');    pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');    pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');    pragma EXCEPTION_INIT(INVALID_CURSOR, '-1001');    pragma EXCEPTION_INIT(NOT_LOGGED_ON, '-1012');    pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');# 例如下面的这个错误报错编码1017，在上面就可以看到是登录异常SQL> CONN scott/tigerrERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.

8.2 异常错误处理方法

bash-4.1$ oerr ora 101701017, 00000, "invalid username/password; logon denied"// *Cause:// *Action:# 在oracle 用户下执行上面的oerr 命令就可以看到解释，原因，处理方法

8.3 非预定义异常

（异常的代码，异常的信息，没有异常的名字）

e1 exception; 自定义异常的名字

pragma exception_init(e1,942);绑定异常

  1  declare  2  e1 exception;  3  pragma exception_init(e1,-942);  4  begin  5  execute immediate 'select * from dept ';  6  exception  7  when e1 then  8  dbms_output.put_line('we not have this table');  9* end;SQL> /we not have this tablePL/SQL procedure successfully completed.

8.4 自定义异常

用户在书写程序的时候让程序在某种情况下抛出异常

自定义异常代码从-20000 到 -20999

（没有异常信息，没有异常名，异常代码）

e2 exception

pragma exception_init(e2,-20001);

raise e2;

SQL> declare   2  v1 number :=1;  3  v2 number :=0;  4  begin  5  v1 := v1/v2;  6  end;  7  /declare*ERROR at line 1:ORA-01476: divisor is equal to zeroORA-06512: at line 5# 上面报错了，我们下面去查一下1476 异常是什么原因和名字叫什么SQL> select text from dba_source where name = 'STANDARD' AND TEXT LIKE '%1476%';TEXT--------------------------------------------------------------------------------    pragma EXCEPTION_INIT(ZERO_DIVIDE, '-1476');# 报错名字叫ZERO_DIVIDE 由于除数为0导致的，我们下面做一下异常抛出  1  declare  2  v1 number :=1;  3  v2 number :=0;  4  begin  5  v1 :=v1/v2;  6  exception  7  when ZERO_DIVIDE then  8  dbms_output.put_line('zeor');  9* end;SQL> /zeorPL/SQL procedure successfully completed.

  1  declare  2  v_name emp.ename%type;  3  v_sal emp.sal%type;  4  e1 exception;  5  pragma exception_init(e1,'-20011');  6  cursor cur_e is select ename,sal from emp;  7  begin  8  open cur_e ;  9  loop 10  fetch cur_e into v_name,v_sal; 11  exit when cur_e%notfound; 12  if v_sal < 3000 then 13  raise e1; 14  end if; 15  exception 16  when e1 then 17  dbms_output.put_line('is too small') 18  end loop; 19  close cur_e; 20* end;

raise_application_error('skfjsdlfdl','-20034');

目录CONTENT

plsql 语言

评论区