1 / 39

PL/SQL Guidelines & Functions: Syntax, Conversions, Operators

Learn PL/SQL best practices, block syntax, functions, data conversions, character string manipulation, and interacting with Oracle Server.

johndellis
Download Presentation

PL/SQL Guidelines & Functions: Syntax, Conversions, Operators

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. PL/SQL Part B

  2. PL/SQL Block Syntax and Guidelines • Identifiers • Can contain up to 30 characters • Cannot contain reserved words unless enclosed in double quotation marks (for example, "SELECT"). • Must begin with an alphabetic character • Should not have the same name as a database table column name

  3. PL/SQL Block Syntax and Guidelines • Literals • Character and date literals must be enclosed in single quotation marks. • A PL/SQL block is terminated by a slash ( / ) on a line by itself. v_ename := 'Henderson';

  4. PL/SQL Arithmetic Operators in Describing Order of Precedence

  5. Commenting Code • Prefix single-line comments with two dashes (--). • Place multi-line comments between the symbols /* and */. • Example ... v_sal NUMBER (9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_sal := v_monthly_sal * 12; END; -- This is the end of the block

  6. PL/SQL Functions • PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories: • Error reporting • Number • Character • Conversion • Date

  7. PL/SQL Functions • Examples • Build the mailing list for a company. • Convert the employee name to lowercase. v_mailing_address := v_name||CHR(10)|| v_address||CHR(10)||v_state|| CHR(10)||v_zip; v_ename := LOWER(v_ename); • CHR is the SQL function that converts an ASCII code to its • corresponding character; 10 is the code for a line feed.

  8. Datatype Conversion • Convert data to comparable datatypes. • Mixed datatypes can result in an error and affect performance. • Conversion functions: • TO_CHAR • TO_DATE • TO_NUMBER DECLARE v_date VARCHAR2(15); BEGIN SELECT TO_CHAR(hiredate, 'MON. DD, YYYY') INTO v_date FROM emp WHERE empno = 7839; END;

  9. PL/SQL Data Conversion Functions • Implicit data conversions • Interpreter automatically converts value from one data type to another • If PL/SQL interpreter unable to implicitly convert value error occurs • Explicit data conversions • Convert variables to different data types • Using data conversion functions

  10. PL/SQL Data Conversion Functions of PL/SQL

  11. Manipulating Character Strings with PL/SQL • String • Character data value • Consists of one or more characters • Concatenating • Joining two separate strings • Parse • Separate single string consisting of two data items separated by commas or spaces

  12. Concatenating Character Strings • Operator • || • Syntax: • new_string := string1 || string2;

  13. To correct the error, use the TO_DATE conversion function. v_date := TO_DATE ('January 13, 1998', 'Month DD, YYYY'); Datatype Conversion • This statement produces a compilation error if the variable v_date is declared as datatype DATE. v_date := 'January 13, 1998';

  14. Removing Blank Leading and Trailing Spaces from Strings • LTRIM function • Remove blank leading spaces • string := LTRIM(string_variable_name); • RTRIM function • Remove blank trailing spaces • string := RTRIM(string_variable_name);

  15. Finding the Length of Character Strings • LENGTH function syntax • string_length := LENGTH(string_variable_name);

  16. Character String Case Functions • Modify case of character strings • Functions and syntax: • string := UPPER(string_variable_name); • string := LOWER(string_variable_name); • string := INITCAP(string_variable_name);

  17. Parsing Character Strings • INSTR function • Searches string for specific substring • Syntax: • start_position := INSTR(original_string, substring); • SUBSTR function • Extracts specific number of characters from character string • Starting at given point

  18. Parsing Character Strings (continued) • SUBSTR function (continued) • Syntax: • extracted_string := SUBSTR(string_variable, starting_point, number_of_characters); • Use INSTR to find delimiter

  19. Debugging PL/SQL Programs • Syntax error • Occurs when command does not follow guidelines of programming language • Generate compiler or interpreter error messages • Logic error • Does not stop program from running • Results in incorrect result

  20. Program with a Syntax Error

  21. Program with a Logic Error

  22. PL/SQL Interacting with the Oracle Server

  23. Comparing SQL and PL/SQL Statement Types • A PL/SQL block is not a transaction unit. Commits, savepoints, and rollbacks are independent of blocks, but you can issue these commands within a block. • PL/SQL does not support data definition language (DDL), such as CREATE TABLE, ALTER TABLE, or DROP TABLE. • PL/SQL does not support data control language (DCL), such as GRANT or REVOKE.

  24. SQL Statements in PL/SQL • Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned. • Make changes to rows in the database by using DML commands. • Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command. • Determine DML outcome with implicit cursors.

  25. SELECT Statements in PL/SQL • Retrieve data from the database with SELECT. • Syntax SELECT select_list INTO {variable_name[, variable_name]... | record_name} FROM table WHERE condition;

  26. SELECT Statements in PL/SQL • The INTO clause is required. • You must give one variable for each item selected • Queries Must Return One and Only One Row • More than one row or no row generates an error. DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; ... END;

  27. Retrieving Data in PL/SQL • Terminate each SQL statement with a (;). • The INTO clause is required for the SELECT statement when it is embedded in PL/SQL. • Retrieve the order date and the ship date for the specified order. DECLARE v_orderdate ord.orderdate%TYPE; v_shipdate ord.shipdate%TYPE; BEGIN SELECT orderdate, shipdate INTO v_orderdate, v_shipdate FROM ord WHERE id = 620; ... END;

  28. Retrieving Data in PL/SQL • Group functions cannot be used in PL/SQL syntax. They are used in SQL statements within a PL/SQL block. • Return the sum of the salaries for all employees in the specified department. DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) -- group function INTO v_sum_sal FROM emp WHERE deptno = v_deptno; END;

  29. INSERT UPDATE DELETE Manipulating Data Using PL/SQL • Make changes to database tables by using DML commands: • INSERT • UPDATE • DELETE

  30. Inserting Data • Add new employee information to the EMP table. • Example BEGIN INSERT INTO emp(empno, ename, job, deptno) VALUES (empno_sequence.NEXTVAL, 'HARDING', 'CLERK', 10); END;

  31. Updating Data • Increase the salary of all employees in the EMP table who are Analysts. • PL/SQL variable assignments always use := and SQL column assignments always use =. • if column names and identifier names are identical in the WHERE clause, the Oracle Server looks to the database first for the name. DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATE emp SET sal = sal + v_sal_increase WHERE job = 'ANALYST'; END;

  32. Deleting Data • Delete rows that belong to department 10 from the EMP table. • Example DECLARE v_deptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END;

  33. Notes • There is no possibility for ambiguity with identifiers and column names in the INSERT statement. Any identifier in the INSERT clause must be a database column name. • There may be ambiguity in the SET clause of the UPDATE statement because although the identifier on the left of the assignment operator is always a database column, the identifier on the right can be either a database column or a PL/SQL variable.

  34. Naming Conventions • Use a naming convention to avoid ambiguity in the WHERE clause. • Database columns and identifiers should have distinct names. • Syntax errors can arise because PL/SQL checks the database first for a column in the table.

  35. Naming Conventions • DECLARE • orderdate ord.orderdate%TYPE; • shipdate ord.shipdate%TYPE; • ordid ord.ordid%TYPE := 601; • BEGIN • SELECT orderdate, shipdate • INTO orderdate, shipdate • FROM ord • WHERE ordid = ordid; • END; • SQL> / • DECLARE • * • ERROR at line 1: • ORA-01422: exact fetch returns more than requested • number of rows • ORA-06512: at line 6

  36. There is no possibility for ambiguity in the SELECT clause because any identifier in the SELECT clause must be a database column name. There is no possibility for ambiguity in the INTO clause because identifiers in the INTO clause must be PL/SQL variables. Only in the WHERE clause is there the possibility of confusion. Notes

  37. Example

  38. Example

  39. Example

More Related