通过plsql 测试存储过程遇到的问题和学习到的一些基础知识整理

pl/sql developer 中文字段显示乱码

在windows中创 建一个名为“NLS_LANG”的系统环境变量，设置其值为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”，然后重新启动 pl/sql developer，这样检索出来的中文内容就不会是乱码了。如果想转换为UTF8字符集，可以赋予“NLS_LANG”为 “AMERICAN_AMERICA.UTF8”，然后重新启动 pl/sql developer。其它字符集设置同上。

 

ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME") 当报这个错误时 ：

解决方法为 把文档中的双引号改为单引号。

 

 

Oracle 的函数写法：

CREATE OR REPLACE FUNCTION ISEQUAL
(
    VAR1 IN VARCHAR2,
    VAR2 IN VARCHAR2
)
RETURN NUMBER  -- 0:no equal 1:equal  -1: error
IS
   BEGIN
      IF(VAR1 IS NULL AND VAR2 IS NULL OR VAR1 IS NOT NULL AND VAR2 IS NOT NULL AND VAR1 = VAR2) THEN
         RETURN 1;
      ELSE
         RETURN 0;
      END IF;
EXCEPTION
    WHEN OTHERS THEN
    RETURN -1;
END ISEQUAL;

 

 

 

存储过程创建语法：

       create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

    Select count(*) into 变量1 from 表A where列名=param1；

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then

       Rollback;

End;

 

 

注意事项：

1，  存储过程参数不带取值范围，in表示传入，out表示输出

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

 

 

 

 

命名的系统异常                          产生原因

ACCESS_INTO_NULL                   未定义对象

CASE_NOT_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION_IS_NULL                集合元素未初始化

CURSER_ALREADY_OPEN          游标已经打开

DUP_VAL_ON_INDEX                   唯一索引对应的列上有重复的值

INVALID_CURSOR                 在不合法的游标上进行操作

INVALID_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND                        使用 select into 未返回行，或应用索引表未初始化的 

 

TOO_MANY_ROWS                      执行 select into 时，结果集超过一行

ZERO_DIVIDE                              除数为 0

SUBSCRIPT_BEYOND_COUNT     元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR                             赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED                           PL/SQL 应用程序连接到 oracle 数据库时，提供了不

正确的用户名或密码

NOT_LOGGED_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL                             使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR                        运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID                         无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE         Oracle 在等待资源时超时 

 

 

触发器

1.触发器的创建规则：
  ①作用范围清晰；
  ②不要让触发器去完成Oracle后台已经能够完成的功能；
  ③限制触发器代码的行数；
  ④不要创建递归的触发器；
  ⑤触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。

2.可以创建被如下语句所触发的触发器：
  ①DML语句（DELETE，INSERT，UPDATE）；
  ②DDL语句（CREATE，ALTER， DROP）；
  ③数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。

3.注意事项
  ①触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发；
  ②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次；
  ③SELECT 并不更改任何行，因此不能创建 SELECT 触发器.这种场合下规则和视图更适合；
  ④触发器和某一指定的表格有关，当该表格备删除时，任何与该表有关的触发器同样会被删除；
  ⑤在一个表上的每一个动作只能有一个触发器与之关联；
  ⑥在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器；

4.删除触发器的语句格式为：

DROP TRIGGER name ON table;

  一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句，可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式，当触发器激发时该布尔表达式是必须为真。触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计算为真时该过程被执行。

5.实例
  编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。

CREATE OR REPLACE TRIGGER del_emp_deptno
  BEFORE DELETE ON dept
  FOR EACH ROW
BEGIN
  DELETE FROM emp WHERE deptno = :OLD.deptno;
END;

6：select into 用法：我在用的时候遇到个错误

SELECT count(*) into v_able_count,sum(useable_scores) into v_total_scores  这样是不对的

应该改成

SELECT count(*) ,sum(useable_scores) into v_able_count ,v_total_scores

7： Oracle 定义数组如下：

Type scoresIdArray is
    table Of user_scores.id%type
    Index by Binary_Integer;

  这里的user_scores.id%type 表示 user_scores表中的 id字段

%ROWTYPE 这种类型 表示某表的行数据类型

补充：：

存储过程中的转义字符 可以是‘  单引号  如'''||v_key_time||'''   v_key_time 是一个定义的变量  类型为字符串 

insert into  select   语法：  +   case when 语法

很强大的一个sql；

insert into ec_carts (ID,USER_ID, GOODS_ID, GOODS_NO, GOODS_NAME,
      MARTET_PRICE, GOODS_PRICE, CONSUME_SCORES,GOODS_NUMBER, IS_REAL,
      IS_GIFT, CREATED_AT,GET_SCORES,LIMIT_NUM,BUY_TYPE ,M_GOODS_ID,KEY_TIME)
    select EC_CARTS_SEQ.NEXTVAL,2011461,id,goods_no,
    case when 0= 1 then GOODS_GBK
        when 0 = 2 then GOODS_EN
        else  GOODS_CN end goods_name,
        MARKET_PRICE ,SHOP_PRICE ,0,SALE_NUMBER,
      case when GOODS_KIND =3 then 0
         else 1 end GOODS_KIND ,0,SYSDATE,
      case when (  BUY_FIXED_SCORES is not null) then BUY_FIXED_SCORES
         when (SCORE_BUY_PARAM is not null) then SHOP_PRICE*SCORE_BUY_PARAM
         else 0 end user_get_score,LIMIT_NUMBER ,1,8440001,SYSDATE
         from ec_goods where id in (8010001,7010007)and SYSDATE >=SALE_START AND SYSDATE <=SALE_END

存储过程中 的 if elsif  else  end if  用法

如：

if v_buy_fixed_score is not null then
        v_user_get_score := v_buy_fixed_score;
    elsif v_buy_score_param is not null then
        v_user_get_score := v_shop_price*v_buy_score_param;
    else
        v_user_get_score := 0;
    end if;

 其中  elsif  没写错  注意了。。。。。。

写存储过程的时候 一般都喜欢定义一个now 的sysdate  在整个存储过程中使用它，然而在拼装sql字符串的时候 千万别用