--1----------------
declare
v_date date;
v_no number:=10;
v_name varchar2(100) not null:='khaled'; --when you use not null then you should give value
begin
dbms_output.put_line(v_date);
dbms_output.put_line(v_no);
dbms_output.put_line(v_name);
v_no:=v_no+10;
v_name:='carla';
dbms_output.put_line(v_name);
v_date:='10-May-2012';
dbms_output.put_line(v_date);
dbms_output.put_line(v_no);
end;
----------------------------------
--2---------
declare
v_date date:=sysdate;
v_no number:=10*2;
v_pi constant number:= 3.14;
begin
dbms_output.put_line(v_date);
dbms_output.put_line(v_no);
dbms_output.put_line(v_pi);
v_date:=v_date+10;
dbms_output.put_line(v_date);
--v_pi:=10; if you try to do this then you will get error;
end;
----------------------------
begin
dbms_output.put_line('Welcome');
end;
-- example i want you to print Father's day
begin
dbms_output.put_line('Father''s day');
end;
-----------------------------
--when your string contains an apstrophe
-- it is recomnded to use the q' notation
select 'today is the father''s day'
from dual;
--using ? as delimiter q'?your text?'
select q'?today is the father's day?'
from dual;
--using * as delimiter q'*your text*'
select q'*today is the father's day*'
from dual;
--using ( as delimiter q'(your text)'
select q'(today is the father's day)'
from dual;
--using [ as delimiter q'[your text]'
select q'[today is the father's day not the mother's day ]'
from dual;
--------------------------
begin
dbms_output.put_line('Father''s day');
dbms_output.put_line( q'(Father's day)' ) ;
end;
---------------------------------------------------------------------------------------
declare
v_no number:=300;
v_date date;
v_timestamp timestamp;
v_project_period interval year to month;
begin
v_no :=125.323;
v_date :=sysdate;
v_timestamp:=current_timestamp;
--current_timestamp return the cuurent date and time for user session
v_project_period :='03-04';
DBMS_OUTPUT.PUT_LINE(v_no);
DBMS_OUTPUT.PUT_LINE(v_date);
DBMS_OUTPUT.PUT_LINE(v_timestamp);
DBMS_OUTPUT.PUT_LINE('the project period is :'||v_project_period);
DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
end;
------------------------------------------------------------------------------------------------
declare
v_empno number:=10;
v_ename varchar2(100):='khaled';
v_salary employees.salary%TYPE;
V_HIRE_PERIOD interval YEAR TO MONTH;
v_empno1 v_empno%type; --it will not be initilized like v_empno
begin
dbms_output.put_line(v_empno);
dbms_output.put_line(v_ename);
v_salary :=5000;
dbms_output.put_line(v_salary);
V_HIRE_PERIOD:= '1-3';
dbms_output.put_line(V_HIRE_PERIOD);
dbms_output.put_line(v_empno1);
end;
-------------------------------------------------
--write a pl/sql block to print the first name and the last name for employee=100
select first_name,last_name
from
employees
where employee_id=100;
declare
v_fname employees.first_name%type;
v_lname employees.last_name%type;
begin
-- The select should retrieve 1 record, otherwise it will be exception
select first_name,last_name
into v_fname, v_lname
from
employees
where employee_id=100;
dbms_output.put_line('first name: '||v_fname );
dbms_output.put_line('last name: '||v_lname );
end;
------------
--example of exception
declare
v_fname employees.first_name%type;
v_lname employees.last_name%type;
begin
-- The query will not retrieve any record, it will be exception
select first_name,last_name
into v_fname, v_lname
from
employees
where employee_id=9000;
dbms_output.put_line('first name: '||v_fname );
dbms_output.put_line('last name: '||v_lname );
end;
---------------------------------------------------------------------------------------------------
select *
from
employees
where employee_id=100;
--define a bind variable v_sal to be a number
--create a bolck to store the salary for employee_id=100 in this variable
--print the variable
--note: number bind variable without size
variable v_sal number
begin
select salary into :v_sal
from
employees
where employee_id=100;
end;
print v_sal
-------------------------------------------
variable v_sal number
set autoprint on
begin
select salary into :v_sal
from
employees
where employee_id=100;
end;
--------
--note: varchar2 bind variable should have size
var v_name varchar2(100)
set autoprint on
begin
select first_name into :v_name
from
employees
where employee_id=100;
end;
No comments:
Post a Comment