INSERT INTO itemfile VALUES('i201','nuts','spares',100,50,250,20);
INSERT INTO itemfile VALUES('i202','bolts','spares',95,125,300,16.5); INSERT INTO itemfile VALUES('i204','holders','spares',18,30,75,112); INSERT INTO itemfile VALUES('i205','covers','accessories',30,15,50,400); INSERT INTO itemfile VALUES('i203','panels','accessories',75,30,150,4000); INSERT INTO itemfile VALUES('i206','brackets','spares',150,73,200,132);COMMIT;
--表2
CREATE TABLE order_master ( orderno VARCHAR2(5), odate DATE, vencode VARCHAR2(5), ostatus CHAR(1), del_date DATE ); alter session set nls_date_language = 'AMERICAN'; INSERT INTO order_master VALUES('o001','12-MAY-05', 'V002','c', '15-MAY-05'); INSERT INTO order_master VALUES('o002','14-MAY-05', 'V001','p', '15-MAY-05'); INSERT INTO order_master VALUES('o003','14-MAY-05', 'V001','p', '15-FEB-05'); INSERT INTO order_master VALUES('o004','14-MAY-05', 'V003','p', '15-FEB-05'); INSERT INTO order_master VALUES('o005','14-MAY-05', 'V001','p', '15-FEB-05'); INSERT INTO order_master VALUES('o006','14-APR-03', 'V004','p', '18-MAY-05'); INSERT INTO order_master VALUES('o007','14-MAY-04', 'V003','p', '10-FEB-05'); INSERT INTO order_master VALUES('o008','11-MAY-05', 'V001','p', '12-JUN-05'); INSERT INTO order_master VALUES('o009','14-JAN-05', 'V002','c', '16-FEB-05'); INSERT INTO order_master VALUES('o011','14-JAN-05', 'V001','p', '10-FEB-05'); INSERT INTO order_master VALUES('o012','14-FEB-05', 'V003','p', '15-MAY-05'); INSERT INTO order_master VALUES('o013','14-MAR-05', 'V001','p', '15-MAY-05'); INSERT INTO order_master VALUES('o014','14-FEB-05', 'V002','c', '12-MAY-05'); INSERT INTO order_master VALUES('o015','14-APR-03', 'V004','p', '17-APR-05'); COMMIT;alter session set nls_date_language = 'SIMPLIFIED CHINESE';
--表3
CREATE TABLE ORDER_DETAIL ( ORDERNO VARCHAR2(5) PRIMARY KEY, ODATE DATE, VENCODE VARCHAR2(5), itemcode VARCHAR2(10), qty_ord NUMBER, qty_deld NUMBER, OSTATUS CHAR(1), DEL_DATE DATE, ORDER_COST NUMBER ); --表4 create table student ( stuid int, stuname varchar2(10), sex varchar2(2) );insert into student values (101, '刘德华', '男');
insert into student values (102, '张学友', '男'); insert into student values (103, '周润发', '男'); insert into student values (104, 'wind', '女'); insert into student values (105, '林青霞', '女');--表5
create table subject ( subid varchar2(10) primary key, subname varchar2(30) not null ); insert into subject values ('s001', 'oracle'); insert into subject values ('s002', 'java');----------------------------------------------------------------
**************************************************************** 第一部分：开发过程 **************************************************************** --------------------------------------------------------------------------------------------------------------------------------
*************************************************************** 1.建立过程：不带任何参数 --------------------------------------------------------------- --案例01：建立一个输出当前系统日期和时间的过程 create or replace procedure system_out_time is begin dbms_output.put_line(systimestamp); end; 调用过程方法01： SQL> exec system_out_time; 调用过程方法02： SQL> call system_out_time();
--案例01：打印乘法小九九
create or replace procedure xjj as i integer; j integer; begin dbms_output.put_line(' 打印小九九 '); for i in 1..9 loop for j in 1..9 loop if i>=j then dbms_output.put_line(to_char(j) ||'*'||to_char(i) || '='||to_char(i*j)||' '); end if; end loop; dbms_output.put_line(' '); end loop; end; ---------------------------------------------------------------- *************************************************************** 2.建立过程：带输入IN参数 --------------------------------------------------------------- --案例01：使用输入参数查询用户信息 create or replace procedure find_emp(emp_no number) as empname emp.ename%type; begin select ename into empname from emp where empno=emp_no; dbms_output.put_line(' 雇员的姓名是： '|| empname); exception when no_data_found then dbms_output.put_line ( ' 雇员编号未找到： '); end;----案例02：根据已知的编号显示出对应职工的姓名 create or replace procedure queryempname (sfindno emp.empno%type ) as sname emp.ename%type; sjob emp.job%type; begin select ename, job into sname, sjob from emp where empno=sfindno; dbms_output.put_line(' 编号为 '||sfindno|| ' 的职工姓名为 '||sname || ' 工作为 :' ||sjob); exception when no_data_found then dbms_output.put_line('没有符合条件的记录！'); when too_many_rows then dbms_output.put_line('返回的行数太多！'); when others then dbms_output.put_line('发生以外错误！'); end;
--案例03：新建一个带有输入参数的存储过程
create or replace procedure add_empoyee
( eno number, name varchar2, sal number, job varchar2 default 'CLERK', dno number ) is e_inte exception; pragma exception_init(e_inte, -2291); --关联例外和错误号,相当于raise begin insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno); exception when dup_val_on_index then --dup_val_on_index是oracle预定义错误 raise_application_error(-20000,'雇员信息不能重复！'); --raise_application_error显示触发例外 when e_inte then raise_application_error(-20001, '部门号不存在！'); end;
---如该改写为，注意运行结果的异同：
create or replace procedure add_empoyee ( eno number, name varchar2, sal number, job varchar2 default 'CLERK', dno number ) is e_inte exception; begin insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno); exception when dup_val_on_index then --dup_val_on_index是oracle预定义错误 dbms_output.put_line('雇员信息不能重复！'); when e_inte then dbms_output.put_line('部门号不存在！'); end;
--调用方法
exec add_empoyee(&no,'&name', &sal,'&job',&dno);exec add_empoyee(1113,'CLERK',2000,'MANAGER',15);
如果使用job的默认值则：
exec add_empoyee(1113,'CLERK',2000,null,10);
----------------------------------------------------------------
*************************************************************** 3.建立过程：带输入out参数 ---------------------------------------------------------------
--案例01:带有输出out参数的存储过程
--新建存储过程
create or replace procedure test001 (epno in number, v02 out varchar2) as salary number; begin select sal into salary from emp where empno=epno; if salary <1000 then v02:='这样的工资太低！'; elsif salary between 1000 and 2000 then v02:='这样的工资还可以接受！'; else v02:='这样的待遇是我们不离开的原因！'; end if; end; --调用存储过程方法01 (这样的值是固定的)： declare v2 varchar2(200); --声明变量时需要和输入参数的类型一致 begin test001 (&no, v2); dbms_output.put_line('v02的值为：'||v2); end;
--调用存储过程方法02（用户交互）：
declare empno number:=&empno; v2 varchar(200); --声明变量时需要和输入参数的类型一致 begin test001 (empno, v2); dbms_output.put_line('v02的值为：'||v2); end;------也可以写成
declare empno number; v2 varchar(200); --声明变量时需要和输入参数的类型一致 begin empno:=&empno; test001 (empno, v2); dbms_output.put_line('v02的值为：'||v2); end;
--案例02：新建一个用于输出雇员名以及工资的过程
create or replace procedure query_emp ( eno number, name out varchar2, salary out number ) is begin select ename, sal into name, salary from emp where empno=eno; exception when no_data_found then raise_application_error(-20000, '这个员工不存在！'); end;
--调用过程方法01:(使用固定的变量)
declare nae varchar2(10); --这里的变量和过程中输入参数的类型需要对应 sala number; begin query_emp(7788,nae,sala); dbms_output.put_line('the employee name is : '||nae); dbms_output.put_line('the employee sal is : '||sala); end;--调用过程方法02:(使用交互的变量)
declare nae varchar2(10); --这里的变量和过程中输入参数的类型需要对应 sala number; begin query_emp(&empno,nae,sala); dbms_output.put_line('the employee name is : '||nae); dbms_output.put_line('the employee sal is : '||sala); end;----------------------------------------------------------------
在存储过程中使用dbms_output.put_line ------------------------------------------------------------------案例02的另外一种写法：
create or replace procedure query_emp ( eno number, name out varchar2, salary out number ) is begin select ename, sal into name, salary from emp where empno=eno; dbms_output.put_line('the employee name is : '||name); dbms_output.put_line('the employee sal is : '||salary); exception when no_data_found then raise_application_error(-20000, '这个员工不存在！'); end;
--调用过程方法01:(使用固定的变量)
declare nae varchar2(10); --这里的变量和过程中输入参数的类型需要对应 sala number; begin query_emp(7788,nae,sala); end; --调用过程方法02:(使用交互式变量)declare
nae varchar2(10); --这里的变量和过程中输入参数的类型需要对应 sala number; begin query_emp(&empno,nae,sala); end;
---------------------------------------------------------------- *************************************************************** 4.建立过程：带输入 IN out参数 ---------------------------------------------------------------
--案例01：新建带 IN OUT参数的过程
--新建代码 create or replace procedure swap ( p1 in out number, p2 in out number ) as v_temp number; begin v_temp:=p1; p1:=p2; p2:=v_temp; end;--调用带IN OUT参数的存储过程方法01：
/*由于参数即是输入又是输出参数，所以需要声明变量并输入值*/ declare n01 number:=100; n02 number:=200; begin swap(n01, n02); dbms_output.put_line ('n01=' ||n01); dbms_output.put_line ('n02=' ||n02); end;--调用带IN OUT参数的存储过程方法02：
declare n01 number; n02 number; begin n01:=100; n02:=200; swap(n01, n02); dbms_output.put_line ('n01=' ||n01); dbms_output.put_line ('n02=' ||n02); end; --案例02:新建一个带有in out参数的过程create or replace procedure comp
( nm01 in out number, nm02 in out number ) is v1 number; v2 number; begin v1:=nm01/nm02; v2:=mod(nm01,nm02); nm01:=v1; nm02:=v2; end; --调用 declare n1 number; n2 number; begin n1:=100; n2:=30; comp(n1,n2); dbms_output.put_line(n1); dbms_output.put_line(n2); end;
----------------------------------------------------------------
*************************************************************** 5.建立过程：为参数传递变量和数据 ---------------------------------------------------------------exec queryempname(sfindno=>7788);
exec queryempname(sfindno=>&no);