关于ORACLE使用存储过程开发

 

一、sqlplus 调用有返回值的存储过程：
set serveroutput on ; --打开显示设置，output才能在界面上显示出来
var[iable] 变量名 变量类型;
exec 存储过程(:变量名);
print 变量名;

例子：
带返回值的存储过程：
        CREATE OR REPLACE PROCEDURE wwb_temp
        (wwb_code out integer) IS

        begin
          wwb_code := 1;
          DBMS_OUTPUT.PUT_LINE('结果');
  
        exception
          when too_many_rows then
               DBMS_OUTPUT.PUT_LINE('返回值多于1行');
          when others then
               DBMS_OUTPUT.PUT_LINE('在inst_temp过程中出错！');
        end;
   在sqlplus中执行：
     SQL> set serveroutput on ;

     SQL> var wwbout number;

     SQL> exec wwb_temp( :wwbout );
    
     结果

     PL/SQL 过程已成功完成。

     SQL> print wwbout;
   
    
                    OUTP
     -------------------
                    1.00

二、在存储过程中写日志到操作系统的日志文件中：

    平时写存储过程基本上调用的时候很难监控到它运行得如何，这样对于程序来说确实是比较悲惨的。以下整理了下存储过程中写日志的笔记。     (1) 给用户赋予创建DIRECTORY的权限
          grant create DIRECTORY to username;
     (2) 创建一个DIRECTORY
          create or replace directory directoryname
             as 实际路径;
     (3) 如果确定该路径的权限大于666，那么就直接调用存储过程就可以了，存储过程在写的时候会和C的文件操作是一样的，不存在则创建。
         否则就必须在该路径下创建一个文件，该文件的权限必须是-rw-rw-rw-即666
          touch wwb_log.txt
          chmod 666 wwb_log.txt
     (4) 在存储过程中调用oracle的包（UTL_FILE），与C程序一样使用打开文件，写入文件，关闭文件：
            fp UTL_FILE.FILE_TYPE; --定义一个文件句柄
          fp := UTL_FILE.FOPEN ('directoryname', 'wwb_log', 'w');  --打开文件，将返回的句柄赋给定义的fp
          UTL_FILE.PUT (fp,'test write log');
          UTL_FILE.FCLOSE (fp);
     例子：
          1、赋权限：
          sqlplus / as sysdba
         
          SQL> grant create DIRECTORY to usr1;
          SQL> quit;

          2、创建文件：
          $ touch /home/server/log/wwb_log
          $ chmod 666 /home/server/log/wwb_log
         
          3、建路径以及相应的存储过程：
              sqlplus usr1/usr1@orcl
   
              SQL> create or replace directory testdir
                   as '/home/server/log';   --这个路径必须存在

              SQL> CREATE OR REPLACE PROCEDURE test_file
                   IS
                             fp UTL_FILE.FILE_TYPE;
                   BEGIN
                             fp := UTL_FILE.FOPEN ('TESTDIR', 'wwb_log', 'w'); 
                   --注意oracle里面存在的内容都是大写的，所以不管你创建的时候是大写还是小写，
                   --这里的路径名一律都是大写，不然oracle会报找不到这个路径，文件必须存在，不然oralce会报文件不存在。

                             UTL_FILE.PUT (fp,'test write log');

                             UTL_FILE.FCLOSE (fp);
                   END;

              SQL> exec test_file;
        
              SQL> quit;

         4、可以查看你的文件了
       
     (5) 常见的报错：
         第 1 行出现错误:
         ORA-29280: 目录路径无效
         ORA-06512: 在 "SYS.UTL_FILE", line 41
         ORA-06512: 在 "SYS.UTL_FILE", line 478
         ORA-06512: 在 "USR1.TEST_FILE", line 5
         ORA-06512: 在 line 1
      原因：
         路径不存在或是没有定义一个directory
      解决：
          SQL> create or replace directory testdir
             as '/home/server/log';   --这个路径必须存在
          检查你创建的这个路径是否正确存在。
          UTL_FILE.FOPEN ('TESTDIR', 'wwb_log', 'w');
          检查这里的路径是否用的是大写字母
   

         第 1 行出现错误:
         ORA-29283: 文件操作无效
         ORA-06512: 在 "SYS.UTL_FILE", line 536
         ORA-29283: 文件操作无效
         ORA-06512: 在 "USR1.TEST_FILE", line 5
         ORA-06512: 在 line 1
      原因：
         文件不存在或是文件没有可以操作的权限。
      解决：
         检查文件是否存在，或是要写文件的路径是否为大于666（即drw-rw-rw-）的权限，必须在OTHER用户的权限处为可写。

（仅供参考，如有意见，请指教）