Oracle實驗總結

(一) 網路設定檔
# TNSNAMES.ORA Network Configuration File: E:/Oracle/NETWORK/ADMIN/tnsnames.ora
# Generated by Oracle configuration tools.

  ELEMENT1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.181)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Element.com)
    )
  )
#其中HOST = 192.168.0.181 (此處為目標主機的IP地址或電腦名稱)
#SERVICE_NAME為目標主機的服務名,引處為Element.com.

(二) Oracle的查詢
      select level empno,ename,mgr from emp connect by
      prior empno=mgr start with empno=7839
     
     select ename,sal,
         decode(sign(sal-3000),1,'A',
         decode(sign(sal-2000),1,'B','C'))
         grade from emp

(三) 編程實現從Oracle資料庫中讀出記錄到檔案
/*****Exam.pc*********/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

#define SQLNOTFOUND  1403
EXEC SQL INCLUDE SQLCA;

FILE * fp_log;

void connect()
{
 EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR username[10], password[10], server[10];
 EXEC SQL END DECLARE SECTION;

 /* 輸入使用者名稱、口令以及伺服器名 */
 printf("/n輸入使用者名稱：");
 gets(username.arr);
 username.len=(unsigned short)strlen((char *)username.arr);
 printf("/n輸入口令：");
 gets(password.arr);
 password.len=(unsigned short)strlen((char *)password.arr);
 printf("/n輸入伺服器名：");
 gets(server.arr);
 server.len=(unsigned short)strlen((char *)server.arr);
 /* 串連到Oracle伺服器上 */
 EXEC SQL CONNECT :username IDENTIFIED BY :password USING :server;
 printf("/n以使用者%s成功地串連到了伺服器%s上！/n", username.arr, server.arr);
}

void disconnect()
{
 char temp;
 printf("/n是否在中斷連線前提交所有事務？ (Y/N)");
 scanf("%c", &temp);
 fflush(stdin);
 if(temp !='Y' && temp != 'y')
 {
  /* 回退事務，中斷連線。 */
  EXEC SQL ROLLBACK WORK RELEASE;
  printf("/n回退事務，中斷連線，退出程式！/n/n");
 }
 else
 {
  /* 提交事務，中斷連線。 */
  EXEC SQL COMMIT WORK RELEASE;
  printf("/n提交事務，中斷連線，退出程式！/n/n");
  exit(1);
 }
}

void delspace(char *sstr)
{
 while(strlen(sstr)>1 && sstr[strlen(sstr)-1]==' ' )
 {
  sstr[strlen(sstr)-1]='/0';
 }
}

void main(int argc,char**argv)
{
EXEC SQL BEGIN DECLARE SECTION;
 VARCHAR sql_text[256];
 char ename[12];
 char job[10];
 char sal[10];
EXEC SQL END DECLARE SECTION;

 FILE * fp;

    char fdataname[64];   /*資料檔案名*/
   
  connect();/*串連到資料庫*/

   memset( fdataname, '/0', 64 );
   strcpy( fdataname, "D://oracle//proc//emp.txt");
   if ((fp = fopen(fdataname,"a+")) == NULL)
    {
        printf("Error opening %s/n",fdataname);
        return;
    }

strcpy(sql_text.arr,"select ename, job, sal from emp ");
sql_text.len=(unsigned short)strlen((char *)sql_text.arr);

EXEC SQL PREPARE pre_sql1 FROM :sql_text;

EXEC SQL DECLARE c1 CURSOR FOR pre_sql1;

   EXEC SQL open c1;
   while(1)
   {
 printf("DEBUG_1:sqlcode=%d",sqlca.sqlcode);
 EXEC SQL fetch c1 into :ename, :job, :sal;
 printf("DEBUG_2:sqlcode=%d",sqlca.sqlcode);
 if( (sqlca.sqlcode == SQLNOTFOUND) || (sqlca.sqlcode <0) ) break;
                printf("DEBUG01:ename=%s",ename);
         delspace(ename);
  delspace(job);
  delspace(sal);
        fprintf(fp,"%s,%s,%s/n",
   ename, job, sal);
   }
   EXEC SQL close c1;
fclose(fp);
/*---------------------------------------------*/
  disconnect();
}

proc sqlcheck=full userid=scott/tiger@ora9205 iname=Exam.pc

產生程式:Exam.c

設定環境變數如下：（安裝VC++）
set LIB=D:/Program Files/Microsoft Visual Studio/VC98/Lib
set INCLUDE=D:/Program Files/Microsoft Visual Studio/VC98/Include;D:/oracle/ora92/precomp/public

CL Exam.c /link D:/oracle/ora92/precomp/lib/msvc/oraSQL9.LI

產生：Exam.exe

運行Exam即可把資料匯入到emp.txt中.