oracle基础语法试题,Oracle基础语法-Oracle

--表

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;