script_76.txt

orale培训教材包括了所有的sql说明和实例

---------- dml.txt ----------
/*
 * 范例名称：在PL/SQL中使用SQL语句
 * 文件名称：dml.txt
 */
drop table  PARTS;
CREATE TABLE PARTS
 (ID  NUMBER(38)
 ,DESCRIPTION  VARCHAR2(250) NOT NULL
 ,UNITPRICE  NUMBER NOT NULL
 ,ONHAND  NUMBER(38) NOT NULL
 ,REORDER  VARCHAR2(40) NOT NULL
 );



INSERT INTO parts
 VALUES (1,'Fax Machine',299,277,50);
INSERT INTO parts
 VALUES (2,'Copy Machine',4895,143,25);
INSERT INTO parts
 VALUES (3,'Laptop PC',2100,7631,1000);
INSERT INTO parts
 VALUES (4,'Desktop PC',1200,5903,1000);
INSERT INTO parts
 VALUES (5,'Scanner',99,490,200);
COMMIT;


--一个匿名plsql block中使用dml
DECLARE 
    newId number := 6;
    newDesc VARCHAR2(250) :='鼠标';
BEGIN
---select into --->变量
--处理变量

--插入数据库
    INSERT INTO parts
    VALUES (newId,newDesc,49,1200,500);
    --newId,newDesc全是plsql变量。
END;

--在 procedure中使用dml
create or replace procedure  proc_insert is 

    newId number := 7;
    newDesc VARCHAR2(250) :='shubiao7';
BEGIN
    INSERT INTO parts
    VALUES (newId,newDesc,49,1200,500);
    --在dml中用plsql变量
END;
--ok



--function中使用dml：在前面已经测试过
create or replace function  fun_insert 
return number

is 
    newId number := 8;
    newDesc VARCHAR2(250) :='mouse8';
BEGIN
    INSERT INTO parts
    VALUES (newId,newDesc,49,1200,500);
    return 0;
    
END;

--建立OK
SELECT fun_insert FROM DUAL;

--ERROR at line 1:
--ORA-14551: cannot perform a DML operation inside a query
--ORA-06512: at "SYSTEM.FUN_INSERT", line 8
--ORA-06512: at line 1



  
---------- query.txt ----------
/*
 * 范例名称：使用Queries
 * 文件名称：query.txt
 */





--select into 没使用纪录
set serveroutput on

DECLARE
	v_empno NUMBER (4);
	v_ename VARCHAR2 (20);
	v_salary NUMBER (10,2);
BEGIN
	SELECT  empno,ename,sal
		INTO v_empno,v_ename,v_salary
	FROM emp
	WHERE sal =(SELECT MAX(sal) FROM EMP);
	--求出工资最高的雇员.select into 支持使用子查询。
	
DBMS_OUTPUT.PUT_LINE ('最高工资是:'||v_ename);
DBMS_OUTPUT.PUT_LINE ('Id 是 '||v_empno ||'salary ' || to_char(v_salary,'999,999.99'));
END;

--使用纪录：见相应的record.txt
--基本子查询--------------------
 SELECT  empno,ename,sal
 FROM emp
 WHERE sal =(SELECT MAX(sal) FROM EMP);


---------- record.txt ----------
/*
 * 范例名称：使用Queries
 * 文件名称：record.txt
 */

--必须首先插入数据
delete from sm_emp;
commit;
insert into SM_EMP values('0000000001','张飞',100,'62613546');

insert into SM_EMP values('0000000002','关羽',100,'62613547');

insert into SM_EMP values('0000000003','刘备',150,'62613547');


set serveroutput on

DECLARE
	
    TYPE remp IS RECORD(
	v_empno  char(10),
	v_ename VARCHAR2 (20),
	v_salary NUMBER (10,2));

    rsm_emp remp;

BEGIN
	SELECT  empid,name,salary
		INTO rsm_emp.v_empno,rsm_emp.v_ename,rsm_emp.v_salary
	FROM sm_emp
	WHERE salary =(SELECT MAX(salary) FROM sm_emp);
	--求出工资最高的雇员
	
DBMS_OUTPUT.PUT_LINE ('Highest Paid Employee is '|| rsm_emp.v_ename);
DBMS_OUTPUT.PUT_LINE ('Id is '||rsm_emp.v_empno ||'Salary ' 
|| to_char(rsm_emp.v_salary,'999,999.99'));
END;

--改为min会如何？
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在line 11



--简单一点：直接向纪录变量整体插入纪录.
DECLARE
	
    TYPE remp IS RECORD(
	v_empno char(10),
	v_ename VARCHAR2 (20),
	v_salary NUMBER (10,2));

    rsm_emp remp;

BEGIN
	SELECT  empid,name,salary
		INTO rsm_emp
	FROM sm_emp
	WHERE salary =(SELECT MAX(salary) FROM sm_emp);
	--求出工资最高的雇员
	
DBMS_OUTPUT.PUT_LINE ('Highest Paid Employee is '||rsm_emp.v_ename);
DBMS_OUTPUT.PUT_LINE ('Id is '||rsm_emp.v_empno ||'Salary ' || to_char(rsm_emp.v_salary,'999,999.99'));
END;



---------- rowtype.txt ------------------------------
/*
 * 范例名称：声明变量--使用%ROWTYPE
 * 文件名称：rowtype.txt
 */

drop table stock;

create table stock
(symbol varchar2(50),
price  number(6,2));

insert into stock values('IBM',188.88);

insert into stock values('ORCL',100.88);

DECLARE
	stock_info1 stock%ROWTYPE;
	stock_info2 stock%ROWTYPE;
	stock_info3 stock%ROWTYPE;
BEGIN
--对应纪录直接取得相应字段的：Populate the specific fields in a record
	SELECT symbol,price
	INTO stock_info1.symbol,stock_info1.price
	FROM stock
	WHERE symbol ='ORCL';

	--对应纪录直接 放入纪录var中
	SELECT * INTO stock_info2 FROM stock
	WHERE symbol ='ORCL';

	stock_info3 := stock_info2;
	--记录间可以相互赋值

	stock_info3.symbol:='ORACLE';
	--对纪录的各字段赋值。

	DBMS_OUTPUT.PUT_LINE('stock_info1 :' || stock_info1.symbol || ' '||stock_info1.price);
	DBMS_OUTPUT.PUT_LINE('stock_info2 :' || stock_info2.symbol || ' '||stock_info2.price);
	DBMS_OUTPUT.PUT_LINE('stock_info3 :' || stock_info3.symbol || ' '||stock_info3.price);
END;

--改变表结构,有影响吗？大家自己尝试一下。
drop table stock;

create table stock
(symbol varchar2(50),
price  number(6,2),
owner varchar2(10));

insert into stock values('IBM',188.88,'me');

insert into stock values('ORCL',100.88,'ij05');



---------- cursor.txt ----------
/*
 * 范例名称：使用cursor
 * 文件名称：cursor.txt
 */

 SQL> desc emp;scott中的oracle样本库雇员表:具体见cursor_parameter_emp_dept
 名称                   
 --------------------
 EMPNO                  雇员编号
 ENAME                  雇员名称
 JOB                    雇员工作
 MGR                    
 HIREDATE               
 SAL                    雇员工资
 COMM                   雇员提成
 DEPTNO                 雇员所属部门编号


----将游标中的字段的值fetch到变量中

--处理一条纪录单一的游标语句。
SET SERVEROUTPUT ON
DECLARE
    V_ename  EMP.ENAME%TYPE;
    V_salary EMP.SAL%TYPE;
	--定义游标
	CURSOR c_emp IS SELECT ename,sal FROM emp;
BEGIN
	OPEN c_emp;--打开光标
	
	FETCH c_emp INTO v_ename,v_salary;
	--将游标中的字段的值fetch到变量中
	
	--PROCESS DATA 
	DBMS_OUTPUT.PUT_LINE ('Sal of Employee ' || v_ename
	|| 	' is '|| v_salary);
	
	CLOSE c_emp;--关闭光标
END;


--循环处理光标结果集中的每一条纪录。

SET SERVEROUTPUT ON
DECLARE
    V_ename  EMP.ENAME%TYPE;
    V_salary EMP.SAL%TYPE;
	CURSOR c_emp IS SELECT ename,sal FROM emp;
BEGIN
	OPEN c_emp;
	LOOP
		FETCH c_emp INTO v_ename,v_salary;
		--将游标中的字段的值fetch到变量中
		EXIT WHEN c_emp%NOTFOUND;
		--当游标中没有数据时跳出loop



		DBMS_OUTPUT.PUT_LINE ('Sal of Employee ' || v_ename
		|| 	' is '|| v_salary);
		--.....处理数据
	END LOOP;
	CLOSE c_emp;
END;




----------------------------------minzu--------
---------- minzu_光标.txt ----------
/*
 * 范例名称：使用参数cursor
 * 文件名称：minzu_光标.txt
 */

drop table man;

drop table minzu;

--增加人口总数:rekou 
create table minzu
(mincheng  varchar2(2) primary key,
renkou number(10,0));

insert into minzu values('汉',100000);
insert into minzu values('满',100);




--注意：此时不使用外键!
create table man 
(name varchar2(10),
minzu varchar2(2));

insert into man values('张飞','汉');
insert into man values('关羽','汉');
insert into man values('康熙','满');
---------------------------------------------
--用参数光标给出man表中对应的汉族人的名称
declare
        cursor c_man(p_minzu  varchar2) is select * from man where minzu=p_minzu;
        a varchar2(2);
        r_man man%rowtype;

begin 
        a :='汉';
        


        open c_man(a);

        loop 
                fetch c_man into r_man ;
                exit when c_man%notfound;
                dbms_output.put_line(r_man.name);
        end loop;
        close c_man;
end;



--