scott.sql
Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM UTLSAMPL.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem gdudey 06/28/95 - Modified for desktop seed database
Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
Rem rlim 04/29/91 - change char to varchar2
Rem mmoore 04/08/91 - use unlimited tablespace priv
Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFFrem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
rem OATES: Created: 16-Feb-83GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;SET TERMOUT ON
SET ECHO ON
Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM UTLSAMPL.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem gdudey 06/28/95 - Modified for desktop seed database
Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
Rem rlim 04/29/91 - change char to varchar2
Rem mmoore 04/08/91 - use unlimited tablespace priv
Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFFrem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
rem OATES: Created: 16-Feb-83GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;SET TERMOUT ON
SET ECHO ON
/*
* sample1.pc
*
* Prompts the user for an employee number,
* then queries the emp table for the employee's
* name, salary and commission. Uses indicator
* variables (in an indicator struct) to determine
* if the commission is NULL.
*
*/#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>/* Define constants for VARCHAR lengths. */
#include "sample1.h"
/* Declare variables. No declare section is
needed if MODE=ORACLE. */
VARCHAR username[20]; /* VARCHAR is an Oracle-supplied struct */
varchar password[40]; /* varchar can be in lower case also. *//* Define a host structure for the output values of
a SELECT statement. */
struct
{
VARCHAR emp_name[20];
float salary;
float commission;
} emprec;/* Define an indicator struct to correspond
to the host output struct. */
struct
{
short emp_name_ind;
short sal_ind;
short comm_ind;
} emprec_ind;/* Input host variable. */
int emp_number;int total_queried;/* Include the SQL Communications Area.
You can use #include or EXEC SQL INCLUDE. */
#include <sqlca.h>/* Declare error handling function. */
void sql_error(msg)
char *msg;
{
char err_msg[128];
size_t buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}void main()
{
char temp_char[32];/* Connect to ORACLE--
* Copy the username into the VARCHAR.
*/
strncpy((char *) username.arr, "SCOTT", 20);/* Set the length component of the VARCHAR. */
username.len =
(unsigned short) strlen((char *) username.arr);/* Copy the password. */
strncpy((char *) password.arr, "TIGER", 40);
password.len =
(unsigned short) strlen((char *) password.arr);/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");/* Connect to ORACLE. Program will call sql_error()
* if an error occurs when connecting to the default database.
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username.arr);/* Loop, selecting individual employee's results */ total_queried = 0; for (;;)
{
emp_number = 0;
printf("\nEnter employee number (0 to quit): ");
gets(temp_char);
emp_number = atoi(temp_char);
if (emp_number == 0)
break;/* Branch to the notfound label when the
* 1403 ("No data found") condition occurs.
*/
EXEC SQL WHENEVER NOT FOUND GOTO notfound; EXEC SQL SELECT ename, sal, comm
INTO :emprec INDICATOR :emprec_ind
FROM EMP
WHERE EMPNO = :emp_number;/* Print data. */
printf("\n\nEmployee Salary Commission\n");
printf("-------- ------- ----------\n");/* Null-terminate the output string data. */
emprec.emp_name.arr[emprec.emp_name.len] = '\0';
printf("%s %7.2f ",
emprec.emp_name.arr, emprec.salary); if (emprec_ind.comm_ind == -1)
printf("NULL\n");
else
printf("%7.2f\n", emprec.commission); total_queried++;
continue;notfound:
printf("\nNot a valid employee number - try again.\n"); } /* end for(;;) */ printf("\n\nTotal rows returned was %d.\n", total_queried);
printf("\nG'day.\n\n\n");/* Disconnect from ORACLE. */
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_SUCCESS);
}
EXEC SQL BEGIN DECLARE SESSION;
VARCHAR USERNAME[20];
VARCHAR PASSWORD[20];
EXEC SQL END DECLARE SESSION;