|
저는 아직 proc를 한번도 써본적이 없지만 어디서 sample program을 본 기억이 나서 올립니다.
도움이 되셨으면 합니다.
그리고 저는 proc로 짠 프로그램을 어떻게 compile을 할 줄을 모르겠거든요
어떤 다른 client program을 깔아야 하는지 등 .......
혹시 아시면 저도 갈켜주세요..
embedded PL/SQL
simple example
char username[100], password[20];
char job_title[20], hire_date[9], temp[32];
int emp_number;
float salary;
#include <sqlca.h>
printf("Username? \n");
gets(username);
printf("Password? \n");
gets(password);
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oracle\n");
for (;;)
{
printf("Employee Number (0 to end)? ");
gets(temp);
emp_number = atoi(temp);
if (emp_number == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting program\n");
break;
}
/*-------------- begin PL/SQL block -----------------*/
EXEC SQL EXECUTE
BEGIN
SELECT job, hiredate, sal
INTO :job_title, :hire_date, :salary
FROM emp
WHERE empno = :emp_number;
END;
END-EXEC;
/*-------------- end PL/SQL block -----------------*/
printf("Number Job Title Hire Date Salary\n");
printf("------------------------------------\n");
printf("%6d %8.8s %9.9s %6.2f\n",
emp_number, job_title, hire_date, salary);
}
...
exit(0);
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
more complex example
#include <stdio.h>
#include <sqlca.h>
char username[20];
char password[20];
char status[80];
char temp[32];
int acct_num;
double trans_amt;
void sql_error();
main()
{
char trans_type;
strcpy(password, "TIGER");
strcpy(username, "SCOTT");
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oracle\n");
for (;;)
{
printf("Account Number (0 to end)? ");
gets(temp);
acct_num = atoi(temp);
if(acct_num == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting program\n");
break;
}
printf("Transaction Type - D)ebit or C)redit? ");
gets(temp);
trans_type = temp[0];
printf("Transaction Amount? ");
gets(temp);
trans_amt = atof(temp);
/*----------------- begin PL/SQL block -------------------*/
EXEC SQL EXECUTE
DECLARE
old_bal NUMBER(9,2);
err_msg CHAR(70);
nonexistent EXCEPTION;
BEGIN
:trans_type := UPPER(:trans_type);
IF :trans_type = ’C’ THEN -- credit the account
UPDATE accts SET bal = bal + :trans_amt
WHERE acctid = :acct_num;
IF SQL%ROWCOUNT = 0 THEN -- no rows affected
RAISE nonexistent;
ELSE
:status := ’Credit applied’;
END IF;
ELSIF :trans_type = ’D’ THEN -- debit the account
SELECT bal INTO old_bal FROM accts
WHERE acctid = :acct_num;
IF old_bal >= :trans_amt THEN -- enough funds
UPDATE accts SET bal = bal - :trans_amt
WHERE acctid = :acct_num;
:status := ’Debit applied’;
ELSE
:status := ’Insufficient funds’;
END IF;
ELSE
:status := ’Invalid type: ’ || :trans_type;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND OR nonexistent THEN
:status := ’Nonexistent account’;
WHEN OTHERS THEN
err_msg := SUBSTR(SQLERRM, 1, 70);
:status := ’Error: ’ || err_msg;
END;
END-EXEC;
/*----------------- end PL/SQL block ----------------------- */
printf("\nStatus: %s\n", status);
}
exit(0);
}
void
sql_error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing error\n");
exit(1);
}
|
