--表 create table tb_myTable( mname vardhar2(30), pwd varchar2(30) ); --存儲過程 create or replace procedure sp_myProcedure(p_name varchar2) is begin end; exec sp_myProcedure('123'); --塊 declare v_ename emp.ename%type;--定義成與原表相同類型及長度 降低維護量 v_eno varchar2(20); v_sal number(6,2); v_sal2 number(6,2):=11.22; v_date date; v_valid boolean not null default false; v_const constant varchar2(2):='hym'; begin select ename,empno into v_ename,v_eno from emp where empno=&no; --打開輸出選項 set serveroutput on --輸出 dbms_output.put_line(v_ename); exception when no_data_found then dbms_output.put_line('沒有找到數據'); end; --函數 create function fn_myFunction(p_name varchar2) return number is mysal number(7,2); begin select sal into mysal from emp where ename=upper(myname); return mysal; end; var sal number; call fn_myFunction('scott') into:sal; --包(只定義) create package pck_mypackage is procedure sp_updateSal(ename varchar2,sal number); function fn_myFunction(ename varchar2) return number; end; --包體 create pacckage body pck_mypacckage is begin --sp_updateSal的實現 --fn_myFunction的實現 end; --調用 call pck_mypackage.sp_updateSal('scott',1000); --循環 declare v_num number:=1; begin loop dbms_output.put_line(v_num); exit when v_num=10; v_num:=v_num+1; end loop; while v_num<=20 loop dbms_output.put_line(v_num); v_num:=v_num+1; end loop; end; --游標 declare text_cursor cursor; begin open test_cursor for select ename,sal from emp where deptno=&no; loop fetch test_cursor into exit when end loop; end;