13주-14주proc.PDF

12 : Pro*C/C++ 1 2 Embeded SQL 3 PRO *C 31 C/C++ PRO *C NOT! NOT AND && AND OR OR EQUAL == = SQL,,, Embeded SQL SQL 32 Pro*C C SQL Pro*C C, C Pro*C, C C 321, C char : char[n] : n int, short, long : float : double float : VARCHAR[N] : VARCHAR C PRO*C 1

VARCAHR Pro*C char, char[n], VARCHAR[n] NUMBER NUMBER(x,x) Pro*C int, short, long, float, double PRO* C,,,, EXEC SQL BEGIN SECTION; SQL (:) testproc1pc (:) int emp_number; real emp_salary; EXEC SQL SELECT sal INTO :emp_salary FROM emp WHERE sal = :emp_salary; 322 C C, PRO*C C ALTER, CREATE, INSERT, DELETE 323 (Indicator Variable) Pro*C NULL (:) DECLARE SECTION, 2 byte testproc1pc #include<stdioh> VARCHAR user_id[20]; VARCHAR passwd[20]; int emp_no; float emp_salary; short emp_sal_indicator; /* */ 2

EXEC SQL INCLUDE SQLCA; /* */ main() { printf("\n Enter User ID :"); scanf("%s", iser_idarr); printf("\n Enter Passward :"); scanf("%s", passwdarr); user_idlen = strln(user_isarr); passwdlen = strln(passwdarr); EXEC SQL CONNECT :user_id IDENTIFIED BY :passwd; if (sqlcasqlcode < 0) { printf("\n@s", sqlcasqlerrmsqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); else { printf("\nconnected to Oracle"); EXEC SQL COMMIT WORK RELEASE; getdata(); exit(0); void getdata() { printf("\n Enter Employee Number to Query:"); scanf("%d\n", emp_number); /* : emp sal emp_sal_indicator */ EXEC SQL SELECT sal INTO :emp_salary :emp_sal_indicator FROM emp WHERE sal = :emp_number; if (emp_sal_indicator == -1_ 3

printf( Employee NO %d has the salary of $ 000\n,emp_number); else { printf("\n Emplyoee %d :", emp_number); printf("\n Salary : %f", emp_salary); SELECT emp sal Pro*C emp_salary, emp_sal_indicator, emp sal NULL, emp_sal_indicator 1, -2 (crash), -1 NULL 0 0 (crash) 1 NULL, 0-1: NULL 0 : emp sal NULL emp_sal_indicator = -1; EXEC SQL INSERT INTO emp(emp_no, sal) VALUES(:emp_number, :emp_salary :emp_sal_indicator); 324 C PRO*C C, int emp_number[10]; float emo_salary[10]; char emp_name[10][20];, SELECT 4

, /* : 2 */ float emp_salary[1][10]; (1) SQL PRO*C INSERT, UPDATE SELECT, FETCH SQL int emp_number[10]; float emo_salary[10]; /* */ EXEC SQL SELECT enpno, ename, sal INTO :emp_number, :emp_name, :emp_salary FROM emp WHERE empnp > 2000; SQL, SQL 10, 10, ORA-02112: PCC: SELECT INTRO returns too many rows (2) int emp_number[10]; float emp_salary[10]; 5

/* */ EXEC SQL INSERT INTO rmp(enpno, sal) VALUES(:emp_number, :emp_salary); 10 emp, C FOR loop 10 Pro*C FOR loop SQL, for (int I=0, I < 10; I++) { EXEC SQL INSERT INTO emp(empno, sal) VALUES (:emp_number[i], :emp_salary[i]);, SQL,, SQL (3) UPDATE, UPDATE int emp_number[10]; float emp_salary[10]; /* */ EXEC SQL UPDATE emp SET sal = :emp_salary WHERE empno = :emp_number; (4) DELETE SQL, int emo_number[10]; 6

/* */ EXEC SQL DELETE FROM emp WHERE empno = :emp_number; (5) (Indicator Variable Array),, Int emp_numnber[10]; Float emp_salary[10]; Char emp_name[10][20]; Short emp_sal_indicator[10]; /* */ EXEC SQL INSERT INTO emp(empno, ename, sal) VALUES (:emp_number, :emp_name, :emp_salary :emp_sal_indicator); 33 SQL Pro*C SQL SELECT, INSERT, DELETE, UPDATE SQL Pro*C Pro*C EXEC SQL SQL SQL, Pro*C SQL 34 PRO *C SELECT,, PRO*C 4 1 DECLARE : 2 OPEN : 3 FETCH : 4 CLOSE : 7

341, PRO *C /* */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT empno, ename, job, sal FROM emp WHERE empno > 1000; SELECT FETCH 342 OPEN OPEN OPEN /* OPEN */ EXEC SQL OPEN emp_cursor; OPEN, OPEN OPEN 343 FETCH OPEN FETCH, FETCH SELECT FETCH /* FETCH */ EXEC SQL FETCH emp_cursor INTO :emp_number, emp_name, :emp_salary, :emp_deptno; FETCH, FETCH CLOSE OPEN, OPEN FETCH OPEN CURSOR, FETCH, CLOSE 344 CLOSE CLOSE, /* CLOSE */ 8

EXEC SQL CLOSE emp_cursor; 345 cursorpc, scott/tiger dept /* File Name : cursorpc */ Description : Connect to Oracle DB as scott/tiger, then gets the department table data, and display them #include<stdioh> VARCHAR username[20]; VARCHAR password[20]; int char char dept_no; dept_name[20]; dept_loc[20]; EXEC SQL INCLUDE SQLCA; main() { strcpy(usernamearr, "SCOTT"); strcpy(passwordarr, "TIGER"); usernamelen = strlen(usernamearr); passwordlen = strlne(passwordarr); /* : scott/tiger */ EXEC SQL CONNECT :username IDENTIFIED BY :password; /*,, */ if (sqlcasqlcode < 0) { printf("\n%s",sqlcasqlerrmsqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); 9

printf("\n Connected to Oracle "); /* */ EXEC SQL DECLARE dept_cursor CURSOR FOR SELECT deptno, dname, loc FROM dept; /* OPEN */ EXEC SQL OPEN dept_cursor; /* OPEN, break */ EXEC SQL WHENEVER NOT FOUND DO break; printf("no Name Location\n"); printf("----------- -------------------- --------------------\n"); for(;;) { /* FETCH */ EXEC SQL FETCH dept_cursor INTO :dept_no, :dept_name, :dept_loc; /* */ printf("%d, %s %s\n", dept_no, dept_name, dept_loc); /* CLOSE */ EXEC SQL CLOSE dept_cursor; /* Commit */ EXEC SQL COMMIT WORK RELEASE; exit(0); 35 Pro*C PL/SQL Pro*C PL/SQL C Pro*C SQL PL/SQL SQL C EXEC SQL, Pro*C PL/SQL EXEC SQL EXECUTE, END-EXEC, 10

int emp_number; int emp_depno; /* PL/SQL */ EXEC SQL EXECUTE BEGIN END; SELECT deptno INTO :emp_deptno FROM emp WHERE empno = :emp_number; END-EXEC; /* PL/SQL */ PL/SQL 36 sqlca SQLCA SQL, SQLCA Pro*C SQL Pro*C SQL SQLCA WHENEVER 361 SQLCA SQLCA PRO*C SQLCA sqlcah EXEC SQL INCLUDE SQLCA C #include, #include<sqlcah> EXEC SQL INCLUDE SQLCA; SQLCA SQLCA SQLCA SQLCA SQLCA, WHENEVER 362 SQLCA SQLCA struct sqlca { 11

char sqlcaid[8]; /* "SQLCA" */ long sqlabc; /* SQLCA */ long sqlcode; /* */ ; struct { unsigned short sqlerrml; /* */ char sqlerrmc[70]; /* */ sqlerrm; char sqlerrp[8]; /* */ long sqlerrd[6]; /* */ char sqlwarn[8]; /* */ char sqlext[8]; struct sqlca sqlca; SQLCA SQL C (), If (sqlcasqlcode!= 0) { printf("sql Error %s\n", sqlcasqlerrmsqlerrmc); 363 SQLCA SQLCA SQLCA (1) sqlcode sqlerrm SQLCA, sqlcode 0 : SQL > 0 : SQL < 0 : SQL,, 12

SQLCA sqlerrm sqlcode sqlerrm SQL (2) sqlerrp (3) sqlerrd 6 sqlerrd[0] : sqlerrd[1] : sqlerrd[2] : SQL SQL SQL, OPEN sqlerrd[2] 0, FETCH INSERT, SELECT, UPDATE, DELETE sqlerrd[3] : sqlerrd[4] : SQL sqlerrd[5] :, parse (4) sqlwarn 8 SQL, `W' sqlwarn[0] : sqlwarn[1] :, sqlwarn[2] : SQL AVG(), SUM() NULL sqlwarn[3] : SELECT FETCH sqlwarn[4] : UPDATE DELETE WHERE sqlwarn[5] : PL/SQL EXEC SQL CREATE PROCEDURE, EXEC SQL CREATE FUNCTION, EXEC SQL CREATE PACKAGE, EXEC SQL CREATE PACKAGE BODY sqlwarn[6] : 13

sqlwarn[7] : (5) sqlext SQLCA, (6) sqlglm() SQLCA 70, sqlglm(), SQLCA sqlerrmsqlerrmc sqlglm() void sqlglm ( char *message_buffer, size_t *buffer_size, size_t *message_length);, message_buffer : buffer_size : message_length : 364 WHENEVER SQLCA sqlglm() SQL,, SQLCA (sqlca_error) sqlglm() (sqlglm_error) /* Function sqlca_error Description : Error-Handling Routine using sqlca Return : 0 if no error, 1 error */ int sqlca_error() { /* */ if(sqlcasqlcode == 0) return 0; /*, */ printf("sql Error code %d : %s\n", sqlcasqlcode, sqlcasqlermsqlermc); return 1; /* Function sqlglm_error Desciption: Error-Handling routine using sqlglm() 14

Return: 0 if no error, 1 if error int sqlglm_error() { char error_msg[200]; size_t buf_len, msg_len; /* */ if (sqlcasqlcode == 0) return 0; /*, sqlglm() */ sqlglm(msg, &buf_len, &msg_len); printf( SQL Error: %s \n, msg); return 1; 365 WHENEVER WHENEVER Pro*C SQL Pro*C, SQL, C/C++ Pro*C SQL SQL SQL, WHENEVER, WHENEVER SQL WHENEVER,,, WHENEVER EXEC SQL WHENEVER <condition> <action> ; (1) WHENEVER (consdition) WHENEVER SQLWARNING : SQL, SQLCA sqlwarn[0], sqlcode SQL (true) SQLERROR : SQL, sqlcode 0 (true) NOT FOUND : SELECT FETCH (true) (2) WHENEVER <action> (true)dl, WHENEVER, WHENEVER CONTINUE :, 15

, WHENEVR CONTINUE DO : DO SQL GOTO lagel_name : label_name GOTO STOP : COMMIT (rollback) (3) WHENEVER WHENEVER WHENEVER, SQL WHENEVER WHENEVER WHENEVER, WHENEVER SQL WHENEVER WHENEVER /* WHENEVER */ EXEC SQL WHENEVER SQLERROR STOP; EXEC SQL CONNECT :username IDENTIFIED BY :password; /* WHENEVER */ EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL SELECT emp INTO : emp_name WHERE empno = :emp_number; WHENEVER, WHENEVER SQL WHENEVER, WHENEVER SQL WHENEVER 16