1.2 %type、%rowtype、select...into
-- %type 变量的类型和数据表中的字段的数据类型一致 -- %rowtype 变量的类型和数据表中的一行记录数据类型一致 -- select ... into 变量 表中查询数据并赋值，可以一次性给多个变量赋值declare v_object CUSTOMER%Rowtype; begin select * into v_object from customer where rownum=1; dbms_output.put_line(v_object.Enterprise_Code ||‘,‘||v_object.CreateDate); end;
1.3 只可以增删改
-- insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制语句可以在pl/sql里用但DDL语句不行，表定义语言不可以在plsql中改变 declare v_name student.name%type:=‘wang‘; --声明变量并赋值 begin insert into student(id,name,age) values(2,v_name,26); --插入数据 end; -------------------------------------------- declare v_name student.name%type:=‘hexian‘; --更新数据 begin update student set name=v_name where id=1; end; begin update student set name=‘qinaide‘ where id=2; end; ------------------------------------------------- --------------2、 PLSQL流程控制
--if判断--- declare v_b boolean:=true; -- := 是赋值 begin if v_b then dbms_output.put_line(‘ok‘); end if; end; --if else判断--- declare v_b boolean:=true; begin if v_b then dbms_output.put_line(‘ok‘); else dbms_output.put_line(‘false‘); end if; end; --if elsif else判断-- declare v_name varchar2(20):=‘cheng‘; begin if v_name=‘0701‘ then dbms_output.put_line(‘0701‘); elsif v_name=‘cheng‘ then dbms_output.put_line(‘cheng‘); else dbms_output.put_line(‘false‘); end if; end; --loop循环，注意推出exit是推出循环，而不是推出整个代码块 -------------------------------------------------------- loop简化写法 declare v_i binary_integer :=0; begin loop exit when v_i>10; v_i :=v_i+1; dbms_output.put_line(‘hehe‘); end loop; dbms_output.put_line(‘over‘); end; while循环------------------------------------------------- declare v_i binary_integer:=0; begin while v_i<10 loop dbms_output.put_line(‘hello‘||v_i ); v_i:=v_i+1; end loop; dbms_output.put_line(‘over‘); end; for循环，注意不需要声明变量----------------------------------------------- begin for v_i in 0..10 loop dbms_output.put_line(‘hello‘||v_i); end loop; dbms_output.put_line(‘over‘); end; ***************************************** PLSQL异常处理 ***************************************** 1、声明异常 异常名 EXCEPTION； 2、抛出异常 RAISE 异常名 3、处理异常 抛出异常后的逻辑代码不会被继续执行 异常的定义使用 ――――――――――――――――――――――――――――――――――――― begin dbms_output.put_line(1/0); --1/0出现错误，抛出异常，输出error exception when others then dbms_output.put_line(‘error‘); end; ---------------- declare e_myException exception; --声明异常 begin dbms_output.put_line(‘hello‘); --raise抛出异常,用此关键字，抛出后转到自定义的e_myException，执行其 --里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。 raise e_myException; dbms_output.put_line(‘world‘); dbms_output.put_line(1/0); exception when e_myException then --执行异常 dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码 dbms_output.put_line(sqlerrm); --当前错误信息 dbms_output.put_line(‘my error‘); when others then dbms_output.put_line(‘error‘); end; ============================================================================================================ ***************************************** PLSQL游标 ***************************************** 备注：下面提到的游标为静态cursor，包括显示和隐式。 游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。 cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。 declare cursor c_student is select * from book; begin open c_student; close c_student; end; 第二种游标的定义方式，用变量控制结果集的数量。 declare v_id binary_integer; cursor c_student is select * from book where id>v_id; begin v_id:=10; open c_student; close c_student; end; 第三种游标的定义方式，带参数的游标，用的最多。 declare cursor c_student(v_id binary_integer) is select * from book where id>v_id; begin open c_student(10); close c_student; end; 游标的使用，一定别忘了关游标。 declare v_student book%rowtype; cursor c_student(v_id binary_integer) is select * from book where id>v_id; begin open c_student(10); fetch c_student into v_student; close c_student; dbms_output.put_line(v_student.name); end; 如何遍历游标fetch 游标的属性 %found，%notfound,%isopen,%rowcount。 %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。 %notfound，与%found行为相反。 %isopen，判断游标是否打开。 %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。 注： no_data_found和%notfound的用法是有区别的，小结如下 1）SELECT . . . INTO 语句触发 no_data_found； 2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound； 3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound； 4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。 下面是几个实例： create table BOOK ( ID VARCHAR2(10) not null, BOOKNAME VARCHAR2(10) not null, PRICE VARCHAR2(10) not null, CID VARCHAR2(10) not null ); -- %rowcount是SQL的属性表示影响了多少条记录 ======insert=================================================================== create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin insert into book(id,bookname,price) values (1,2,3); --这里会报错 o_result_msg := ‘success‘; exception when others then rollback; --o_result_msg := substr(sqlerrm, 1, 200); --返回完整错误信息 o_result_msg := substr(sqlcode, 1, 200); --返回错误码 end; ====update/delete====================================================================== create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin update book set price = ‘55‘ where bookname = i_name; delete from book where bookname = i_name; if sql%notfound then raise e_myException; end if; o_result_msg := ‘success‘; exception when e_myException then rollback; o_result_msg := ‘update or delete dail‘; end; ===select====================================================================== create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin select price into v_price from book where bookname = i_name; o_result_msg := ‘success‘; exception when no_data_found then rollback; o_result_msg := ‘select into dail‘; end; =============================================================================== loop方式遍历游标 declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); Loop Fetch c_book into v_bookname; exit when c_student%notfound; update book set price = ‘33‘ where bookname = v_bookname; End Loop; Close c_book; end; 或 declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); Fetch c_book into v_bookname; While c_book%Found Loop update book set price = ‘33‘ where bookname = v_bookname; Fetch c_book into v_bookname; End Loop; Close c_book; end; ============================================================================================ while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环 解决方法：while nvl(c_student%found,true) loop declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); while nvl(c_book%found,true) --或这种写法：while c_book%found is null or c_book%found loop Fetch c_book into v_bookname; update book set price = ‘33‘ where bookname = v_bookname; End Loop; Close c_book; end; =================================================================================================== for循环遍历，最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式) declare cursor c_book(i_id number) is select bookname from book where id = i_id; begin for cur in c_book(i_id) --直接将入参i_id传入cursor即可 loop update book set price = ‘53‘ where bookname = cur.bookname; end loop; end; ***************************************** Oracle存储过程 ***************************************** 在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程sp_get_product_prompt set serveroutput on var ret1 varchar2(200); var ret2 varchar2(200); exec sp_get_product_prompt(83,:ret1,:ret2); --或execute print ret1; print ret2; 或 set serveroutput on declare ret1 varchar2(200); ret2 varchar2(200); begin sp_get_product_prompt(83,ret1,ret2); dbms_output.put_line(ret1); dbms_output.put_line(ret2); end; 存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。 create or replace procedure say_hello( v_name in varchar2, v_flag number, o_ret out number ) as begin if v_name is null and v_flag is null then --v_name和v_flag都等于null o_ret := 10; else o_ret := 100; end if; end; 对于入参为null情况下给予缺省值 create or replace procedure say_hello( i_name in varchar2, i_flag number, o_ret out number ) as v_name varchar2(100); begin if i_name is null then v_name := ‘0‘; else v_name := i_name; end if; insert into phone(..,wname..,) values(..,v_name,..); end; 或直接在insert语句中调用nvl函数赋缺省值 insert into phone(..,wname..,) values(..,nvl(v_name,‘ ‘),..); ----如果将‘ ‘写成‘‘,则insert进来的v_name值还是为‘‘等价于null值 带一个参数的存储过程 输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量； 输入参数in，可以作为变量进行条件判断； 默认不写就是in； 存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。 create or replace procedure say_hello(v_name in varchar2) as begin --v_name:=‘a‘; --存储过程入参v_name不能做为赋值目标 dbms_output.put_line(‘hello ‘||v_name); end; 存储过程输入参数作为变量进行条件判断 create or replace procedure say_hello( i_opFlag in number ) as v_name varchar2(100); begin if i_opFlag = 1 then v_name :=‘0‘; else v_name :=‘haha‘; end if; dbms_output.put_line(‘hello ‘||v_name); end; 利用存储过程中定义的变量对入参的空值处理： create or replace procedure say_hello( i_name in varchar2 ) as v_name varchar2(100); begin if i_name is null then v_name :=‘0‘; else v_name :=i_name;--将入赋值给定义变量 end if; dbms_output.put_line(‘hello ‘||v_name); end; 多个参数的存储过程 create or replace procedure say_hello( v_first_name in varchar2, v_last_name in varchar2) as begin dbms_output.put_line(‘hello ‘||v_first_name||‘.‘||v_last_name); end; out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值 create or replace procedure say_hello( v_name in varchar2, v_content out varchar2 ) begin v_content:=‘hello‘||v_name; end; 调用： declare v_con varchar2(200); v_in varchar2(20):=‘wang‘; begin say_hello(v_in,v_con); dbms_output.put_line(v_con); end; in out参数，既赋值又取值 create or replace procedure say_hello(v_name in out varchar2) as begin v_name:=‘hi ‘||v_name; end; 调用： declare v_inout varchar2(20):=‘wangsu‘; begin say_hello(v_inout); dbms_output.put_line(v_inout); end; 对存储过程入参赋缺省值 create or replace procedure say_hello( v_name varchar2 default ‘susu‘, v_content varchar2 default ‘hello‘ ) as begin dbms_output.put_line(v_name||‘ ‘||v_content); end; 调用：(用指明形参名的方式调用更好) begin say_hello(); end; 或 begin say_hello(‘cheng‘); end; 或 begin say_hello(v_name=>‘cheng‘); end; ***************************************** PLSQL中的function ***************************************** FUNCTION和PROCEDURE的区别 1、函数有返回值，过程没有 2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句 过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程 有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的 主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达 式的方式调用 create or replace function func(v_name in varchar2) return varchar2 is begin return(v_name||‘ hello‘); end; --调用： declare v_name varchar2(20); begin v_name:=func(‘cheng‘); dbms_output.put_line(v_name); end; 带out参数的函数 create or replace function func( v_name in varchar2, v_content out varchar2 ) return varchar2 is begin v_content:=v_name||‘ hello‘; return v_content; end; 调用： declare v_name varchar2(20); v_name1 varchar2(20); begin v_name1:=func(‘susu‘,v_name);--返回v_name值 dbms_output.put_line(v_name1);--打印func结果 dbms_output.put_line(v_name);--打印v_name结果 end; 带in out 参数的函数 create or replace function func( v_name in out varchar2) return varchar2 is begin v_name:=v_name||‘ hello‘; return ‘cheng‘; end; 调用： declare v_inout varchar2(20):=‘world‘; v_ret varchar2(20); begin v_ret:=func(v_inout);--返回调用v_inout值(作为出参) dbms_output.put_line(v_ret);--打印func结果 dbms_output.put_line(v_inout);--返回v_name结果 end;Oracle游标/异常/过程/函数
标签：cheng 日期 substr 处理 才有 通过 back 空值 bsp