컴퓨터 Pro*C에서 Dyanmic Query 사용하기 Sample
2007.12.06 14:16
http://database.sarang.net/?inc=read&aid=9022&criteria=oracle&subcrit=tutorials&id=9199&limit=20&keyword=&page=12 6.3 EXECUTE IMMEDIATE 의 사용
/* 예제 #10 */
#include
/***************************************************************
This is is a sample Pro*C program which will prompt for a
WHERE clause to be used in an update statement. This is to be
used with EXECUTE IMMEDIATE.
***************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[20];
VARCHAR pwd[20];
char select[132];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void main(void)
{
char where[80];
int scode;
/* log into ORACLE */
strcpy(uid.arr, "SCOTT"); /* copy the user name */
uid.len = strlen(uid.arr);
strcpy(pwd.arr, "TIGER"); /* copy the password */
pwd.len = strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("Connected to ORACLE user : %sn", uid.arr);
strcpy(select, "UPDATE EMP SET COMM = 100 WHERE");
printf("Please enter where clause for the following : n");
printf("%s", select);
scode = scanf("%s", where);
if(scode == EOF !! scode == 0)
{
printf("Invalid entry. n");
exit(1);
}
strcat(select, where);
EXEC SQL EXECUTE IMMEDIATE :select;
printf("%d records updated. n", sqlca.sqlerrd[2]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
6.4 PREPARE 및 EXECUTE 의 사용
/* 예제 #11 */
#include
/***************************************************************
This is is a sample Pro*C program which will prompt for a
WHERE clause to be used in an update statement. This is to be
used with PREPARE and EXECUTE.
***************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[20];
VARCHAR pwd[20];
float comm;
char select[132];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void main(void)
{
char where[80];
int scode;
/* log into ORACLE */
strcpy(uid.arr, "SCOTT"); /* copy the user name */
uid.len = strlen(uid.arr);
strcpy(pwd.arr, "TIGER"); /* copy the password */
pwd.len = strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR GOTO errrpt;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("Connected to ORACLE user : %sn", uid.arr);
strcpy(select, "UPDATE EMP SET COMM = :comm WHERE");
printf("Please enter where clause for the follwing : n");
printf("%s", select);
scode = scanf("%s", where);
if(scode == EOF !! scode == 0)
{
printf("Invalid entry. n");
exit(1);
}
strcat(select, where);
EXEC SQL PREPARE S1 FROM :select;
printf("Please enter commission : ");
scanf("%f", &comm);
EXEC SQL EXECUTE S1 USING :comm;
printf("%d records updated. n", sqlca.sqlerrd[2]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
errrpt:
printf("n %70s n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
6.5 PREPARE, DECLARE, OPEN, FETCH, CLOSE 의 사용
/* 예제 #12 */
#include
/***************************************************************
This is is a sample Pro*C program which will prompt for a
WHERE clause to be used in a select statement.
This sample uses PREPARE, DECLARE, OPEN, FETCH, CLOSE
since this may be a multi-row select and world require a cursor.
***************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[20];
VARCHAR pwd[20];
int deptno;
float sal;
char select[132];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void main(void)
{
char where[80];
int i, scode;
/* log into ORACLE */
strcpy(uid.arr, "SCOTT"); /* copy the user name */
uid.len = strlen(uid.arr);
strcpy(pwd.arr, "TIGER"); /* copy the password */
pwd.len = strlen(pwd.arr);
EXEC SQL WHENEVER SQLERROR GOTO errrpt;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf("Connected to ORACLE user : %sn", uid.arr);
strcpy(select, "SELECT ENAME, SAL FROM EMP ");
printf("Please enter where clause for the following : n");
printf("%s", select);
scode = scanf("%s", where);
if(scode == EOF !! scode == 0)
{
printf("Invalid entry. n");
exit(1);
}
strcat(select, where);
EXEC SQL PREPARE S1 FROM :select;
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
printf("Employee tSalary n");
printf("--------------t-----------n");
EXEC SQL WHENEVER NOT FOUND GOTO endloop;
for(i = 0; ; i++)
{
EXEC SQL FETCH C1 INTO :ename, :sal;
printf("%10st%6.2fn", ename, sal);
}
endloop:
printf("nn%d records selected.n", i);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
errrpt:
printf("n %70s n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
7.1 STORED PROCEDURE 의 사용
/* calldemo.sql */
rem
Rem calldemo.sql -
Rem DESCRIPTION
Rem
Rem RETURNS
Rem
CREATE OR REPLACE PACKAGE calldemo AS
TYPE name_array IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;
TYPE job_array IS TABLE OF emp.job%type
INDEX BY BINARY_INTEGER;
TYPE sal_array IS TABLE OF emp.sal%type
INDEX BY BINARY_INTEGER;
PROCEDURE get_employees(
dept_number IN number, -- department to query
batch_size IN INTEGER, -- rows at a time
found IN OUT INTEGER, -- rows actually returned
done_fetch OUT INTEGER, -- all done flag
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array);
END calldemo;
/
CREATE OR REPLACE PACKAGE BODY calldemo AS
CURSOR get_emp (dept_number IN number) IS
SELECT ename, job, sal FROM emp
WHERE deptno = dept_number;
PROCEDURE get_employees(
dept_number IN number,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT name_array,
job OUT job_array,
sal OUT sal_array) IS
BEGIN
IF NOT get_emp%ISOPEN THEN -- open the cursor if
OPEN get_emp(dept_number); -- not already open
END IF;
done_fetch := 0; -- set the done flag FALSE
found := 0;
FOR i IN 1..batch_size LOOP
FETCH get_emp INTO emp_name(i), job(i), sal(i);
IF get_emp%NOTFOUND THEN -- if no row was found
CLOSE get_emp;
done_fetch := 1; -- indicate all done
EXIT;
ELSE
found := found + 1; -- count row
END IF;
END LOOP;
END;
END;
/
/* 예제 #13 */
#include
#include
/***************************************************************
This program connects to ORACLE using the SCOTT/TIGER account.
The program declares several host arrays, then calls a PL/SQL
stored procedure (GET_EMPLOYEES in the CALLDEMO package)
that fills the table OUT parameters. The PL/SQL procedure returns
up to ASIZE values.
****************************************************************/
EXEC SQL INCLUDE sqlca.h;
typedef char asciz[20];
typedef char vc2_arr[11];
EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;
/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;
asciz username;
asciz password;
int dept_no; /* which department to query? */
vc2_arr emp_name[10]; /* array of returned names */
vc2_arr job[10];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
EXEC SQL END DECLARE SECTION;
long SQLCODE;
void print_rows(); /* produces program output */
void sql_error(); /* handles unrecoverable errors */
main()
{
int i;
char temp_buf[32];
/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
strcpy(username, "scott");
strcpy(password, "tiger");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("nConnected to ORACLE as user: %snn", username);
printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);
/* Print column headers. */
printf("nn");
printf("%-10.10s%-10.10s%sn", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%sn", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop.
* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back. */
for ( ; ; )
{
EXEC SQL EXECUTE
BEGIN calldemo.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;
print_rows(num_ret);
if(done_flag) break;
}
/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void print_rows(int n)
{
int i;
if (n == 0)
{
printf("No rows retrieved.n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2fn", emp_name[i ], job[i ], salary[i ]);
}
/* Handle errors. Exit on any error. */
void sql_error()
{
char msg[512];
int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);
printf("nORACLE error detected:");
printf("n%.*s n", msg_len, msg);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
