1 / 25

CS178 Database Management PL/SQL

CS178 Database Management PL/SQL. session 8. References: ORACLE 9i PROGRAMMING A Primer Rajshekhar Sunderraman. What is PL/SQL ?. Oracle’s procedural extension to SQL superset of the SQL language, including high-level programming features such as: structures, types variables constants

ida
Download Presentation

CS178 Database Management PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS178 Database ManagementPL/SQL session 8 References: ORACLE 9i PROGRAMMING A Primer Rajshekhar Sunderraman

  2. What is PL/SQL ? • Oracle’s procedural extension to SQL • superset of the SQL language, including high-level programming features such as: • structures, types • variables • constants • assignment statements • conditional statements • loops • customized error handling • structured data

  3. Data types and variables • all the sql data types • variables declaration: • <variable-name> <datatype> [not null] [:=<initial value>] ex. sid number(5) not null := 1111; sname varchar2(30); grade real(5,2) := 2.5; • constants declaration: • <constant-name> constant <data-type> := <value> ex. maxcolumns constant integer(2) := 30; • anchor variables: • <variable-name><object>%type [not null] [:= <initial-value>] ex. cnum customers.sid%type; ctable customers%rowtype; -- creates a variable of type table that has the same fields as the customers table;

  4. Comments • Single line comments : “--” • Block comments : C like • “/* this is a comment */”

  5. Assignments • <variable> := <expression> ex. i:=i+1; sname := ‘Jones’; sales := price*qty;

  6. Example • In SQLPLUS run the following command: • set SERVEROUTPUT on DECLARE i INTEGER; sid NUMBER(5) NOT NULL := 1111; sname VARCHAR2(30); grade REAL(5) := 12.5; MAXCOLUMNS CONSTANT INTEGER(2) := 30; BEGIN i := 35; sname := 'Jones'; sid := 2000; DBMS_OUTPUT.PUT_LINE('i = ' || i); DBMS_OUTPUT.PUT_LINE('sid = ' || sid); DBMS_OUTPUT.PUT_LINE('sname = ' || sname); DBMS_OUTPUT.PUT_LINE('grade = ' || grade); DBMS_OUTPUT.PUT_LINE('MAXCOLUMNS = ' || MAXCOLUMNS); END; /

  7. Conditional statements • if-then • if <condition> then <statement> end if; ex. if (grade > 70) and (grade <90) then i:=i+1; end if; • if-then-else • if <condition> then <stmt1> else <stmt2> end if; • if-then-elseif • if <condition1> then <stmt1> elseif <condition2> then <stmt2> … elseif <conditionn> then <stmtn> else <stmtn+1> endif;

  8. Loops • Basic loop • loop <stmt> end loop; ex. loop i:=i+1; if (i>10) then exit; end if; sum := sum + i; end loop; • Alternatively we can have exited with “exit when i >10”

  9. Loops • For loop for <loop-counter> in [reverse] <lower>..<upper> loop <statement>; end loop; ex. for i in 1..10 loop sum := sum + i; end loop; • While loop while <condition> loop <statement>; end loop; ex. while (i<10) loop sum := sum +i; i := i+1; end loop;

  10. Program structure • Anonymous programs declare --type and variable declarations begin --executable section null; exception -- exception handlers when others then null; --default handler for all untreated exceptions end; • Procedures and functions procedure <proc-name> ( <p1>,..,<pn> ) is [declarations] begin --executable section; exception --exception handlers; end; where “<p1> has the following syntax: <variable-name> [in | out | in out] <datatype>

  11. Functions and procedures • Procedure : will not return a result • Function : will return a value after execution • ex. function myfunc( param1 IN number) return number is grade number; begin grade:=param1 ; return (grade); end; procedure myproc( param1 IN number, param2 out number) is begin param2:=param1; end;

  12. How to call the function ? declare function myfunc( param1 IN number) return number is grade number; begin grade:=param1 ; return (grade); end; begin DBMS_OUTPUT.PUT_LINE('The function returned: ' || myfunc(10)); end; / • Exercise : WRITE THE CODE FOR CALLING THE PROCEDURE

  13. Use the select statement in PL/SQL(only if the select returns one single row as result ) declare name varchar2(100); id number; begin select sid, fname into id,name from students where sid = 1111; end; /

  14. Cursors • When the result of a select statement consists of more than one row the “select into” statement can not be used. • A PL/SQL cursor allows a program to fetch and process information one row at a time • Declaration: cursor <sname> is <select statement>;

  15. Cursor example DECLARE CURSOR c1 IS select sid,fname from students; c1_rec c1%rowtype; BEGIN if not c1%isopen then open c1; end if; fetch c1 into c1_rec; while c1%found loop dbms_output.put_line('Row Number ' || c1%rowcount || '> ' || c1_rec.sid || ' ' || c1_rec.fname); fetch c1 into c1_rec; end loop; close c1; END; /

  16. How to work with cursors • declare the cursor • declare a variable rec_name of type cursor%rowtype • “open c_name” • fetch row by row “fetch c_name into rec_name” • “close cursor” • c_name%found – returns true if there are still records , false otherwise • c_name%isopen - returns true if the cursor is open, false otherwise

  17. Cursor “for” example DECLARE CURSOR c1 IS select sid,fname from students; BEGIN for c1_rec in c1 loop dbms_output.put_line('Row Number ' || c1%rowcount || '> ' || c1_rec.sid || ' ' || c1_rec.fname); end loop; END; / • When using “for loops” the cursor does not have to be explicitly opened and fetched from.

  18. Stored Procedures • Syntax create [or replace] procedure <proc_name> [(<parameter_list>)] as <declarations> begin --executable section [exception <exception-section>] end

  19. why needed ? • most of the time the stored procedures contain the entire application logic • Ex: create a report with all the courses on all the years, average grade of the curse, students enrolled in the course, their grades on all the components of the courses and their final grade.

  20. Exceptions • when an error occurs during the execution of a PL/SQL program a exception is raised • program control is transferred to the exception section

  21. Common exception • NO_DATA_FOUND -- select into failed because the it resulted in no row • TOO_MANY_ROWS -- select into failed because the it resulted more than one row • INVALID_NUMBER -- to_number(string) has invalid input parameter • ZERO_DEVIDE -- a division by 0 occured

  22. Views • A view is a named query , virtual table • Views are created, dropped or granted access to, identical to a table.

  23. How do views differ from tables? From : http://www.cdoug.org/docs/views-1099.pdf

  24. Syntax create view <view_name> as <select statement>; drop view <view_name> ; ex. create view vCourses as select catalog.ctitle, courses.term, courses.lineno from catalog, courses where catalog.cno=courses.cno; select * from vCourses;

  25. Sql Injection • http://www.unixwiz.net/techtips/sql-injection.html • SELECT fieldlist FROM table WHERE field = '$EMAIL'; • SELECT fieldlist FROM table WHERE field = 'anything' OR 'x'='x'; • SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'; UPDATE members SET email = 'steve@unixwiz.net' WHERE email = 'bob@example.com'; • SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'; DROP TABLE members; --';

More Related