PLSQL

PLSQL块

/*

 * declare块可以不写

  

   exception块可以写。

  

   如果没有程序块，使用NULL来处理。

 */

declare--定义块

 

begin   --运行块

  null;

exception--异常块

  whenothersthen

    null;

end;

变量，常量的定义，变量的静态与动态赋值

declare

  /*变量的声明：声明格式

 

   identifier[CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

  */

  v_number number(5, 2) := 100.34;    --总共5位，小数占两位

  v_str    varchar2(20);

  v_sex    char(2);

  v_flag   boolean;

  v_date   date;

 

  /*type和rowtype类型*/

  v_username t_userinfo.username%type; --v_username的类型与t_userinfo.username的类型一致

 

  v_userRow t_userinfo%RowType;        --v_userRow 的类型与t_userinfo的行的类型一致

 

  /*Record类型*/

 

  type Student isRecord(

    stu_id   number,

    stu_name varchar2(20),

    stu_age  number(2)

    );

 

  v_stu_1 Student;

 

 /*常量的定义*/

 

  PI constantnumber := 3.1415926;

 

  /*动态赋值定义的变量*/

  v_userCount number;

 

  v_test1 t_userinfo.userid%type;

  v_test2 t_userinfo.username%type;

 

begin

  /*为变量赋值

    

     A：declare块定义时，直接赋值。

    

     B：begin块中静态赋值。

    

     C：begin块中动态赋值

  */

  /*静态赋值即直接赋值*/

  v_str  := '字符串变量';

  v_sex  := '男';

  v_flag := false;

  v_date := sysdate;

 

  v_username := 'admin';

 

  v_userRow.userid   := 11;

  v_userRow.username := 'test';

  v_userRow.password := '123';

  v_userRow.truename := '真实姓名';

 

  v_stu_1.stu_id   := 100;

  v_stu_1.stu_name := '学生名称';

  v_stu_1.stu_age  := 20;

 

  /*输出变量值*/

  dbms_output.put_line(v_str);

  dbms_output.put_line(v_sex);

  --布尔值是不能输出

  --dbms_output.put_line(v_flag);

  dbms_output.put_line(v_date);

  dbms_output.put_line(v_username);

 

 dbms_output.put_line(v_userRow.username);

 dbms_output.put_line(v_stu_1.stu_name);

 

  /*动态赋值：将Select的结果赋到变量中

                  

     1：Select的结果只能是一行多列。不可以返回多行。

    

     2：Select的结果必须要有记录。

  */

 

  selectcount(1) into v_userCount from T_userinfo;

  dbms_output.put_line(v_userCount);

 

  Select userid, username

    into v_test1, v_test2

    From T_userinfo

   where userid = 50;

  

   dbms_output.put_line(v_test1 || '    ' || v_test2);

end;

转义字符与字符串的拼接

declare

 

  v_sql      varchar2(100);

  v_username t_userinfo.username%type;

begin

  v_username := 'a';

  --单引号转义：2个单引号，代表1个单引号。

  v_sql := 'Select * From T_Userinfo where 1 = 1and username like ''%'||v_username ||'%''';

 

  dbms_output.put_line(v_sql);

 

  --oracle中引用变量 '||变量名||' (相当于java中的"+变量名+")

  v_sql := 'Select * From T_Userinfo where 1 = 1and username like ''%'||v_username||'%''';

          

    dbms_output.put_line(v_sql);

end;

表达式

declare

 

  v_sql      varchar2(100);

  v_username t_userinfo.username%type;

  v_truename t_userinfo.truename%Type;

 

begin

  --v_username := 'a';

  --v_truename := '张';

 

  v_sql := 'Select * From T_userinfo where 1 =1';

  if v_username isnotnulland v_username != ' 'then

    v_sql := v_sql || ' and username like''%'||v_username||'%''';

  endif;

 

  if v_truename isnotnulland v_truename != ' 'then

    v_sql := v_sql || ' and truename like''%'||v_truename||'%''';

  endif; 

 

  dbms_output.put_line(v_sql);

end;

条件判断

declare

  v_age number;

begin

  v_age := 43;

  --使用if/else

  if v_age < 10then

    dbms_output.put_line('小于10');

 

  elsif v_age > 10and v_age <= 20then

    dbms_output.put_line('大于10并且小于20');

 

  elsif v_age > 20and v_age <= 40then

    dbms_output.put_line('大于20并且小于40');

  else

    dbms_output.put_line('大于40');

  endif;

 

  --使用case语句。

  v_age := 13;

  case

    when v_age < 10then

      dbms_output.put_line('小于10');

   

    when v_age > 10and v_age <= 20then

      dbms_output.put_line('大于10并且小于20');

   

    when v_age > 20and v_age <= 40then

      dbms_output.put_line('大于20并且小于40');

    else

      dbms_output.put_line('大于40');

  endcase;

end;

循环

declare

  v_i number;

begin

  v_i := 1;

  --loop循环

  loop

    dbms_output.put_line(v_i);

    v_i := v_i + 1;

    --退出循环

    --if v_i > 100 then

    -- exit;

    --end if;

 

    exitwhen v_i > 100;

 

  endloop;

 

  --while循环

  v_i := 1;

 

  while v_i <= 100loop

    dbms_output.put_line('while ' || v_i);

    v_i := v_i + 2;

  endloop;

 

  --for循环

  --默认步长加1.而且不能控制步长。

  --变量不需要定义。

  --for循环中，可以直接为一个结果集。

  v_i := 1;

  for v_i in1 .. 100loop

    dbms_output.put_line('for ' || v_i);

  endloop;

 

  for v_j in1 .. 10loop

    dbms_output.put_line('for ' || v_j);

  endloop;

 

  for v_userRow in (Select * From T_userinfo orderby userid) loop

 

   dbms_output.put_line(v_userRow.userid || '  ' ||v_userRow.username);

  endloop;

end;

 

break与continue

/*

 * 使用GoTo/return/null来模拟break与continue

 */

declare

begin

  for v_j in1 .. 10loop

    if v_j =5then

      --break;      

      --return;           return直接结束整个程序，而不是跳出循环

      GOTO next_code;   --goto可以用来模拟break跳出循环

    endif;

    dbms_output.put_line('break' || v_j);

  endloop;

 

 

  <<next_code>>

  for v_j in1 .. 10loop

    if v_j =5then

       NULL;            --NULL不执行任何语句可以模拟continue跳到下一个循环

    else

       dbms_output.put_line('continue ' || v_j); 

    endif;

  endloop; 

end;

动态sql语句1

declare

  v_count number;

  v_sql varchar2(100);

  v_deptid t_dept.deptid%type;

  v_deptname t_dept.deptname%type;

begin

  /*编译SQL,只能执行DML和Select语句,不能执行DDL与DCL*/

  insertinto T_userinfo

    (userid, username, password)

  values

    (200, 'aa', 'bb');

  rollback;

  Selectcount(1) into v_count From T_userinfo;

 

  --drop table t_a;

 

  /*动态SQL,可以执行DDL语名

    SQL语句必须是标准的SQL语句，不能带有into等符号。

   */

  v_sql := 'drop table t_a';

 

  --execute immediate v_sql;   --执行动态sql语句

 

  v_sql := 'Select count(1) From T_Dept';

  executeimmediate v_sql into v_count;

 

  dbms_output.put_line('v_count = ' || v_count);

 

 

  v_sql := 'Select deptid,deptname From T_deptwhere deptid = 1';

  executeimmediate v_sql into v_deptid,v_deptname;

 

  dbms_output.put_line('v_deptid = ' || v_deptid);

  dbms_output.put_line('v_deptname = ' || v_deptname);

 

end;

动态sql语句2

/*动态建表，动态插数据，动态计算，删除*/

declare

  v_createSQL varchar2(1000);

  v_isExist   number;

  v_insertSQL varchar2(1000);

begin

  selectcount(1)

    into v_isExist

    From User_tables

   where table_name = 'T_TEMP';

  if v_isExist > 0then

    dbms_output.put_line('表存在，执行删除的操作');

    executeimmediate'drop table T_Temp';

  endif;

  v_createSQL := 'create tableT_temp(';

  v_createSQL := v_createSQL || ' temp_id numberprimary key,';

  v_createSQL := v_createSQL || ' temp_namevarchar2(100),';

  v_createSQL := v_createSQL || ' temp_count number';

  v_createSQL := v_createSQL || ' )';

 

  executeimmediate v_createSQL;

 

  --插入数据

  for v_i in1 .. 100loop

 

    v_insertSQL := 'insert into T_Temp(Temp_Id, Temp_Name, Temp_Count)';

    v_insertSQL := v_insertSQL || 'values (';

    v_insertSQL := v_insertSQL || ' ' || v_i || ',''名称_' || v_i || ''', ' ||

                   v_i * 10 || ') ';

                  

    executeimmediate v_insertSQL;

  endloop;

 commit;

 --逻辑业务(游标)

 

 --删除

 

end;