Download
1 / 6
60 likes | 174 Views
Introduction to PL/SQL. Procedural language extension to SQL Variable name: must begin with a letter, limited to 30 characters Data types: number, varchar2, date Reference data type: can define the data type using a database column or row. Example: lname faculty.flname%TYPE ;
E N D
Introduction to PL/SQL • Procedural language extension to SQL • Variable name: must begin with a letter, limited to 30 characters • Data types: number, varchar2, date • Reference data type: can define the data type using a database column or row. • Example: lname faculty.flname%TYPE ; • fac_row faculty%rowtype; • Comments: • block comments /* */ • line comments -- • Arithmetic and relational operators: +,-,*,/,**,<>,!=,>,>=,<,<=,=,:= • Handling character strings • Concatenation: || 1* select sfname || ' ' || smi || ' '|| slname Name from student SQL> / NAME --------------------------------------------------------------- Sarah M Miller Brian D Robinson Daniel Black Amanda J Mobley Ruben R Sanchez Michael S Connoly
Handling character strings • LENGTH(character string or variable): returns the total length of the argument SQL> select cadd, length(cadd) from customer where custid = 107; CADD LENGTH(CADD) ------------------------------ ------------ 1111 Water Street, Apt. #3 26 • UPPER (LOWER): converts the case of the argument 1 select first, upper(first), lower(first) 2* from customer where custid = 107 SQL> / FIRST UPPER(FIRST) LOWER(FIRST) ------------------------------ ------------------------------ ------------ Paula PAULA paula Space • INSTR (string, substring, [starting position], [nth occurrence]) • returns the position where substring occurs 1* select cadd,instr(cadd,’ '),instr(cadd,’ ',1,2) from customer SQL> / CADD INSTR(CADD,'') INSTR(CADD,'',1,2) ------------------------------ -------------- ------------------ 1111 Water Street, Apt. #3 5 11 4204 Garner Street 5 12 2211 Pine Drive 5 10 699 Pluto St. NW 4 10 987 Durham Rd. 4 11
PL/SQL program blocks • SUBSTR(string, starting position, [length]) SQL> select substr(cadd,1,4) from customer; SUBS ---- 1111 4204 2211 699 987 1* select substr(cadd,1,instr(cadd,' ')) from customer SQL> / SUBSTR(CADD,1,INSTR(CADD,’ ')) ------------------------------ 1111 4204 2211 699 987 • PL/SQL program blocks • General structure: • declare • <variable declarations> • begin • <body> • exception • <error handling statements> • end; SQL> set serveroutput on; 1 declare 2 total_order number; 3 begin 4 select sum(order_price*quantity) into total_order from orderline; 5 dbms_output.put_line('The total order quantity is: $' || total_order); 6* end; SQL> / The total order quantity is: $1615.52
Control structure • IF..THEN statements: • IF <condition> THEN <statements> end if; • if enrollment > 0 then • counter := counter+1; • end if; • IF <condition> THEN <statements> else <statements> end if; • if enrollment > 0 then • counter1 := counter1+1; • else counter2 := counter2+1; • end if; • IF <condition> THEN <statements> ELSIF <condition>… end if; • if enrollment > 0 then • counter1 := counter1+1; • elsif enrollment <0 then • counter2 := counter2+1; • elsif enrollment = 0 then • counter3 := counter3+1; • end if;
Iteration • Simple loops: keeps iterating until and exit or exit when is reached. • Declare pi number(9,7) := 3.1415926; • radius number(5); • area number(14,2); • begin • radius := 3; • loop • area := pi*power(radius,2); • insert into AREAS values (radius, area); • radius := radius+1; • exit when area > 100; • end loop; • end; SQL> desc areas Name Null? Type ------------------------------- -------- ---- RADIUS NUMBER(5) AREA NUMBER(14,2) SQL> select * from areas; RADIUS AREA --------- --------- 3 28.27 4 50.27 5 78.54 6 113.1
Cursors • A handle or pointer to control a number or rows retrieved by a query. • Declaring a cursor: CURSOR <cursor_name> IS <select statement>; • Opening a cursor: OPEN <cursor_name>; • Fetching from a cursor: FETCH <cursor_name> INTO <PL/SQL variables>; • Closing a cursor: CLOSE <cursor_name>; declare counter number := 0; enrl number := 0; cursor get_cs is select maxenrl - currenrl from course_section; begin open get_cs; loop fetch get_cs into enrl; if enrl > 0 then counter := counter+1; end if; exit when get_cs%NOTFOUND; end loop; dbms_output.put_line('The total number of course sections with maximum enrollment is: ' || counter); close get_cs; end;
