|
|
Topics: Hands-On 03 – Procedure Builder Procedure Builder allows you to develop stored procedures, functions, packages, and triggers in Oracle. Many developers have found the SQL*PLUS and favorite text editor approach cumbersome. You use Procedure Builder for ease of writing program development, debugging, and version control. In this Hands-On, you will learn how to: Creating PL/SQL Procedure using Procedure Builder, add a record into the “dept” table using PL/SQL. In this Hands-On, you will learn the basic fundamentals of PL/SQL block. The Core Subjects are: 1- Developing a PL/SQL block 2- Types of block 3- Anonymous or unnamed block 4- Components of a PL/SQL block a. Declaration b. Body or Execution c. Exception 5- Cursor a. Implicit cursor b. Explicit cursor 6- Parameters in cursor
Manuscript Go to “MS-DOS.” Change directory to the iself directory and login to “sqlplus” as "iself/schooling." >> cd ..\iself >>sqlplus iself/schooling
Write a PL/SQL block to use only the "body" section with PL/SQL statement. >> begin null; end; / Use the slash (/) to compile and run the block.
Add the "declaration" section with no variables. Then compile and run the block. >> declare -- no variable begin null; end; /
Use the “Set serveroutput on” to display the buffer used by dbms_output. >> set serveroutput on
Write a PL/SQL block, to output the "Hello iselfschooling" message. >> begin dbms_output.put_line('Hello iselfschooling'); end; /
describe the department table. >> desc dept
Write a PL/SQL block, to declare a department name variable with the same datatype of the department name.
Then assign "HR" to the variable and output the variable. >> declare v_dname varchar2(14); begin v_dname := 'HR'; dbms_output.put_line(v_dname); end; /
Save the PL/SQL block as “test_myblock.” Then go to notepad and open the PL/sql block from the “iself” directory.
Use the %type keyword, to declare a variable as the same datatype and size of the department name column of the dept table. Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same output! >> declare v_dname dept.dname%type; begin v_dname := 'HR'; dbms_output.put_line(v_dname); end; /
Go back to “notepad,” use the %rowtype keyword to declare a variable row type. Assign ‘HR’ to the “department name” item of the defined variable row. Output the variable. >> declare v_drec dept%rowtype; begin v_drec.dname := 'HR'; dbms_output.put_line(v_drec.dname); end; /
Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same output!
Back to “Notepad.” Use the implicit cursor to query the department table information where deptno = 30. Check if no record was found then print “Record was not found.” Else print the department name only.
>> declare v_drec dept%rowtype; begin select deptno, dname, loc into v_drec.deptno,v_drec.dname, v_drec.loc from dept where deptno = 30; if sql%notfound then dbms_output.put_line('Record was not found.'); else dbsm_output.put_line(v_drec.dname); end if; end; /
Then save the file. Go to “SQLPLUS.” Get the file. And run it.
Back to “Notepad” Modify the PL/SQL block to move the entire record into the variable row.
Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same result!
Now, you should practice this over and over, until you become a master at it. Good Luck! |
|