script_75.txt

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

---------- fun_proc_2.txt ----------
/*
 * 范例名称：存储过程
 * 文件名称：fun_proc_2.txt
 */

--query a procedure
desc user_source;
select text from user_source where name='find_area';
--未选定行, 大写数据库对象名！
select text from user_source where name='FIND_AREA';


--drop function procedure
DROP FUNCTION find_area;
DROP PROCEDURE get_area;

--在DECLARE SESSION使用----------subprogram---------------------------------
set serveroutput on
DECLARE
        num_a NUMBER := 6;
        num_b NUMBER;
	num_area NUMBER;
	
	--定义一个客户端函数，在匿名块中使用
	function find_area_sub(vlength in number,vwidth in number)
		return number
	as
		varea number;
	begin
		varea :=vlength *vwidth;
		return varea;
	end;
	--END SUBPROGAME
BEGIN
        num_b := 10;
	num_area :=find_area_sub(num_a,num_b);
        dbms_output.put_line(' Value of num_a ' || num_a);
        dbms_output.put_line(' Value of num_b ' || num_b);
        dbms_output.put_line(' Area of num_b and num_a ' || num_area);
	
END;
  
  
  
---------- type.txt ----------
/*
 * 范例名称：变量和常量
 * 文件名称：type.txt
 */

DECLARE
 V_A NUMBER (5):=10;

 V_B V_A%TYPE :=15;  --v_b为v_a的类型
 
 V_C V_A%TYPE;
 BEGIN
 DBMS_OUTPUT.PUT_LINE
 ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END;




---------- plsql1.txt ----------
/*
 * 范例名称：plsql流控
 * 文件名称：plsql1.txt
 */


--建立调价过程(hike_prices)，以后要用
--old_price	原价
--percent_hike 	上涨百分比
--new_price	最终价格    
CREATE  or replace PROCEDURE hike_prices (old_price NUMBER,
                               percent_hike NUMBER := 5,
                               new_price OUT NUMBER)
IS
BEGIN
        ---new_price := old_price + old_price * percent_hike / 100;
	
END hike_prices;

--变量和常量的定义使用
--使用hike_prices过程，改变常量的值
DECLARE
        price_to_hike NUMBER(6,2) := 20;
        hiked_price NUMBER(6,2) := 0;
	ten_percent CONSTANT NUMBER :=10; --定义一个常量
	
	
BEGIN
        dbms_output.put_line('Price before hike ' || price_to_hike);
        dbms_output.put_line('hiked_price before hike ' || hiked_price);
	dbms_output.put_line('the constant is ' || ten_percent);
	
	--对常量赋值
	--ten_percent:=15;
	--ten_percent:=hiked_price;
	
	--调用调价过程(hike_prices)
	hike_prices (price_to_hike,ten_percent, hiked_price);

	--以常量作为调价过程(hike_prices)out 参数
	--hike_prices (price_to_hike, 10,ten_percent);

        dbms_output.put_line('price_to_hike after hike ' || price_to_hike);
        dbms_output.put_line('hiked_price after hike ' || hiked_price);
	dbms_output.put_line('the constant is ' || ten_percent);
END;


-----------------------------SELECT   INTO为变量赋值-----------------------------------------------
--单独使用SELECT语句不用INTO
CREATE  OR REPLACE PROCEDURE sm_second_proc IS
BEGIN
    SELECT * FROM emp;
    --因为linux下还没有建立sm_emp表，该为emp;
END;
 --Err警告: 创建的过程带有编译错误。对于plsql,porcedure中不可以有select语句。可以用select into语句

     
CREATE  OR REPLACE PROCEDURE sm_second_proc IS
     
BEGIN
    --SELECT name FROM sm_emp where empid='0000000007';
    SELECT ename FROM emp where empno=7934;
END;
--警告: 创建的过程带有编译错误


--使用select into 为变量赋值
DECLARE
	TOTAL_SALARY NUMBER(10,2);
	TOTAL_COMMISSION NUMBER(10,2);
BEGIN
	--从EMP雇员表取出10号部门的(DEPTNO =10)工资和提成总和
	SELECT SUM(SAL),SUM(SAL *0.1)
	INTO TOTAL_SALARY,TOTAL_COMMISSION
	FROM EMP
	WHERE DEPTNO =10;
	dbms_output.put_line('toatal salary is ' || TOTAL_SALARY);
	dbms_output.put_line('提成总和： ' || TOTAL_COMMISSION);
END;



---------- type.txt ----------
/*
 * 范例名称：变量和常量
 * 文件名称：type.txt
 */

DECLARE
 V_A NUMBER (5):=10;

 V_B V_A%TYPE :=15;  --v_b为v_a的类型
 
 V_C V_A%TYPE;
 BEGIN
 DBMS_OUTPUT.PUT_LINE
 ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END;




-------------IF 语句-----------------------------------------here--

--执行结果如何呢？
DECLARE

  --注意emp.sal%TYPE这一类型定义，
  --定义v_SALARY为emp表的sal字段的类型
  --这是plsql一种方便的定义类型方法。
  v_SALARY emp.sal%TYPE;

  v_Comment VARCHAR2(35);
BEGIN
  /* 返回emp的salary */
  SELECT sal
    INTO v_SALARY
    FROM emp
    WHERE empno = 1;
    --WHERE empno = 7900;

  
  IF v_SALARY < 50 THEN
    v_Comment := '比较低';
  ELSIF v_SALARY < 1000 THEN
    v_Comment := 'A little higher：高了点';
  ELSIF v_SALARY < 10000 THEN   --ELSIF! 不是elseif
    v_Comment := 'high';
  ELSE
    v_Comment := 'VERY HIGH!';
  END IF;
  
  dbms_output.put_line('The Employee: salary is ' || v_SALARY);
  dbms_output.put_line('his salary is ' || v_Comment);

END;

/*
	ERROR 位于第 1 行:
	ORA-01403: 未找到数据
	ORA-06512: 在line 10
	
	SQL> show error;
	没有错误。
*/
--因为emp表中没有empno = 1的纪录！在exception部分将专门处理这类问题。
select * from emp;
--改为查询empno= 7900  的纪录


-------------建立一个临时表
DROP TABLE temp_table;

CREATE TABLE temp_table (
  num_col    NUMBER,
  char_col   VARCHAR2(60)
  );



-------------LOOP语句
DECLARE
  v_Counter NUMBER := 1;
BEGIN
  LOOP
    -- 以loop counter向temp_table Insert纪录
    -- .
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- 循环终止条件 - when the loop counter > 50 
    -- 跳出循环.
    IF v_Counter > 50 THEN
      EXIT;
    END IF;
  END LOOP;
END;

--扩充：还可以使用exit  when 条件式：当条件成立时，推出循环
DECLARE
  v_Counter NUMBER := 1;
BEGIN
  LOOP
    -- 以loop counter向temp_table Insert纪录
    -- .
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- 循环终止条件 - when the loop counter > 50 
    -- 跳出循环.
    
      EXIT when v_Counter>50;
    
  END LOOP;
END;




-------------while  LOOP语句 
DECLARE
  v_Counter NUMBER := 1;
BEGIN
  --v_Counter <= 50循环.
  WHILE v_Counter <= 50 
  LOOP
    INSERT INTO temp_table  -- 以loop counter向temp_table Insert纪录
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;


-------------FOR  LOOP语句

BEGIN
  FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO temp_table (num_col)
      VALUES (v_LoopCounter);-- 以loop counter向temp_table Insert纪录
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_LoopCounter);
  --在for循环外不可以访问循环变量
END;

/*
	ERROR 位于第 1 行:
	ORA-06550: 第 6 行, 第 24 列:
	PLS-00201: 必须说明标识符 'V_LOOPCOUNTER'
	ORA-06550: 第 6 行, 第 3 列:
	PL/SQL: Statement ignored


*/
BEGIN
  FOR v_LoopCounter IN 50..1 LOOP
    INSERT INTO temp_table (num_col)
      VALUES (v_LoopCounter);-- 以loop counter向temp_table Insert纪录
  END LOOP;
  --DBMS_OUTPUT.PUT_LINE(v_LoopCounter);
  --在for循环外不可以访问循环变量
END;


----------  ----------
/*
 * 范例名称：GOTO
 * 文件名称：goto.txt
 */

--GOTO TEST
BEGIN
	
	FOR V_COUNTER in 1 ..10 LOOP
		DBMS_OUTPUT.PUT_LINE('COUNTER IS ' || V_COUNTER);
		IF V_COUNTER =4 THEN
			
			GOTO end_of_loop;
		END IF;
	  
	END LOOP;
	
	<<end_of_loop>>
	DBMS_OUTPUT.PUT_LINE( '4 LOOP PRINTED!');
        --如果在标号后没有语句，则会出err!
END ;





---------- 扩展LABLE_LOOP ----------
DECLARE
   

BEGIN
    <<LOOP_OUTER>>
    FOR V_LOOP1  IN  1..3 LOOP
      DBMS_OUTPUT.PUT_LINE('V_LOOP1 IS ' || V_LOOP1);
      <<LOOP_INNER>>
      FOR  V_LOOP2 IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('V_LOOP2 IS ' || V_LOOP2);
	--当内层循环变量=2，退出外层循环
	IF V_LOOP2=2 THEN
	  EXIT LOOP_OUTER;
	END IF;
      END LOOP LOOP_INNER;
    END LOOP  LOOP_OUTER;
    DBMS_OUTPUT.PUT_LINE('END BOTH LOOP ');
    
END;