1,上次难点复习
1 求所有部门的平均奖金
2 求各部门的平均薪水
3 求各部门每个工种的平均薪水
4 求各部门每个工种大于2000的平均薪水
5 求10号部门的平均工资(2种写法)
6 创建一个学生表
7 并向表中插入一条数据
8 列出不是管理层的员工:
9 工资最高前10名的员工信息(rownum是一个属性值)
10 找到各部门大于本部门平均薪水的员工:
11 创建索引
Linux 环境 Oracle Pro*C Oracle 编程:
官方文档
Linux 下Oracle数据库的环境部署
配置proc编译器
配置vim 为pc文件高亮
Proc*C/C++编译配置
Oracle数据类型
proc小试身手
C语言方式:SQL 内嵌语法:
C++方式,SQL 内嵌语法,proc编译c++需要配置一下
Pro*C 编程:insert into 向数据库插入数据:
Pro*C 编程:update 向数据库更新数据:
Pro*C 编程:select从数据库查数据:之char数据类型
Pro*C varchar 数据类型,自动去除占位的空格
Pro*C string数据类型,自动去除占位的空格
NULL问题,1,插入数据时,字段填NULL
NULL问题,2,获取数据时,判断是否为NULL
Pro*C/C++ 编程,向Oracle 插入中文
先把Linux,Oracle,Sqlplus设置为UTF8
Oracle 通讯区 和错误处理:
游标的官方文档
游标与数组:查询多条记录,全部插入一张表中
用普通游标提取数据,一行一行的获取数据,就像单向链表
用滚动游标提取数据,可查询指定任意行数据:指哪儿打哪儿
[难点]动态SQL,1,只能使用非select语法
[难点]动态SQL,2,非查询语言, 带固定数量的宿主变量
[难点]动态SQL,3 处理选择列表项(select查询出来的结果列数固定)
[难点]动态SQL4,具备完整select 语法 ANSI方法
[难点]动态SQL4,具备完整select 语法Oracle官方方法
复习
1 求所有部门的平均奖金SQL> select avg(comm) from emp;
AVG(COMM)
----------
550
SQL> select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0))
----------------
115.789474
2 求各部门的平均薪水SQL> select deptno, avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 3839.28571
10 4187.5
3 求各部门每个工种的平均薪水SQL> select deptno, job, avg(sal) from emp group by deptno, job;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 CLERK 950
30 SALESMAN 1400
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
20 clerk 8000
30 MANAGER 2850
10 CLERK 1300
20
10 MANAGER 2450
20 ANALYST 3000
10 8000
12 rows selected.
4 求各部门每个工种大于2000的平均薪水SQL> select deptno, job, avg(sal) from emp group by deptno, job having avg(sal) >2000;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
20 clerk 8000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 3000
10 8000
7 rows selected.
5 求10号部门的平均工资(2种写法)
1:SQL> select deptno,avg(sal) from emp group by deptno having deptno=10;
DEPTNO AVG(SAL)
---------- ----------
10 4187.5
2:SQL> select deptno,avg(sal) from emp where deptno=10 group by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 4187.5
6 创建一个学生表sid
sname
sex
age
SQL> create table studet (sid number(2) primary key, sname varchar2(50) not null,email varchar2(50), sex varchar2(1),age number(2));
Table created.
SQL> desc studet;
Name Null? Type
------------------------- -------- -----------------------
SID NOT NULL NUMBER(2)
SNAME NOT NULL VARCHAR2(14)
EMAIL VARCHAR2(20)
SEX VARCHAR2(1)
AGE NUMBER(2)
7 并向表中插入一条数据SQL> insert into studet values(10,'鲁智深','Linux','m',20);
1 row created.
8 列出不是管理层的员工:SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 tom_abc 8000 10
2 aaa clerk 7092 17-DEC-80 8000 20
3 abc 20
4 aaa clerk 7092 26-AUG-16 8000 20
5 chunli 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9,显示工资最高前10名的员工信息(rownum是一个属性值):SQL> select * from (select * from emp order by sal desc nulls last) where rownum <= 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 tom_abc 8000 10
4 aaa clerk 7092 26-AUG-16 8000 20
2 aaa clerk 7092 17-DEC-80 8000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
10 rows selected.
10 ,找到各部门大于本部门平均薪水的员工:SQL> select d.deptno, d.avgsal,e.ename,e.sal from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and sal>=avgsal;
DEPTNO AVGSAL ENAME SAL
---------- ---------- ---------- ----------
30 1566.66667 ALLEN 1600
30 1566.66667 BLAKE 2850
10 4187.5 KING 5000
10 4187.5 tom_abc 8000
20 3839.28571 aaa 8000
20 3839.28571 aaa 8000
6 rows selected.
创建索引:SQL> desc studet;
Name Null? Type
----------------- -------- -------- ------------
SID NOT NULL NUMBER(2)
SNAME NOT NULL VARCHAR2(14)
EMAIL VARCHAR2(20)
SEX VARCHAR2(1)
AGE NUMBER(2)
SQL> create index student_index on studet (sname);
SQL> desc studet;
Name Null? Type
------------------------ -------- ---------------------
SID NOT NULL NUMBER(2)
SNAME NOT NULL VARCHAR2(14)
EMAIL VARCHAR2(20)
SEX VARCHAR2(1)
AGE NUMBER(2)
Linux 环境 Oracle Pro*C Oracle 编程:
官方文档:Pro*C/C++ Programmer's Guide:
Linux 下Oracle数据库的安装,
配置proc编译器:[oracle@oracle11 proc]$ vim /opt/oracle/app/product/11.2.0/dbhome_1/precomp/admin/pcscfg.cfg
在后面加:/usr/lib/gcc/x86_64-redhat-linux/4.4.4/include
变成这样的:
[oracle@oracle11 ~]$ cat /opt/oracle/app/product/11.2.0/dbhome_1/precomp/admin/pcscfg.cfg
sys_include=($ORACLE_HOME/precomp/public,/usr/include,/usr/lib/gcc-lib/x86_64-redhat-linux/3.2.3/include,/usr/lib/gcc/x86_64-redhat-linux/4.1.1/include,/usr/lib64/gcc/x86_64-suse-linux/4.1.0/include,/usr/lib64/gcc/x86_64-suse-linux/4.3/include,/usr/lib/gcc/x86_64-redhat-linux/4.4.4/include)
ltype=short
define=__x86_64__
[oracle@oracle11 ~]$
如果不配置:
Proc 编译，出现错误PCC-S-02015, unable to open include file
加选项parse=no也可以解决
[配置]vim 为pc文件高亮[oracle@oracle11 ~]$ cat /home/oracle/.vimrc
autocmd BufEnter *.pc set filetype=esqlc
[Proc*C/C++编译配置]proc的头文件在:/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/
proc的动态库在:/opt/oracle/app/product/11.2.0/dbhome_1/lib/
编译示例:$ proc hello.pc > /dev/null && gcc hello.c -I/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/ -L/opt/oracle/app/product/11.2.0/dbhome_1/lib/ -lclntsh && ./a.out
Oracle数据类型
proc小试身手[oracle@oracle11 proc]$ cat hello.pc
#include
#include
#include
int main()
{
printf("Hello \n");
return 0;
}
[oracle@oracle11 proc]$
proc编译:[oracle@oracle11 proc]$ proc parse=no hello.pc
gcc 编译:[oracle@oracle11 proc]$ gcc hello.c && ./a.out
Hello
一键联编:
[oracle@oracle11 proc]$ proc hello.pc > /dev/null && gcc hello.c && ./a.out
Hello
[oracle@oracle11 proc]$
C语言方式:SQL 内嵌语法:[oracle@oracle11 proc]$ cat hello.pc
#include
#include
#include
#include "sqlca.h"
//定义宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char *serverid="scott/11@orcl";
EXEC SQL END DECLARE SECTION;
int main()
{
int ret = 0;
printf("Hello \n");
//嵌入式SQL语句必须以 EXEC SQL 开头
EXEC SQL connect:serverid;//妈蛋,这是谁龟腚的语法
if(sqlca.sqlcode != 0)
{
ret = sqlca.sqlcode;
printf("EXEC SQL connect:serverid err \n");
exit(1);
}
printf("connect ok!\n");
return ret;
}
[oracle@oracle11 proc]$ proc hello.pc > /dev/null && gcc hello.c -I/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/ -L/opt/oracle/app/product/11.2.0/dbhome_1/lib/ -lclntsh && ./a.out
Hello
connect ok!
[oracle@oracle11 proc]$
编译很烦:Makefile就好用了
[oracle@oracle11 proc]$ proc hello.pc > /dev/null
[oracle@oracle11 proc]$ gcc hello.c-I/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/ \
-L/opt/oracle/app/product/11.2.0/dbhome_1/lib/ -lclntsh
[oracle@oracle11 proc]$ ./a.out
C++方式,SQL 内嵌语法:[oracle@oracle11 proc]$ cat c++_hello.pc
#include
#include
#include
#include "sqlca.h"
using namespace std;
//定义宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char *serverid="scott/11@orcl";
EXEC SQL END DECLARE SECTION;
int main()
{
int ret = 0;
//printf("Hello \n");
cout <
//嵌入式SQL语句必须以 EXEC SQL 开头
EXEC SQL connect:serverid;//妈蛋,这是谁龟腚的语法
if(sqlca.sqlcode != 0)
{
ret = sqlca.sqlcode;
cout<
exit(1);
}
cout<
return ret;
}
[oracle@oracle11 proc]$ proc iname=./c++_hello.pc oname=c++_hello.cpp PARSE=NONE CODE=cpp > /dev/null && g++ c++_hello.cpp -I/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/ -L/opt/oracle/app/product/11.2.0/dbhome_1/lib/ -lclntsh && ./a.out
c++_hello.cpp:153: warning: deprecated conversion from string constant to ‘char*’
hello c++ sql
connect ok!
[oracle@oracle11 proc]$
proc编译c++需要配置一下
[oracle@oracle11 proc]
$ proc iname=./c++_hello.pc oname=c++_hello.cpp PARSE=NONE CODE=cpp > /dev/null \
&& g++ c++_hello.cpp -I/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/\
-L/opt/oracle/app/product/11.2.0/dbhome_1/lib/ -lclntsh \
&& ./a.out
Pro*C 编程:insert into 向数据库插入数据:[oracle@oracle11 proc]$ cat db_op.pc
#include
#include
#include
#include "sqlca.h"
//定义宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char *serverid="scott/11@orcl";
int deptno = 0;
char dname[20];
char loc[20];
EXEC SQL END DECLARE SECTION;
int main()
{
int ret = 0;
printf("Hello \n");
//嵌入式SQL语句必须以 EXEC SQL 开头
EXEC SQL connect:serverid;
if(sqlca.sqlcode != 0)
{
ret = sqlca.sqlcode;
printf("EXEC SQL connect:serverid err \n");
exit(1);
}
deptno =50;
strcpy(dname,"oracle");
strcpy(loc,"China");
EXEC SQL insert into dept (deptno,dname,loc) values(:deptno,:dname,:loc);
EXEC SQL commit;
//EXEC SQL commit RELEASE;提交事务并断开连接
printf("connect ok!\n");
return ret;
}
[oracle@oracle11 proc]$ cat comp
INCLUDE_PATH=/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/
LD_PATH=/opt/oracle/app/product/11.2.0/dbhome_1/lib/
proc db_op.pc
gcc db_op.c -I$INCLUDE_PATH -L$LD_PATH -lclntsh
./a.out
[oracle@oracle11 proc]$ bash comp
Pro*C/C++: Release 11.2.0.1.0 - Production on Mon Aug 29 03:09:12 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /opt/oracle/app/product/11.2.0/dbhome_1/precomp/admin/pcscfg.cfg
Hello
connect ok!
开一个窗口:验证
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 oracle China
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Pro*C 编程:update 向数据库更新数据:[oracle@oracle11 proc]$ cat db_op.pc
#include
#include
#include
#include "sqlca.h"
//定义宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char *serverid="scott/11@orcl";
int deptno = 0;
char dname[20];
char loc[20];
EXEC SQL END DECLARE SECTION;
int main()
{
int ret = 0;
printf("Hello \n");
//嵌入式SQL语句必须以 EXEC SQL 开头
EXEC SQL connect:serverid;
if(sqlca.sqlcode != 0)
{
ret = sqlca.sqlcode;
printf("EXEC SQL connect:serverid err \n");
exit(1);
}
deptno =50;
strcpy(dname,"oracle");
strcpy(loc,"England");
EXEC SQL update dept set loc = :loc where deptno=:deptno;
EXEC SQL commit;
//EXEC SQL commit RELEASE;提交事务并断开连接
printf("connect ok!\n");
return ret;
}
[oracle@oracle11 proc]$
编译小脚本:
[oracle@oracle11 proc]$ cat comp
INCLUDE_PATH=/opt/oracle/app/product/11.2.0/dbhome_1/precomp/public/
LD_PATH=/opt/oracle/app/product/11.2.0/dbhome_1/lib/
proc db_op.pc
gcc db_op.c -I$INCLUDE_PATH -L$LD_PATH -lclntsh
./a.out
编译:
[oracle@oracle11 proc]$ bash comp
Pro*C/C++: Release 11.2.0.1.0 - Production on Mon Aug 29 03:14:01 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /opt/oracle/app/product/11.2.0/dbhome_1/precomp/admin/pcscfg.cfg
Hello
connect ok!
验证:
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 oracle England
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
Pro*C 编程:select从数据库查数据:之char数据类型[oracle@oracle11 proc]$ cat db_op.pc
#include
#include
#include
#include "sqlca.h"
EXEC SQL BEGIN DECLARE SECTION;//定义宿主变量
char *serverid="scott/11@orcl";
int deptno = 0;
char dname[20];
char loc[20];
EXEC SQL END DECLARE SECTION;
int main()
{
int ret = 0;
printf("Hello \n
最低0.47元/天 解锁文章
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
