可以使用: 在你写sql语句的时候可以这么写: ‘ declare ... begin ... end;'
能给个简单的例子吗? 用proc开发的话,该怎么写阿
给你一个例子 #include <stdlib.h> #include <stdio.h> #include <ctype.h> #include <string.h> #include <sqlda.h> #include <sqlcpr.h>EXEC SQL INCLUDE SQLCA.H;EXEC SQL BEGIN DECLARE SECTION; varchar empname[11]; varchar jobtype[9]; varchar hired[9]; int salary; int dept; int worked_longer; int higher_sal; int total_in_dept; int no_data_flag; varchar uid[20]; varchar pwd[20]; EXEC SQL END DECLARE SECTION; void main() { /* Set up userid and password */ strcpy ((char *)uid.arr,"scott"); uid.len= (short) strlen((char *)uid.arr); strcpy ((char *)pwd.arr,"tiger"); pwd.len= (short) strlen((char *)pwd.arr); printf("\n\n\tEmbedded PL/SQL Demo\n\n"); printf("Trying to connect..."); EXEC SQL WHENEVER SQLERROR GOTO errprint; /* Connect to ORACLE */ EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf(" connected.\n"); for (;;) /* Loop infinitely */ { printf("\n** Name of employee? (<return> to end) \n"); gets((char *)empname.arr); /* Get the name */ if (strlen((char *)empname.arr) == 0) /* No name entered, */ { EXEC SQL COMMIT WORK RELEASE; /* so log off ORACLE */ exit(0); /* and exit program */ } empname.len = (short) strlen((char *)empname.arr); /* ---------------------------------- */ /* ----- Begin the PL/SQL block ----- */ /* ---------------------------------- */ EXEC SQL EXECUTE BEGIN /* Clear flag upon entry to block */ :no_data_flag := 0; SELECT job, hiredate, sal, deptno INTO :jobtype, :hired, :salary, :dept FROM emp WHERE ename = UPPER(:empname); /* Get number of people whose length of * * service is longer */ SELECT count(*) INTO :worked_longer FROM emp WHERE hiredate < :hired; /* Get number of people with a higher salary */ SELECT count(*) INTO :higher_sal FROM emp WHERE sal > :salary; /* Get number of people in the same department */ SELECT count(*) INTO :total_in_dept FROM emp WHERE deptno = :dept; EXCEPTION /* If we have a bad employee name, set flag */ WHEN NO_DATA_FOUND THEN :no_data_flag := 1; END; END-EXEC; /* -------------------------------- */ /* ----- End the PL/SQL block ----- */ /* -------------------------------- */ /* Check flag to see if valid name * * was entered */ if (no_data_flag == 1) { printf("\n%s does not work for the company.\n",empname.arr); printf("Please try again.\n"); continue; } /* Properly terminate character strings * * returned by ORACLE */ jobtype.arr[jobtype.len] = '\0'; hired.arr[hired.len] = '\0'; /* Display all the information */ printf("\n%s's job is: %s\n", empname.arr, jobtype.arr); printf("Hired on: %s\n", hired.arr); printf(" %d people have served longer\n", worked_longer); printf("Salary is: %d\n", salary); printf(" %d people have a higher salary\n", higher_sal); printf("Department number is: %d\n", dept); printf(" %d people in the department\n", total_in_dept); } /* End of loop */errprint: /* We end up here if an error occurs */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n\n>>>>> Error during execution:\n"); /* Print ORACLE error message and log off the database */ printf("%s\n",sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK RELEASE; exit(1); }
在你写sql语句的时候可以这么写:
‘ declare ... begin ... end;'
#include <stdlib.h>
#include <stdio.h>
#include <ctype.h>
#include <string.h>
#include <sqlda.h>
#include <sqlcpr.h>EXEC SQL INCLUDE SQLCA.H;EXEC SQL BEGIN DECLARE SECTION;
varchar empname[11];
varchar jobtype[9];
varchar hired[9];
int salary;
int dept;
int worked_longer;
int higher_sal;
int total_in_dept;
int no_data_flag;
varchar uid[20];
varchar pwd[20];
EXEC SQL END DECLARE SECTION;
void main()
{ /* Set up userid and password */
strcpy ((char *)uid.arr,"scott");
uid.len= (short) strlen((char *)uid.arr);
strcpy ((char *)pwd.arr,"tiger");
pwd.len= (short) strlen((char *)pwd.arr); printf("\n\n\tEmbedded PL/SQL Demo\n\n");
printf("Trying to connect..."); EXEC SQL WHENEVER SQLERROR GOTO errprint; /* Connect to ORACLE */
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
printf(" connected.\n");
for (;;) /* Loop infinitely */
{
printf("\n** Name of employee? (<return> to end) \n");
gets((char *)empname.arr); /* Get the name */
if (strlen((char *)empname.arr) == 0) /* No name entered, */
{
EXEC SQL COMMIT WORK RELEASE; /* so log off ORACLE */
exit(0); /* and exit program */
}
empname.len = (short) strlen((char *)empname.arr); /* ---------------------------------- */
/* ----- Begin the PL/SQL block ----- */
/* ---------------------------------- */
EXEC SQL EXECUTE BEGIN
/* Clear flag upon entry to block */
:no_data_flag := 0; SELECT job, hiredate, sal, deptno INTO
:jobtype, :hired, :salary, :dept FROM emp
WHERE ename = UPPER(:empname); /* Get number of people whose length of *
* service is longer */
SELECT count(*) INTO :worked_longer FROM emp
WHERE hiredate < :hired; /* Get number of people with a higher salary */
SELECT count(*) INTO :higher_sal FROM emp
WHERE sal > :salary; /* Get number of people in the same department */
SELECT count(*) INTO :total_in_dept FROM emp
WHERE deptno = :dept; EXCEPTION
/* If we have a bad employee name, set flag */
WHEN NO_DATA_FOUND THEN
:no_data_flag := 1;
END; END-EXEC;
/* -------------------------------- */
/* ----- End the PL/SQL block ----- */
/* -------------------------------- */ /* Check flag to see if valid name *
* was entered */
if (no_data_flag == 1)
{
printf("\n%s does not work for the company.\n",empname.arr);
printf("Please try again.\n");
continue;
} /* Properly terminate character strings *
* returned by ORACLE */
jobtype.arr[jobtype.len] = '\0';
hired.arr[hired.len] = '\0'; /* Display all the information */
printf("\n%s's job is: %s\n", empname.arr, jobtype.arr);
printf("Hired on: %s\n", hired.arr);
printf(" %d people have served longer\n", worked_longer);
printf("Salary is: %d\n", salary);
printf(" %d people have a higher salary\n", higher_sal);
printf("Department number is: %d\n", dept);
printf(" %d people in the department\n", total_in_dept);
} /* End of loop */errprint:
/* We end up here if an error occurs */
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n\n>>>>> Error during execution:\n");
/* Print ORACLE error message and log off the database */
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}