在使用oracle中遇到的一些问题

水深无声 2022-09-24 15:13 325阅读 0赞

今天在写oracle的存储过程，遇到一个非常奇怪的现象，  
定义了一个游标，用for循环获取变量。再用这个变量做条件去执行查询语句，结果查询结果总是跟实际数据不符。  
具体的写法如下：  
create or replace procedure test2 as   
yesterday VARCHAR(16);  
cn varchar(16);  
sn varchar(16);  
cp VARCHAR(32);  
cursor c\_job is SELECT DISTINCT CPID FROM T\_MT\_20151002;//定义游标  
begin  
  select to\_char(sysdate-1,'yyyyMMdd') into yesterday from dual;  
  dbms\_output.put\_line(yesterday);  
  for c\_row in c\_job loop  
    cp := c\_row.CPID;//通过游标获取变量  
    SELECT CHANNELNAME, num  into cn, sn FROM (SELECT CHANNELNAME, SUM(SNUM) as num FROM T\_MT\_20151002 WHERE CPID=cp GROUP BY CHANNELNAME) WHERE ROWNUM=1;//用变量做条件执行查询语句  
    dbms\_output.put\_line(cp||'   '||cn||'   '||sn);  
    INSERT INTO "QXTDB"."T\_OWN\_CUSTDB\_LT1065\_TMP2" ( PHONE, CARID, MOTOR ) VALUES ( ''||cp||'', ''||cn||'', ''||sn||'' );  
  end loop;  
end;  
问题一直出现在这句：  
SELECT CHANNELNAME, num  into cn, sn FROM (SELECT CHANNELNAME, SUM(SNUM) as num FROM T\_MT\_20151002 WHERE CPID=cp GROUP BY CHANNELNAME) WHERE ROWNUM=1;//用变量做条件执行查询语句  
经过反复的检查、测试发现原来如果直接把cp这个变量作为条件写在这里是有问题的，因为CPID这个字段本事是varchar类型的，但是如果直接把cp写在这里，oracle会把cp认为成'cp'，这样查询条件就有问题了。  
所以这里一定要用连接符把条件变量cp和整个语句链接起来。并且要在变量外面加单引号表明变量类型。于是把语句改成了了下面的内容：  
SELECT CHANNELNAME, num  into cn, sn FROM (SELECT CHANNELNAME, SUM(SNUM) as num FROM T\_MT\_20151002 WHERE CPID='||cp||' GROUP BY CHANNELNAME) WHERE ROWNUM=1;  
这样测试了一下，发现还是有问题，在执行的时候，oracle把'||cp||'当作一个完整的字符串处理了。又查资料，发现这里需要对单引号进行转义处理，必须连续两个单引号才能实现目的，修改语句如下：  
SELECT CHANNELNAME, num  into cn, sn FROM (SELECT CHANNELNAME, SUM(SNUM) as num FROM T\_MT\_20151002 WHERE CPID=''||cp||'' GROUP BY CHANNELNAME) WHERE ROWNUM=1;  
再测试，成功。  
以前一直用sqlserver和mysql，最近开始接触oracle，初学乍练遇到很多低级问题。任重道远呀。  
  
  
这里顺便记录一下前几天积累的三个经验：  
  
  
一、创建【作业】时如果遇到权限不够ORA-27486，需要给【用户】赋予下权限  
sqlplus / as sysdba  
grant create job to 【用户】;  
  
  
二、在存储过程中执行比较复杂的sql语句时（比如create等）需要先把语句定义成为变量，在执行变量才可以。  
  
  
三、如果执行的存储过程中有CREATE TABLE（创建表）的需求时，需要给创建语句增加授权authid current\_user。